ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > Linux操作系统 > Linux操作系统 > resetlogs打开数据库后备份、归档日志是否可用的测试 RMAN-06054

resetlogs打开数据库后备份、归档日志是否可用的测试 RMAN-06054

Linux操作系统 作者：拾翠暮忘归 时间：2014-06-05 14:14:53 0 删除 编辑
本测试验证数据库在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博客 ” ，链接：https://blog.itpub.net/29602308/viewspace-1176213/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： oracle的resetlogs机制
下一篇： RMAN-06059: expected archived log not found
请登录后发表评论 登录
全部评论
拾翠暮忘归

注册时间：2014-04-11

  • 博文量
    24
  • 访问量
    91785

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593