oracle 11g 手工冷備

標籤：非歸檔   table   不一致   into   cat   找不到   exist   bytes   參數   

查看資料庫是否處於非歸檔模式

關閉資料庫
shutdown immediate
備份控制檔案和資料檔案（沒有備份記錄檔，建議一起備份）

[[email protected] PROD]$ lltotal 2014624-rw-r----- 1 oracle oinstall   9748480 Jan 24 21:49 control01.ctl-rw-r----- 1 oracle oinstall   9748480 Jan 24 21:49 control02.ctl-rw-r----- 1 oracle oinstall 363077632 Jan 24 21:49 example01.dbf-rw-r----- 1 oracle oinstall  52429312 Jan 24 21:48 redo01.log-rw-r----- 1 oracle oinstall  52429312 Jan 24 21:49 redo02.log-rw-r----- 1 oracle oinstall  52429312 Jan 24 21:48 redo03.log-rw-r----- 1 oracle oinstall 576724992 Jan 24 21:49 sysaux01.dbf-rw-r----- 1 oracle oinstall 807411712 Jan 24 21:49 system01.dbf-rw-r----- 1 oracle oinstall  30416896 Jan 24 21:48 temp01.dbf-rw-r----- 1 oracle oinstall 110108672 Jan 24 21:49 undotbs01.dbf-rw-r----- 1 oracle oinstall  26222592 Jan 24 21:49 users01.dbf[[email protected] PROD]$ cp *.dbf /home/oracle/bak/[[email protected] PROD]$ cp *.ctl /home/oracle/bak/[[email protected] PROD]$ 

啟動資料庫，並建立一個表，插入資料提交

startup  ——啟動資料庫[email protected]> create table t4(id number);Table created.[email protected]> insert into t4 values(1);1 row created.[email protected]> commit;Commit complete.[email protected]> select * from t4;    ID----------     1[email protected]> 

關閉資料庫，刪除資料檔案和控制檔案

[[email protected] PROD]$ rm -rf *.dbf[[email protected] PROD]$ rm -rf *.ctl[[email protected] PROD]$ lltotal 153612-rw-r----- 1 oracle oinstall 52429312 Jan 24 21:56 redo01.log-rw-r----- 1 oracle oinstall 52429312 Jan 24 21:57 redo02.log-rw-r----- 1 oracle oinstall 52429312 Jan 24 21:56 redo03.log[[email protected] PROD]$ 

啟動資料庫到nomount，說明參數檔案沒問題，到mount，找不到控制檔案，使用作業系統命令拷貝控制檔案再mount

[email protected]> startup nomount;ORACLE instance started.Total System Global Area  835104768 bytesFixed Size          2257840 bytesVariable Size         536874064 bytesDatabase Buffers      289406976 bytesRedo Buffers            6565888 bytes[email protected]> alter database mount;alter database mount*ERROR at line 1:ORA-00205: error in identifying control file, check alert log for more info[email protected]> host[[email protected] PROD]$ cp /home/oracle/bak/*.ctl ./[[email protected] PROD]$ lltotal 172652-rw-r----- 1 oracle oinstall  9748480 Jan 24 22:01 control01.ctl-rw-r----- 1 oracle oinstall  9748480 Jan 24 22:01 control02.ctl-rw-r----- 1 oracle oinstall 52429312 Jan 24 21:56 redo01.log-rw-r----- 1 oracle oinstall 52429312 Jan 24 21:57 redo02.log-rw-r----- 1 oracle oinstall 52429312 Jan 24 21:56 redo03.log[[email protected] PROD]$ exit[email protected]> alter database mount;——可以到mount了，控制檔案沒問題了Database altered.[email protected]> [email protected]> alter database open;——找不到資料檔案，alter database open*ERROR at line 1:ORA-01157: cannot identify/lock data file 1 - see DBWR trace fileORA-01110: data file 1: ‘/u01/app/oracle/oradata/PROD/system01.dbf‘[email protected]> 

拷貝回資料檔案

[[email protected] PROD]$ cp /home/oracle/bak/*.dbf ./[[email protected] PROD]$ lltotal 2014848-rw-r----- 1 oracle oinstall   9748480 Jan 24 22:05 control01.ctl-rw-r----- 1 oracle oinstall   9748480 Jan 24 22:05 control02.ctl-rw-r----- 1 oracle oinstall 363077632 Jan 24 22:03 example01.dbf-rw-r----- 1 oracle oinstall  52429312 Jan 24 21:56 redo01.log-rw-r----- 1 oracle oinstall  52429312 Jan 24 21:57 redo02.log-rw-r----- 1 oracle oinstall  52429312 Jan 24 21:56 redo03.log-rw-r----- 1 oracle oinstall 576724992 Jan 24 22:04 sysaux01.dbf-rw-r----- 1 oracle oinstall 807411712 Jan 24 22:05 system01.dbf-rw-r----- 1 oracle oinstall  30416896 Jan 24 22:05 temp01.dbf-rw-r----- 1 oracle oinstall 110108672 Jan 24 22:05 undotbs01.dbf-rw-r----- 1 oracle oinstall  26222592 Jan 24 22:05 users01.dbf[[email protected] PROD]$ exit[email protected]> alter database open;alter database open*ERROR at line 1:ORA-03113: end-of-file on communication channelProcess ID: 2951Session ID: 125 Serial number: 3

發現報錯了，因為我備份了控制檔案和資料檔案，然後開庫建立了一個表，導致資料庫的記錄檔和控制檔案的scn不一致了，查看alter日誌

進入startup mount執行
recover database until cancel;
alter database open resetlogs;

startup mount ——進入到mount[email protected]> recover database until cancel;——不完全恢複Media recovery complete.[email protected]> alter database open resetlogs;——重設日誌Database altered.[email protected]> select open_mode from v$database;——資料庫已經開啟OPEN_MODE--------------------READ WRITE[email protected]> select * from t4;——查詢t4表，沒有資料select * from t4              *ERROR at line 1:ORA-00942: table or view does not exist[email protected]> 

oracle 11g 手工冷備