Oracle 12C新特性-RMAN恢復表

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 hh34: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 ch2 type disk;

allocate channel ch3 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 ch2;

release channel ch3;

}

查看備份信息

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 hh34: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

Database Backup and Recovery User's Guide

22.1.3 Basic Concepts of Recovering Tables and Table Partitions from RMAN Backups

...... 

歡迎關注我的微信公眾號"IT小Chen"，共同學習，共同成長！！！