<< 返回文章列表

12c RMAN新特性之Recover Table

2018年8月14日
周玉其
2475


在 Oracle 12c 之前,如果误删一张表,常规的方法是 Flashback 闪回或 TSPITR。而开启 flashback 会消耗磁盘空间,且如果被 purge 或者经过 DDL,将无法 flashback。如果需要恢复的表空间过大,TSPITR 会耗时非常久。12c 中提供了Table Recovery 的新特性,可以针对单表(或单个表分区)基于时间点进行恢复,该特性用以直接从 RMAN 备份中恢复表的方法,该特性有以下特点:


  • 不影响数据库中的其他数据库对象

  • 减少时间和磁盘空间的消耗,和早期的 TSPIRT 工作流程类似

  • 从另一个磁盘路径恢复表空间

  • 导出想要的表数据

  • 导入到原数据库(可选)

 

recover table 可以将一个或多个表或表分区恢复到指定的时间点,而不会影响其余的数据库对象。可以使用以前创建的 RMAN 备份将表和表分区恢复到指定的时间点。

该表被恢复到一个辅助实例中,并且可以选择:

  • 使用 REMAP 选项将恢复的表导入新表或分区

  • 仅在恢复的表中创建 expdp dump 文件,以便在其他时间进行导入

 


先决条件



目标数据库必须处于读写模式。

目标数据库必须处于 ARCHIVELOG 模式。


您必须拥有表或表分区的 RMAN 备份,因为它们存在于要恢复这些对象的时间点。

要恢复单个表分区,目标数据库的 COMPATIBLE 初始化参数必须设置为 11.1.0 或更高。


请确保 AUXILIARY DESTINATION 有足够的空间来恢复 system、sysaux、undo 以及恢复表所需的表空间。


请注意,表恢复后,辅助目标中的这些文件将自动删除

必须使用 AUXILIARY DESTINATION 子句和后面其中一个子句来指定恢复的时间点:UNTIL TIME、UNTIL SCN 或 UNTIL SEQUENCE。

 


使用限制


  •  属于SYS模式的表和表分区无法恢复。

  •  SYSTEM和SYSAUX表空间中的表和表分区无法恢复。

  •  standby数据库上的表和表分区无法恢复。

  •  命名为NOT NULL约束的表不能用REMAP选项恢复。



使用场景

RMAN 的表级和表分区级恢复在以下场景中非常适用:

(1) 需要将非常少量的表恢复至特定的时间点。在这种情况下,TSPITR 不是最有效的解决方案,因为它会将表空间中的所有对象恢复到指定的时间点。

(2) 需要恢复已被逻辑损坏或已被 drop 和 purge 的表。

(3) flashback 不可用,比如需要的 undo 数据被覆盖。

(4) DDL 操作修改表结构后丢失的数据。flashback table 不能跨越 DDL(例如truncate)来回滚表。

 


在这Recover table 过程演示



创建测试表



SQL> create table t_recover_test as select * from dba_objects;  

 

Table created.

 

SQL> select count(*) from t_recover_test;

 

COUNT(*)

----------

 72652

 


备份



[oracle@localhost ~]$ rman target /

 

Recovery Manager: Release 12.2.0.1.0 - Production on Thu Jun 28 21:06:17 2018

 

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

 

connected to target database: PCC (DBID=3164878903)

 

RMAN> backup database format '/home/oracle/bak/db_%U';

 

Starting backup at 28-JUN-18

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=61 device type=DISK

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001 name=/home/oracle/oradata/pcc/system01.dbf

input datafile file number=00003 name=/home/oracle/oradata/pcc/sysaux01.dbf

input datafile file number=00004 name=/home/oracle/oradata/pcc/undotbs01.dbf

input datafile file number=00005 name=/home/oracle/oradata/pcc/tbs_zyq01.dbf

input datafile file number=00007 name=/home/oracle/oradata/pcc/users01.dbf

channel ORA_DISK_1: starting piece 1 at 28-JUN-18

channel ORA_DISK_1: finished piece 1 at 28-JUN-18

piece handle=/home/oracle/bak/db_01t6k254_1_1 tag=TAG20180628T215236 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15

Finished backup at 28-JUN-18

 

Starting Control File and SPFILE Autobackup at 28-JUN-18

piece handle=/u01/app/oracle/product/12.2.0.1/dbs/c-3164878903-20180628-00 comment=NONE

Finished Control File and SPFILE Autobackup at 28-JUN-18

 


恢复表




contents of Memory Script:

{

# set requested point in time

set until  time "to_date('2018-06-28 23:56:06','yyyy-mm-dd hh24:mi:ss')";

# set destinations for recovery set and auxiliary set datafiles

set newname for datafile  5 to new;

# restore the tablespaces in the recovery set and the auxiliary set

restore clone datafile  5;

 

switch clone datafile all;

}

executing Memory Script

 

executing command: SET until clause

 

executing command: SET NEWNAME

 

Starting restore at 29-JUN-18

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=34 device type=DISK

 

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00005 to /data01/HCED_PITR_PCC/datafile/o1_mf_tbs_zyq_%u_.dbf

channel ORA_AUX_DISK_1: reading from backup piece /home/oracle/bak/db_01t6k254_1_1

channel ORA_AUX_DISK_1: piece handle=/home/oracle/bak/db_01t6k254_1_1 tag=TAG20180628T215236

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03

Finished restore at 29-JUN-18

 

datafile 5 switched to datafile copy

input datafile copy RECID=8 STAMP=980038478 file name=/data01/HCED_PITR_PCC/datafile/o1_mf_tbs_zyq_fmcsfttn_.dbf

 

contents of Memory Script:

{

# set requested point in time

set until  time "to_date('2018-06-28 23:56:06','yyyy-mm-dd hh24:mi:ss')";

# online the datafiles restored or switched

sql clone "alter database datafile  5 online";

# recover and open resetlogs

recover clone database tablespace  "TBS_ZYQ", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;

alter clone database open resetlogs;

}

executing Memory Script

 

executing command: SET until clause

 

sql statement: alter database datafile  5 online

 

Starting recover at 29-JUN-18

using channel ORA_AUX_DISK_1

 

starting media recovery

 

archived log for thread 1 with sequence 3 is already on disk as file /u01/app/oracle/product/12.2.0.1/dbs/arch1_3_979872889.dbf

archived log for thread 1 with sequence 4 is already on disk as file /u01/app/oracle/product/12.2.0.1/dbs/arch1_4_979872889.dbf

archived log file name=/u01/app/oracle/product/12.2.0.1/dbs/arch1_3_979872889.dbf thread=1 sequence=3

archived log file name=/u01/app/oracle/product/12.2.0.1/dbs/arch1_4_979872889.dbf thread=1 sequence=4

media recovery complete, elapsed time: 00:00:01

Finished recover at 29-JUN-18

 

database opened

 

contents of Memory Script:

{

# create directory for datapump import

sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''

/data01''";

# create directory for datapump export

sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''

/data01''";

}

executing Memory Script

 

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/data01''

 

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/data01''

 

Performing export of tables...

   EXPDP> Starting "SYS"."TSPITR_EXP_hCed_Dhxe":  

   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 "ZYQ"."T_RECOVER_TEST"                      9.324 MB   72652 rows

   EXPDP> Master table "SYS"."TSPITR_EXP_hCed_Dhxe" successfully loaded/unloaded

   EXPDP> ******************************************************************************

   EXPDP> Dump file set for SYS.TSPITR_EXP_hCed_Dhxe is:

   EXPDP>   /data01/tspitr_hCed_71393.dmp

   EXPDP> Job "SYS"."TSPITR_EXP_hCed_Dhxe" successfully completed at Fri Jun 29 00:55:41 2018 elapsed 0 00:00:35

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_hCed_Arhm" successfully loaded/unloaded

   IMPDP> Starting "SYS"."TSPITR_IMP_hCed_Arhm":  

   IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE

   IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

   IMPDP> . . imported "ZYQ"."T_RECOVER_TEST_MAP"                  9.324 MB   72652 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_hCed_Arhm" successfully completed at Fri Jun 29 00:56:43 2018 elapsed 0 00:00:45

Import completed

 

Removing automatic instance

Automatic instance removed

auxiliary instance file /data01/PCC/datafile/o1_mf_temp_fmcsd5ny_.tmp deleted

auxiliary instance file /data01/HCED_PITR_PCC/onlinelog/o1_mf_3_fmcsg3y6_.log deleted

auxiliary instance file /data01/HCED_PITR_PCC/onlinelog/o1_mf_2_fmcsg07o_.log deleted

auxiliary instance file /data01/HCED_PITR_PCC/onlinelog/o1_mf_1_fmcsfzr1_.log deleted

auxiliary instance file /data01/HCED_PITR_PCC/datafile/o1_mf_tbs_zyq_fmcsfttn_.dbf deleted

auxiliary instance file /data01/PCC/datafile/o1_mf_sysaux_fmcsc75c_.dbf deleted

auxiliary instance file /data01/PCC/datafile/o1_mf_undotbs1_fmcsc75h_.dbf deleted

auxiliary instance file /data01/PCC/datafile/o1_mf_system_fmcsc755_.dbf deleted

auxiliary instance file /data01/PCC/controlfile/o1_mf_fmcsc00p_.ctl deleted

auxiliary instance file tspitr_hCed_71393.dmp deleted

Finished recover at 29-JUN-18

 

RMAN>

 


验证



SQL> select count(*) from T_RECOVER_TEST_MAP;

 

  COUNT(*)

----------

     72652



总结



(1) 根据为恢复指定的时间点确定哪个备份包含需要恢复的表或表分区。

(2) 确定目标主机上是否有足够的空间来创建将在表或分区恢复过程中使用的辅助实例。如果所需空间不可用,则 RMAN 会显示错误并退出恢复操作。

(3) 在目标主机上创建一个辅助数据库,并将指定的表或表分区恢复到此辅助数据库中,直到指定的时间点。您可以指定恢复的数据文件存储在辅助数据库中的目标主机上的位置。

(4) 创建包含恢复的表或表分区的数据泵导出转储文件。您可以指定用于存储恢复的表或表分区的元数据的数据泵导出转储文件的名称和位置。

(5) (可选)将 Data Pump 导出转储文件导入到目标实例中。您可以选择不将包含恢复的表或表分区的导出转储文件导入目标数据库。如果不将导出转储文件作为恢复过程的一部分导入,则必须稍后使用“数据泵导入”实用程序手动导入它。

注:如果在导入操作期间发生错误,RMAN 不会在表恢复结束时删除导出转储文件。这使您能够手动导入转储文件。

(6) (可选)重命名目标数据库中已恢复的表或表分区。您还可以将恢复的对象导入到与最初存在的表空间或模式不同的表空间或模式中。12.2 开始,schema 可不同

注:当您使用 REMAP 选项时,不会导入任何指定的约束和索引。这是为了避免与现有表格的名称冲突。

 

如果不希望表被导入,只需要恢复出 dmp 文件,可以使用 notableimport 选项,命令如下:

RECOVER TABLE SCOTT.EMP, SCOTT.DEPT    UNTIL TIME 'SYSDATE-1'    AUXILIARY DESTINATION '/tmp/oracle/recover'    DATAPUMP DESTINATION '/tmp/recover/dumpfiles'    DUMP FILE 'emp_dept_exp_dump.dat'    NOTABLEIMPORT;

 

如果需要将表恢复至另外的 schema,命令如下:

RECOVER TABLE HR.DEPARTMENTS, SH.CHANNELS

UNTIL TIME 'SYSDATE – 1' AUXILIARY DESTINATION '/tmp/auxdest'

REMAP TABLE hr.departments:example.new_departments, sh.channels:example.new_channels;