Search This Blog

Total Pageviews

Sunday, 15 May 2011

Oracle Flashback full / oracle hanged due archive log dest full

ORA-00257: archiver error. Connect internal only, until freed.
ORA-19809: limit exceeded for recovery files
ARC1: Error 19809 Creating archive log file to

or

this error

oracle@apt-amd-02:/opt/app/oracle/product/11.2/network/admin> sqlplus system/sys@//apt-amd-02.aptus.co.uk:1521/orcl.aptus.co.uk

SQL*Plus: Release 11.2.0.1.0 Production on Sun May 15 05:18:37 2011

Copyright (c) 1982, 2009, Oracle. All rights reserved.

ERROR:
ORA-00257: archiver error. Connect internal only, until freed.


Enter user-name: oracle@apt-amd-02:/opt/app/oracle/product/11.2/network/admin> sqlplus system/sys@apt-amd-02.aptus.co.uk:1521/orcl.aptus.co.uk

SQL*Plus: Release 11.2.0.1.0 Production on Sun May 15 05:18:59 2011

Copyright (c) 1982, 2009, Oracle. All rights reserved.

ERROR:
ORA-00257: archiver error. Connect internal only, until freed.




SQL> select group#,status archived from v$log;

GROUP# ARCHIVED
---------- ----------------
1 INACTIVE
2 INACTIVE
3 CURRENT

SQL> alter system switch logfile; <<<<---- will also hang


oracle@apt-amd-02:~> adrci

ADRCI: Release 11.2.0.1.0 - Production on Sun May 15 05:29:12 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

ADR base = "/opt/app/oracle"
adrci> show error
DIA-48415: Syntax error found in string [show error] at column [10]

adrci> show alert

Choose the alert log from the following homes to view:

1: diag/tnslsnr/apt-amd-02/listener
2: diag/tnslsnr/apt-amd-02/listenernew
3: diag/rdbms/orcl/orcl
Q: to quit

Please select option: 3





************************************************************************
Errors in file /opt/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_arc0_23558.trc:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 46065152 bytes disk space from 4070572032 limit
ARC0: Error 19809 Creating archive log file to '/opt/app/oracle/flash_recovery_area/ORCL/archivelog/2011_05_15/o1_mf_1_422_%u_.arc'
2011-05-15 05:29:44.667000 +00:00
Errors in file /opt/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_arc1_23560.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 4070572032 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.
************************************************************************
Errors in file /opt/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_arc1_23560.trc:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 46065152 bytes disk space from 4070572032 limit
ARC1: Error 19809 Creating archive log file to '/opt/app/oracle/flash_recovery_area/ORCL/archivelog/2011_05_15/o1_mf_1_422_%u_.arc'



Review your backup retention policy and if required change the RMAN RETENTION POLICY

RMAN>CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;


- If RMAN is not part of backup strategy and archivelogs are going to FRA then manual intervention required for deletion of archivelogs. Periodically purse old archivelogs


RMAN>Delete archivelog all completed before 'SYSDATE-7';

Do you really want to delete the above objects (enter YES or NO)? yes

archived log file name=/opt/app/oracle/flash_recovery_area/ORCL/archivelog/2011_05_07/o1_mf_1_374_6wco5np6_.arc RECID=135 STAMP=750554681
Deleted 42 objects


run again now ever thing ok

RMAN> Delete archivelog all completed before 'SYSDATE-7';

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=52 device type=DISK

RMAN> exit


SQL> alter system switch logfile; ---- now this will work


System altered.

No comments:

Oracle DBA

anuj blog Archive