?

??????? 數據庫處于非歸檔狀態，其聯機日志循環覆蓋使用，這意味著如果數據庫需要進行介質恢復應用相關歸檔，又由于非歸檔導致歸檔文件不存在，那么此時數據庫只能執行基于取消的恢復，回退整個數據庫到上一次全備份狀態。或者修改數據文件頭部信息，來規避缺失歸檔。但不管怎么樣都會丟失數據。 下面介紹非歸檔模式下幾種常見故障處理方法。 一 非歸檔下的常用備份和恢復方法： 如何備份 Backing Up a Database in NOARCHIVELOG Mode You can only backup a database in NOARCHIVELOG mode when the database is closed and in a consistent state. 1 shutdown immediate 2 startup mount 3 backup database 如何恢復 注意 和歸檔模式下的恢復有如下不同點： 1 Only consistent backups can be used in restoring a database in NOARCHIVELOG?mode. 2 Media recovery is not possible because no archived redo logs exist. 恢復步驟如下： 1?startup force mount； 2?restore database； 3?recover database until cancel； 4?alter database open resetlogs； 舉例如下： 備份 SQL> archive log list; Database log modeNo Archive Mode Automatic archivalDisabled Archive destination/oracle/archive Oldest online log sequence1 Current log sequence2 RMAN> shutdown immediate; using target database control file instead of recovery catalog database closed database dismounted Oracle instance shut down RMAN> startup mount; connected to target database (not started) Oracle instance started database mounted Total System Global Area1252663296 bytes Fixed Size2226072 bytes Variable Size1006635112 bytes Database Buffers234881024 bytes Redo Buffers8921088 bytes RMAN> backup database; Starting backup at 2013-02-03 13:26:36 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=63 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=129 device type=DISK channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00004 name=/backup/users01.dbf input datafile file number=00003 name=/oracle/CRM/undotbs3.dbf input datafile file number=00006 name=/oracle/CRM/erp.dbf input datafile file number=00007 name=/oracle/CRM/user01.dbf channel ORA_DISK_1: starting piece 1 at 2013-02-03 13:26:38 channel ORA_DISK_2: starting full datafile backup set channel ORA_DISK_2: specifying datafile(s) in backup set input datafile file number=00001 name=/oracle/CRM/system01.dbf input datafile file number=00002 name=/oracle/CRM/sysaux01.dbf input datafile file number=00005 name=/oracle/CRM/pos.dbf channel ORA_DISK_2: starting piece 1 at 2013-02-03 13:26:39 channel ORA_DISK_1: finished piece 1 at 2013-02-03 13:29:36 piece handle=/backup/20130203_fro11v4d_1_1 tag=TAG20130203T132637 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:02:59 channel ORA_DISK_2: finished piece 1 at 2013-02-03 13:29:38 piece handle=/backup/20130203_fso11v4e_1_1 tag=TAG20130203T132637 comment=NONE channel ORA_DISK_2: backup set complete, elapsed time: 00:02:59 Finished backup at 2013-02-03 13:29:38 Starting Control File and SPFILE Autobackup at 2013-02-03 13:29:38 piece handle=/backup/c-3599153036-20130203-00 comment=NONE Finished Control File and SPFILE Autobackup at 2013-02-03 13:29:41 ? 恢復 RMAN> startup force mount; Oracle instance started database mounted Total System Global Area1252663296 bytes Fixed Size2226072 bytes Variable Size1006635112 bytes Database Buffers234881024 bytes Redo Buffers8921088 bytes RMAN> restore database; Starting restore at 2013-02-03 16:13:04 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=63 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=129 device type=DISK channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00003 to /oracle/CRM/undotbs3.dbf channel ORA_DISK_1: restoring datafile 00004 to /backup/users01.dbf channel ORA_DISK_1: restoring datafile 00006 to /oracle/CRM/erp.dbf channel ORA_DISK_1: restoring datafile 00007 to /oracle/CRM/user01.dbf channel ORA_DISK_1: reading from backup piece /backup/20130203_fro11v4d_1_1 channel ORA_DISK_2: starting datafile backup set restore channel ORA_DISK_2: specifying datafile(s) to restore from backup set channel ORA_DISK_2: restoring datafile 00001 to /oracle/CRM/system01.dbf channel ORA_DISK_2: restoring datafile 00002 to /oracle/CRM/sysaux01.dbf channel ORA_DISK_2: restoring datafile 00005 to /oracle/CRM/pos.dbf channel ORA_DISK_2: reading from backup piece /backup/20130203_fso11v4e_1_1 channel ORA_DISK_1: piece handle=/backup/20130203_fro11v4d_1_1 tag=TAG20130203T132637 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:04:09 channel ORA_DISK_2: piece handle=/backup/20130203_fso11v4e_1_1 tag=TAG20130203T132637 channel ORA_DISK_2: restored backup piece 1 channel ORA_DISK_2: restore complete, elapsed time: 00:04:11 Finished restore at 2013-02-03 16:17:17 RMAN> host; [oracle@oracle ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.2.0 Production on Sun Feb 3 16:27:12 2013 Copyright (c) 1982, 2010, Oracle.?All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> recover database until cancel; ORA-00279: change 2942346 generated at 02/03/2013 13:26:11 needed for thread 1 ORA-00289: suggestion : /oracle/archive/1_2_806264057.dbf ORA-00280: change 2942346 for thread 1 is in sequence #2 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} cancel Media recovery cancelled. SQL> alter database open resetlogs; Database altered. SQL> select group#,archived,sequence#,status from v$Log; GROUP# ARC?SEQUENCE# STATUS ---------- --- ---------- ---------------- 1 NO?????????? 1 CURRENT 2 YES????????? 0 UNUSED 3 YES????????? 0 UNUSED 4 YES????????? 0 UNUSED 二 非歸檔無備份，單個文件丟失造成數據庫無法打開 第一種方法： SQL> startup ; ORACLE instance started. Total System Global Area 1252663296 bytes Fixed Size2226072 bytes Variable Size1006635112 bytes Database Buffers234881024 bytes Redo Buffers8921088 bytes Database mounted. ORA-01157: cannot identify/lock data file 8 - see DBWR trace file ORA-01110: data file 8: '/oracle/CRM/test.dbf' SQL> alter database datafile 8 offline drop; Database altered. SQL> alter database open; Database altered. SQL> alter database create datafile '/oracle/CRM/test.dbf'; Database altered. SQL> select hxfil,fhscn,fhrba_seq from x$kcvfh; HXFIL FHSCN???????????? FHRBA_SEQ ---------- ---------------- ---------- 1 3078640????????????????? 37 2 3078640????????????????? 37 3 3078640????????????????? 37 4 3078640????????????????? 37 5 3078640????????????????? 37 6 3078640????????????????? 37 7 3078640????????????????? 37 8 3077425????????????????? 31 8 rows selected. SQL> select group#,archived,sequence#,status from v$log; GROUP# ARC?SEQUENCE# STATUS ---------- --- ---------- ---------------- 1 NO????????? 37 CURRENT 2 NO????????? 34 INACTIVE 3 NO????????? 35 INACTIVE 4 NO????????? 36 INACTIVE 由于是非歸檔則31、32、33號歸檔丟失，完全恢復已經不可能，只能執行基于取消的恢復，回退整個數據庫。 SQL> startup force mount; ORACLE instance started. Total System Global Area 1252663296 bytes Fixed Size2226072 bytes Variable Size1006635112 bytes Database Buffers234881024 bytes Redo Buffers8921088 bytes Database mounted. SQL> recover database until cancel; ORA-00279: change 3078640 generated at 02/20/2013 16:44:47 needed for thread 1 ORA-00289: suggestion : /oracle/archive/1_37_806501358.dbf ORA-00280: change 3078640 for thread 1 is in sequence #37 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} cancel ORA-10879: error signaled in parallel recovery slave 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: '/oracle/CRM/system01.dbf SQL> alter database open resetlogs; alter database open resetlogs * ERROR at line 1: ORA-01194: file 1 needs more recovery to be consistent ORA-01110: data file 1: '/oracle/CRM/system01.dbf' SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile; System altered. SQL> startup force mount; ORACLE instance started. Total System Global Area 1252663296 bytes Fixed Size2226072 bytes Variable Size1006635112 bytes Database Buffers234881024 bytes Redo Buffers8921088 bytes Database mounted. SQL> alter database open resetlogs; alter database open resetlogs * ERROR at line 1: ORA-01092: ORACLE instance terminated. Disconnection forced ORA-00600: internal error code, arguments: [2662], [0], [3078648], [0], [3078665], [4194432], [], [], [], [], [], [] Process ID: 5678 Session ID: 191 Serial number: 3 這里通過重啟規避ora-600 [2662]錯誤 SQL> startup; ORACLE instance started. Total System Global Area 1252663296 bytes Fixed Size2226072 bytes Variable Size1006635112 bytes Database Buffers234881024 bytes Redo Buffers8921088 bytes Database mounted. Database opened. 第二種方法 SQL> startup ORACLE instance started. Total System Global Area 1252663296 bytes Fixed Size2226072 bytes Variable Size1006635112 bytes Database Buffers234881024 bytes Redo Buffers8921088 bytes Database mounted. ORA-01157: cannot identify/lock data file 8 - see DBWR trace file ORA-01110: data file 8: '/oracle/CRM/test.dbf' SQL> alter database create datafile '/oracle/CRM/test.dbf'; Database altered. 注意，當我們用 alter database create datafile 創建數據文件時，數據文件頭部scn以及rba.seq號均取自于該數據文件創建時控制文件中記錄的scn和rba.seq號，如下： DATA FILE #8: name #14: /oracle/CRM/test.dbf Creation Checkpointed at scn:?0x0000.002d4053 02/03/2013 17:02:59 ?thread:1 rba:(0x2.20d.10) 0x2d4053準換為10進制為2965587 ?rba:(0x2.20d.10)則表示seq號為2.塊525.偏移量10 SQL> select hxfil,fhscn,fhrba_seq,fhrba_bno from x$kcvfh; HXFIL FHSCN???????????? FHRBA_SEQ?FHRBA_BNO ---------- ---------------- ---------- ---------- 1 2967064?????????????????? 9??????? 807 2 2967064?????????????????? 9??????? 807 3 2967064?????????????????? 9??????? 807 4 2967064?????????????????? 9??????? 807 5 2967064?????????????????? 9??????? 807 6 2967064?????????????????? 9??????? 807 7 2967064?????????????????? 9??????? 807 8 2965587?????????????????? 2??????? 525 8 rows selected. SQL> select group#,archived,sequence#,status from v$Log; GROUP# ARC?SEQUENCE# STATUS ---------- --- ---------- ---------------- 1 NO?????????? 9 CURRENT 4 NO?????????? 8 INACTIVE 3 NO?????????? 7 INACTIVE 2 NO?????????? 6 INACTIVE 注意：結合8號數據文件頭部rba.seq號為2以及數據庫當前日志的seq為9，那么當我們用命令recover datafile 8進行介質恢復時，數據庫需調用2、3、4、5號歸檔和聯機日志6、7、8、9來完成恢復。但事實是，數據庫處于非歸檔模式 2、3、4、5 號歸檔是不存在的，進行完全介質恢復已不可能。能夠做的就是進行基于取消的恢復，或者修改數據 文件頭部，使數據文件從9號聯機日志處開始介質恢復或者調整該數據文件頭部信息和其它數據文件保持一致，使介質恢復跳過歸檔和聯機日志。但不過不管怎樣，都會丟失數據。 三 非歸檔current組日志文件丟失 非歸檔模式 oracle當前聯機日志文件的處理通常有兩種處理方式如下： 1?啟動數據庫到mount狀態，用命令alter database clear logfile group n 對丟失的當前日志組進行初始化 2?執行基于取消的恢復 方法一：用命令alter database clear logfile group n 初始化當前日志組 1 數據庫的log_mode如下： SQL> archive log list; Database log modeNo Archive Mode Automatic archivalDisabled Archive destination/oracle/archive Oldest online log sequence5 Current log sequence8 2 當前日志組信息如下： SQL> select group#,archived,sequence#,status from v$log; GROUP# ARC?SEQUENCE# STATUS ---------- --- ---------- ---------------- 1 NO?????????? 5 INACTIVE 2 NO?????????? 6 INACTIVE 3 NO?????????? 7 INACTIVE 4 NO?????????? 8 CURRENT SQL> col member for a40 SQL> select group#,member from v$logfile; GROUP# MEMBER ---------- ---------------------------------------- 3 /oracle/CRM/redo03.log 2 /oracle/CRM/redo02.log 1 /oracle/CRM/redo01.log 4 /oracle/CRM/redo02.dbf ? 3 刪除當前日志組???? SQL> host [oracle@oracle ~]$ rm -rf /oracle/CRM/redo02.dbf 4 啟動報錯日志如下： Errors in file /oracle/app/diag/rdbms/crm/CRM/trace/CRM_m000_5259.trc: ORA-00313: open failed for members of log group 4 of thread 1 ORA-00312: online log 4 thread 1: '/oracle/CRM/redo02.dbf' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 Dumping diagnostic data in directory=[cdmp_20130201163228], requested by (instance=1, osid=5257), summary=[abnormal instance termination]. USER (ospid: 5257): terminating the instance due to error 313 Instance terminated by USER, pid = 5257 5恢復過程 SQL> select group#,archived,sequence#,status from v$log; GROUP# ARC?SEQUENCE# STATUS ---------- --- ---------- ---------------- 1 NO?????????? 5 INACTIVE 4 NO?????????? 8 CURRENT 3 NO?????????? 7 INACTIVE 2 NO?????????? 6 INACTIVE SQL> alter database clear logfile group 4; Database altered. SQL> alter database open; Database altered. 方法二：執行基于取消的恢復 1 數據庫當前日志狀態如下： SQL> select group#,archived,sequence#,status from v$log; GROUP# ARC?SEQUENCE# STATUS ---------- --- ---------- ---------------- 1 NO?????????? 9 INACTIVE 2 NO?????? ????6 INACTIVE 3 NO?????????? 7 INACTIVE 4 NO????????? 10 CURRENT SQL> col member for a40 SQL> select group#,member from v$logfile; GROUP# MEMBER ---------- ---------------------------------------- 3 /oracle/CRM/redo03.log 2 /oracle/CRM/redo02.log 1 /oracle/CRM/redo01.log 4 /oracle/CRM/redo02.dbf SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. 2 刪除當前聯機日志文件 [oracle@oracle ~]$ rm -rf /oracle/CRM/redo02.dbf 3 恢復過程如下： SQL> startup mount; ORACLE instance started. Total System Global Area 1252663296 bytes Fixed Size2226072 bytes Variable Size1006635112 bytes Database Buffers234881024 bytes Redo Buffers8921088 bytes Database mounted. SQL> recover database until cancel; Media recovery complete. SQL> alter database open resetlogs; Database altered. SQL> select group#,archived,sequence#,status from v$log; GROUP# ARC?SEQUENCE# STATUS ---------- --- ---------- ---------------- 1 NO?????????? 1 CURRENT 2 YES????????? 0 UNUSED 3 YES????????? 0 UNUSED 4 YES????????? 0 UNUSED 需要注意的是對于非歸檔數據庫當前日志損壞的處理，我們可以直接對當前日志組用命令alter database clear logfile group n 進行處理 這點和歸檔模式下還是有區別的。 總結： 個人感覺如果數據庫沒更新，只是做查詢用，數據庫為非歸檔，做一次全備份即可。

總結

以上是生活随笔為你收集整理的非归档下oracle的备份和恢复的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。