如果自己搞不定可以找诗檀软件专业ORACLE数据库修复团队成员帮您恢复!
诗檀软件专业数据库修复团队
服务热线 ： 13764045638 QQ号:47079569 邮箱：service@parnassusdata.com
适用于：
Oracle Database – Enterprise Edition – 版本 10.2.0.4 到 11.1.0.7 [Release 10.2 到 11.1]
本文信息适用于任何平台。
目的
本文介绍了如何使用闪回数据库从一个DROP TABLESPACE语句中恢复。
范围
要使用闪回数据库从DROP TABLESPACE中恢复，必须满足以下条件：
- 在发出DROP TABLESPACE之前，表空间的备份必须存在。
- 从创建备份到发出DROP TABLESPACE语句期间，所有的归档日志文件必须存在。
详细信息
1. 在警报日志中，Oracle记录DROP TABLESPACE语句的发出时间和它的完成时间。找出表明发出DROP TABLESPACE语句的信息。例如：
Tue Feb 17 10:02:02 2009
drop tablespace ts1 including contents and datafiles
2. 确保数据库可以闪回到DROP TABLESPACE命令之前。如果以下查询返回的时间在发出DROP TABLESPACE语句之后，则无法使用闪回数据库恢复表空间。
SQL> select to_char(oldest_flashback_time,’Dy Mon DD HH24:MI:SS YYYY’)
from v$flashback_database_log;
TO_CHAR(OLDEST_FLASHBACK
————————
Wed Feb 10 19:34:12 2009
3. 闪回数据库到DROP TABLESPACE命令之前。使用从警报日志获取的日期戳。
RMAN> run {
# Flashback database requires a mounted database
shutdown immediate;
startup mount;
# Flashback the database to just before the DROP occurred
flashback database to before time
“to_date(‘Tue Feb 17 10:02:02 2009′,’Dy Mon DD HH24:MI:SS YYYY’)”;
}
注意在闪回数据库操作过程中，作为被drop的表空间一部分的数据文件被添加回控制文件，成为UNNAMED数据文件。正确恢复被drop的表空间需要未命名UNNAMED数据文件名和数据文件最初被创建的名称。
Recovery deleting file #6:’/u01/app/oracle/product/11.1.0/11.1.0.7/db/dbs/UNNAMED00006‘ from controlfile.
Recovery deleting file #7:’/u01/app/oracle/product/11.1.0/11.1.0.7/db/dbs/UNNAMED00007‘ from controlfile.
Recovery dropped tablespace ‘TS1‘
Flashback recovery: Added file #6 to control file as OFFLINE and ‘UNNAMED00006’
because it was dropped during the flashback interval
or it was added during flashback media recovery.
File was originally created as:
‘+ENG/demo/datafile/ts1.14360.679053609‘
File will have to be restored from a backup or
recreated using ALTER DATABASE CREATE DATAFILE command,
and the file has to be onlined and recovered.
Flashback recovery: Added file #7 to control file as OFFLINE and ‘UNNAMED00007’
because it was dropped during the flashback interval
or it was added during flashback media recovery.
File was originally created as:
‘+ENG/demo/datafile/ts1.17165.679053609‘
File will have to be restored from a backup or
recreated using ALTER DATABASE CREATE DATAFILE command,
and the file has to be onlined and recovered.
注意：可以在只读模式打开数据库来查询被drop表空间以外存在的数据，以确定闪回操作是否应该继续（被drop表空间中的数据不可用，直到它在后续步骤中被恢复）。如果你想在只读下打开数据库后继续闪回数据库操作，在下一步之前SHUTDOWN 并 STARTUP MOUNT数据库。
4. 确保在表空间被drop之前有其备份。使用从警报日志中获取的日期戳。Crosscheck备份来验证状态。
RMAN> list backup of tablespace ts1 completed before
“to_date(‘Tue Feb 17 10:02:02 2009′,’Dy Mon DD HH24:MI:SS YYYY’)”;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— ——————
5 Full 184.00K DISK 00:00:01 17-FEB-09 10:00:42
BP Key: 5 Status: AVAILABLE Compressed: NO Tag: TAG20090217T100041
Piece Name: +ENG/demo/backupset/2009_02_17/nnndf0_tag20090217t100041_0.9705.679053641
List of Datafiles in backup set 5
File LV Type Ckp SCN Ckp Time Name
—- — —- ———- —————— —-
6 Full 1039902 17-FEB-09 10:00:41 /u01/app/oracle/product/11.1.0/11.1.0.7/db/dbs/UNNAMED00006
7 Full 1039902 17-FEB-09 10:00:41 /u01/app/oracle/product/11.1.0/11.1.0.7/db/dbs/UNNAMED00007
Crosscheck 备份来验证状态。
RMAN> crosscheck backup of tablespace ts1 completed before
“to_date(‘Tue Feb 17 10:02:02 2009′,’Dy Mon DD HH24:MI:SS YYYY’)”;
using channel ORA_DISK_1
crosschecked backup piece: found to be ‘AVAILABLE’
backup piece handle=+ENG/demo/backupset/2009_02_17/nnndf0_tag20090217t100041_0.9705.679053641 RECID=5 STAMP=679053641
Crosschecked 1 objects
如果表空间没有有效备份，则无法恢复表空间。Flashback database 命令应通过运行RECOVER DATABASE来abandon。参见 Oracle Database Backup and Recovery User’s Guide 获取详情。
5. 如果有被drop表空间的有效备份，则继续恢复被drop表空间并打开数据库。在此步骤中，必须使用SET NEWNAME将所有未命名的数据文件重命名回它们的正确名称。该信息从上面识别的警报日志输出中获取。
RMAN> run {
# Rename the UNNAMED datafiles back to their original names.
# There will be one SET NEWNAME command for each UNNAMED datafile.
set newname for
datafile ‘/u01/app/oracle/product/11.1.0/11.1.0.7/db/dbs/UNNAMED00007’
to ‘+DATA/demo/datafile/ts1.17165.679053609’;
set newname for
datafile ‘/u01/app/oracle/product/11.1.0/11.1.0.7/db/dbs/UNNAMED00006’
to ‘+DATA/demo/datafile/ts1.14360.679053609’;
# Restore the files from the backup
# and switch the controlfile to point to them
restore tablespace ts1;
switch datafile all;
# Open resetlogs – required for flashback database
alter database open resetlogs;
# Recover and online the tablespace that was dropped
recover tablespace ts1;
sql “alter tablespace ts1 online”;
}
Comment