Solved

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

Posted on 2006-11-27
46
11,083 Views
Last Modified: 2012-06-21
experts, i'm in need of urgent help
users are getting this error when connecting to the database
ORA-00257: Archiver error. Connect internal only, until freed
there is plenty of room on both drives but it look like the the logs are not getting archinved.
I can't verify if autoextend is enabled as i don't know how to connect.

When I try to connect to sql as below I get the same message.

connect sys/password as sysdba or

sqlplus connect /as sysdba
0
Comment
Question by:macareli
  • 19
  • 12
  • 6
  • +2
46 Comments
 
LVL 76

Expert Comment

by:arnold
Comment Utility
Did you try the following on the Oracle server:
sqlplus / /nolog
connect as sysdba?
http://www.dbaoncall.net/references/er_257.html
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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:
http://www.experts-exchange.com/Databases/Oracle/Q_21364551.html
0
 
LVL 76

Expert Comment

by:arnold
Comment Utility
You might find this question helpful as well:
http://www.experts-exchange.com/Databases/Oracle/Q_21477355.html
0
 

Author Comment

by:macareli
Comment Utility
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

0
 

Author Comment

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

0
 
LVL 6

Expert Comment

by:btutt
Comment Utility
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
0
 

Author Comment

by:macareli
Comment Utility
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?
0
 
LVL 6

Expert Comment

by:btutt
Comment Utility
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=??
0
 
LVL 6

Expert Comment

by:btutt
Comment Utility
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
0
 

Author Comment

by:macareli
Comment Utility
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?

0
 
LVL 6

Expert Comment

by:btutt
Comment Utility
You can simply issue "SHOW PARAMETER  LOG_ARCHIVE_MIN_SUCCEED_DEST" and the engine will return you the current value.

Bryce
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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?
0
 

Author Comment

by:macareli
Comment Utility
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
0
 

Author Comment

by:macareli
Comment Utility
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
0
 
LVL 6

Expert Comment

by:btutt
Comment Utility
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

--------------------------------------------------------------------------------
0
 

Author Comment

by:macareli
Comment Utility
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
0
 

Author Comment

by:macareli
Comment Utility
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
0
 
LVL 8

Expert Comment

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

Author Comment

by:macareli
Comment Utility
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
0
 

Author Comment

by:macareli
Comment Utility
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.
0
 
LVL 8

Expert Comment

by:gvsbnarayana
Comment Utility
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.
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:macareli
Comment Utility
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>
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
Has this ever worked (is this a new install)?

Is the E drive a mapped network drive?
0
 

Author Comment

by:macareli
Comment Utility
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.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>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.
0
 
LVL 6

Expert Comment

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

STARTUP PFILE='<LOCATION_OF_PFILE>' open
0
 

Author Comment

by:macareli
Comment Utility
can  archiving be disabled to avoid this issue and just reanbled later with only this command

alter database noarchivelog; and restart instance/
0
 
LVL 8

Expert Comment

by:gvsbnarayana
Comment Utility
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


0
 

Author Comment

by:macareli
Comment Utility
no database is started from db console or sqlplus setting with startup commnad.  
0
 
LVL 8

Expert Comment

by:gvsbnarayana
Comment Utility
>>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.
0
 

Author Comment

by:macareli
Comment Utility
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?
0
 
LVL 6

Expert Comment

by:btutt
Comment Utility
Yes exactly, then how are you starting the database and how do you know what pfile you are using??
0
 

Author Comment

by:macareli
Comment Utility
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.
0
 
LVL 8

Expert Comment

by:gvsbnarayana
Comment Utility
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.
0
 

Author Comment

by:macareli
Comment Utility
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.
0
 
LVL 6

Expert Comment

by:btutt
Comment Utility
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!
0
 
LVL 6

Expert Comment

by:btutt
Comment Utility
Additionally did you follow the metalink note I had provided you??
0
 

Author Comment

by:macareli
Comment Utility
ok thanks
i did follow the metalink instructions and posted the results.
an arhive log all command still returned a ORA-16020 error.
0
 
LVL 8

Accepted Solution

by:
gvsbnarayana earned 125 total points
Comment Utility
Sorry... I mean   ... I would suggest NOT marking one of the archive log destinations to mandatory.
You can leave all of them in optional mode and log_archive_min_suceed_dest =1 .
Then, you can have your db running atleast one of them are available.
HTH
Regards,
Badri.
0
 
LVL 6

Expert Comment

by:btutt
Comment Utility
I would tend to agree (regarding the mandatory switch), I still, however, would like to see the contents of your pfile.
0
 
LVL 6

Expert Comment

by:btutt
Comment Utility
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?
0
 

Author Comment

by:macareli
Comment Utility
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'
0
 
LVL 6

Assisted Solution

by:btutt
btutt earned 125 total points
Comment Utility
alter system set log_archive_dest_1='LOCATION=E:\archive1 OPTIONAL REOPEN=300 QUOTA_SIZE=512'
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

This article describes some very basic things about SQL Server filegroups.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now