ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 海量数据库被drop/truncate表的不完全恢复

海量数据库被drop/truncate表的不完全恢复

原创 Linux操作系统 作者:cc59 时间:2007-11-30 00:00:00 0 删除 编辑
d

海量数据库被drop/truncate表的不完全恢复


在实际情况中,数据库经常会由于工作人员的误操作造成一些表被删除或者截断,
如果数据库的数据量小是很方便做时间点恢复的,但如果数据量非常大。或者10T
的数据量,那是不可能再安排一台拥有这么大的存储空间的测试机让你做恢复的,


这是在去年,一电信的核心业务数据库一张非常重要的表被删除掉的情况,由于
当时并没有足够的空间在做全库的时间点恢复,因此采用了以下方法来,全过程
如下:

该库中一张表被用户使用drop table p操作,而p表位于proc表空间中。
这张表大约有2GB的数据量,并且用户尝试了重建该表,
由于种种原因无法补齐数据,只能从备份进行恢复,而该库有200GB的数据量,
而这张表的数据在4小时之后必须使用,

恢复过程:在异机配置dp 环境,并安装oracle

rman target /

Recovery Manager: Release 9.2.0.4.0 - Production

Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

connected to target database (not started)

这里也可以先使用dummy来nomount;

RMAN> startup nomount;

connected to target database (not started)
Oracle instance started

Total System Global Area 236000356 bytes

Fixed Size 451684 bytes
Variable Size 201326592 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes

把spfile 还原


RMAN> restore spfile to '/u03/arch/spfileorcl.ora'
2> from '/u03/arch/backuparch_6398607364';

Starting restore at 28-NOV-07

using channel ORA_DISK_1
channel ORA_DISK_1: autobackup found: /u03/arch/backuparch_6398607364
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 28-NOV-07

RMAN> shutdown immediate;

使用刚刚恢复的spfile启动到nomount,主要为了让rman识别controlfile信息。
RMAN> startup nomount;

connected to target database (not started)
Oracle instance started

Total System Global Area 236000356 bytes

Fixed Size 451684 bytes
Variable Size 201326592 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes


RMAN> restore controlfile from '/u03/arch/backuparch_6398607364';

Starting restore at 28-NOV-07

using channel ORA_DISK_1
channel ORA_DISK_1: restoring controlfile
channel ORA_DISK_1: restore complete
replicating controlfile
input filename=/u01/app/oracle/oradata/orcl/control01.ctl
output filename=/u01/app/oracle/oradata/orcl/control02.ctl
output filename=/u01/app/oracle/oradata/orcl/control03.ctl
Finished restore at 28-NOV-07

RMAN> alter database mount;

database mounted
RMAN> crosscheck backup;

using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=12 devtype=DISK
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u03/arch/arch_ORCL6398607033 recid=3 stamp=639860704
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u03/arch/backuparch_6398607364 recid=4 stamp=639860738
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u03/arch/arch_ORCL6398608945 recid=5 stamp=639860895
Crosschecked 3 objects


还原system和undo两个必须的表空间,当然,由于p表在proc表空间中,因此也需要还原
RMAN> restore tablespace system,UNDOTBS1,proc;

Starting restore at 29-NOV-07

using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/app/oracle/oradata/orcl/system01.dbf
restoring datafile 00002 to /u01/app/oracle/oradata/orcl/undotbs01.dbf
restoring datafile 00006 to /u01/app/oracle/oradata/orcl/proc.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/u03/arch/backuparch_6398607364 tag=TAG20071128T190535 params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 29-NOV-07

打开sqlplus,

列中datafile信息:
SQL> /

FILE# NAME
---------- --------------------------------------------------
1 /u01/app/oracle/oradata/orcl/system01.dbf
2 /u01/app/oracle/oradata/orcl/undotbs01.dbf
3 /u01/app/oracle/oradata/orcl/drsys01.dbf
4 /u01/app/oracle/oradata/orcl/users01.dbf
5 /u01/app/oracle/oradata/orcl/xdb01.dbf
6 /u01/app/oracle/oradata/orcl/proc.dbf
7 /u01/app/oracle/oradata/orcl/t_data.dbf
8 /u01/app/oracle/oradata/orcl/t_data2.dbf
.....
.....

SQL> alter database datafile 3,4,5,7,8..... offline drop;

Database altered.


RMAN> recover database until time "to_DATE('2007-11-28 19:10:00', 'yyyy-mm-dd hh24:mi:ss')"
2> skip tablespace DRSYS,USERS,XDB,T_DATA,TEMP;

Starting recover at 28-NOV-07
using channel ORA_DISK_1

starting media recovery
media recovery failed
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 11/28/2007 21:26:20
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover if needed
start until time 'NOV 28 2007 19:10:00'
ORA-00283: recovery session canceled due to errors
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/orcl/redo03.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

RMAN>


因为没有redo日志,因此在恢复过程中会报错误,不过没有关系。
接下来退出rman

进入sqlplus

alter database backup controlfile to trace;

生成重建控制文件脚本。

并使用以下方式重建:

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS FORCE LOGGING ARCHIVELOG
-- SET STANDBY TO MAXIMIZE PERFORMANCE
MAXLOGFILES 5
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 226
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/orcl/redo01.log' SIZE 10M,
GROUP 2 '/u01/app/oracle/oradata/orcl/redo02.log' SIZE 10M,
GROUP 3 '/u01/app/oracle/oradata/orcl/redo03.log' SIZE 10M
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/orcl/system01.dbf',
'/u01/app/oracle/oradata/orcl/undotbs01.dbf',
'/u01/app/oracle/oradata/orcl/drsys01.dbf',
'/u01/app/oracle/oradata/orcl/users01.dbf',
'/u01/app/oracle/oradata/orcl/xdb01.dbf',
'/u01/app/oracle/oradata/orcl/proc.dbf',
'/u01/app/oracle/oradata/orcl/t_data.dbf',
'/u01/app/oracle/oradata/orcl/t_data2.dbf'
....
....
CHARACTER SET ZHS16GBK
;

alter database mount;

alter database open resetlogs;

注意这里必须以resetlogs打开数据库。
SQL> select count(*) from p;

COUNT(*)
---------------
47104001


P表已经恢复,exp出来这张表,并导入到生产库。
这里只花了前后不到2个小时的时间就完成了恢复。

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

上一篇: 做一个好的dba
下一篇: Using dbms_monitor
请登录后发表评论 登录
全部评论

注册时间:2007-12-21

  • 博文量
    132
  • 访问量
    286469