oracle恢复表delete/truncate/drop的方法总结

最新推荐文章于 2023-01-08 11:46:32 发布

congse3359

最新推荐文章于 2023-01-08 11:46:32 发布

阅读量311

点赞数

文章标签：数据库

    在有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主要是通过表在被删除时,并没有立即真正清除掉数据的原理.可参考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.

</eof

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28539951/viewspace-1985713/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28539951/viewspace-1985713/