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@]