一：引數檔案位置

[oracle@chen dbs]$ pwd

/u01/app/oracle/product/11.2.0/dbs

二：引數檔案優先順序

9i spfile[sid].ora>spfile.ora>init[sid].ora

11g spfile[sid].ora>init[sid].ora

測試

[oracle@chen dbs]$ ls

init.ora initorcl.ora spfileorcl.ora

SQL> show parameter spfile

NAME TYPE VALUE

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

spfile string /u01/app/oracle/product/11.2.0

/dbs/spfileorcl.ora

[oracle@chen dbs]$ mv spfileorcl.ora spfileorcl.ora.bak

SQL> startup force

SQL> show parameter spfile

NAME TYPE VALUE

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

spfile string

[oracle@chen dbs]$ mv initorcl.ora initorcl.ora.bak

引數檔案都損壞，將無法啟動例項

SQL> startup force

ORA-01078: failure in processing system parameters

LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/dbs/initorcl.ora'

三：引數檔案全部丟失恢復

恢復一：如果有備份，直接從備份中啟動資料庫

也就是其他目錄下有之前備份的pfile或spfile檔案

恢復二：如果有備份，從備份中建立引數檔案

恢復三：如果沒有備份，且資料庫是11G，資料庫是open狀態下，可以從memory獲得引數檔案

恢復四：如果沒有備份，資料庫沒有open,可以透過rman將資料庫啟動到nomount狀態

允許在沒有引數的情況下啟動資料庫例項，db_name被預設命令為DUMMY

恢復五：如果沒有備份，手動建立initorcl.ora檔案，寫入db_name='ORCL'引數，可以啟動到nomount

透過最少的引數(db_name),啟動資料庫例項,修改路徑啟動資料庫

恢復六：如果有rman備份，透過rman備份恢復

需要將資料庫啟動到nomount狀態下(手動建立initorcl.ora檔案，寫入db_name='ORCL')

恢復七：如果沒有備份，可以透過init.ora編輯建立initorcl.ora檔案

1

[oracle@chen dbs]$ mv initorcl.ora.bak /home/oracle/initorcl.ora

SQL> startup pfile='/home/oracle/initorcl.ora';

ORA-32006: LOG_ARCHIVE_START initialization parameter has been deprecated

ORACLE instance started.

Total System Global Area 1269366784 bytes

Fixed Size 2212976 bytes

Variable Size 788532112 bytes

Database Buffers 469762048 bytes

Redo Buffers 8859648 bytes

Database mounted.

Database opened.

2

SQL> create spfile from pfile='/home/oracle/initorcl.ora';

File created.

3

在以前的版本中，如果spfile引數檔案丟失，我們只能透過備份去恢復，或者從存在的pfile中建立，

實際上如果僅僅是引數檔案丟失，資料庫仍然在執行，那我們完全可以從資料庫例項中得到當前的所有執行引數，從Oracle 11g開始，一個新的命令被引入，這個命令是：

create spfile from memory;

這個命令可以使用當前的引數設定在預設位置建立一個spfile檔案，當然我們也可以指定一個不同的位置：

[oracle@chen dbs]$ rm -rf spfileorcl.ora

SQL> create spfile from memory;

create spfile from memory

*

ERROR at line 1:

ORA-32002: cannot create SPFILE already being used by the instance

SQL> create pfile from memory;

File created.

4

SQL> startup

ORA-01078: failure in processing system parameters

LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/dbs/initorcl.ora'

[oracle@chen dbs]$ rman target / nocatalog

Recovery Manager: Release 11.2.0.1.0 - Production on Sun Aug 24 00:30:17 2014

connected to target database (not started)

RMAN> startup nomount;

startup failed: ORA-01078: failure in processing system parameters

LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/dbs/initorcl.ora'

starting Oracle instance without parameter file for retrieval of spfile

Oracle instance started

Total System Global Area 158662656 bytes

Fixed Size 2211448 bytes

Variable Size 88080776 bytes

Database Buffers 62914560 bytes

Redo Buffers 5455872 bytes

RMAN> alter database mount;

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of alter db command at 08/24/2014 00:46:52

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

RMAN> exit

[oracle@chen dbs]$ cd /u01/app/oracle/diag/rdbms/dummy/orcl/trace/

[oracle@chen trace]$ vim alert_orcl.log

alter database mount

Sun Aug 24 00:46:49 2014

ORA-00210: cannot open the specified control file

ORA-00202: control file: '/u01/app/oracle/product/11.2.0/dbs/cntrlorcl.dbf'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

Sun Aug 24 00:46:49 2014

Checker run found 1 new persistent data failures

ORA-205 signalled during: alter database mount...

SQL> show parameter control_files

NAME TYPE VALUE

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

control_files string /u01/app/oracle/product/11.2.0

/dbs/cntrlorcl.dbf

SQL> show parameter db_name

NAME TYPE VALUE

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

db_name string DUMMY

[oracle@chen trace]$ cp /u01/app/oracle/oradata/orcl/control01.ctl /u01/app/oracle/product/11.2.0/dbs/cntrlorcl.dbf

SQL> alter database mount;

alter database mount

*

ERROR at line 1:

ORA-01103: database name 'ORCL' in control file is not 'DUMMY'

資料庫無法進行mount以及open;

5

SQL> startup

ORA-01078: failure in processing system parameters

LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/dbs/initorcl.ora'

[oracle@chen dbs]$ touch initorcl.ora

[oracle@chen dbs]$ echo db_name='ORCL'>initorcl.ora

SQL> startup nomount

ORACLE instance started.

Total System Global Area 217157632 bytes

Fixed Size 2211928 bytes

Variable Size 159387560 bytes

Database Buffers 50331648 bytes

Redo Buffers 5226496 bytes

SQL> alter database mount;

alter database mount

*

ERROR at line 1:

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

11.2.0.0.0

ORA-00202: control file: '/u01/app/oracle/product/11.2.0/dbs/cntrlorcl.dbf'

[oracle@chen dbs]$ cp /u01/app/oracle/oradata/orcl/control01.ctl /u01/app/oracle/product/11.2.0/dbs/cntrlorcl.dbf

SQL> alter database mount;

Database altered.

SQL> alter database open;

Database altered.

SQL> select name from v$controlfile;

NAME

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

/u01/app/oracle/product/11.2.0/dbs/cntrlorcl.dbf

[oracle@chen dbs]$ vim initorcl.ora

db_name=ORCL

6

[oracle@chen ~]$ rman target / nocatalog

...................

RMAN> backup spfile;

...................

Starting Control File and SPFILE Autobackup at 23-AUG-14

piece handle=/home/oracle/contr_bak/c-1384143564-20140823-05 comment=NONE

Finished Control File and SPFILE Autobackup at 23-AUG-14

當rman開啟自動備份控制檔案時，如果資料庫當前使用的引數檔案是spfile,那麼spfile引數檔案也會自動備份，

如果當前使用的是pfile,那麼引數檔案不會自動備份，pfile是不能透過rman命令來進行備份的，只有spfile才支援rman備份。

因為在9i和9i之前的資料庫沒有spfile,所以9i和9i之前的資料庫在自動備份控制檔案時不會備份spfile引數檔案；

[oracle@chen ~]$ cd $ORACLE_HOME/dbs

[oracle@chen dbs]$ rm -rf spfileorcl.ora*

[oracle@chen dbs]$ rm -rf initorcl.ora

SQL> startup

ORA-01078: failure in processing system parameters

LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/dbs/initorcl.ora'

報錯：沒有找到引數檔案，資料庫無法啟動，甚至無法到達nomount狀態，這個時候我們是不能使用rman的，那麼我們必須手工寫一個非常簡單的pfile，將資料庫啟動到nomount狀態下，這個時候就可以透過rman來進行對資料庫的spfile進行恢復了。

[oracle@chen dbs]$ touch initorcl.ora

[oracle@chen dbs]$ echo db_name='ORCL'>initorcl.ora

SQL> startup

ORACLE instance started.

Total System Global Area 217157632 bytes

Fixed Size 2211928 bytes

Variable Size 159387560 bytes

Database Buffers 50331648 bytes

Redo Buffers 5226496 bytes

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

SQL> select instance_name,status from v$instance;

INSTANCE_NAME STATUS

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

orcl STARTED

[oracle@chen dbs]$ rman target / nocatalog

Recovery Manager: Release 11.2.0.1.0 - Production on Sat Aug 23 23:00:20 2014

connected to target database: ORCL (not mounted)

using target database control file instead of recovery catalog

RMAN> restore spfile;

Starting restore at 23-AUG-14

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=19 device type=DISK

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of restore command at 08/23/2014 23:00:29

RMAN-06563: control file or SPFILE must be restored using FROM AUTOBACKUP

恢復的時候報錯，說控制檔案和spfile檔案的恢復必須是來自於自動備份，但是我們沒有做自動備份呀，怎麼辦，我們剛才是手工備份的引數檔案。那麼我們將用如下的指定備份路徑來進行引數檔案的恢復。

RMAN> restore spfile from '/home/oracle/contr_bak/c-1384143564-20140823-05';

Starting restore at 23-AUG-14

using channel ORA_DISK_1

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /home/oracle/contr_bak/c-1384143564-20140823-05

channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete

Finished restore at 23-AUG-14

SQL> shutdown immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

重啟資料庫，目的是讓資料庫使用新恢復出的例項

SQL> startup

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

Total System Global Area 1269366784 bytes

Fixed Size 2212976 bytes

Variable Size 788532112 bytes

Database Buffers 469762048 bytes

Redo Buffers 8859648 bytes

Database mounted.

Database opened.

7

[oracle@chen dbs]$ rm -rf initorcl.ora

[oracle@chen dbs]$ rm -rf spfileorcl.ora

SQL> startup

ORA-01078: failure in processing system parameters

LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/dbs/initorcl.ora'

[oracle@chen dbs]$ cp init.ora initorcl.ora

[oracle@chen dbs]$ unset LANG

[oracle@chen dbs]$ vim initorcl.ora

使用sqlplus時出錯

================================================================================

[oracle@chen dbs]$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Sun Aug 24 00:29:28 2014

Enter user-name: /as sysdba

ERROR:

ORA-09925: Unable to create audit trail file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 9925

ORA-09925: Unable to create audit trail file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 9925

解決思路

1.首先確定$ORACLE_BASE目錄下的adump是否存在，檔名，路徑是否正確，許可權是否正確

admin下的格式是$ORACLE_SID/adump

2.檢查是否空間adump使用的分割槽是否已經過滿

3.檢查共享記憶體段是否被stuck，在作業系統級別檢視

[oracle@chen rman0]$ ipcs -a

------ Shared Memory Segments --------

key shmid owner perms bytes nattch status

0x00000000 8486935 oracle 660 4096 0

0x00000000 8519704 oracle 660 4096 0

0xf5c3ad9c 8552473 oracle 660 4096 0

0x00000000 7995420 oracle 660 536870912 24

0x2c636768 8028190 oracle 660 2097152 24

------ Semaphore Arrays --------

key semid owner perms nsems

0x2eafb3c4 2129922 oracle 660 154

0x86e26be4 1605635 oracle 660 154

------ Message Queues --------

key msqid owner perms used-bytes messages

儘管沒有任何資料檔案，引數檔案，我們可以在系統中仍可以看到，系統確實分配了共享記憶體段和訊號量，oracle也啟動了相關程式

[oracle@tips dupl]$ ps -ef | grep ora_

.......................................

由以上資料表明，系統分配了共享記憶體段，所以即使$ORACLE_BASE目錄下有adump，許可權也正確，但是共享記憶體和訊號量已經被卡住，仍會導致使用sqlplus時ORA-09925錯誤

解決方案有兩種

1.重新系統作業系統，釋放共享記憶體段和訊號量，（重新啟動系統耗時長）

2.在作業系統級別使用ipcsrm -m ，ipcsrm -s 刪除共享記憶體段和訊號量即可

[oracle@chen rman0]$ ipcrm -m 7995420

[oracle@chen rman0]$ ipcrm -m 8028190

[oracle@chen rman0]$ ipcrm -s 2129922

[oracle@chen rman0]$ ipcrm -s 1605635

[oracle@chen rman0]$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Sun Aug 24 00:36:00 2014

Enter user-name: /as sysdba

Connected to an idle instance.

================================================================================

[oracle@chen dbs]$ vim initorcl.ora

db_name='ORCL'

memory_target=1G

processes = 150

audit_file_dest='/u01/app/oracle/admin/orcl/adump'

audit_trail ='db'

db_block_size=8192

db_domain=''

db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'

db_recovery_file_dest_size=2G

diagnostic_dest='/u01/app/oracle'

dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'

open_cursors=300

remote_login_passwordfile='EXCLUSIVE'

undo_tablespace='UNDOTBS1'

control_files = '/u01/app/oracle/oradata/orcl/control01.ctl'

compatible ='11.2.0'

主要是修改：db_name,control_files,ORACLE_BASE

SQL> startup

ORACLE instance started.

Total System Global Area 1068937216 bytes

Fixed Size 2220200 bytes

Variable Size 616566616 bytes

Database Buffers 444596224 bytes

Redo Buffers 5554176 bytes

Database mounted.

Database opened.

SQL> show parameter control_files

NAME TYPE VALUE

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

control_files string /u01/app/oracle/oradata/orcl/c

ontrol01.ctl

SQL> show parameter db_name

NAME TYPE VALUE

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

db_name string ORCL

歡迎關注我的微信公眾號"IT小Chen"，共同學習，共同成長！！！

引數檔案

引數檔案

相關文章