1.1.1 手动杀死Oracle进程引起的问题
Oracle无法停止也无法启动,ps –ef|grep ora 也查询不到Oracle进程:
<2 vca1 [oracle] : /home/oracle >ps -ef|grep ora
oracle 876704 893374 0 21:09:42 pts/5 0:00 grep ora
oracle 884744 385122 0 20:51:59 pts/1 0:00 -csh
oracle 344494 892962 0 21:01:17 pts/14 0:00 -csh
oracle 364866 893374 0 21:09:42 pts/5 0:00 ps -ef
oracle 835880 856070 0 21:00:25 pts/8 0:00 -csh
oracle 893374 790586 0 21:09:28 pts/5 0:00 -csh
启动的时候报错:
<5 vca1 [oracle] : /home/oracle >sqlplus "/as sysdba"
SQL*Plus: Release 9.2.0.6.0 - Production on Thu Nov 12 21:10:22 2009
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected.
SQL> startup
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> shutdown immediate
ORA-24324: service handle not initialized
ORA-24323: value not allowed
ORA-01089: immediate shutdown in progress - no operations are permitted
SQL> quit
Disconnected
分析后,应该是谁手动杀死了Oracle进程,而对应的共享内存没有释放导致:
<6 vca1 [oracle] : /home/oracle >ps -ef|oracle
<7 vca1 [oracle] : /home/oracle >ps -ef|oracle
<8 vca1 [oracle] : /home/oracle >ipcs -m|grep oracle
m 1048579 0xbc1f2cac --rw-r----- oracle dba
ipcrm -m 1048579
1.1.2 ORA-00020 超过最大连接数
尝试sqlplus连接数据库,抱错:
BMP# sqlplus sysdb/sysdb@ora11g
SQL*Plus: Release 9.2.0.7.0 - Production on Sun Jan 31 21:00:42 2010
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
ERROR:
ORA-00020: maximum number of processes (%s) exceeded
首先察看Oracle错误描述:
[1] % oerr ora 00020
00020, 00000, "maximum number of processes (%s) exceeded"
// *Cause: All process state objects are in use.
// *Action: Increase the value of the PROCESSES initialization parameter.
建议是修改PROCESSES进程个数空置参数;
使用oracle的sysdba登录察看,并修改为合适进程个数;
[2] % sqlplus / as sysdba
SQL*Plus: Release 11.1.0.6.0 - Production on Tue Feb 2 18:41:21 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> show parameter processes
NAME TYPE VALUE
------------------------------------ -----------
aq_tm_processes integer 0
db_writer_processes integer 1
gcs_server_processes integer 0
global_txn_processes integer 1
job_queue_processes integer 1000
log_archive_max_processes integer 2
processes integer 300
SQL>alter system set processes=500 scope=spfile;
1.1.3 ORA-28002 密码过期警告
[问题描述]
当用户出现提示密码将要过期的错误ORA-28002时如何处理?
ocs102 /ora/oracle> sqlplus cntsdb/test
SQL*Plus: Release 11.1.0.6.0 - Production on Tue Jan 19 15:15:50 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
ERROR:
ORA-28002: the password will expire within 302 days
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
[问题处理]
因为设置了密码过期时间,所以才会有密码过期提醒,如何查看和修改密码过期无限制呢?
步骤 1 登陆数据库,查看用户的proifle文件名称,一般是default:
执行如下操作:
SQL> SELECT username,PROFILE FROM dba_users where USERNAME=upper('cntsdb');
USERNAME PROFILE
------------------------------ ------------------------------
CNTSDB DEFAULT
步骤 2 查看指定概要文件(如default)的密码有效期设置:
SQL> select LIMIT from dba_profiles s where s.profile='DEFAULT' and resource_name='PASSWORD_LIFE_TIME';
LIMIT
----------------------------------------
180
步骤 3 将密码有效期由默认的180天修改成“无限制”:
SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
Profile altered.
SQL> select LIMIT from dba_profiles s where s.profile='DEFAULT' and resource_name='PASSWORD_LIFE_TIME';
LIMIT
----------------------------------------
UNLIMITED
步骤 4 修改后,还没有被提示ORA-28002警告的用户不会再碰到同样的提示;已经被提示的用户必须再改一次密码,举例如下:
SQL> alter user <user> identified by <original_password>
----注:不用换新密码
Example: SQL> alter user cntsdb identified by test;
User altered.
注: 如果像上面问题描述中显示信息,用户已经出现了 ORA-28002警告,在修改了用户密码为UNLIMITED之后,必须执行第四步,重新修改一下用户密码(密码可以保留与原来相同),否则仍然报:ORA-28002警告;
1.1.4 如何关闭监听listener.log日志
[问题描述]
Oracle的监听(Listener)在缺省情况下,会在文件中记录日志,记录数据库实例注册操作、客户端的连接等。缺省(没有设置log_file参数时)的文件是$ORACLE_HOME/network/log/listener.log。对于一些使用短连接的,频繁的连接数据库的应用,listener.log增长很快。有的可以在比较短的时间内(十几天)就可以超过2GB。对于一些平台的某些版本的Oracle,在监听日志增大到2GB以后会导致监听不能正常工作.
[解决方法]
对于这种listener.log增长非常迅速的系统,可以关闭监听日志,不让监听写日志到文件。也可以写个job定期清理。
[27] % lsnrctl
LSNRCTL for HPUX: Version 11.1.0.6.0 - Production on 17-NOV-2010 19:12:10
Copyright (c) 1991, 2007, Oracle. All rights reserved.
Welcome to LSNRCTL, type "help" for information.
LSNRCTL> status
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for HPUX: Version 11.1.0.6.0 - Production
Start Date 09-OCT-2010 15:32:48
Uptime 39 days 3 hr. 40 min. 34 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/db/product/11.1.0/db_1/network/admin/listener.ora
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hpa001)(PORT=1521)))
Services Summary...
Service "ora11g" has 1 instance(s).
Instance "ora11g", status READY, has 1 handler(s) for this service...
Service "ora11gXDB" has 1 instance(s).
Instance "ora11g", status READY, has 1 handler(s) for this service...
Service "ora11g_XPT" has 1 instance(s).
Instance "ora11g", status READY, has 1 handler(s) for this service...
The command completed successfully
LSNRCTL> set log_status off
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
LISTENER parameter "log_status" set to OFF
LSNRCTL> show
The following operations are available after show
An asterisk (*) denotes a modifier or extended command:
rawmode displaymode
rules trc_file
trc_directory trc_level
log_file log_directory
log_status current_listener
inbound_connect_timeout startup_waittime
snmp_visible save_config_on_stop
dynamic_registration
LSNRCTL> help
The following operations are available
An asterisk (*) denotes a modifier or extended command:
start stop status
services version reload
save_config trace spawn
change_password quit exit
set* show*
LSNRCTL> save_config
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
Saved LISTENER configuration parameters.
Listener Parameter File /oracle/db/product/11.1.0/db_1/network/admin/listener.ora
Old Parameter File /oracle/db/product/11.1.0/db_1/network/admin/listener.bak
The command completed successfully
LSNRCTL> show log_status
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
LISTENER parameter "log_status" set to OFF
The command completed successfully
LSNRCTL> quit