標籤：oracle oracle冷備份
Oracle 冷備份詳解
--準備工作
select * from v$database;
select file_name from dba_data_files;
create tablespace inv datafile '/oradata/ocm/inv01.dbf' size 2048M;
create user inv identified by inv default tablespace inv temporary tablespace temp;
grant dba,resource to inv;
alter user scott account unlock;
SQL> conn scott/tiger
select * from tab;
select 'create table '||tname||' as select * from scott.'||tname||';' from tab;
create table BONUS as select * from scott.BONUS;
create table DEPT as select * from scott.DEPT;
create table EMP as select * from scott.EMP;
create table SALGRADE as select * from scott.SALGRADE;
create table te_objects as select * from dba_objects;
--oracle冷備份
select instance_name,version,status,archiver,database_status from v$instance;
select dbid,name,log_mode from v$database;
--需要備份檔案包括：
/*
資料檔案，聯機重做記錄檔，控制檔案，臨時檔案，參數檔案，密碼檔案，監聽檔案，tnsnames檔案，sqlnet檔案
*/
--執行語句準備
select 'cp '|| name ||' /ggs/backup/cold_back' from v$datafile
union all
select 'cp '|| name ||' /ggs/backup/cold_back' from v$tempfile
union all
select 'cp '|| member ||' /ggs/backup/cold_back' from v$logfile
union all
select 'cp '|| name ||' /ggs/backup/cold_back' from v$controlfile;
--註：使用以上SQL產生的Linux命令進行執行，如下：
cp /oradata/ocm/system01.dbf /ggs/backup/cold_back
cp /oradata/ocm/sysaux01.dbf /ggs/backup/cold_back
cp /oradata/ocm/undotbs01.dbf /ggs/backup/cold_back
cp /oradata/ocm/users01.dbf /ggs/backup/cold_back
cp /oradata/ocm/ogg.dbf /ggs/backup/cold_back
cp /oradata/ocm/inv01.dbf /ggs/backup/cold_back
cp /oradata/ocm/temp01.dbf /ggs/backup/cold_back
cp /oradata/ocm/redo04a.rdo /ggs/backup/cold_back
cp /oradata/ocm/redo04b.rdo /ggs/backup/cold_back
cp /oradata/ocm/redo03a.rdo /ggs/backup/cold_back
cp /oradata/ocm/redo03b.rdo /ggs/backup/cold_back
cp /oradata/ocm/redo02a.rdo /ggs/backup/cold_back
cp /oradata/ocm/redo02b.rdo /ggs/backup/cold_back
cp /oradata/ocm/redo01a.rdo /ggs/backup/cold_back
cp /oradata/ocm/redo01b.rdo /ggs/backup/cold_back
cp /oradata/ocm/control01.ctl /ggs/backup/cold_back
cp /oradata/ocm/control02.ctl /ggs/backup/cold_back
cp /oradata/ocm/control03.ctl /ggs/backup/cold_back
create pfile='/ggs/backup/cold_back/dbs/initocm.ora' from spfile;
cp $ORACLE_HOME/dbs/orapwocm /ggs/backup/cold_back/dbs/
cp $ORACLE_HOME/network/admin/ /ggs/backup/cold_back/admin/
--關閉資料庫
alter system archive log all;
shutdown immediate;
--執行代碼
cp /oradata/ocm/system01.dbf /ggs/backup/cold_back
cp /oradata/ocm/sysaux01.dbf /ggs/backup/cold_back
cp /oradata/ocm/undotbs01.dbf /ggs/backup/cold_back
cp /oradata/ocm/users01.dbf /ggs/backup/cold_back
cp /oradata/ocm/ogg.dbf /ggs/backup/cold_back
cp /oradata/ocm/inv01.dbf /ggs/backup/cold_back
cp /oradata/ocm/temp01.dbf /ggs/backup/cold_back
cp /oradata/ocm/redo04a.rdo /ggs/backup/cold_back
cp /oradata/ocm/redo04b.rdo /ggs/backup/cold_back
cp /oradata/ocm/redo03a.rdo /ggs/backup/cold_back
cp /oradata/ocm/redo03b.rdo /ggs/backup/cold_back
cp /oradata/ocm/redo02a.rdo /ggs/backup/cold_back
cp /oradata/ocm/redo02b.rdo /ggs/backup/cold_back
cp /oradata/ocm/redo01a.rdo /ggs/backup/cold_back
cp /oradata/ocm/redo01b.rdo /ggs/backup/cold_back
cp /oradata/ocm/control01.ctl /ggs/backup/cold_back
cp /oradata/ocm/control02.ctl /ggs/backup/cold_back
cp /oradata/ocm/control03.ctl /ggs/backup/cold_back
cp $ORACLE_HOME/dbs/orapwocm /ggs/backup/cold_back/dbs/
cp -r $ORACLE_HOME/network/admin/ /ggs/backup/cold_back/
--啟動資料庫
sqlplus / as sysdba
startup
【冷備份的優點和缺點】
01、優點冷備模式下概念易於理解，即將需要備份的檔案複製到安全的位置操作比較簡單，不需要太多的幹預容易恢複到某個時間點上(只需將檔案再拷貝回去)能與歸檔方法相結合，作資料庫“最新狀態”的恢複。
02、缺點 備份時，資料庫必須處於一致性關閉狀態 只能提供到某一時間點的恢複 備份時速度比較慢，尤其是資料量大效能影響比較大 不能實現基於表和使用者層級的資料恢複。
Oracle 冷備份詳解【實戰案例】
