Oracle 基础FAQ

Oracle

2.1 oracle用户卸载时被占用，怎么找到是谁占用

主题：oracle用户卸载时被占用，怎么找到是谁(session)占用

在卸载oracle用户时，发现用户被占用，一般情况可以很顺利的解决：

查询某用户会话session
SQL> select sid,serial#,status,server from v$session where username='ENIPDBZQ';

SID SERIAL# STATUS SERVER
---------- ---------- -------- ---------
348 21909 INACTIVE DEDICATED

终止非激活状态的会话
SQL> alter system kill session '348,21909';

这样就可以将被占用的用户session强制终止，可以顺利的卸载掉用户。

往往还会遇到不一般的情况，当有进程重连时，你将当前的终止了，马上又出来新的会话，令人头疼：

这种情况就用追根溯源法：

查询oracle数据库的某oracle用户的会话信息，如会话发起主机，会话发起OS用户，进程名，状态，会话个数

select username,machine,osuser,program,status,count(machine)
from v$session where username='CBPDBZQ1'
group by username,machine,program,status,osuser
order by username,machine ;

查找oracle连接客户端，找到连接oracle用户的源头进程，将其干掉，oracle用户会话session占用自然解决

SQL> select username,machine,osuser,program,status,count(machine)
2 from v$session where username='CBPDBZQ1'
3 group by username,machine,program,status,osuser
4 order by username,machine ;

USERNAME
------------------------------
MACHINE
----------------------------------------------------------------
OSUSER PROGRAM
------------------------------ ------------------------------------------------
STATUS COUNT(MACHINE)
-------- --------------
CBPDBZQ1
KDJ28901A ----------进程所在主机
cbpzq---------会话发起的进程DBAgent@KDJ28901A (TNS V1-V3) ------具体进程名
INACTIVE 6

USERNAME
------------------------------
MACHINE
----------------------------------------------------------------
OSUSER PROGRAM
------------------------------ ------------------------------------------------
STATUS COUNT(MACHINE)
-------- --------------
CBPDBZQ1
KDJ28901A
cbpzq RSIProcess@KDJ28901A (TNS V1-V3)
INACTIVE 1

USERNAME
------------------------------
MACHINE
----------------------------------------------------------------
OSUSER PROGRAM
------------------------------ ------------------------------------------------
STATUS COUNT(MACHINE)
-------- --------------
CBPDBZQ1
KDJ28901A
cbpzq SyncCache@KDJ28901A (TNS V1-V3)
INACTIVE 1

2.2 查看数据库端口号

查看数据库端口号：

在cbp中连接数据库 su – oracle

cd $ORACLE_HOME/network/admin vi tnsname.ora

2.3 远程登录数据库

远程登录数据库：sqlplus userdb/cbs/@TNS (sqlplus /用户名/密码@TNS)

2.4 数据库session被锁解决方法

如果对数据库进行了insert/update/delete操作有没有提交的话，过一段时间session会被锁

解锁：先查询v$locked_object表找到对应数据库用户的session_id，然后再查询v$session表找到更具体的信息，最后用alter system kill session语句解锁。

2.5 数据库用户被锁解决方法

以sqlplus / as sysdba 登录数据库

Select username,account_status from dba_users 看用户状态：open，locked

alter user username account unlock

2.6 普通用户如何登陆sys库

如何在linux的一个非oracle用户下，使用sys登陆呢？

就是使用如下命名登陆：sqlplus sys/syspasswd@sid as sysdba，其中syspasswd为sys用户的密码，sid为oracle的实例名

在oracle上需要做如下设置：

1、首先找到sqlnet.ora文件

在该文件中添加SQLNET.AUTHENTICATION_SERVICES=(ALL)

2、查看并修改oracle系统参数remote_login_passwordfile

在oracle下使用sqlplus / as sysdba登陆

然后执行：

show parameter remote_login_passwordfile 查看remote_login_passwordfile的值

如果remote_login_passwordfile的值不是EXCLUSIVE的话，需要修改remote_login_passwordfile的值为remote_login_passwordfile

修改方法如下：

alter system set remote_login_passwordfile=EXCLUSIVE scope=spfile;

3、查看oracle是否建立密码文件

在oracle下使用sqlplus / as sysdba登陆

然后执行：

select * from v$pwfile_users;

如果显示为空，则需要建立密码文件

4、如何建立密码文件

不需要登陆sysdba，直接在oracle的家目录下执行如下命令：

orapwd file="/oracle/app/product/11g/db/orapasswd/orapwsysdb" password=sys entries=10;

其中file的路径和文件需要大家自己指定即可，其中密码文件orapasswdysdb的组成必须为：orapasswd+SID，其中sysdba就是SID；password为sys用户的登陆鉴权密码

5、修改sys用户的密码为sys

在oracle用户下使用sqlplus / as sysdba登陆

然后执行：

alter user sys identified by sys;

6、最后一步，重启数据库

使用sqlplus / as sysdba登陆

然后执行如下两条命令：

shutdown immediate;

startup;

这样我们就可以在非oracle用户下，使用sqlplus sys/syspasswd@sid as sysdba登陆数据库系统用户了。

2.7 oracle用户下存在oracle${ORACLE_SID}进程CPU占用率100%现象

oracle用户下存在oracle${ORACLE_SID}进程CPU占用率100%

定位过程：

第一步，通过top命令，找到该进程的PID（本例中为7944）：

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
7944 oracle 25 0 2078m 581m 223m R 100 1.2 31:53.85 oracle

第二步，以oracle用户执行sqlplus / as sysdba，查询该PID对应的sql信息：

SQL> select addr from v$process where spid = 7944;

ADDR
----------------
00000000BF52A228

SQL> select sql_id from v$session where paddr ='00000000BF52A228';

SQL_ID
-------------
fyk8b9986ntk7

SQL> select sql_text from v$sql where sql_id = 'fyk8b9986ntk7';

SQL_TEXT
--------------------------------------------------------------------------------
SELECT EXECUTION_ID, STATUS, STATUS_DETAIL FROM MGMT_JOB_EXEC_SUMMARY WHERE JOB_
ID = :B3 AND TARGET_LIST_INDEX = :B2 AND EXPECTED_START_TIME = :B1

第三步，找到了占CPU为100%的sql内容，通过网上查询得知，此为oracle的一个bug：

HIGH CPU UTILIZATION AFTER INSTALLING ORACLE 11G [ID 734104.1]

修改时间 03-JAN-2011类型 PROBLEM状态 PUBLISHED

In this Document
Symptoms
Cause
Solution
References

Applies to:
Enterprise Manager Grid Control - Version: 11.1.0.6 to 11.2.0.2 - Release: 11.1 to 11.2
Information in this document applies to any platform.
Symptoms
Installed oracle 11g server that when I start the enterprise manager console using the command emctl start dbconsole. A new user shadow process start to request for a very intensive cpu usage it takes 100% of CPU.
Cause
Found that the query it's been executed by this shadow process is:

SELECT EXECUTION_ID, STATUS, STATUS_DETAIL FROM MGMT_JOB_EXEC_SUMMARY
WHERE JOB_ID = :B3 AND
TARGET_LIST_INDEX = :B2 AND EXPE
CTED_START_TIME = :B1

This query is also shown at the dbconsole as the most intensive cpu usage query.

There is a << Bug 7182962 >> being worked on this.The bug7182962is cLOS/ed as duplicate of internal unpublished bug7119851.

Solution
Following patch is available for 11.1.0.7 database control:

Patch 7119851 -SLOW DB STARTUP PERFORMANCE DUE TO SKIP QUERY RUN BY SYSMAN

Workaround:

1. Stop the dbconsole
./emctl stop dbconsole

2. Login as a sysman user and restart the provisioning daemon by running the below procedures:

SQL> execute MGMT_PAF_UTL.STOP_DAEMON
SQL> execute MGMT_PAF_UTL.START_DAEMON

3. start the dbconsole
./emctl start dbconsole

注意：em系统是oracle的一个企业管理界面，我们是用不到的，因此，执行了solution中的“emctl stop dbconsole ”和“execute MGMT_PAF_UTL.STOP_DAEMON ”步骤，而不执行剩下的启动步骤。

至此，问题解决。

2.8 数据文件误删除了解决方法

数据文件误删了，解决方法：

1)startup mount

归档模式下

alter database datafile 'D:/datafile/test.dbf' offline

非归档模式下

alter database datafile 'D:/datafile/test.dbf' offline drop

2) alter database open

2.9 调整oracle内核参数

系统内核参数

参数	建议值	文件
semmsl	250	/proc/sys/kernel/sem
semmns	32000
semopm	100
semmni	128
shmmax	1/2的内存大小	/proc/sys/kernel/shmmax
shmmni	4096	/proc/sys/kernel/shmmni
shmall	2097152	/proc/sys/kernel/shmall
file-max	512 * PROCESSES	/proc/sys/fs/file-max
ip_local_port_range	1024~65000	/proc/sys/net/ipv4/ip_local_port_range
rmem_default	262144	/proc/sys/net/core/rmem_default
rmem_max	4194304	/proc/sys/net/core/rmem_max
wmem_default	262144	/proc/sys/net/core/wmem_default
wmem_max	262144	/proc/sys/net/core/wmem_max

操作步骤

以root用户登录。

查看semmsl、semmns、semopm和semmni的值。

# /sbin/sysctl -a | grep sem

系统显示如下信息：

kernel.sem = 250 32000 100 128

查看shmall、shmmax和shmmni的值。

# /sbin/sysctl -a | grep shm

系统显示如下信息：

kernel.shmmni = 4096

kernel.shmall = 2097152

kernel.shmmax = 2147483648

查看file-max的值。

# /sbin/sysctl -a | grep file-max

系统显示如下信息：

fs.file-max = 65536

查看ip_local_port_range的值。

# /sbin/sysctl -a | grep ip_local_port_range

系统显示如下信息：

net.ipv4.ip_local_port_range = 1024 65000

查看rmem_default、rmem_max、wmem_default和wmem_max的值。

# /sbin/sysctl -a | grep net.core.rmem

# /sbin/sysctl -a | grep net.core.wmem

系统显示如下信息：

net.core.rmem_default = 262144

net.core.rmem_max = 4194304

net.core.wmem_default = 262144

net.core.wmem-default = 262144

----结束

后续处理

如果系统内核参数值不正确，修改内核参数值。

以root用户登录。

修改系统内核参数。

# vi /etc/sysctl.conf

/etc/sysctl.conf的内容如下：

kernel.shmall = 2097152

kernel.shmmax = 2147483648

kernel.shmmni = 4096

kernel.sem = 250 32000 100 128

fs.file-max = 65536

net.ipv4.ip_local_port_range =1024 65000

net.core.rmem_default = 262144

net.core.rmem_max = 4194304

net.core.wmem_default = 262144

net.core.wmem_max = 262144

同步系统内核参数到内存中，使修改生效。

# sysctl -p

以上系统内核参数的设置仅在当前有效，要使系统重启后保留设置。则需要使系统启动时优先读取/etc/sysctl.conf文件。

# /sbin/chkconfig boot.sysctl on

----结束

2.10 新建ORACLE用户时如果密码校验需要复杂密码

新建ORACLE用户时报如下错误：

SQL> create user zbacdb identified by zbacdb;

create user zbacdb identified by zbacdb

*

ERROR at line 1:

ORA-28003: password verification for the specified password failed

ORA-20001: Password length less than 8

解决方法：

sqlplus / as sysdba

alter profile default limit PASSWORD_VERIFY_FUNCTION null;

2.11 Undo表空间无法自动释放，导致数据库更新失败

修改方法：

Ø 连接数据库：

oracle@mdsp01:~> sqlplus "/as sysdba"

Ø 检查数据库是否已经配置

SQL> show parameter _smu_debug_mode;

SQL> show parameter _undo_autotune;

Ø 修改配置

SQL> alter system set "_smu_debug_mode"=33554432;
SQL> alter system set "_undo_autotune"=false;

Ø 检查数据库是否已经配置

SQL> show parameter _smu_debug_mode;

SQL> show parameter _undo_autotune;

执行完成后可以使用show parameter +参数名查看是否修改成功

1) Set _smu_debug_mode=33554432
2) Set_undo_autotune = false

2.12 怎么查看Oracle字符集是否正确

SQL> select * from nls_database_parameters where parameter=upper('NLS_CHARACTERSET');

PARAMETER------------------------------VALUE--------------------------------------------------------------------------------NLS_CHARACTERSETAL32UTF8

SQL> select userenv('language') from dual;

USERENV('LANGUAGE')----------------------------------------------------AMERICAN_AMERICA.AL32UTF8

说明：

执行上面两个命令，数据库字符集的取值都为“AL32UTF8”，表明数据库字符集设置正确。

如果数据库字符集检查不正确，请重新安装数据库。

2.13 导入数据库用户的时候报错rollback segment已经超过最大扩展了，怎么解决

报错如下：

IMP-00003: ORACLE error 604 encountered

ORA-00604: error occurred at recursive SQL level 1

ORA-01628: max # extents (32765) reached for rollback segment_SYSSMU7_1303807983$

可以使用如下命令创建：

CREATE PUBLIC ROLLBACK SEGMENT R01 TABLESPACE RBS STORAGE (initial 10M NEXT 5M OPTIMAL 20M MINEXTENTS 3 MAXEXTENTS 100);

也可以使用命令使segment变成不受限制的大小：

alter rollback segment "_SYSSMU32$" storage(maxextents unlimited);

2.14 怎么迁移oracle表空间数据的路径？how to change data file path?

我想把数据库文件迁移一下；
比如把/home/oracle/cbp_workdbs1.dbf
迁移到/oracle/dbs/cbp_workdbs1.dbf

有没有什么方法?
方法：
showdown immediate;
startup mount;
alter database rename file '/home/oracle/cbp_workdbs1.dbf' to '/oracle/dbs/cbp_workdbs1.dbf'
alter database open;
select name from v$datafile;

要保证/oracle目录下空间足够大

2.15 怎么恢复oracle数据库中的数据

数据库用户中使用如下命令：

flashback table VFIELD_DICT_EXT_S to timestamp to_timestamp('2011-09-08 19:20:00','yyyy-mm-dd hh24:mi:ss');

将VFIELD_DICT_EXT_S表数据恢复到2011年9月8日19:20:00

alter table TS_DBFIELDCATALOG enable row movement;

flashback table TS_DBFIELDCATALOG to timestamp systimestamp - interval '5' hour;

2.16 怎么解决锁表

select t1.SID,t1.SERIAL#,t1.USERNAME from v$session t1, v$locked_object t2 where t1.sid = t2.SESSION_ID;

查看锁表的用户的session，

然后用：

alter system kill session ‘SID,SERIAL’;

select object_id,session_id,locked_mode from v$locked_object;
select t2.username,t2.sid,t2.serial#,t2.logon_time
from v$locked_object t1,v$session t2
where t1.session_id=t2.sid order by t2.logon_time;

2.16 数据库被锁，使用plsql解锁

如果报Re-connecting database(hycdrdb) failed: ORA-28000: the account is locked数据库被锁，可能是oracle数据库密码被修改了，但是配置文件中没有修改，导致数据库连接不上，解决方法：去后台把密码修改掉。然后去把数据库解锁：

以system用户登录plsql；密码也是system，如果密码不对，去后台oracle修改。

右键点击被锁的数据库：

弹出来的对话框中，如果Account locked前面被钩了，说明被锁了，把钩去掉即可。

2.16 修改最大连接数

alter system set processes=500 scope=spfile;

2.16 oracle的sys用户默认密码

oracle@z106735C4c16g150g33:~> sqlplus sys as sysdba;

SQL*Plus: Release 11.1.0.7.0 - Production on Mon Jul 16 18:08:38 2012

Copyright (c) 1982, 2008, Oracle. All rights reserved.

Enter password: change_on_install

Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

或者直接：

sqlplus sys/change_on_install as sysdba

2.16 服务器oracle占用内存太大

SQL>alter system set MEMORY_TARGET=2g scope=spfile;

SQL> alter system set MEMORY_MAX_TARGET=2g scope=spfile;

SQL> alter system set SGA_MAX_SIZE=2g scope=spfile;

SQL>alter system set sga_target=2000m scope=spfile;

oracle占内存太大，通过如下方法进行调整，重启oracle之后即可

2.16 oracle监听无法停止

停止监听lsnrctl stop时报如下错误：

TNS-01189: The listener could not authenticate the user错误

解决方法：

用TNS-01189作为关键字搜索，搜到了一篇文章[ID 285439.1]，从各方面的信息来看，估计就是这个原因了。Oracle的解释是该错误是由于LSNRCTL与tnslsnr进程通信验证失败造成的，但是导致错误的原因未知（汗～～，看来又是个BUG了）。受影响的版本从10.1.0.2到11.2.0.2。当然Oracle也提供了解决方案，就是在listener.ora加入

(ADDRESS=(PROTOCOL=IPC)(KEY=ANYTHING))

但是要确保该行是在ADDRESS LIST的第一行。

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

(ADDRESS = (PROTOCOL = TCP)(HOST = ocg1011)(PORT = 1521))

)

)

红色为加入的

2.16 oracle占内存太大，通过如下方法进行调整

SQL>alter system set MEMORY_TARGET=2g scope=spfile;

SQL> alter system set MEMORY_MAX_TARGET=2g scope=spfile;

SQL> alter system set SGA_MAX_SIZE=2g scope=spfile;

SQL>alter system set sga_target=2000m scope=spfile;

oracle占内存太大，通过如下方法进行调整，重启oracle之后即可

2.16 执行sqlplus，lsnrctl报命令找不到

$ lsnrctl start

ksh: lsnrctl: not found.

应该是环境变量设置不对，找到命令的路径，然后跟环境变量对比一下，找出差异，修改下就好了

find . -name lsnrctl

还有另外一个可能，由于是新服务器，每次首次登陆都需要先source一下.profile才可以用，

解决方法：

在/etc/profile中添加：source $HOME/.profile -----$HOME是oracle用户的家目录，例如oralce用户家目录是/opt/oracle

vi /etc/profile

添加：

source /opt/oracle/.profile