Oracle冷备份与恢复及案例分析
1、手动进行冷备份
2、为了更方便的进行冷备份，将冷备份的操作写成批处理脚本
[oracle@linuxForOra coldbackup]$ vi coldbak.sql
第一种脚本：
[oracle@linuxForOra ~]$ sqlplus /nolog SQL*Plus: Release 10.2.0.1.0 - Production on Sun Sep 4 20:56:11 2011 Copyright (c) 1982, 2005, Oracle. All rights reserved. SQL> conn / as sysdba; Connected. SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /u01/oracle/oradata/general10g/system01.dbf /u01/oracle/oradata/general10g/undotbs01.dbf /u01/oracle/oradata/general10g/sysaux01.dbf /u01/oracle/oradata/general10g/users01.dbf /u01/oracle/oradata/general10g/example01.dbf SQL> select name from v$controlfile; NAME -------------------------------------------------------------------------------- /u01/oracle/oradata/general10g/control01.ctl /u01/oracle/oradata/general10g/control02.ctl /u01/oracle/oradata/general10g/control03.ctl /u01/oracle/oradata/general10g/control04.ctl SQL> select member from v$logfile; MEMBER -------------------------------------------------------------------------------- /u01/oracle/oradata/general10g/redo03.log /u01/oracle/oradata/general10g/redo02.log /u01/oracle/oradata/general10g/redo01.log SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> ! [oracle@linuxForOra ~]$ cp /u01/oracle/oradata/general10g/*.dbf /u01/oracle/coldbackup/ [oracle@linuxForOra ~]$ cp /u01/oracle/oradata/general10g/*.ctl /u01/oracle/coldbackup/ [oracle@linuxForOra ~]$ cp /u01/oracle/oradata/general10g/*.log /u01/oracle/coldbackup/ [oracle@linuxForOra ~]$上述的基本情况就是：关闭数据库的状态下，利用linux拷贝命令，将数据文件、控制文件、重做日志文件备份到相应的位置。
2、为了更方便的进行冷备份，将冷备份的操作写成批处理脚本
[oracle@linuxForOra coldbackup]$ vi coldbak.sql
第一种脚本：
#rem desc:offline full backup database $ORACLE_HOME/bin/sqlplus "/as sysdba" <<EOF #--shutdown database shutdown immediate #--Copy Data file !cp /u01/oracle/oradata/general10g/*.dbf /u01/oracle/coldbackup/ #--Copy Control file !cp /u01/oracle/oradata/general10g/*.ctl /u01/oracle/coldbackup/ --Copy Log file !cp /u01/oracle/oradata/general10g/*.log /u01/oracle/coldbackup/ #--startup database startup
第二种脚本：
3、模拟相关文件丢失或者损坏情况下，利用冷备份进行恢复
(1)、非系统表空间数据文件丢失情况
状态：有非系统表空间，数据文件是cold_test.dbf，存在一张表create table cold_test(a int); 表中有数据：11，12
操作：查看非系统表空间，然后移除数据文件cold_test.dbf
接下来使用先前的冷备份进行恢复
附加案例：
说明：如果没有先前冷备份的数据文件，具体操作如下：
关闭数据库，删除数据文件、启动：
解决方法：
(3)、undo表空间丢失
a、shutdown immediate
b、shutdown abort
(5)、控制文件丢失
set pagesize 0 linesize 32767 feedback off verify off trimspool on termout off trimout on serveroutput on define fil=/u01/oracle/coldbk.sql define log=/u01/oracle/coldbk.log spool &fil prompt connect sys/syspass@general10g as sysdba; prompt shutdown immediate; select 'host cp ' || file_name || ' /u01/oracle/coldbackup'||substr(file_name,instr(file_name,'/',-1,1)) from dba_data_files; prompt startup spool off spool &log @&fil; spool off;注意：脚本改成可执行权限
3、模拟相关文件丢失或者损坏情况下，利用冷备份进行恢复
(1)、非系统表空间数据文件丢失情况
状态：有非系统表空间，数据文件是cold_test.dbf，存在一张表create table cold_test(a int); 表中有数据：11，12
操作：查看非系统表空间，然后移除数据文件cold_test.dbf
SQL> col status for a10; SQL> col name for a60; SQL> select status ,name from v$datafile; STATUS NAME ---------- ------------------------------------------------------------ SYSTEM /u01/oracle/oradata/general10g/system01.dbf ONLINE /u01/oracle/oradata/general10g/undotbs01.dbf ONLINE /u01/oracle/oradata/general10g/sysaux01.dbf ONLINE /u01/oracle/oradata/general10g/users01.dbf ONLINE /u01/oracle/oradata/general10g/example01.dbf ONLINE /u01/oracle/oradata/general10g/cold_test.dbf 6 rows selected. SQL> ! [oracle@linuxForOra general10g]$ rm -f cold_test.dbf
SQL> select tablespace_name from dba_data_files where file_name='/u01/oracle/oradata/general10g/cold_test.dbf'; TABLESPACE_NAME ------------------------------ COLD_TEST SQL> select * from cold_test; select * from cold_test * ERROR at line 1: ORA-01116: error in opening database file 6 ORA-01110: data file 6: '/u01/oracle/oradata/general10g/cold_test.dbf' ORA-27041: unable to open file Linux Error: 2: No such file or directory Additional information: 3上步操作说明：删除用户数据文件，数据库还能正常工作，但是访问已删除的表空间的对象时会报错！
接下来使用先前的冷备份进行恢复
SQL> select file#,status,name from v$datafile; FILE# STATUS NAME ---------- ---------- -------------------------------------------------- 1 SYSTEM /u01/oracle/oradata/general10g/system01.dbf 2 ONLINE /u01/oracle/oradata/general10g/undotbs01.dbf 3 ONLINE /u01/oracle/oradata/general10g/sysaux01.dbf 4 ONLINE /u01/oracle/oradata/general10g/users01.dbf 5 ONLINE /u01/oracle/oradata/general10g/example01.dbf 6 ONLINE /u01/oracle/oradata/general10g/cold_test.dbf 6 rows selected. SQL> alter database datafile 6 offline; Database altered. SQL> select file#,status,name from v$datafile; FILE# STATUS NAME ---------- ---------- -------------------------------------------------- 1 SYSTEM /u01/oracle/oradata/general10g/system01.dbf 2 ONLINE /u01/oracle/oradata/general10g/undotbs01.dbf 3 ONLINE /u01/oracle/oradata/general10g/sysaux01.dbf 4 ONLINE /u01/oracle/oradata/general10g/users01.dbf 5 ONLINE /u01/oracle/oradata/general10g/example01.dbf 6 RECOVER /u01/oracle/oradata/general10g/cold_test.dbf 6 rows selected. SQL> !cp /u01/oracle/coldbackup/cold_test.dbf /u01/oracle/oradata/general10g SQL> recover datafile 6; Media recovery complete. SQL> select file#,status,name from v$datafile; FILE# STATUS NAME ---------- ---------- -------------------------------------------------- 1 SYSTEM /u01/oracle/oradata/general10g/system01.dbf 2 ONLINE /u01/oracle/oradata/general10g/undotbs01.dbf 3 ONLINE /u01/oracle/oradata/general10g/sysaux01.dbf 4 ONLINE /u01/oracle/oradata/general10g/users01.dbf 5 ONLINE /u01/oracle/oradata/general10g/example01.dbf 6 OFFLINE /u01/oracle/oradata/general10g/cold_test.dbf 6 rows selected. SQL> alter database datafile 6 online; Database altered. SQL> select file#,status,name from v$datafile; FILE# STATUS NAME ---------- ---------- -------------------------------------------------- 1 SYSTEM /u01/oracle/oradata/general10g/system01.dbf 2 ONLINE /u01/oracle/oradata/general10g/undotbs01.dbf 3 ONLINE /u01/oracle/oradata/general10g/sysaux01.dbf 4 ONLINE /u01/oracle/oradata/general10g/users01.dbf 5 ONLINE /u01/oracle/oradata/general10g/example01.dbf 6 ONLINE /u01/oracle/oradata/general10g/cold_test.dbf 6 rows selected. SQL> select * from cold_test; A ---------- 11 22 SQL>至此，使用冷备份恢复完成！
附加案例：
说明：如果没有先前冷备份的数据文件，具体操作如下：
关闭数据库，删除数据文件、启动：
SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> ! [oracle@linuxForOra general10g]$ ls cold_test.dbf control03.ctl redo01.log sysaux01.dbf undotbs01.dbf control01.ctl control04.ctl redo02.log system01.dbf users01.dbf control02.ctl example01.dbf redo03.log temp01.dbf [oracle@linuxForOra general10g]$ rm -f cold_test.dbf [oracle@linuxForOra general10g]$ sqlplus /nolog SQL*Plus: Release 10.2.0.1.0 - Production on Tue Sep 6 15:17:40 2011 Copyright (c) 1982, 2005, Oracle. All rights reserved. SQL> conn / as sysdba; Connected to an idle instance. SQL> startup; ORACLE instance started. Total System Global Area 167772160 bytes Fixed Size 1218316 bytes Variable Size 71305460 bytes Database Buffers 92274688 bytes Redo Buffers 2973696 bytes Database mounted. ORA-01157: cannot identify/lock data file 6 - see DBWR trace file ORA-01110: data file 6: '/u01/oracle/oradata/general10g/cold_test.dbf' SQL>ORA-01157: 无法标识/锁定数据文件 2 - 请参阅 DBWR 跟踪文件
解决方法：
SQL> alter database datafile 6 offline; Database altered. SQL> select open_mode from v$database; OPEN_MODE ---------- MOUNTED SQL> alter database open; Database altered. SQL> alter database create datafile '/u01/oracle/oradata/general10g/cold_test.dbf'; Database altered. SQL> select status from v$datafile; STATUS ------- SYSTEM ONLINE ONLINE ONLINE ONLINE OFFLINE 6 rows selected. SQL> alter database datafile 6 online; alter database datafile 6 online * ERROR at line 1: ORA-01113: file 6 needs media recovery ORA-01110: data file 6: '/u01/oracle/oradata/general10g/cold_test.dbf' SQL> recover datafile 6; Media recovery complete. SQL> alter database datafile 6 online; Database altered. SQL>(2)、系统表空间丢失
[oracle@linuxForOra general10g]$ rm -f system01.dbf [oracle@linuxForOra general10g]$ ls control01.ctl control04.ctl redo02.log temp01.dbf control02.ctl example01.dbf redo03.log undotbs01.dbf control03.ctl redo01.log sysaux01.dbf users01.dbf [oracle@linuxForOra general10g]$ ls -l total 891476 -rw-r----- 1 oracle oinstall 7094272 Sep 6 15:52 control01.ctl -rw-r----- 1 oracle oinstall 7094272 Sep 6 15:52 control02.ctl -rw-r----- 1 oracle oinstall 7094272 Sep 6 15:52 control03.ctl -rw-r----- 1 oracle oinstall 7094272 Sep 6 15:52 control04.ctl -rw-r----- 1 oracle oinstall 104865792 Sep 6 15:50 example01.dbf -rw-r----- 1 oracle oinstall 52429312 Sep 6 15:50 redo01.log -rw-r----- 1 oracle oinstall 52429312 Sep 6 15:50 redo02.log -rw-r----- 1 oracle oinstall 52429312 Sep 6 15:50 redo03.log -rw-r----- 1 oracle oinstall 304095232 Sep 6 15:50 sysaux01.dbf -rw-r----- 1 oracle oinstall 22028288 Sep 6 10:53 temp01.dbf -rw-r----- 1 oracle oinstall 272637952 Sep 6 15:50 undotbs01.dbf -rw-r----- 1 oracle oinstall 23601152 Sep 6 15:50 users01.dbf [oracle@linuxForOra general10g]$ sqlplus /nolog SQL*Plus: Release 10.2.0.1.0 - Production on Tue Sep 6 15:52:24 2011 Copyright (c) 1982, 2005, Oracle. All rights reserved. SQL> conn / as sysdba; Connected. SQL> show user; USER is "SYS" SQL> select * from dual; select * from dual * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-01116: error in opening database file 1 ORA-01110: data file 1: '/u01/oracle/oradata/general10g/system01.dbf' ORA-27041: unable to open file Linux Error: 2: No such file or directory Additional information: 3 SQL>
SQL> alter database datafile 1 offline; alter database datafile 1 offline * ERROR at line 1: ORA-01541: system tablespace cannot be brought offline; shut down if necessarysystem系统表空间是不能offline的，所以需要强制关闭数据库(abort)，并启动到mount状态
[oracle@linuxForOra general10g]$ cp /u01/oracle/coldbackup/system01.dbf /u01/oracle/oradata/general10g/
SQL> shutdown abort; ORACLE instance shut down. SQL> startup mount; ORACLE instance started. Total System Global Area 167772160 bytes Fixed Size 1218316 bytes Variable Size 71305460 bytes Database Buffers 92274688 bytes Redo Buffers 2973696 bytes Database mounted. SQL> select open_mode from v$database; OPEN_MODE ---------- MOUNTED
SQL> alter database open; alter database open * ERROR at line 1: ORA-01113: file 1 needs media recovery ORA-01110: data file 1: '/u01/oracle/oradata/general10g/system01.dbf' SQL> recover datafile 1; Media recovery complete. SQL> alter database open; Database altered. SQL>恢复完毕！
(3)、undo表空间丢失
[oracle@linuxForOra general10g]$ rm -f undotbs01.dbf SQL> col recover for a10; SQL> select status,recover from v$datafile_header; STATUS RECOVER ------- ---------- ONLINE NO ONLINE ONLINE NO ONLINE NO ONLINE NO SQL> SQL> col status for a10; SQL> col name for a50; SQL> set linesize 150; SQL> select file#,status,name from v$datafile; FILE# STATUS NAME ---------- ---------- -------------------------------------------------- 1 SYSTEM /u01/oracle/oradata/general10g/system01.dbf 2 ONLINE /u01/oracle/oradata/general10g/undotbs01.dbf 3 ONLINE /u01/oracle/oradata/general10g/sysaux01.dbf 4 ONLINE /u01/oracle/oradata/general10g/users01.dbf 5 ONLINE /u01/oracle/oradata/general10g/example01.dbf SQL> SQL> alter system checkpoint; System altered. SQL> select status,recover from v$datafile_header; STATUS RECOVER ---------- ---------- ONLINE NO OFFLINE ONLINE NO ONLINE NO ONLINE NO SQL> select file#,status,name from v$datafile; FILE# STATUS NAME ---------- ---------- -------------------------------------------------- 1 SYSTEM /u01/oracle/oradata/general10g/system01.dbf 2 RECOVER /u01/oracle/oradata/general10g/undotbs01.dbf 3 ONLINE /u01/oracle/oradata/general10g/sysaux01.dbf 4 ONLINE /u01/oracle/oradata/general10g/users01.dbf 5 ONLINE /u01/oracle/oradata/general10g/example01.dbf SQL> !cp /u01/oracle/coldbackup/undotbs01.dbf /u01/oracle/oradata/general10g/ SQL> recover datafile 2; ORA-00279: change 4201437 generated at 09/07/2011 16:37:05 needed for thread 1 ORA-00289: suggestion : /u01/oracle/flash_recovery_area/GENERAL10G/archivelog/2011_09_07/o1_mf_1_106_%u_ .arc ORA-00280: change 4201437 for thread 1 is in sequence #106 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} AUTO ORA-00603: ORACLE server session terminated by fatal error ERROR: ORA-03114: not connected to ORACLE SQL> SQL> conn / as sysdba; Connected. SQL> select status,recover from v$datafile_header; STATUS REC ------- --- ONLINE NO OFFLINE NO ONLINE NO ONLINE NO ONLINE NO SQL> col status for a10; SQL> col name for a50; SQL> set linesize 150; SQL> select file#,status,name from v$datafile; FILE# STATUS NAME ---------- ---------- -------------------------------------------------- 1 SYSTEM /u01/oracle/oradata/general10g/system01.dbf 2 OFFLINE /u01/oracle/oradata/general10g/undotbs01.dbf 3 ONLINE /u01/oracle/oradata/general10g/sysaux01.dbf 4 ONLINE /u01/oracle/oradata/general10g/users01.dbf 5 ONLINE /u01/oracle/oradata/general10g/example01.dbf SQL> SQL> alter database datafile 2 online; Database altered. SQL>(4)、日志文件丢失(分为两种情况)--shutdown immediate 和 shutdown aborta、shutdown immediate
[oracle@linuxForOra general10g]$ rm -f redo* [oracle@linuxForOra general10g]$ ls control01.ctl control04.ctl sysaux01.dbf undotbs01.dbf control02.ctl example01.dbf system01.dbf users01.dbf control03.ctl GENERAL10G temp01.dbf [oracle@linuxForOra general10g]$ sqlplus /nolog SQL*Plus: Release 10.2.0.1.0 - Production on Wed Sep 7 19:53:41 2011 Copyright (c) 1982, 2005, Oracle. All rights reserved. SQL> conn / as sysdba; Connected. SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup; ORACLE instance started. Total System Global Area 167772160 bytes Fixed Size 1218316 bytes Variable Size 75499764 bytes Database Buffers 88080384 bytes Redo Buffers 2973696 bytes Database mounted. ORA-00313: open failed for members of log group 1 of thread 1 ORA-00312: online log 1 thread 1: '/u01/oracle/oradata/general10g/redo01.log' SQL> SQL> select group#,archived,status from v$log; GROUP# ARC STATUS ---------- --- ---------------- 1 NO CURRENT 3 YES UNUSED 2 YES UNUSED SQL> alter database clear logfile group 3; Database altered. SQL> alter database clear logfile group 2; Database altered. SQL> SQL> recover database until cancel; Media recovery complete. SQL> alter database open resetlogs; Database altered.完成恢复！
b、shutdown abort
[oracle@linuxForOra coldbackup]$ sqlplus /nolog SQL*Plus: Release 10.2.0.1.0 - Production on Wed Sep 7 20:39:55 2011 Copyright (c) 1982, 2005, Oracle. All rights reserved. SQL> conn / as sysdba; Connected. SQL> shutdown abort; ORACLE instance shut down. SQL> !rm -f /u01/oracle/oradata/general10g/redo* SQL> startup; ORACLE instance started. Total System Global Area 167772160 bytes Fixed Size 1218316 bytes Variable Size 75499764 bytes Database Buffers 88080384 bytes Redo Buffers 2973696 bytes Database mounted. ORA-00313: open failed for members of log group 1 of thread 1 ORA-00312: online log 1 thread 1: '/u01/oracle/oradata/general10g/redo01.log' ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory Additional information: 3 SQL> SQL> select group#,archived,status from v$log; GROUP# ARC STATUS ---------- --- ---------------- 1 NO CURRENT 3 YES UNUSED 2 YES INACTIVE SQL> recover database until cancel; ORA-00279: change 4202761 generated at 09/07/2011 20:29:53 needed for thread 1 ORA-00289: suggestion : /u01/oracle/flash_recovery_area/GENERAL10G/archivelog/2011_09_07/o1_mf_1_2_%u_.a rc ORA-00280: change 4202761 for thread 1 is in sequence #2 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} auto ORA-00308: cannot open archived log '/u01/oracle/flash_recovery_area/GENERAL10G/archivelog/2011_09_07/o1_mf_1_2_%u_. arc' ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory Additional information: 3 ORA-00308: cannot open archived log '/u01/oracle/flash_recovery_area/GENERAL10G/archivelog/2011_09_07/o1_mf_1_2_%u_. arc' ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory Additional information: 3 ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below ORA-01194: file 1 needs more recovery to be consistent ORA-01110: data file 1: '/u01/oracle/oradata/general10g/system01.dbf' SQL> recover database until cancel; ORA-00279: change 4202761 generated at 09/07/2011 20:29:53 needed for thread 1 ORA-00289: suggestion : /u01/oracle/flash_recovery_area/GENERAL10G/archivelog/2011_09_07/o1_mf_1_2_%u_.a rc ORA-00280: change 4202761 for thread 1 is in sequence #2 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} auto ORA-00308: cannot open archived log '/u01/oracle/flash_recovery_area/GENERAL10G/archivelog/2011_09_07/o1_mf_1_2_%u_. arc' ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory Additional information: 3 ORA-00308: cannot open archived log '/u01/oracle/flash_recovery_area/GENERAL10G/archivelog/2011_09_07/o1_mf_1_2_%u_. arc' ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory Additional information: 3 ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below ORA-01194: file 1 needs more recovery to be consistent ORA-01110: data file 1: '/u01/oracle/oradata/general10g/system01.dbf' SQL> SQL> recover database until cancel; ORA-00279: change 4202761 generated at 09/07/2011 20:29:53 needed for thread 1 ORA-00289: suggestion : /u01/oracle/flash_recovery_area/GENERAL10G/archivelog/2011_09_07/o1_mf_1_2_%u_.a rc ORA-00280: change 4202761 for thread 1 is in sequence #2 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} /u01/oracle/coldbackup/redo01.log Log applied. Media recovery complete. SQL>恢复完成！(5)、控制文件丢失
[oracle@linuxForOra general10g]$ rm -f control* [oracle@linuxForOra general10g]$ sqlplus /nolog SQL*Plus: Release 10.2.0.1.0 - Production on Thu Sep 8 17:07:14 2011 Copyright (c) 1982, 2005, Oracle. All rights reserved. SQL> conn / as sysdba; Connected. SQL> shutdown immediate; ORA-00210: cannot open the specified control file ORA-00202: control file: '/u01/oracle/oradata/general10g/control01.ctl' ORA-27041: unable to open file Linux Error: 2: No such file or directory Additional information: 3 SQL> shutdown abort; ORACLE instance shut down. SQL> startup nomount; ORACLE instance started. Total System Global Area 167772160 bytes Fixed Size 1218316 bytes Variable Size 75499764 bytes Database Buffers 88080384 bytes Redo Buffers 2973696 bytes SQL> !cp /u01/oracle/coldbackup/control* /u01/oracle/oradata/general10g SQL> alter database mount; Database altered. SQL> alter database open resetlogs; alter database open resetlogs * ERROR at line 1: ORA-01139: RESETLOGS option only valid after an incomplete database recovery SQL> recover database using backup controlfile; ORA-00279: change 4268109 generated at 09/08/2011 15:52:56 needed for thread 1 ORA-00289: suggestion : /u01/oracle/flash_recovery_area/GENERAL10G/archivelog/2011_09_08/o1_mf_1_2_%u_.a rc ORA-00280: change 4268109 for thread 1 is in sequence #2 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} /u01/oracle/oradata/general10g/control01.ctl ORA-00317: file type 0 in header is not log file ORA-00334: archived log: '/u01/oracle/oradata/general10g/control01.ctl' SQL> alter database open resetlogs; alter database open resetlogs * ERROR at line 1: ORA-01092: ORACLE instance terminated. Disconnection forced SQL> conn / as sysdba; Connected to an idle instance. SQL> startup; ORACLE instance started. Total System Global Area 167772160 bytes Fixed Size 1218316 bytes Variable Size 75499764 bytes Database Buffers 88080384 bytes Redo Buffers 2973696 bytes Database mounted. Database opened. SQL> 恢复完成！
baojian322 
Yangcl 
honghu79