Oracle基於cancel的不完全恢複

Oracle基於cancel的不完全恢複

實驗：Oracle基於cancel的不完全恢複

實驗環境查看

lsnrctl status
select open_mode from v$database;
  --監聽與資料庫狀態

show parameter recovery;
select flashback_on from v$database;
archive log list;
  --閃回與歸檔的配置

1)準備環境：RMAN全庫備份
RMAN> backup as compressed backupset full database;
      --壓縮備份
      --backup full database ;備份組備份

Starting backup at 20-MAR-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=45 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/ORA11GR2/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/ORA11GR2/sysaux01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/ORA11GR2/undotbs01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/ORA11GR2/example01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/ORA11GR2/users01.dbf
channel ORA_DISK_1: starting piece 1 at 20-MAR-13
channel ORA_DISK_1: finished piece 1 at 20-MAR-13
piece handle=/u01/app/FRA/ORA11GR2/backupset/2013_03_20/o1_mf_nnndf_TAG20130320T151949_8nlrrrfm_.bkp tag=TAG20130320T151949 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:17
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 20-MAR-13
channel ORA_DISK_1: finished piece 1 at 20-MAR-13
piece handle=/u01/app/FRA/ORA11GR2/backupset/2013_03_20/o1_mf_ncsnf_TAG20130320T151949_8nlrx2qs_.bkp tag=TAG20130320T151949 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 20-MAR-13

 

 

2)建立測試資料
SYS@ORA11GR2>create table scott.t1 as select 1 as id from dual;

Table created.

SYS@ORA11GR2>alter system archive log current;

System altered.

SYS@ORA11GR2>create table scott.t2 as select 2 as id from dual;

Table created.

SYS@ORA11GR2>alter system archive log current;

System altered.

SYS@ORA11GR2>create table scott.t3 as select 3 as id from dual;

Table created.

SYS@ORA11GR2>alter system archive log current;

System altered.

SYS@ORA11GR2>
SYS@ORA11GR2>select table_name,tablespace_name from dba_tables where owner='SCOTT' AND table_name LIKE 'T_';

TABLE_NAME                  TABLESPACE_NAME
------------------------- ------------------------------
T3                            USERS
T2                            USERS
T1                            USERS

完成測試資料構造後，查看產生的歸檔日誌
ls /u01/app/oracle/fast_recovery_area/PROD/archivelog/2014_10_08
ls -lrt

3)刪除所有資料檔案和線上資料檔案

在sqlplus裡面刪除：
SYS@PROD>select name from v$datafile;

NAME
--------------------
/u01/app/oracle/oradata/PROD/datafile/o1_mf_system_b2251bs1_.dbf

/u01/app/oracle/oradata/PROD/datafile/o1_mf_sysaux_b2251bvo_.dbf

/u01/app/oracle/oradata/PROD/datafile/o1_mf_undotbs1_b2251bw5_.dbf

/u01/app/oracle/oradata/PROD/datafile/o1_mf_users_b2251byw_.dbf

/u01/app/oracle/oradata/PROD/datafile/o1_mf_example_b2257d0c_.dbf

/u01/app/oracle/oradata/PROD/datafile/tbs_move_01.dbf

SYS@PROD>select member from v$logfile;

MEMBER
----------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/PROD/onlinelog/o1_mf_3_b22567o2_.log
/u01/app/oracle/fast_recovery_area/PROD/onlinelog/o1_mf_3_b2256827_.log
/u01/app/oracle/oradata/PROD/onlinelog/o1_mf_2_b2255zsg_.log
/u01/app/oracle/fast_recovery_area/PROD/onlinelog/o1_mf_2_b22560gb_.log
/u01/app/oracle/oradata/PROD/onlinelog/o1_mf_1_b2255npg_.log
/u01/app/oracle/fast_recovery_area/PROD/onlinelog/o1_mf_1_b2255nxl_.log

SYS@PROD>!rm /u01/app/oracle/oradata/PROD/datafile/*.dbf;

SYS@PROD>!rm /u01/app/oracle/oradata/PROD/onlinelog/*.log;

SYS@PROD>!rm /u01/app/oracle/fast_recovery_area/PROD/onlinelog/*.log;

在OS裡面刪除：
[oracle@ocmu ORA11GR2]$ pwd
/u01/app/oracle/oradata/ORA11GR2
[oracle@ocmu ORA11GR2]$ ls
control01.ctl  example01.dbf  redo02.log  sysaux01.dbf  temp01.dbf    users01.dbf
control02.ctl  redo01.log    redo03.log  system01.dbf  undotbs01.dbf
[oracle@ocmu ORA11GR2]$ rm *.log
[oracle@ocmu ORA11GR2]$ ls
control01.ctl  example01.dbf  system01.dbf  undotbs01.dbf
control02.ctl  sysaux01.dbf  temp01.dbf    users01.dbf
[oracle@ocmu ORA11GR2]$

4)資料庫啟動到mount模式
SYS@ORA11GR2>shutdown abort;
ORACLE instance shut down.
SYS@ORA11GR2>startup mount;
ORACLE instance started.

Total System Global Area  841162752 bytes
Fixed Size                  1339768 bytes
Variable Size            532680328 bytes
Database Buffers          301989888 bytes
Redo Buffers                5152768 bytes
Database mounted.
SYS@ORA11GR2>

5)RMAN還原資料庫
RMAN> restore database;

Starting restore at 20-MAR-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/ORA11GR2/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/ORA11GR2/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/ORA11GR2/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/ORA11GR2/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/ORA11GR2/example01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/FRA/ORA11GR2/backupset/2013_03_20/o1_mf_nnndf_TAG20130320T151949_8nlrrrfm_.bkp
channel ORA_DISK_1: piece handle=/u01/app/FRA/ORA11GR2/backupset/2013_03_20/o1_mf_nnndf_TAG20130320T151949_8nlrrrfm_.bkp tag=TAG20130320T151949
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:03:26
Finished restore at 20-MAR-13

RMAN>

6)基於cancel恢複
SYS@ORA11GR2>recover database until cancel;
  --在sqlplus中完成

ORA-00279: change 883460 generated at 03/20/2013 15:19:50 needed for thread 1
ORA-00289: suggestion :
/u01/app/FRA/ORA11GR2/archivelog/2013_03_20/o1_mf_1_8_8nlrzy8w_.arc
ORA-00280: change 883460 for thread 1 is in sequence #8

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

  --應用sequence #8為8，名字為o1_mf_1_8_8nlrzy8w_.arc的歸檔記錄檔，直接按斷行符號鍵

ORA-00279: change 884069 generated at 03/20/2013 15:23:42 needed for thread 1
ORA-00289: suggestion :
/u01/app/FRA/ORA11GR2/archivelog/2013_03_20/o1_mf_1_9_8nls091w_.arc
ORA-00280: change 884069 for thread 1 is in sequence #9
ORA-00278: log file
'/u01/app/FRA/ORA11GR2/archivelog/2013_03_20/o1_mf_1_8_8nlrzy8w_.arc' no longer
needed for this recovery

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

  --應用sequence #9為9的這個歸檔記錄檔，直接按斷行符號鍵

ORA-00279: change 884101 generated at 03/20/2013 15:23:53 needed for thread 1
ORA-00289: suggestion :
/u01/app/FRA/ORA11GR2/archivelog/2013_03_20/o1_mf_1_10_8nls0os6_.arc
ORA-00280: change 884101 for thread 1 is in sequence #10
ORA-00278: log file
'/u01/app/FRA/ORA11GR2/archivelog/2013_03_20/o1_mf_1_9_8nls091w_.arc' no longer
needed for this recovery

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
  --手動輸入cancel結束恢複，不管後面有沒有可恢複檔案，都會結束；
Media recovery cancelled.
SYS@ORA11GR2>
SYS@ORA11GR2>alter database open resetlogs;

Database altered.

SYS@ORA11GR2>

7）檢查
SYS@ORA11GR2>select table_name,tablespace_name from dba_tables where owner='SCOTT' AND table_name LIKE 'T_';

TABLE_NAME                  TABLESPACE_NAME
------------------------- ------------------------------
T2                            USERS
T1                            USERS

SYS@ORA11GR2>
注 應用了兩個歸檔日誌，第三個歸檔日誌cancel了，從結果中也可以看到，恢複以後的資料庫中，只
 存在T1,T2兩張表，它們倆的重做日誌就在前兩個歸檔中，T3表的所有重做條目都在第三個歸檔中，
 由於採取了cancel，所以，T3表並未恢複

還有一篇供參考：

1 2 下一頁