oracle恢復表delete/truncate/drop的方法總結
在有rman備份的情況下,資料庫級別,檔案級別,塊級別這種物理類的恢復相對方便,而因為使用者誤操作或業務bug引起的邏輯類恢復反而有時耗費大量時間.
以下是對表delete/truncate/drop後進行恢復的一些方法總結.
測試環境:
os:centos 6.6
hostname:ct6605
oracle:11.2.0.4
oracle sid:ct66
[oracle@ct6605 ct6604sb]$ ORACLE_SID=ct66
[oracle@ct6605 ct6604sb]$ sqlplus / as sysdba
#建用於測試的表空間
SQL> create tablespace tbs_test datafile '/u02/oradata/ct66/tbs_test01.dbf' size 100m ;
#建用於測試的表
SQL> create table scott.t_delete tablespace tbs_test as select * from dba_objects;
#檢視資料庫的表空間資訊
SQL> select tablespace_name,status from dba_tablespaces;
/*
TABLESPACE_NAME STATUS
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
TBS01 READ ONLY
TBS02 ONLINE
TBS_TEST ONLINE
*/
#檢視資料庫的資料檔案資訊
SQL> select file_name,file_id,tablespace_name,online_status from dba_data_files;
/*
FILE_NAME FILE_ID TABLESPACE_NAME ONLINE_STATUS
/u02/oradata/ct66/users01.dbf 4 USERS ONLINE
/u02/oradata/ct66/undotbs01.dbf 3 UNDOTBS1 ONLINE
/u02/oradata/ct66/sysaux01.dbf 2 SYSAUX ONLINE
/u02/oradata/ct66/system01.dbf 1 SYSTEM SYSTEM
/u02/oradata/ct66/tbs_test01.dbf 5 TBS_TEST ONLINE
/u02/oradata/ct66/tbs01.dbf 6 TBS01 ONLINE
/u02/oradata/ct66/tbs02.dbf 7 TBS02 ONLINE
*/
#檢視資料庫的日誌檔案資訊
SQL> select group#,member from v$logfile;
/*
GROUP# MEMBER
3 /u02/oradata/ct66/redo03.log
2 /u02/oradata/ct66/redo02.log
1 /u02/oradata/ct66/redo01.log
*/
#對資料庫做全備
[oracle@ct6605 ~]$ rman target /
RMAN> backup database plus archivelog delete input;
RMAN> sql 'alter system switch logfile';
#檢視資料庫的當前SCN
[oracle@ct6605 ct6604sb]$ sqlplus / as sysdba
SQL> select current_scn from v$database;
/*
CURRENT_SCN
1637499
*/
#檢視用於測試的表的記錄數
SQL> select count(1) from scott.t_delete;
/*
COUNT(1)
86390
*/
情況1:delete表
SQL> delete from scott.t_delete;
方法1.用flashback query
#查詢undo中此scn或時間點之前的記錄並插入到一個表中,再插入回來.此方法依賴於UNDO_RETENTION,如果保留時間太短且事務繁忙,就無法恢復.
SQL> create table scott.t_recover tablespace tbs_test
as select * from scott.t_delete as of scn 1637499;
方法2.用duplicate database
#透過基於scn或logseq的複製資料庫,將delete的資料恢復到輔助庫,再插入到實際資料庫.
#輔助庫sid:ct6604sb
[oracle@ct6605 ~]$ cd $ORACLE_HOME/dbs
#透過dba_data_files,v$logfile,spfile檢視輔助庫要建的目錄
[oracle@ct6605 dbs]$ strings spfilect66.ora |grep /
ct66.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
*.audit_file_dest='/u01/app/oracle/admin/ct66/adump'
*.control_files='/u02/oradata/ct66/control01.ctl','/u01/app/oracle/fast_
recovery_area/ct66/control02.ctl'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.diagnostic_dest='/u01/app/oracle'
*.log_archive_dest_1='location=/u03/archivelog/ct66'
#建輔助庫目錄
[oracle@ct6605 dbs]$ mkdir -p /u01/app/oracle/admin/ct6604sb/adump
[oracle@ct6605 dbs]$ mkdir -p /u02/oradata/ct6604sb
[oracle@ct6605 dbs]$ mkdir -p /u01/app/oracle/fast_recovery_area/ct6604sb
[oracle@ct6605 dbs]$ mkdir -p /u03/archivelog/ct6604sb
#複製密碼檔案
[oracle@ct6605 dbs]$ cp orapwct66 orapwct6604sb
#新增tnsname
tnsnames.ora
CT6604SB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.108.56.121)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ct6604sb)
)
)
#新增靜態註冊
listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ct6604sb)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = ct6604sb)
)
)
[oracle@ct6605 dbs]$ lsnrctl reload
#複製資料庫
#如果執行duplicate時報,RMAN-06457: UNTIL SCN (xxxxx) is ahead of last SCN in archived logs (xxxxx),要先執行sql 'alter system switch logfile'切換主庫日誌;
#skip readonly,skip tablespace跳過不需要恢復的使用者表空間,節省時間
#經測試,要恢復的表空間如果已刪除,則新增skip tablespace會報錯,無法執行.
[oracle@ct6605 dbs]$ ORACLE_SID=ct6604sb
[oracle@ct6605 dbs]$ rman target /
RMAN> statup nomount
[oracle@ct6605 dbs]$ ORACLE_SID=ct66
[oracle@ct6605 dbs]$ rman target / auxiliary sys/system@ct6604sb
RMAN> run
{
set until scn 1637499;
duplicate target database
to ct6604sb
spfile
parameter_value_convert 'ct66','ct6604sb'
set log_file_name_convert 'ct66','ct6604sb'
db_file_name_convert 'ct66','ct6604sb'
skip readonly
skip tablespace TBS02;
}
#在輔助庫上檢視恢復情況
[oracle@ct6605 dbs]$ ORACLE_SID=ct6604sb
[oracle@ct6605 dbs]$ sqlplus / as sysdba
SQL> select count(1) from scott.t_delete;
/*
COUNT(1)
86390
*/
#透過dblink或imp/exp或impdp/expdp匯入主庫,省略
#清除輔助庫
[oracle@ct6605 dbs]$ ORACLE_SID=ct6604sb
[oracle@ct6605 dbs]$ sqlplus / as sysdba
SQL> shutdown abort
[oracle@ct6605 dbs]$ rm -rf spfilect6604sb.ora
[oracle@ct6605 ct6604sb]$ rm -rf /u02/oradata/ct6604sb/*
方法3.用transport tablespace
#透過transport tablespace自動產生輔助例項併產生要恢復的表所對應的表空間,remap匯入到主庫就行恢復
[oracle@ct6605 dbs]$ ORACLE_SID=ct66
[oracle@ct6605 dbs]$ sqlplus / as sysdba
##確認表空間TBS_TEST是否是自包含
SQL> begin
dbms_tts.transport_set_check('TBS_TEST', true,true);
end;
/
SQL> select * from transport_set_violations;
/**/
#產生要恢復的表所對應的表空間檔案
#經測試,要恢復的表空間如果已刪除,則無法使用此方法.
[oracle@ct6605 dbs]$ rman target /
RMAN> transport tablespace 'TBS_TEST'
tablespace destination '/home/oracle'
auxiliary destination '/home/oracle'
until scn 1637499;
#檢視恢復的表空間檔案及相關檔案
[oracle@ct6605 dbs]$ cd /home/oracle
[oracle@ct6605 ~]$ ll -rth
drwxr-x--- 5 oracle oinstall 4.0K Feb 2 15:19 CT66
-rw-r----- 1 oracle oinstall 101M Feb 2 15:21 tbs_test01.dbf
-rw-r----- 1 oracle oinstall 100K Feb 2 15:21 dmpfile.dmp
-rw-r--r-- 1 oracle oinstall 2.1K Feb 2 15:21 impscrpt.sql
#將產生的檔案匯入主庫
#主庫上原使用者和表空間已存在,需要新建一個臨時使用者
[oracle@ct6605 ~]$ sqlplus / as sysdba
SQL> create user tempuser identified by tempuser;
SQL> create directory temp_dump as '/home/oracle';
#透過impdp匯入,remap使用者,remap表空間
[oracle@ct6605 ~]$ impdp system dumpfile=dmpfile.dmp directory=temp_dump transport_datafiles=/home/oracle/tbs_test01.dbf nologfile=y remap_schema=scott:tempuser remap_tablespace=tbs_test:temp_tbs_test
#檢視要恢復的表
[oracle@ct6605 ~]$ sqlplus / as sysdba
SQL> select count(1) from tempuser.t_delete;
/*
COUNT(1)
86390
*/
#恢復完成後,清理臨時的使用者,表空間,目錄.
[oracle@ct6605 ~]$ sqlplus / as sysdba
SQL> drop user tempuser cascade;
SQL> drop tablespace temp_tbs_test including contents and datafiles;
SQL> drop directory temp_dump;
方法4.用rman做不完全恢復
#如果方法2,3無法恢復,透過最原始的不完全恢復建一個臨時庫來恢復資料
#新建臨時庫的pfile
[oracle@ct6605 ~]$ cd $ORACLE_HOME/dbs
[oracle@ct6605 dbs]$ cat > initct6604sb.ora <<eof
db_name=ct66
db_unique_name=ct66temp
compatible=11.2.0.4.0
db_block_size=8192
db_files=200
sga_target=280M
processes=50
db_create_file_dest=/home/oracle/db_ct66temp
log_archive_dest_1='location=/home/oracle/db_ct66temp'
EOF
#檢視主庫的備份資訊,用於臨時庫恢復時呼叫
[oracle@ct6605 dbs]$ ORACLE_SID=ct66
[oracle@ct6605 dbs]$ rman target /
#在主庫上切換日誌,防止RMAN-06457
RMAN> sql 'alter system switch logfile';
#檢視備份的控制檔案資訊
RMAN> list backup of controlfile;
using target database control file instead of recovery catalog
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
53 Full 9.52M DISK 00:00:02 02-FEB-16
BP Key: 53 Status: AVAILABLE Compressed: NO Tag: TAG20160202T145323
Piece Name: /u01/app/oracle/fast_recovery_area/CT66/backupset/2016_02_02/o1_mf_ncsnf_TAG20160202T145323_cc0no66d_.bkp
Control File Included: Ckp SCN: 1637438 Ckp time: 02-FEB-16
#檢視備份的歸檔日誌資訊
RMAN> list backup of archivelog all;
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
51 15.17M DISK 00:00:00 02-FEB-16
BP Key: 51 Status: AVAILABLE Compressed: NO Tag: TAG20160202T145322
Piece Name: /u01/app/oracle/fast_recovery_area/CT66/backupset/2016_02_02/o1_mf_annnn_TAG20160202T145322_cc0nm2gg_.bkp
List of Archived Logs in backup set 51
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 98 1631079 02-FEB-16 1637404 02-FEB-16
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
54 3.50K DISK 00:00:00 02-FEB-16
BP Key: 54 Status: AVAILABLE Compressed: NO Tag: TAG20160202T145431
Piece Name: /u01/app/oracle/fast_recovery_area/CT66/backupset/2016_02_02/o1_mf_annnn_TAG20160202T145431_cc0no7rn_.bkp
List of Archived Logs in backup set 54
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 99 1637404 02-FEB-16 1637444 02-FEB-16
#檢視歸檔日誌資訊
RMAN> list archivelog all;
List of Archived Log Copies for database with db_unique_name CT66
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
96 1 100 A 02-FEB-16
Name: /u03/archivelog/ct66/1_100_899919619.dbf
97 1 101 A 02-FEB-16
Name: /u03/archivelog/ct66/1_101_899919619.dbf
98 1 102 A 02-FEB-16
Name: /u03/archivelog/ct66/1_102_899919619.dbf
#啟動臨時庫到nomount
[oracle@ct6605 dbs]$ ORACLE_SID=ct6604sb
[oracle@ct6605 dbs]$ rman target /
RMAN> startup nomount
#還原控制檔案並mount
RMAN> restore controlfile from '/u01/app/oracle/fast_recovery_area/CT66/backupset/2016_02_02/o1_mf_ncsnf_TAG20160202T145323_cc0no66d_.bkp';
RMAN> sql 'alter database mount';
#在控制檔案中加入備份歸檔日誌資訊和歸檔日誌資訊
RMAN> catalog start with '/u01/app/oracle/fast_recovery_area/CT66/backupset/2016_02_02/';
RMAN> catalog start with '/u03/archivelog/ct66/';
#restore必要的檔案
RMAN> run
{
set until scn 1637499;
set newname for datafile 1 to new;
set newname for datafile 2 to new;
set newname for datafile 3 to new;
set newname for datafile 5 to new;
set newname for tempfile 1 to new;
switch tempfile all;
restore datafile 1,2,3,5;
switch datafile all;
}
#recover 資料庫並跳過不必要的表空間
RMAN> run
{
set until scn 1637499;
sql 'alter database datafile 1 online';
sql 'alter database datafile 2 online';
sql 'alter database datafile 3 online';
sql 'alter database datafile 5 online';
recover database skip forever tablespace TBS01,USERS,TBS02 ;
}
#修改redolog位置
RMAN> sql "alter database rename file ''/u02/oradata/ct66/redo01.log'' to ''/home/oracle/db_ct66temp/redo01.log''";
RMAN> sql "alter database rename file ''/u02/oradata/ct66/redo02.log'' to ''/home/oracle/db_ct66temp/redo02.log''";
RMAN> sql "alter database rename file ''/u02/oradata/ct66/redo03.log'' to ''/home/oracle/db_ct66temp/redo03.log''";
#開啟資料庫
RMAN> alter database open resetlogs;
#檢視臨時庫恢復出來的表資料,匯入主庫略
[oracle@ct6605 dbs]$ ORACLE_SID=ct6604sb
[oracle@ct6605 dbs]$ sqlplus / as sysdba
SQL> select count(1) from scott.t_delete;
/*
COUNT(1)
86390
*/
#恢復完成,清除臨時庫
[oracle@ct6605 dbs]$ ORACLE_SID=ct6604sb
[oracle@ct6605 dbs]$ sqlplus / as sysdba
[oracle@ct6605 dbs]$ cd ~
[oracle@ct6605 ~]$ rm -rf db_ct66temp
方法5.其它
#如果表被delete,truncate,drop既無備份,又無法flashbackup,可以考慮odu工具.
odu主要是透過表在被刪除時,並沒有立即真正清除掉資料的原理.可參考).
#據說12c有基於時間點的表自動化恢復
沒用過,估計也是透過類似tspitr的原理.
情況2:truncate表
SQL> truncate table scott.t_delete;
方法:同情況1的方法2,3,4,5.
情況3:drop表
SQL> drop table scott.t_delete;
方法1:用flashback drop
#在預設開啟回收站recyclebin的情況下,透過flashback table
#檢視錶scott.t_delete在recyclebin的狀態
SQL> select * from dba_recyclebin where owner='SCOTT' and original_name=upper('t_delete');
/*
OWNER OBJECT_NAME ORIGINAL_NAME OPERATION TYPE TS_NAME CREATETIME DROPTIME DROPSCN PARTITION_NAME CAN_UNDROP CAN_PURGE RELATED BASE_OBJECT PURGE_OBJECT SPACE
SCOTT BIN$KsbWNbVJWr7gU3k4bMCO0Q==$0 T_DELETE DROP TABLE TBS_TEST 2016-02-02:14:45:32 2016-02-02:17:07:54 1643183 YES YES 88737 88737 88737 1280
*/
#flashback table
SQL> flashback table scott.t_delete to before drop;
或者SQL> flashback table scott."BIN$KsbWNbVJWr7gU3k4bMCO0Q==$0" to before drop;
#表已恢復
SQL> select count(1) from scott.t_delete;
其它方法:同情況1的方法2,3,4,5.
</eof
以下是對表delete/truncate/drop後進行恢復的一些方法總結.
測試環境:
os:centos 6.6
hostname:ct6605
oracle:11.2.0.4
oracle sid:ct66
[oracle@ct6605 ct6604sb]$ ORACLE_SID=ct66
[oracle@ct6605 ct6604sb]$ sqlplus / as sysdba
#建用於測試的表空間
SQL> create tablespace tbs_test datafile '/u02/oradata/ct66/tbs_test01.dbf' size 100m ;
#建用於測試的表
SQL> create table scott.t_delete tablespace tbs_test as select * from dba_objects;
#檢視資料庫的表空間資訊
SQL> select tablespace_name,status from dba_tablespaces;
/*
TABLESPACE_NAME STATUS
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
TBS01 READ ONLY
TBS02 ONLINE
TBS_TEST ONLINE
*/
#檢視資料庫的資料檔案資訊
SQL> select file_name,file_id,tablespace_name,online_status from dba_data_files;
/*
FILE_NAME FILE_ID TABLESPACE_NAME ONLINE_STATUS
/u02/oradata/ct66/users01.dbf 4 USERS ONLINE
/u02/oradata/ct66/undotbs01.dbf 3 UNDOTBS1 ONLINE
/u02/oradata/ct66/sysaux01.dbf 2 SYSAUX ONLINE
/u02/oradata/ct66/system01.dbf 1 SYSTEM SYSTEM
/u02/oradata/ct66/tbs_test01.dbf 5 TBS_TEST ONLINE
/u02/oradata/ct66/tbs01.dbf 6 TBS01 ONLINE
/u02/oradata/ct66/tbs02.dbf 7 TBS02 ONLINE
*/
#檢視資料庫的日誌檔案資訊
SQL> select group#,member from v$logfile;
/*
GROUP# MEMBER
3 /u02/oradata/ct66/redo03.log
2 /u02/oradata/ct66/redo02.log
1 /u02/oradata/ct66/redo01.log
*/
#對資料庫做全備
[oracle@ct6605 ~]$ rman target /
RMAN> backup database plus archivelog delete input;
RMAN> sql 'alter system switch logfile';
#檢視資料庫的當前SCN
[oracle@ct6605 ct6604sb]$ sqlplus / as sysdba
SQL> select current_scn from v$database;
/*
CURRENT_SCN
1637499
*/
#檢視用於測試的表的記錄數
SQL> select count(1) from scott.t_delete;
/*
COUNT(1)
86390
*/
情況1:delete表
SQL> delete from scott.t_delete;
方法1.用flashback query
#查詢undo中此scn或時間點之前的記錄並插入到一個表中,再插入回來.此方法依賴於UNDO_RETENTION,如果保留時間太短且事務繁忙,就無法恢復.
SQL> create table scott.t_recover tablespace tbs_test
as select * from scott.t_delete as of scn 1637499;
方法2.用duplicate database
#透過基於scn或logseq的複製資料庫,將delete的資料恢復到輔助庫,再插入到實際資料庫.
#輔助庫sid:ct6604sb
[oracle@ct6605 ~]$ cd $ORACLE_HOME/dbs
#透過dba_data_files,v$logfile,spfile檢視輔助庫要建的目錄
[oracle@ct6605 dbs]$ strings spfilect66.ora |grep /
ct66.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
*.audit_file_dest='/u01/app/oracle/admin/ct66/adump'
*.control_files='/u02/oradata/ct66/control01.ctl','/u01/app/oracle/fast_
recovery_area/ct66/control02.ctl'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.diagnostic_dest='/u01/app/oracle'
*.log_archive_dest_1='location=/u03/archivelog/ct66'
#建輔助庫目錄
[oracle@ct6605 dbs]$ mkdir -p /u01/app/oracle/admin/ct6604sb/adump
[oracle@ct6605 dbs]$ mkdir -p /u02/oradata/ct6604sb
[oracle@ct6605 dbs]$ mkdir -p /u01/app/oracle/fast_recovery_area/ct6604sb
[oracle@ct6605 dbs]$ mkdir -p /u03/archivelog/ct6604sb
#複製密碼檔案
[oracle@ct6605 dbs]$ cp orapwct66 orapwct6604sb
#新增tnsname
tnsnames.ora
CT6604SB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.108.56.121)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ct6604sb)
)
)
#新增靜態註冊
listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ct6604sb)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = ct6604sb)
)
)
[oracle@ct6605 dbs]$ lsnrctl reload
#複製資料庫
#如果執行duplicate時報,RMAN-06457: UNTIL SCN (xxxxx) is ahead of last SCN in archived logs (xxxxx),要先執行sql 'alter system switch logfile'切換主庫日誌;
#skip readonly,skip tablespace跳過不需要恢復的使用者表空間,節省時間
#經測試,要恢復的表空間如果已刪除,則新增skip tablespace會報錯,無法執行.
[oracle@ct6605 dbs]$ ORACLE_SID=ct6604sb
[oracle@ct6605 dbs]$ rman target /
RMAN> statup nomount
[oracle@ct6605 dbs]$ ORACLE_SID=ct66
[oracle@ct6605 dbs]$ rman target / auxiliary sys/system@ct6604sb
RMAN> run
{
set until scn 1637499;
duplicate target database
to ct6604sb
spfile
parameter_value_convert 'ct66','ct6604sb'
set log_file_name_convert 'ct66','ct6604sb'
db_file_name_convert 'ct66','ct6604sb'
skip readonly
skip tablespace TBS02;
}
#在輔助庫上檢視恢復情況
[oracle@ct6605 dbs]$ ORACLE_SID=ct6604sb
[oracle@ct6605 dbs]$ sqlplus / as sysdba
SQL> select count(1) from scott.t_delete;
/*
COUNT(1)
86390
*/
#透過dblink或imp/exp或impdp/expdp匯入主庫,省略
#清除輔助庫
[oracle@ct6605 dbs]$ ORACLE_SID=ct6604sb
[oracle@ct6605 dbs]$ sqlplus / as sysdba
SQL> shutdown abort
[oracle@ct6605 dbs]$ rm -rf spfilect6604sb.ora
[oracle@ct6605 ct6604sb]$ rm -rf /u02/oradata/ct6604sb/*
方法3.用transport tablespace
#透過transport tablespace自動產生輔助例項併產生要恢復的表所對應的表空間,remap匯入到主庫就行恢復
[oracle@ct6605 dbs]$ ORACLE_SID=ct66
[oracle@ct6605 dbs]$ sqlplus / as sysdba
##確認表空間TBS_TEST是否是自包含
SQL> begin
dbms_tts.transport_set_check('TBS_TEST', true,true);
end;
/
SQL> select * from transport_set_violations;
/**/
#產生要恢復的表所對應的表空間檔案
#經測試,要恢復的表空間如果已刪除,則無法使用此方法.
[oracle@ct6605 dbs]$ rman target /
RMAN> transport tablespace 'TBS_TEST'
tablespace destination '/home/oracle'
auxiliary destination '/home/oracle'
until scn 1637499;
#檢視恢復的表空間檔案及相關檔案
[oracle@ct6605 dbs]$ cd /home/oracle
[oracle@ct6605 ~]$ ll -rth
drwxr-x--- 5 oracle oinstall 4.0K Feb 2 15:19 CT66
-rw-r----- 1 oracle oinstall 101M Feb 2 15:21 tbs_test01.dbf
-rw-r----- 1 oracle oinstall 100K Feb 2 15:21 dmpfile.dmp
-rw-r--r-- 1 oracle oinstall 2.1K Feb 2 15:21 impscrpt.sql
#將產生的檔案匯入主庫
#主庫上原使用者和表空間已存在,需要新建一個臨時使用者
[oracle@ct6605 ~]$ sqlplus / as sysdba
SQL> create user tempuser identified by tempuser;
SQL> create directory temp_dump as '/home/oracle';
#透過impdp匯入,remap使用者,remap表空間
[oracle@ct6605 ~]$ impdp system dumpfile=dmpfile.dmp directory=temp_dump transport_datafiles=/home/oracle/tbs_test01.dbf nologfile=y remap_schema=scott:tempuser remap_tablespace=tbs_test:temp_tbs_test
#檢視要恢復的表
[oracle@ct6605 ~]$ sqlplus / as sysdba
SQL> select count(1) from tempuser.t_delete;
/*
COUNT(1)
86390
*/
#恢復完成後,清理臨時的使用者,表空間,目錄.
[oracle@ct6605 ~]$ sqlplus / as sysdba
SQL> drop user tempuser cascade;
SQL> drop tablespace temp_tbs_test including contents and datafiles;
SQL> drop directory temp_dump;
方法4.用rman做不完全恢復
#如果方法2,3無法恢復,透過最原始的不完全恢復建一個臨時庫來恢復資料
#新建臨時庫的pfile
[oracle@ct6605 ~]$ cd $ORACLE_HOME/dbs
[oracle@ct6605 dbs]$ cat > initct6604sb.ora <<eof
db_name=ct66
db_unique_name=ct66temp
compatible=11.2.0.4.0
db_block_size=8192
db_files=200
sga_target=280M
processes=50
db_create_file_dest=/home/oracle/db_ct66temp
log_archive_dest_1='location=/home/oracle/db_ct66temp'
EOF
#檢視主庫的備份資訊,用於臨時庫恢復時呼叫
[oracle@ct6605 dbs]$ ORACLE_SID=ct66
[oracle@ct6605 dbs]$ rman target /
#在主庫上切換日誌,防止RMAN-06457
RMAN> sql 'alter system switch logfile';
#檢視備份的控制檔案資訊
RMAN> list backup of controlfile;
using target database control file instead of recovery catalog
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
53 Full 9.52M DISK 00:00:02 02-FEB-16
BP Key: 53 Status: AVAILABLE Compressed: NO Tag: TAG20160202T145323
Piece Name: /u01/app/oracle/fast_recovery_area/CT66/backupset/2016_02_02/o1_mf_ncsnf_TAG20160202T145323_cc0no66d_.bkp
Control File Included: Ckp SCN: 1637438 Ckp time: 02-FEB-16
#檢視備份的歸檔日誌資訊
RMAN> list backup of archivelog all;
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
51 15.17M DISK 00:00:00 02-FEB-16
BP Key: 51 Status: AVAILABLE Compressed: NO Tag: TAG20160202T145322
Piece Name: /u01/app/oracle/fast_recovery_area/CT66/backupset/2016_02_02/o1_mf_annnn_TAG20160202T145322_cc0nm2gg_.bkp
List of Archived Logs in backup set 51
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 98 1631079 02-FEB-16 1637404 02-FEB-16
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
54 3.50K DISK 00:00:00 02-FEB-16
BP Key: 54 Status: AVAILABLE Compressed: NO Tag: TAG20160202T145431
Piece Name: /u01/app/oracle/fast_recovery_area/CT66/backupset/2016_02_02/o1_mf_annnn_TAG20160202T145431_cc0no7rn_.bkp
List of Archived Logs in backup set 54
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 99 1637404 02-FEB-16 1637444 02-FEB-16
#檢視歸檔日誌資訊
RMAN> list archivelog all;
List of Archived Log Copies for database with db_unique_name CT66
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
96 1 100 A 02-FEB-16
Name: /u03/archivelog/ct66/1_100_899919619.dbf
97 1 101 A 02-FEB-16
Name: /u03/archivelog/ct66/1_101_899919619.dbf
98 1 102 A 02-FEB-16
Name: /u03/archivelog/ct66/1_102_899919619.dbf
#啟動臨時庫到nomount
[oracle@ct6605 dbs]$ ORACLE_SID=ct6604sb
[oracle@ct6605 dbs]$ rman target /
RMAN> startup nomount
#還原控制檔案並mount
RMAN> restore controlfile from '/u01/app/oracle/fast_recovery_area/CT66/backupset/2016_02_02/o1_mf_ncsnf_TAG20160202T145323_cc0no66d_.bkp';
RMAN> sql 'alter database mount';
#在控制檔案中加入備份歸檔日誌資訊和歸檔日誌資訊
RMAN> catalog start with '/u01/app/oracle/fast_recovery_area/CT66/backupset/2016_02_02/';
RMAN> catalog start with '/u03/archivelog/ct66/';
#restore必要的檔案
RMAN> run
{
set until scn 1637499;
set newname for datafile 1 to new;
set newname for datafile 2 to new;
set newname for datafile 3 to new;
set newname for datafile 5 to new;
set newname for tempfile 1 to new;
switch tempfile all;
restore datafile 1,2,3,5;
switch datafile all;
}
#recover 資料庫並跳過不必要的表空間
RMAN> run
{
set until scn 1637499;
sql 'alter database datafile 1 online';
sql 'alter database datafile 2 online';
sql 'alter database datafile 3 online';
sql 'alter database datafile 5 online';
recover database skip forever tablespace TBS01,USERS,TBS02 ;
}
#修改redolog位置
RMAN> sql "alter database rename file ''/u02/oradata/ct66/redo01.log'' to ''/home/oracle/db_ct66temp/redo01.log''";
RMAN> sql "alter database rename file ''/u02/oradata/ct66/redo02.log'' to ''/home/oracle/db_ct66temp/redo02.log''";
RMAN> sql "alter database rename file ''/u02/oradata/ct66/redo03.log'' to ''/home/oracle/db_ct66temp/redo03.log''";
#開啟資料庫
RMAN> alter database open resetlogs;
#檢視臨時庫恢復出來的表資料,匯入主庫略
[oracle@ct6605 dbs]$ ORACLE_SID=ct6604sb
[oracle@ct6605 dbs]$ sqlplus / as sysdba
SQL> select count(1) from scott.t_delete;
/*
COUNT(1)
86390
*/
#恢復完成,清除臨時庫
[oracle@ct6605 dbs]$ ORACLE_SID=ct6604sb
[oracle@ct6605 dbs]$ sqlplus / as sysdba
[oracle@ct6605 dbs]$ cd ~
[oracle@ct6605 ~]$ rm -rf db_ct66temp
方法5.其它
#如果表被delete,truncate,drop既無備份,又無法flashbackup,可以考慮odu工具.
odu主要是透過表在被刪除時,並沒有立即真正清除掉資料的原理.可參考).
#據說12c有基於時間點的表自動化恢復
沒用過,估計也是透過類似tspitr的原理.
情況2:truncate表
SQL> truncate table scott.t_delete;
方法:同情況1的方法2,3,4,5.
情況3:drop表
SQL> drop table scott.t_delete;
方法1:用flashback drop
#在預設開啟回收站recyclebin的情況下,透過flashback table
#檢視錶scott.t_delete在recyclebin的狀態
SQL> select * from dba_recyclebin where owner='SCOTT' and original_name=upper('t_delete');
/*
OWNER OBJECT_NAME ORIGINAL_NAME OPERATION TYPE TS_NAME CREATETIME DROPTIME DROPSCN PARTITION_NAME CAN_UNDROP CAN_PURGE RELATED BASE_OBJECT PURGE_OBJECT SPACE
SCOTT BIN$KsbWNbVJWr7gU3k4bMCO0Q==$0 T_DELETE DROP TABLE TBS_TEST 2016-02-02:14:45:32 2016-02-02:17:07:54 1643183 YES YES 88737 88737 88737 1280
*/
#flashback table
SQL> flashback table scott.t_delete to before drop;
或者SQL> flashback table scott."BIN$KsbWNbVJWr7gU3k4bMCO0Q==$0" to before drop;
#表已恢復
SQL> select count(1) from scott.t_delete;
其它方法:同情況1的方法2,3,4,5.
</eof
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28539951/viewspace-1985713/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- truncate表後恢復方法總結
- RMAN恢復單個表空間或被DROP/DELETE/TRUNCATE的表delete
- drop、delete 與truncatedelete
- Oracle Truncate表恢復(ODU)Oracle
- Truncate,Delete,Drop的比較.delete
- truncate delete drop 區別delete
- ORACLE—DELETE表後的恢復Oracledelete
- Oracle Delete表恢復(ODU)Oracledelete
- 恢復oracle中drop掉的表Oracle
- truncate,delete,drop的異同點delete
- Oracle Drop表(purge)恢復(ODU)Oracle
- 簡述truncate、delete和dropdelete
- HWM和delete，drop，truncate的關係delete
- truncate,delete,drop的異同點（原）delete
- 關於delete,drop,truncate的問題delete
- SQL truncate 、delete與drop區別SQLdelete
- 利用Log Explorer將你已經delete,truncate,drop過的資料進行恢復delete
- mysql恢復drop表MySql
- 資料庫：drop、truncate、delete的區別資料庫delete
- 使用ODU恢復oracle被truncate的表資料Oracle
- 詳解SQL中drop、delete和truncate的異同SQLdelete
- Truncate table 詳解及與delete,drop 的區別delete
- Truncate table詳解及與delete,drop的區別delete
- oracle之DELETE後的恢復Oracledelete
- 【資料庫資料恢復】如何恢復Oracle資料庫truncate表的資料資料庫資料恢復Oracle
- 資料庫關鍵詞 drop、truncate和delete的用法資料庫delete
- truncate和不帶where子句的delete, 以及drop區別delete
- 【北亞資料恢復】誤操作導致雲伺服器表被truncate，表內資料被delete的資料恢復資料恢復伺服器delete
- 使用ODU恢復被truncate表的資料
- 恢復Oracle表空間的方法Oracle
- 循序漸進oracle第8章：Oracle的閃回特性之恢復drop表四種方法Oracle
- 使用hellodba的工具恢復truncate表的資料
- oracle truncate 與 delete 的區別Oracledelete
- Oracle中truncate table後的資料恢復(Oracle資料恢復工具-ODU)Oracle資料恢復
- oracle logminer恢復truncate table的資料Oracle
- 恢復MySQL InnoDB表結構的方法MySql
- 恢復被執行truncate table的表資料
- Oracle 10g 中誤刪除(drop)表的恢復處理Oracle 10g