Oracle 12C 新特性用rman備份恢復(fù)表

在12c之前對于用戶因DDL(DROPTRUNCATE )等操作引起的數(shù)據(jù)丟失，一般通過數(shù)據(jù)庫時(shí)間點(diǎn)不完全恢復(fù)、基于表空間的時(shí)間點(diǎn)恢復(fù)（TSPITR）、閃回技術(shù)。

12c 推出了rman基于時(shí)間點(diǎn)對表的恢復(fù)。原理如下：

Recover Table 新特性是利用創(chuàng)建輔助臨時(shí)實(shí)例加數(shù)據(jù)泵工具來實(shí)現(xiàn)的。通常在進(jìn)行Recover Table之前應(yīng)該準(zhǔn)備好兩個(gè)目錄（AUXILIARY DESTINATION和DATAPUMP DESTINATION），AUXILIARY DESTINATION用來臨時(shí)存放輔助實(shí)例的數(shù)據(jù)文件，DATAPUMP DESTINATION用來臨時(shí)存放數(shù)據(jù)泵導(dǎo)出的文件。

一：測試過程如下：

1.

SQL> set lines 60

SQL> desc tb ;

 Name                          Null?    Type

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

 OWNER                                  VARCHAR2(128)

 OBJECT_NAME                            VARCHAR2(128)

 SUBOBJECT_NAME                         VARCHAR2(128)

 OBJECT_ID                              NUMBER

 DATA_OBJECT_ID                         NUMBER

 OBJECT_TYPE                            VARCHAR2(23)

 CREATED                                DATE

 LAST_DDL_TIME                          DATE

SQL> insert into  tb select * from tb ;

72633 rows created.

SQL> commit  ;

Commit complete.

SQL>

SQL> conn / as sysdba

Connected.

2. 切換日志使數(shù)據(jù)寫到磁盤上。

SQL> alter system switch logfile ;

3.rman 備份數(shù)據(jù)庫

Recovery Manager: Release 12.2.0.1.0 -Production on Fri Jun 29 11:41:03 2018

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

connected to target database: MISDB(DBID=4279250949)

RMAN> run

2> {

3> crosscheck archivelog all;

4> crosscheck  backup;

5> delete noprompt obsolete;

6> delete noprompt expired backup;

7> allocate channel d1  type disk ;

8> allocate channel d2 type disk ;

9> backup format '/u01/bk/%T_%d_%s_%p.dat'tag 'full_data' database ;         

10> backup format'/u01/bk/%T_%d_%s_%p.arc' tag 'full_arc' archivelog all delete all input ;    

11> backup format'/u01/bk/%T_%d_%s_%p.ctl' tag 'full_ctl' current controlfile;

12> release channel d1;

13> release channel d2;

14> };

released channel: d1

released channel: d2

RMAN>

SQL> select count(*) from rita.tb ;

 COUNT(*)

----------

   145266

SQL> select current_scn from v$database;

CURRENT_SCN

-----------

2074690

4. 刪除表

SQL> drop table rita.tb  purge;

SQL> select count(*) from rita.tb ;

 selectcount(*) from rita.tb

                           *

ERROR at line 1:

ORA-00942: table or view does not exist

5. 建輔助目錄

[oracle@host01 u01]$ mkdir  recover/

[oracle@host01 u01]$ mkdir  dumpfiles

[oracle@host01 u01]$ ls

app bk  dumpfiles  lost+found recover

[oracle@host01 u01]$ cd dumpfiles/

[oracle@host01 dumpfiles]$ ls

[oracle@host01 dumpfiles]$ cd  ..

[oracle@host01 u01]$

6. 查看數(shù)據(jù)庫備份：

list backup of database;

connected to target database: MISDB(DBID=4279250949)

RMAN>

using target database control file instead ofrecovery catalog

7. 恢復(fù)刪除的表：

rman target /

Recovery Manager: Release 12.2.0.1.0 -Production on Fri Jun 29 15:22:20 2018

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

connected to target database: MISDB(DBID=4279250949)

RMAN> run{

2> RECOVER TABLE rita.tb

3> UNTIL SCN 2074690

4> AUXILIARY DESTINATION '/u01/recover'

5> datapump destination '/u01/dumpfiles';

6> };

Starting recover at 29-JUN-18

using target database control file instead ofrecovery catalog

current log archived

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=52 instance=misdb1device type=DISK

RMAN-05026: warning: presuming following setof tablespaces applies to specified point-in-time

List of tablespaces expected to have UNDOsegments

Tablespace SYSTEM

Tablespace UNDOTBS1

Creating automatic instance, with SID='ygBi' 自動創(chuàng)建一個(gè)數(shù)據(jù)庫實(shí)例

oracle@host01 datafile]$ ps -ef |grep smon

grid      5050      1  0 14:57 ?        00:00:00 asm_smon_+ASM1

root      5144      1  1 14:58 ?        00:00:30/u01/app/12.2.0/grid/bin/osysmond.bin

oracle    7730      1  0 15:00 ?        00:00:00 ora_smon_misdb1

oracle   36939      1  015:28 ?        00:00:00 ora_smon_ygBi

reating automatic instance, with SID='ygBi'

initialization parameters used for automatic instance:

db_name=MISDB

db_unique_name=ygBi_pitr_MISDB

compatible=12.2.0

db_block_size=8192

db_files=200

diagnostic_dest=/u01/app/oracle

_system_trig_enabled=FALSE

sga_target=1728M

processes=200

db_create_file_dest=/u01/recover

log_archive_dest_1='location=/u01/recover' --- 這個(gè)空間要足夠大

#No auxiliary parameter file used

datafile 1 switched to datafile copy

input datafile copy RECID=4 STAMP=980090756 filename=/u01/recover/MISDB/datafile/o1_mf_system_fmcqnsc8_.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=5 STAMP=980090756 filename=/u01/recover/MISDB/datafile/o1_mf_undotbs1_fmcqpx2n_.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=6 STAMP=980090756 filename=/u01/recover/MISDB/datafile/o1_mf_sysaux_fmcqpx17_.dbf

contents of Memory Script:

{

# set requested point in time

set until  scn2074690;

# online the datafiles restored or switched

sql clone "alter database datafile  1 online";

sql clone "alter database datafile  4 online";

sql clone "alter database datafile  3 online";

# recover and open database read only

recover clone database tablespace  "SYSTEM", "UNDOTBS1","SYSAUX";

sql clone 'alter database open read only';

。。。

contents of Memory Script:

{

# create directory for datapump import

sql "create or replace directory TSPITR_DIROBJ_DPDIRas ''

/u01/dumpfiles''";

# create directory for datapump export

sql clone "create or replace directoryTSPITR_DIROBJ_DPDIR as ''

/u01/dumpfiles''";

}

executing Memory Script

sql statement: create or replace directoryTSPITR_DIROBJ_DPDIR as ''/u01/dumpfiles''

sql statement: create or replace directoryTSPITR_DIROBJ_DPDIR as ''/u01/dumpfiles''

Performing export of tables...

  EXPDP> Starting"SYS"."TSPITR_EXP_ygBi_gcph": 

  EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

  EXPDP> Processing object typeTABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

  EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

  EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE

   EXPDP> . .exported "RITA"."TB"                                 19.21 MB  145266 rows

   EXPDP> Mastertable "SYS"."TSPITR_EXP_ygBi_gcph" successfullyloaded/unloaded

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

   EXPDP> Dumpfile set for SYS.TSPITR_EXP_ygBi_gcph is:

   EXPDP>   /u01/dumpfiles/tspitr_ygBi_13106.dmp

   EXPDP> Job"SYS"."TSPITR_EXP_ygBi_gcph" successfully completed at FriJun 29 15:31:30 2018 elapsed 0 00:01:09

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

  IMPDP> Starting"SYS"."TSPITR_IMP_ygBi_yzeq": 

    IMPDP> Processing object typeTABLE_EXPORT/TABLE/TABLE

   IMPDP>Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

   IMPDP> . .imported "RITA"."TB"                                 19.21 MB  145266 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_ygBi_yzeq" successfully completed at FriJun 29 15:32:44 2018 elapsed 0 00:00:41

Import completed

二：從上邊的例子可以看出，恢復(fù)大體流程是

1.rman  根據(jù)指定的表確定備份。

2.rman  根據(jù)指定的recover 路徑恢復(fù)一個(gè)輔助實(shí)例，恢復(fù)到指定的時(shí)間點(diǎn)。

Creating automatic instance, with SID='ygBi'

initialization parameters used for automatic instance:

db_name=MISDB

db_unique_name=ygBi_pitr_MISDB

sql statement: alter database mount clone database

sql statement: alter system archive log current

contents of Memory Script:

{

# set requested point in time

set until  scn2074690;

# set destinations for recovery set and auxiliary setdatafiles

set newname for clone datafile  1 to new;

set newname for clone datafile  4 to new;

set newname for clone datafile  3 to new;

set newname for clone tempfile  1 to new;

3. 用恢復(fù)好的輔助實(shí)例，數(shù)據(jù)庫導(dǎo)出在導(dǎo)入生產(chǎn)環(huán)境

EXPDP> Starting"SYS"."TSPITR_EXP_ygBi_gcph": 

   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

Performing import of tables...

   IMPDP> Mastertable "SYS"."TSPITR_IMP_ygBi_yzeq" successfullyloaded/unloaded

   IMPDP>Starting "SYS"."TSPITR_IMP_ygBi_yzeq": 

   IMPDP>Processing object type TABLE_EXPORT/TABLE/TABLE

   IMPDP>Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

   IMPDP> . .imported "RITA"."TB"                                 19.21 MB  145266 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_ygBi_yzeq" successfully completed at FriJun 29 15:32:44 2018 elapsed 0 00:00:41

Import completed

4. 刪除輔助實(shí)例

恢復(fù)完成。

三：RECOVER TABLE需要注意的幾個(gè)問題：

1. 目標(biāo)數(shù)據(jù)庫必須被置于讀寫模式。

2. 目標(biāo)數(shù)據(jù)庫必須被置于歸檔模式。

3.SYS 用戶下的表或分區(qū)無法恢復(fù)。

4. 存儲于SYSAUX和SYSTEM表空間下的表和分區(qū)無法恢復(fù)。

5.Standby 數(shù)據(jù)庫上的表或表分區(qū)不能進(jìn)行恢復(fù)。

6. 在執(zhí)行“RECOVER TABLE”命令時(shí)，可以根據(jù)需要在以下三種級別指定時(shí)間：

（1）SCN號

（2）Sequence number（日志序列號）

（3）Time：根據(jù)NLS_LANG和NLS_DATE_FORMAT環(huán)境變量中的格式來指定時(shí)間，也可以用SYSDATE，比如"SYSDATE-30"、"to_date('2018-06-29:17:51:48','yyyy-mm-ddhh34:mi:ss')"

參考文檔

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/bradv/rman-recovering-tables-partitions.html#GUID-87B7F772-335F-4179-81C9-91678D026D01