rman恢复一个面试问题

Itpub上一个网友的问题：
rman恢复一个面试问题
昨天去了一家公司面试，被问到一个问题。
rman备份的时候，在备份数据文件的同时备份有控制文件(不是alter database backup controlfile to trace这样，而是在run中包含备份控制文件)
现在备份的控制文件被删除了，之后数据库的控制文件坏了。   
在rman备份前，人工做过一次alter database backup controlfile to trace。
问：可以用重新建立控制文件的方法恢复数据库吗？
下面是我给出的答案：
1. 执行alter database backup controlfile to trace;创建控制文件文本备份。
2. 然后进行备份
RMAN> run {
2>  allocate channel ch00 type disk;
3>  backup database
4>  format 'd:\oradata\orabak\full_%d_%s_%p_%t';
5>  sql 'alter system archive log current';
6>  release channel ch00;
7> }

released channel: ORA_DISK_1
allocated channel: ch00
channel ch00: sid=157 devtype=DISK

Starting backup at 12-JAN-09
channel ch00: starting full datafile backupset
channel ch00: specifying datafile(s) in backupset
input datafile fno=00001 name=D:\ORADATA\ORCL\SYSTEM01.DBF
input datafile fno=00003 name=D:\ORADATA\ORCL\SYSAUX01.DBF
input datafile fno=00002 name=D:\ORADATA\ORCL\UNDOTBS01.DBF
input datafile fno=00004 name=D:\ORADATA\ORCL\USERS01.DBF
channel ch00: starting piece 1 at 12-JAN-09
channel ch00: finished piece 1 at 12-JAN-09
piece handle=D:\ORADATA\ORABAK\FULL_ORCL_2_1_675940546 tag=TAG20090112T091545 comment=NONE
channel ch00: backup set complete, elapsed time: 00:01:45
channel ch00: starting full datafile backupset
channel ch00: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ch00: starting piece 1 at 12-JAN-09
channel ch00: finished piece 1 at 12-JAN-09
piece handle=D:\ORADATA\ORABAK\FULL_ORCL_3_1_675940652 tag=TAG20090112T091545 comment=NONE
channel ch00: backup set complete, elapsed time: 00:00:07
Finished backup at 12-JAN-09

sql statement: alter system archive log current

released channel: ch00

RMAN> list backup of controlfile;

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
213     Full    6.80M      DISK        00:00:06     12-JAN-09
        BP Key: 215   Status: AVAILABLE  Compressed: NO  Tag: TAG20090112T091545
        Piece Name: D:\ORADATA\ORABAK\FULL_ORCL_3_1_675940652
  Control File Included: Ckp SCN: 724207       Ckp time: 12-JAN-09

RMAN> list backup of database;

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
212     Full    454.30M    DISK        00:01:42     12-JAN-09
        BP Key: 214   Status: AVAILABLE  Compressed: NO  Tag: TAG20090112T091545
        Piece Name: D:\ORADATA\ORABAK\FULL_ORCL_2_1_675940546
  List of Datafiles in backup set 212
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 723825     12-JAN-09 D:\ORADATA\ORCL\SYSTEM01.DBF
  2       Full 723825     12-JAN-09 D:\ORADATA\ORCL\UNDOTBS01.DBF
  3       Full 723825     12-JAN-09 D:\ORADATA\ORCL\SYSAUX01.DBF
  4       Full 723825     12-JAN-09 D:\ORADATA\ORCL\USERS01.DBF

RMAN> exit

Recovery Manager complete.
3. 修改控制文件备份集和控制文件本身，模拟文件丢失。
C:\Documents and Settings\Administrator>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jan 12 09:19:27 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

4. 重新启动数据库
SQL> startup
ORACLE instance started.

Total System Global Area  113246208 bytes
Fixed Size                  1247588 bytes
Variable Size              75499164 bytes
Database Buffers           33554432 bytes
Redo Buffers                2945024 bytes
ORA-00205: error in identifying control file, check alert log for more info

5. 利用文本备份创建控制文件
SQL> CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS  ARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 'D:\ORADATA\ORCL\REDO01.LOG'  SIZE 10M,
  9    GROUP 2 'D:\ORADATA\ORCL\REDO02.LOG'  SIZE 10M,
 10    GROUP 3 'D:\ORADATA\ORCL\REDO03.LOG'  SIZE 10M
 11  -- STANDBY LOGFILE
 12  DATAFILE
 13    'D:\ORADATA\ORCL\SYSTEM01.DBF',
 14    'D:\ORADATA\ORCL\UNDOTBS01.DBF',
 15    'D:\ORADATA\ORCL\SYSAUX01.DBF',
 16    'D:\ORADATA\ORCL\USERS01.DBF'
 17  CHARACTER SET ZHS16GBK
 18  ;

Control file created.

SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-01100: database already mounted

SQL> set pagesize 100
SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION           STARTUP_TIME STATUS       PAR    THREAD# ARCHIVE
----------------- ------------ ------------ --- ---------- -------
LOG_SWITCH_WAIT LOGINS     SHU DATABASE_STATUS   INSTANCE_ROLE      ACTIVE_ST
--------------- ---------- --- ----------------- ------------------ ---------
BLO
---
              1 orcl
RD-WANG
10.2.0.1.0        12-JAN-09    MOUNTED      NO           1 STOPPED
                ALLOWED    NO  ACTIVE            PRIMARY_INSTANCE   NORMAL
NO

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

6. 连接到catalog查看备份集状态(此时没有执行crosscheck，所以所有备份集都是available状态)
C:\Documents and Settings\Administrator>rman target / catalog test/test@catalog

Recovery Manager: Release 10.2.0.1.0 - Production on Mon Jan 12 09:23:12 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database: ORCL (DBID=1197938805, not open)
connected to recovery catalog database

RMAN> list backup of controlfile;

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
213     Full    6.80M      DISK        00:00:06     12-JAN-09
        BP Key: 215   Status: AVAILABLE  Compressed: NO  Tag: TAG20090112T091545
        Piece Name: D:\ORADATA\ORABAK\FULL_ORCL_3_1_675940652
  Control File Included: Ckp SCN: 724207       Ckp time: 12-JAN-09

RMAN> list backup of database;

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
212     Full    454.30M    DISK        00:01:42     12-JAN-09
        BP Key: 214   Status: AVAILABLE  Compressed: NO  Tag: TAG20090112T091545
        Piece Name: D:\ORADATA\ORABAK\FULL_ORCL_2_1_675940546
  List of Datafiles in backup set 212
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 723825     12-JAN-09 D:\ORADATA\ORCL\SYSTEM01.DBF
  2       Full 723825     12-JAN-09 D:\ORADATA\ORCL\UNDOTBS01.DBF
  3       Full 723825     12-JAN-09 D:\ORADATA\ORCL\SYSAUX01.DBF
  4       Full 723825     12-JAN-09 D:\ORADATA\ORCL\USERS01.DBF
注：从上面的信息可以看到，备份集的信息在catalog中已经存在。

6. 执行数据库恢复

RMAN> run {
2>     allocate channel ch00 type disk;
3>     restore database;
4>     release channel ch00;
5>    }

allocated channel: ch00
channel ch00: sid=157 devtype=DISK

Starting restore at 12-JAN-09

channel ch00: starting datafile backupset restore
channel ch00: specifying datafile(s) to restore from backup set
restoring datafile 00001 to D:\ORADATA\ORCL\SYSTEM01.DBF
restoring datafile 00002 to D:\ORADATA\ORCL\UNDOTBS01.DBF
restoring datafile 00003 to D:\ORADATA\ORCL\SYSAUX01.DBF
restoring datafile 00004 to D:\ORADATA\ORCL\USERS01.DBF
channel ch00: reading from backup piece D:\ORADATA\ORABAK\FULL_ORCL_2_1_675940546
channel ch00: restored backup piece 1
piece handle=D:\ORADATA\ORABAK\FULL_ORCL_2_1_675940546 tag=TAG20090112T091545
channel ch00: restore complete, elapsed time: 00:01:26
Finished restore at 12-JAN-09

released channel: ch00

RMAN> recover database;

Starting recover at 12-JAN-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=159 devtype=DISK
added temporary file D:\ORADATA\ORCL\TEMP01.DBF to tablespace TEMP in control file

starting media recovery

archive log thread 1 sequence 133 is already on disk as file D:\ORADATA\ARCH\ARC00133_0670158069.001

archive log filename=D:\ORADATA\ARCH\ARC00133_0670158069.001 thread=1 sequence=133
unable to find archive log
archive log thread=1 sequence=134
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 01/12/2009 09:30:14
RMAN-06054: media recovery requesting unknown log: thread 1 seq 134 lowscn 724225

RMAN> exit

Recovery Manager complete.

C:\Documents and Settings\Administrator>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jan 12 09:31:03 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

SQL> recover database using backup controlfile;
ORA-00279: change 724225 generated at 01/12/2009 09:17:43 needed for thread 1
ORA-00289: suggestion : D:\ORADATA\ARCH\ARC00134_0670158069.001
ORA-00280: change 724225 for thread 1 is in sequence #134

Specify log: {=suggested | filename | AUTO | CANCEL}
D:\ORADATA\ORCL\REDO02.LOG
Log applied.
Media recovery complete.

7. 重新打开数据库

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs;

Database altered.

SQL>

至此，恢复完成。
总结，由此可见如果有rman catalog的话，一切都会变得简单些，而且catalog本身的维护并不复杂，所以没有什么理由不用它。当然，如果用controlfile作为替代的话，问题肯定会变得复杂些，这样棉花糖one提到的方法，就有它的用武之地了：
“重新创建的控制文件里是不包含rman的备份信息的,如果是10g的话可以用catalog命令重新注册一次,否则用9i的dbms_backup_restore包恢复吧”
关于dbms_backup_restore可以参考eygle大师的文章“应对RMAN-06026错误，使用dbms_backup_restore进行恢复”
http://www.eygle.com/archives/2005/06/oorman06026iioe.html

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/16689238/viewspace-536392/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/16689238/viewspace-536392/