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

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

解決辦法：
-
 

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

sql> startup mount (注意，這時候還會報錯，但是可以更改系統(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ù)磁盤剩余空間大小和實際需要來設置，沒有定論

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

sql> alter database open 不會成功的

4、刪除失效歸檔日志.

RMAN> connect target /

RMAN> crosscheck archivelog all;

RMAN> delete expired archivelog all;

RMAN> exit

5、重新啟動數(shù)據(jù)庫

sql> shutdown immediate

sql> startup

-----------------另一個版本的解決方法-----------------

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

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

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

ORA-16038: 日志 3 序列號 46 無法歸檔

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

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

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

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

3：SQL> shutdown immediate

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

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

4：SQL> startup

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

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

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

無法歸檔錯誤主要是因為數(shù)據(jù)庫不正常關閉,SQL語句出錯造成數(shù)據(jù)線程停止所因起。

-----------------另一個版本的解決方法-----------------

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

1：監(jiān)控郵件監(jiān)控到ALERT日志中的錯誤如下：< 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日志詳細錯誤如下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:
很明顯的錯誤信息，說明歸檔空間滿了

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.

服務器租用/服務器托管中國五強！虛擬主機域名注冊頂級提供商！15年品質保障！--億恩科技[ENKJ.COM]