学习笔记:Oracle 12C新特性使用RMAN对表table进行基于时间点的恢复

时间:2016-06-16 23:22 来源:Oracle研究中心作者:惜分飞点击: 次

天萃荷净 对Oracle最新版本12C数据库的RMAN新特性RMAN，基于时间点的table恢复

1.查看Oracle 12C数据库版本

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_oracleplus(id number,insert_time date);

Table created.

SQL> insert into t_oracleplus 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

2.备份Oracle 12C数据库

RMAN> backup  as compressed backupset database  format '/tmp/oracleplus_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/oracleplus/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/oracleplus/sysaux01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/oracleplus/undotbs01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/oracleplus/oracleplus01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/oracleplus/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/oracleplus_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/oracleplus_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_oracleplus 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_oracleplus values(3,sysdate);

1 row created.

SQL> insert into t_oracleplus 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_oracleplus;

        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_oracleplus 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

3.rman recover table

[oracle@Lunar tmp]$ rman target sys/oracleplus log=/tmp/recover_table.log
RMAN> RECOVER TABLE XFF."T_oracleplus" 
until time  "to_date('2013-01-17 18:41:17','yyyy-mm-dd hh24:mi:ss')"
AUXILIARY DESTINATION '/tmp/recovertable'
REMAP TABLE 'XFF'.'T_oracleplus':'T_oracleplus_NEW';
--recover table XFF.T_oracleplus data impdp into XFF.T_oracleplus_NEW

验证数据库

SQL> select id,to_char(insert_time,'yyyy-mm-dd hh24:mi:ss') from t_oracleplus_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导出表,根据需要决定是否导入表到数据库

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之学习笔记:Oracle 12C新特性使用RMAN对表table进行基于时间点的恢复

本文由大师惜分飞原创分享，网址：http://www.oracleplus.net/arch/406.html