【12c】新特性：使用RMAN恢復表或表分區(qū)

Coder編程 2022-03-26

展開全文

當對一張表進行誤操作，比如刪除了數(shù)據(jù)、drop表，或者truncate表，我們可以通過使用閃回表、閃回drop，或者TSPITR可以進行恢復，但在以下場景下，上面的恢復技術將會束手無策：

表邏輯損壞或使用了purge選項的刪除；
UNDO不可用時，便不可使用閃回技術對表進行恢復；
對表誤操作后，執(zhí)行了DDL操作；
使用TSPITR，將會恢復表空間里的所有對象，不利于只恢復某些表的場景。

從Oracle 12c版本開始，Oracle引入了可以從RMAN備份中進行表和表分區(qū)的恢復，這樣可大大簡化了對表和表分區(qū)的恢復，本篇將對這一新特性進行演示。

1 備份數(shù)據(jù)庫

[oracle@odd ~]$ rman target /



Recovery Manager: Release 12.1.0.2.0 - Production on Sun May 3 16:15:03 2020



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



connected to target database: ORCL (DBID=1567069190)



RMAN> backup database;

------------------------------------------------備份過程省略----------------------------------------------

2 創(chuàng)建測試數(shù)據(jù)

SQL> create table alen(id number,name varchar2(100));



Table created.



SQL> insert into alen values(1,'Alen');



1 row created.



SQL> commit;



Commit complete.

SQL> drop table alen purge;



Table dropped.



SQL> show recyclebin;

3 利用RMAN備份進行恢復

RMAN> recover table scott.alen

2> until time '2020-05-03 16:38:03'

3> auxiliary destination '/home/oracle/recover'

4> datapump destination '/home/oracle/dumpfiles'

5> dump file 'scott.alen.dat'

6> notableimport;

RECOVER TABLE語法，可參考：https://docs.oracle.com/database/121/RCMRF/rcmsynta2001.htm#GUID-CA98040F-9865-4F4F-BAF2-91C518612E95

4 查看DUMP文件

[oracle@odd ~]$ ll dumpfiles/

total 140

-rw-r----- 1 oracle oinstall 143360 May 3 16:44 scott.alen.dat

5 使用IMPDP導入

[oracle@odd ~]$ cd /u01/app/oracle/admin/ORCL/dpdump/

[oracle@odd dpdump]$ cp /home/oracle/dumpfiles/scott.alen.dat ./

[oracle@odd dpdump]$ ll

total 144

-rw-r-----. 1 oracle oinstall 116 May 1 19:19 dp.log

-rw-r----- 1 oracle oinstall 143360 May 3 16:48 scott.alen.dat

[oracle@odd dpdump]$ impdp system/system directory=DATA_PUMP_DIR dumpfile=scott.alen.dat tables=scott.alen



Import: Release 12.1.0.2.0 - Production on Sun May 3 16:53:14 2020



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



Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded

Starting "SYSTEM"."SYS_IMPORT_TABLE_01": system/******** directory=DATA_PUMP_DIR dumpfile=scott.alen.dat tables=scott.alen

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported "SCOTT"."ALEN" 5.476 KB 1 rows

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at Sun May 3 16:53:20 2020 elapsed 0 00:00:03

6 驗證結果

SQL> select * from alen;



ID NAME

---------- ----------------------------------------------------------------------------------------------------

1 Alen