|
思路：
当前控制文件里面已经不存在被删除的表空间的任何信息，如果直接进行不完全恢复，
由于ＣＦ中没有记录，不管你有没有备份这个表空间，ＯＲＡＣＬＥ的恢复进程都不会叼你这个ＴＢＳ
我开始认同tlczwyj的说法，利用日志信息前滚到创建表空间的一刻，这样会在ＣＦ中产生对应的文件信息（可能名称是乱码），
然后create as一次，产生文件，然后应用后续的日志，直到删除ＴＢＳ的前一刻，但测试后发现在cf中始终产生不了对应的文件信息
SQL> create tablespace mytestspace datafile 'D:\ORACLE_BASE\datafiles\mytestspace.dbf' size 20M;
Tablespace created.
SQL> CREATE TABLE MYTEST (ROW_ID INT) TABLESPACE mytestspace;
Table created.
SQL> INSERT INTO MYTEST VALUES(1);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
2006-01-20 14:15:01 --建立时间
2006-01-20 14:16:14 --恢复时间点
SQL> SHUTDOWN;
Database closed.
Database dismounted.
ORACLE instance shut down.
--备份所有数据文件
SQL> STARTUP;
ORACLE instance started.
Total System Global Area 529604068 bytes
Fixed Size 454116 bytes
Variable Size 520093696 bytes
Database Buffers 8388608 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
--创建新表空间
SQL> create tablespace mytestspace datafile 'D:\ORACLE_BASE\datafiles\mytestspac
e.dbf' size 20M;
Tablespace created.
SQL> ALTER SYSTEM CHECKPOINT;
System altered.
--记录创建TBS的完成时间
SQL> SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
TO_CHAR(SYSDATE,'YYYY-MM-DDHH
---------------------------------------------------------------------------
2006-01-20 14:15:01
--产生数据
SQL> CREATE TABLE MYTEST (ROW_ID INT) TABLESPACE mytestspace;
Table created.
SQL> INSERT INTO MYTEST VALUES(1);
1 row created.
SQL> commit;
Commit complete.
SQL> ALTER SYSTEM CHECKPOINT;
System altered.
--最终要恢复的时间点
SQL> SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
TO_CHAR(SYSDATE,'YYYY-MM-DDHH
---------------------------------------------------------------------------
2006-01-20 14:16:14
--删除表空间
SQL> DROP TABLESPACE MYTESTSPACE INCLUDING CONTENTS AND DATAFILES;
Tablespace dropped.
SQL> shutdown abort;
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
--将备份文件COPY回来
C:\Documents and Settings\luochun.BSCY>sqlplus /nolog
SQL*Plus: Release 9.2.0.1.0 - Production on Fri Jan 20 14:17:44 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> connect / as sysdba
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 529604068 bytes
Fixed Size 454116 bytes
Variable Size 520093696 bytes
Database Buffers 8388608 bytes
Redo Buffers 667648 bytes
Database mounted.
SQL> recover database until time '2006-01-20 14:15:01';
Media recovery complete.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
D:\ORACLE_BASE\DATAFILES\SYSTEM01.DBF
D:\ORACLE_BASE\DATAFILES\UNDOTBS.DBF
D:\ORACLE_BASE\DATAFILES\MYSPACE.DBF
D:\ORACLE_BASE\DATAFILES\MYAUTO.DBF
SQL> recover database until time '2006-01-20 14:15:01';
Media recovery complete.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
D:\ORACLE_BASE\DATAFILES\SYSTEM01.DBF
D:\ORACLE_BASE\DATAFILES\UNDOTBS.DBF
D:\ORACLE_BASE\DATAFILES\MYSPACE.DBF
D:\ORACLE_BASE\DATAFILES\MYAUTO.DBF
SQL>
这样后续部分就没法进行了，大家有什么意见，欢迎拍砖！！！！！
|
