　　上一篇中進行不完全恢复的練習，但這個并不實用，因為對於刪表沒有記錄時間，對於這樣的問題可以通過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.



-- 查看alert.log & 恢复

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博客

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

最新文章

ITPub博客

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

最新文章

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