使用旧的控制文件备份来恢复控制文件

有时候我们会遇到这样的场景,控制文件损坏,并且可以用来恢复的备份控制文件比较旧,不包含备份之后发生的各种变化。幸运的是归档日志和联机重做日志都健全。我们使用旧的控制文件来恢复的时候,或者是数据库迁移的时候,面对那些旧控制文件未知的新变化会采取怎样的应对呢?我们通过下面的实验来验证一下。

1,选择一份可用的手工备份的控制文件。

1号备份集是自动备份的控制文件,所在路径为autobackup下,时间也是最新的。3号备份集和7号备份集中备份的控制文件都是手工生成的,记录的是当时数据库的状态。在这之后发生的任何操作都没有记录。本次试验我们使用 3号备份集 中的控制文件。

RMAN> list backup of controlfile;

using target database control file instead of recovery catalog

List of Backup Sets
===================
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
1       Full    9.67M      DISK        00:00:00     2014-10-06 17:56:10
        BP Key: 1   Status: AVAILABLE  Compressed: NO  Tag: TAG20141006T175610
        Piece Name: /u01/fast_recovery_area/PRACTICE/autobackup/2014_10_06/o1_mf_s_860262970_b363zt2x_.bkp
  Control File Included: Ckp SCN: 1051644      Ckp time: 2014-10-06 17:56:10

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
3       Full    9.33M      DISK        00:00:00     2014-10-06 13:28:27
        BP Key: 3   Status: AVAILABLE  Compressed: NO  Tag: TAG20141006T132827
        Piece Name: /u01/fast_recovery_area/PRACTICE/backupset/2014_10_06/o1_mf_ncnnf_TAG20141006T132827_b35n9w39_.bkp
  Control File Included: Ckp SCN: 1005439      Ckp time: 2014-10-06 13:28:27

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
7       Full    9.33M      DISK        00:00:00     2014-10-06 14:39:16
        BP Key: 7   Status: AVAILABLE  Compressed: NO  Tag: TAG20141006T143909
        Piece Name: /home/oracle/full_PRACTICE_9_20141006_1.bak
  Control File Included: Ckp SCN: 1013438      Ckp time: 2014-10-06 14:39:16


2,查看当前表空间

RMAN> report schema;

Report of database schema for database with db_unique_name PRACTICE

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    750      SYSTEM               ***     /u01/oradata/practice/system01.dbf
2    550      SYSAUX               ***     /u01/oradata/practice/sysaux01.dbf
3    100      UNDOTBS1             ***     /u01/oradata/practice/undotbs01.dbf
4    5        USERS                ***     /u01/oradata/practice/users01.dbf
5    313      EXAMPLE              ***     /u01/oradata/practice/example01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    29       TEMP                 29          /u01/oradata/practice/temp01.dbf

3,创建新的表空间tbs1,自动备份控制文件的配置已经开启,该操作会记录在自动备份中,但不会记录在历史手工备份的控制文件中。

SYS@practice >create tablespace tbs1 datafile '/u01/oradata/practice/tbs1.dbf' size 5m;


4,查看日志文件确认一次创建tbs1表空间的时间

tail -500 /u01/diag/rdbms/practice/practice/trace/alert_practice.log
…...截取片段如下
Tue Oct 07 10:03:14 2014   <==创建表空间tbs1的时间
create tablespace tbs1 datafile '/u01/oradata/practice/tbs1.dbf' size 5m
Completed: create tablespace tbs1 datafile '/u01/oradata/practice/tbs1.dbf' size 5m

5,再次查看RMAN自动备份控制文件的备份集为:o1_mf_s_860321450_b37x3b83_.bkp,时间是10:10:50。由于新建表空间tbs1会触发控制文件自动备份,备份集生成的时间为10:10:50,比创建表空间的时间延后了将近8分钟。

RMAN> list backup of controlfile;

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

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
1       Full    9.67M      DISK        00:00:00     2014-10-06 17:56:10
        BP Key: 1   Status: AVAILABLE  Compressed: NO  Tag: TAG20141006T175610
        Piece Name: /u01/fast_recovery_area/PRACTICE/autobackup/2014_10_06/o1_mf_s_860262970_b363zt2x_.bkp
  Control File Included: Ckp SCN: 1051644      Ckp time: 2014-10-06 17:56:10

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
3       Full    9.33M      DISK        00:00:00     2014-10-06 13:28:27
        BP Key: 3   Status: AVAILABLE  Compressed: NO  Tag: TAG20141006T132827
        Piece Name: /u01/fast_recovery_area/PRACTICE/backupset/2014_10_06/o1_mf_ncnnf_TAG20141006T132827_b35n9w39_.bkp
  Control File Included: Ckp SCN: 1005439      Ckp time: 2014-10-06 13:28:27

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
7       Full    9.33M      DISK        00:00:00     2014-10-06 14:39:16
        BP Key: 7   Status: AVAILABLE  Compressed: NO  Tag: TAG20141006T143909
        Piece Name: /home/oracle/full_PRACTICE_9_20141006_1.bak
  Control File Included: Ckp SCN: 1013438      Ckp time: 2014-10-06 14:39:16

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
10      Full    9.67M      DISK        00:00:00     2014-10-07 10:10:50
        BP Key: 10   Status: AVAILABLE  Compressed: NO  Tag: TAG20141007T101050
        Piece Name: /u01/fast_recovery_area/PRACTICE/autobackup/2014_10_07/o1_mf_s_860321450_b37x3b83_.bkp
  Control File Included: Ckp SCN: 1063485      Ckp time: 2014-10-07 10:10:50 <==创建tbs1触发控制文件自动备份时间

6,再一次查看表空间,将出现tbs1,文件号为6

RMAN> report schema;

Report of database schema for database with db_unique_name PRACTICE

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    750      SYSTEM               ***     /u01/oradata/practice/system01.dbf
2    550      SYSAUX               ***     /u01/oradata/practice/sysaux01.dbf
3    100      UNDOTBS1             ***     /u01/oradata/practice/undotbs01.dbf
4    5        USERS                ***     /u01/oradata/practice/users01.dbf
5    313      EXAMPLE              ***     /u01/oradata/practice/example01.dbf
6    5        TBS1                 ***     /u01/oradata/practice/tbs1.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    29       TEMP                 29          /u01/oradata/practice/temp01.dbf

准备工作结束,下面开始恢复控制文件,当然采用的是旧的控制文件,不包含最新表空间tbs1的,看看会使什么结果?

7,使用旧的控制文件恢复。新建的表空间tbs1,6号数据文件会被日志文件给重新创建出来,注意下面红色字体 creating datafile….

RMAN> run{
2> startup force nomount;
3> restore controlfile from '/home/oracle/full_PRACTICE_9_20141006_1.bak';
4> mount database;
5> recover database;
6> alter database open resetlogs;
7> }

Oracle instance started

Total System Global Area     580395008 bytes

Fixed Size                     2255392 bytes
Variable Size                406848992 bytes
Database Buffers             167772160 bytes
Redo Buffers                   3518464 bytes

Starting restore at 2014-10-07 10:38:31
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/oradata/practice/control01.ctl
output file name=/u01/fast_recovery_area/practice/control02.ctl
Finished restore at 2014-10-07 10:38:33

database mounted
released channel: ORA_DISK_1

Starting recover at 2014-10-07 10:38:37
Starting implicit crosscheck backup at 2014-10-07 10:38:37
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
Crosschecked 6 objects
Finished implicit crosscheck backup at 2014-10-07 10:38:38

Starting implicit crosscheck copy at 2014-10-07 10:38:38
using channel ORA_DISK_1
Finished implicit crosscheck copy at 2014-10-07 10:38:38

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

List of Cataloged Files
=======================
File Name: /u01/fast_recovery_area/PRACTICE/archivelog/2014_10_06/o1_mf_1_1_b35s02o6_.arc
File Name: /u01/fast_recovery_area/PRACTICE/archivelog/2014_10_06/o1_mf_1_4_b363m8xz_.arc
File Name: /u01/fast_recovery_area/PRACTICE/archivelog/2014_10_06/o1_mf_1_2_b35rgr1k_.arc
File Name: /u01/fast_recovery_area/PRACTICE/archivelog/2014_10_06/o1_mf_1_1_b35rgr0g_.arc
File Name: /u01/fast_recovery_area/PRACTICE/archivelog/2014_10_06/o1_mf_1_2_b35s02on_.arc
File Name: /u01/fast_recovery_area/PRACTICE/archivelog/2014_10_06/o1_mf_1_3_b363m7qs_.arc
File Name: /u01/fast_recovery_area/PRACTICE/archivelog/2014_10_06/o1_mf_1_1_b362kndr_.arc
File Name: /u01/fast_recovery_area/PRACTICE/archivelog/2014_10_06/o1_mf_1_3_b362lc83_.arc
File Name: /u01/fast_recovery_area/PRACTICE/archivelog/2014_10_06/o1_mf_1_3_b35s02oq_.arc
File Name: /u01/fast_recovery_area/PRACTICE/archivelog/2014_10_06/o1_mf_1_2_b363m7qn_.arc
File Name: /u01/fast_recovery_area/PRACTICE/archivelog/2014_10_06/o1_mf_1_2_b362l34q_.arc
File Name: /u01/fast_recovery_area/PRACTICE/autobackup/2014_10_07/o1_mf_s_860321450_b37x3b83_.bkp
File Name: /u01/fast_recovery_area/PRACTICE/autobackup/2014_10_06/o1_mf_s_860251717_b35s05gm_.bkp
File Name: /u01/fast_recovery_area/PRACTICE/autobackup/2014_10_06/o1_mf_s_860251162_b35rgt84_.bkp
File Name: /u01/fast_recovery_area/PRACTICE/autobackup/2014_10_06/o1_mf_s_860262970_b363zt2x_.bkp
File Name: /u01/fast_recovery_area/PRACTICE/autobackup/2014_10_06/o1_mf_s_860251158_b35rgpms_.bkp
File Name: /u01/fast_recovery_area/PRACTICE/autobackup/2014_10_06/o1_mf_s_860246909_b35n9x55_.bkp

using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 1 is already on disk as file /u01/fast_recovery_area/PRACTICE/archivelog/2014_10_06/o1_mf_1_1_b35s02o6_.arc
archived log for thread 1 with sequence 2 is already on disk as file /u01/fast_recovery_area/PRACTICE/archivelog/2014_10_06/o1_mf_1_2_b35rgr1k_.arc
archived log for thread 1 with sequence 3 is already on disk as file /u01/fast_recovery_area/PRACTICE/archivelog/2014_10_06/o1_mf_1_3_b35s02oq_.arc
archived log for thread 1 with sequence 1 is already on disk as file /u01/fast_recovery_area/PRACTICE/archivelog/2014_10_06/o1_mf_1_1_b362kndr_.arc
archived log for thread 1 with sequence 2 is already on disk as file /u01/fast_recovery_area/PRACTICE/archivelog/2014_10_06/o1_mf_1_2_b363m7qn_.arc
archived log for thread 1 with sequence 3 is already on disk as file /u01/fast_recovery_area/PRACTICE/archivelog/2014_10_06/o1_mf_1_3_b363m7qs_.arc
archived log for thread 1 with sequence 4 is already on disk as file /u01/fast_recovery_area/PRACTICE/archivelog/2014_10_06/o1_mf_1_4_b363m8xz_.arc
archived log for thread 1 with sequence 5 is already on disk as file /u01/oradata/practice/redo02.log
archived log file name=/u01/fast_recovery_area/PRACTICE/archivelog/2014_10_06/o1_mf_1_1_b35s02o6_.arc thread=1 sequence=1
archived log file name=/u01/fast_recovery_area/PRACTICE/archivelog/2014_10_06/o1_mf_1_2_b35rgr1k_.arc thread=1 sequence=2
archived log file name=/u01/fast_recovery_area/PRACTICE/archivelog/2014_10_06/o1_mf_1_3_b35s02oq_.arc thread=1 sequence=3
archived log file name=/u01/fast_recovery_area/PRACTICE/archivelog/2014_10_06/o1_mf_1_1_b362kndr_.arc thread=1 sequence=1
archived log file name=/u01/fast_recovery_area/PRACTICE/archivelog/2014_10_06/o1_mf_1_2_b363m7qn_.arc thread=1 sequence=2
archived log file name=/u01/fast_recovery_area/PRACTICE/archivelog/2014_10_06/o1_mf_1_3_b363m7qs_.arc thread=1 sequence=3
archived log file name=/u01/fast_recovery_area/PRACTICE/archivelog/2014_10_06/o1_mf_1_4_b363m8xz_.arc thread=1 sequence=4
archived log file name=/u01/oradata/practice/redo02.log thread=1 sequence=5
creating datafile file number=6 name=/u01/oradata/practice/tbs1.dbf     <=6号文件,tbs1被创建出来了
archived log file name=/u01/oradata/practice/redo02.log thread=1 sequence=5
media recovery complete, elapsed time: 00:00:00
Finished recover at 2014-10-07 10:38:39

database opened

7,在SQLPULS下查看表空间tbs1数据文件是否存在
SYS@practice >select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/oradata/practice/system01.dbf
/u01/oradata/practice/sysaux01.dbf
/u01/oradata/practice/undotbs01.dbf
/u01/oradata/practice/users01.dbf
/u01/oradata/practice/example01.dbf
/u01/oradata/practice/tbs1.dbf

6 rows selected.

结论:使用旧的控制文件来恢复的话,只要日志文件健全会自动填补旧控制文件的缺失。

如果出现相反的情景,控制文件中包含表空间tbs1,可是在之后的操作将该表空间删除了。恢复的时候采用包含tbs1的控制文件会怎样呢?

还记得刚才创建tbs1触发控制文件自动备份的那个备份集么,我们这次就采用它来恢复。但是在恢复之前,要删除tbs1表空间使得该控制文件边旧。


1,删除表空间tbs1及其数据文件

SYS@practice >drop tablespace tbs1 including contents and datafilee;

查看日志确认删除时间为11:03:52,而采用的自动备份控制文件为10:10:50的
[oracle@single ~]$ tail -10 /u01/diag/rdbms/practice/practice/trace/alert_practice.log
…..
Tue Oct 07 11:03:52 2014
drop tablespace tbs1 including contents and datafiles
Deleted file /u01/oradata/practice/tbs1.dbf
Completed: drop tablespace tbs1 including contents and datafilee

2,开始恢复
RMAN> startup force no mount;

RMAN> restore controlfile from auto backup;

RMAN> mount database;

此时登录sqlplus还是能看到表空间tbs1
select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/oradata/practice/system01.dbf
/u01/oradata/practice/sysaux01.dbf
/u01/oradata/practice/undotbs01.dbf
/u01/oradata/practice/users01.dbf
/u01/oradata/practice/example01.dbf
/u01/oradata/practice/tbs1.dbf


采用关键字skip来去除tbs1,在屏幕输出中可以看到RMAN将tbs1的数据文件datafile6给offline了。

RMAN> recover database skip tablespace tbs1;
...
Executing: alter database datafile 6 offline
starting media recovery

如果不采取skip tbs1恢复的数据库的话会提示
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 10/07/2014 11:11:09
RMAN-06094: datafile 6 must be restored

RMAN> alter database open resetlogs;

再次通过sqlplus查看表空间tbs1已经不存在了。
SYS@practice >/

NAME
--------------------------------------------------------------------------------
/u01/oradata/practice/system01.dbf
/u01/oradata/practice/sysaux01.dbf
/u01/oradata/practice/undotbs01.dbf
/u01/oradata/practice/users01.dbf
/u01/oradata/practice/example01.dbf



此次试验结束。

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

转载于:http://blog.itpub.net/29047826/viewspace-1290715/

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值