if following error occurred on Oracle database or database hanged due to this error
Errors in file /opt/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_arc3_1202.trc:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 44131328 bytes disk space from 4070572032 limit
ARC3: Error 19809 Creating archive log file to '/opt/app/oracle/flash_recovery_area/ORCL/archivelog/2011_04_29/o1_mf_1_330_%u_.arc'
check space is fill !!!!!!!!!
set lines 100
col name format a60
select name
, floor(space_limit / 1024 / 1024) "Size MB"
, ceil(space_used / 1024 / 1024) "Used MB"
from v$recovery_file_dest
order by name
/
oracle@apt-amd-02:~> id
uid=1001(oracle) gid=1000(oinstall) groups=1000(oinstall)
oracle@apt-amd-02:~> rman
Recovery Manager: Release 11.2.0.1.0 - Production on Fri Apr 29 06:56:30 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
RMAN> connect target /
connected to target database: ORCL (DBID=1267852645)
RMAN> delete force archivelog until time 'sysdate -1'; ---- delete some archive log file
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=50 device type=DISK
List of Archived Log Copies for database with db_unique_name ORCL
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
91 1 330 A 13-APR-11
Name: /opt/app/oracle/flash_recovery_area/ORCL/archivelog/2011_04_29/o1_mf_1_330_6vnqzs5c_.arc
92 1 331 A 13-APR-11
Name: /opt/app/oracle/flash_recovery_area/ORCL/archivelog/2011_04_29/o1_mf_1_331_6vnr0q87_.arc
93 1 332 A 13-APR-11
Name: /opt/app/oracle/flash_recovery_area/ORCL/archivelog/2011_04_29/o1_mf_1_332_6vnr0x7c_.arc
Do you really want to delete the above objects (enter YES or NO)? yes
deleted archived log
archived log file name=/opt/app/oracle/flash_recovery_area/ORCL/archivelog/2011_04_29/o1_mf_1_330_6vnqzs5c_.arc RECID=91 STAMP=749717522
deleted archived log
archived log file name=/opt/app/oracle/flash_recovery_area/ORCL/archivelog/2011_04_29/o1_mf_1_331_6vnr0q87_.arc RECID=92 STAMP=749717587
deleted archived log
archived log file name=/opt/app/oracle/flash_recovery_area/ORCL/archivelog/2011_04_29/o1_mf_1_332_6vnr0x7c_.arc RECID=93 STAMP=749717596
Deleted 3 objects
check all the file deleted ....
RMAN> delete force archivelog until time 'sysdate -1';
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=50 device type=DISK
specification does not match any archived log in the repository
========================================================================================================================================
error
************************************************************************
2025-01-09T03:30:43.218396-05:00
Errors in file /u01/app/oracle/diag/rdbms/ugaryd/ugaryd1/trace/ugaryd1_tt00_8810.trc:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 105906176 bytes disk space from 1073741824 bytes limit
2025-01-09T03:31:43.253212-05:00
Errors in file /u01/app/oracle/diag/rdbms/ugaryd/ugaryd1/trace/ugaryd1_tt00_8810.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 1073741824 bytes is 100.00% used, and has 0 remaining bytes available.
2025-01-09T03:31:43.253469-05:00
************************************************************************
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.
************************************************************************
cat /u01/app/oracle/diag/rdbms/ugaryd/ugaryd1/trace/ugaryd1_tt00_8810.trc|more
*** 2025-01-09T02:04:41.313767-05:00
*** SESSION ID:(200.33043) 2025-01-09T02:04:41.313824-05:00
*** CLIENT ID:() 2025-01-09T02:04:41.313835-05:00
*** SERVICE NAME:(SYS$BACKGROUND) 2025-01-09T02:04:41.313841-05:00
*** MODULE NAME:() 2025-01-09T02:04:41.313847-05:00
*** ACTION NAME:() 2025-01-09T02:04:41.313853-05:00
*** CLIENT DRIVER:() 2025-01-09T02:04:41.313858-05:00
ORA-19815: WARNING: db_recovery_file_dest_size of 1073741824 bytes is 100.00% used, and has 0 remaining bytes available.
2025-01-09 02:04:41.315*:KRA:kra.c@2332:kraalert(): ************************************************************************
2025-01-09 02:04:41.315*:KRA:kra.c@2334:kraalert(): You have following choices to free up space from recovery area:
2025-01-09 02:04:41.315*:KRA:kra.c@2336:kraalert(): 1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
2025-01-09 02:04:41.315*:KRA:kra.c@2338:kraalert(): then consider changing RMAN ARCHIVELOG DELETION POLICY.
2025-01-09 02:04:41.315*:KRA:kra.c@2340:kraalert(): 2. Back up files to tertiary device such as tape using RMAN
2025-01-09 02:04:41.315*:KRA:kra.c@2342:kraalert(): BACKUP RECOVERY AREA command.
2025-01-09 02:04:41.315*:KRA:kra.c@2345:kraalert(): 3. Add disk space and increase db_recovery_file_dest_size parameter to
2025-01-09 02:04:41.315*:KRA:kra.c@2347:kraalert(): reflect the new space.
2025-01-09 02:04:41.315*:KRA:kra.c@2349:kraalert(): 4. Delete unnecessary files using RMAN DELETE command. If an operating
2025-01-09 02:04:41.315*:KRA:kra.c@2351:kraalert(): system command was used to delete files, then use RMAN CROSSCHECK and
2025-01-09 02:04:41.315*:KRA:kra.c@2353:kraalert(): DELETE EXPIRED commands.
2025-01-09 02:04:41.315*:KRA:kra.c@2355:kraalert(): ************************************************************************
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 105906176 bytes disk space from 1073741824 bytes limit
krsi_dest_check: Exception when reserving disk space for OMF, or getting file name or creating test file
krsd_check_stuck_arch: inactive mandatory LAD:1
check space
set linesize 300
col FILE_TYPE for a30
col PERCENT_SPACE_USED for 999.99 heading 'PERCENT_SPACE_USED%'
SELECT NVL(FRAU.FILE_TYPE, 'Total:') FILE_TYPE,
SUM(ROUND(FRAU.PERCENT_SPACE_USED / 100 * RFD.SPACE_LIMIT / 1024 / 1024 / 1024,3)) USED_GB,
SUM(FRAU.PERCENT_SPACE_USED) PERCENT_SPACE_USED,
SUM(FRAU.PERCENT_SPACE_RECLAIMABLE) PERCENT_SPACE_RECLAIMABLE,
SUM(ROUND(FRAU.PERCENT_SPACE_RECLAIMABLE / 100 * RFD.SPACE_LIMIT / 1024 / 1024 / 1024,3)) RECLAIM_GB,
SUM(FRAU.NUMBER_OF_FILES) NUMBER_OF_FILES,
SUM(FRAU.PERCENT_SPACE_USED) PERCENT_SPACE_USED
FROM V$FLASH_RECOVERY_AREA_USAGE FRAU, V$RECOVERY_FILE_DEST RFD
GROUP BY ROLLUP(FILE_TYPE);
FILE_TYPE USED PERCENT_SPACE_USED% PERCENT_SPACE_RECLAIMABLE RECLAIM NUMBER_OF_FILE PERCENT_SPACE_USED%
------------------------------ ---------- ------------------- ------------------------- ---------- -------------- -------------------
ARCHIVED LOG 1942M 189.65 0 0 27 189.65
AUXILIARY DATAFILE COPY 0 .00 0 0 0 .00
BACKUP PIECE 0 .00 0 0 0 .00
CONTROL FILE 13M 1.27 0 0 1 1.27
FLASHBACK LOG 0 .00 0 0 0 .00
FOREIGN ARCHIVED LOG 0 .00 0 0 0 .00
IMAGE COPY 0 .00 0 0 0 .00
REDO LOG 0 .00 0 0 0 .00
Total: 1955M 190.92 0 0 28 190.92
9 rows selected.
SQL> show parameter DB_RECOVERY_FILE_DEST_SIZE
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size big integer 1G
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 10G scope=both sid='*';
alter system set db_recovery_file_dest='+FRA' scope=both sid='*';
alter system switch logfile;
delete force archivelog all completed before 'sysdate-1/24';
set linesize 300
col FILE_TYPE for a30
col PERCENT_SPACE_USED for 999.99 heading 'PERCENT_SPACE_USED%'
col USED_GB for 999.99
col "PERCENT_SPACE_USED%" for 999.99
col 'PERCENT_SPACE_RECLAIMABLE%' for 999.99
col RECLAIM for a10
col NUMBER_OF_FILES for a14
col USED for a10
SELECT NVL(FRAU.FILE_TYPE, 'Total:') FILE_TYPE,
dbms_xplan.format_size(SUM(ROUND(FRAU.PERCENT_SPACE_USED / 100 * RFD.SPACE_LIMIT ))) USED,
SUM(FRAU.PERCENT_SPACE_USED) PERCENT_SPACE_USED,
SUM(FRAU.PERCENT_SPACE_RECLAIMABLE) PERCENT_SPACE_RECLAIMABLE,
dbms_xplan.format_size(SUM(ROUND(FRAU.PERCENT_SPACE_RECLAIMABLE / 100 * RFD.SPACE_LIMIT ))) RECLAIM,
dbms_xplan.format_Number(SUM(FRAU.NUMBER_OF_FILES)) NUMBER_OF_FILES,
SUM(FRAU.PERCENT_SPACE_USED) PERCENT_SPACE_USED
FROM V$FLASH_RECOVERY_AREA_USAGE FRAU, V$RECOVERY_FILE_DEST RFD
GROUP BY ROLLUP(FILE_TYPE);
FILE_TYPE USED PERCENT_SPACE_USED% PERCENT_SPACE_RECLAIMABLE RECLAIM NUMBER_OF_FILE PERCENT_SPACE_USED%
------------------------------ ---------- ------------------- ------------------------- ---------- -------------- -------------------
ARCHIVED LOG 1942M 189.65 0 0 27 189.65
AUXILIARY DATAFILE COPY 0 .00 0 0 0 .00
BACKUP PIECE 0 .00 0 0 0 .00
CONTROL FILE 13M 1.27 0 0 1 1.27
FLASHBACK LOG 0 .00 0 0 0 .00
FOREIGN ARCHIVED LOG 0 .00 0 0 0 .00
IMAGE COPY 0 .00 0 0 0 .00
REDO LOG 0 .00 0 0 0 .00
Total: 1955M 190.92 0 0 28 190.92
9 rows selected.
run {
crosscheck backupset of database;
crosscheck backupset of controlfile;
crosscheck backupset of archivelog all;
crosscheck backup;
delete noprompt expired backup;
delete force noprompt obsolete;
delete expired archivelog all;
delete noprompt obsolete;
delete noprompt obsolete orphan;
crosscheck archivelog all;
delete expired archivelog all;
CROSSCHECK COPY OF CONTROLFILE;
}
Friday, 29 April 2011
ORA-19809: limit exceeded for recovery files
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment