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