rman 穿越incarnation恢復資料

一 備份資料庫
RMAN> run{
2> allocate channel c1 type disk;
3> backup database include current controlfile format '/home/oracle/rmanbak/chen_%U.trc';
4> }


二 生成測試表
SQL> drop table t_incarnation purge;

SQL> create table t_incarnation as select level as id,'cross successful' as cc from dual connect by level<=3;

SQL> select * from t_incarnation;

        ID CC
---------- --------------------------------
         1 cross successful
         2 cross successful
         3 cross successful


三 記錄表資料生成時間
SQL> select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual;

TO_CHAR(SYSDATE,'YYYY-MM-DDHH:MI:SS')
--------------------------------------
2015-07-08 11:14:00



四 模擬誤刪除事故
SQL> drop table t_incarnation purge;


五 RMAN按時間不完全恢復
[oracle@ogg1 ogg1]$ rman target / nocatalog

RMAN> list incarnation;

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       OGG1     4055439112       PARENT  1          15-AUG-09
2       2       OGG1     4055439112       PARENT  945184     06-APR-15
3       3       OGG1     4055439112       PARENT  1590817    07-JUL-15
4       4       OGG1     4055439112       CURRENT 1600502    08-JUL-15

RMAN> shutdown immediate

database dismounted
Oracle instance shut down

RMAN> startup mount

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area    1252663296 bytes

Fixed Size                     2212936 bytes
Variable Size                805309368 bytes
Database Buffers             436207616 bytes
Redo Buffers                   8933376 bytes

RMAN> list incarnation;

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       OGG1     4055439112       PARENT  1          15-AUG-09
2       2       OGG1     4055439112       PARENT  945184     06-APR-15
3       3       OGG1     4055439112       PARENT  1590817    07-JUL-15
4       4       OGG1     4055439112       CURRENT 1600502    08-JUL-15


將時間恢復到建表之前，模擬恢復故障
RMAN> run{
2> allocate channel t1 type disk;
3> set until time "to_date('2015-07-08 11:12:00','yyyy-mm-dd hh24:mi:ss')";
4> restore database;
5> recover database;
6> }
allocated channel: t1
channel t1: SID=133 device type=DISK

executing command: SET until clause

Starting restore at 08-JUL-15

skipping datafile 4; already restored to file /u01/app/oracle/oradata/ogg1/users01.dbf
channel t1: starting datafile backup set restore
channel t1: specifying datafile(s) to restore from backup set
channel t1: restoring datafile 00001 to /u01/app/oracle/oradata/ogg1/system01.dbf
channel t1: restoring datafile 00002 to /u01/app/oracle/oradata/ogg1/sysaux01.dbf
channel t1: restoring datafile 00003 to /u01/app/oracle/oradata/ogg1/undotbs01.dbf
channel t1: restoring datafile 00005 to /u01/app/oracle/oradata/ogg1/example01.dbf
channel t1: restoring datafile 00006 to /u01/app/oracle/oradata/ogg1/chen01.dbf
channel t1: restoring datafile 00007 to /u01/app/oracle/oradata/ogg1/test01.dbf
channel t1: reading from backup piece /home/oracle/rmanbak/chen_0rqbha37_1_1.trc
channel t1: piece handle=/home/oracle/rmanbak/chen_0rqbha37_1_1.trc tag=TAG20150708T110926
channel t1: restored backup piece 1
channel t1: restore complete, elapsed time: 00:00:35
Finished restore at 08-JUL-15

Starting recover at 08-JUL-15
datafile 4 not processed because file is read-only

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 08-JUL-15
released channel: t1

RMAN> alter database open resetlogs;

database opened

SQL> select * from t_incarnation;
select * from t_incarnation
              *
ERROR at line 1:
ORA-00942: table or view does not exist

表不存在，說明恢復時間沒有掌握準確

六 進行incarnation穿越，找回資料
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area 1252663296 bytes
Fixed Size                  2212936 bytes
Variable Size             805309368 bytes
Database Buffers          436207616 bytes
Redo Buffers                8933376 bytes
Database mounted.

[oracle@ogg1 ~]$ rman target / nocatalog

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Jul 8 11:30:56 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: OGG1 (DBID=4055439112, not open)
using target database control file instead of recovery catalog

RMAN> list incarnation;

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       OGG1     4055439112       PARENT  1          15-AUG-09
2       2       OGG1     4055439112       PARENT  945184     06-APR-15
3       3       OGG1     4055439112       PARENT  1590817    07-JUL-15
4       4       OGG1     4055439112       PARENT  1600502    08-JUL-15
5       5       OGG1     4055439112       CURRENT 1600970    08-JUL-15


RMAN> reset database to incarnation 4;

database reset to incarnation 4

RMAN> list incarnation;

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       OGG1     4055439112       PARENT  1          15-AUG-09
2       2       OGG1     4055439112       PARENT  945184     06-APR-15
3       3       OGG1     4055439112       PARENT  1590817    07-JUL-15
4       4       OGG1     4055439112       CURRENT 1600502    08-JUL-15
5       5       OGG1     4055439112       ORPHAN  1600970    08-JUL-15


將資料庫恢復到建表之後，drop之前
RMAN> run{
2> allocate channel t1 type disk;
3> set until time "to_date('2015-07-08 11:14:00','yyyy-mm-dd hh24:mi:ss')";
4> restore database;
5> recover database;
6> }

allocated channel: t1
channel t1: SID=133 device type=DISK

executing command: SET until clause

Starting restore at 08-JUL-15

skipping datafile 4; already restored to file /u01/app/oracle/oradata/ogg1/users01.dbf
channel t1: starting datafile backup set restore
channel t1: specifying datafile(s) to restore from backup set
channel t1: restoring datafile 00001 to /u01/app/oracle/oradata/ogg1/system01.dbf
channel t1: restoring datafile 00002 to /u01/app/oracle/oradata/ogg1/sysaux01.dbf
channel t1: restoring datafile 00003 to /u01/app/oracle/oradata/ogg1/undotbs01.dbf
channel t1: restoring datafile 00005 to /u01/app/oracle/oradata/ogg1/example01.dbf
channel t1: restoring datafile 00006 to /u01/app/oracle/oradata/ogg1/chen01.dbf
channel t1: restoring datafile 00007 to /u01/app/oracle/oradata/ogg1/test01.dbf
channel t1: reading from backup piece /home/oracle/rmanbak/chen_0rqbha37_1_1.trc
channel t1: piece handle=/home/oracle/rmanbak/chen_0rqbha37_1_1.trc tag=TAG20150708T110926
channel t1: restored backup piece 1
channel t1: restore complete, elapsed time: 00:00:35
Finished restore at 08-JUL-15

Starting recover at 08-JUL-15
datafile 4 not processed because file is read-only

starting media recovery

archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/flash_recovery_area/OGG1/archivelog/2015_07_08/o1_mf_1_1_bss61qqs_.arc
archived log file name=/u01/app/oracle/flash_recovery_area/OGG1/archivelog/2015_07_08/o1_mf_1_1_bss61qqs_.arc thread=1 sequence=1
media recovery complete, elapsed time: 00:00:00
Finished recover at 08-JUL-15
released channel: t1

RMAN> alter database open resetlogs;

database opened

SQL> select * from t_incarnation;

        ID CC
---------- --------------------------------
         1 cross successful
         2 cross successful
         3 cross successful
穿越成功！！！

歡迎關注我的微信公眾號"IT小Chen"，共同學習，共同成長！！！