第四章關閉數据庫的備份与恢复(不完全恢复二)-CSDN博客

　　上一篇中進行不完全恢复的練習，但這個并不實用，因為對於刪表沒有記錄時間，對於這樣的問題可以通過LogMiner來挖掘日誌文件，但更多時候是根据相關人員确定大概的時候，本篇練習刪除表空間的不完全恢复，恢复到刪除表空間之前，因為對數据庫的操作如刪除表空間，會記錄在alert.log中的，因為刪除表空間時控制文件中已經刪除的數据文件的信息，所以還原時要還要所有的數据文件以及控制文件，還原過來的控制文件時間小於要恢复的時候，為了要使以恢复的時間為准應該以recover database until time using backup controlfile的命令來恢复！

[@more@]

SYS@HUIYI>create table t
  2  (
  3  	col	date
  4  )
  5  tablespace tbs01;

Table created.

SYS@HUIYI>insert into t values(sysdate);

1 row created.

SYS@HUIYI>commit;

Commit complete.

SYS@HUIYI>select * from t;

COL
------------------
09-Mar-07 10:42:05


SYS@HUIYI>@cold_backup;



SYS@HUIYI>insert into t values(sysdate);

1 row created.

SYS@HUIYI>commit;

Commit complete.

SYS@HUIYI>select * from t;

COL
------------------
09-Mar-07 10:42:05
09-Mar-07 10:45:05



SYS@HUIYI>drop tablespace tbs01 including contents and datafiles;

Tablespace dropped.

SYS@HUIYI>select * from t;
select * from t
              *
ERROR at line 1:
ORA-00942: table or view does not exist


SYS@HUIYI>alter system switch logfile;

System altered.

SYS@HUIYI>/

System altered.

SYS@HUIYI>/

System altered.

SYS@HUIYI>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@HUIYI>host copy g:bbackupcold*.dbf %oracle_home%oradatahuiyi /y

SYS@HUIYI>host copy g:bbackupcold*.ctl %oracle_home%oradatahuiyi /y

SYS@HUIYI>startup mount;
ORACLE instance started.

Total System Global Area  143727516 bytes
Fixed Size                   453532 bytes
Variable Size             109051904 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
Database mounted.



Fri Mar 09 10:47:27 2007
drop tablespace tbs01 including contents and datafiles
Fri Mar 09 10:47:29 2007
Deleted file G:A9IORADATAHUIYITBS01.DBF
Completed: drop tablespace tbs01 including contents and dataf

SYS@HUIYI>recover database until time '2007-03-09 10:47:27' using backup controlfile;
ORA-00279: change 486128661 generated at 03/09/2007 10:42:52 needed for thread 1
ORA-00289: suggestion : D:ARCHIVELOGHUIYIARC00002.001
ORA-00280: change 486128661 for thread 1 is in sequence #2


Specify log: {=suggested | filename | AUTO | CANCEL}
auto
Log applied.
Media recovery complete.
SYS@HUIYI>alter database open resetlogs;

Database altered.

SYS@HUIYI>select * from t;

COL
------------------
09-Mar-07 10:42:05
09-Mar-07 10:45:05

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

转载于:http://blog.itpub.net/8799875/viewspace-903565/