oracle row_scn,oracle基于scn的不完全恢复

下面做一个实验，使用scn进行不完全恢复(做之前要确保数据库有有效备份)：

首先在hh用户下创建一张表h2，在表中插入数据：

SQL> create table h2(id int);

Table created.

SQL> insert into h2 values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> insert into h2 values(2);

1 row created.

SQL> select * from h2;

ID

----------

1

2

然后记录下此时的scn：

SQL> conn /as sysdba

Connected.

SQL> select dbms_flashback.get_system_change_number scn from dual;

SCN

----------

850798

接着删除表h2:

SQL> conn hh/hh

Connected.

SQL> drop table h2;

Table dropped.

现在使用rman将数据库恢复到h2被删除之前：

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

[oracle@localhost ~]$ rman target / nocatalog

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Aug 22 23:49:41 2014

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

connected to target database (not started)

RMAN> startup mount;

Oracle instance started

database mounted

Total System Global Area     849530880 bytes

Fixed Size                     1339824 bytes

Variable Size                553651792 bytes

Database Buffers             289406976 bytes

Redo Buffers                   5132288 bytes

RMAN> restore database until scn 850798

2> ;

Starting restore at 22-AUG-14

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=18 device type=DISK

skipping datafile 6; already restored to file /u01/app/datafile/test01.dbf

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 00003 to /u01/app/oracle/oradata/orcl/undotbs01.dbf

channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/orcl/example01.dbf

channel ORA_DISK_1: reading from backup piece /u01/app/backup/fulldb_0lpgkfcr_1_1

channel ORA_DISK_1: piece handle=/u01/app/backup/fulldb_0lpgkfcr_1_1 tag=FULLDB

channel ORA_DISK_1: restored backup piece 1

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

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/orcl/sysaux01.dbf

channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/orcl/users01.dbf

channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/orcl/hh01.dbf

channel ORA_DISK_1: reading from backup piece /u01/app/backup/fulldb_0kpgkfcr_1_1

channel ORA_DISK_1: piece handle=/u01/app/backup/fulldb_0kpgkfcr_1_1 tag=FULLDB

channel ORA_DISK_1: restored backup piece 1

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

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/orcl/system01.dbf

channel ORA_DISK_1: reading from backup piece /u01/app/backup/fulldb_0jpgkfcr_1_1

channel ORA_DISK_1: piece handle=/u01/app/backup/fulldb_0jpgkfcr_1_1 tag=FULLDB

channel ORA_DISK_1: restored backup piece 1

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

Finished restore at 22-AUG-14

RMAN> recover database until scn 850798

2> ;

Starting recover at 22-AUG-14

using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 11 is already on disk as file /u01/app/archivelog/dest1/1_11_856205535.dbf

archived log for thread 1 with sequence 1 is already on disk as file /u01/app/archivelog/dest1/1_1_856304357.dbf

archived log for thread 1 with sequence 2 is already on disk as file /u01/app/archivelog/dest1/1_2_856304357.dbf

archived log file name=/u01/app/archivelog/dest1/1_11_856205535.dbf thread=1 sequence=11

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

Finished recover at 22-AUG-14

使用resetlogs打开数据库：

SQL> alter database open resetlogs;

Database altered.

SQL> select * from hh.h2;

ID

----------

1

2

表h2已经回来了。