使用舊的控制檔案備份來恢復控制檔案

snowdba發表於2014-10-07
有時候我們會遇到這樣的場景,控制檔案損壞,並且可以用來恢復的備份控制檔案比較舊,不包含備份之後發生的各種變化。幸運的是歸檔日誌和聯機重做日誌都健全。我們使用舊的控制檔案來恢復的時候,或者是資料庫遷移的時候,面對那些舊控制檔案未知的新變化會採取怎樣的應對呢?我們透過下面的實驗來驗證一下。

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/,如需轉載,請註明出處,否則將追究法律責任。

相關文章