有關resetlogs和incarnation關係的理解

一、實驗環境：

Sqldeveloper+linux in virtual machine；

[oracle@lzc ~]$ lsb_release -a

LSB Version:    :core-3.1-ia32:core-3.1-noarch:graphics-3.1-ia32:graphics-3.1-noarch

Distributor ID: RedHatEnterpriseServer

Description:    Red Hat Enterprise Linux Server release 5.4 (Tikanga)

Release:        5.4

Codename:       Tikanga

資料庫版本：

SQL> select * from v$version;

 

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQL Release 10.2.0.1.0 - Production

CORE    10.2.0.1.0      Production

TNS for Linux: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 - Production

歸檔狀態：

SQL> archive log;

SP2-0716: invalid combination of ARCHIVE LOG options

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

 

二、實驗思想：

對資料庫採用rman設定進行 一次備份，然後採用resetlog方式不完全恢復到備份的一個時間點。完成之後再進行第二次不完全恢復，驗證是否能夠成功。

 

三、實驗設計

1、先將資料庫進行一次全備連同current controlfile

2、建立測試表，並新增記錄

3、進行第一次不完全恢復

5、第二次不完全恢復

 

四、實驗步驟：

       1、進行一次資料庫全備

RMAN> backup database;

 

Starting backup at 04-AUG-12

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=153 devtype=DISK

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00005 name=/u01/app/oracle/oradata/dblzc/soe.dbf

input datafile fno=00001 name=/u01/app/oracle/oradata/dblzc/system01.dbf

input datafile fno=00003 name=/u01/app/oracle/oradata/dblzc/sysaux01.dbf

input datafile fno=00002 name=/u01/app/oracle/oradata/dblzc/undotbs01.dbf

input datafile fno=00004 name=/u01/app/oracle/oradata/dblzc/users01.dbf

channel ORA_DISK_1: starting piece 1 at 04-AUG-12

channel ORA_DISK_1: finished piece 1 at 04-AUG-12

piece handle=/u01/app/oracle/flash_recovery_area/DBLZC/backupset/2012_08_04/o1_mf_nnndf_TAG20120804T222751_81tdc8by_.bkp tag=TAG20120804T222751 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:05:42

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

including current control file in backupset

including current SPFILE in backupset

channel ORA_DISK_1: starting piece 1 at 04-AUG-12

channel ORA_DISK_1: finished piece 1 at 04-AUG-12

piece handle=/u01/app/oracle/flash_recovery_area/DBLZC/backupset/2012_08_04/o1_mf_ncsnf_TAG20120804T222751_81tdp00w_.bkp tag=TAG20120804T222751 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:05

Finished backup at 04-AUG-12

 

2、建立測試表

SQL> create table test(tscn number(10) primary key);

插入資料：

在生氣了developer中執行以下語句（注意：該語句在sys使用者下執行）：

begin

for I in 1..10

LOOP

insert into hr.test values(DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER);

commit;

end LOOP;

end;

/

檢視資料：

SQL> select * from hr.test;

 

      TSCN

----------

   1224305

   1224306

   1224307

   1224308

   1224309

   1224310

   1224311

   1224312

   1224313

   1224314

 

10 rows selected.

 

3、進行第一次不完全恢復，現在假設我準備恢復到tscn 1224312這一點結果卻不小心恢復到1224308這一點。

在執行恢復以前檢視當前資料庫的incarnation：

RMAN> list incarnation;

 

using target database control file instead of recovery catalog

 

List of Database Incarnations

DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time

------- ------- -------- ---------------- --- ---------- ----------

1       1       DBLZC    1821208303       PARENT  446075     24-JUN-12

2       2       DBLZC    1821208303       CURRENT 1093399    02-AUG-12

執行恢復

RMAN> run{

2> startup mount;

3> set until scn 1224308;

4> restore database;

5> recover database;

6> alter database open resetlogs;

7> }

connected to target database (not started)

Oracle instance started

database mounted

 

Total System Global Area     285212672 bytes

 

Fixed Size                     1218992 bytes

Variable Size                 83887696 bytes

Database Buffers             197132288 bytes

Redo Buffers                   2973696 bytes

 

executing command: SET until clause

 

Starting restore at 04-AUG-12

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=157 devtype=DISK

 

channel ORA_DISK_1: starting datafile backupset restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

restoring datafile 00001 to /u01/app/oracle/oradata/dblzc/system01.dbf

restoring datafile 00002 to /u01/app/oracle/oradata/dblzc/undotbs01.dbf

restoring datafile 00003 to /u01/app/oracle/oradata/dblzc/sysaux01.dbf

restoring datafile 00004 to /u01/app/oracle/oradata/dblzc/users01.dbf

restoring datafile 00005 to /u01/app/oracle/oradata/dblzc/soe.dbf

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/DBLZC/backupset/2012_08_04/o1_mf_nnndf_TAG20120804T222751_81tdc8by_.bkp

channel ORA_DISK_1: restored backup piece 1

piece handle=/u01/app/oracle/flash_recovery_area/DBLZC/backupset/2012_08_04/o1_mf_nnndf_TAG20120804T222751_81tdc8by_.bkp tag=TAG20120804T222751

channel ORA_DISK_1: restore complete, elapsed time: 00:04:31

Finished restore at 04-AUG-12

 

Starting recover at 04-AUG-12

using channel ORA_DISK_1

 

starting media recovery

media recovery complete, elapsed time: 00:00:05

 

Finished recover at 04-AUG-12

 

database opened

這時再檢視test表中資料：

SQL> select * from hr.test;

 

      TSCN

----------

   1224305

   1224306

只有1224308該點以前的記錄

 

再次檢視當前資料庫的incarnation：

RMAN> list incarnation;

 

 

List of Database Incarnations

DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time

------- ------- -------- ---------------- --- ---------- ----------

1       1       DBLZC    1821208303       PARENT  446075     24-JUN-12

2       2       DBLZC    1821208303       PARENT  1093399    02-AUG-12

3       3       DBLZC    1821208303       CURRENT 1224309    04-AUG-12

可以發現比恢復前多了一條記錄，且incarnation 3為當前使用的database incarnation；

 

現在我想再次恢復到我最初想恢復的1224312這點以前，再次執行

RMAN> backup current controlfile;

RMAN> shutdown immediate;

 

database closed

database dismounted

Oracle instance shut down

RMAN> run{      

2> startup mount;     

3> set until scn 1224312;

4> restore database;   

5> recover database;

6> alter database open resetlogs;

7> }

完成之後，查詢test表中資料：

SQL> select * from hr.test;

 

      TSCN

----------

   1224305

   1224306

仍然沒有恢復我想要的資料。

現在再看incarnation的記錄：

RMAN> list incarnation;

 

 

List of Database Incarnations

DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time

------- ------- -------- ---------------- --- ---------- ----------

1       1       DBLZC    1821208303       PARENT  446075     24-JUN-12

2       2       DBLZC    1821208303       PARENT  1093399    02-AUG-12

3       3       DBLZC    1821208303       PARENT  1224309    04-AUG-12

4       4       DBLZC    1821208303       CURRENT 1224322    05-AUG-12

發現又多了一條記錄；

現在我將資料庫reset  to incarnation 2，再進行以上恢復：

RMAN> backup current controlfile;

RMAN> shutdown immediate;

RMAN> startup mount;

RMAN> reset database to incarnation 2;

 

database reset to incarnation 2

 

RMAN> run{

2> set until scn 1224312;

3> restore database;

4> recover database;

5> alter database open resetlogs;

6> }

恢復成功之後再次檢視test表中的內容：

SQL> select * from hr.test;

 

      TSCN

----------

   1224305

   1224306

   1224307

   1224308

   1224309

   1224310

 

6 rows selected.

恢復成功！

檢視 incarnation 記錄：

List of Database Incarnations

DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time

------- ------- -------- ---------------- --- ---------- ----------

1       1       DBLZC    1821208303       PARENT  446075     24-JUN-12

2       2       DBLZC    1821208303       PARENT  1093399    02-AUG-12

3       3       DBLZC    1821208303       ORPHAN  1224309    04-AUG-12

5       5       DBLZC    1821208303       CURRENT 1224313    05-AUG-12

4       4       DBLZC    1821208303       ORPHAN  1224322    05-AUG-12

發現又多了一條記錄，且該記錄為the current incarnation of the target database;

 

五、實驗小結

每次當你在rman或者sqlplus中執行ALTER DATABASE OPEN RESETLOGS 這條命令之後就會為資料庫自動建立一個新的incarnation在recovery catalog中，並且將the new incarnation置為current incarnation。

隨後對資料庫所做的備份和歸檔等操作都只與the new incarnation 相關。可以通過檢視V$DATABASE_INCARNATION 讀取new incarnation的記錄。

在reset database to incarnation #；這條命令之後若current control file is not available則需要

Restore a control file from the old incarnation。