ORACLE 12C RMAN recover table

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:ORACLE 12C RMAN recover table

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

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

此条目发表在 Oracle, ORACLE 12C 分类目录。将固定链接加入收藏夹。

ORACLE 12C RMAN recover table》有 2 条评论

  1. 惜分飞 说:

    直接恢复出来dmp文件(不impdp导入)

    rman target sys/xifenfei log=/tmp/recover_table.log
    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'
              dump file 'xifenfei.dmp'
              NOTABLEIMPORT;
    
    --部分日志
    Performing export of tables...
       EXPDP> Starting "SYS"."TSPITR_EXP_jdcp_Buty":  
       EXPDP> Estimate in progress using BLOCKS method...
       EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
       EXPDP> Total estimation using BLOCKS method: 64 KB
       EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
       EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
       EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
       EXPDP> . . exported "XFF"."T_XIFENFEI"                          5.507 KB       4 rows
       EXPDP> Master table "SYS"."TSPITR_EXP_jdcp_Buty" successfully loaded/unloaded
       EXPDP> ******************************************************************************
       EXPDP> Dump file set for SYS.TSPITR_EXP_jdcp_Buty is:
       EXPDP>   /tmp/recovertable/xifenfei.dmp
       EXPDP> Job "SYS"."TSPITR_EXP_jdcp_Buty" successfully completed at Thu Jan 17 19:46:45 2013 elapsed 0 00:00:40
    Export completed
    
    Not performing table import after point-in-time recovery
    
  2. 惜分飞 说:

    12.0.1.0及其以上版本能够比较好的支持pdb中的recover table

    RECOVER TABLE SMEDS."RECTEST" OF PLUGGABLE DATABASE T12CPDB1
              UNTIL SEQUENCE 48 THREAD 1
              AUXILIARY DESTINATION '/testcases/SR3-6629539201/rectbl'
              REMAP TABLE 'SMEDS'.'RECTEST':'TEST4_RECTEST';