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

欢迎关注我的微信公众号"IT小Chen"