Oracle误删除表空间后恢复


作者介绍: 王涛 Vogts 财神 现CNOUG斑竹,阿里巴巴DBA 电话:138-67423-7766 MSN: wt66550601@hotmail.com 前几天有网友问,如何恢复被删除的表空间,今天我做了一个范例,仅供大家参考。 创建了表空间,然后在上面创建了数据: @>create table wt_tb tablespace wt as select * from dba_objects; Table created. @> @>alter database backup controlfile to trace; Database altered. 其间使用了RMAN备份,然后手动备份了控制文件; A:RMAN备份数据库: RMAN> connect target connected to target database: WTDB (DBID=591126187) RMAN> backup database; Starting backup at 2008-09-02 09:11:56 using target database controlfile instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=8 devtype=DISK channel ORA_DISK_1: starting full datafile backupset channel ORA_DISK_1: specifying datafile(s) in backupset including current SPFILE in backupset including current controlfile in backupset input datafile fno=00001 name=/disk2/oradata/wtdb/system01.dbf input datafile fno=00002 name=/disk2/oradata/wtdb/undotbs01.dbf input datafile fno=00004 name=/disk2/oradata/wtdb/wtdb01.dbf input datafile fno=00003 name=/disk2/oradata/wtdb/donny.dbf channel ORA_DISK_1: starting piece 1 at 2008-09-02 09:11:57 channel ORA_DISK_1: finished piece 1 at 2008-09-02 09:12:43 piece handle=/opt/oracle/products/9.2.0/dbs/01jpinms_1_1 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:47 Finished backup at 2008-09-02 09:12:43 RMAN> exit B:手工备份了控制文件: @>alter database backup controlfile to trace; Database altered. 删除表空间和数据: @>drop tablespace wt including contents; Tablespace dropped. 重启了数据库,发现表空间不在了): shutdown immediate; startup; 要恢复被删除的表空间,一定要满足如下条件: 1:控制文件里有这些信息,我们上面已经做了; 2:数据字典里也要有相关信息,那么我们必须恢复system表空间,到未删除之前的状态; 先做第二步,然后做第一步; OS里RM掉system的DBF文件;然后用rman的备份集,恢复一个老的system表空间,它包含了老的信息: oracle@bopscha:/disk2/oradata/wtdb>rm system01.dbf 启动数据库到mount状态: @>startup mount; ORACLE instance started. Total System Global Area 169415700 bytes Fixed Size 451604 bytes Variable Size 50331648 bytes Database Buffers 117440512 bytes Redo Buffers 1191936 bytes Database mounted. 用RMAN恢复system表空间; RMAN> connect target connected to target database: WTDB (DBID=591126187) RMAN> restore datafile 1; Starting restore at 2008-09-02 09:22:33 using target database controlfile instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=14 devtype=DISK channel ORA_DISK_1: starting datafile backupset restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set restoring datafile 00001 to /disk2/oradata/wtdb/system01.dbf channel ORA_DISK_1: restored backup piece 1 piece handle=/opt/oracle/products/9.2.0/dbs/01jpinms_1_1 tag=TAG20080902T091156 params=NULL channel ORA_DISK_1: restore complete Finished restore at 2008-09-02 09:23:21 RMAN> 然后做步骤二: 重启数据库,然后到nomount状态: @>shutdown immediate; startup nomount; ORA-01109: database not open Database dismounted. ORACLE instance shut down. @>ORACLE instance started. Total System Global Area 169415700 bytes Fixed Size 451604 bytes Variable Size 50331648 bytes Database Buffers 117440512 bytes Redo Buffers 1191936 bytes --重建控制文件,因为我的控制文件一直是老的,没有 @>CREATE CONTROLFILE REUSE DATABASE "WTDB" RESETLOGS ARCHIVELOG 2 -- SET STANDBY TO MAXIMIZE PERFORMANCE 3 MAXLOGFILES 9 4 MAXLOGMEMBERS 3 5 MAXDATAFILES 100 6 MAXINSTANCES 1 7 MAXLOGHISTORY 1361 8 LOGFILE 9 GROUP 1 '/disk2/oradata/wtdb/redo0101.log' SIZE 10M, 10 GROUP 2 '/disk2/oradata/wtdb/redo0102.log' SIZE 10M, 11 GROUP 3 '/disk2/oradata/wtdb/redo0103.log' SIZE 10M 12 -- STANDBY LOGFILE 13 DATAFILE 14 '/disk2/oradata/wtdb/system01.dbf', 15 '/disk2/oradata/wtdb/undotbs01.dbf', 16 '/disk2/oradata/wtdb/donny.dbf', 17 '/disk2/oradata/wtdb/wtdb01.dbf' 18 CHARACTER SET US7ASCII 19 ; Control file created. --注意他们的checkpoint_time都是不一致的,说明这些数据文件不在一个时间点上。 @>select name,checkpoint_time from v$datafile; NAME ------------------------------------------------------------------------------------------------------- ----------------------------------------------- CHECKPOINT_TIME ------------------- /disk2/oradata/wtdb/system01.dbf 2008-09-02 09:11:57 /disk2/oradata/wtdb/undotbs01.dbf 2008-09-02 09:13:10 /disk2/oradata/wtdb/donny.dbf 2008-09-02 09:13:10 /disk2/oradata/wtdb/wtdb01.dbf 2008-09-02 09:12:57 @>alter database open; alter database open * ERROR at line 1: ORA-01589: must use RESETLOGS or NORESETLOGS option for database open --数据库OPEN了,但是必须resetlogs,这样才能使用被删除的表空间。 @>alter database open resetlogs; Database altered. @>select name,checkpoint_time from v$datafile; NAME ------------------------------------------------------------------------------------------------------- ----------------------------------------------- CHECKPOINT_TIME ------------------- /disk2/oradata/wtdb/system01.dbf 2008-09-02 09:25:16 /disk2/oradata/wtdb/undotbs01.dbf 2008-09-02 09:25:16 /disk2/oradata/wtdb/donny.dbf 2008-09-02 09:25:16 /disk2/oradata/wtdb/wtdb01.dbf 2008-09-02 09:25:16 @>select count(*) from wt_tb; COUNT(*) ---------- 6164 其他疑问: 1:数据库在归档模式下运行,没有任何备份能恢复吗? 答:你有该表空间创建以来的所有归档日志,重新新建一个。否则没有办法。 2:在非归档模式下运行,能恢复吗? 答:采用上面的方法试试。
还剩3页未读

继续阅读

下载pdf到电脑,查找使用更方便

pdf的实际排版效果,会与网站的显示效果略有不同!!

需要 5 金币 [ 分享pdf获得金币 ] 0 人已下载

下载pdf

pdf贡献者

gj1985

贡献于2013-06-08

下载需要 5 金币 [金币充值 ]
亲,您也可以通过 分享原创pdf 来获得金币奖励!
下载pdf