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

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

-_\5e'R+D3a
a$G"j0N69265在实际情况中，数据库经常会由于工作人员的误操作造成一些表被删除或者截断，
%||,L0y0Y#L*^69265如果数据库的数据量小是很方便做时间点恢复的，但如果数据量非常大。或者10T
LQJ.wO2DY69265的数据量，那是不可能再安排一台拥有这么大的存储空间的测试机让你做恢复的，

ITPUB个人空间/_7z g:N3Ae
这是在去年，一电信的核心业务数据库一张非常重要的表被删除掉的情况，由于
*^O/o`,bh69265当时并没有足够的空间在做全库的时间点恢复，因此采用了以下方法来,全过程ITPUB个人空间)p g_"l5}nE,eOr
如下：

该库中一张表被用户使用drop table p操作,而p表位于proc表空间中。ITPUB个人空间:D-c;o __u
这张表大约有2GB的数据量，并且用户尝试了重建该表，
,tG+Nu#x/GU4s7a69265由于种种原因无法补齐数据，只能从备份进行恢复，而该库有200GB的数据量，ITPUB个人空间,N+C Z ]s]eL
而这张表的数据在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)ITPUB个人空间I{e_9g$F#F|
Oracle instance started

Total System Global Area 236000356 bytes

Fixed Size 451684 bytesITPUB个人空间$q,JW;U9uP%p0HR
Variable Size 201326592 bytesITPUB个人空间vH(y n[
Database Buffers 33554432 bytes
/b*F$_Q5DN4ou69265Redo Buffers 667648 bytes

把spfile 还原

~,x:C:I1s8s7[4z69265RMAN> restore spfile to '/u03/arch/spfileorcl.ora'
z y.q2lm5izi*}692652> from '/u03/arch/backuparch_6398607364';

Starting restore at 28-NOV-07

using channel ORA_DISK_1
rs
N+hUA'DY69265channel ORA_DISK_1: autobackup found: /u03/arch/backuparch_6398607364
FO0N"tct*W69265channel ORA_DISK_1: SPFILE restore from autobackup completeITPUB个人空间$v7Rfx&uv/H
Finished restore at 28-NOV-07

RMAN> shutdown immediate;

使用刚刚恢复的spfile启动到nomount,主要为了让rman识别controlfile信息。
U9F$?n,o&X~)^5L69265RMAN> startup nomount;

connected to target database (not started)ITPUB个人空间 Czvhkm.PX N;V
Oracle instance started

Total System Global Area 236000356 bytes

Fixed Size 451684 bytesITPUB个人空间
qDryE"a
Variable Size 201326592 bytes
;q-Um
j1mx7U'[v69265Database Buffers 33554432 bytes
}*_sWh7L_7}69265Redo Buffers 667648 bytes

ITPUB个人空间cCo-\C}'e,h ~ {
RMAN> restore controlfile from '/u03/arch/backuparch_6398607364';

Starting restore at 28-NOV-07

using channel ORA_DISK_1
5YE+GW~edW5BFy69265channel ORA_DISK_1: restoring controlfile
OD!F+kq4oW4o0^69265channel ORA_DISK_1: restore completeITPUB个人空间 d
V\@%zP:n-P{dq
replicating controlfileITPUB个人空间;{W{F\W1uk
input filename=/u01/app/oracle/oradata/orcl/control01.ctl
1h%i]jvu69265output filename=/u01/app/oracle/oradata/orcl/control02.ctlITPUB个人空间zAj H;q
output filename=/u01/app/oracle/oradata/orcl/control03.ctlITPUB个人空间!b;K9u2g$c+U"s5zb
Finished restore at 28-NOV-07

RMAN> alter database mount;

database mountedITPUB个人空间uu1z[$y"JO&EgI:|
RMAN> crosscheck backup;

using target database controlfile instead of recovery catalogITPUB个人空间J"k$ma E
allocated channel: ORA_DISK_1ITPUB个人空间?-~m ]K h@0d w/o
channel ORA_DISK_1: sid=12 devtype=DISKITPUB个人空间bt*b4m+hSQ
crosschecked backup piece: found to be 'AVAILABLE'ITPUB个人空间Y {JK*V8O&V1y/AG
backup piece handle=/u03/arch/arch_ORCL6398607033 recid=3 stamp=639860704ITPUB个人空间/yU5oK)G!V\4vl
crosschecked backup piece: found to be 'AVAILABLE'ITPUB个人空间%^)}(a0|%N*Ov,^b
backup piece handle=/u03/arch/backuparch_6398607364 recid=4 stamp=639860738
,W-xV-a`;OB}69265crosschecked backup piece: found to be 'AVAILABLE'ITPUB个人空间9mM%}coCuZ*c&I
backup piece handle=/u03/arch/arch_ORCL6398608945 recid=5 stamp=639860895
#z9l\9k9Q|69265Crosschecked 3 objects

ITPUB个人空间BcZ {^!Nc7h
还原system和undo两个必须的表空间，当然，由于p表在proc表空间中，因此也需要还原
X L{4AE(z4s3I;{%J69265RMAN> restore tablespace system,UNDOTBS1,proc;

Starting restore at 29-NOV-07

using channel ORA_DISK_1
Zah r!M%f69265channel ORA_DISK_1: starting datafile backupset restoreITPUB个人空间b'[:k Z8NW
channel ORA_DISK_1: specifying datafile(s) to restore from backup setITPUB个人空间z\:} ?b%U
restoring datafile 00001 to /u01/app/oracle/oradata/orcl/system01.dbfITPUB个人空间 `0Gj5Qw
restoring datafile 00002 to /u01/app/oracle/oradata/orcl/undotbs01.dbfITPUB个人空间'cl/r6{ S
restoring datafile 00006 to /u01/app/oracle/oradata/orcl/proc.dbfITPUB个人空间}1qn.}7i@)t
channel ORA_DISK_1: restored backup piece 1
VPM1F-Y69265piece handle=/u03/arch/backuparch_6398607364 tag=TAG20071128T190535 params=NULL
^ C+G3w1c,gG4W69265channel ORA_DISK_1: restore complete
+P,[f.?hF5aZW69265Finished restore at 29-NOV-07

打开sqlplus，

列中datafile信息：
dJo@b`h"G69265SQL> /

FILE# NAMEITPUB个人空间H:IL5e'ng
---------- --------------------------------------------------ITPUB个人空间2a_%Z]1zy1s
1 /u01/app/oracle/oradata/orcl/system01.dbfITPUB个人空间D@8QC7R*~
?
2 /u01/app/oracle/oradata/orcl/undotbs01.dbf
&{O-^&\%y%h k0K]69265 3 /u01/app/oracle/oradata/orcl/drsys01.dbfITPUB个人空间JJ?$\0y C,I
4 /u01/app/oracle/oradata/orcl/users01.dbfITPUB个人空间CJ!y"GN5FD
5 /u01/app/oracle/oradata/orcl/xdb01.dbf

w)l'uP1M Q69265 6 /u01/app/oracle/oradata/orcl/proc.dbf
$L$L%opJP69265 7 /u01/app/oracle/oradata/orcl/t_data.dbf
P0C#c9I/iD"z69265 8 /u01/app/oracle/oradata/orcl/t_data2.dbfITPUB个人空间7q$whvhej
.....
^5|v~@6}y69265 .....

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

Database altered.

ITPUB个人空间coS:t&jQzNuh
RMAN> recover database until time "to_DATE('2007-11-28 19:10:00', 'yyyy-mm-dd hh24:mi:ss')"
#b i_"sW692652> skip tablespace DRSYS,USERS,XDB,T_DATA,TEMP;

Starting recover at 28-NOV-07ITPUB个人空间t#c4L@9d
using channel ORA_DISK_1

starting media recoveryITPUB个人空间N@ x(g]T,wk5y
media recovery failed
#\&xxVArsP69265RMAN-00571: ===========================================================
y
KT~[7{"|69265RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
Z1Nw;WM1Uc69265RMAN-00571: ===========================================================
0?z2ID^z#fr69265RMAN-03002: failure of recover command at 11/28/2007 21:26:20
wK^Nc69265ORA-00283: recovery session canceled due to errors
;IuNH"^1e F69265RMAN-11003: failure during parse/execution of SQL statement: alter database recover if neededITPUB个人空间i3ipTS}
start until time 'NOV 28 2007 19:10:00'ITPUB个人空间;{3A:L9pek
ORA-00283: recovery session canceled due to errorsITPUB个人空间&QtC6Q9UDX
ORA-00313: open failed for members of log group 3 of thread 1ITPUB个人空间 JG0I9MF:gYNH%a
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/orcl/redo03.log'ITPUB个人空间~\%[h]8LX
ORA-27037: unable to obtain file statusITPUB个人空间 b.]q0o ge$c(C
Linux Error: 2: No such file or directory
|#s4G(TX$d$Wr69265Additional information: 3

RMAN>

ITPUB个人空间E.y)|?8iu
因为没有redo日志，因此在恢复过程中会报错误，不过没有关系。
"}u K+Ne69265接下来退出rman

进入sqlplus

alter database backup controlfile to trace;

生成重建控制文件脚本。

并使用以下方式重建：

STARTUP NOMOUNTITPUB个人空间YI b g Px1GDN
CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS FORCE LOGGING ARCHIVELOG
#d;t"nD'u6O&n*{#s69265-- SET STANDBY TO MAXIMIZE PERFORMANCEITPUB个人空间NdZ V!X3q{ S&x
MAXLOGFILES 5
(?_dz3Lun69265 MAXLOGMEMBERS 3
r/Nar(f4l69265 MAXDATAFILES 100ITPUB个人空间"?4]/j5f\T{#cz
MAXINSTANCES 1ITPUB个人空间!x)kC
rrQK*Q
MAXLOGHISTORY 226
R{NH%X69265LOGFILE
K;Qg!c?C7ag69265 GROUP 1 '/u01/app/oracle/oradata/orcl/redo01.log' SIZE 10M,ITPUB个人空间?f
S;w$|rvh%k
GROUP 2 '/u01/app/oracle/oradata/orcl/redo02.log' SIZE 10M,ITPUB个人空间4okB NL~!U
GROUP 3 '/u01/app/oracle/oradata/orcl/redo03.log' SIZE 10MITPUB个人空间;b/?| V]5|
-- STANDBY LOGFILEITPUB个人空间3j}
t%d"_3v N5?
DATAFILE
Zg P(e
E-O1c69265 '/u01/app/oracle/oradata/orcl/system01.dbf',
!}i4MNoDMQP0F#H69265 '/u01/app/oracle/oradata/orcl/undotbs01.dbf',
7Q%W2j[(C"g#c&A6X69265 '/u01/app/oracle/oradata/orcl/drsys01.dbf',ITPUB个人空间(a
@a+N3k'W7q
'/u01/app/oracle/oradata/orcl/users01.dbf',ITPUB个人空间4D9EU(]N7D-?"I1s1B,N!lx
'/u01/app/oracle/oradata/orcl/xdb01.dbf',ITPUB个人空间Q&Jaf,kHv
'/u01/app/oracle/oradata/orcl/proc.dbf',ITPUB个人空间n]B1TH2j
'/u01/app/oracle/oradata/orcl/t_data.dbf',ITPUB个人空间8Q6L J;]!^O~7q*Ll"bHb
'/u01/app/oracle/oradata/orcl/t_data2.dbf'
iD3vX-z h:^-iV` f:a69265 ....ITPUB个人空间+? N(p4~T,Dw%_&TI
....ITPUB个人空间,g
c5@tI["C
CHARACTER SET ZHS16GBK
9{ G;HB|x*f'_69265;

alter database mount;

alter database open resetlogs;

注意这里必须以resetlogs打开数据库。
&t ]+@_o%`
?+H7VW69265SQL> select count(*) from p;

COUNT(*)
kd6ed/eCJr,db69265---------------ITPUB个人空间M)wd,B `^|L
47104001

"Jk:bD%lV3D'cV69265P表已经恢复，exp出来这张表，并导入到生产库。ITPUB个人空间R7L,dx U8A(X7z
这里只花了前后不到2个小时的时间就完成了恢复。