ORA-00257: archiver error. Connect internal only, until freed.
Action: Check the 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.
-------------------------------------------------------------------------------------
ORA-000257 error is somewhat common error and is caused whenever archiver is unable to archive the online redo log files at the desired location. Usually this error cocurs when archive destination is full and the archiver was not able to write any more due to lack of space.
So for understanding this ORA-000257 error, it is better knowing the concepts of Oracle Archiving. SO here are the archiving details
What is archiving and Oracle archived redo logs ?
The folowing explanation excerpt 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."
Why archived redo logs are used?
-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:
step1 )Use SQL*Plus to shut down the database.
SHUTDOWN
step2 )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 = /disk1/archive'
LOG_ARCHIVE_DEST_2 = 'LOCATION = /disk2/archive'
Step3) 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:
step1) Use SQL*Plus to shut down the database.
SHUTDOWN
Step2) 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 folowing 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 11270
Next log sequence to archive 11274
Current log sequence 11274
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\TESTDB1\archive.
The oldest filled redo log group has a sequence number of 11270.
The next filled redo log group to archive has a sequence number of 11274.
The current redo log file has a sequence number of 11274.
RESOLUTION/Fix to ORA-00257 error:
Increase the free space in the archive log destination directories. The location where archiver archives the log is determined by parameter file pfile or spfile.
You can find the archive log destination by running the following command from SQL*Plus
SQL> show parameter log_archive_dest
You can also find archive destination information using above mentioned oracle internal views and commands mainly by using " archive log list" command
In case no more space is available at the current archive destination due to OS / Disk limitations , you can specify a new desination location by setting the parameter log_archive_dest (or log_archive_dest_1 in some cases) so that the new archives are produced at new location.
You can do this modifying init.ora file or using alter system if spfile is present
SQL> alter system set log_archive_dest_1=’LOCATION = /disk9/archive'
The other option is to take a backup of the archives from the current desination and delete those archives from that place so that new archives can generated at the current destination.
The backup can be OS level backup and OS level file deletion BUT the recommended method for ASM in place is taking RMAN backup and delete using RMAN.
Eample:
rman target sys/sys
RMAN> backup archive log all device type disk format ‘/oracle/arch_%U’;
RMAN> delete archive until time ‘trunc(sysdate)’;
This will delete all the archive logs until today and space will freed and the archiver will start archiving redo logs
CONCLUSION:
The ORA-00257 Oracle error is related to archiving of the oracele database redo logs and it requires immmediate attention of the DBA. So Oracle users when you encounter this error notify the DBA so that this error will be corrected abd instance crashes will be avoided.
This error is mainly caused because of lack of space at the archive destination. Oracle provides number of views and commands to find the archivelog, archive destination info as mentioned above in the article, by using them we can find identify which archive destination ran out of space and can take appropriate action as described in the resolution to the error.
I hope this article is informative enough to identify root cause for ORA-00257 error, to provide insights on oracle ARCHIVELOG feature and to help in resolving ORA-000257 error.
No comments:
Post a Comment