FAQ of oracle 7

来源：互联网发布：软件破解vip视频教程编辑：程序博客网时间：2024/06/10 12:16

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

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

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

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

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

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