Oracle 12C 新特性用rman备份恢复表

  在12c之前对于用户因DDL(DROP TRUNCATE )等操作引起的数据丢失，一般通过数据库时间点不完全恢复、基于表空间的时间点恢复（TSPITR）、闪回技术。 12c推出了rman基于时间点对表的恢复。原理如下： Recover Table新特性是利用创建辅助临时实例加数据泵工具来实现的。通常在进行Recover Table之前应该准备好两个目录（AUXILIARY DESTINATION和DATAPUMP DESTINATION），AUXILIARY DESTINATION用来临时存放辅助实例的数据文件，DATAPUMP DESTINATION用来临时存放数据泵导出的文件。 一：测试过程如下： 1. SQL> set lines 60 SQL> desc tb ;  Name                          Null?    Type  ----------------------------- -------- --------------------  OWNER                                  VARCHAR2(128)  OBJECT_NAME                            VARCHAR2(128)  SUBOBJECT_NAME                         VARCHAR2(128)  OBJECT_ID                              NUMBER  DATA_OBJECT_ID                         NUMBER  OBJECT_TYPE                            VARCHAR2(23)  CREATED                                DATE  LAST_DDL_TIME                          DATE   SQL> insert into  tb  select * from tb ; 72633 rows created. SQL> commit  ; Commit complete. SQL>  SQL> conn / as sysdba Connected. 2.切换日志使数据写到磁盘上。 SQL> alter system switch logfile ; 3.rman备份数据库 Recovery Manager: Release 12.2.0.1.0 - Production on Fri Jun 29 11:41:03 2018 Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved. connected to target database: MISDB (DBID=4279250949) RMAN> run  2> { 3> crosscheck archivelog all; 4> crosscheck  backup; 5> delete noprompt obsolete;  6> delete noprompt expired backup; 7> allocate channel d1  type disk  ; 8> allocate channel d2 type disk ; 9> backup format '/u01/bk/%T_%d_%s_%p.dat' tag 'full_data' database ;           10> backup format '/u01/bk/%T_%d_%s_%p.arc' tag 'full_arc' archivelog all delete all input ;      11> backup format '/u01/bk/%T_%d_%s_%p.ctl' tag 'full_ctl' current controlfile;  12> release channel d1; 13> release channel d2; 14> }; released channel: d1 released channel: d2 RMAN>  SQL> select count(*) from rita.tb ;   COUNT(*) ----------     145266 SQL> select current_scn from v$database; CURRENT_SCN ----------- 2074690 4. 删除表 SQL> drop table rita.tb  purge; SQL>  select count(*) from rita.tb ;  select count(*) from rita.tb                            * ERROR at line 1: ORA-00942: table or view does not exist 5.建辅助目录 [oracle@host01 u01]$ mkdir  recover/ [oracle@host01 u01]$ mkdir  dumpfiles [oracle@host01 u01]$ ls app  bk  dumpfiles  lost+found  recover [oracle@host01 u01]$ cd dumpfiles/ [oracle@host01 dumpfiles]$ ls [oracle@host01 dumpfiles]$ cd  .. [oracle@host01 u01]$  6.查看数据库备份： list backup of database; connected to target database: MISDB (DBID=4279250949) RMAN>  using target database control file instead of recovery catalog 7.恢复删除的表： rman target  / Recovery Manager: Release 12.2.0.1.0 - Production on Fri Jun 29 15:22:20 2018 Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved. connected to target database: MISDB (DBID=4279250949) RMAN> run{ 2> RECOVER TABLE rita.tb 3> UNTIL SCN 2074690 4> AUXILIARY DESTINATION '/u01/recover' 5> datapump destination '/u01/dumpfiles'; 6> }; Starting recover at 29-JUN-18 using target database control file instead of recovery catalog current log archived allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=52 instance=misdb1 device type=DISK RMAN-05026: warning: presuming following set of tablespaces applies to specified point-in-time List of tablespaces expected to have UNDO segments Tablespace SYSTEM Tablespace UNDOTBS1 Creating automatic instance, with SID='ygBi'自动创建一个数据库实例 oracle@host01 datafile]$ ps -ef |grep smon grid       5050      1  0 14:57 ?        00:00:00 asm_smon_+ASM1 root       5144      1  1 14:58 ?        00:00:30 /u01/app/12.2.0/grid/bin/osysmond.bin oracle     7730      1  0 15:00 ?        00:00:00 ora_smon_misdb1 oracle    36939      1  0 15:28 ?        00:00:00 ora_smon_ygBi reating automatic instance, with SID='ygBi' initialization parameters used for automatic instance: db_name=MISDB db_unique_name=ygBi_pitr_MISDB compatible=12.2.0 db_block_size=8192 db_files=200 diagnostic_dest=/u01/app/oracle _system_trig_enabled=FALSE sga_target=1728M processes=200 db_create_file_dest=/u01/recover log_archive_dest_1='location=/u01/recover'---这个空间要足够大 #No auxiliary parameter file used datafile 1 switched to datafile copy input datafile copy RECID=4 STAMP=980090756 file name=/u01/recover/MISDB/datafile/o1_mf_system_fmcqnsc8_.dbf datafile 4 switched to datafile copy input datafile copy RECID=5 STAMP=980090756 file name=/u01/recover/MISDB/datafile/o1_mf_undotbs1_fmcqpx2n_.dbf datafile 3 switched to datafile copy input datafile copy RECID=6 STAMP=980090756 file name=/u01/recover/MISDB/datafile/o1_mf_sysaux_fmcqpx17_.dbf contents of Memory Script: { # set requested point in time set until  scn 2074690; # online the datafiles restored or switched sql clone "alter database datafile  1 online"; sql clone "alter database datafile  4 online"; sql clone "alter database datafile  3 online"; # recover and open database read only recover clone database tablespace  "SYSTEM", "UNDOTBS1", "SYSAUX"; sql clone 'alter database open read only'; 。。。 contents of Memory Script: { # create directory for datapump import sql "create or replace directory TSPITR_DIROBJ_DPDIR as '' /u01/dumpfiles''"; # create directory for datapump export sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as '' /u01/dumpfiles''"; } executing Memory Script sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/dumpfiles'' sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/dumpfiles'' Performing export of tables...    EXPDP> Starting "SYS"."TSPITR_EXP_ygBi_gcph":      EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA    EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS    EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER    EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE    EXPDP> . . exported "RITA"."TB"                                 19.21 MB  145266 rows    EXPDP> Master table "SYS"."TSPITR_EXP_ygBi_gcph" successfully loaded/unloaded    EXPDP> ******************************************************************************    EXPDP> Dump file set for SYS.TSPITR_EXP_ygBi_gcph is:    EXPDP>   /u01/dumpfiles/tspitr_ygBi_13106.dmp    EXPDP> Job "SYS"."TSPITR_EXP_ygBi_gcph" successfully completed at Fri Jun 29 15:31:30 2018 elapsed 0 00:01:09 Export completed contents of Memory Script: { # shutdown clone before import shutdown clone abort } executing Memory Script Oracle instance shut down Performing import of tables...    IMPDP> Master table "SYS"."TSPITR_IMP_ygBi_yzeq" successfully loaded/unloaded    IMPDP> Starting "SYS"."TSPITR_IMP_ygBi_yzeq":      IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE    IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA    IMPDP> . . imported "RITA"."TB"                                 19.21 MB  145266 rows    IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS    IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER    IMPDP> Job "SYS"."TSPITR_IMP_ygBi_yzeq" successfully completed at Fri Jun 29 15:32:44 2018 elapsed 0 00:00:41 Import completed 二：从上边的例子可以看出，恢复大体流程是 1.rman 根据指定的表确定备份。 2.rman 根据指定的recover 路径恢复一个辅助实例，恢复到指定的时间点。 Creating automatic instance, with SID='ygBi' initialization parameters used for automatic instance: db_name=MISDB db_unique_name=ygBi_pitr_MISDB sql statement: alter database mount clone database sql statement: alter system archive log current contents of Memory Script: { # set requested point in time set until  scn 2074690; # set destinations for recovery set and auxiliary set datafiles set newname for clone datafile  1 to new; set newname for clone datafile  4 to new; set newname for clone datafile  3 to new; set newname for clone tempfile  1 to new; 3.用恢复好的辅助实例，数据库导出在导入生产环境 EXPDP> Starting "SYS"."TSPITR_EXP_ygBi_gcph":      EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA    EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS    EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER Performing import of tables...    IMPDP> Master table "SYS"."TSPITR_IMP_ygBi_yzeq" successfully loaded/unloaded    IMPDP> Starting "SYS"."TSPITR_IMP_ygBi_yzeq":      IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE    IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA    IMPDP> . . imported "RITA"."TB"                                 19.21 MB  145266 rows    IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS    IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER    IMPDP> Job "SYS"."TSPITR_IMP_ygBi_yzeq" successfully completed at Fri Jun 29 15:32:44 2018 elapsed 0 00:00:41 Import completed 4.删除辅助实例 恢复完成。 三：RECOVER TABLE需要注意的几个问题： 1.目标数据库必须被置于读写模式。 2.目标数据库必须被置于归档模式。 3.SYS用户下的表或分区无法恢复。 4.存储于SYSAUX和SYSTEM表空间下的表和分区无法恢复。 5.Standby数据库上的表或表分区不能进行恢复。 6.在执行“RECOVER TABLE”命令时，可以根据需要在以下三种级别指定时间： （1）SCN号 （2）Sequence number（日志序列号） （3）Time：根据NLS_LANG和NLS_DATE_FORMAT环境变量中的格式来指定时间，也可以用SYSDATE，比如"SYSDATE-30"、"to_date('2018-06-29:17:51:48','yyyy-mm-dd hh24:mi:ss')" 参考文档 https://docs.oracle.com/en/database/oracle/oracle-database/12.2/bradv/rman-recovering-tables-partitions.html#GUID-87B7F772-335F-4179-81C9-91678D026D01