怎么进行基于sequence日志序号的恢复

2024-04-02 19:04:59 926人浏览薄情痞子

摘要

今天就跟大家聊聊有关怎么进行基于sequence日志序号的恢复，可能很多人都不太了解，为了让大家更加了解，小编给大家总结了以下内容，希望大家根据这篇文章可以有所收获。实验如下：

今天就跟大家聊聊有关怎么进行基于sequence日志序号的恢复，可能很多人都不太了解，为了让大家更加了解，小编给大家总结了以下内容，希望大家根据这篇文章可以有所收获。

实验如下：

--创建表ab:
SQL> create table ab as select to_char(sysdate,'yyyy-mm-dd hh34:mi:ss') as dd from dual;

Table created.

SQL> select * from v$log;

    GROUP#    THREAD# SEQUENCE#      BYTES BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME   NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------ ------------ ------------
         1          1          1   52428800        512          1 NO CURRENT                3933621 08-DEC-17      2.8147E+14
         2          1          0   52428800        512          1 YES UNUSED                       0                         0
         3          1          0   52428800        512          1 YES UNUSED                       0                         0

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
SQL>
SQL> alter system switch logfile;

System altered.

SQL> select * from v$log;

    GROUP#    THREAD# SEQUENCE#      BYTES BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME   NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------ ------------ ------------
         1          1          1   52428800        512          1 YES ACTIVE                 3933621 08-DEC-17         3934299 08-DEC-17
         2          1          2   52428800        512          1 NO CURRENT                3934299 08-DEC-17      2.8147E+14
         3          1          0   52428800        512          1 YES UNUSED                       0                         0

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   2
Current log sequence           2
SQL>

--备份数据库，热备：
RMAN> backup database;

Starting backup at 08-DEC-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00004 name=/u01/app/oracle/oradata/DBdb/users01.dbf
input datafile file number=00006 name=/u01/app/oracle/fast_recovery_area/DBDB/newback/ts_xxf_01.dbf
input datafile file number=00001 name=/u01/app/oracle/oradata/DBdb/system01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/DBdb/undotbs01.dbf
channel ORA_DISK_1: starting piece 1 at 08-DEC-17
channel ORA_DISK_1: finished piece 1 at 08-DEC-17
piece handle=/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/backupset/2017_12_08/o1_mf_nnndf_TAG20171208T063250_f2mjdm4b_.bkp tag=TAG20171208T063250 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:15
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00002 name=/u01/app/oracle/oradata/DBdb/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/DBdb/example01.dbf
channel ORA_DISK_1: starting piece 1 at 08-DEC-17
channel ORA_DISK_1: finished piece 1 at 08-DEC-17
piece handle=/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/backupset/2017_12_08/o1_mf_nnndf_TAG20171208T063250_f2mjgy6s_.bkp tag=TAG20171208T063250 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting 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 08-DEC-17
channel ORA_DISK_1: finished piece 1 at 08-DEC-17
piece handle=/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/backupset/2017_12_08/o1_mf_ncsnf_TAG20171208T063250_f2mjhrc4_.bkp tag=TAG20171208T063250 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 08-DEC-17

RMAN>

--创建表bc:
SQL> create table bc as select to_char(sysdate,'yyyy-mm-dd hh34:mi:ss') as dd from dual;

Table created.

SQL> alter system switch logfile;

System altered.

SQL> select * from v$log;

    GROUP#    THREAD# SEQUENCE#      BYTES BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME   NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------ ------------ ------------
         1          1          1   52428800        512          1 YES INACTIVE               3933621 08-DEC-17         3934299 08-DEC-17
         2          1          2   52428800        512          1 YES ACTIVE                 3934299 08-DEC-17         3934606 08-DEC-17
         3          1          3   52428800        512          1 NO CURRENT                3934606 08-DEC-17      2.8147E+14

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   3
Current log sequence           3
SQL>
SQL> select * from ab;

DD
-------------------
2017-12-08 06:32:00

SQL> select * from bc;

DD
-------------------
2017-12-08 06:35:09


--模拟删除表ab、bc:
SQL> drop table ab purge;

Table dropped.

SQL> drop table bc purge;

Table dropped.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup mount;
ORACLE instance started.

Total System Global Area 835104768 bytes
Fixed Size                  2257840 bytes
Variable Size             549456976 bytes
Database Buffers          281018368 bytes
Redo Buffers                2371584 bytes
Database mounted.
SQL>

--开始进行恢复操作：
v[oracle@wang ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Dec 8 06:39:20 2017

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

connected to target database: DBDB (DBID=3282897732, not open)

RMAN> run {
    set until sequence=2;
    restore database;
    recover database;
    alter database open resetlogs; }

executing command: SET until clause

Starting restore at 08-DEC-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 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/DBdb/system01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/DBdb/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/DBdb/users01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/fast_recovery_area/DBDB/newback/ts_xxf_01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/backupset/2017_12_08/o1_mf_nnndf_TAG20171208T061035_f2mh3w1s_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/backupset/2017_12_08/o1_mf_nnndf_TAG20171208T061035_f2mh3w1s_.bkp tag=TAG20171208T061035
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:02:25
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 00002 to /u01/app/oracle/oradata/DBdb/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/DBdb/example01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/backupset/2017_12_08/o1_mf_nnndf_TAG20171208T061035_f2mh6759_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/backupset/2017_12_08/o1_mf_nnndf_TAG20171208T061035_f2mh6759_.bkp tag=TAG20171208T061035
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 08-DEC-17

Starting recover at 08-DEC-17
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2017_12_08/o1_mf_1_1_f2mh8kkg_.arc
archived log file name=/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2017_12_08/o1_mf_1_1_f2mh8kkg_.arc thread=1 sequence=1
archived log file name=/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2017_12_08/o1_mf_1_1_f2mjco49_.arc thread=1 sequence=1
media recovery complete, elapsed time: 00:00:02
Finished recover at 08-DEC-17

database opened

RMAN>

--验证：
SQL>     select status from v$instance;

STATUS
------------
OPEN

SQL> select * from ab;

DD
-------------------
2017-12-08 06:32:00

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

结论：使用sequence进行不完全恢复时需要设置的sequence号为想要恢复的日志sequence+1。