rman之基于时间点的不完全恢复

查看oracle是否归档
SYS>archive log list;
Database log mode 

          Archive Mode
Automatic archival           Enabled
Archive destination           USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     1
Next log sequence to archive   3
Current log sequence           3
如果oracle为非归档模式，将其改为归档模式关闭数据库
SQL> shutdown immediate
启动数据mount状态：
SQL> startup mount
修改数据库为归档模式：
SQL> alter database archivelog;
打开数据库，查询：
SQL> alter database open;
查看scott用户下的e表
SCOTT>set linesize 200;
SCOTT>set pagesize 20;
SCOTT>select * from e;
SCOTT>select * from e;

     EMPNO ENAME      JOB           MGR HIREDATE              SAL    COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7369 SMITH      CLERK          7902 1980-12-17 00:00:00          800             20
      7499 ALLEN      SALESMAN          7698 1981-02-20 00:00:00         1600     300         30
      7521 WARD       SALESMAN          7698 1981-02-22 00:00:00         1250     500         30
      7566 JONES      MANAGER          7839 1981-04-02 00:00:00         2975             20
      7654 MARTIN     SALESMAN          7698 1981-09-28 00:00:00         1250    1400         30
      7698 BLAKE      MANAGER          7839 1981-05-01 00:00:00         2850             30
      7782 CLARK      MANAGER          7839 1981-06-09 00:00:00         2450             10
      7788 SCOTT      ANALYST          7566 1987-04-19 00:00:00         3000             20
      7839 KING       PRESIDENT        1981-11-17 00:00:00         5000             10
      7844 TURNER     SALESMAN          7698 1981-09-08 00:00:00         1500       0         30
      7876 ADAMS      CLERK          7788 1987-05-23 00:00:00         1100             20
      7900 JAMES      CLERK          7698 1981-12-03 00:00:00          950             30
      7902 FORD       ANALYST          7566 1981-12-03 00:00:00         3000             20
      7934 MILLER     CLERK          7782 1982-01-23 00:00:00         1300             10
给数据库做0级备份，生成文件：rman_inc0_20130523816190700
备份脚本见 oracle之rman
向表中写入数据
SCOTT>insert into e values(1000,'wang','aa',7566,'25-JAN-82',1000,1000,20);
SCOTT>insert into e values(1001,'andy','aa',7566,'25-JAN-82',1000,1000,20);
查看e表
SCOTT>select * from e;

     EMPNO ENAME      JOB           MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK          7902 17-DEC-80        800            20
      7499 ALLEN      SALESMAN          7698 20-FEB-81       1600        300       30
      7521 WARD       SALESMAN          7698 22-FEB-81       1250        500       30
      7566 JONES      MANAGER          7839 02-APR-81       2975            20
      7654 MARTIN     SALESMAN          7698 28-SEP-81       1250       1400       30
      7698 BLAKE      MANAGER          7839 01-MAY-81       2850            30
      7782 CLARK      MANAGER          7839 09-JUN-81       2450            10
      7788 SCOTT      ANALYST          7566 19-APR-87       3000            20
      7839 KING       PRESIDENT        17-NOV-81       5000            10
      7844 TURNER     SALESMAN          7698 08-SEP-81       1500      0       30
      7876 ADAMS      CLERK          7788 23-MAY-87       1100            20
      7900 JAMES      CLERK          7698 03-DEC-81        950            30
      7902 FORD       ANALYST          7566 03-DEC-81       3000            20
      7934 MILLER     CLERK          7782 23-JAN-82       1300            10
      1000 wang       aa          7566 25-JAN-82       1000       1000       20
      1001 andy       aa          7566 25-JAN-82       1000       1000       20
做2级备份，生成文件：rman_inc2_20130523816191062
备份脚本见 oracle之rman
查看当前数据库时间
SCOTT>alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
SCOTT>select sysdate from dual;
SYSDATE
-------------------
2013-05-23 15:47:55
删除e表中插入的数据
SCOTT>delete from e where empno in (1000,1001);
再次查看e表中数据
SCOTT>select * from e;
     EMPNO ENAME      JOB           MGR HIREDATE              SAL    COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7369 SMITH      CLERK          7902 1980-12-17 00:00:00          800             20
      7499 ALLEN      SALESMAN          7698 1981-02-20 00:00:00         1600     300         30
      7521 WARD       SALESMAN          7698 1981-02-22 00:00:00         1250     500         30
      7566 JONES      MANAGER          7839 1981-04-02 00:00:00         2975             20
      7654 MARTIN     SALESMAN          7698 1981-09-28 00:00:00         1250    1400         30
      7698 BLAKE      MANAGER          7839 1981-05-01 00:00:00         2850             30
      7782 CLARK      MANAGER          7839 1981-06-09 00:00:00         2450             10
      7788 SCOTT      ANALYST          7566 1987-04-19 00:00:00         3000             20
      7839 KING       PRESIDENT        1981-11-17 00:00:00         5000             10
      7844 TURNER     SALESMAN          7698 1981-09-08 00:00:00         1500       0         30
      7876 ADAMS      CLERK          7788 1987-05-23 00:00:00         1100             20
      7900 JAMES      CLERK          7698 1981-12-03 00:00:00          950             30
      7902 FORD       ANALYST          7566 1981-12-03 00:00:00         3000             20
      7934 MILLER     CLERK          7782 1982-01-23 00:00:00         1300             10
现在e表中新插入的数据已经删除了
关闭目标数据库，并启动到mount状态
SYS>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS>startup mount;
ORACLE instance started.

Total System Global Area  608174080 bytes
Fixed Size            1220820 bytes
Variable Size          184553260 bytes
Database Buffers      415236096 bytes
Redo Buffers            7163904 bytes
Database mounted.
在目录数据库中进行基于时间点的不完全恢复，恢复到2013-05-23 15:47:55，以下是恢复过程
[oracle@localhost ~]$ rman target sys@orcl catalog rman/rman@orcl2

Recovery Manager: Release 10.2.0.1.0 - Production on Thu May 23 15:50:29 2013

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

target database Password:
connected to target database: ORCL (DBID=1292656107, not open)
connected to recovery catalog database

RMAN> restore database until time "to_date('2013-05-23 15:47:55','yyyy-mm-dd hh24:mi:ss')";

Starting restore at 23-MAY-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/app/oracle/oradata/orcl/system01.dbf
restoring datafile 00002 to /u01/app/oracle/oradata/orcl/undotbs01.dbf
restoring datafile 00003 to /u01/app/oracle/oradata/orcl/sysaux01.dbf
restoring datafile 00004 to /u01/app/oracle/oradata/orcl/users01.dbf
restoring datafile 00005 to /u01/app/oracle/oradata/orcl/example01.dbf
restoring datafile 00006 to /u01/app/oracle/oradata/orcl/scott_data.dbf
restoring datafile 00007 to /u01/app/oracle/oradata/orcl/epe_data01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ORCL/backupset/rman_inc0_20130523816190700
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/rman_inc0_20130523816190700 tag=INC0
channel ORA_DISK_1: restore complete, elapsed time: 00:03:20
Finished restore at 23-MAY-13

RMAN> recover database until time "to_date('2013-05-23 15:47:55','yyyy-mm-dd hh24:mi:ss')";

Starting recover at 23-MAY-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u01/app/oracle/oradata/orcl/system01.dbf
destination for restore of datafile 00002: /u01/app/oracle/oradata/orcl/undotbs01.dbf
destination for restore of datafile 00003: /u01/app/oracle/oradata/orcl/sysaux01.dbf
destination for restore of datafile 00004: /u01/app/oracle/oradata/orcl/users01.dbf
destination for restore of datafile 00005: /u01/app/oracle/oradata/orcl/example01.dbf
destination for restore of datafile 00006: /u01/app/oracle/oradata/orcl/scott_data.dbf
destination for restore of datafile 00007: /u01/app/oracle/oradata/orcl/epe_data01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ORCL/backupset/rman_inc2_20130523816191062
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/rman_inc2_20130523816191062 tag=INC2
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

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

Finished recover at 23-MAY-13
将目标数据库开启
SYS>alter database open resetlogs;

Database altered.
查看e表中的数据，确认是否恢复
SYS>set linesize 200;
SYS>set pagesize 20;
SYS>select * from scott.e;

     EMPNO ENAME      JOB           MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK          7902 17-DEC-80        800            20
      7499 ALLEN      SALESMAN          7698 20-FEB-81       1600        300       30
      7521 WARD       SALESMAN          7698 22-FEB-81       1250        500       30
      7566 JONES      MANAGER          7839 02-APR-81       2975            20
      7654 MARTIN     SALESMAN          7698 28-SEP-81       1250       1400       30
      7698 BLAKE      MANAGER          7839 01-MAY-81       2850            30
      7782 CLARK      MANAGER          7839 09-JUN-81       2450            10
      7788 SCOTT      ANALYST          7566 19-APR-87       3000            20
      7839 KING       PRESIDENT        17-NOV-81       5000            10
      7844 TURNER     SALESMAN          7698 08-SEP-81       1500      0       30
      7876 ADAMS      CLERK          7788 23-MAY-87       1100            20
      7900 JAMES      CLERK          7698 03-DEC-81        950            30
      7902 FORD       ANALYST          7566 03-DEC-81       3000            20
      7934 MILLER     CLERK          7782 23-JAN-82       1300            10
      1000 wang       aa          7566 25-JAN-82       1000       1000       20
      1001 andy       aa          7566 25-JAN-82       1000       1000       20

16 rows selected.
查看结果显示数据恢复成功
注意！！！resetlogs后务必将数据库重新做一次完整的备份

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/27181165/viewspace-775867/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/27181165/viewspace-775867/