Oracle 12c Recovering tables and table partitions 表或分割槽級別的恢復
在12c之前，如果我們想將某些表單獨恢復至之前的時間點(不考慮Flashback特性)，通常都是利用RMAN的不一致性恢復功能，將必須的表空間或整個資料庫恢復至另一單獨的例項，例項恢復成功後再將表匯出，然後再將表進行匯入。這個過程還是很麻煩的，而且手工進行容易出錯，恢復時間比較長。
從Oracle 12c開始，可以使用RMAN的RECOVER TABLE命令來現實表或表分割槽級別的不完全恢復。
Recover Table在下面的場景中可能被用到：
1.你只想恢復資料庫中的某幾張表，但發現使用Restore Database或Tablespace的代價很高而且效率很低。
2.對於使用者誤刪除等邏輯方面的錯誤(drop or truncate)，可以使用Recover Table進行恢復。
3.對於undo內容已被覆蓋Flashback Table無法完成恢復的情況下，可以使用Recover Table進行恢復。
Oracle 12c的Recover Tables特性實際上我認為就是將本文開始提到的步驟全部自動化了,我們只需簡單使用幾條命令，剩下全部交由Oracle自動完成。這個新特性是利用建立輔助臨時例項+資料泵工具實現的,通常在進行Recover Table之前我們應該準備好兩個目錄(AUXILIARY DESTINATION和DATAPUMP DESTINATION),一個用來臨時存放輔助例項的資料檔案，另一個用來臨時存放資料泵匯出的檔案。想要使用Recover Tables,我們至少還得有system,sysaux,sysaux和包含了要恢復的表的表空間的備份。如果是PDB，那麼我們必須得擁有ROOT container的undo,system,sysaux的備份和PDB的system,sysaux和包含了要恢復的表的表空間的備份。
從本文下面的實驗日誌中我們可以看出,Recover Table的過程大致如下：
1.在當前的環境中新建一個臨時的輔助例項,輔助例項的資料檔案存放在AUXILIARY DESTINATION指定的位置,這個輔助例項只包括了恢復所必須的system,sysaux,undo和表所在表空間對應的資料檔案。
2.對輔助例項進行不一致性恢復,可基於SCN,Timestamp,Log sequence。
3.使用資料泵工具匯出輔助例項中指定表或分割槽的資料,匯出的檔案存放在DATAPUMP DESTINATION指定位置。
4.使用資料泵工具匯入之前匯出的表(可選),可對錶進行重新命名或指定表空間名稱,也可以利用db link匯入至其他資料庫例項。
5.清除上述過程產生的臨時檔案。
使用Recover Table特性的幾種限制：
1.SYS使用者下的表無法被recovered.
2.SYSTEM和SYSAUX表空間下的表無法被recovered.
3.standby端的表無法被recovered.
4.帶有NOT NULL約束的表無法被用於REMAP選項.
示例1：在PDB中恢復表HR.PDB_EMP,恢復後的表命名為EMP_RECVR
RECOVER TABLE HR.PDB_EMP OF PLUGGABLE DATABASE HR_PDB
UNTIL TIME 'SYSDATE-4'
AUXILIARY DESTINATION '/tmp/backups'
REMAP TABLE 'HR'.'PDB_EMP':'EMP_RECVR';
示例2：從RMAN備份中恢復表SCOTT.EMP,SCOTT.DEPT,並以資料泵格式匯出emp_dept_exp_dump.dat,並不進行表的匯入
RECOVER TABLE SCOTT.EMP, SCOTT.DEPT
UNTIL TIME 'SYSDATE-1'
AUXILIARY DESTINATION '/tmp/oracle/recover'
DATAPUMP DESTINATION '/tmp/recover/dumpfiles'
DUMP FILE 'emp_dept_exp_dump.dat'
NOTABLEIMPORT;
示例3：恢復表的兩個分割槽,恢復後表分割槽重新命名並且放置於SALES_PRE_2000_TS表空間
RECOVER TABLE SH.SALES:SALES_1998, SH.SALES:SALES_1999
UNTIL SEQUENCE 354
AUXILIARY DESTINATION '/tmp/oracle/recover'
REMAP TABLE 'SH'.'SALES':'SALES_1998':'HISTORIC_SALES_1998',
'SH'.'SALES':'SALES_1999':'HISTORIC_SALES_1999'
REMAP TABLESPACE 'SALES_TS':'SALES_PRE_2000_TS';
下面是我在測試環境中進行的實驗：將PDB中的一張表進行不完全恢復，恢復後匯入並重新命名。
SQL> conn pbadm/pbadm@xxxxxx:11521/ora12cpd
Connected.
SQL> select sys_context('userenv','con_name') from dual;
SYS_CONTEXT('USERENV','CON_NAME')
-----------------------------------------------------------
ORA12CPD
SQL> create table tt as select * from dba_objects;
Table created.
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
SQL> select sysdate from dual; --====>>>我們恢復的時間點
SYSDATE
-------------------
2016-05-27 11:09:04
SQL> select count(1) from tt; --===>>>>>表中的原始記錄數
COUNT(1)
----------
90936
SQL> delete from tt; ---=====>>>>模擬使用者誤刪除
90936 rows deleted.
SQL> commit;
Commit complete.
SQL> select count(1) from tt;
COUNT(1)
----------
0
oracle@lzstix0itest12:~> rman target /
Recovery Manager: Release 12.1.0.2.0 - Production on Fri May 27 11:33:00 2016
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORA12C (DBID=290586312)
RMAN> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
using target database control file instead of recovery catalog
Statement processed
RMAN> recover table pbadm.tt of pluggable database ora12cpd
2> until time "to_date('2016-05-27 11:09:04', 'yyyy-mm-dd hh24:mi:ss')"
3> auxiliary destination '/tmp/oracle'
4> remap table 'PBADM'.'TT':'TT_RECVR';
Starting recover at 27-MAY-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=302 device type=DISK
RMAN-05026: WARNING: presuming following set of tablespaces applies to specified Point-in-Time
List of tablespaces expected to have UNDO segments
Tablespace SYSTEM
Tablespace UNDOTBS1
Creating automatic instance, with SID='DErk'
initialization parameters used for automatic instance:
db_name=ORA12C
db_unique_name=DErk_pitr_ora12cpd_ORA12C
compatible=12.1.0.2.0
db_block_size=8192
db_files=200
diagnostic_dest=/u01/app/oracle
_system_trig_enabled=FALSE
sga_target=2560M
processes=200
db_create_file_dest=/tmp/oracle
log_archive_dest_1='location=/tmp/oracle'
enable_pluggable_database=true
_clone_one_pdb_recovery=true
#No auxiliary parameter file used
starting up automatic instance ORA12C --=======>>>>自助建立的輔助例項
Oracle instance started
Total System Global Area 2684354560 bytes
Fixed Size 3714440 bytes
Variable Size 654312056 bytes
Database Buffers 2013265920 bytes
Redo Buffers 13062144 bytes
Automatic instance created
contents of Memory Script:
{
# set requested point in time
set until time "to_date('2016-05-27 11:09:04', 'yyyy-mm-dd hh24:mi:ss')";
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone 'alter database mount clone database';
# archive current online log
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET until clause
Starting restore at 27-MAY-16
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=191 device type=DISK
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORA12C/autobackup/2016_05_27/o1_mf_s_912936317_cnh8hx2x_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORA12C/autobackup/2016_05_27/o1_mf_s_912936317_cnh8hx2x_.bkp tag=TAG20160527T092517
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/tmp/oracle/ORA12C/controlfile/o1_mf_cnhhf5xf_.ctl
Finished restore at 27-MAY-16
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 time "to_date('2016-05-27 11:09:04', 'yyyy-mm-dd hh24:mi:ss')";
# set destinations for recovery set and auxiliary set datafiles
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 datafile 17 to new;
set newname for clone datafile 18 to new;
set newname for clone tempfile 1 to new;
set newname for clone tempfile 4 to new;
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 1, 4, 3, 17, 18; --====>>>>>>1,3,4,17,18號資料檔案為ROOT的system,sysaux,undo和ora12cpd的system,sysaux
switch clone datafile all;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed tempfile 1 to /tmp/oracle/ORA12C/datafile/o1_mf_temp_%u_.tmp in control file
renamed tempfile 4 to /tmp/oracle/ORA12C/datafile/o1_mf_temp_%u_.tmp in control file
Starting restore at 27-MAY-16
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /tmp/oracle/ORA12C/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /tmp/oracle/ORA12C/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /tmp/oracle/ORA12C/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORA12C/backupset/2016_05_27/o1_mf_nnndf_TAG20160527T091026_cnh7n323_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORA12C/backupset/2016_05_27/o1_mf_nnndf_TAG20160527T091026_cnh7n323_.bkp tag=TAG20160527T091026
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00017 to /tmp/oracle/ORA12C/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00018 to /tmp/oracle/ORA12C/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORA12C/33ABAC8133770B4DE0530A562EA0BF77/backupset/2016_05_27/o1_mf_nnndf_TAG20160527T092513_cnh8hsyp_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORA12C/33ABAC8133770B4DE0530A562EA0BF77/backupset/2016_05_27/o1_mf_nnndf_TAG20160527T092513_cnh8hsyp_.bkp tag=TAG20160527T092513
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 27-MAY-16
datafile 1 switched to datafile copy
input datafile copy RECID=8 STAMP=912943425 file name=/tmp/oracle/ORA12C/datafile/o1_mf_system_cnhhfcqv_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=9 STAMP=912943425 file name=/tmp/oracle/ORA12C/datafile/o1_mf_undotbs1_cnhhfcr1_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=10 STAMP=912943425 file name=/tmp/oracle/ORA12C/datafile/o1_mf_sysaux_cnhhfcqy_.dbf
datafile 17 switched to datafile copy
input datafile copy RECID=11 STAMP=912943425 file name=/tmp/oracle/ORA12C/datafile/o1_mf_system_cnhhfttp_.dbf
datafile 18 switched to datafile copy
input datafile copy RECID=12 STAMP=912943425 file name=/tmp/oracle/ORA12C/datafile/o1_mf_sysaux_cnhhfttm_.dbf
contents of Memory Script:
{
# set requested point in time
set until time "to_date('2016-05-27 11:09:04', 'yyyy-mm-dd hh24:mi:ss')";
# 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";
sql clone 'ORA12CPD' "alter database datafile
17 online";
sql clone 'ORA12CPD' "alter database datafile
18 online";
# recover and open database read only
recover clone database tablespace "SYSTEM", "UNDOTBS1", "SYSAUX", "ORA12CPD":"SYSTEM", "ORA12CPD":"SYSAUX";
sql clone 'alter database open read only';
}
executing Memory Script
executing command: SET until clause
sql statement: alter database datafile 1 online
sql statement: alter database datafile 4 online
sql statement: alter database datafile 3 online
sql statement: alter database datafile 17 online
sql statement: alter database datafile 18 online
Starting recover at 27-MAY-16
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 42 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12C/archivelog/2016_05_27/o1_mf_1_42_cnhgksrf_.arc
archived log for thread 1 with sequence 43 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12C/archivelog/2016_05_27/o1_mf_1_43_cnhgnpph_.arc
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12C/archivelog/2016_05_27/o1_mf_1_42_cnhgksrf_.arc thread=1 sequence=42
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12C/archivelog/2016_05_27/o1_mf_1_43_cnhgnpph_.arc thread=1 sequence=43
media recovery complete, elapsed time: 00:00:01
Finished recover at 27-MAY-16
sql statement: alter database open read only
contents of Memory Script:
{
sql clone 'alter pluggable database ORA12CPD open read only';
}
executing Memory Script
sql statement: alter pluggable database ORA12CPD open read only
contents of Memory Script:
{
sql clone "create spfile from memory";
shutdown clone immediate;
startup clone nomount;
sql clone "alter system set control_files =
''/tmp/oracle/ORA12C/controlfile/o1_mf_cnhhf5xf_.ctl'' comment=
''RMAN set'' scope=spfile";
shutdown clone immediate;
startup clone nomount;
# mount database
sql clone 'alter database mount clone database';
}
executing Memory Script
sql statement: create spfile from memory
database closed
database dismounted
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 2684354560 bytes
Fixed Size 3714440 bytes
Variable Size 671089272 bytes
Database Buffers 1996488704 bytes
Redo Buffers 13062144 bytes
sql statement: alter system set control_files = ''/tmp/oracle/ORA12C/controlfile/o1_mf_cnhhf5xf_.ctl'' comment= ''RMAN set'' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 2684354560 bytes
Fixed Size 3714440 bytes
Variable Size 671089272 bytes
Database Buffers 1996488704 bytes
Redo Buffers 13062144 bytes
sql statement: alter database mount clone database
contents of Memory Script:
{
# set requested point in time
set until time "to_date('2016-05-27 11:09:04', 'yyyy-mm-dd hh24:mi:ss')";
# set destinations for recovery set and auxiliary set datafiles
set newname for datafile 19 to new;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 19; --=====>>>>>我ora12cpd中是存在19號檔案的,這裡從歸檔日誌裡新建了19號檔案，是與我建立的表在歸檔日誌中的原因。
switch clone datafile all;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
Starting restore at 27-MAY-16
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=191 device type=DISK
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00019 to /tmp/oracle/DERK_PITR_ORA12CPD_ORA12C/datafile/o1_mf_sales_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORA12C/33ABAC8133770B4DE0530A562EA0BF77/backupset/2016_05_27/o1_mf_nnndf_TAG20160527T092513_cnh8hsyp_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORA12C/33ABAC8133770B4DE0530A562EA0BF77/backupset/2016_05_27/o1_mf_nnndf_TAG20160527T092513_cnh8hsyp_.bkp tag=TAG20160527T092513
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 27-MAY-16
datafile 19 switched to datafile copy
input datafile copy RECID=14 STAMP=912943475 file name=/tmp/oracle/DERK_PITR_ORA12CPD_ORA12C/datafile/o1_mf_sales_cnhhhkyz_.dbf
contents of Memory Script:
{
# set requested point in time
set until time "to_date('2016-05-27 11:09:04', 'yyyy-mm-dd hh24:mi:ss')";
# online the datafiles restored or switched
sql clone 'ORA12CPD' "alter database datafile
19 online";
# recover and open resetlogs
recover clone database tablespace "ORA12CPD":"SALES", "SYSTEM", "UNDOTBS1", "SYSAUX", "ORA12CPD":"SYSTEM", "ORA12CPD":"SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script
executing command: SET until clause
sql statement: alter database datafile 19 online
Starting recover at 27-MAY-16
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 42 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12C/archivelog/2016_05_27/o1_mf_1_42_cnhgksrf_.arc
archived log for thread 1 with sequence 43 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12C/archivelog/2016_05_27/o1_mf_1_43_cnhgnpph_.arc
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12C/archivelog/2016_05_27/o1_mf_1_42_cnhgksrf_.arc thread=1 sequence=42
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12C/archivelog/2016_05_27/o1_mf_1_43_cnhgnpph_.arc thread=1 sequence=43
media recovery complete, elapsed time: 00:00:00
Finished recover at 27-MAY-16
database opened
contents of Memory Script:
{
sql clone 'alter pluggable database ORA12CPD open';
}
executing Memory Script
sql statement: alter pluggable database ORA12CPD open
contents of Memory Script:
{
# create directory for datapump import ---====>>>>建立匯入匯出所需的目錄
sql 'ORA12CPD' "create or replace directory
TSPITR_DIROBJ_DPDIR as ''
/tmp/oracle''";
# create directory for datapump export
sql clone 'ORA12CPD' "create or replace directory
TSPITR_DIROBJ_DPDIR as ''
/tmp/oracle''";
}
executing Memory Script
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/tmp/oracle''
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/tmp/oracle''
Performing export of tables...
EXPDP> Starting "SYS"."TSPITR_EXP_DErk_gqEy":
EXPDP> Estimate in progress using BLOCKS method...
EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
EXPDP> Total estimation using BLOCKS method: 13 MB
EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
EXPDP> . . exported "PBADM"."TT" 10.36 MB 90936 rows ---=====>>>執行表的匯出
EXPDP> Master table "SYS"."TSPITR_EXP_DErk_gqEy" successfully loaded/unloaded
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYS.TSPITR_EXP_DErk_gqEy is:
EXPDP> /tmp/oracle/tspitr_DErk_61246.dmp
EXPDP> Job "SYS"."TSPITR_EXP_DErk_gqEy" successfully completed at Fri May 27 11:24:57 2016 elapsed 0 00:00:15
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_DErk_bgri" successfully loaded/unloaded
IMPDP> Starting "SYS"."TSPITR_IMP_DErk_bgri":
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
IMPDP> . . imported "PBADM"."TT_RECVR" 10.36 MB 90936 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_DErk_bgri" successfully completed at Fri May 27 11:25:20 2016 elapsed 0 00:00:17
Import completed
Removing automatic instance --====>>>>>自動清理生成的臨時檔案
Automatic instance removed
auxiliary instance file /tmp/oracle/ORA12C/datafile/o1_mf_temp_cnhhg50w_.tmp deleted
auxiliary instance file /tmp/oracle/ORA12C/datafile/o1_mf_temp_cnhhg48y_.tmp deleted
auxiliary instance file /tmp/oracle/DERK_PITR_ORA12CPD_ORA12C/onlinelog/o1_mf_3_cnhhhnyk_.log deleted
auxiliary instance file /tmp/oracle/DERK_PITR_ORA12CPD_ORA12C/onlinelog/o1_mf_2_cnhhhntg_.log deleted
auxiliary instance file /tmp/oracle/DERK_PITR_ORA12CPD_ORA12C/onlinelog/o1_mf_1_cnhhhnpv_.log deleted
auxiliary instance file /tmp/oracle/DERK_PITR_ORA12CPD_ORA12C/datafile/o1_mf_sales_cnhhhkyz_.dbf deleted
auxiliary instance file /tmp/oracle/ORA12C/datafile/o1_mf_sysaux_cnhhfttm_.dbf deleted
auxiliary instance file /tmp/oracle/ORA12C/datafile/o1_mf_system_cnhhfttp_.dbf deleted
auxiliary instance file /tmp/oracle/ORA12C/datafile/o1_mf_sysaux_cnhhfcqy_.dbf deleted
auxiliary instance file /tmp/oracle/ORA12C/datafile/o1_mf_undotbs1_cnhhfcr1_.dbf deleted
auxiliary instance file /tmp/oracle/ORA12C/datafile/o1_mf_system_cnhhfcqv_.dbf deleted
auxiliary instance file /tmp/oracle/ORA12C/controlfile/o1_mf_cnhhf5xf_.ctl deleted
auxiliary instance file tspitr_DErk_61246.dmp deleted
Finished recover at 27-MAY-16
RMAN>
--驗證資料
SQL> select count(1) from pbadm.tt_recvr;
COUNT(1)
----------
90936
如此全自動的Recover Table特性,我們是否再也不怕使用者級的誤刪除了呢？瞭解了恢復過程就可以知道,這個過程我們在12c之前也可以手動完成，只不過現在變成自動化了，另外恢復的過程對當前的主機資源要有一定的消耗，是否採用這種恢復方式還得根據現場情況而定。
儘管oracle 12c提供了表或分割槽級別的恢復能力,在實際環境中如果有人誤刪了表，第一選擇仍然是透過回收站或者閃回特性來進行恢復，如果兩種方法均無法恢復,最後才是採用RMAN的Recover Table特性。
從Oracle 12c開始，可以使用RMAN的RECOVER TABLE命令來現實表或表分割槽級別的不完全恢復。
Recover Table在下面的場景中可能被用到：
1.你只想恢復資料庫中的某幾張表，但發現使用Restore Database或Tablespace的代價很高而且效率很低。
2.對於使用者誤刪除等邏輯方面的錯誤(drop or truncate)，可以使用Recover Table進行恢復。
3.對於undo內容已被覆蓋Flashback Table無法完成恢復的情況下，可以使用Recover Table進行恢復。
Oracle 12c的Recover Tables特性實際上我認為就是將本文開始提到的步驟全部自動化了,我們只需簡單使用幾條命令，剩下全部交由Oracle自動完成。這個新特性是利用建立輔助臨時例項+資料泵工具實現的,通常在進行Recover Table之前我們應該準備好兩個目錄(AUXILIARY DESTINATION和DATAPUMP DESTINATION),一個用來臨時存放輔助例項的資料檔案，另一個用來臨時存放資料泵匯出的檔案。想要使用Recover Tables,我們至少還得有system,sysaux,sysaux和包含了要恢復的表的表空間的備份。如果是PDB，那麼我們必須得擁有ROOT container的undo,system,sysaux的備份和PDB的system,sysaux和包含了要恢復的表的表空間的備份。
從本文下面的實驗日誌中我們可以看出,Recover Table的過程大致如下：
1.在當前的環境中新建一個臨時的輔助例項,輔助例項的資料檔案存放在AUXILIARY DESTINATION指定的位置,這個輔助例項只包括了恢復所必須的system,sysaux,undo和表所在表空間對應的資料檔案。
2.對輔助例項進行不一致性恢復,可基於SCN,Timestamp,Log sequence。
3.使用資料泵工具匯出輔助例項中指定表或分割槽的資料,匯出的檔案存放在DATAPUMP DESTINATION指定位置。
4.使用資料泵工具匯入之前匯出的表(可選),可對錶進行重新命名或指定表空間名稱,也可以利用db link匯入至其他資料庫例項。
5.清除上述過程產生的臨時檔案。
使用Recover Table特性的幾種限制：
1.SYS使用者下的表無法被recovered.
2.SYSTEM和SYSAUX表空間下的表無法被recovered.
3.standby端的表無法被recovered.
4.帶有NOT NULL約束的表無法被用於REMAP選項.
示例1：在PDB中恢復表HR.PDB_EMP,恢復後的表命名為EMP_RECVR
RECOVER TABLE HR.PDB_EMP OF PLUGGABLE DATABASE HR_PDB
UNTIL TIME 'SYSDATE-4'
AUXILIARY DESTINATION '/tmp/backups'
REMAP TABLE 'HR'.'PDB_EMP':'EMP_RECVR';
示例2：從RMAN備份中恢復表SCOTT.EMP,SCOTT.DEPT,並以資料泵格式匯出emp_dept_exp_dump.dat,並不進行表的匯入
RECOVER TABLE SCOTT.EMP, SCOTT.DEPT
UNTIL TIME 'SYSDATE-1'
AUXILIARY DESTINATION '/tmp/oracle/recover'
DATAPUMP DESTINATION '/tmp/recover/dumpfiles'
DUMP FILE 'emp_dept_exp_dump.dat'
NOTABLEIMPORT;
示例3：恢復表的兩個分割槽,恢復後表分割槽重新命名並且放置於SALES_PRE_2000_TS表空間
RECOVER TABLE SH.SALES:SALES_1998, SH.SALES:SALES_1999
UNTIL SEQUENCE 354
AUXILIARY DESTINATION '/tmp/oracle/recover'
REMAP TABLE 'SH'.'SALES':'SALES_1998':'HISTORIC_SALES_1998',
'SH'.'SALES':'SALES_1999':'HISTORIC_SALES_1999'
REMAP TABLESPACE 'SALES_TS':'SALES_PRE_2000_TS';
下面是我在測試環境中進行的實驗：將PDB中的一張表進行不完全恢復，恢復後匯入並重新命名。
SQL> conn pbadm/pbadm@xxxxxx:11521/ora12cpd
Connected.
SQL> select sys_context('userenv','con_name') from dual;
SYS_CONTEXT('USERENV','CON_NAME')
-----------------------------------------------------------
ORA12CPD
SQL> create table tt as select * from dba_objects;
Table created.
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
SQL> select sysdate from dual; --====>>>我們恢復的時間點
SYSDATE
-------------------
2016-05-27 11:09:04
SQL> select count(1) from tt; --===>>>>>表中的原始記錄數
COUNT(1)
----------
90936
SQL> delete from tt; ---=====>>>>模擬使用者誤刪除
90936 rows deleted.
SQL> commit;
Commit complete.
SQL> select count(1) from tt;
COUNT(1)
----------
0
oracle@lzstix0itest12:~> rman target /
Recovery Manager: Release 12.1.0.2.0 - Production on Fri May 27 11:33:00 2016
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORA12C (DBID=290586312)
using target database control file instead of recovery catalog
Statement processed
2> until time "to_date('2016-05-27 11:09:04', 'yyyy-mm-dd hh24:mi:ss')"
3> auxiliary destination '/tmp/oracle'
4> remap table 'PBADM'.'TT':'TT_RECVR';
Starting recover at 27-MAY-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=302 device type=DISK
RMAN-05026: WARNING: presuming following set of tablespaces applies to specified Point-in-Time
List of tablespaces expected to have UNDO segments
Tablespace SYSTEM
Tablespace UNDOTBS1
Creating automatic instance, with SID='DErk'
initialization parameters used for automatic instance:
db_name=ORA12C
db_unique_name=DErk_pitr_ora12cpd_ORA12C
compatible=12.1.0.2.0
db_block_size=8192
db_files=200
diagnostic_dest=/u01/app/oracle
_system_trig_enabled=FALSE
sga_target=2560M
processes=200
db_create_file_dest=/tmp/oracle
log_archive_dest_1='location=/tmp/oracle'
enable_pluggable_database=true
_clone_one_pdb_recovery=true
#No auxiliary parameter file used
starting up automatic instance ORA12C --=======>>>>自助建立的輔助例項
Oracle instance started
Total System Global Area 2684354560 bytes
Fixed Size 3714440 bytes
Variable Size 654312056 bytes
Database Buffers 2013265920 bytes
Redo Buffers 13062144 bytes
Automatic instance created
contents of Memory Script:
{
# set requested point in time
set until time "to_date('2016-05-27 11:09:04', 'yyyy-mm-dd hh24:mi:ss')";
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone 'alter database mount clone database';
# archive current online log
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET until clause
Starting restore at 27-MAY-16
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=191 device type=DISK
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORA12C/autobackup/2016_05_27/o1_mf_s_912936317_cnh8hx2x_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORA12C/autobackup/2016_05_27/o1_mf_s_912936317_cnh8hx2x_.bkp tag=TAG20160527T092517
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/tmp/oracle/ORA12C/controlfile/o1_mf_cnhhf5xf_.ctl
Finished restore at 27-MAY-16
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 time "to_date('2016-05-27 11:09:04', 'yyyy-mm-dd hh24:mi:ss')";
# set destinations for recovery set and auxiliary set datafiles
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 datafile 17 to new;
set newname for clone datafile 18 to new;
set newname for clone tempfile 1 to new;
set newname for clone tempfile 4 to new;
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 1, 4, 3, 17, 18; --====>>>>>>1,3,4,17,18號資料檔案為ROOT的system,sysaux,undo和ora12cpd的system,sysaux
switch clone datafile all;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed tempfile 1 to /tmp/oracle/ORA12C/datafile/o1_mf_temp_%u_.tmp in control file
renamed tempfile 4 to /tmp/oracle/ORA12C/datafile/o1_mf_temp_%u_.tmp in control file
Starting restore at 27-MAY-16
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /tmp/oracle/ORA12C/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /tmp/oracle/ORA12C/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /tmp/oracle/ORA12C/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORA12C/backupset/2016_05_27/o1_mf_nnndf_TAG20160527T091026_cnh7n323_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORA12C/backupset/2016_05_27/o1_mf_nnndf_TAG20160527T091026_cnh7n323_.bkp tag=TAG20160527T091026
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00017 to /tmp/oracle/ORA12C/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00018 to /tmp/oracle/ORA12C/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORA12C/33ABAC8133770B4DE0530A562EA0BF77/backupset/2016_05_27/o1_mf_nnndf_TAG20160527T092513_cnh8hsyp_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORA12C/33ABAC8133770B4DE0530A562EA0BF77/backupset/2016_05_27/o1_mf_nnndf_TAG20160527T092513_cnh8hsyp_.bkp tag=TAG20160527T092513
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 27-MAY-16
datafile 1 switched to datafile copy
input datafile copy RECID=8 STAMP=912943425 file name=/tmp/oracle/ORA12C/datafile/o1_mf_system_cnhhfcqv_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=9 STAMP=912943425 file name=/tmp/oracle/ORA12C/datafile/o1_mf_undotbs1_cnhhfcr1_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=10 STAMP=912943425 file name=/tmp/oracle/ORA12C/datafile/o1_mf_sysaux_cnhhfcqy_.dbf
datafile 17 switched to datafile copy
input datafile copy RECID=11 STAMP=912943425 file name=/tmp/oracle/ORA12C/datafile/o1_mf_system_cnhhfttp_.dbf
datafile 18 switched to datafile copy
input datafile copy RECID=12 STAMP=912943425 file name=/tmp/oracle/ORA12C/datafile/o1_mf_sysaux_cnhhfttm_.dbf
contents of Memory Script:
{
# set requested point in time
set until time "to_date('2016-05-27 11:09:04', 'yyyy-mm-dd hh24:mi:ss')";
# 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";
sql clone 'ORA12CPD' "alter database datafile
17 online";
sql clone 'ORA12CPD' "alter database datafile
18 online";
# recover and open database read only
recover clone database tablespace "SYSTEM", "UNDOTBS1", "SYSAUX", "ORA12CPD":"SYSTEM", "ORA12CPD":"SYSAUX";
sql clone 'alter database open read only';
}
executing Memory Script
executing command: SET until clause
sql statement: alter database datafile 1 online
sql statement: alter database datafile 4 online
sql statement: alter database datafile 3 online
sql statement: alter database datafile 17 online
sql statement: alter database datafile 18 online
Starting recover at 27-MAY-16
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 42 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12C/archivelog/2016_05_27/o1_mf_1_42_cnhgksrf_.arc
archived log for thread 1 with sequence 43 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12C/archivelog/2016_05_27/o1_mf_1_43_cnhgnpph_.arc
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12C/archivelog/2016_05_27/o1_mf_1_42_cnhgksrf_.arc thread=1 sequence=42
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12C/archivelog/2016_05_27/o1_mf_1_43_cnhgnpph_.arc thread=1 sequence=43
media recovery complete, elapsed time: 00:00:01
Finished recover at 27-MAY-16
sql statement: alter database open read only
contents of Memory Script:
{
sql clone 'alter pluggable database ORA12CPD open read only';
}
executing Memory Script
sql statement: alter pluggable database ORA12CPD open read only
contents of Memory Script:
{
sql clone "create spfile from memory";
shutdown clone immediate;
startup clone nomount;
sql clone "alter system set control_files =
''/tmp/oracle/ORA12C/controlfile/o1_mf_cnhhf5xf_.ctl'' comment=
''RMAN set'' scope=spfile";
shutdown clone immediate;
startup clone nomount;
# mount database
sql clone 'alter database mount clone database';
}
executing Memory Script
sql statement: create spfile from memory
database closed
database dismounted
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 2684354560 bytes
Fixed Size 3714440 bytes
Variable Size 671089272 bytes
Database Buffers 1996488704 bytes
Redo Buffers 13062144 bytes
sql statement: alter system set control_files = ''/tmp/oracle/ORA12C/controlfile/o1_mf_cnhhf5xf_.ctl'' comment= ''RMAN set'' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 2684354560 bytes
Fixed Size 3714440 bytes
Variable Size 671089272 bytes
Database Buffers 1996488704 bytes
Redo Buffers 13062144 bytes
sql statement: alter database mount clone database
contents of Memory Script:
{
# set requested point in time
set until time "to_date('2016-05-27 11:09:04', 'yyyy-mm-dd hh24:mi:ss')";
# set destinations for recovery set and auxiliary set datafiles
set newname for datafile 19 to new;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 19; --=====>>>>>我ora12cpd中是存在19號檔案的,這裡從歸檔日誌裡新建了19號檔案，是與我建立的表在歸檔日誌中的原因。
switch clone datafile all;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
Starting restore at 27-MAY-16
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=191 device type=DISK
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00019 to /tmp/oracle/DERK_PITR_ORA12CPD_ORA12C/datafile/o1_mf_sales_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORA12C/33ABAC8133770B4DE0530A562EA0BF77/backupset/2016_05_27/o1_mf_nnndf_TAG20160527T092513_cnh8hsyp_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORA12C/33ABAC8133770B4DE0530A562EA0BF77/backupset/2016_05_27/o1_mf_nnndf_TAG20160527T092513_cnh8hsyp_.bkp tag=TAG20160527T092513
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 27-MAY-16
datafile 19 switched to datafile copy
input datafile copy RECID=14 STAMP=912943475 file name=/tmp/oracle/DERK_PITR_ORA12CPD_ORA12C/datafile/o1_mf_sales_cnhhhkyz_.dbf
contents of Memory Script:
{
# set requested point in time
set until time "to_date('2016-05-27 11:09:04', 'yyyy-mm-dd hh24:mi:ss')";
# online the datafiles restored or switched
sql clone 'ORA12CPD' "alter database datafile
19 online";
# recover and open resetlogs
recover clone database tablespace "ORA12CPD":"SALES", "SYSTEM", "UNDOTBS1", "SYSAUX", "ORA12CPD":"SYSTEM", "ORA12CPD":"SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script
executing command: SET until clause
sql statement: alter database datafile 19 online
Starting recover at 27-MAY-16
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 42 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12C/archivelog/2016_05_27/o1_mf_1_42_cnhgksrf_.arc
archived log for thread 1 with sequence 43 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12C/archivelog/2016_05_27/o1_mf_1_43_cnhgnpph_.arc
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12C/archivelog/2016_05_27/o1_mf_1_42_cnhgksrf_.arc thread=1 sequence=42
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12C/archivelog/2016_05_27/o1_mf_1_43_cnhgnpph_.arc thread=1 sequence=43
media recovery complete, elapsed time: 00:00:00
Finished recover at 27-MAY-16
database opened
contents of Memory Script:
{
sql clone 'alter pluggable database ORA12CPD open';
}
executing Memory Script
sql statement: alter pluggable database ORA12CPD open
contents of Memory Script:
{
# create directory for datapump import ---====>>>>建立匯入匯出所需的目錄
sql 'ORA12CPD' "create or replace directory
TSPITR_DIROBJ_DPDIR as ''
/tmp/oracle''";
# create directory for datapump export
sql clone 'ORA12CPD' "create or replace directory
TSPITR_DIROBJ_DPDIR as ''
/tmp/oracle''";
}
executing Memory Script
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/tmp/oracle''
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/tmp/oracle''
Performing export of tables...
EXPDP> Starting "SYS"."TSPITR_EXP_DErk_gqEy":
EXPDP> Estimate in progress using BLOCKS method...
EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
EXPDP> Total estimation using BLOCKS method: 13 MB
EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
EXPDP> . . exported "PBADM"."TT" 10.36 MB 90936 rows ---=====>>>執行表的匯出
EXPDP> Master table "SYS"."TSPITR_EXP_DErk_gqEy" successfully loaded/unloaded
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYS.TSPITR_EXP_DErk_gqEy is:
EXPDP> /tmp/oracle/tspitr_DErk_61246.dmp
EXPDP> Job "SYS"."TSPITR_EXP_DErk_gqEy" successfully completed at Fri May 27 11:24:57 2016 elapsed 0 00:00:15
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_DErk_bgri" successfully loaded/unloaded
IMPDP> Starting "SYS"."TSPITR_IMP_DErk_bgri":
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
IMPDP> . . imported "PBADM"."TT_RECVR" 10.36 MB 90936 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_DErk_bgri" successfully completed at Fri May 27 11:25:20 2016 elapsed 0 00:00:17
Import completed
Removing automatic instance --====>>>>>自動清理生成的臨時檔案
Automatic instance removed
auxiliary instance file /tmp/oracle/ORA12C/datafile/o1_mf_temp_cnhhg50w_.tmp deleted
auxiliary instance file /tmp/oracle/ORA12C/datafile/o1_mf_temp_cnhhg48y_.tmp deleted
auxiliary instance file /tmp/oracle/DERK_PITR_ORA12CPD_ORA12C/onlinelog/o1_mf_3_cnhhhnyk_.log deleted
auxiliary instance file /tmp/oracle/DERK_PITR_ORA12CPD_ORA12C/onlinelog/o1_mf_2_cnhhhntg_.log deleted
auxiliary instance file /tmp/oracle/DERK_PITR_ORA12CPD_ORA12C/onlinelog/o1_mf_1_cnhhhnpv_.log deleted
auxiliary instance file /tmp/oracle/DERK_PITR_ORA12CPD_ORA12C/datafile/o1_mf_sales_cnhhhkyz_.dbf deleted
auxiliary instance file /tmp/oracle/ORA12C/datafile/o1_mf_sysaux_cnhhfttm_.dbf deleted
auxiliary instance file /tmp/oracle/ORA12C/datafile/o1_mf_system_cnhhfttp_.dbf deleted
auxiliary instance file /tmp/oracle/ORA12C/datafile/o1_mf_sysaux_cnhhfcqy_.dbf deleted
auxiliary instance file /tmp/oracle/ORA12C/datafile/o1_mf_undotbs1_cnhhfcr1_.dbf deleted
auxiliary instance file /tmp/oracle/ORA12C/datafile/o1_mf_system_cnhhfcqv_.dbf deleted
auxiliary instance file /tmp/oracle/ORA12C/controlfile/o1_mf_cnhhf5xf_.ctl deleted
auxiliary instance file tspitr_DErk_61246.dmp deleted
Finished recover at 27-MAY-16
RMAN>
--驗證資料
SQL> select count(1) from pbadm.tt_recvr;
COUNT(1)
----------
90936
如此全自動的Recover Table特性,我們是否再也不怕使用者級的誤刪除了呢？瞭解了恢復過程就可以知道,這個過程我們在12c之前也可以手動完成，只不過現在變成自動化了，另外恢復的過程對當前的主機資源要有一定的消耗，是否採用這種恢復方式還得根據現場情況而定。
儘管oracle 12c提供了表或分割槽級別的恢復能力,在實際環境中如果有人誤刪了表，第一選擇仍然是透過回收站或者閃回特性來進行恢復，如果兩種方法均無法恢復,最後才是採用RMAN的Recover Table特性。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26753337/viewspace-2107978/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle 12c系列(十) | 12c中的Recovering Tables and Table PartitionsOracle
- 【12c】12c RMAN新特性之recover table（表級別恢復）
- Oracle分割槽表基礎運維-08Coalescing PartitionsOracle運維
- Oracle drop分割槽表單個分割槽無法透過閃回恢復Oracle
- 【12.2】Oracle 12C R2新特性-外部表支援分割槽了（Partitioning External Tables）Oracle
- 磁碟分割槽表恢復原理
- Oracle 12.2之後ALTER TABLE .. MODIFY轉換非分割槽表為分割槽表Oracle
- oracle分割槽表和分割槽表exchangeOracle
- LVM分割槽恢復LVM
- Oracle 12C新特性-線上把非分割槽錶轉為分割槽表Oracle
- [oracle] expdp 匯出分割槽表的分割槽Oracle
- oracle分割槽表和非分割槽表exchangeOracle
- Oracle 12C新特性-RMAN恢復表Oracle
- oracle 分割槽表move和包含分割槽表的lob moveOracle
- oracle 線上重新定義，普通表改變分割槽表，分割槽表可以更改型別、分割槽欄位等Oracle型別
- ORACLE分割槽表梳理系列Oracle
- Oracle分割槽表基礎運維-04列表分割槽Oracle運維
- Oracle分割槽表基礎運維-07增加分割槽(3列表分割槽)Oracle運維
- 對oracle分割槽表的理解整理Oracle
- 分割槽丟失資料恢復資料恢復
- oracle將表配置為分割槽表Oracle
- oracle 普通表-分割槽表改造流程Oracle
- Oracle分割槽表基礎運維-07增加分割槽(2 HASH分割槽)Oracle運維
- Oracle分割槽表基礎運維-06分割槽表索引Oracle運維索引
- Oracle分割槽表基礎運維-09刪除分割槽Oracle運維
- Oracle分割槽表基礎運維-05組合分割槽Oracle運維
- Oracle分割槽表基礎運維-02範圍分割槽Oracle運維
- Oracle分割槽表基礎運維-03HASH分割槽Oracle運維
- Oracle分割槽表基礎運維-07增加分割槽(1範圍分割槽)Oracle運維
- Oracle分割槽表基礎運維-01分割槽表分類Oracle運維
- Oracle 12c 備份與恢復Oracle
- Oracle SQL調優之分割槽表OracleSQL
- oracle分割槽表的分類及測試Oracle
- 電腦硬碟分割槽表損壞怎麼修復？電腦硬碟分割槽表損壞的修復方法硬碟
- PG的非分割槽表線上轉分割槽表
- 【伺服器資料恢復】XFS檔案系統分割槽分割槽丟失無法訪問的資料恢復案例伺服器資料恢復
- 【北亞資料恢復】伺服器重灌系統後分割槽消失和分割槽不可訪問的資料恢復案例資料恢復伺服器
- ORACLE刪除-表分割槽和資料Oracle
- Oracle 19c Concepts(02)：Tables and Table ClustersOracle