--=======================

-- Oracle 熱備份

--=======================

?

??? Oracle 熱備份是指數(shù)據(jù)庫處于 open 狀態(tài)下，對數(shù)據(jù)庫的數(shù)據(jù)文件、控制文件、參數(shù)文件、密碼文件等進(jìn)行一系列備份操作。

熱備是基于用戶管理備份恢復(fù)的一種方式，也是除了 RMAN 備份之外較為常用的一種備份方式。

?

一、熱備的過程

??? 凍結(jié)塊頭 --> 控制 SCN 在備份時不發(fā)生變化

??? 進(jìn)行物理拷貝

??? 解凍塊頭 --> 讓 SCN 可以變化 ( 當(dāng)對 SCN 解凍后，系統(tǒng)會自動更新 SCN 至最新的狀態(tài) )

?

二、基于數(shù)據(jù)庫的熱備

??? alter database begin backup ;

??? 拷貝所有的 datafile 到備份目錄

??? alter database end backup ;

?

三、基于表空間的熱備

??? alter tablespace tablespace_name begin backup ;

??? 拷貝 tablespace_name 表空間的數(shù)據(jù)文件到備份目錄

??? alter tablespace tablespace_name end backup ;

?

??? alter tablespace tablespace_name begin backup 時完成的任務(wù)

??????? 檢查點(diǎn)事件發(fā)生，檢查點(diǎn)通知 DBWn 將該表空間上所有的臟數(shù)據(jù)被寫入到磁盤

??????? 在數(shù)據(jù)文件頭部凍結(jié)當(dāng)前檢查點(diǎn)事件發(fā)生時的 SCN 號

??????? 所有發(fā)生變化數(shù)據(jù)塊的完整鏡像 ( 修改前后 ) 被寫入到 redo log 中

??????? 允許該表空間內(nèi)數(shù)據(jù)的正常讀寫

?

??? 建議使用基于表空間的熱備，這樣將盡可能的減少對系統(tǒng)性能的影響 ???

?

四、控制文件的熱備

??? alter database backup controlfile to '<dir>' ; ????????? -- 控制文件的完整備份

??? alter database backup controlfile to trace as '<dir>' ? -- 用于創(chuàng)建控制文件的語句，丟失了部分信息

??? 控制文件發(fā)生變化情況

??????? alter database [add |drop] logfile

??????? alter database [add |drop] logfile member

??????? alter database [add |drop] logfile group

??????? alter database [archivelog |noarchivelog]

??????? alter database rename file

??????? create tablespace

??????? alter tablespace [add | rename] datafile

??????? alter tablespace [read write | read only]

??????? drop tablespace

??? 更多關(guān)于控制文件請參考： Oracle 控制文件

???

五、參數(shù)文件的熱備

??? create pfile from spfile ;

??? create pfile = '<dir>' from spfile ;

??? 更多關(guān)于參數(shù)文件請參考： Oracle 參數(shù)文件

???

六、臨時表空間的數(shù)據(jù)文件、日志文件不需要備份

??? -- 查看臨時表空間

??????? SQL > select tablespace_name from dba_temp_files ;

?

??????? TABLESPACE_NAME

??????? ------------------------------

??????? TEMP

???

??? -- 查看臨時表空間的數(shù)據(jù)文件

??????? SQL > select name from v$tempfile ;

?

??????? NAME

??????? ------------------------------------------------------

??????? / u01 / app / oracle / oradata / orcl / temp01 . dbf

???

??? -- 將臨時表空間置為備份模式，收到了錯誤的提示

??????? SQL > alter tablespace temp begin backup ;

??????? alter tablespace temp begin backup

??????? *

??????? ERROR at line 1 :

??????? ORA - 03217 : invalid option for alter of TEMPORARY TABLESPACE

?

??? -- 將臨時表空間置為備份模式，收到了錯誤的提示

??????? SQL > alter temporary tablespace temp begin backup ;

??????? alter temporary tablespace temp begin backup

??????????? ? *

??????? ERROR at line 1 :

??????? ORA - 00940 : invalid ALTER command

?

七、熱備腳本

??? 1. 基于數(shù)據(jù)庫熱備的腳本

??????? SQL > ho cat / tmp / tmphotbak . sql ;

??????????? set feedback off

??????????? set heading off

??????????? set verify off

??????????? set trimspool off

??????????? set pagesize 0

??????????? set linesize 200

??????????? define dir = '/u01/app/oracle/hotbak'

??????????? define script = '/tmp/hotbak.sql'

??????????? spool & script

??????????? select 'ho cp ' || name|| ' &dir' ? from v$datafile ;

??????????? spool off

??????????? alter database begin backup ;

??????????? start & script

??????????? alter database end backup ;

??????????? alter database backup controlfile to '&dir/controlbak.ctl' ;

??????????? create pfile = '&dir/initorcl.ora' from spfile ;

??????? SQL > start / tmp / tmphotbak . sql ; ? -- 執(zhí)行該腳本即可對數(shù)據(jù)庫進(jìn)行熱備

???????

??? 2. 基于表空間熱備的腳本

??????? SQL > ho cat / tmp / tmphotbak_tb . sql

??????????? set feedback off

??????????? set heading off

??????????? set verify off

??????????? set trimspool off

??????????? set pagesize 0

??????????? set linesize 200

??????????? define dir = '/u01/app/oracle/hotbak'

??????????? define script = '/tmp/hotbak_tb.sql'

??????????? ho rm & script

??????????? ho rm & dir /* ?

??????????? spool & script

??????????? select 'alter tablespace ' || tablespace_name || ' begin backup ;' ||

??????????? ? chr ( 10 )|| 'ho cp ' || file_name || ' &dir ' ||

??????????? ? chr ( 10 )|| 'alter tablespace ' || tablespace_name || ' end backup;'

??????????? ? from dba_data_files order by tablespace_name ;

??????????? spool off

??????????? start & script

??????????? alter database backup controlfile to '&dir/controlbak.ctl' ;

??????????? create pfile = '&dir/initorcl.ora' from spfile ;

??????? SQL > start / tmp / tmphotbak . sql ; ? -- 執(zhí)行該腳本即可對數(shù)據(jù)庫基于表空間進(jìn)行熱備

???????

八、備份的相關(guān)視圖

??? v$backup

???

??? SQL > desc v$backup ;

??? ? Name ????????????????????????????????????? Null ? ??? Type

??? ? ----------------------------------------- -------- ---------------------

??? ? FILE# ????????????????????????????????????????????? NUMBER

??? ? STATUS ???????????????????????????????????????????? VARCHAR2 ( 18 )

??? ? CHANGE# ??????????????????????????????????????????? NUMBER ?? -- 記錄備份時的 SCN 號

??? ? TIME ?????????????????????????????????????????????? DATE

?

??? SQL > select * from v$backup ; ?? -- 狀態(tài)為 NOT ACTIVE , 此時沒有任何數(shù)據(jù)處于備份狀態(tài)

?

??????? ? FILE# STATUS ??????????????? CHANGE# TIME

??? ---------- ------------------ ---------- ---------

??????????? ? 1 NOT ACTIVE ??????????? 1006747 16 - AUG - 10

??????????? ? 2 NOT ACTIVE ??????????? 1006793 16 - AUG - 10

??????????? ? 3 NOT ACTIVE ??????????? 1006729 16 - AUG - 10

??????????? ? 4 NOT ACTIVE ??????????? 1006807 16 - AUG - 10

??????????? ? 5 NOT ACTIVE ??????????? 1006717 16 - AUG - 10

?

??? SQL > alter tablespace users begin backup ; ? -- 對表空間 users 進(jìn)行熱備

?

??? Tablespace altered .

?

??? SQL > select * from v$backup ; ?? -- 對應(yīng)的 file# 為的處于 ACTIVE 狀態(tài)

?

??????? ? FILE# STATUS ??????????????? CHANGE# TIME

??? ---------- ------------------ ---------- ---------

??????????? ? 1 NOT ACTIVE ??????????? 1006747 16 - AUG - 10

??????????? ? 2 NOT ACTIVE ??????????? 1006793 16 - AUG - 10

??????????? ? 3 NOT ACTIVE ??????????? 1006729 16 - AUG - 10

??????????? ? 4 ACTIVE ??????????????? 1006852 16 - AUG - 10

??????????? ? 5 NOT ACTIVE ??????????? 1006717 16 - AUG - 10

?

??? -- 備份時發(fā)生斷電或意外故障的恢復(fù)

??? -- 假定 users 表空間目前置于 begin bakup 模式，系統(tǒng)斷電 ?????

??????? SQL > alter tablespace users begin backup ;

?

??????? Tablespace altered .

???????

??????? -- 在另一個會話中強(qiáng)制關(guān)閉數(shù)據(jù)庫 ?

??????? SQL > shutdown abort ;

??????? ORACLE instance shut down .

?

??????? -- 啟動后收到錯誤提示

??????? SQL > startup

??????? ORACLE instance started .

?

??????? Total System Global Area ? 469762048 bytes

??????? Fixed Size ????????????????? 1220048 bytes

??????? Variable Size ????????????? 92275248 bytes

??????? Database Buffers ????????? 373293056 bytes

??????? Redo Buffers ??????????????? 2973696 bytes

??????? Database mounted .

??????? ORA - 01113 : file 4 needs media recovery

??????? ORA - 01110 : data file 4 : '/u01/app/oracle/oradata/orcl/users01.dbf'

?

??????? -- 查看備份視圖，文件此時處于活動狀態(tài)

??????? SQL > select * from v$backup ;

?

??????????? ? FILE# STATUS ??????????????? CHANGE# TIME

??????? ---------- ------------------ ---------- ---------

??????????????? ? 1 NOT ACTIVE ??????????? 1006958 16 - AUG - 10

??????????????? ? 2 NOT ACTIVE ??????????? 1006983 16 - AUG - 10

??????????????? ? 3 NOT ACTIVE ??????????? 1006943 16 - AUG - 10

??????????????? ? 4 ACTIVE ??????????????? 1007072 16 - AUG - 10

??????????????? ? 5 NOT ACTIVE ??????????? 1006931 16 - AUG - 10

?

??????? -- 使用 end backup 來終止備份

??????? SQL > alter database datafile 4 end backup ; ?? -- 此處也可以使用 recover datafile 4 來完成恢復(fù)

?

??????? Database altered .

?

??????? SQL > alter database open ;

?

??????? Database altered .

?

??????? SQL > select * from dual ;

?

??????? D

??????? -

??????? X

?

??? 有關(guān) Oracle 的冷備份，請參考： Oracle 冷備份

九、更多參考

?

Oracle 冷備份

?

SPFILE 錯誤導(dǎo)致數(shù)據(jù)庫無法啟動

?

Oracle 用戶、對象權(quán)限、系統(tǒng)權(quán)限

?

Oracle 角色、配置文件

?

? Oracle 聯(lián)機(jī)重做日志文件(ONLINE LOG FILE)

?

? Oracle 控制文件(CONTROLFILE)

?

? Oracle 表空間與數(shù)據(jù)文件

?

Oracle 歸檔日志

?

Oracle 熱備份