URGENT: ORA-00257: Archiver error. Connect internal only, until freed

Did you try the following on the Oracle server:
sqlplus / /nolog
connect as sysdba?
http://www.dbaoncall.net/references/er_257.html

This all depends on your version of Oracle.

For most versions, go to a command prompt and type: sqlplus /nolog
from the SQL prompt:  conn / as sysdba

Once there, do:
archive log list

My bet is automatic archiving isn't enabled.  If it is then we have bigger problems.  Make sure you are archiving where you thing you are:
from the same SQL prompt:  show parameter arch

Make sure all the deistinations are correct.

Here's a decent question already covering automatic archiving:
https://www.experts-exchange.com/questions/21364551/Automatic-archival-how-to-configure.html

You might find this question helpful as well:
https://www.experts-exchange.com/questions/21477355/ORA-00257.html

I entered the command 'archive log list' and
Automatic Archival is enabled
oldest online log sequence 120
next log sequence to archive 120
current log 123

It looks like the problem is that The destination folder name had a typo I corrected the folder name to match what oracle is returning.
will oracle correct itself  or should i force an archive.

when I try to connect to the database via Enterprise manager I still get this
SQLException ORA-00257: archiver error. Connect internal only, until freed.  
current status: open
Operation: Shutdown immediate

also when type the command: archive log all
I get this message ORA-16020: less distinations available than specified by LOG_ARCHIVE_SUCCEED_DEST.

If you indeed have a typo where your archive log destination is you may be able to set it within a session logged on by "sys" and simply issue:

set logsource = "PATH_OF_VALID_DESTINATION"

and now at this point issue "alter system archive log all"

Bryce

bryce
i believe the destination is correct. the name of the destination folder had a type which I have corrected so
it now matches the path reported by oracle when i type the command archive log list.
do I still need to issue the two commands above to correct the situation?

Sorry I see that you have tried that, can you not lower this value dynamically? i.e. Alter system set LOG_ARCHIVE_MIN_SUCCEED_DEST=??

Well the archiver should try again to flush the contents of its buffer periodically as it acts like a service (or daemon in the UNIX world). But to allleviate the problem immediately, yes I would issue the command (archive log all)

bryce

bryce,
I tried that command (archive log all) but that's when I get the ORA -16020 message.
before i run the command you suggested to lower the value (Alter system set LOG_ARCHIVE_MIN_SUCCEED_DEST=??)
what is this telling me? and how do I determine what the current value is?

You can simply issue "SHOW PARAMETER  LOG_ARCHIVE_MIN_SUCCEED_DEST" and the engine will return you the current value.

Bryce

the sqlplus command: "show parameter arch" should show you that parameters current value.  If you only want that one: show parameter LOG_ARCHIVE_MIN_SUCCEED_DEST

I still think there is a problem with the destination.  You should not have to change the LOG_ARCHIVE_MIN_SUCCEED_DEST value to get this to work (espically if it's the default value of 1).


What operating system are you on?

If you are 100% sure the PATHS match, then its probably a permisions problem (if on UNIX).

On Windows?  Does the archive destionation have a <space> in the path?

the value of LOG_ARCHIVE_MIN_SUCEED_DEST = 1 (CHANGED FROM 2)

OS: windows server 2003
archive destination: E:\archive2  (path E:\archive2)

Nothing is archiving, when archiving was enabled there were two destinations set , E:\arhive1 and E:\archive2

archive log all still returns the same error ORA-16020 less destinations available than specified by LOG_ARCHIVE_MIN_SUCCEED_DEST

MORE INFO:

log_archive_dest_1    LOCATION=E:\archive1 MANDATORY  REOPEN=300 QUOTA_SIZE=512
log_archive_dest_2    LOCATION=E:\archive2 OPTIONAL REOPEN=300 QUOTA_SIZE=512
log_archive_local_first boolean TRUE
log_archive_max_processes integer 2
log_archive_start boolean FALSE
log_archive_trace integer 0
remote_archive_enable string true

OK, try this from Metalink:
\
Subject:  Ora-00257:Archiver Error, Connect Internal Only Until Freed and ORA-16014 and ORA-00312
  Doc ID:  Note:376923.1 Type:  PROBLEM
  Last Revision Date:  15-JUL-2006 Status:  MODERATED

In this Document
  Symptoms
  Cause
  Solution



--------------------------------------------------------------------------------


This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) Rapid Visibility (RaV) process, and therefore has not been subject to an independent technical review.



Applies to: Oracle Server - Enterprise Edition - Version: 9.2.0 to 10.2.0.3
This problem can occur on any platform.
SymptomsUsers cannot connect to the database :

0RA-00257:archiver error, connect internal only until freed
ORA-16014:log 2 sequence# 231 not archived, no available destinations
ORA-00312:online log 2 thread 1:'/u02/oradata/wlbranch/redo02.log'


CauseThe flash_recovery_area is not full.

SQL>  archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive Destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 231
Next log sequence to archive 231
Current log sequence 233
    SQL>select group#,status archived from v$log;
    GROUP# ARCHIVED
    ------------  ------------------------
    1                INVALIDATED
    2                INACTIVE
    3                INACTIVE
The suggested solution to archive all fails :

SQL>archive log all;
ORA-16020: less destinations available than specified by
LOG_ARCHIVE_MIN_SUCCEEDED_DEST

The only one destination allowed (log_archive_min_succeed_dest= 1) is not able to perform the archiving.
Solution1. Either allow more space in the DB_RECOVERY_FILE_DEST with the DB_RECOVERY_FILE_DEST_SIZE parameter  :

SQL> alter system set db_recovery_file_dest_size=3G ;

2. Or to avoid the situation once the 3Gb is full , set the following parameters so that when the dest1 is full, archiving is automatically  performed to the alternate dest2 :

log_archive_dest_1='LOCATION=use_db_recovery_file_dest NOREOPEN ALTERNATE=LOG_ARCHIVE_DEST_2'
log_archive_dest_2='LOCATION=/other_dest_for_archiving'
log_archive_dest_state_1='enable'
log_archive_dest_state_2='alternate'
db_recovery_file_dest='/u01/app/oracle/product/10.1.0/db_1/flash_recovery_area'
db_recovery_file_dest_size=2G



ErrorsORA-312 online log <name> thread <num>: <str>
ORA-257 archiver is stuck.
ORA-16020 less destinations available than specified by LOG_ARCHIVE_MIN_
ORA-16014 log %s sequence# %s not archived, no available destinations

--------------------------------------------------------------------------------

db_recovery_file_dest_size from 1 to 3GB


SQL>select group#,status archived from v$log;
    GROUP# ARCHIVED
    ------------  ------------------------
    1                INACTIVE
    2                CURRENT
    3                INACTIVE
    4                INACTIVE
The suggested solution to archive all fails :

SQL>archive log all;
ORA-16020: less destinations available than specified by
LOG_ARCHIVE_MIN_SUCCEEDED_DEST

why is oracle not seeing the first destination, if you note in the info i posted earlier,
location 1 is mandatory (and INACTIVE), location 2 is optional (and CURRENT).
a  archive log list; command only returns the archive2 destination.

log_archive_dest_1    LOCATION=E:\archive1 MANDATORY  REOPEN=300 QUOTA_SIZE=512
log_archive_dest_2    LOCATION=E:\archive2 OPTIONAL REOPEN=300 QUOTA_SIZE=512
log_archive_local_first boolean TRUE

I tried restarting the database and now the problem got worse

connected to an idle instance,
then issued a startup command
ORA-32004 obsolete and/or deprecated parameter specified
ORa-10997 another startup/shutdown operation of this instance in progress
ORA-09968 unable to lock file

Hi,
   What is your oracle version?
Are you using init file or spfile?
can you please paste your archive_log parameters here?
Regards,
Badri.

10g
both init.ora and spfile.ora are present (spfile is dated more recent).

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            E:\archive2
Oldest online log sequence     0
Next log sequence to archive   125
Current log sequence           125

Badri,
I should mentio that I got rid of the ora-32004 message by reseting the parameter
I also forced a startup and i'm able to open the instance
now i'm back to my original problem.
the database is in archive mode but i keep losing connection because the logs fill up and nothing is getting archived.

as you can see from my earlier posting archival is enabled and the location is correct but I have a disconnect somewhere.
the log_archive_dest in init.ora does not match.

how can i confirm which file/setting oracle is reading? or alternative just need to disable archiving to allow users to connect to the database.


SGA_MAX_SIZE = 600M
REMOTE_OS_AUTHENT = FALSE
LOG_ARCHIVE_DEST = c:\oracle\oradata\DEV\archive
BACKGROUND_DUMP_DEST=C:\oracle\admin\DEV\bdump

When I query 'select archive from v$instance' it returns STARTED but still i see nothing getting archived.

Hi,
   Can you show the result of
SQL> show parameter archive;

Do you have sufficient space on the archive log destination? i.e.:\oracle\oradata\DEV\archive ? How much space is left on the device?
What is the size of online redo logs?

Regards,
Badri.

see below, note that as I mentioed before the location does not match setting in init.ora file.
plenty of room but no archive files created  in either location.

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
archive_lag_target                   integer     0
log_archive_config                   string
log_archive_dest                     string
log_archive_dest_1                   string      LOCATION=E:\archive1 MANDATORY
                                                  REOPEN=300 QUOTA_SIZE=512
log_archive_dest_10                  string
log_archive_dest_2                   string      LOCATION=E:\archive2 OPTIONAL
                                                 REOPEN=300 QUOTA_SIZE=512
log_archive_dest_3                   string
log_archive_dest_4                   string
log_archive_dest_5                   string

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_6                   string
log_archive_dest_7                   string
log_archive_dest_8                   string
log_archive_dest_9                   string
log_archive_dest_state_1             string      enable
log_archive_dest_state_10            string      enable
log_archive_dest_state_2             string      enable
log_archive_dest_state_3             string      enable
log_archive_dest_state_4             string      enable
log_archive_dest_state_5             string      enable
log_archive_dest_state_6             string      enable

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_7             string      enable
log_archive_dest_state_8             string      enable
log_archive_dest_state_9             string      enable
log_archive_duplex_dest              string
log_archive_format                   string      %d%t%s%r.arc
log_archive_local_first              boolean     TRUE
log_archive_max_processes            integer     2
log_archive_min_succeed_dest         integer     1
log_archive_start                    boolean     FALSE
log_archive_trace                    integer     0
remote_archive_enable                string      true

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest                 string      %ORACLE_HOME%\RDBMS
SQL>

Has this ever worked (is this a new install)?

Is the E drive a mapped network drive?

the database has been running smoothly for a while
archiving was enabled on friday and the problems started then.
E is another drive in the save server.

>>how can i confirm which file/setting oracle is reading?
issue: show parameter spfile
By default Oracle uses the SPFILE instead of an init.ora file.

Do you have an Oracle support contract?  You might want to open a service request with Oracle.  I'm about out of ideas.  Nothing is jumping out at me as wrong.  Everything looks like it should be working.

Are you specifically using your pfile parameter when you start the database? Inm other words something like:

STARTUP PFILE='<LOCATION_OF_PFILE>' open

can  archiving be disabled to avoid this issue and just reanbled later with only this command

alter database noarchivelog; and restart instance/

Hi,
>>log_archive_dest_1                   string      LOCATION=E:\archive1 MANDATORY
                                                  REOPEN=300 QUOTA_SIZE=512

this parameter shows that log_archive_dest_1 is mandatory.
Is this drive accessible? How much space do you have left on E:\? What is the size of your online redo logs?
I understand that you have three online redo logs groups. Are all of these groups are of same size?

If you have sufficient space, then try
SQL>alter system swith logfile;

But one thing puzzles me is,
>>Oldest online log sequence     0

no database is started from db console or sqlplus setting with startup commnad.  

>>no database is started from db console or sqlplus setting with startup commnad.  
How did you start the database using db console?
Am I missing something?
Regards,
Badri.

oldest online log sequence =0
may be because I issued command to clear the log groups and the active group is now empty

i just disabled archivelog mode from db console

SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            E:\archive2
Oldest online log sequence     0
Current log sequence           125

would this effectively disable setting allowing redo logs to be overwritten for now while we figure out how to correct this configuration for archving?

Yes exactly, then how are you starting the database and how do you know what pfile you are using??

btutt,
I'm not sure if I understand your question,
when I start from db console it is poiting to this specific instance
when I start form sqlplus i usually have to set the ORACLE_SID to this instance, then login to sqlplus as sysdba
but I dont specify a pfile.

Yes, Now, archiving is disabled. You can enable the same in future.
Any of your online back-up jobs will fail because archiving is not enabled.
Once you have got enough space on disks, you can

SQL>Shutdown the database
SQL>startup mount
SQL>alter database archive log;
SQL>alter database open;

I would suggest having one destination as mandatory in it's parameter. the parameter log_archive_min_succeed_dest    =1 is the default and it would not stop you from running the database, if there is no space left on the device, as long as there is space on other destinations.
By the way, is it a live or test database?
Regards,
Badri.

badri,
this is not yet in production but needed for testing, I can affor no archive for a day or so.
the thing is my configuration reflects your suggestion, one mandatory, one optional location, log_archive_min_suceed_dest =1
I'm sure it is something very simple we are missing I will post an update as I figure this out.

That is fine that you are starting the database from the console as you say, but when you indicate the instance the default pfile is at $ORACLE_HOME/dbs/init$ORACLE_SID.ora.

Is this the file you are wanting to start your instance with. I would examine this file to ensure this is the one you are using!

Additionally did you follow the metalink note I had provided you??

ok thanks
i did follow the metalink instructions and posted the results.
an arhive log all command still returned a ORA-16020 error.

I would tend to agree (regarding the mandatory switch), I still, however, would like to see the contents of your pfile.

Additionally, you can change your log source to a completely different location and then issue "alter system archive log all". Have you tried to change this log source destination on your session just so  you can flush the archive log buffer to disk?

what would be the command for changing the archive log from mandatory to optional?

spfile contents:
scdev.__java_pool_size=4194304
scdev.__large_pool_size=4194304
scdev.__shared_pool_size=121634816
scdev.__streams_pool_size=0
*.aq_tm_processes=1
*.audit_trail='db'
*.BACKGROUND_DUMP_DEST='C:\oracle\admin\dev\bdump'
*.compatible='10.2.0.1.0'
*.control_file_record_keep_time=90
*.CONTROL_FILES='c:\oracle\oradata\dev\control1.ctl','c:\Oracle\oradata\dev\control2.ctl'
*.CORE_DUMP_DEST='C:\oracle\admin\dev\cdump'
*.db_block_size=8192
*.db_file_multiblock_read_count=16
*.db_files=200
*.DB_NAME='DEV'
*.db_recovery_file_dest='E:\flashrecov'
*.db_recovery_file_dest_size=5
*.fast_start_mttr_target=30
*.job_queue_processes=10
*.log_archive_dest=''
*.log_archive_dest_1='LOCATION=E:\archive1 MANDATORY REOPEN=300 QUOTA_SIZE=512'
*.log_archive_dest_2='LOCATION=E:\archive2 OPTIONAL REOPEN=300 QUOTA_SIZE=512'
*.log_archive_format='%d%t%s%r.arc'
*.log_archive_min_succeed_dest=1
*.log_buffer=3145728
*.open_cursors=500
*.processes=300
*.remote_login_passwordfile='exclusive'
*.REMOTE_OS_AUTHENT=FALSE
*.SGA_MAX_SIZE=600M
*.SGA_TARGET=300M
*.trace_enabled=false
*.undo_management='AUTO'
*.undo_tablespace='UNDO'
*.USER_DUMP_DEST='C:\oracle\admin\dev\udump'