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

　　保護數据庫最簡單的方法就是把所有的數据文件複製到另一個地方。一旦出現故障，可以把所有文件還原到原來的位置上進行恢复，然後啟動數据庫，這种操作叫做完全（或全部）一致數据庫備份与恢复。

　　當進行數据庫還原或恢复時，使用的是在一致(關閉/冷備份)或非一致(打開/熱備份)數据庫上生成的備份。一致數据庫是正常關閉的，并包含經過同步的文件。文件的是一致是看各個文件的SCN號是否一樣！

　　一致性備份稱為冷備份（關閉狀態），非一致性備份稱為熱備份（打開狀態）！

　　備份文件包括：數据文件，控制文件，重做日誌文件

　　注：拷貝數据庫中的所有文件來進行一次完全備份。在拷貝所有的數据文件、控制文件、重做日誌文件之前，先關閉數据庫。已關閉數据庫（正常關閉）將包含一致的文件。當數据庫打開時，事務被寫入內存，數据文件，控制文件和重做日誌得到更新。如果在數据庫打開狀態下對數据庫進行物理拷貝，得到的複製文件之間將會不一致，而且也不可能借助這些文件來還原數据庫。數据在打開狀態時是可以對其進行備份的，但需要在表空間級進行。

[@more@]

--**************************************************************
-- cold_backup [cold_backup.sql]
--
-- 2007-03-06 by HuiYi.Zhang
--
--   No part of this SQL script may be copied. Sold or distributed
--      without the express consent of HuiYi.Zhang
--**************************************************************
-- set sql*plus variable to output
set feedback off heading off trimspool on verify off
set pagesize 0
-- set sql*plus user variable
define l_path='G:BBACKUPCOLD'
define l_file='.cold_backup_command.sql'
prompt ... spooling to &l_file
--
spool &l_file
select 'host copy '||name||' &l_path' || ' /y' from v$datafile;
select 'host copy '||name||' &l_path' || ' /y' from v$controlfile;
select 'host copy '||member||' &l_path' || ' /y' from v$logfile;
spool off
--
-- shutdown & run bakcup_command and startup Database;
shutdown immediate;
@ &l_file;
startup;
exit


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

Table created.

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

1 row created.

SYS@HUIYI>commit;

Commit complete.

SYS@HUIYI>select * from t;

COL
------------------
08-Mar-07 16:03:51


SYS@HUIYI>@cold_backup;



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

1 row created.

SYS@HUIYI>/

1 row created.

SYS@HUIYI>commit;

Commit complete.

SYS@HUIYI>select * from t;

COL
------------------
08-Mar-07 16:03:51
08-Mar-07 16:07:38
08-Mar-07 16:07:39



SYS@HUIYI>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@HUIYI>host del %oracle_home%oradatahuiyitbs01.dbf

SYS@HUIYI>startup
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.
ORA-01157: cannot identify/lock data file 10 - see DBWR trace file
ORA-01110: data file 10: 'G:A9IORADATAHUIYITBS01.DBF'


SYS@HUIYI>col error format a30
SYS@HUIYI>select * from v$recover_file;

     FILE# ONLINE         ONLINE_STATUS  ERROR                             CHANGE# TIME
---------- -------------- -------------- ------------------------------ ---------- ------------------
        10 ONLINE         ONLINE         FILE NOT FOUND                          0

-- 還原備份的TBS01.DBF
SYS@HUIYI>host copy g:bbackupcoldtbs01.dbf %oracle_home%oradatahuiyi /y
        1 file(s) copied.

SYS@HUIYI>select * from v$recover_file;

     FILE# ONLINE         ONLINE_STATUS  ERROR                             CHANGE# TIME
---------- -------------- -------------- ------------------------------ ---------- ------------------
        10 ONLINE         ONLINE                                         486081001 08-Mar-07 16:04:11

SYS@HUIYI>select file#,checkpoint_change#,checkpoint_time,last_change#,last_time from v$datafile;

     FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME    LAST_CHANGE# LAST_TIME
---------- ------------------ ------------------ ------------ ------------------
         1          486081904 08-Mar-07 16:08:57    486081904 08-Mar-07 16:08:57
         2          486081904 08-Mar-07 16:08:57    486081904 08-Mar-07 16:08:57
         3          486081904 08-Mar-07 16:08:57    486081904 08-Mar-07 16:08:57
         4          486081904 08-Mar-07 16:08:57    486081904 08-Mar-07 16:08:57
         5          486081904 08-Mar-07 16:08:57    486081904 08-Mar-07 16:08:57
         6          486081904 08-Mar-07 16:08:57    486081904 08-Mar-07 16:08:57
         7          486081904 08-Mar-07 16:08:57    486081904 08-Mar-07 16:08:57
         8          486081904 08-Mar-07 16:08:57    486081904 08-Mar-07 16:08:57
         9          486081904 08-Mar-07 16:08:57    486081904 08-Mar-07 16:08:57
        10          486081904 08-Mar-07 16:08:57    486081904 08-Mar-07 16:08:57

10 rows selected.


SYS@HUIYI>select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARCHIV STATUS     FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- ------ ---------- ------------- ------------------
         1          1         29  104857600          1 NO     CURRENT        486062424 08-Mar-07 13:56:39
         2          1         27  104857600          1 YES    INACTIVE       486029901 08-Mar-07 11:31:44
         3          1         28  104857600          1 YES    INACTIVE       486062333 08-Mar-07 13:56:36




SYS@HUIYI>recover database;
Media recovery complete.
SYS@HUIYI>alter database open;

Database altered.

SYS@HUIYI>select * from t;

COL
------------------
08-Mar-07 16:03:51
08-Mar-07 16:07:38
08-Mar-07 16:07:39


Thu Mar 08 16:21:20 2007
ALTER DATABASE RECOVER  database  
Thu Mar 08 16:21:20 2007
Media Recovery Start
Starting datafile 10 recovery in thread 1 sequence 29
Datafile 10: 'G:A9IORADATAHUIYITBS01.DBF'
Media Recovery Log 
Recovery of Online Redo Log: Thread 1 Group 1 Seq 29 Reading mem 0
  Mem# 0 errs 0: G:A9IORADATAHUIYIREDO01.LOG
Media Recovery Complete
Completed: ALTER DATABASE RECOVER  database

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

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