Oracle聯機日志出現錯誤ORA-16038 ORA-19809 ORA-00312 解決

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

解決辦法：
-
 

1、mount數據庫

sql> startup mount (注意，這時候還會報錯，但是可以更改系統參數)

2、 更改系統參數

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

參數db_recovery_file_dest_size的值根據磁盤剩余空間大小和實際需要來設置，沒有定論

3、打開數據庫

sql> alter database open 不會成功的

4、刪除失效歸檔日志.

RMAN> connect target /

RMAN> crosscheck archivelog all;

RMAN> delete expired archivelog all;

RMAN> exit

5、重新啟動數據庫

sql> shutdown immediate

sql> startup

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

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

1：SQL> startup Oracle 例程已經啟動。

  數據庫裝載完畢。

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

ORA-19809: 超出了恢復文件數的限制

ORA-00312: 聯機日志 3 線程 1: 'D:OraclePRODUCT10.2.0ORADATAORCLREDO03.LOG'

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

數據庫已更改。

3：SQL> shutdown immediate

ORA-01109: 數據庫未打開 已經卸載數據庫。

Oracle 例程已經關閉。

4：SQL> startup

Oracle 例程已經啟動。

數據庫裝載完畢。

數據庫已經打開。

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

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

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

1：監控郵件監控到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：查看歸檔參數
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]