控制文件的恢复(三)

    在之前的控制文件恢复过程中使用‘recover database'命令恢复数据库时要求所有的归档日志都是可访问的，如果某个归档日志出现损坏和丢失，那整个的恢复步骤又不一样，下面的例子说明在备份完控制文件后，某个归档日志丢失且所有控制文件损坏是如何进行实例恢复(前提是数据库开启归档日志)。

    注：某个归档日志丢失后备份了控制文件，之后所有控制文件损坏的恢复步骤和之前的恢复步骤没有区别。

    DB：Oracle 11g 11.2.0.3.0 X86_64,OS RHEL 6.3 X86_64,未使用Recovery Catalog

1.1备份控制文件
RMAN> configure controlfile autobackup on;
using target database control file instead of recovery catalog
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

RMAN> backup current controlfile;
Starting backup at 03-JAN-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=133 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 03-JAN-13
channel ORA_DISK_1: finished piece 1 at 03-JAN-13
piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2013_01_03/o1_mf_ncnnf_TAG20130103T163808_8gbjvnh7_.bkp tag=TAG20130103T163808 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 03-JAN-13

Starting Control File and SPFILE Autobackup at 03-JAN-13
piece handle=/u01/app/oracle/fast_recovery_area/ORCL/autobackup/2013_01_03/o1_mf_s_803752695_8gbjvs9b_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 03-JAN-13

1.2查询v$log视图，找出'CURRENT'状态的redo日志
SYS@orcl>select group#,sequence#,status,members,archived from v$log;       ---当期redo日志是12号
    GROUP#  SEQUENCE# STATUS          MEMBERS ARC
---------- ---------- ---------------- ---------- ---
     1       10 INACTIVE            1 YES
     2       11 INACTIVE            1 YES
     3       12 CURRENT            1 NO

1.3创建scott schema下的test表，并查处完成建表的redo日志
SYS@orcl>create table scott.test as select * from scott.emp;
Table created.

SYS@orcl>select count(*) from scott.test;
  COUNT(*)
----------
    14

---查出当期redo日志仍然是12号，12号redo日志包含上面建表的重做信息，这个例子中每个日志组的成员只有一个，生产环境下要保证至少有2-3个组成员
SYS@orcl>select group#,sequence#,status,members,archived from v$log;
    GROUP#  SEQUENCE# STATUS          MEMBERS ARC
---------- ---------- ---------------- ---------- ---
     1       10 INACTIVE            1 YES
     2       11 INACTIVE            1 YES
     3       12 CURRENT            1 NO

1.4切换日志组，使得12号redo日志归档
SYS@orcl>alter system switch logfile;

System altered.

SYS@orcl>alter system switch logfile;
System altered.

SYS@orcl>alter system switch logfile;
System altered.

SYS@orcl>alter system switch logfile;
System altered.

---当前日志组是16号，12号日志组已经归档
SYS@orcl>select group#,sequence#,status,members,archived from v$log;
    GROUP#  SEQUENCE# STATUS          MEMBERS ARC
---------- ---------- ---------------- ---------- ---
     1       16 CURRENT            1 NO
     2       14 INACTIVE            1 YES
     3       15 ACTIVE            1 YES

1.5查出12号redo日志的归档文件并删除
SYS@orcl>select name,sequence#,first_time from v$archived_log  where sequence#=12 and to_char(first_time,'YYYY-MM-DD')='2013-01-03';
NAME
--------------------------------------------------------------------------------
 SEQUENCE# FIRST_TIM
---------- ---------
/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2013_01_03/o1_mf_1_12_8gbjzkts_.arc
    12 03-JAN-13


[oracle@ora ~]$ rm /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2013_01_03/o1_mf_1_12_8gbjzkts_.arc

1.6使所有的控制文件不可用

[oracle@ora ORCL]$  dd if=/dev/zero of=/u01/app/oracle/oradata/orcl/control01.ctl bs=512K count=10;
10+0 records in
10+0 records out
5242880 bytes (5.2 MB) copied, 0.00586479 s, 894 MB/s
[oracle@ora ORCL]$ dd if=/dev/zero of=/u01/app/oracle/fast_recovery_area/orcl/control02.ctl bs=512K count=10;
10+0 records in
10+0 records out
5242880 bytes (5.2 MB) copied, 0.00559785 s, 937 MB/s
[oracle@ora ORCL]$ dd if=/dev/zero of=/u01/app/oracle/oradata/orcl/control03.ctl bs=512K count=10;
10+0 records in
10+0 records out

1.7启动实例到nomount状态
SYS@orcl>startup force nomount;
ORACLE instance started.

Total System Global Area 1887350784 bytes
Fixed Size            2229464 bytes
Variable Size         1107299112 bytes
Database Buffers      771751936 bytes
Redo Buffers            6070272 bytes

1.8从1.1的自动备份里恢复控制文件
RMAN> restore controlfile from autobackup;

Starting restore at 03-JAN-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10 device type=DISK

recovery area destination: /u01/app/oracle/fast_recovery_area
database name (or database unique name) used for search: ORCL
channel ORA_DISK_1: AUTOBACKUP /u01/app/oracle/fast_recovery_area/ORCL/autobackup/2013_01_03/o1_mf_s_803752695_8gbjvs9b_.bkp found in the recovery area
AUTOBACKUP search with format "%F" not attempted because DBID was not set
channel ORA_DISK_1: restoring control file from AUTOBACKUP /u01/app/oracle/fast_recovery_area/ORCL/autobackup/2013_01_03/o1_mf_s_803752695_8gbjvs9b_.bkp
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=/u01/app/oracle/oradata/orcl/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/orcl/control02.ctl
output file name=/u01/app/oracle/oradata/orcl/control03.ctl
Finished restore at 03-JAN-13

1.9使实例到mount状态
RMAN> mount database;
database mounted
released channel: ORA_DISK_1

1.10执行'recover database'命令恢复数据库
RMAN> recover database;
Starting recover at 03-JAN-13
Starting implicit crosscheck backup at 03-JAN-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10 device type=DISK
Crosschecked 1 objects
Finished implicit crosscheck backup at 03-JAN-13

Starting implicit crosscheck copy at 03-JAN-13
using channel ORA_DISK_1
Finished implicit crosscheck copy at 03-JAN-13

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2013_01_03/o1_mf_1_9_8gbjxsow_.arc
File Name: /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2013_01_03/o1_mf_1_7_8gbjxn5x_.arc
File Name: /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2013_01_03/o1_mf_1_14_8gbjzqvh_.arc
File Name: /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2013_01_03/o1_mf_1_6_8gbjxlow_.arc
File Name: /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2013_01_03/o1_mf_1_10_8gbjxv45_.arc
File Name: /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2013_01_03/o1_mf_1_11_8gbjxwgy_.arc
File Name: /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2013_01_03/o1_mf_1_15_8gbjzspz_.arc
File Name: /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2013_01_03/o1_mf_1_13_8gbjzm4x_.arc
File Name: /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2013_01_03/o1_mf_1_8_8gbjxrdj_.arc
File Name: /u01/app/oracle/fast_recovery_area/ORCL/autobackup/2013_01_03/o1_mf_s_803752695_8gbjvs9b_.bkp

using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 6 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2013_01_03/o1_mf_1_6_8gbjxlow_.arc
archived log for thread 1 with sequence 7 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2013_01_03/o1_mf_1_7_8gbjxn5x_.arc
archived log for thread 1 with sequence 8 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2013_01_03/o1_mf_1_8_8gbjxrdj_.arc
archived log for thread 1 with sequence 9 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2013_01_03/o1_mf_1_9_8gbjxsow_.arc
archived log for thread 1 with sequence 10 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2013_01_03/o1_mf_1_10_8gbjxv45_.arc
archived log for thread 1 with sequence 11 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2013_01_03/o1_mf_1_11_8gbjxwgy_.arc
archived log for thread 1 with sequence 13 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2013_01_03/o1_mf_1_13_8gbjzm4x_.arc
archived log for thread 1 with sequence 14 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2013_01_03/o1_mf_1_14_8gbjzqvh_.arc
archived log for thread 1 with sequence 15 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2013_01_03/o1_mf_1_15_8gbjzspz_.arc
archived log for thread 1 with sequence 16 is already on disk as file /u01/app/oracle/oradata/orcl/redo01.log
archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2013_01_03/o1_mf_1_6_8gbjxlow_.arc thread=1 sequence=6
archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2013_01_03/o1_mf_1_7_8gbjxn5x_.arc thread=1 sequence=7
archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2013_01_03/o1_mf_1_8_8gbjxrdj_.arc thread=1 sequence=8
archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2013_01_03/o1_mf_1_9_8gbjxsow_.arc thread=1 sequence=9
archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2013_01_03/o1_mf_1_10_8gbjxv45_.arc thread=1 sequence=10
archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2013_01_03/o1_mf_1_11_8gbjxwgy_.arc thread=1 sequence=11
unable to find archived log
archived log thread=1 sequence=12
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 01/03/2013 16:44:53
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 12 and starting SCN of 1088992
---在执行恢复过程中提示找不到12号的归档日志


---查询v$archived_log视图得知归档日志最大的序列号是16
SYS@orcl>select max(sequence#) from v$archived_log where to_char(first_time,'YYYY-MM-DD')='2013-01-03';
MAX(SEQUENCE#)
--------------
        16

---查询v$log试图得知数据库CURRENT的日志是6号
SYS@orcl>select sequence# from v$log where status='CURRENT';
 SEQUENCE#
----------
     6

1.11创建新控制文件，使新控制文件不知道CURRENT日志的序列号，不强制要任何日志对其恢复
SYS@orcl>alter database backup controlfile to trace;                     ---保存控制文件到trace文件
Database altered.

SYS@orcl>select value from v$diag_info where name='Default Trace File';       ---查询trace文件位置
VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_15729.trc


SYS@orcl>startup force nomount;                          ---重启实例到nomount状态
ORACLE instance started.

Total System Global Area 1887350784 bytes
Fixed Size            2229464 bytes
Variable Size         1107299112 bytes
Database Buffers      771751936 bytes
Redo Buffers            6070272 bytes

---使用trace文件中带'NORESETLOGS'关键字的'CREATE CONTROLFILE'语句创建新控制文件
SYS@orcl>CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/orcl/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/u01/app/oracle/oradata/orcl/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u01/app/oracle/oradata/orcl/redo03.log'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/oradata/orcl/system01.dbf',
  '/u01/app/oracle/oradata/orcl/sysaux01.dbf',
  '/u01/app/oracle/oradata/orcl/undotbs01.dbf',
  '/u01/app/oracle/oradata/orcl/users01.dbf',
  '/u01/app/oracle/oradata/orcl/example01.dbf'
CHARACTER SET AL32UTF8
;
Control file created.

1.12再执行'recover database'命令恢复数据库
RMAN> recover database;
Starting recover at 03-JAN-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10 device type=DISK

starting media recovery

archived log for thread 1 with sequence 15 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2013_01_03/o1_mf_1_15_8gbjzspz_.arc
archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2013_01_03/o1_mf_1_15_8gbjzspz_.arc thread=1 sequence=15
unable to find archived log
archived log thread=1 sequence=16
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 01/03/2013 16:59:52
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 16 and starting SCN of 1089055
---提示找不到16号归档日志

---查询v$log视图可知16号日志是当前日志组，没有被归档
SYS@orcl>select sequence#,status,group# from v$log;

 SEQUENCE# STATUS        GROUP#
---------- ---------------- ----------
    16 CURRENT             1
    15 INACTIVE             3
    14 INACTIVE             2

---使用sqlplus的recover database命令进行恢复
SYS@orcl>recover database using backup controlfile;
ORA-00279: change 1089055 generated at 01/03/2013 16:40:25 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2013_01_03/o1_mf_1_16_%u_.arc
ORA-00280: change 1089055 for thread 1 is in sequence #16


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}        ---回车，再输入16号日志组的成员路径,应用redo日志进行恢复
/u01/app/oracle/oradata/orcl/redo01.log             
Log applied.
Media recovery complete.


1.13以resetlogs命令打开实例
SYS@orcl>alter database open resetlogs;
Database altered.

---添加临时数据文件
SYS@orcl>alter tablespace temp add tempfile  '/u01/app/oracle/oradata/orcl/temp01.dbf' reuse;
Tablespace altered.

1.14再查询test表，数据依旧存在
SYS@orcl>select count(*) from scott.test;
  COUNT(*)
----------
    14

1.15执行'catalog db_recovery_file_dest'命令找回快速恢复区的备份信息
RMAN> list backup;
specification does not match any backup in the repository

RMAN> catalog db_recovery_file_dest;
searching for all files in the recovery area

List of Files Unknown to the Database
=====================================
File Name: /u01/app/oracle/fast_recovery_area/ORCL/autobackup/2013_01_03/o1_mf_s_803752695_8gbjvs9b_.bkp
File Name: /u01/app/oracle/fast_recovery_area/ORCL/backupset/2013_01_03/o1_mf_ncnnf_TAG20130103T163808_8gbjvnh7_.bkp

Do you really want to catalog the above files (enter YES or NO)?