Archive Hung error:
===============
===============
Scenario#1
=========
Error Description: Your database is running on ARCHIVELOG
MODE and when you try to connect through sqlplus you are getting following
error. ORA-00257: archiver is stuck.
CONNECT INTERNAL only, until freed.
Problem Description: If your database is running on archive
log mode, database redo log files will be frequently archived to the archive
log destination.
The frequency of the redo archiving is depended on the
volume of the transaction on the database.
If the archive log destination is full then the archiver (ARCH)
background process cannot write the archieve files into the archive log
destination. Further, the database will be in hung state no connections will be
allowed to the database. In this situation if you try to connect the database
you get following error message. ORA-00257:
archiver is stuck. CONNECT INTERNAL.
Solution Description:
Check your database archive log destination
(LOG_ARCHIVE_DEST). The file system must be full and no more room for additional
files. Remove some archive files into tape storage or delete permanently if you
have already backed up. If you have a
successful physical database backup(HOT/COLD), you can remove the archive files
which got generate before the physical backup. Archive will not start
automatically even after the file system clean up. You have to connect to the
database sys as sysdba and execute
Alter system archive log all;
Recommendation:
Implement the file system monitor scripts with some
threshold limit especially for archive log destination so that you get alarms
when the file system reaches the threshold. Also periodically backup/move the
older archive files to permanent storage or to other file system where you have
the space. Otherwise if your database is a critical production database, you
need to face a outage on the database.
Scenario#2
=========
Error Description:
The database is running in ARCHIVE MODE and one fine day
when you try to connect via SQLPLUS, it throws following error. ORA-00257: archiver is stuck. CONNECT INTERNAL only,
until freed.
When you try to connect via sqlplus as internal and issue.
Alter system archive log all;
You are getting below mentioned error message
ORA-16020: less
destinations available than specified by
LOG_ARCHIVE_MIN_SUCCEED_DEST
If you issue
select * from v$archive_dest
then you are getting below error message
ORA-19504: failed to
create file %s
When space becomes available again after cleaning up the
destination automatic achieving is not resumed.
00257, 00000, "archiver error. Connect internal only,
until freed."
Cause: The archiver
process received an error while trying to archive a redo log. If the problem is not resolved soon, the
database will stop executing transactions. The most likely cause of this
message is the destination device is out of space to store the redo log
file.
Action: Check
archiver trace file for a detailed description of the problem. Also verify that
the device specified in the initialization parameter ARCHIVE_LOG_DEST is set up
properly for archiving.
16020, 00000,
"less destinations available than specified by LOG_ARCHIVE_MIN_SU"
Cause: With automatic
archiving enabled, the number of archive log
destinations that could be used for the database was less than the
LOG_ARCHIVE_MIN_SUCCEED_DEST parameter value.
Action: Either adjust
the settings of the log archive destination parameters, or lower the value of
LOG_ARCHIVE_MIN_SUCCEED_DEST
19504, 00000,
"failed to create file \"%s\""
Cause: call to create
file returned an error
Action: check additional messages, check access permissions
Solution Description
Even if you clean up the archive log destination by moving
some archive files to table or to some other location, and archive destination
has enough room for additional new files, you get this error message. For each
and every archive destination give correct the archivelogpath and issue.
alter system set LOG_ARCHIVE_DEST_.. =
'location=/archivelogpath reopen';
Solution Explanation:
Automatic archiving got stuck due to the fact that the
archive destination filled up, there is no more space available. When space becomes available again after
archives have been moved to tape (or elsewhere) automatic archiving is not automatically
resumed unfortunately.
Scenario#3
=========
Error Description:
The database is running in ARCHIVE MODE and one fine day
when you try to connect via SQLPLUS, it throws following error.
0RA-00257:archiver error, connect internal only until freed
ORA-16014:log 2 sequence# 1789 not archived, no available
destinations
ORA-00312:online log 2 thread
1:'/u02/oradata/prod/redo/redo02.log'
Problem Description
The error is due to the flash_recovery_area is full.
SQL>select group#,status archived from v$log;
GROUP# ARCHIVED
------------ ------------------------
1 INVALIDATED
2 INACTIVE
3 INACTIVE
If you issue the solution command to start archive all file,
it is giving the following error.
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.
Solution Description
1. 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
Solution
1. Confirm whether the database is running in archive log
mode and automatic archiving is enabled.
SQL> archive log list;
2. If archive destination is defined by
USE_DB_RECOVERY_FILE_DEST, determine the archive destination by:
SQL> show parameter db_recovery_file_dest;
Also check the value set for db_recovery_file_dest_size
3. Check the space used in flash recovery area by:
SQL> SELECT * FROM V$RECOVERY_FILE_DEST;
4. If the SPACE_USED is reached SPACE_LIMIT, cleanup the
archive logs to a different destination.
5. Archive all the log files
SQL> alter system archive log all;
6. If ORA-16020: less destinations available than specified
by LOG_ARCHIVE_MIN_SUCCEED_DEST
is received for step 5, then for each destination give the
correct archivelog path and issue:
SQL>alter system set LOG_ARCHIVE_DEST_.. =
'location=/archivelogpath reopen';
TIP: If you have configured Flash Recovery Area (default in
10gR2)--meaning that you have set the two initialization parameters:
DB_RECOVERY_FILE_DEST_SIZE
DB_RECOVERY_FILE_DEST
you cannot use the LOG_ARCHIVE_DEST and
LOG_ARCHIVE_DUPLEX_DEST parameters any more. You must always use the LOG_ARCHIVE_DEST_n
parameters in case you have configured flash recovery area. LOG_ARCHIVE_DEST_10
is implicitly set to USE_DB_RECOVERY_FILE_DEST if you create a recovery area
and do not set any other local archiving destinations. If you try to use
LOG_ARCHIVE_DEST with a Flash Recovery Area configured, you will receive errors
like:
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified
value is invalid
ORA-16018: cannot use LOG_ARCHIVE_DEST with
LOG_ARCHIVE_DEST_n or
DB_RECOVERY_FILE_DEST
7. Just switch the logs to verify:
SQL> alter system switch logfile;
Below mentioned are steps to increase the flash recovery
area.
1. Find out the flash recovery area location.
SQL> show parameter db_recovery_file_dest;
2. Disable the Flash Recovery Area
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='' SCOPE=BOTH
SID='*';
3. Increase the Flash Recovery Area
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 10g
SCOPE=BOTH SID='*';
4. Enable the Flash Recovery Area
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/dir1'
SCOPE=BOTH SID='*';
If the flash recovery area location is an Automatic Storage
Management disk group named disk1, for example, then you can do the following:
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '+disk1'
SCOPE=BOTH SID='*';
Some Additional Tips:
Archiving & Oracle archived redo logs
Explanation From Oracle documentation
"Oracle Database lets you save filled groups of redo
log files to one or more offline destinations, known collectively as the
archived redo log, or more simply the archive log. The process of turning redo
log files into archived redo log files is called archiving. This process is
only possible if the database is running in ARCHIVELOG mode. You can choose
automatic or manual archiving.
An archived redo log file is a copy of one of the filled
members of a redo log group. It includes the redo entries and the unique log
sequence number of the identical member of the redo log group. For example, if
you are multiplexing your redo log, and if group 1 contains identical member
files a_log1 and b_log1, then the archiver process (ARCn) will archive one of
these member files. Should a_log1 become corrupted, then ARCn can still archive
the identical b_log1. The archived redo log contains a copy of every group created
since you enabled archiving.
When the database is running in ARCHIVELOG mode, the log
writer process (LGWR) cannot reuse and hence overwrite a redo log group until
it has been archived. The background process ARCn automates archiving
operations when automatic archiving is enabled. The database starts multiple
archiver processes as needed to ensure that the archiving of filled redo logs
does not fall behind."
Purpose of Archive logs
• To
Recover a database
• To
Update a standby database
• To Get
information about the history of a database using the LogMiner utility
Oracle database can be run in two modes 1. ARCHIVELOG mode
2. NOARCHIVELOG mode
NOARCHIVELOG Mode:
When you run your database in NOARCHIVELOG mode, you disable
the archiving of the redo log.
NOARCHIVELOG mode protects a database from instance failure
but not from media failure. Only the most recent changes made to the database,
which are stored in the online redo log groups, are available for instance recovery.
If a media failure occurs while the database is in NOARCHIVELOG mode, you can
only restore the database to the point of the most recent full database backup.
You cannot recover transactions subsequent to that backup.
In NOARCHIVELOG mode you cannot perform online tablespace
backups, nor can you use online tablespace backups taken earlier while the
database was in ARCHIVELOG mode. To restore a database operating in
NOARCHIVELOG mode, you can use only whole database backups taken while the
database is closed. Therefore, if you decide to operate a database in
NOARCHIVELOG mode, take whole database backups at regular, frequent intervals.
ARCHIVELOG Mode:
When you run a database in ARCHIVELOG mode, you enable the
archiving of the redo log. The database control file indicates that a group of
filled redo log files cannot be reused by LGWR until the group is archived. A
filled group becomes available for archiving immediately after a redo log
switch occurs.
The archiving of filled groups has these advantages:
A database backup, together with online and archived redo
log files, guarantees that you can recover all committed transactions in the
event of an operating system or disk failure.
If you keep an archived log, you can use a backup taken
while the database is open and in normal system use.
You can keep a standby database current with its original
database by continuously applying the original archived redo logs to the
standby.
So, If you cannot afford to lose any data in your database
in the event of a disk failure, use ARCHIVELOG mode.
In reality most of the Oracle databases run in ARCHIVELOG
mode.
The Oracle error ORA-000257, which we are discussing occurs
in the databases that run in ARCHIVELOG mode.
Where does the Archive Logs stored/resides?
Oracle database lets you specify whether to archive, redo
logs to a single destination or multiplex them. If you want to archive only to
a single destination, you specify that destination in the LOG_ARCHIVE_DEST
initialization parameter. If you want to multiplex the archived logs, you can
choose whether to archive to up to ten locations (using the LOG_ARCHIVE_DEST_n
parameters) or to archive only to a primary and secondary destination (using
LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST).
Two methods for specifying archive destination:
Method 1: Using the LOG_ARCHIVE_DEST_n Parameter
Use the LOG_ARCHIVE_DEST_n parameter (where n is an integer
from 1 to 10) to specify from one to ten different destinations for archival.
Each numerically suffixed parameter uniquely identifies an individual
destination.
Steps for setting archivelog destination:
1. Use SQL*Plus to shut down the database.
SHUTDOWN
2. Set the LOG_ARCHIVE_DEST_n initialization parameter to
specify from one to ten archiving locations. The LOCATION keyword specifies an
operating system specific path name. For example, enter:
LOG_ARCHIVE_DEST_1 = 'LOCATION = /oradata1/archive'
LOG_ARCHIVE_DEST_2 = 'LOCATION = /oradata2/archive'
3. Optionally, set the LOG_ARCHIVE_FORMAT initialization
parameter, using %t to include the thread number as part of the file name, %s
to include the log sequence number, and %r to include the resetlogs ID (a
timestamp value represented in ub4). Use capital letters (%T, %S, and %R) to
pad the file name to the left with zeroes.
example for UNIX OS:
LOG_ARCHIVE_FORMAT=%t_%s_%r.dbf
Method 2: Using LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST
To specify a maximum of two locations, use the
LOG_ARCHIVE_DEST parameter to specify a primary archive destination and the
LOG_ARCHIVE_DUPLEX_DEST to specify an optional secondary archive destination.
All locations must be local. Whenever the database archives a redo log, it
archives it to every destination specified by either set of parameters.
Perform the following steps the use method 2:
1. Use SQL*Plus to shut down the database.
SHUTDOWN
2. Specify destinations for the LOG_ARCHIVE_DEST and
LOG_ARCHIVE_DUPLEX_DEST parameter (you can also specify LOG_ARCHIVE_DUPLEX_DEST
dynamically using the ALTER SYSTEM statement). For example, enter:
LOG_ARCHIVE_DEST = '/disk1/archive'
LOG_ARCHIVE_DUPLEX_DEST = '/disk2/archive'
Step3) Set the LOG_ARCHIVE_FORMAT initialization parameter.
example for UNIX OS:
LOG_ARCHIVE_FORMAT=%t_%s_%r.dbf
The Oracle error ORA-000257, which we are discussing occurs
in the databases that run in ARCHIVELOG mode when the above mentioned archive
destinations are full and when the archiver was unable to write to these
archive destinations
In order to resolve this error you need to find the archive
log info from the database. For that Oracle provides number of internal views
and commands. The following are those details:
Oracle Archive related Views:
V$DATABASE Shows if the database is in ARCHIVELOG or
NOARCHIVELOG mode and if MANUAL (archiving mode) has been specified.
V$ARCHIVED_LOG Displays historical archived log information
from the control file. If you use a recovery catalog, the RC_ARCHIVED_LOG view
contains similar information.
V$ARCHIVE_DEST Describes the current instance, all archive
destinations, and the current value, mode, and status of these destinations.
V$ARCHIVE_PROCESSES Displays information about the state of
the various archive processes for an instance.
V$BACKUP_REDOLOG Contains information about any backups of
archived logs. If you use a recovery catalog, the RC_BACKUP_REDOLOG contains
similar information.
V$LOG Displays all redo log groups for the database and
indicates which need to be archived.
V$LOG_HISTORY Contains log history information such as which
logs have been archived and the SCN range for each archived log.
To find which redo log group requires archiving run the
following query:
SELECT GROUP#, ARCHIVED
FROM SYS.V$LOG;
GROUP# ARC
-------- ---
1 YES
2 NO
To find the current archiving mode run the following query:
SELECT LOG_MODE FROM SYS.V$DATABASE;
LOG_MODE
------------
NOARCHIVELOG
The ARCHIVE LOG LIST Command
The SQL*Plus command ARCHIVE LOG LIST displays archiving
information for the connected instance. For example:
SQL> ARCHIVE LOG LIST
Database log mode Archive Mode
Automatic archival Enabled
Archive destination D:\oracle\oradata\TESTDB1\archive
Oldest online log sequence 1270
Next log sequence to archive 1274
Current log sequence 1274
This display tells you all the necessary information
regarding the archived redo log settings for the current instance:
The database is currently operating in ARCHIVELOG mode.
Automatic archiving is enabled.
The archived redo log destination is
D:\oracle\oradata\PROD\archive.
The oldest filled redo log group has a sequence number of
1270.
The next filled redo log group to archive has a sequence
number of 1274.
The current redo log file has a sequence number of 1274.
No comments:
Post a Comment