Oracle基于时间点的恢复

Oracle基于时间点的恢复，在数据库日常维护中，对于处理用户误删除操作等有很好的恢复作用，本实验就是对数据库基于时间点恢复的测试。
[oracle@node0 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Jul 8 15:35:33 2014

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> show parameter instance_name;
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0


SQL> startup
ORACLE instance started.

Total System Global Area  849530880 bytes
Fixed Size                  1339824 bytes
Variable Size             511708752 bytes
Database Buffers          331350016 bytes
Redo Buffers                5132288 bytes
Database mounted.
Database opened.
首先对数据库进行一个0级备份
RMAN> list backup;

using target database control file instead of recovery catalog
specification does not match any backup in the repository

RMAN> run
2> {
3> allocate channel d1 type disk;
4> allocate channel d2 type disk;
5> backup incremental level=0 database include current controlfile plus archivelog all;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "all": expecting one of: "auxiliary, channel, delete, diskratio, filesperset, force, format, from, include, keep, maxsetsize, noexclude, nokeep, not, pool, reuse, section, skip readonly, skip, tag, to, ;"
RMAN-01007: at line 5 column 81 file: standard input

RMAN> run
2> {
3> allocate channel d1 type disk;
4> allocate channel d2 type disk;
5> backup incremental level=0 database include current controlfile plus archivelog;
6> release channel d1;
7> release channel d2;
8> }

allocated channel: d1
channel d1: SID=44 device type=DISK

allocated channel: d2
channel d2: SID=47 device type=DISK


Starting backup at 08-JUL-14
current log archived
channel d1: starting archived log backup set
channel d1: specifying archived log(s) in backup set
input archived log thread=1 sequence=3 RECID=1 STAMP=852391155
channel d1: starting piece 1 at 08-JUL-14
channel d2: starting archived log backup set
channel d2: specifying archived log(s) in backup set
input archived log thread=1 sequence=4 RECID=2 STAMP=852391353
channel d2: starting piece 1 at 08-JUL-14
channel d1: finished piece 1 at 08-JUL-14
piece handle=/u01/app/oracle/flash_recovery_area/HNJY/backupset/2014_07_08/o1_mf_annnn_TAG20140708T152233_9vq71tfm_.bkp tag=TAG20140708T152233 comment=NONE
channel d1: backup set complete, elapsed time: 00:00:04
channel d2: finished piece 1 at 08-JUL-14
piece handle=/u01/app/oracle/flash_recovery_area/HNJY/backupset/2014_07_08/o1_mf_annnn_TAG20140708T152233_9vq71thd_.bkp tag=TAG20140708T152233 comment=NONE
channel d2: backup set complete, elapsed time: 00:00:04
Finished backup at 08-JUL-14

Starting backup at 08-JUL-14
channel d1: starting incremental level 0 datafile backup set
channel d1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/hnjy/system01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/hnjy/users01.dbf
channel d1: starting piece 1 at 08-JUL-14
channel d2: starting incremental level 0 datafile backup set
channel d2: specifying datafile(s) in backup set
input datafile file number=00002 name=/u01/app/oracle/oradata/hnjy/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/hnjy/example01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/hnjy/undotbs01.dbf
channel d2: starting piece 1 at 08-JUL-14
channel d1: finished piece 1 at 08-JUL-14
piece handle=/u01/app/oracle/flash_recovery_area/HNJY/backupset/2014_07_08/o1_mf_nnnd0_TAG20140708T152238_9vq71ywo_.bkp tag=TAG20140708T152238 comment=NONE
channel d1: backup set complete, elapsed time: 00:02:41
channel d1: starting incremental level 0 datafile backup set
channel d1: specifying datafile(s) in backup set
including current control file in backup set
channel d1: starting piece 1 at 08-JUL-14
channel d2: finished piece 1 at 08-JUL-14
piece handle=/u01/app/oracle/flash_recovery_area/HNJY/backupset/2014_07_08/o1_mf_nnnd0_TAG20140708T152238_9vq728mv_.bkp tag=TAG20140708T152238 comment=NONE
channel d2: backup set complete, elapsed time: 00:02:58
channel d2: starting incremental level 0 datafile backup set
channel d2: specifying datafile(s) in backup set
including current SPFILE in backup set
channel d2: starting piece 1 at 08-JUL-14
channel d1: finished piece 1 at 08-JUL-14
piece handle=/u01/app/oracle/flash_recovery_area/HNJY/backupset/2014_07_08/o1_mf_ncnn0_TAG20140708T152238_9vq77jxb_.bkp tag=TAG20140708T152238 comment=NONE
channel d1: backup set complete, elapsed time: 00:00:03
channel d2: finished piece 1 at 08-JUL-14
piece handle=/u01/app/oracle/flash_recovery_area/HNJY/backupset/2014_07_08/o1_mf_nnsn0_TAG20140708T152238_9vq77mcs_.bkp tag=TAG20140708T152238 comment=NONE
channel d2: backup set complete, elapsed time: 00:00:00
Finished backup at 08-JUL-14

Starting backup at 08-JUL-14
current log archived
channel d1: starting archived log backup set
channel d1: specifying archived log(s) in backup set
input archived log thread=1 sequence=5 RECID=3 STAMP=852391540
channel d1: starting piece 1 at 08-JUL-14
channel d1: finished piece 1 at 08-JUL-14
piece handle=/u01/app/oracle/flash_recovery_area/HNJY/backupset/2014_07_08/o1_mf_annnn_TAG20140708T152540_9vq77n7h_.bkp tag=TAG20140708T152540 comment=NONE
channel d1: backup set complete, elapsed time: 00:00:01
Finished backup at 08-JUL-14

released channel: d1

released channel: d2
记录下在开始操作之前的时间
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') time from dual;

TIME
-------------------
2014-07-08 15:37:59
SQL> conn scott/tiger
Connected.
查看scott用户下面的表
SQL> select table_name,tablespace_name from user_tables;

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
SALGRADE                       USERS
BONUS                          USERS
EMP                            USERS
DEPT                           USERS
创建一个test表进行测试
SQL> create table test(name char);

Table created.
插入一条数据
SQL> insert into test values('a');

1 row created.

SQL> select * from test;

N
-
a

SQL> commit;

Commit complete.
记录下当前的时间
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') time from dual;

TIME
-------------------
2014-07-08 15:41:43

SQL> drop table test;

Table dropped.

SQL> commit;

Commit complete.

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') time from dual;

TIME
-------------------
2014-07-08 15:43:13
删除scott用户
SQL> conn /as sysdba
Connected.
SQL> drop user scott cascade;

User dropped.

[oracle@node0 ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Jul 8 15:44:23 2014

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

connected to target database: HNJY (DBID=1408835946)
查看此时数据库的状态
RMAN> list incarnation;

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       HNJY     1408835946       PARENT  1          13-AUG-09
2       2       HNJY     1408835946       CURRENT 754488     08-JUL-14
关闭数据库
RMAN> shutdown immediate;

database closed
database dismounted
Oracle instance shut down
启动到mount状态
RMAN> startup mount;

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area     849530880 bytes

Fixed Size                     1339824 bytes
Variable Size                511708752 bytes
Database Buffers             331350016 bytes
Redo Buffers                   5132288 bytes
首先恢复scott用户
RMAN> run
2> {
3> allocate channel d1 type disk;
4> allocate channel d2 type disk;
5> set until time "to_date('2014-07-08 15:43:13','yyyy-mm-dd hh24:mi:ss')";
6> restore database;
7> recover database;
8> }

allocated channel: d1
channel d1: SID=18 device type=DISK

allocated channel: d2
channel d2: SID=21 device type=DISK

executing command: SET until clause

Starting restore at 08-JUL-14

channel d1: starting datafile backup set restore
channel d1: specifying datafile(s) to restore from backup set
channel d1: restoring datafile 00001 to /u01/app/oracle/oradata/hnjy/system01.dbf
channel d1: restoring datafile 00004 to /u01/app/oracle/oradata/hnjy/users01.dbf
channel d1: reading from backup piece /u01/app/oracle/flash_recovery_area/HNJY/backupset/2014_07_08/o1_mf_nnnd0_TAG20140708T152238_9vq71ywo_.bkp
channel d2: starting datafile backup set restore
channel d2: specifying datafile(s) to restore from backup set
channel d2: restoring datafile 00002 to /u01/app/oracle/oradata/hnjy/sysaux01.dbf
channel d2: restoring datafile 00003 to /u01/app/oracle/oradata/hnjy/undotbs01.dbf
channel d2: restoring datafile 00005 to /u01/app/oracle/oradata/hnjy/example01.dbf
channel d2: reading from backup piece /u01/app/oracle/flash_recovery_area/HNJY/backupset/2014_07_08/o1_mf_nnnd0_TAG20140708T152238_9vq728mv_.bkp
channel d2: piece handle=/u01/app/oracle/flash_recovery_area/HNJY/backupset/2014_07_08/o1_mf_nnnd0_TAG20140708T152238_9vq728mv_.bkp tag=TAG20140708T152238
channel d2: restored backup piece 1
channel d2: restore complete, elapsed time: 00:03:16
channel d1: piece handle=/u01/app/oracle/flash_recovery_area/HNJY/backupset/2014_07_08/o1_mf_nnnd0_TAG20140708T152238_9vq71ywo_.bkp tag=TAG20140708T152238
channel d1: restored backup piece 1
channel d1: restore complete, elapsed time: 00:03:29
Finished restore at 08-JUL-14

Starting recover at 08-JUL-14

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

Finished recover at 08-JUL-14
released channel: d1
released channel: d2

RMAN> alter database open resetlogs;

database opened

RMAN> exit


Recovery Manager complete.
[oracle@node0 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Jul 8 15:53:32 2014

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> conn scott/tiger
Connected.
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BIN$/am9RJGdPMngQKjAb3IRGg==$0 TABLE
BONUS                          TABLE
DEPT                           TABLE
EMP                            TABLE
SALGRADE                       TABLE
可以看到scott用户已经恢复，但是test表还没有恢复
下面开始恢复test表

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@node0 ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Jul 8 15:54:04 2014

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

connected to target database: HNJY (DBID=1408835946)

RMAN> shutdown immediate;

using target database control file instead of recovery catalog
database closed
database dismounted
Oracle instance shut down

RMAN> startup mount;

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area     849530880 bytes

Fixed Size                     1339824 bytes
Variable Size                511708752 bytes
Database Buffers             331350016 bytes
Redo Buffers                   5132288 bytes

RMAN> run
2> {
3> allocate channel d1 type disk;
4> allocate channel d2 type disk;
5> set until time "to_date('2014-07-08 15:41:43','yyyy-mm-dd hh24:mi:ss')"; 指定删除表之前的时间
6> restore database;
7> recover database;
8> }

allocated channel: d1
channel d1: SID=18 device type=DISK

allocated channel: d2
channel d2: SID=19 device type=DISK

executing command: SET until clause

Starting restore at 08-JUL-14
released channel: d1
released channel: d2
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 07/08/2014 15:56:56
RMAN-20207: UNTIL TIME or RECOVERY WINDOW is before RESETLOGS time
提示报错，查看此时数据库的状态DB Key由2变为3，解决办法就是重置数据库到状态2，然后直接恢复到删除EMP表之前的时间点即可。
RMAN> list incarnation;
List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       HNJY     1408835946       PARENT  1          13-AUG-09
2       2       HNJY     1408835946       PARENT  754488     08-JUL-14
3       3       HNJY     1408835946       CURRENT 823876     08-JUL-14

RMAN> reset database to incarnation 2;  重置数据库的状态

database reset to incarnation 2

RMAN> run
2> {
3> allocate channel d1 type disk;
4> allocate channel d2 type disk;
5> set until time "to_date('2014-07-08 15:41:43','yyyy-mm-dd hh24:mi:ss')";
6> restore database;
7> recover database;
8> }

allocated channel: d1
channel d1: SID=18 device type=DISK

allocated channel: d2
channel d2: SID=19 device type=DISK

executing command: SET until clause

Starting restore at 08-JUL-14

channel d1: starting datafile backup set restore
channel d1: specifying datafile(s) to restore from backup set
channel d1: restoring datafile 00001 to /u01/app/oracle/oradata/hnjy/system01.dbf
channel d1: restoring datafile 00004 to /u01/app/oracle/oradata/hnjy/users01.dbf
channel d1: reading from backup piece /u01/app/oracle/flash_recovery_area/HNJY/backupset/2014_07_08/o1_mf_nnnd0_TAG20140708T152238_9vq71ywo_.bkp
channel d2: starting datafile backup set restore
channel d2: specifying datafile(s) to restore from backup set
channel d2: restoring datafile 00002 to /u01/app/oracle/oradata/hnjy/sysaux01.dbf
channel d2: restoring datafile 00003 to /u01/app/oracle/oradata/hnjy/undotbs01.dbf
channel d2: restoring datafile 00005 to /u01/app/oracle/oradata/hnjy/example01.dbf
channel d2: reading from backup piece /u01/app/oracle/flash_recovery_area/HNJY/backupset/2014_07_08/o1_mf_nnnd0_TAG20140708T152238_9vq728mv_.bkp
channel d2: piece handle=/u01/app/oracle/flash_recovery_area/HNJY/backupset/2014_07_08/o1_mf_nnnd0_TAG20140708T152238_9vq728mv_.bkp tag=TAG20140708T152238
channel d2: restored backup piece 1
channel d2: restore complete, elapsed time: 00:02:18
channel d1: piece handle=/u01/app/oracle/flash_recovery_area/HNJY/backupset/2014_07_08/o1_mf_nnnd0_TAG20140708T152238_9vq71ywo_.bkp tag=TAG20140708T152238
channel d1: restored backup piece 1
channel d1: restore complete, elapsed time: 00:02:39
Finished restore at 08-JUL-14

Starting recover at 08-JUL-14

starting media recovery

archived log for thread 1 with sequence 5 is already on disk as file /u01/app/oracle/archive/1_5_852390318.dbf
archived log for thread 1 with sequence 6 is already on disk as file /u01/app/oracle/archive/1_6_852390318.dbf
archived log for thread 1 with sequence 7 is already on disk as file /u01/app/oracle/archive/1_7_852390318.dbf
archived log file name=/u01/app/oracle/archive/1_5_852390318.dbf thread=1 sequence=5
archived log file name=/u01/app/oracle/archive/1_6_852390318.dbf thread=1 sequence=6
archived log file name=/u01/app/oracle/archive/1_7_852390318.dbf thread=1 sequence=7
media recovery complete, elapsed time: 00:00:11
Finished recover at 08-JUL-14
released channel: d1
released channel: d2

RMAN> alter database open resetlogs;

database opened

RMAN> quit


Recovery Manager complete.
[oracle@node0 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Jul 8 16:03:40 2014

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> conn scott/tiger
Connected.
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BONUS                          TABLE
DEPT                           TABLE
EMP                            TABLE
SALGRADE                       TABLE
TEST                           TABLE

SQL> select * from test;

N
-
a
可以看到表test已经恢复
参考文章：
http://blog.sina.com.cn/s/blog_70e5638f01017cet.html