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
阅读(10314) | 评论(0) | 转发(3) |
