Oracle 不完全恢復

顧名思義，不完全恢復就是指 不完全的資料庫恢復 。不完全恢復與完全恢復在許多方面是相同的，它們基本的命令集相同，但不完全恢復新增了一些其它命令。引起不完全恢復的原因有很多，如丟失了聯機重做日誌或歸檔的重做日誌，或者出現重大的使用者錯誤。

RMAN不完全恢復的三個標準模式：基於time、基於scn和基於sequence：

1.基於scn

        alter database open resetlogs;

2.基於sequence

    alter database open resetlogs;

3.基於時間

    startup mount；

或

基於時間實際測試：

1 、測試表 t1

SQL> select * from sa.t1;

        ID NAME

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

         2 aa

 

2 、備份資料庫和控制檔案

RMAN> backup database;

Starting backup at 23-JUN-16

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=32 device type=DISK

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00003 name=/oradata/his/undotbs01.dbf

input datafile file number=00004 name=/oradata/his/users01.dbf

input datafile file number=00001 name=/oradata/his/system01.dbf

input datafile file number=00002 name=/oradata/his/sysaux01.dbf

channel ORA_DISK_1: starting piece 1 at 23-JUN-16

channel ORA_DISK_1: finished piece 1 at 23-JUN-16

piece handle=/oracle/app/oracle/product/11.2.0/db_1/dbs/0mr8s8mr_1_1 tag=TAG20160623T134459 comment=NONE

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

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

channel ORA_DISK_1: starting piece 1 at 23-JUN-16

channel ORA_DISK_1: finished piece 1 at 23-JUN-16

piece handle=/oracle/app/oracle/product/11.2.0/db_1/dbs/0nr8s8nu_1_1 tag=TAG20160623T134459 comment=NONE

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

Finished backup at 23-JUN-16

RMAN>

 

RMAN> backup current controlfile;

Starting backup at 23-JUN-16

using channel ORA_DISK_1

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

channel ORA_DISK_1: starting piece 1 at 23-JUN-16

channel ORA_DISK_1: finished piece 1 at 23-JUN-16

piece handle=/oracle/app/oracle/product/11.2.0/db_1/dbs/0or8s8o9_1_1 tag=TAG20160623T134545 comment=NONE

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

Finished backup at 23-JUN-16

 

3 、檢視當前時間點

SQL> select to_char(sysdate,'hh24:mi:ss') from dual;

TO_CHAR(

--------

13:46:21

 

 

4 、刪除測試資料

SQL> select * from sa.t1;

 

        ID NAME

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

         2 aa

 

SQL> delete from sa.t1;

1 row deleted.

 

SQL> commit;

Commit complete.

 

 

關閉資料庫，假裝資料庫起不來了。

5 、開始基於時間點的不完全恢復

[oracle@test oradata]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Jun 23 13:47:30 2016

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

connected to target database (not started)

 

nomount

RMAN> startup nomount

Oracle instance started

Total System Global Area     363266048 bytes

 

Fixed Size                     2253304 bytes

Variable Size                171970056 bytes

Database Buffers             184549376 bytes

Redo Buffers                   4493312 bytes

5.1 恢復控制檔案

RMAN> restore controlfile from '/oracle/app/oracle/product/11.2.0/db_1/dbs/0or8s8o9_1_1';

 

Starting restore at 23-JUN-16

using channel ORA_DISK_1

 

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

output file name=/oradata/his/control01.ctl

Finished restore at 23-JUN-16

 

5 、 2 開始正式恢復

 

RMAN> alter database mount;

 

database mounted

released channel: ORA_DISK_1

 

RMAN> run{

2> set until time "to_date('2016-06-23 13:46:21','yyyy-mm-dd hh24:mi:ss')";

3> restore database;

4> recover database;

5> }

 

executing command: SET until clause

 

Starting restore at 23-JUN-16

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=19 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 /oradata/his/system01.dbf

channel ORA_DISK_1: restoring datafile 00002 to /oradata/his/sysaux01.dbf

channel ORA_DISK_1: restoring datafile 00003 to /oradata/his/undotbs01.dbf

channel ORA_DISK_1: restoring datafile 00004 to /oradata/his/users01.dbf

channel ORA_DISK_1: reading from backup piece /oracle/app/oracle/product/11.2.0/db_1/dbs/0mr8s8mr_1_1

channel ORA_DISK_1: piece handle=/oracle/app/oracle/product/11.2.0/db_1/dbs/0mr8s8mr_1_1 tag=TAG20160623T134459

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:01:05

Finished restore at 23-JUN-16

 

Starting recover at 23-JUN-16

using channel ORA_DISK_1

 

starting media recovery

 

archived log for thread 1 with sequence 1 is already on disk as file /oradata/his/redo01a.log

archived log file name=/oradata/his/redo01a.log thread=1 sequence=1

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

Finished recover at 23-JUN-16

 

RMAN>

 

只讀開啟驗證資料

SQL> alter database open read only;

Database altered.

 

SQL> select * from sa.t1;

        ID NAME

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

         2 aa

SQL>

 

 

如果要開啟的話

shutdown immediate

startup mount

alter database open resetlogs;