常常会遇到下面的报错信息
欢迎大家加入ORACLE超级群：17115662 免费解决各种ORACLE问题,以后BLOG将迁移到http://www.htz.pw
|
ARCH: Error 19809 Creating archive log file to ‘/oracle/app/oracle/fast_recovery_area/ORCL1124/archivelog/2014_06_05/o1_mf_1_113_%u_.arc’ ARC1: Error 19809 Creating archive log file to ‘/oracle/app/oracle/fast_recovery_area/ORCL1124/archivelog/2014_06_05/o1_mf_1_114_%u_.arc’ Errors in file /oracle/app/oracle/diag/rdbms/orcl1124/orcl1124/trace/orcl1124_ora_22611.trc: ORA-16038: log 2 sequence# 113 cannot be archived ORA-19809: limit exceeded for recovery files ORA-00312: online log 2 thread 1: ‘/oracle/app/oracle/oradata/orcl1124/redo02.log’ ARCH: Archival stopped, error occurred. Will continue retrying ORACLE Instance orcl1124 – Archival Error ORA-16038: log 3 sequence# 114 cannot be archived ORA-19809: limit exceeded for recovery files ORA-00312: online log 3 thread 1: ‘/oracle/app/oracle/oradata/orcl1124/redo03.log’ System state dump requested by (instance=1, osid=22611), summary=[abnormal instance termination]. System State dumped to trace file /oracle/app/oracle/diag/rdbms/orcl1124/orcl1124/trace/orcl1124_diag_22544_20140605180150.trc Dumping diagnostic data in directory=[cdmp_20140605180150], requested by (instance=1, osid=22611), summary=[abnormal instance termination]. USER (ospid: 22611): terminating the instance due to error 16038 Instance terminated by USER, pid = 22611 |
原因很简单：归档目录满了，解决的方法也很多的
1，如果归档存放在FRA区，修改一个FRA区的大小就可以了，前提是FRA目录上还有剩余的空间。
2，使用单独的归档目录，扩一下文件系统或者将归档路径修改到其它的地方就可以了。
3，下面我们测试另一种方法
1，启动数据库报错
|
[oracle@www.htz.pw oradata]$sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Jun 5 18:01:30 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
www.htz.pw > startup ORACLE instance started.
Total System Global Area 379965440 bytes Fixed Size 2253464 bytes Variable Size 171969896 bytes Database Buffers 201326592 bytes Redo Buffers 4415488 bytes Database mounted. ORA-03113: end-of-file on communication channel Process ID: 22611 Session ID: 1 Serial number: 5 |
2，alert中报下面的错误
|
ORA-19815: WARNING: db_recovery_file_dest_size of 4385144832 bytes is 100.00% used, and has 0 remaining bytes available. ************************************************************************ You have following choices to free up space from 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. ************************************************************************ ARCH: Error 19809 Creating archive log file to ‘/oracle/app/oracle/fast_recovery_area/ORCL1124/archivelog/2014_06_05/o1_mf_1_113_%u_.arc’ ARC1: Error 19809 Creating archive log file to ‘/oracle/app/oracle/fast_recovery_area/ORCL1124/archivelog/2014_06_05/o1_mf_1_114_%u_.arc’ Errors in file /oracle/app/oracle/diag/rdbms/orcl1124/orcl1124/trace/orcl1124_ora_22611.trc: ORA-16038: log 2 sequence# 113 cannot be archived ORA-19809: limit exceeded for recovery files ORA-00312: online log 2 thread 1: ‘/oracle/app/oracle/oradata/orcl1124/redo02.log’ ARCH: Archival stopped, error occurred. Will continue retrying ORACLE Instance orcl1124 – Archival Error ORA-16038: log 3 sequence# 114 cannot be archived ORA-19809: limit exceeded for recovery files ORA-00312: online log 3 thread 1: ‘/oracle/app/oracle/oradata/orcl1124/redo03.log’ System state dump requested by (instance=1, osid=22611), summary=[abnormal instance termination]. System State dumped to trace file /oracle/app/oracle/diag/rdbms/orcl1124/orcl1124/trace/orcl1124_diag_22544_20140605180150.trc Dumping diagnostic data in directory=[cdmp_20140605180150], requested by (instance=1, osid=22611), summary=[abnormal instance termination]. USER (ospid: 22611): terminating the instance due to error 16038 Instance terminated by USER, pid = 22611 |
3，下面将归档库更改为非归档就可以正常启动了
我做的事情增加了resetlog的方式，在生产环境这里可以使用noresetlogs的方式就可以了，因为我们只是更改一下归档模式的。
|
CREATE CONTROLFILE REUSE DATABASE "ORCL1124" RESETLOGS NOARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 ‘/oracle/app/oracle/oradata/orcl1124/redo01.log’ SIZE 50M BLOCKSIZE 512, GROUP 2 ‘/oracle/app/oracle/oradata/orcl1124/redo02.log’ SIZE 50M BLOCKSIZE 512, GROUP 3 ‘/oracle/app/oracle/oradata/orcl1124/redo03.log’ SIZE 50M BLOCKSIZE 512 — STANDBY LOGFILE DATAFILE ‘/oracle/app/oracle/oradata/orcl1124/system01.dbf’, ‘/oracle/app/oracle/oradata/orcl1124/sysaux01.dbf’, ‘/oracle/app/oracle/oradata/orcl1124/undotbs01.dbf’, ‘/oracle/app/oracle/oradata/orcl1124/users01.dbf’, ‘/oracle/app/oracle/oradata/orcl1124/htz01.dbf’, ‘/oracle/app/oracle/oradata/orcl1124/undotbs02.dbf’ CHARACTER SET ZHS16GBK ;
www.htz.pw > recover database using backup controlfile until cancel; ORA-00279: change 2795841 generated at 06/05/2014 17:52:39 needed for thread 1 ORA-00289: suggestion : /oracle/app/oracle/fast_recovery_area/ORCL1124/archivelog/2014_06_05/o1_mf_1_115 _%u_.arc ORA-00280: change 2795841 for thread 1 is in sequence #115
Specify log: {<RET>=suggested | filename | AUTO | CANCEL} /oracle/app/oracle/oradata/orcl1124/redo03.log ORA-00310: archived log contains sequence 114; sequence 115 required ORA-00334: archived log: ‘/oracle/app/oracle/oradata/orcl1124/redo03.log’
www.htz.pw > recover database using backup controlfile until cancel; ORA-00279: change 2795841 generated at 06/05/2014 17:52:39 needed for thread 1 ORA-00289: suggestion : /oracle/app/oracle/fast_recovery_area/ORCL1124/archivelog/2014_06_05/o1_mf_1_115 _%u_.arc ORA-00280: change 2795841 for thread 1 is in sequence #115
Specify log: {<RET>=suggested | filename | AUTO | CANCEL} /oracle/app/oracle/oradata/orcl1124/redo01.log Log applied. Media recovery complete. 这里一定要手动输入日志路径，不然可以会丢失数据，或者遇到其它的报错的。
www.htz.pw > alter database open resetlogs;
Database altered.
www.htz.pw > archive log list; Database log mode No Archive Mode Automatic archival Disabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 1 Current log sequence 1 |
Database Crashed With ORA-19815 ORA-19809 ORA-16038 (文档 ID 829254.1) 转到底部转到底部 修改时间:2014-1-23类型:PROBLEM 为此文档评级 通过电子邮件发送此文档的链接 在新窗口中打开文档 可打印页In this DocumentSymptomsCauseSolutionReferencesAPPLIES TO:Oracle Database – Enterprise Edition – Version 10.2.0.1 to 11.2.0.2.0 [Release 10.2 to 11.2]Information in this document applies to any platform.***Checked for relevance on 08-Mar-2013***SYMPTOMSInstance terminated due to error 16038 as its not able to archive the log in FRAORA-19815: WARNING: db_recovery_file_dest_size of 99614720000 bytes is 100.00% used, and has 0 remaining bytes available. Sat Mar 8 00:57:07 2008 ************************************************************************ 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. ************************************************************************ Sat Mar 8 00:57:07 2008 Errors in file /usr/oracle/admin/ORAPTCMK/bdump/oraptcmk1_arc0_623454.trc: ORA-19809: limit exceeded for recovery files ORA-19804: cannot reclaim 308281344 bytes disk space from 99614720000 limit Sat Mar 8 00:57:07 2008 ARC0: Error 19809 Creating archive log file to ‘+DATA’ Sat Mar 8 00:57:07 2008 Errors in file /usr/oracle/admin/ORAPTCMK/udump/oraptcmk1_ora_680508.trc: ORA-16038: log 17 sequence# 34003 cannot be archived ORA-19809: limit exceeded for recovery files ORA-00312: online log 17 thread 1: ‘+DATA/oraptcmk/onlinelog/redolog171.log’ ORA-00312: online log 17 thread 1: ‘+FLRC/oraptcmk/onlinelog/redolog172.log’ Sat Mar 8 00:57:07 2008 USER: terminating instance due to error 16038CAUSEDefault archive log destination was set to Flash Recovery Area and FRA is 100% used. There is no space to create additional archive log.Similar situation also occur if the database is up and running and archive log’s destination for FRA is full then the database will hang.Other similar issue because of archiving is stuck because of FRA space pressure are1. Database Hangs2. Users not able to connect to database3. Not able to open the database 4. FRA space related error in the alert.log file ( ORA-19809 )SOLUTIONMake more space in Flash Recovery Area or change the archivelog destination to outside Flash Recovery Area.By default Archive log are created in FRA if no specific log_archive_dest_n parameter was set and Flash Recovery Area is enabled.SQL> show parameter db_recovery_file_dest NAME VALUE ———————- ————————– db_recovery_file_dest E:oracleproduct10.2.0flash_recovery_area db_recovery_file_dest_size 2GSQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 174 Next log sequence to archive 176 Current log sequence 176If you are using RMAN for the database backup then check the space distribution in FRAfor exampleSQL>select file_type, percent_space_used as used,percent_space_reclaimable as reclaimable, number_of_files as “number” from v$flash_recovery_area_usage; FILE_TYPE USED RECLAIMABLE number ———— ———- ———– ———- CONTROLFILE 0 0 0 ONLINELOG 0 0 0 ARCHIVELOG 89.94 0 53 BACKUPPIECE 9.51 0 11 IMAGECOPY 0 0 0 FLASHBACKLOG 0 0 0In the Above example almost all the space are used by Archivelogs and backup pieces and there is no space to reclaim. In this type of case you cana) Increase the FRA size b) Take backup backup of the archivelogs to different location c) If tape backup is a option then take backup of FRA to tape d) Change archivelogs destination out of FRA d) Delete archivelogs to make more space. ( should be the last option) and in case of standby database make sure those logs are already applied to standby Usually archiving is configured to FRA for automatic management of archivelog files. This works well if you are using a standby configuration or using RMAN for backups so that there is a basis for archives to get obsolete and be cleaned up automatically from FRA. If you do not want to take advantage of automatic space management in FRA, you can set any non FRA location for the archivelogs.for exampleSet an archivelog destination SQL> alter system set log_archive_dest_1=’LOCATION=E:oracleproduct’ scope=both ; Unset the default setting for FRA SQL> alter system set log_archive_dest_10=” scope=both; + If this is an database version less than 11.2, then due to an unpublished Bug# 6964464 (fixed in 11.2), stuck archiver causes the instance to crash which is not the normal behaviour. Normally, the instance would hang until the stuck archiver is freed from errors. REFERENCESNOTE:829755.1 – Space issue in Fast / Flash Recovery Area – FRA Full
RMAN backup to Flash Recovery Area failed with ORA-19809 (文档 ID 831225.1) 转到底部转到底部 修改时间:2013-11-6类型:PROBLEM 为此文档评级 通过电子邮件发送此文档的链接 在新窗口中打开文档 可打印页In this DocumentSymptomsCauseSolutionReferencesAPPLIES TO:Oracle Database – Enterprise Edition – Version 10.2.0.1 to 11.2.0.2.0 [Release 10.2 to 11.2]Information in this document applies to any platform.***Checked for relevance on 06-Nov-2013***SYMPTOMSRMAN backup of the database to Flash Recovery Area ( FRA ) failed with ORA-19809: limit exceeded for recovery files NOTE : since 11gRelease2 ‘Flash Recovery Area’ has been renamed to ‘Fast Recovery Area’Error——RMAN-03009: failure of backup command on ORA_DISK_4 channel at 12/18/2007 09:53: 44 ORA-19809: limit exceeded for recovery files ORA-19804: cannot reclaim 1561238528 bytes disk space from 21474836480 limitCAUSEBy default RMAN backup goes to Flash Recovery Area if it is set . Whenever there is space pressure, the RDBMS will try to delete obsolete backups in FRA in order to make more space. In case its not able to make more space and required space exceeds the limit of flash recovery area then its fails with those error message.Its because of the FRA’s space limit. Flash Recovery Area has a defined size when the required space exceed the limit of the FRA those errors are expected. SOLUTION Make more space in FRA by either increasing the size of FRA or by deleting unwanted backups or by taking existing FRA contents backup to tape.- Increase the size of FRA SQL> alter system set db_recovery_file_dest_size=xG SCOPE=BOTH; — Higher_size – Take a backup of FRA to tapeRMAN> BACKUP RECOVERY AREA; You can also delete the unwanted backups. Its recommended to delete it via RMAN only. Please note in case you are deleting manually then you need to execute a CROSSCHECK and DELETE EXPIRED in order to get those information reflected in FRA size.For more on FRA space issue refer Note 829755.1 : Space issue in Flash Recovery Area( FRA )REFERENCESNOTE:305648.1 – What is a Flash Recovery Area and how to configure it ?NOTE:829755.1 – Space issue in Flash Recovery Area( FRA )