Oracle 不完全恢复

来源：这里教程网时间：2026-03-03 16:25:37 作者：

顾名思义，不完全恢复就是指 不完全的数据库恢复 。不完全恢复与完全恢复在许多方面是相同的，它们基本的命令集相同，但不完全恢复添加了一些其它命令。引起不完全恢复的原因有很多，如丢失了联机重做日志或归档的重做日志，或者出现重大的用户错误。

RMAN不完全恢复的三个标准模式：基于time、基于scn和基于sequence：

1.基于scn

    startup mount;
    restore database until SCN 10000;
    recover database until SCN 10000;
        
alter database open resetlogs;




2.基于sequence



    startup mount;
    restore database until sequence 100 thread 1;
    recover database until sequence 100 thread 1;




    alter database open resetlogs;



3.基于时间

  
  startup mount；
    restore database until time
    “to_date('01/19/16 13:00:00','MM/DD/YY HH24:MI:SS')";
    recover database until time
    "to_date('01/19/16 13:00:00','MM/DD/YY HH24:MI:SS')";
    alter database open resetlogs;



或



    run
    {
    set until time "to_date('01/19/16 15:00:00','mm/dd/yy hh24:mi:ss')";
    restore database;
    recover database;
    alter database open resetlogs;
    }







基于时间实际测试：

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;