冷備就是把資料庫的相關檔案複制一份,它隻能做還原操作,不能做恢複操作。現在登入到資料庫中,把資料檔案、控制檔案、日志檔案存放路徑找到,然後關閉資料庫,備份列出的檔案。
SQL> show user
USER 為 "SYS"
SQL> select INSTANCE_NAME,STATUS from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
data OPEN
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/data/users01.dbf
/u01/app/oracle/oradata/data/sysaux01.dbf
/u01/app/oracle/oradata/data/undotbs01.dbf
/u01/app/oracle/oradata/data/system01.dbf
SQL> select name from v$controlfile;
NAME
--------------------------------------------------
/u01/app/oracle/oradata/data/control01.ctl
/u01/app/oracle/oradata/data/control02.ctl
/u01/app/oracle/oradata/data/control03.ctl
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------
/u01/app/oracle/oradata/data/redo03.log
/u01/app/oracle/oradata/data/redo02.log
/u01/app/oracle/oradata/data/redo01.log
SQL> shutdown immediate
資料庫已經關閉。
已經解除安裝資料庫。
ORACLE 例程已經關閉。
臨時檔案可以不備份,實際中還應該備份參數檔案。這裡我把上面所有的檔案複制到/u01/datafile/下面,然後删除備份前的所有檔案,模拟磁盤故障。
[[email protected] datafile]$ pwd
/u01/datafile
[[email protected] datafile]$ cp /u01/app/oracle/oradata/data/* /u01/datafile/
[[email protected] datafile]$ ll
總計 954144
-rw-r----- 1 oracle oinstall 7061504 10-08 07:46 control01.ctl
-rw-r----- 1 oracle oinstall 7061504 10-08 07:46 control02.ctl
-rw-r----- 1 oracle oinstall 7061504 10-08 07:46 control03.ctl
-rw-r----- 1 oracle oinstall 52429312 10-08 07:46 redo01.log
-rw-r----- 1 oracle oinstall 52429312 10-08 07:46 redo02.log
-rw-r----- 1 oracle oinstall 52429312 10-08 07:46 redo03.log
-rw-r----- 1 oracle oinstall 262152192 10-08 07:46 sysaux01.dbf
-rw-r----- 1 oracle oinstall 503324672 10-08 07:46 system01.dbf
-rw-r----- 1 oracle oinstall 20979712 10-08 07:46 temp01.dbf
-rw-r----- 1 oracle oinstall 26222592 10-08 07:46 undotbs01.dbf
-rw-r----- 1 oracle oinstall 5251072 10-08 07:46 users01.dbf
[[email protected] datafile]$ rm -rf temp01.dbf
[[email protected] datafile]$ ll
總計 953560
-rw-r----- 1 oracle oinstall 7061504 10-08 07:46 control01.ctl
-rw-r----- 1 oracle oinstall 7061504 10-08 07:46 control02.ctl
-rw-r----- 1 oracle oinstall 7061504 10-08 07:46 control03.ctl
-rw-r----- 1 oracle oinstall 52429312 10-08 07:46 redo01.log
-rw-r----- 1 oracle oinstall 52429312 10-08 07:46 redo02.log
-rw-r----- 1 oracle oinstall 52429312 10-08 07:46 redo03.log
-rw-r----- 1 oracle oinstall 262152192 10-08 07:46 sysaux01.dbf
-rw-r----- 1 oracle oinstall 503324672 10-08 07:46 system01.dbf
-rw-r----- 1 oracle oinstall 26222592 10-08 07:46 undotbs01.dbf
-rw-r----- 1 oracle oinstall 5251072 10-08 07:46 users01.dbf
[[email protected] datafile]$ rm -rf /u01/app/oracle/oradata/data/
可以看見資料庫不能正常啟動
SQL> conn /as sysdba
已連接配接到空閑例程。
SQL> startup
ORACLE 例程已經啟動。
Total System Global Area 167772160 bytes
Fixed Size 1266392 bytes
Variable Size 67112232 bytes
Database Buffers 96468992 bytes
Redo Buffers 2924544 bytes
ORA-00205: ?????????, ??????, ???????
SQL> select status from v$instance;
STATUS
------------------------
STARTED
SQL> shutdown immediate
ORA-01507: ??????
ORACLE 例程已經關閉。
下面記錄還原操作:
1.建立pfile參數檔案,修改控制檔案的存儲路徑。
SQL> create pfile from spfile;
檔案已建立。
*.control_files='/u01/datafile/control01.ctl','/u01/datafile/control02.ctl','/u01/datafile/control03.ctl'
2.mount資料庫。
SQL> conn /as sysdba
已連接配接到空閑例程。
SQL> startup nomount pfile='/u01/app/oracle/product/10.2.0/db_1/dbs/initdata.ora'
ORACLE 例程已經啟動。
Total System Global Area 167772160 bytes
Fixed Size 1266392 bytes
Variable Size 67112232 bytes
Database Buffers 96468992 bytes
Redo Buffers 2924544 bytes
SQL> alter database mount;
資料庫已更改。
此時,資料庫還不能open,因為資料庫檔案,日志檔案的路徑還不對。
SQL> alter database open;
alter database open
*
第 1 行出現錯誤:
ORA-01157: ????/?????? 1 - ??? DBWR ????
ORA-01110: ???? 1: '/u01/app/oracle/oradata/data/system01.dbf'
SQL> select status from v$instance;
STATUS
------------
MOUNTED
3.修改路徑
SQL> select name from v$datafile;
NAME
--------------------------------------------------
/u01/app/oracle/oradata/data/system01.dbf
/u01/app/oracle/oradata/data/undotbs01.dbf
/u01/app/oracle/oradata/data/sysaux01.dbf
/u01/app/oracle/oradata/data/users01.dbf
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------
/u01/app/oracle/oradata/data/redo03.log
/u01/app/oracle/oradata/data/redo02.log
/u01/app/oracle/oradata/data/redo01.log
SQL> alter database rename file '/u01/app/oracle/oradata/data/system01.dbf' to '/u01/datafile/system01.dbf';
資料庫已更改。
SQL> alter database rename file '/u01/app/oracle/oradata/data/undotbs01.dbf' to '/u01/datafile/undotbs01.dbf';
資料庫已更改。
SQL> alter database rename file '/u01/app/oracle/oradata/data/sysaux01.dbf' to '/u01/datafile/sysaux01.dbf';
資料庫已更改。
SQL> alter database rename file '/u01/app/oracle/oradata/data/users01.dbf' to '/u01/datafile/users01.dbf';
資料庫已更改。
SQL> alter database rename file '/u01/app/oracle/oradata/data/redo01.log' to '/u01/datafile/redo01.log';
資料庫已更改。
SQL> alter database rename file '/u01/app/oracle/oradata/data/redo02.log' to '/u01/datafile/redo02.log';
資料庫已更改。
SQL> alter database rename file '/u01/app/oracle/oradata/data/redo03.log' to '/u01/datafile/redo03.log';
資料庫已更改。
SQL> select name from v$datafile;
NAME
--------------------------------------------------
/u01/datafile/system01.dbf
/u01/datafile/undotbs01.dbf
/u01/datafile/sysaux01.dbf
/u01/datafile/users01.dbf
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------
/u01/datafile/redo03.log
/u01/datafile/redo02.log
/u01/datafile/redo01.log
4.打開資料庫驗證
SQL> alter database open;
資料庫已更改。
SQL> select count(*) from tab;
COUNT(*)
----------
3657