Oracle 基于备份控制文件的恢复(unsing backup controlfile)
Oracle 基于备份控制文件的恢复(unsing backup controlfile)
通常在当前控制文件丢失，或者当前的控制文件与需要恢复的控制文件不一致的情况下，我们需要重新创建一个控制文件或者使用 unsing
backup controlfile方式来恢复控制文件。说简单点，只要是备份的控制文件与当前的控制文件不一致进行恢复数据库，就需要使用到 unsing
backup controlfile方式，而一旦使用了该方式，则需使用resetlgos选项来打开数据库。
有关控制文件的介绍请参考： Oracle 控制文件(CONTROL FILE)
一、基于备份控制文件的恢复注意事项(无论是否使用恢复目录catalog)
1、即使没有数据文件需要还原，当使用unsing backup controlfile 方式时必须结合 recover 命令
2、不论使用备份的控制文件进行时点恢复或完全恢复，必须使用 open resetlogs 方式打开数据库
3、如果联机日志不可访问，必须使用不完全恢复到联机日志文件中最早的一个SCN之前。这是因为RMAN并不备份联机日志文件
4、在恢复期间，RMAN自动搜索联机日志和没有记录在RMAN存储仓库中的归档日志以完成恢复
5、RMAN会根据初始化参数文件中归档位置以及控制文件联机日志信息自动寻找有效的归档日志和联机日志。使用unsing backup controlfile方
式时，在恢复期间，一旦归档目的地以及归档格式发生变化，或添加新的联机日志成员将收到RMAN-06054错误信息。
本文主要使用热备方式来完成演示
二、演示unsing backup controlfile的使用
1、控制文件全部丢失的情形(控制文件备份后发生变化)
- -->首先使用热备脚本进行备份
- sys@SYBO2SZ> get db_hot_bak.sql
- 1 set feedback off heading off verify off
- 2 set pagesize 0 linesize 200
- 3 define dir='/u02/database/SYBO2SZ/backup/hotbak'
- 4 define script='/tmp/tmphotbak.sql'
- 5 spool &script
- 6 select 'ho cp '||name||' &dir' from v$datafile;
- 7 spool off
- 8 alter database begin backup;
- 9 start &script
- 10 alter database end backup;
- 11 alter database backup controlfile to '&dir/contlbak.ctl' reuse;
- 12 create pfile='&dir/initSYBO2SZ.ora' from spfile;
- 13* set feedback on heading on verify on pagesize 100
- sys@SYBO2SZ> @db_hot_bak
- sys@SYBO2SZ> show parameter control_files
- NAME TYPE VALUE
- ------------------------------------ ----------- ------------------------------
- control_files string /u02/database/SYBO2SZ/controlf
- /cntl1SYBO2SZ.ctl, /u02/databa
- se/SYBO2SZ/controlf/cntl2SYBO2
- SZ.ctl, /u02/database/SYBO2SZ/
- controlf/cntl3SYBO2SZ.ctl
- -->为数据库添加新的表空间，此时控制文件将不同于先前备份的控制文件
- sys@SYBO2SZ> create tablespace tbs datafile '/u02/database/SYBO2SZ/oradata/tbs_tmp.dbf' size 10m autoextend on;
- Tablespace created.
- -->为数据库添加对象
- sys@SYBO2SZ> create table tb_emp tablespace tbs as select * from scott.emp ;
- Table created.
- sys@SYBO2SZ> select count(*) from tb_emp;
- COUNT(*)
- ----------
- 14
- sys@SYBO2SZ> select * from v$log;
- GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
- 3 1 116 20971520 2 NO CURRENT 1078066 08-SEP-12
- 4 1 115 20971520 2 YES INACTIVE 1063428 08-SEP-12
- sys@SYBO2SZ> select to_char(sysdate,'yyyymmdd hh24:mi:ss') from dual;
- TO_CHAR(SYSDATE,
- -----------------
- 20120908 16:30:41
- -->切换日志
- sys@SYBO2SZ> alter system archive log current;
- System altered.
- -->删除部分记录用于恢复后验证
- sys@SYBO2SZ> delete from tb_emp where deptno=10;
- 3 rows deleted.
- sys@SYBO2SZ> commit;
- Commit complete.
- sys@SYBO2SZ> alter system archive log current;
- System altered.
- sys@SYBO2SZ> ho ls -hltr /u02/database/SYBO2SZ/archive
- total 143M
- -rw-r----- 1 oracle oinstall 15M 2012-09-08 16:20 arch_792094299_1_115.arc
- -rw-r----- 1 oracle oinstall 236K 2012-09-08 16:30 arch_792094299_1_116.arc
- -rw-r----- 1 oracle oinstall 9.0K 2012-09-08 16:32 arch_792094299_1_117.arc
- -->异常关机
- sys@SYBO2SZ> shutdown abort;
- ORACLE instance shut down.
- -->模拟所有控制文件丢失
- sys@SYBO2SZ> ho rm -rf /u02/database/SYBO2SZ/controlf/*
- sys@SYBO2SZ> ho ls /u02/database/SYBO2SZ/controlf/
- -->启动后收到ORA-00205错误
- sys@SYBO2SZ> startup
- ORACLE instance started.
- Total System Global Area 599785472 bytes
- Fixed Size 2074568 bytes
- Variable Size 276826168 bytes
- Database Buffers 314572800 bytes
- Redo Buffers 6311936 bytes
- ORA-00205: error in identifying control file, check alert log for more info
- sys@SYBO2SZ> select instance_name,status from v$instance;
- INSTANCE_NAME STATUS
- ---------------- ------------
- SYBO2SZ STARTED
- sys@SYBO2SZ> select name,open_mode from v$database;
- select name,open_mode from v$database
- *
- ERROR at line 1:
- ORA-01507: database not mounted
- -->还原控制文件
- sys@SYBO2SZ> ho cp /u02/database/SYBO2SZ/backup/hotbak/contlbak.ctl /u02/database/SYBO2SZ/controlf/cntl1SYBO2SZ.ctl
- sys@SYBO2SZ> ho cp /u02/database/SYBO2SZ/backup/hotbak/contlbak.ctl /u02/database/SYBO2SZ/controlf/cntl2SYBO2SZ.ctl
- sys@SYBO2SZ> ho cp /u02/database/SYBO2SZ/backup/hotbak/contlbak.ctl /u02/database/SYBO2SZ/controlf/cntl3SYBO2SZ.ctl
- -->mount数据库
- sys@SYBO2SZ> alter database mount;
- Database altered.
- -->由于仅仅是丢失了控制文件，因此我们只还原控制文件
- -->恢复数据库，提示需要使用BACKUP CONTROLFILE选项，因为控制文件在备份后发生了变化
- sys@SYBO2SZ> recover database;
- ORA-00283: recovery session canceled due to errors
- ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
- -->提示需要实用到116归档日志
- sys@SYBO2SZ> recover database using backup controlfile;
- ORA-00279: change 1078785 generated at 09/08/2012 16:20:48 needed for thread 1
- ORA-00289: suggestion : /u02/database/SYBO2SZ/archive/arch_792094299_1_116.arc
- ORA-00280: change 1078785 for thread 1 is in sequence #116
- -->下面提示在介质恢复期间有未知的文件添加到控制文件，且文件id为9
- -->由此可以推断文件9是记录在尾数为116的归档日志中，正好与前面查看的归档日志时间相符
- Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
- auto
- ORA-00283: recovery session canceled due to errors
- ORA-01244: unnamed datafile(s) added to control file by media recovery
- ORA-01110: data file 9: '/u02/database/SYBO2SZ/oradata/tbs_tmp.dbf'
- ORA-01112: media recovery not started
- -->使用alter database create datafile重建数据文件
- -->此处故意使用了不同于创建之前的文件名tbs.dbf，此处并没有任何影响，相对于对数据文件进行了重命名
- sys@SYBO2SZ> alter database create datafile 9 as '/u02/database/SYBO2SZ/oradata/tbs.dbf';
- Database altered.
- -->尝试再次恢复，需要使用为数位116的归档日志，输入auto后，尾数为116,117的不在需要
- sys@SYBO2SZ> recover database using backup controlfile;
- ORA-00279: change 1078817 generated at 09/08/2012 16:29:19 needed for thread 1
- ORA-00289: suggestion : /u02/database/SYBO2SZ/archive/arch_792094299_1_116.arc
- ORA-00280: change 1078817 for thread 1 is in sequence #116
- Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
- auto
- ORA-00279: change 1078886 generated at 09/08/2012 16:30:52 needed for thread 1
- ORA-00289: suggestion : /u02/database/SYBO2SZ/archive/arch_792094299_1_117.arc
- ORA-00280: change 1078886 for thread 1 is in sequence #117
- ORA-00278: log file '/u02/database/SYBO2SZ/archive/arch_792094299_1_116.arc' no longer needed for this recovery
- ORA-00279: change 1078922 generated at 09/08/2012 16:32:22 needed for thread 1
- ORA-00289: suggestion : /u02/database/SYBO2SZ/archive/arch_792094299_1_118.arc
- ORA-00280: change 1078922 for thread 1 is in sequence #118
- ORA-00278: log file '/u02/database/SYBO2SZ/archive/arch_792094299_1_117.arc' no longer needed for this recovery
- -->提示未找尾数为118的归档日志，118本身还没有归档，因此来说此时是联机日志
- ORA-00308: cannot open archived log '/u02/database/SYBO2SZ/archive/arch_792094299_1_118.arc'
- ORA-27037: unable to obtain file status
- Linux-x86_64 Error: 2: No such file or directory
- Additional information: 3
- -->再次恢复
- sys@SYBO2SZ> recover database using backup controlfile;
- ORA-00279: change 1078922 generated at 09/08/2012 16:32:22 needed for thread 1
- ORA-00289: suggestion : /u02/database/SYBO2SZ/archive/arch_792094299_1_118.arc
- ORA-00280: change 1078922 for thread 1 is in sequence #118
- -->直接指定redo日志，介质恢复成功
- Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
- /u02/database/SYBO2SZ/redolog/log3aSYBO2SZ.log
- Log applied.
- Media recovery complete.
- -->下面需要使用RESETLOGS选项打开数据库
- sys@SYBO2SZ> alter database open;
- alter database open
- *
- ERROR at line 1:
- ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
- sys@SYBO2SZ> alter database open resetlogs;
- Database altered.
- --> Author : Robinson Cheng -->Blog : http://blog.csdn.net/robinson_0612
- -->验证新建对象的总记录数，正好等于删除后的记录数11条
- sys@SYBO2SZ> select count(*) from tb_emp;
- COUNT(*)
- ----------
- 11
|
评论暂时关闭