一：参数文件位置
[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
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
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
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
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
Copyright (c) 1982, 2009, Oracle. All rights reserved.
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
Copyright (c) 1982, 2009, Oracle. All rights reserved.
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
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29785807/viewspace-1308265/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/29785807/viewspace-1308265/
2053
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
