12c的rman有了很大的增强，其中一个亮点就是可以进行table 级别的恢复，本试验测试了rman基于时间点恢复一个被删除表的操作过程
数据库版本
SQL> select * from v$version; BANNER CON_ID -------------------------------------------------------------------------------- ---------- Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 - 64bit 0 PL/SQL Release 12.1.0.0.2 0 CORE 12.1.0.0.2 0 TNS for Linux: Version 12.1.0.0.2 0 NLSRTL Version 12.1.0.0.2 0
创建表并插入数据
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual; TO_CHAR(SYSDATE,'YY ------------------- 2013-01-17 18:37:07 SQL> create table t_xifenfei(id number,insert_time date); Table created. SQL> insert into t_xifenfei values(1,sysdate); 1 row created. SQL> commit; Commit complete. SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 53 Next log sequence to archive 55 Current log sequence 55 SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual; TO_CHAR(SYSDATE,'YY ------------------- 2013-01-17 18:37:55
备份数据库
RMAN> backup as compressed backupset database format '/tmp/xifenfei_db_%U'; Starting backup at 17-JAN-13 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=30 device type=DISK channel ORA_DISK_1: starting compressed full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00001 name=/u01/app/oracle/oradata/xifenfei/system01.dbf input datafile file number=00002 name=/u01/app/oracle/oradata/xifenfei/sysaux01.dbf input datafile file number=00003 name=/u01/app/oracle/oradata/xifenfei/undotbs01.dbf input datafile file number=00005 name=/u01/app/oracle/oradata/xifenfei/xifenfei01.dbf input datafile file number=00004 name=/u01/app/oracle/oradata/xifenfei/users01.dbf channel ORA_DISK_1: starting piece 1 at 17-JAN-13 channel ORA_DISK_1: finished piece 1 at 17-JAN-13 piece handle=/tmp/xifenfei_db_07nvln1g_1_1 tag=TAG20130117T183839 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:01:15 channel ORA_DISK_1: starting compressed full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set including current control file in backup set including current SPFILE in backup set channel ORA_DISK_1: starting piece 1 at 17-JAN-13 channel ORA_DISK_1: finished piece 1 at 17-JAN-13 piece handle=/tmp/xifenfei_db_08nvln3r_1_1 tag=TAG20130117T183839 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03 Finished backup at 17-JAN-13
插入数据继续测试
SQL> insert into t_xifenfei values(2,sysdate);
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> insert into t_xifenfei values(3,sysdate);
1 row created.
SQL> insert into t_xifenfei values(4,sysdate);
1 row created.
SQL> commit;
Commit complete.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 55
Next log sequence to archive 57
Current log sequence 57
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2013-01-17 18:41:17
SQL> select id,to_char(insert_time,'yyyy-mm-dd hh24:mi:ss') from t_xifenfei;
ID TO_CHAR(INSERT_TIME
---------- -------------------
1 2013-01-17 18:37:22
2 2013-01-17 18:40:37
3 2013-01-17 18:40:58
4 2013-01-17 18:40:59
删除测试表
SQL> drop table t_xifenfei purge; Table dropped. SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual; TO_CHAR(SYSDATE,'YY ------------------- 2013-01-17 18:41:36
rman recover table
[oracle@Lunar tmp]$ rman target sys/xifenfei log=/tmp/recover_table.log
RMAN> RECOVER TABLE XFF."T_XIFENFEI"
until time "to_date('2013-01-17 18:41:17','yyyy-mm-dd hh24:mi:ss')"
AUXILIARY DESTINATION '/tmp/recovertable'
REMAP TABLE 'XFF'.'T_XIFENFEI':'T_XIFENFEI_NEW';
--recover table XFF.T_XIFENFEI data impdp into XFF.T_XIFENFEI_NEW
验证数据库
SQL> select id,to_char(insert_time,'yyyy-mm-dd hh24:mi:ss') from t_xifenfei_new;
ID TO_CHAR(INSERT_TIME
---------- -------------------
1 2013-01-17 18:37:22
2 2013-01-17 18:40:37
3 2013-01-17 18:40:58
4 2013-01-17 18:40:59
补充说明
1.rman recover table 必须使用sys用户登录,而不能使用/,因为12c默认有backup用户
2.rman recover table 需要还原system,undo,sysaux表空间,需要还原表所在表空间,和expdp导出文件空间,所以需要额外空间较大
3.整体恢复过程是:还原system,undo,sysaux表空间,然后read only数据库,然后重启数据库还原表所在表空间,然后expdp导出表,根据需要决定是否导入表到数据库
4.完整的rman recover table执行过程脚本recover_table
