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.

Oracle LISTENER NO need for listener.ora

Oracle connect without listener file

oracle@apt-amd-02:~> sql / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun May 15 05:56:12 2011

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string
db_name string orcl
db_unique_name string orcl
global_names boolean FALSE
instance_name string orcl <<<<<----
lock_name_space string
log_file_name_convert string
service_names string orcl.aptus.co.uk <<<<-----

instance_name,service_names should set properly .


oracle@apt-amd-02:/opt/app/oracle/product/11.2/network/admin> lsnrctl stop

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 15-MAY-2011 05:58:47

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=apt-amd-02.aptus.co.uk)(PORT=1521)))
The command completed successfully
oracle@apt-amd-02:/opt/app/oracle/product/11.2/network/admin> mv listener.ora listener.ora20110515-1

moved the file . so no listener file now

oracle@apt-amd-02:/opt/app/oracle/product/11.2/network/admin> ls -ltr listener.ora
ls: cannot access listener.ora: No such file or directory

oracle@apt-amd-02:/opt/app/oracle/product/11.2/network/admin> lsnrctl start


oracle@apt-amd-02:/opt/app/oracle/product/11.2/network/admin> lsnrctl stat

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 15-MAY-2011 06:10:07

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 15-MAY-2011 06:00:11
Uptime 0 days 0 hr. 9 min. 55 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Log File /opt/app/oracle/diag/tnslsnr/apt-amd-02/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=apt-amd-02.aptus.co.uk)(PORT=1521)))
Services Summary...
Service "orcl.aptus.co.uk" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB.aptus.co.uk" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully



********************************************************************************

In this case PMON(process monitor ) process automatically register the service to SERVICE_NAMES and INSTANCE_NAME ,if these parameter set properly in init.ora file

*********************************************************************************
sqlplus [user]/[password]@//[database_host]:[port]/[service_name]

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

SQL*Plus: Release 11.2.0.1.0 Production on Sun May 15 06:03:41 2011

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

oracle@apt-amd-02:/opt/app/oracle/product/11.2/network/admin> sqlplus scott/tiger@anuj

SQL*Plus: Release 11.2.0.1.0 Production on Sun May 15 06:04:46 2011

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>



sqlplus [user]/[password]@//[database_host]:[port]/[service_name]


sqlplus scott/tiger@apt-amd-02.aptus.co.uk:1521/orcl.aptus.co.uk



LISTENER with different name, LISTENER default name is "LISTENER"
============================


oracle@apt-amd-02:/opt/app/oracle/product/11.2/network/admin> cat listener.ora20110515
# listener.ora Network Configuration File: /opt/app/oracle/product/11.2/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = apt-amd-02.aptus.co.uk)(PORT = 1521))
)
)

ADR_BASE_LISTENER = /opt/app/oracle

oracle@apt-amd-02:/opt/app/oracle/product/11.2/network/admin> cp listener.ora20110515 listener.ora
oracle@apt-amd-02:/opt/app/oracle/product/11.2/network/admin> lsnrctl start LISTENER1

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 16-MAY-2011 15:14:44

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

Starting /opt/app/oracle/product/11.2/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /opt/app/oracle/product/11.2/network/admin/listener.ora
Log messages written to /opt/app/oracle/diag/tnslsnr/apt-amd-02/listener1/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=apt-amd-02.aptus.co.uk)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=apt-amd-02.aptus.co.uk)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER1
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 16-MAY-2011 15:14:47
Uptime 0 days 0 hr. 0 min. 3 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /opt/app/oracle/product/11.2/network/admin/listener.ora
Listener Log File /opt/app/oracle/diag/tnslsnr/apt-amd-02/listener1/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=apt-amd-02.aptus.co.uk)(PORT=1521)))
The listener supports no services
The command completed successfully
oracle@apt-amd-02:/opt/app/oracle/product/11.2/network/admin> ps -ef|grep LIST
oracle 20618 1 0 15:14 ? 00:00:00 /opt/app/oracle/product/11.2/bin/tnslsnr LISTENER1 -inherit
oracle 20629 26430 0 15:14 pts/13 00:00:00 grep LIST