Oracle 实例启动必须的参数分析(2010-05-06)

本文分析下，一个Oracle实例启动至少需要几个参数。

Oracle10g以后实例启动默认的是spfile文件，当没有spfile文件的时候才去使用pfile文件。

现在我们来看pfile和spfile文件丢失的情况下怎么来重建。

1） alert*.log日志中查询启动过的记录；

2） vi pfile文件，写入一些最基本的参数来启动数据，把库先拉起来，再做调整；

举例：

必须参数db_name

[oracle@dbserver dbs]$ more initlctx.ora

*.db_name='lctx'

SQL> startup pfile='/u01/app/oracle/ora10g/dbs/initlctx.ora';

ORACLE instance started.

Total System Global Area 113246208 bytes

Fixed Size 1218004 bytes

Variable Size 58722860 bytes

Database Buffers 50331648 bytes

Redo Buffers 2973696 bytes

ORA-00205: error in identifying control file, check alert log for more info

SQL>

分析：这个时候数据库仅加载了内存，还没有nomount成功。

必须参数control_files

[oracle@dbserver dbs]$ more initlctx.ora

*.db_name='lctx'

*.control_files='/u01/app/oracle/oradata/lctx/control01.ctl','/u01/app/oracle/oradata/lctx/control02.ctl','/u01/app/oracle/oradata/l

ctx/control03.ctl'

[oracle@dbserver dbs]$ [oracle@dbserver dbs]$

SQL> startup pfile='/u01/app/oracle/ora10g/dbs/initlctx.ora';

ORACLE instance started.

Total System Global Area 113246208 bytes

Fixed Size 1218004 bytes

Variable Size 58722860 bytes

Database Buffers 50331648 bytes

Redo Buffers 2973696 bytes

ORA-00201: control file version 10.2.0.1.0 incompatible with ORACLE version

10.2.0.0.0

ORA-00202: control file: '/u01/app/oracle/oradata/lctx/control01.ctl'

分析：incompatible参数是必须的，没有nomount成功，说明参数文件有问题。

必须参数 incompatible

[oracle@dbserver dbs]$ more initlctx.ora

*.db_name='lctx'

*.control_files='/u01/app/oracle/oradata/lctx/control01.ctl','/u01/app/oracle/oradata/lctx/control02.ctl','/u01/app/oracle/oradata/l

ctx/control03.ctl'

*.compatible='10.2.0.1.0'

[oracle@dbserver dbs]$

SQL> startup pfile='/u01/app/oracle/ora10g/dbs/initlctx.ora';

ORACLE instance started.

Total System Global Area 113246208 bytes

Fixed Size 1218004 bytes

Variable Size 58722860 bytes

Database Buffers 50331648 bytes

Redo Buffers 2973696 bytes

Database mounted.

ORA-01092: ORACLE instance terminated. Disconnection forced

SQL>

分析：数据库实例被中断，这个时候操作系统已经没有了ora进程；

SQL> conn / as sysdba;

Connected to an idle instance.

SQL> startup nomount pfile='/u01/app/oracle/ora10g/dbs/initlctx.ora';

ORACLE instance started.

Total System Global Area 113246208 bytes

Fixed Size 1218004 bytes

Variable Size 58722860 bytes

Database Buffers 50331648 bytes

Redo Buffers 2973696 bytes

SQL>

SQL> alter database mount;

Database altered.

SQL>

SQL> select open_mode from v$database;

OPEN_MODE

----------

MOUNTED

SQL>

分析：可见如果只是让oracle实例挂载到mount状态下，只需要以上三个参数就够了！

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01092: ORACLE instance terminated. Disconnection forced

分析：

Thu May 6 02:44:22 2010

alter database open

Thu May 6 02:44:22 2010

Beginning crash recovery of 1 threads

Thu May 6 02:44:22 2010

Started redo scan

Thu May 6 02:44:22 2010

Completed redo scan

3 redo blocks read, 5 data blocks need recovery

Thu May 6 02:44:22 2010

Started redo application at

Thread 1: logseq 50, block 3

Thu May 6 02:44:22 2010

Recovery of Online Redo Log: Thread 1 Group 1 Seq 50 Reading mem 0

Mem# 0 errs 0: /u01/app/oracle/oradata/lctx/redo01.log

Mem# 1 errs 0: /u01/app/oracle/oradata/lctx/redo01_2.log

Thu May 6 02:44:22 2010

Completed redo application

Thu May 6 02:44:22 2010

Completed crash recovery at

Thread 1: logseq 50, block 6, scn 1478656

5 data blocks read, 5 data blocks written, 3 redo blocks read

Thu May 6 02:44:22 2010

LGWR: STARTING ARCH PROCESSES

ARC0 started with pid=13, OS id=2028

Thu May 6 02:44:22 2010

ARC0: Archival started

ARC1: Archival started

LGWR: STARTING ARCH PROCESSES COMPLETE

ARC1 started with pid=14, OS id=2030

Thu May 6 02:44:22 2010

Thread 1 advanced to log sequence 51

Thu May 6 02:44:22 2010

ARC0: STARTING ARCH PROCESSES

Thu May 6 02:44:22 2010

Thread 1 opened at log sequence 51

Current log# 3 seq# 51 mem# 0: /u01/app/oracle/oradata/lctx/redo03.log

Successful open of redo thread 1

Thu May 6 02:44:22 2010

MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set

Thu May 6 02:44:22 2010

SMON: enabling cache recovery

SMON: enabling tx recovery

Thu May 6 02:44:23 2010

ARC1: Becoming the 'no FAL' ARCH

ARC1: Becoming the 'no SRL' ARCH

Thu May 6 02:44:23 2010

Database Characterset is ZHS16GBK

Thu May 6 02:44:23 2010

ARC2: Archival started

ARC0: STARTING ARCH PROCESSES COMPLETE

ARC0: Becoming the heartbeat ARCH

ARC2 started with pid=15, OS id=2032

Thu May 6 02:44:23 2010

Errors in file /u01/app/oracle/admin/lctx/udump/lctx_ora_2023.trc:

ORA-04031: unable to allocate 4116 bytes of shared memory ("shared pool","select i.obj#,i.ts#,i.file#,...","Typecheck","seg:kggfaAllocSeg")

Thu May 6 02:44:23 2010

Error 4031 happened during db open, shutting down database

USER: terminating instance due to error 4031

Instance terminated by USER, pid = 2023

ORA-1092 signalled during: alter database open...

必须参数

shared_pool_size=67108864

db_cache_size=88080384

[oracle@dbserver dbs]$ cat initlctx.ora

db_name='lctx'

control_files='/u01/app/oracle/oradata/lctx/control01.ctl','/u01/app/oracle/oradata/lctx/control02.ctl','/u01/app/oracle/oradata/lctx/control03.ctl'

compatible='10.2.0.1.0'

shared_pool_size=67108864

db_cache_size=88080384

SQL> startup mount pfile='/u01/app/oracle/ora10g/dbs/initlctx.ora';

ORACLE instance started.

Total System Global Area 184549376 bytes

Fixed Size 1218412 bytes

Variable Size 92276884 bytes

Database Buffers 88080384 bytes

Redo Buffers 2973696 bytes

Database mounted.

SQL> alter database open;

Database altered.

SQL>

SQL> show parameter dest

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

audit_file_dest string /u01/app/oracle/admin/lctx/adump

background_dump_dest string /u01/app/oracle/admin/lctx/bdump

core_dump_dest string /u01/app/oracle/admin/lctx/cdump

user_dump_dest string /u01/app/oracle/admin/lctx/udump

….

说明：以上几个dump文件默认位置$ORACLE_BASE/admin/$DB_NAME/*dump 如果默认有这几个目录可以不设置，但是如果没有，启动会报错。、

如：

SQL> startup mount pfile='/u01/app/oracle/ora10g/dbs/initlctx.ora';

ORA-09925: Unable to create audit trail file

Linux Error: 2: No such file or directory

Additional information: 9925

总结：Oracle实例启动必须的参数如下：

db_name=

control_files=

incompatible=

shared_pool_size=

db_cache_size=

audit_file_dest=

background_dump_dest

core_dump_dest=

user_dump_dest=

除了以上参数，还有些默认参数，如果数据库实例不是使用默认参数，需要自行设置，具体可以参考以前的alert.log记录!

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/60144/viewspace-1033420/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/60144/viewspace-1033420/