resetlogs開啟資料庫後備份、歸檔日誌是否可用的測試 RMAN-06054

YallonKing發表於2012-04-10
資料庫
本測試驗證資料庫在resetlogs開啟後，之前的備份、日誌是否可用。

--首先測試備份是否可用
--檢視資料庫版本
SQL> select * from v$version where rownum<5;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
--插入測試資料
SQL> insert into test values(1,'before');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test;
        ID NAME
---------- --------------------
         1 before
--檢視當前scn
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
2912936147
--清空所有備份
RMAN> list backup of database summary;
specification does not match any backup in the repository
RMAN> list backup of archivelog all;
specification does not match any backup in the repository
--進行資料庫全備
RMAN> backup database;
Starting backup at 09-APR-12
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
using channel ORA_DISK_5
using channel ORA_DISK_6
... ...
rdn_.bkp tag=TAG20120409T231116 comment=NONE
channel ORA_DISK_3: backup set complete, elapsed time: 00:01:50
Finished backup at 09-APR-12
RMAN> list backup of database summary;

List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
119     B  F  A DISK        09-APR-12       1       1       YES        TAG20120409T231116
120     B  F  A DISK        09-APR-12       1       1       YES        TAG20120409T231116
123     B  F  A DISK        09-APR-12       1       1       YES        TAG20120409T231116
124     B  F  A DISK        09-APR-12       1       1       YES        TAG20120409T231116
125     B  F  A DISK        09-APR-12       1       1       YES        TAG20120409T231116
--檢視當前資料庫日誌序列
SQL> select SEQUENCE#,STATUS from v$log;
SEQUENCE# STATUS
---------- ----------------
        10 INACTIVE
        11 INACTIVE
        12 CURRENT
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     10
Next log sequence to archive   12
Current log sequence           12
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES
--插入備份後的測試資料2
SQL> insert into test values(2,'yallonking');
1 row created.
SQL> commit;
Commit complete.
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
2912937359
--建資料庫閃回到記錄1的狀態，並以restlogs開啟資料庫（模擬不完全恢復）
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area  422670336 bytes
Fixed Size                  1336960 bytes
Variable Size             331352448 bytes
Database Buffers           83886080 bytes
Redo Buffers                6094848 bytes
Database mounted.
SQL> flashback database to scn 2912936147;
Flashback complete.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs;
Database altered.
--檢視測試資料
SQL> conn test/test
Connected.
SQL> select * from test;
        ID NAME
---------- --------------------
         1 before
--檢視當前日誌序列
SQL> select SEQUENCE#,STATUS from v$log;
SEQUENCE# STATUS
---------- ----------------
         1 CURRENT
         0 UNUSED
         0 UNUSED
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     1
Next log sequence to archive   1
Current log sequence           1
--檢視當前歸檔日誌中的序列號變化
[oracle@test 2012_04_09]$ ll
total 16304
-rw-r----- 1 oracle oinstall 14886912 Apr  9 23:59 o1_mf_1_10_7r61vk5f_.arc
-rw-r----- 1 oracle oinstall   487936 Apr  9 23:47 o1_mf_1_11_7r614lr5_.arc
-rw-r----- 1 oracle oinstall   487936 Apr  9 23:59 o1_mf_1_11_7r61vl56_.arc
-rw-r----- 1 oracle oinstall   819712 Apr  9 23:59 o1_mf_1_12_7r61vl79_.arc
[oracle@test 2012_04_09]$ cd -
/tmp/TEST/archivelog/2012_04_10
[oracle@test 2012_04_10]$ ll
total 1532
-rw-r----- 1 oracle oinstall 1040384 Apr 10 00:07 o1_mf_1_1_7r6299vp_.arc
-rw-r----- 1 oracle oinstall  250368 Apr 10 00:08 o1_mf_1_1_7r62cx25_.arc
-rw-r----- 1 oracle oinstall  271872 Apr 10 00:10 o1_mf_1_1_7r62j5bk_.arc

--插入閃回後的測試資料3
SQL> insert into test values(3,'after');
1 row created.
SQL> commit;
Commit complete.
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
2912937130
--嘗試閃回到備份後的記錄2，但資料庫已經閃回到備份時的1，且將日誌序列重置，故此處無法獲得記錄2 的相關日誌資訊，故閃回不到記錄2。
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area  422670336 bytes
Fixed Size                  1336960 bytes
Variable Size             331352448 bytes
Database Buffers           83886080 bytes
Redo Buffers                6094848 bytes
Database mounted.
SQL>  flashback database to scn 2912937359;
flashback database to scn 2912937359
*
ERROR at line 1:
ORA-38743: Time/SCN is in the future of the database.
--嘗試閃回到資料庫已經閃回到1且在之後又進行了3的插入，因為閃回到1後，記錄3的日誌資訊還在沒有被重置，故此刻是可以閃回到3的。
SQL>  flashback database to scn 2912937130;
Flashback complete.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs;
Database altered.
SQL> select * from test.test;
        ID NAME
---------- --------------------
         1 before
         3 after
--再次嘗試閃回到備份記錄1成功。
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area  422670336 bytes
Fixed Size                  1336960 bytes
Variable Size             331352448 bytes
Database Buffers           83886080 bytes
Redo Buffers                6094848 bytes
Database mounted.
SQL>  flashback database to scn 2912936147;
Flashback complete.
SQL> alter database open resetlogs;
Database altered.
SQL> select * from test.test;
        ID NAME
---------- --------------------
         1 before

--下面是2種情況進行分析resetlogs後的和前的日誌是否可用的測試
--情況一
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area  422670336 bytes
Fixed Size                  1336960 bytes
Variable Size             331352448 bytes
Database Buffers           83886080 bytes
Redo Buffers                6094848 bytes
Database mounted.
RMAN> run{
2> set until sequence 12 thread 1;
3> recover database;
4> }
executing command: SET until clause
Starting recover at 10-APR-12
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
using channel ORA_DISK_5
using channel ORA_DISK_6
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 10-APR-12
SQL> alter database open resetlogs;
Database altered.
SQL> select * from test.test;
        ID NAME
---------- --------------------
         1 before
--此處發現resetlogs以前的歸檔日誌是可以使用的，但記錄2還是沒有恢復過來，初步斷定是當時日誌還是在redo中，沒有寫到歸檔中。
--下面重新進行測試
SQL> truncate table test.test;
Table truncated.
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
2912941477
SQL> select sequence#,status from v$log;
SEQUENCE# STATUS
---------- ----------------
         1 CURRENT
         0 UNUSED
         0 UNUSED
SQL> insert into test.test values(1,'oraking');
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> alter system archive log current;
System altered.
SQL> select sequence#,status from v$log;
SEQUENCE# STATUS
---------- ----------------
         4 INACTIVE
         5 INACTIVE
         6 CURRENT
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
2912941587
SQL> insert into test.test values(2,'yallonking');
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> alter system archive log current;
System altered.
SQL> select sequence#,status from v$log;
SEQUENCE# STATUS
---------- ----------------
        10 INACTIVE
        11 CURRENT
         9 INACTIVE
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
2912942137
SQL> insert into test.test values(3,'yallonking');
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> alter system archive log current;
System altered.
SQL> select sequence#,status from v$log;
SEQUENCE# STATUS
---------- ----------------
        16 CURRENT
        14 INACTIVE
        15 ACTIVE
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
2912942252
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area  422670336 bytes
Fixed Size                  1336960 bytes
Variable Size             343935360 bytes
Database Buffers           71303168 bytes
Redo Buffers                6094848 bytes
Database mounted.
SQL> flashback database to scn 2912942252;
Flashback complete.
SQL> alter database open resetlogs;
Database altered.
SQL> select sequence#,status from v$log;
SEQUENCE# STATUS
---------- ----------------
         1 CURRENT
         0 UNUSED
         0 UNUSED
SQL> select * from test.test;
        ID NAME
---------- --------------------
         1 oraking
         2 yallonking
         3 yallonking

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area  422670336 bytes
Fixed Size                  1336960 bytes
Variable Size             343935360 bytes
Database Buffers           71303168 bytes
Redo Buffers                6094848 bytes
Database mounted.
SQL> !
[oracle@test ~]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Tue Apr 10 02:30:34 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
connected to target database: TEST (DBID=2055832488, not open)
RMAN> run{
2> set until sequence 10 thread 1;
3> recover database;
4> }
executing command: SET until clause
Starting recover at 10-APR-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=22 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=23 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=24 device type=DISK
allocated channel: ORA_DISK_5
channel ORA_DISK_5: SID=25 device type=DISK
allocated channel: ORA_DISK_6
channel ORA_DISK_6: SID=26 device type=DISK
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 10-APR-12
SQL> alter database open resetlogs;
Database altered.
SQL> select * from test.test;
        ID NAME
---------- --------------------
         1 oraking
         2 yallonking
         3 yallonking
SQL> flashback database to scn 2912941587;
Flashback complete.
SQL> !
[oracle@test ~]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Tue Apr 10 02:37:20 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
connected to target database: TEST (DBID=2055832488, not open)
RMAN> run{
2> set until sequence 10 thread 1;
3> recover database;}
executing command: SET until clause
Starting recover at 10-APR-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=22 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=23 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=24 device type=DISK
allocated channel: ORA_DISK_5
channel ORA_DISK_5: SID=25 device type=DISK
allocated channel: ORA_DISK_6
channel ORA_DISK_6: SID=26 device type=DISK
starting media recovery
archived log for thread 1 with sequence 6 is already on disk as file /tmp/TEST/archivelog/2012_04_10/o1_mf_1_6_7r6bcs7b_.arc
archived log for thread 1 with sequence 7 is already on disk as file /tmp/TEST/archivelog/2012_04_10/o1_mf_1_7_7r6bctbj_.arc
archived log for thread 1 with sequence 8 is already on disk as file /tmp/TEST/archivelog/2012_04_10/o1_mf_1_8_7r6bcx9t_.arc
archived log for thread 1 with sequence 9 is already on disk as file /tmp/TEST/archivelog/2012_04_10/o1_mf_1_9_7r6bcy2w_.arc
archived log for thread 1 with sequence 10 is already on disk as file /tmp/TEST/archivelog/2012_04_10/o1_mf_1_10_7r6bd34t_.arc
archived log for thread 1 with sequence 11 is already on disk as file /tmp/TEST/archivelog/2012_04_10/o1_mf_1_11_7r6bg47x_.arc
archived log for thread 1 with sequence 12 is already on disk as file /tmp/TEST/archivelog/2012_04_10/o1_mf_1_12_7r6bg58h_.arc
archived log for thread 1 with sequence 13 is already on disk as file /tmp/TEST/archivelog/2012_04_10/o1_mf_1_13_7r6bg8fs_.arc
archived log for thread 1 with sequence 14 is already on disk as file /tmp/TEST/archivelog/2012_04_10/o1_mf_1_14_7r6bmpom_.arc
archived log for thread 1 with sequence 15 is already on disk as file /tmp/TEST/archivelog/2012_04_10/o1_mf_1_15_7r6bmppk_.arc
archived log for thread 1 with sequence 16 is already on disk as file /tmp/TEST/archivelog/2012_04_10/o1_mf_1_16_7r6bmpnf_.arc
archived log for thread 1 with sequence 1 is already on disk as file /tmp/TEST/archivelog/2012_04_10/o1_mf_1_1_7r6bthx9_.arc
archived log file name=/tmp/TEST/archivelog/2012_04_10/o1_mf_1_6_7r6bcs7b_.arc thread=1 sequence=6
archived log file name=/tmp/TEST/archivelog/2012_04_10/o1_mf_1_7_7r6bctbj_.arc thread=1 sequence=7
archived log file name=/tmp/TEST/archivelog/2012_04_10/o1_mf_1_8_7r6bcx9t_.arc thread=1 sequence=8
archived log file name=/tmp/TEST/archivelog/2012_04_10/o1_mf_1_9_7r6bcy2w_.arc thread=1 sequence=9
archived log file name=/tmp/TEST/archivelog/2012_04_10/o1_mf_1_10_7r6bd34t_.arc thread=1 sequence=10
archived log file name=/tmp/TEST/archivelog/2012_04_10/o1_mf_1_11_7r6bg47x_.arc thread=1 sequence=11
archived log file name=/tmp/TEST/archivelog/2012_04_10/o1_mf_1_12_7r6bg58h_.arc thread=1 sequence=12
archived log file name=/tmp/TEST/archivelog/2012_04_10/o1_mf_1_13_7r6bg8fs_.arc thread=1 sequence=13
archived log file name=/tmp/TEST/archivelog/2012_04_10/o1_mf_1_14_7r6bmpom_.arc thread=1 sequence=14
archived log file name=/tmp/TEST/archivelog/2012_04_10/o1_mf_1_15_7r6bmppk_.arc thread=1 sequence=15
archived log file name=/tmp/TEST/archivelog/2012_04_10/o1_mf_1_16_7r6bmpnf_.arc thread=1 sequence=16
archived log file name=/tmp/TEST/archivelog/2012_04_10/o1_mf_1_1_7r6bthx9_.arc thread=1 sequence=1
unable to find archived log
archived log thread=1 sequence=1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 04/10/2012 02:37:59
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 1 and starting SCN of 2912942650
RMAN> quit
以上說明:時刻A插入資料，進行到時刻C，閃回至時刻B，則A->B間的日誌不可用，也就是不能進行不完全恢復到A和B之間的某個時刻。

--情況二
SQL> truncate table test.test;
Table truncated.
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
2912943400
SQL> select sequence#,status from v$log;
SEQUENCE# STATUS
---------- ----------------
         1 CURRENT
         0 UNUSED
         0 UNUSED
SQL> insert into test.test values(1,'oraking');
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> alter system archive log current;
System altered.
SQL> select sequence#,status from v$log;
SEQUENCE# STATUS
---------- ----------------
         4 INACTIVE
         5 INACTIVE
         6 CURRENT
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
2912943445
SQL> insert into test.test values(2,'yallonking');
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> alter system archive log current;
System altered.
SQL> select sequence#,status from v$log;
SEQUENCE# STATUS
---------- ----------------
        10 INACTIVE
        11 CURRENT
         9 INACTIVE
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
2912943484

SQL> insert into test.test values(3,'king');
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> alter system archive log current;
System altered.
SQL> select sequence#,status from v$log;
SEQUENCE# STATUS
---------- ----------------
        16 INACTIVE
        17 INACTIVE
        18 CURRENT
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
2912943534
SQL> select * from test.test;
        ID NAME
---------- --------------------
         1 oraking
         2 yallonking
         3 king
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area  422670336 bytes
Fixed Size                  1336960 bytes
Variable Size             356518272 bytes
Database Buffers           58720256 bytes
Redo Buffers                6094848 bytes
Database mounted.
SQL> flashback database to scn 2912943445;
Flashback complete.
SQL> !
[oracle@test ~]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Tue Apr 10 03:01:36 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
connected to target database: TEST (DBID=2055832488, not open)RMAN> run{
2> set until sequence 10 thread 1;
3> recover database;
4> }
executing command: SET until clause
Starting recover at 10-APR-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=22 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=23 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=24 device type=DISK
allocated channel: ORA_DISK_5
channel ORA_DISK_5: SID=25 device type=DISK
allocated channel: ORA_DISK_6
channel ORA_DISK_6: SID=26 device type=DISK
starting media recovery
archived log for thread 1 with sequence 6 is already on disk as file /tmp/TEST/archivelog/2012_04_10/o1_mf_1_6_7r6dcdn2_.arc
archived log for thread 1 with sequence 7 is already on disk as file /tmp/TEST/archivelog/2012_04_10/o1_mf_1_7_7r6dcfgs_.arc
archived log for thread 1 with sequence 8 is already on disk as file /tmp/TEST/archivelog/2012_04_10/o1_mf_1_8_7r6dcgko_.arc
archived log for thread 1 with sequence 9 is already on disk as file /tmp/TEST/archivelog/2012_04_10/o1_mf_1_9_7r6dcjdd_.arc
archived log file name=/tmp/TEST/archivelog/2012_04_10/o1_mf_1_6_7r6dcdn2_.arc thread=1 sequence=6
archived log file name=/tmp/TEST/archivelog/2012_04_10/o1_mf_1_7_7r6dcfgs_.arc thread=1 sequence=7
archived log file name=/tmp/TEST/archivelog/2012_04_10/o1_mf_1_8_7r6dcgko_.arc thread=1 sequence=8
archived log file name=/tmp/TEST/archivelog/2012_04_10/o1_mf_1_9_7r6dcjdd_.arc thread=1 sequence=9
media recovery complete, elapsed time: 00:00:00
Finished recover at 10-APR-12
RMAN> quit

Recovery Manager complete.
[oracle@test ~]$ exit
exit
SQL> alter database open resetlogs;
Database altered.
SQL> select * from test.test;
        ID NAME
---------- --------------------
         1 oraking
         2 yallonking
--以上說明當資料庫閃回到之前的A時刻後，A之後的日誌也是可以繼續使用的。
總結：當資料庫以resetlogs開啟資料庫後，日誌序列重置，之前的備份仍然可用，但備份之後的日誌需要分情況討論。
 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26143577/viewspace-720847/，如需轉載，請註明出處，否則將追究法律責任。

相關文章