資料庫處于非歸檔狀态，其聯機日志循環覆寫使用，這意味着如果資料庫需要進行媒體恢複應用相關歸檔，又由于非歸檔導緻歸檔檔案不存在，那麼此時資料庫隻能執行基于取消的恢複，回退整個資料庫到上一次全備份狀态。或者修改資料檔案頭部資訊，來規避缺失歸檔。但不管怎麼樣都會丢失資料。
下面介紹非歸檔模式下幾種常見故障處理方法。
一 非歸檔下的常用備份和恢複方法：
如何備份
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 mode No Archive Mode
Automatic archival Disabled
Archive destination /oracle/archive
Oldest online log sequence 1
Current log sequence 2
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 Area 1252663296 bytes
Fixed Size 2226072 bytes
Variable Size 1006635112 bytes
Database Buffers 234881024 bytes
Redo Buffers 8921088 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;
RMAN> restore database;
Starting restore at 2013-02-03 16:13:04
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 Size 2226072 bytes
Variable Size 1006635112 bytes
Database Buffers 234881024 bytes
Redo Buffers 8921088 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;
SQL> alter database open;
SQL> alter database create datafile '/oracle/CRM/test.dbf';
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;
1 NO 37 CURRENT
2 NO 34 INACTIVE
3 NO 35 INACTIVE
4 NO 36 INACTIVE
由于是非歸檔則31、32、33号歸檔丢失，完全恢複已經不可能，隻能執行基于取消的恢複，回退整個資料庫。
SQL> startup force mount;
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
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
alter database open resetlogs
*
ERROR at line 1:
ORA-01110: data file 1: '/oracle/CRM/system01.dbf'
SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile;
System altered.
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;
Database opened.
第二種方法
SQL> startup
注意，當我們用 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
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如下：
Oldest online log sequence 5
Current log sequence 8
2 目前日志組資訊如下：
1 NO 5 INACTIVE
2 NO 6 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> alter database clear logfile group 4;
方法二：執行基于取消的恢複
1 資料庫目前日志狀态如下：
1 NO 9 INACTIVE
2 NO 6 INACTIVE
4 NO 10 CURRENT
SQL> col member for a40
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
2 删除目前聯機日志檔案
3 恢複過程如下：
SQL> startup mount;
Media recovery complete.
3 YES 0 UNUSED
需要注意的是對于非歸檔資料庫目前日志損壞的處理，我們可以直接對目前日志組用指令alter database clear logfile group n 進行處理 這點和歸檔模式下還是有差別的。
總結：
個人感覺如果資料庫沒更新，隻是做查詢用，資料庫為非歸檔，做一次全備份即可。
本文轉自 zhangxuwl 51CTO部落格，原文連結：http://blog.51cto.com/jiujian/1139304，如需轉載請自行聯系原作者
![【MySQL資料庫】資料庫索引事務1.索引2.事務[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)