Oracle冷備份與恢復及案例分析

baojian322 13年前發布 | 7K 次閱讀 Sublime Text eCromedos

1、手動進行冷備份

[oracle@linuxForOra ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Sep 4 20:56:11 2011

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 ~]$ </pre>上述的基本情況就是：關閉數據庫的狀態下，利用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</pre>

第二種腳本：

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 </pre>

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</pre>上步操作說明：刪除用戶數據文件，數據庫還能正常工作，但是訪問已刪除的表空間的對象時會報錯！
接下來使用先前的冷備份進行恢復

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;

    11
    22

SQL> </pre>至此，使用冷備份恢復完成！
附加案例：
說明：如果沒有先前冷備份的數據文件，具體操作如下：
關閉數據庫，刪除數據文件、啟動：

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

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> </pre>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> </pre>(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

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> </pre>

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 necessary

system系統表空間是不能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 </pre>

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> </pre>恢復完畢！
(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_1106%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></pre>(4)、日志文件丟失(分為兩種情況)--shutdown immediate 和 shutdown abort
a、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

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.</pre>完成恢復！
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

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.

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_12%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_12%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_12%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_12%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></pre>恢復完成！
(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

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_12%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> </pre>恢復完成！</span></div></span>

本文由用戶 baojian322 自行上傳分享，僅供網友學習交流。所有權歸原作者，若您的權利被侵害，請聯系管理員。

轉載本站原創文章，請注明出處，并保留原始鏈接、圖片水印。

本站是一個以用戶分享為主的開源技術平臺，歡迎各類分享！

本文地址：http://www.baiduhome.net/blog/5035946816763698169.html

Sublime Text eCromedos

寫博客