恢复dropped table

zr_baobao

摘要： recover dropped table without recyclebin

关键词：DBITR;RECOVER;RESTORE

恢复dropped table办法

     在这次场景中，应用方在三天前DROP了一个表，这个表已经因为空闲空间压力被purge，无法简单地使用flashback drop来恢复。这种情况下一般有三种选择：

Ø         通过三天前的全备份进行全库Restore不完全恢复；

Ø         通过三天前的全备份进行数据库关键部分(system,undotbs,表相关的表空间)的Restore不完全恢复；

Ø         通过三天前的全备份restore system表空间与被drop表所在表空间的数据文件，使用dul工具恢复。

具体内容：

1．   Restore 控制文件

 

Set DBID=3827063033

run{

allocate channel m1t1 type 'SBT_TAPE' parms 'ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo_INVDB1.opt)';

    RESTORE CONTROLFILE FROM TAG 'TAG20110427T052621' ;

    release channel m1t1;

}

 

2．   Restore 关键数据文件

 

run{

allocate channel c3 type sbt_tape parms 'ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo_INVDB1.opt)';

set until  time "to_date('2011-04-27 07:00:00','yyyy-mm-dd hh24:mi:ss')";

set newname for datafile '/dev/rinv1sg2_2g06' to '/oracle/data/sysaux_1.dbf';     

set newname for datafile '/dev/rinv1sg1_aux1' to '/oracle/data/sysaux_2.dbf';

set newname for datafile '/dev/rinv1sg1_sys1' to '/oracle/data/system.dbf';

set newname for datafile '/dev/rinv1sg1_2g01' to '/oracle/data/ts_def_data_1.dbf';

set newname for datafile '/dev/rinv1sg1_4g08' to '/oracle/data/ts_def_data_2.dbf';

set newname for datafile '/dev/rinv1sg4_2g02' to '/oracle/data/ts_def_data_3.dbf';

set newname for datafile '/dev/rinv1sg4_2g03' to '/oracle/data/ts_def_data_4.dbf';

set newname for datafile '/dev/rinv1sg3_2g03' to '/oracle/data/ts_def_data_5.dbf';

set newname for datafile '/dev/rinv1sg2_2g05' to '/oracle/data/ts_def_data_6.dbf';

set newname for datafile '/dev/rinv1sg2_2g04' to '/oracle/data/ts_def_data_7.dbf';

set newname for datafile '/dev/rinv1sg4_2g01' to '/oracle/data/ts_def_data_8.dbf';

set newname for datafile '/dev/rinv1sg4_4g09' to '/oracle/data/ts_def_data_9.dbf';

set newname for datafile '/dev/rinv1sg2_2g01' to '/oracle/data/ts_def_data_10.dbf';

set newname for datafile '/dev/rinv1sg3_2g01' to '/oracle/data/ts_def_data_11.dbf';

set newname for datafile '/dev/rinv1sg1_undo1' to '/oracle/data/undotbs1_1.dbf';

set newname for datafile '/dev/rinv1sg4_undo1' to '/oracle/data/undotbs1_2.dbf';

set newname for datafile '/dev/rinv1sg3_undo1' to '/oracle/data/undotbs1_3.dbf';

set newname for datafile '/dev/rinv1sg2_undo1' to '/oracle/data/undotbs1_4.dbf';

set newname for datafile '/dev/rinv1sg4_undo2' to '/oracle/data/undotbs2_1.dbf';

set newname for datafile '/dev/rinv1sg3_undo2' to '/oracle/data/undotbs2_2.dbf';

set newname for datafile '/dev/rinv1sg2_undo2' to '/oracle/data/undotbs2_3.dbf';

set newname for datafile '/dev/rinv1sg1_undo2' to '/oracle/data/undotbs2_4.dbf';

restore  datafile 154,3,1,67,158,147,146,145,144,143,70,186,68,69,2,7,6,5,10,8,4,9;

SWITCH DATAFILE ALL;

release channel  c3;

}

 

3．   查询并restore备份开始与结束范围的归档文件

 

run {

 allocate channel c4 type sbt_tape parms 'ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo_INVDB1.opt)';

 set archivelog destination to '/oracle/admin/INVDB1/arc';

 SQL 'ALTER SESSION SET NLS_DATE_FORMAT="YYYY-MM-DD:HH24:MI:SS"';

 restore archivelog scn between 12211375193199 and 12211387066570;

 release channel c4;

 }

 

4．   进行不完全恢复

 

在恢复之前，将数据库置为mount状态，并将没有restore的数据文件offline。

 

Recover database until cancel using backup controlfile;

 

在恢复的时候遭遇到两个错误。

 

SQL> ALTER DATABASE OPEN RESETLOGS;

ALTER DATABASE OPEN RESETLOGS

*

ERROR at line 1:

ORA-01152: file 1 was not restored from a sufficiently old backup

ORA-01110: data file 1: '/oracle/data/system.dbf'

 

无奈只能重建控制文件后继续恢复。

 

SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-01113: file 1 needs media recovery

ORA-01110: data file 1: '/oracle/data/system.dbf'

 

无奈只能选择auto形式进行恢复，如有需要，restore后续的归档文件持续recover，直到这个错误消失。

 

5．   以resetlogs打开数据库

SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-38856: cannot mark instance UNNAMED_INSTANCE_2 (redo thread 2) as enabled

 

这个错误是因为原数据库为RAC环境，我们将thread 2的redo log加入数据库后解决：

SQL> ALTER DATABASE ADD LOGFILE THREAD 2

  2    GROUP 7 (

    '/oracle/data/inv1sg1_redo2',

    '/oracle/data/inv1sg2_redo2'

  ) SIZE 510M REUSE,

  GROUP 8 (

    '/oracle/data/inv1sg3_redo2',

    '/oracle/data/inv1sg4_redo2'

  ) SIZE 510M REUSE,

  GROUP 9 (

    '/oracle/data/inv1sg1_redo4',

    '/oracle/data/inv1sg2_redo4'

  ) SIZE 510M REUSE,

  GROUP 10 (

    '/oracle/data/inv1sg3_redo4',

    '/oracle/data/inv1sg4_redo4'

  ) SIZE 510M REUSE,

  GROUP 11 (

    '/oracle/data/inv1sg1_redo6',

    '/oracle/data/inv1sg2_redo6'

  ) SIZE 510M REUSE,

  GROUP 12 (

    '/oracle/data/inv1sg3_redo6',

    '/oracle/data/inv1sg4_redo6'

  ) SIZE 510M REUSE;

 

成功打开数据库后，找回目标表：

SQL> select count(*) from SHUINV1C.HZ_LT_YG_201102;

 

  COUNT(*)

----------

      2642