Oracle 12C新特性-RMAN恢复表

来源：这里教程网时间：2026-03-03 14:57:19 作者：

Oracle 12C 新特性 -RMAN 恢复表 oracle 如何恢复单张表数据？ 场景一：处理简单，恢复速度快 1 如果表被 delete 、 update 、 insert ，并已经 commit ，在时间和 undo 允许的情况下，可以通过闪回将数据迅速找回； alter table t1 enable row movement; --- 基于 Scn 闪回 flashback table t1 to scn 1726953; --- 基于时间闪回 flashback table t1 to timestamp to_timestamp('2020-02-08 12:00:00','yyyy-mm-dd hh24:mi:ss'); 或者通过CTAS 方式将数据保存到另一张表里 create table t1_recv as select * from t1 as of timestamp (systimestamp - interval '2' minute); 2 如果表被 drop ，并且没有使用 purge 清空回收站，可以通过回收站找回被删除的表 (user_recyclebin); --- 将回收站的数据通过 CTAS 方式保存到另一张表里 show recyclebin select object_name,ORIGINAL_NAME from user_recyclebin; select original_name,operation,droptime from recyclebin; create table t1 as select * from "BIN$HKCe4+VkDVXgU8UDqMAWvQ==$0"; --- 或者直接闪回表 flashback table t1 to before drop; 场景二：通过备份进行恢复 处理复杂，恢复速度慢的场景 1 如果表被 delete 、 update 、 insert ，并已经 commit ，时间已过去很久， undo 信息已被覆盖：或者表被truncate 或 drop 并清空了回收站。如果有expdp 或 exp 备份，可以用备份恢复，会丢失数据。如果有rman 备份， 12C 之前，可以将备份基于时间点不完全恢复到另一个数据库里，然后通过 expdp 导出丢失数据的表，在导入到生产库，缺点是操作繁琐，时间较长。从12C 开始，通过 rman 恢复单张表，操作命令变的更简单，但恢复时间还是会比较长。 场景三：处理难度大，不一定能完全恢复的场景 表被误操作(delete 、 update 、 truncate 、 drop) ，并且没有有效的备份，回收站已被清空， undo 数据已被覆盖：可以考虑bbed,ODU 等工具进行数据恢复。 本次实验模拟pdb 中一张表误被 delete ，并通过 rman 进行单表恢复。 实验过程如下： 一查看数据库基本信息 1 数据库版本为19C( 相当于 12.2.0.3 版本 ) SQL> select banner_full from v$version; BANNER_FULL --------------------------------------------------------------------- Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 二 检查并 启动归档模式 SQL> archive log list; Database log mode No Archive Mode Automatic archival Disabled Archive destination /u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch Oldest online log sequence 12 Current log sequence 14 [root@cjcos ~]# mkdir /arch [root@cjcos ~]# chown oracle.oinstall /arch SQL> alter system set log_archive_dest_1='location=/arch' scope=both; SQL> alter system set log_archive_format = "cjcpdb_arch_%t_%s_%r.arc" scope=spfile; SQL> shutdown immediate SQL> startup mount SQL> alter database archivelog; SQL> alter database open; SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /arch Oldest online log sequence 12 Next log sequence to archive 14 Current log sequence 14 SQL> alter system switch logfile; [oracle@cjcos ~]$ cd /arch/ [oracle@cjcos arch]$ ll -rth total 74M -rw-r----- 1 oracle oinstall 74M Feb 8 11:04 cjcpdb_arch_1_14_1030641846.arc 三 创建测试数据 SQL> conn sys/oracle@cjcpdb as sysdba Connected. SQL> select name from v$dbfile; NAME --------------------------------------------------------------------- /u01/app/oracle/oradata/CJCDB/cjcpdb/system01.dbf /u01/app/oracle/oradata/CJCDB/cjcpdb/sysaux01.dbf /u01/app/oracle/oradata/CJCDB/cjcpdb/undotbs01.dbf /u01/app/oracle/oradata/CJCDB/cjcpdb/users01.dbf SQL> create tablespace cjctbs datafile '/u01/app/oracle/oradata/CJCDB/cjcpdb/cjctbs01.dbf' size 10M autoextend on; SQL> create user cjc identified by cjc default tablespace cjctbs; SQL> grant connect,resource,dba to cjc; SQL> conn cjc/cjc@cjcpdb SQL> create table t1 as select * from dba_objects; SQL> insert into t1 select* from t1; 72397 rows created. SQL> / 144794 rows created. SQL> commit; SQL> update t1 set object_id=rownum; 289588 rows updated. SQL> commit; Commit complete. 四 ：全库备份 [root@cjcos ~]# mkdir /backup/rman -p [root@cjcos ~]# chown oracle.oinstall /backup -R [oracle@cjcos ~]$ rman target / Recovery Manager: Release 19.0.0.0.0 - Production on Sat Feb 8 11:21:00 2020 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. connected to target database: CJCDB (DBID=3744777523) RMAN> run { allocate channel ch1 type disk; allocate channel ch2 type disk; backup database tag 'full' format '/backup/rman/%d_full_%T_%U.bak'; sql 'alter system archive log current'; backup archivelog all tag 'arch' format '/backup/rman/%d_arch_%T_%U.bak'; backup current controlfile tag 'ctl' format '/backup/rman/%d_ctl_%T_%U.bak'; release channel ch1; release channel ch2; } 查看备份信息 RMAN> list backup of database; [oracle@cjcos rman]$ pwd /backup/rman [oracle@cjcos rman]$ ll -rth total 2.4G -rw-r----- 1 oracle oinstall 415M Feb 8 11:48 CJCDB_full_20200208_13uo0vma_1_1.bak -rw-r----- 1 oracle oinstall 789M Feb 8 11:48 CJCDB_full_20200208_12uo0vma_1_1.bak -rw-r----- 1 oracle oinstall 272M Feb 8 11:49 CJCDB_full_20200208_15uo0vp0_1_1.bak -rw-r----- 1 oracle oinstall 302M Feb 8 11:49 CJCDB_full_20200208_14uo0vp0_1_1.bak -rw-r----- 1 oracle oinstall 305M Feb 8 11:50 CJCDB_full_20200208_16uo0vqd_1_1.bak -rw-r----- 1 oracle oinstall 252M Feb 8 11:50 CJCDB_full_20200208_17uo0vqe_1_1.bak -rw-r----- 1 oracle oinstall 6.3M Feb 8 11:50 CJCDB_arch_20200208_1auo0vrt_1_1.bak -rw-r----- 1 oracle oinstall 74M Feb 8 11:50 CJCDB_arch_20200208_19uo0vrt_1_1.bak -rw-r----- 1 oracle oinstall 125K Feb 8 11:50 CJCDB_arch_20200208_1buo0vs1_1_1.bak -rw-r----- 1 oracle oinstall 18M Feb 8 11:50 CJCDB_ctl_20200208_1cuo0vs2_1_1.bak 五 ：查看当前SCN 等信息 SQL> conn cjc/cjc@cjcpdb Connected. SQL> show con_name user CON_NAME ------------------------------ CJCPDB USER is "CJC" SQL> select count(*) from t1; COUNT(*) ---------- 289588 SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual; TO_CHAR(SYSDATE,'YY ------------------- 2020-02-08 11:53:51 SQL> select current_scn from v$database; CURRENT_SCN ----------- 2301721 六 ：模拟误删除操作 SQL> delete t1 where object_id>10; 289578 rows deleted. SQL> commit; Commit complete. SQL> select count(*) from t1; COUNT(*) ---------- 10 --- 针对 delete 级别的误删除，在时间和 undo 允许的情况下，可以通过闪回将数据迅速找回 SQL> create table t2 as select * from t1 as of timestamp (systimestamp - interval '3' minute); Table created. SQL> select count(*) from t2; COUNT(*) ---------- 289588 本案例使用rman 进行 t1 表恢复，恢复到删除操作之前的时刻。 七 ：使用rman 备份恢复 t1 表 1 创建辅助数据库恢复目录 /auxiliary/recover ，创建 expdp 导出目录 /auxiliary/dumpfiles [root@cjcos ~]# mkdir /auxiliary/{recover,dumpfiles} -p [root@cjcos ~]# chown oracle.oinstall /auxiliary -R [root@cjcos ~]# ll -rth /auxiliary/ total 0 drwxr-xr-x 2 oracle oinstall 6 Feb 8 12:14 recover drwxr-xr-x 2 oracle oinstall 6 Feb 8 12:14 dumpfiles 2 rman执行恢复操作 [oracle@cjcos ~]$ rman target / Recovery Manager: Release 19.0.0.0.0 - Production on Sat Feb 8 12:15:53 2020 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. connected to target database: CJCDB (DBID=3744777523) RMAN> run{ RECOVER TABLE CJC.T1 OF PLUGGABLE DATABASE CJCPDB UNTIL SCN 2301721 AUXILIARY DESTINATION '/auxiliary/recover' DATAPUMP DESTINATION '/auxiliary/dumpfiles' DUMP FILE 't1_scn_2301721.dmp' REMAP TABLE 'CJC'.'T1':'T1_RECVR'; } 1 恢复 CJCPDB 数据库下 CJC 用户下的 T1 表。 2 恢复到 SCN 2301721 时刻。 3 恢复过程中创建的辅助实例恢复位置 /auxiliary/recover 。 4 恢复过程中通过 expdp 导出的备份位置 /auxiliary/dumpfiles ，备份名为 t1_scn_2301721.dmp 。 5 并将恢复数据插入到 T1_RECVR 表中，验证数据没问题后再手动将 T1_RECVR 数据插回到 T1 中。 下面是截取恢复过程中部分信息，均是自动完成，无需人为干预。 ...... ---1 创建辅助实例 DiFo 并恢复数据 Creating automatic instance, with SID='DiFo' initialization parameters used for automatic instance: db_name=CJCDB db_unique_name=DiFo_pitr_CJCPDB_CJCDB compatible=19.0.0 db_block_size=8192 db_files=200 diagnostic_dest=/u01/app/oracle _system_trig_enabled=FALSE sga_target=1104M processes=200 db_create_file_dest=/auxiliary/recover log_archive_dest_1='location=/auxiliary/recover' enable_pluggable_database=true _clone_one_pdb_recovery=true #No auxiliary parameter file used starting up automatic instance CJCDB ...... ---2 基于 scn 恢复 contents of Memory Script: { # set requested point in time set until scn 2301721; # online the datafiles restored or switched sql clone 'CJCPDB' "alter database datafile 13 online"; # recover and open resetlogs recover clone database tablespace "CJCPDB":"CJCTBS", "SYSTEM", "CJCPDB":"SYSTEM", "UNDOTBS1", "CJCPDB":"UNDOTBS1", "SYSAUX", "CJCPDB":"SYSAUX" delete archivelog; alter clone database open resetlogs; } executing Memory Script ...... --- 3 expdp 导出 T1 表数据，到 /auxiliary/dumpfiles/t1_scn_2301721.dmp contents of Memory Script: { # create directory for datapump import sql 'CJCPDB' "create or replace directory TSPITR_DIROBJ_DPDIR as '' /auxiliary/dumpfiles''"; # create directory for datapump export sql clone 'CJCPDB' "create or replace directory TSPITR_DIROBJ_DPDIR as '' /auxiliary/dumpfiles''"; } executing Memory Script ..... Performing export of tables... EXPDP> Starting "SYS"."TSPITR_EXP_DiFo_aBzr": 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 "CJC"."T1" 38.18 MB 289588 rows EXPDP> Master table "SYS"."TSPITR_EXP_DiFo_aBzr" successfully loaded/unloaded EXPDP> ****************************************************************************** EXPDP> Dump file set for SYS.TSPITR_EXP_DiFo_aBzr is: EXPDP> /auxiliary/dumpfiles/t1_scn_2301721.dmp EXPDP> Job "SYS"."TSPITR_EXP_DiFo_aBzr" successfully completed at Sat Feb 8 12:32:57 2020 elapsed 0 00:01:45 Export completed ...... --- 4 impdp 将 T1 数据导入到正式库 T1_RECVR 表中 Performing import of tables... IMPDP> Master table "SYS"."TSPITR_IMP_DiFo_BABf" successfully loaded/unloaded IMPDP> Starting "SYS"."TSPITR_IMP_DiFo_BABf": IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA IMPDP> . . imported "CJC"."T1_RECVR" 38.18 MB 289588 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_DiFo_BABf" successfully completed at Sat Feb 8 12:34:17 2020 elapsed 0 00:00:49 Import completed 八 ：查看数据已经恢复到t1_recvr ---对比数据没问题后可以 选择手动 插回到t1 中 SQL> conn cjc/cjc@cjcpdb SQL> select count(*) from t1; COUNT(*) ---------- 10 SQL> select count(*) from t1_recvr; COUNT(*) ----------

289588

详细信息可参考官方文档：

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/bradv/rman-recovering-tables-partitions.html#GUID-04D1B794-25D0-4C1E-9A98-F08AD361B41E