您的位置:首页 > 博客中心 > 数据库 >

ORACLE跨越时间点的恢复

时间:2022-03-13 23:04

在oracle10g之前使用resetlogs打开数据库之后,之前的的备份就不能用于恢复了。即不能进行跨resetlogs时间点的恢复。所以要求执行完之后马上进行全库备份。

Oracle10g以后允许跨越resetlogs时间点进行完全或者不完全恢复。实验如下:

  1. 执行全库备份。

    RMAN> backup database plus archivelog delete all input;

    insert into test select * from test;

    SQL> alter system switch logfile;

    SQL> commit;

    Commit complete.

    SQL> alter system switch logfile;

    System altered.

    SQL> truncate table test;

    Table truncated.

    SQL> alter system switch logfile;

    System altered.

    SQL> select * from v$log_history;

    SQL> select recid,stamp,sequence#,first_change#,first_time,next_change# from v$log_history

    2 where recid >80;

    RECIDSTAMP FIRST_CHANGE# FIRST_TIME NEXT_CHANGE#

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

    81 856723148 2130643 27-AUG-14 2130889

    82 856752323 2130889 27-AUG-14 2155374

    83 856753004 2155375 28-AUG-14 2175380

    84 856753636 2175380 28-AUG-14 2176030

    85 856754916 2176030 28-AUG-14 2177334

    86 856754945 2177334 28-AUG-14 2177347

    87 856755046 2177347 28-AUG-14 2178414

    88 856755142 2178414 28-AUG-14 2178471

    89 856755199 2178471 28-AUG-14 2178508

    90 856755243 2178508 28-AUG-14 2178543

    91 856755424 2178543 28-AUG-14 2178823

    RECIDSTAMP FIRST_CHANGE# FIRST_TIME NEXT_CHANGE#

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

    92 856755456 2178823 28-AUG-14 2178836

    93 856755483 2178836 28-AUG-14 2178940

    13 rows selected.

  2. 关闭数据库、删除数据模拟故障

    删除之后,执行如下

    RMAN> startup mount;

    Oracle instance started

    database mounted

    Total System Global Area 599785472 bytes

    Fixed Size 2022600 bytes

    Variable Size 268436280 bytes

    Database Buffers 322961408 bytes

    Redo Buffers 6365184 bytes

    RMAN> run{

    set until sequence 12 thread 1;

    restore database;

    recover database;

    }

    这里判断具体到那个sequence,当前查询如下,历史查看v$log_history的sequence#:

    Sql>archive log list;

    如果出现这个错误:

    SQL> recover database until cancel;

    ORA-00283: recovery session canceled due to errors

    ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

    SQL> recover database BACKUP CONTROLFILE

    ORA-00905: missing keyword

    解决:

    recover database using backup controlfile until cancel;

    RMAN> alter database open resetlogs;

    查看序号是否复位:

    SQL> archive log list;

    Database log mode Archive Mode

    Automatic archival Enabled

    Archive destination USE_DB_RECOVERY_FILE_DEST

    Oldest online log sequence 0

    Next log sequence to archive 1

    Current log sequence 1

    SQL> create conn xzsp create tt able tt as select * from dba_users;

    Table created.

    SQL> alter system switch logfile;

    System altered.

    SQL> select count(*) from tt;

    COUNT(*)

    ----------

    26

    SQL> inert in sert into tt select * from tt;

    26 rows created.

    SQL> /

    52 rows created.

    SQL> /

    104 rows created.

    SQL> commit

    2 ;

    Commit complete.

    SQL> alter system switch logfile;

    System altered.

    SQL> select recid,stamp,first_change#,first_time,next_change# from v$log_history;

    RECID STAMP SEQUENCE# FIRST_CHANGE# FIRST_TIME NEXT_CHANGE#

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

    100 856759449 1 2179277 28-AUG-14 2179707

    101 856759476 2 2179707 28-AUG-14 2179788

    102 856761071 1 2179621 28-AUG-14 2179920

    103 856761108 2 2179920 28-AUG-14 2179945

    103 rows selected.

    Oracle保留了resetlogs之前的日志序列号,并且日志recid继续增长,控制文件也保留了归档日志的序列。所以可以跨RESETLOGS进行恢复。10g以前 是不能够使用当前控制文件恢复之前的备份的。

    RMAN> run {

    2> restore database;

    3> recover database;

    4> }

    SQL> select count(*) from tt;

    COUNT(*)

    ----------

    208

    恢复能够完成,还有log_archive_format有关系。

    Sql>show parameter log_archive_format

    log_archive_format string archive_%t_%s_%r.log

    这里的%R是oracle新增的参数,是resetlogs的标志号.这一归档日志格式可以是不同Incarnation的数据库归档日志避免相互覆盖,从而跨越resetlogs恢复的日志基础得以保证。

    ?

    ?

    思考:这里看到rman备份只有一次,是在resetlogs之前,第二次完全恢复是基于之前的备份。这里看到oracle10确实是越过了resetlog执行了恢复。

热门排行

今日推荐

热门手游