当前位置:Oracle研究中心 > 运维DBA >
时间:2016-06-16 23:22 来源:Oracle研究中心 作者:惜分飞 点击: 次
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
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
[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
Powered by Oracle研究中心 OraclePlus Inc.
BY DedeCMS 京ICP备14022373号-5
声明:Oracle研究中心部分文章源自互联网,转载均按原文作者要求转载,如有疑问联系QQ:1026002015,协商未果24小时删除。