轉自：http://blog.csdn.net/newhappy2008/article/details/6587143
一、冷備份
數據庫在關閉狀態下完成所有物理系統文件拷貝的過程，也稱脫機備份
適合于非歸檔模式下，數據庫處于一致性狀態
二、步驟
首先在運行的庫中得到數據庫運行的所有的物理文件位置，然后在計劃內關閉數據庫(shutdown)
再執行拷貝物理文家到備份路徑或備份設備
備份完成后立即啟動數據庫讓其提供正常的服務
三、冷備腳本的寫法
首先應該在相關視圖里查出數據庫的數據文件，日志文件，控制文件，臨時文件所在的位置
注意：不要直接把oradata下的cp就行了，因為生產庫里各個文件通常分布在不同的磁盤，不同的地方，所以在去視圖里獲得真實路徑
--查看實例和數據庫的相關信息
SQL> select instance_name,version,status,archiver,database_status from v$instance;
INSTANCE_NAME VERSION STATUS ARCHIVE DATABASE_STATUS
---------------- ----------------- ------------ ------- -----------------
orcl 10.2.0.1.0 OPEN STOPPED ACTIVE
SQL> select dbid,name,log_mode from v$database;
DBID NAME LOG_MODE
---------- --------- ------------
1242732291 ORCL NOARCHIVELOG
--查看數據文件及狀態信息
SQL> select file_name,tablespace_name,status,online_status from dba_data_files;
FILE_NAME TABLESPACE STATUS ONLINE_
------------------------------------------------------- ---------- --------- -------
/u01/app/oracle/oradata/orcl/undotbs01.dbf UNDOTBS1 AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/system01.dbf SYSTEM AVAILABLE SYSTEM
/u01/app/oracle/oradata/orcl/sysaux01.dbf SYSAUX AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/users01.dbf USERS AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/example01.dbf EXAMPLE AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/tbs1_1.dbf TBS1 AVAILABLE ONLINE
/u01/app/oracle/oradata/orcl/tbs1_2.dbf TBS1 AVAILABLE ONLINE
--查看數據文件
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/system01.dbf
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/sysaux01.dbf
/u01/app/oracle/oradata/orcl/users01.dbf
/u01/app/oracle/oradata/orcl/example01.dbf
/u01/app/oracle/oradata/orcl/tbs1_1.dbf
/u01/app/oracle/oradata/orcl/tbs1_2.dbf
--查看臨時文件
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/temp01.dbf
--查看日志文件
SQL> select member from v$logfile;
MEMBER
------------------------------------------------------------
/u01/app/oracle/oradata/orcl/redo2a.rdo
/u01/app/oracle/oradata/orcl/redo2b.rdo
/u01/app/oracle/oradata/orcl/redo1a.rdo
/u01/app/oracle/oradata/orcl/redo3a.rdo
/u01/app/oracle/oradata/orcl/redo3b.rdo
/u01/app/oracle/oradata/orcl/redo1b.rdo
--查看控制文件
SQL> select name from v$controlfile;
NAME
------------------------------------------------------------
/u01/app/oracle/oradata/orcl/control01.ctl
/u01/app/oracle/oradata/orcl/control02.ctl
--創建備份目錄
SQL> ho mkdir /u01/app/oracle/coolbak
--使用連接符生成復制文件命令
SQL> select 'ho cp ' || name || ' /u01/app/oracle/coolbak' from v$controlfile;
'HOCP'||NAME||'/U01/APP/ORACLE/COOLBAK'
----------------------------------------------------------------------------------
ho cp /u01/app/oracle/oradata/orcl/control01.ctl /u01/app/oracle/coolbak
ho cp /u01/app/oracle/oradata/orcl/control02.ctl /u01/app/oracle/coolbak
SQL> save /tmp/tmpbak.sql; --將上面的輸入保存為tmpbak.sql
Created file /tmp/tmpbak.sql
SQL> ho vim /tmp/tmpbak.sql --編輯tmpbak.sql,將下面的內容輸入到tmpbak.sql
set feedback off
set heading off
set verify off
set trimspool off
set pagesize 0
set linesize 200
define dir = '/u01/app/oracle/coolbak'
define script = '/tmp/coolbak.sql'
spool &script
select 'ho cp ' || name || ' &dir' from v$controlfile
union all
select 'ho cp ' || name || ' &dir' from v$datafile
union all
select 'ho cp ' || member || ' &dir' from v$logfile
union all
select 'ho cp ' || name || ' &dir' from v$tempfile
/
create pfile = '&dir/initorcl.ora' from spfile;
ho cp /u01/app/oracle/10g/dbs/orapworcl &dir
spool off
shutdown immediate
start &script
ho rm &script
startup
--執行tmpbak.sql
SQL> @/tmp/tmpbak.sql;
--執行過程及數據庫啟動略
--啟動后查看備份的文件
SQL> ho ls /u01/app/oracle/coolbak
control01.ctl orapworcl redo2b.rdo system01.dbf users01.dbf
control02.ctl redo1a.rdo redo3a.rdo tbs1_1.dbf
example01.dbf redo1b.rdo redo3b.rdo tbs1_2.dbf
initorcl.ora redo2a.rdo sysaux01.dbf undotbs01.dbf
四、總結
優點
冷備模式下概念易于理解，即將需要備份的文件復制到安全的位置
操作比較簡單，不需要太多的干預
容易恢復到某個時間點上(只需將文件再拷貝回去)
能與歸檔方法相結合，作數據庫“最新狀態”的恢復。
缺點
備份時，數據庫必須處于一致性關閉狀態
只能提供到某一時間點的恢復
備份時速度比較慢，尤其是數據量大性能影響比較大
不能實現基于表和用戶級別的數據恢復