Chinaunix首页 | 论坛 | 博客
  • 博客访问: 120704
  • 博文数量: 27
  • 博客积分: 1200
  • 博客等级: 中尉
  • 技术积分: 350
  • 用 户 组: 普通用户
  • 注册时间: 2010-12-22 14:56
文章分类

全部博文(27)

文章存档

2011年(19)

2010年(8)

我的朋友

分类: Oracle

2011-05-16 15:14:13

摘要: 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 2redo 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

阅读(1717) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~