Oracle聯(lián)機(jī)日志出現(xiàn)錯(cuò)誤ORA-16038 ORA-19809 ORA-00312 解決

出現(xiàn)ORA-16038 ORA-19809 ORA-00312 Oracle 10g默認(rèn)的歸檔日志存放在flash_recovery_area，而默認(rèn)安裝的時(shí)候該區(qū)間選擇了2G大小當(dāng)歸檔日志超過(guò)2G的時(shí)候，空間不足，于是無(wú)法歸檔，當(dāng)時(shí)手工刪除歸檔日志文件，Oracle是無(wú)法識(shí)別空間是否釋放, 導(dǎo)致數(shù)據(jù)庫(kù)無(wú)法啟動(dòng)。

解決辦法：
-
 

1、mount數(shù)據(jù)庫(kù)

sql> startup mount (注意，這時(shí)候還會(huì)報(bào)錯(cuò)，但是可以更改系統(tǒng)參數(shù))

2、 更改系統(tǒng)參數(shù)

sql> alter system set db_recovery_file_dest_size=2g scope=spfile;

參數(shù)db_recovery_file_dest_size的值根據(jù)磁盤(pán)剩余空間大小和實(shí)際需要來(lái)設(shè)置，沒(méi)有定論

3、打開(kāi)數(shù)據(jù)庫(kù)

sql> alter database open 不會(huì)成功的

4、刪除失效歸檔日志.

RMAN> connect target /

RMAN> crosscheck archivelog all;

RMAN> delete expired archivelog all;

RMAN> exit

5、重新啟動(dòng)數(shù)據(jù)庫(kù)

sql> shutdown immediate

sql> startup

-----------------另一個(gè)版本的解決方法-----------------

Oracle出現(xiàn)ORA-16038，ORA-19809，ORA-00312問(wèn)題的解決方法www.linuxidc.com

1：SQL> startup Oracle 例程已經(jīng)啟動(dòng)。

  數(shù)據(jù)庫(kù)裝載完畢。

ORA-16038: 日志 3 序列號(hào) 46 無(wú)法歸檔

ORA-19809: 超出了恢復(fù)文件數(shù)的限制

ORA-00312: 聯(lián)機(jī)日志 3 線程 1: 'D:OraclePRODUCT10.2.0ORADATAORCLREDO03.LOG'

2：SQL> alter database clear unarchived logfile 'D:OraclePRODUCT10.2.0ORADATAO RCLREDO03.LOG';

數(shù)據(jù)庫(kù)已更改。

3：SQL> shutdown immediate

ORA-01109: 數(shù)據(jù)庫(kù)未打開(kāi) 已經(jīng)卸載數(shù)據(jù)庫(kù)。

Oracle 例程已經(jīng)關(guān)閉。

4：SQL> startup

Oracle 例程已經(jīng)啟動(dòng)。

數(shù)據(jù)庫(kù)裝載完畢。

數(shù)據(jù)庫(kù)已經(jīng)打開(kāi)。

無(wú)法歸檔錯(cuò)誤主要是因?yàn)閿?shù)據(jù)庫(kù)不正常關(guān)閉,SQL語(yǔ)句出錯(cuò)造成數(shù)據(jù)線程停止所因起。

-----------------另一個(gè)版本的解決方法-----------------

還有就是設(shè)置歸檔日志的大小，設(shè)置為10G的時(shí)候，錯(cuò)誤可解決，www.linuxidc.com報(bào)錯(cuò)的時(shí)候系統(tǒng)ALERT日志里也提示有幾種參考方法如下工作日志所示：

1：監(jiān)控郵件監(jiān)控到ALERT日志中的錯(cuò)誤如下：< ORA-19809: limit exceeded for recovery files
< ORA-19804: cannot reclaim 43570176 bytes disk space from 2147483648 limit
< ORA-19815: WARNING: db_recovery_file_dest_size of 2147483648 bytes is 100.00% used, and has 0 remaining bytes available.

2：查看ALERT日志詳細(xì)錯(cuò)誤如下ORA-19815: WARNING: db_recovery_file_dest_size of 2147483648 bytes is 100.00% used, and has 0 remaining bytes available.
Tue May 31 14:57:42 2011
************************************************************************
You have following choices to free up space from flash recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
   then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
   BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
   reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
   system command was used to delete files, then use RMAN CROSSCHECK and
   DELETE EXPIRED commands.
************************************************************************
Tue May 31 14:57:42 2011
Errors in file /usr/app/db-server/ora_base/admin/gis/bdump/gis_arc1_3602.trc:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 43570176 bytes disk space from 2147483648 limit
ARC1: Error 19809 Creating archive log file to '/usr/app/db-server/ora_base/flash_recovery_area/GIS/archivelog/2011_05_31/o1_mf_1_44477_0_.arc'
Tue May 31 15:17:20 2011
Thread 1 advanced to log sequence 44479
  Current log# 3 seq# 44479 mem# 0: /usr/app/db-server/ora_base/oradata/gis/redo03.log
Tue May 31 15:17:20 2011
Errors in file /usr/app/db-server/ora_base/admin/gis/bdump/gis_arc0_3600.trc:
很明顯的錯(cuò)誤信息，說(shuō)明歸檔空間滿了

3：查看歸檔參數(shù)
SQL> show parameter rec

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
buffer_pool_recycle                  string
control_file_record_keep_time        integer     7
db_recovery_file_dest                string      /usr/app/db-server/ora_base/fl
                                                 ash_recovery_area
db_recovery_file_dest_size           big integer 2G
db_recycle_cache_size                big integer 0
ldap_directory_access                string      NONE
recovery_parallelism                 integer     0
recyclebin                           string      on
use_indirect_data_buffers            boolean     FALSE

4：查看使用率SQL> select * from v$flash_recovery_area_usage;
FILE_TYPE    PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE                   0                         0               0
ONLINELOG                     0                         0               0
ARCHIVELOG                99.99                         0               0
BACKUPPIECE                   0                         0               0
IMAGECOPY                     0                         0               0
FLASHBACKLOG                  0                         0               0

5：變更歸檔大小為10GSQL> alter system set db_recovery_file_dest_size=10G scope=both;
System altered.

6：再次查看使用率SQL> select * from v$flash_recovery_area_usage;
FILE_TYPE    PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE                   0                         0               0
ONLINELOG                     0                         0               0
ARCHIVELOG                19.93                         0              49
BACKUPPIECE                   0                         0               0
IMAGECOPY                     0                         0               0
FLASHBACKLOG                  0                         0               0

6 rows selected.

服務(wù)器租用/服務(wù)器托管中國(guó)五強(qiáng)！虛擬主機(jī)域名注冊(cè)頂級(jí)提供商！15年品質(zhì)保障！--億恩科技[ENKJ.COM]