本文测试了误删除spfile,pfile,init.ora等文件后的恢复方法,考虑多种场景,在不同场景下进行参数文件恢复。
第一步:连上数据库,查看spfile文件所在路径
-
[oracle@ora11g ~]$ sqlplus / as sysdba
-
-
SQL*Plus: Release 11.2.0.4.0 Production on Wed Aug 2 11:37:08 2017
-
-
Copyright (c) 1982, 2013, Oracle. All rights reserved.
-
-
Connected to an idle instance.
-
-
SYS@cams>startup;
-
ORACLE instance started.
-
-
Total System Global Area 776646656 bytes
-
Fixed Size 2257272 bytes
-
Variable Size 478154376 bytes
-
Database Buffers 289406976 bytes
-
Redo Buffers 6828032 bytes
-
Database mounted.
-
Database opened.
-
SYS@cams>show parameter pfile;
-
-
NAME TYPE VALUE
-
------------------------------------ ----------- ------------------------------
-
spfile string /u01/app/oracle/product/11.2.0
-
/db_1/dbs/spfilecams.ora
第二步:查看参数文件路径下文件信息
-
[oracle@ora11g ~]$ cd $ORACLE_HOME/dbs
-
[oracle@ora11g dbs]$ ls
-
hc_cams.dat init.ora lkCAMS orapwcams spfilecams.ora
第三步:为了便于测试,这里创建一个pfile文件
-
SYS@cams>create pfile from spfile;
-
-
File created.
查看新创建的pfile文件
-
[oracle@ora11g dbs]$ ls
-
hc_cams.dat initcams.ora init.ora lkCAMS orapwcams spfilecams.ora
查看每个参数文件的内容
第四步:模拟参数文件被误删除
-
[oracle@ora11g dbs]$ mkdir backup
-
[oracle@ora11g dbs]$ mv initcams.ora init.ora spfilecams.ora backup/
-
[oracle@ora11g dbs]$ ls
-
backup hc_cams.dat lkCAMS orapwcams
-
[oracle@ora11g dbs]$ ls backup/
-
initcams.ora init.ora spfilecams.ora
第五步:检查数据库是否还能正常工作
-
SYS@cams>select name,open_mode from v$database;
-
-
NAME OPEN_MODE
-
--------- --------------------
-
CAMS READ WRITE
显然,现在数据库是可以正常工作的,因为数据库启动过程中已经将spfile参数文件的信息读到内存中。
第六步:这里模拟在数据库运行时,及时发现参数文件被误删除,进行恢复。
这里需要用到Oracle11gR2的新特性,对于Oracle官方文档的路径为:
Home / Database / Oracle Database Online Documentation 11g?Release 2 (11.2) / Database Administration/SQL Language Reference/What's New in the SQL Language Reference?
可以找到
CREATE PFILE has new syntax that lets you create a parameter file from current system-wide parameter settings.
CREATE SPFILE has new syntax that lets you create a system parameter file from current system-wide parameter settings.
点击create pfile或者create spfile进入链接页面,可以找到SQL命令:
CREATE PFILE [= 'pfile_name' ]
FROM { SPFILE [= 'spfile_name']
| MEMORY
} ;
CREATE SPFILE [= 'spfile_name' ]
FROM { PFILE [= 'pfile_name' ]
| MEMORY
} ;
这里执行恢复语句:
-
SYS@cams>create spfile from memory;
-
create spfile from memory
-
*
-
ERROR at line 1:
-
ORA-32002: cannot create SPFILE already being used by the instance
-
-
-
SYS@cams>create pfile from memory;
-
-
File created.
-
-
SYS@cams>create spfile='spfilecams1.ora' from memory;
-
-
File created.
查看恢复后的spfile和pfile文件:
第七步:重启数据库,检查恢复后的参数文件能否正常使用,并进行分析
-
SYS@cams>shutdown immediate;
-
Database closed.
-
Database dismounted.
-
ORACLE instance shut down.
-
SYS@cams>host mv /u01/app/oracle/product/11.2.0/db_1/dbs/spfilecams1.ora /u01/app/oracle/product/11.2.0/db_1/dbs/spfilecams.ora
-
-
SYS@cams>startup;
-
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
-
ORACLE instance started.
-
-
Total System Global Area 776646656 bytes
-
Fixed Size 2257272 bytes
-
Variable Size 482348680 bytes
-
Database Buffers 285212672 bytes
-
Redo Buffers 6828032 bytes
-
Database mounted.
-
Database opened.
这里出现ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance的问题,是因为background_dump_dest和user_dump_dest两个参数在11gR1中废弃了,在alert日志中可以看到明确提示:
对于两个废弃参数信息,可以查看官方文档
Home / Database / Oracle Database Online Documentation 11g?Release 1 (11.1) / Database Administration/Reference/1?Initialization Parameters
在BACKGROUND_DUMP_DEST目录下看到:
Note:
This parameter is ignored by the new diagnosability infrastructure introduced in Oracle Database 11g Release 1, which places trace and core files in a location controlled by the DIAGNOSTIC_DEST initialization parameter.
在USER_DUMP_DEST目录下看到:
Note:
This parameter is ignored by the new diagnosability infrastructure introduced in Oracle Database 11g Release 1, which places trace and core files in a location controlled by the DIAGNOSTIC_DEST initialization parameter.
第八步:调整pfile参数文件,并创建spfile文件后启动
打开pfile文件,可以看到两个弃用的参数后面都有注释“#Deprecate parameter”
将两个废弃的参数注释,然后生成spfile文件后启动
-
SYS@cams>create spfile from pfile;
-
-
File created.
-
-
SYS@cams>startup;
-
ORACLE instance started.
-
-
Total System Global Area 776646656 bytes
-
Fixed Size 2257272 bytes
-
Variable Size 482348680 bytes
-
Database Buffers 285212672 bytes
-
Redo Buffers 6828032 bytes
-
Database mounted.
-
Database opened.
第九步:模拟在第六步的时候未能及时发现参数文件被误删除,然后数据库关闭了,启动的时候报错。
-
[oracle@ora11g dbs]$ ls
-
backup hc_cams.dat initcams.ora lkCAMS orapwcams spfilecams.ora
-
[oracle@ora11g dbs]$ mkdir backup1
-
[oracle@ora11g dbs]$ mv initcams.ora spfilecams.ora backup1/
-
[oracle@ora11g dbs]$ ls backup1/
-
initcams.ora spfilecams.ora
-
[oracle@ora11g dbs]$ ls
-
backup backup1 hc_cams.dat lkCAMS orapwcams
-
SYS@cams>shutdown immediate;
-
Database closed.
-
Database dismounted.
-
ORACLE instance shut down.
-
SYS@cams>startup;
-
ORA-01078: failure in processing system parameters
-
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/db_1/dbs/initcams.ora'
第十步:找到alert日志,通过alert日志中的启动信息恢复pfile参数文件。
-
[oracle@ora11g dbs]$ cd /u01/app/oracle/diag/rdbms/cams/cams/trace/
-
[oracle@ora11g trace]$ ls | grep alert
-
alert_cams.log
找到最近几次成功的日志信息,选取其中正确无误的一条日志信息:
创建pfile文件initcams.ora,将alert日志中的参数信息填入:
-
[oracle@ora11g trace]$ cd $ORACLE_HOME/dbs
-
[oracle@ora11g dbs]$ ls
-
backup backup1 hc_cams.dat lkCAMS orapwcams
-
[oracle@ora11g dbs]$ vi initcams.ora
-
[oracle@ora11g dbs]$ cat initcams.ora
-
processes = 150
-
memory_target = 744M
-
control_files = "/u01/app/oracle/oradata/cams/control01.ctl"
-
control_files = "/u01/app/oracle/fast_recovery_area/cams/control02.ctl"
-
db_block_size = 8192
-
compatible = "11.2.0.4.0"
-
db_recovery_file_dest = "/u01/app/oracle/fast_recovery_area"
-
db_recovery_file_dest_size= 4182M
-
undo_tablespace = "UNDOTBS1"
-
remote_login_passwordfile= "EXCLUSIVE"
-
db_domain = ""
-
dispatchers = "(PROTOCOL=TCP) (SERVICE=camsXDB)"
-
job_queue_processes = 1000
-
audit_file_dest = "/u01/app/oracle/admin/cams/adump"
-
audit_trail = "DB"
-
db_name = "cams"
-
open_cursors = 300
-
diagnostic_dest = "/u01/app/oracle"
直接使用pfile文件启动数据库:
-
SYS@cams>startup pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initcams.ora';
-
ORACLE instance started.
-
-
Total System Global Area 776646656 bytes
-
Fixed Size 2257272 bytes
-
Variable Size 511708808 bytes
-
Database Buffers 255852544 bytes
-
Redo Buffers 6828032 bytes
-
Database mounted.
-
Database opened.
第十一步:这里假设第十步的alert日志中没找到参数信息,需要进行恢复,假设init.ora还能找到。
-
[oracle@ora11g backup]$ ls
-
initcams.ora init.ora spfilecams.ora
-
[oracle@ora11g backup]$ cat init.ora | grep -v ^# | grep -v ^$ > initcams.ora
-
[oracle@ora11g backup]$ cat initcams.ora
-
db_name='ORCL'
-
memory_target=1G
-
processes = 150
-
audit_file_dest='<ORACLE_BASE>/admin/orcl/adump'
-
audit_trail ='db'
-
db_block_size=8192
-
db_domain=''
-
db_recovery_file_dest='<ORACLE_BASE>/flash_recovery_area'
-
db_recovery_file_dest_size=2G
-
diagnostic_dest='<ORACLE_BASE>'
-
dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
-
open_cursors=300
-
remote_login_passwordfile='EXCLUSIVE'
-
undo_tablespace='UNDOTBS1'
-
control_files = (ora_control1, ora_control2)
-
compatible ='11.2.0'
然后根据实际环境情况修改initcams.ora,启动数据库,不过可能会出现部分参数的值与原数据库不一致,需要DBA进行调整。
第十二步:这里假设第十步的alert日志中没找到参数信息,需要进行恢复,假设init.ora不能找到。
-
[oracle@ora11g dbs]$ vi initcams.ora
-
[oracle@ora11g dbs]$ cat initcams.ora
-
db_name='cams'
使用pfile启动数据库:
-
SYS@cams>shutdown immediate;
-
Database closed.
-
Database dismounted.
-
ORACLE instance shut down.
-
SYS@cams>startup pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initcams.ora';
-
ORACLE instance started.
-
-
Total System Global Area 263090176 bytes
-
Fixed Size 2252256 bytes
-
Variable Size 205521440 bytes
-
Database Buffers 50331648 bytes
-
Redo Buffers 4984832 bytes
-
ORA-00205: error in identifying control file, check alert log for more info
检查alert日志
-
[oracle@ora11g trace]$ tail -n 20 alert_cams.log
-
CKPT started with pid=12, OS id=5722
-
Wed Aug 02 14:38:15 2017
-
SMON started with pid=13, OS id=5724
-
Wed Aug 02 14:38:15 2017
-
RECO started with pid=14, OS id=5726
-
Wed Aug 02 14:38:15 2017
-
MMON started with pid=15, OS id=5728
-
Wed Aug 02 14:38:15 2017
-
MMNL started with pid=16, OS id=5730
-
ORACLE_BASE from environment = /u01/app/oracle
-
Wed Aug 02 14:38:15 2017
-
ALTER DATABASE MOUNT
-
ORA-00210: cannot open the specified control file
-
ORA-00202: control file: '/u01/app/oracle/product/11.2.0/db_1/dbs/cntrlcams.dbf'
-
ORA-27037: unable to obtain file status
-
Linux-x86_64 Error: 2: No such file or directory
-
Additional information: 3
-
ORA-205 signalled during: ALTER DATABASE MOUNT...
-
Wed Aug 02 14:38:15 2017
-
Checker run found 1 new persistent data failures
修改pfile文件,指定control_files参数(如果真的忘了,可以用linux命令查找)
-
[oracle@ora11g dbs]$ find $ORACLE_BASE -name control*
-
/u01/app/oracle/product/11.2.0/db_1/oc4j/j2ee/oc4j_applications/applications/em/em/images/database/storage/controlfile.gif
-
/u01/app/oracle/product/11.2.0/db_1/apex/images/fck/editor/dialog/fck_spellerpages/spellerpages/controlWindow.js
-
/u01/app/oracle/product/11.2.0/db_1/apex/images/fck/editor/dialog/fck_spellerpages/spellerpages/controls.html
-
/u01/app/oracle/fast_recovery_area/cams/control02.ctl
-
/u01/app/oracle/oradata/cams/control01.ctl
-
[oracle@ora11g dbs]$ vi initcams.ora
-
[oracle@ora11g dbs]$ cat initcams.ora
-
db_name='cams'
-
control_files='/u01/app/oracle/oradata/cams/control01.ctl','/u01/app/oracle/fast_recovery_area/cams/control02.ctl'
再次使用pfile启动数据库
-
SYS@cams>shutdown immediate;
-
ORA-01507: database not mounted
-
-
-
ORACLE instance shut down.
-
SYS@cams>startup;
-
ORACLE instance started.
-
-
Total System Global Area 263090176 bytes
-
Fixed Size 2252256 bytes
-
Variable Size 205521440 bytes
-
Database Buffers 50331648 bytes
-
Redo Buffers 4984832 bytes
-
ORA-00201: control file version 11.2.0.4.0 incompatible with ORACLE version
-
11.2.0.0.0
-
ORA-00202: control file: '/u01/app/oracle/oradata/cams/control01.ctl'
查看错误信息
-
[oracle@ora11g dbs]$ oerr ORA 00201
-
00201, 00000, "control file version %s incompatible with ORACLE version %s"
-
// *Cause: The control file was created by incompatible software.
-
// *Action: Either restart with a compatible software release or use
-
// CREATE CONTROLFILE to create a new control file that is
-
// compatible with this release.
这里需要在参数文件配置一个compatible参数
-
[oracle@ora11g dbs]$ vi initcams.ora
-
[oracle@ora11g dbs]$ cat initcams.ora
-
db_name='cams'
-
control_files='/u01/app/oracle/oradata/cams/control01.ctl','/u01/app/oracle/fast_recovery_area/cams/control02.ctl'
-
compatible="11.2.0.4.0"
再次使用pfile启动数据库
-
SYS@cams>shutdown immediate;
-
ORA-01507: database not mounted
-
-
-
ORACLE instance shut down.
-
SYS@cams>startup;
-
ORACLE instance started.
-
-
Total System Global Area 263090176 bytes
-
Fixed Size 2252256 bytes
-
Variable Size 205521440 bytes
-
Database Buffers 50331648 bytes
-
Redo Buffers 4984832 bytes
-
Database mounted.
-
Database opened.
数据库启动成功。同时,我们也从测试过程中知道,参数文件至少需要配置db_name,control_files和compatible等3个参数信息,可以让数据库成功启动。不过启动之后也需要DBA对数据库参数进行调整。