以下是对表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 <
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主要是通过表在被删除时,并没有立即真正清除掉数据的原理.可参考http://www.oracleodu.com/cn/).
#据说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.