oracle 现实不完全,Oracle 不完全恢复

顾名思义，不完全恢复就是指

不完全的数据库恢复

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

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;