冷备就是把数据库的相关文件复制一份,它只能做还原操作,不能做恢复操作。现在登录到数据库中,把数据文件、控制文件、日志文件存放路径找到,然后关闭数据库,备份列出的文件。
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/下面,然后删除备份前的所有文件,模拟磁盘故障。
[oracle@linux datafile]$ pwd
/u01/datafile
[oracle@linux datafile]$ cp /u01/app/oracle/oradata/data/* /u01/datafile/
[oracle@linux 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
[oracle@linux datafile]$ rm -rf temp01.dbf
[oracle@linux 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
[oracle@linux 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