使用舊的控制檔案備份來恢復控制檔案
有時候我們會遇到這樣的場景,控制檔案損壞,並且可以用來恢復的備份控制檔案比較舊,不包含備份之後發生的各種變化。幸運的是歸檔日誌和聯機重做日誌都健全。我們使用舊的控制檔案來恢復的時候,或者是資料庫遷移的時候,面對那些舊控制檔案未知的新變化會採取怎樣的應對呢?我們透過下面的實驗來驗證一下。
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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 【備份恢復】利用 備份控制檔案到指定目錄下的控制檔案 恢復控制檔案
- 利用備份的控制檔案恢復
- 控制檔案的備份和恢復
- 備份與恢復--利用備份的控制檔案恢復
- 【備份恢復】 控制檔案多路徑
- 備份與恢復--重建控制檔案
- 【備份恢復】所有控制檔案丟失後 利用trace中的控制檔案備份執行恢復
- 使用備份的控制檔案恢復資料庫資料庫
- 恢復之丟失全部控制檔案以及備份中的控制檔案
- trace檔案備份控制檔案並執行恢復
- RMAN備份恢復之控制檔案的恢復(三)
- RMAN備份恢復之控制檔案的恢復(二)
- RMAN備份恢復之控制檔案的恢復(一)
- 備份與恢復系列 十一 控制檔案的備份與恢復
- rman備份丟失控制檔案恢復
- Oracle備份與恢復【丟失控制檔案的恢復】Oracle
- RMAN備份與恢復(新舊控制檔案及歸檔日誌)測試
- 【備份與恢復】控制檔案的恢復(不完全恢復)
- 備份與恢復--重建控制檔案後資料檔案損壞的恢復
- rman備份-(1) 利用備份級恢復資料檔案和控制檔案
- oralce備份與恢復1 控制檔案(轉)
- 【備份與恢復】恢復受損的複用控制檔案
- 開啟 控制檔案自動備份下,引數檔案、控制檔案全部丟失恢復
- RMAN備份與恢復(新舊控制檔案及歸檔日誌)測試(敘)
- 備份與恢復--重新控制檔案資料字典和控制檔案不一致的恢復
- 循序漸進oracle第7章:備份與恢復之利用控制檔案快照恢復控制檔案Oracle
- 使用rman恢復控制檔案
- Oracle手工不完全恢復(二):使用備份控制檔案Oracle
- 非歸檔無備份下控制檔案丟失的恢復
- 備份&恢復之十三:損壞全部控制檔案
- RMAN基於備份控制檔案恢復失敗
- rman恢復資料庫--用備份的控制檔案資料庫
- 只有rman備份集,控制檔案丟失的恢復
- Oracle 11g RAMN恢復-控制檔案的備份和恢復Oracle
- 【備份恢復】 丟失一個控制檔案 之恢復操作
- 【rman 備份與恢復】恢復丟失所有的控制檔案
- 備份控制檔案
- 無備份丟失部分資料檔案和控制檔案恢復 [轉]