advertisements
_____________________________________________________________________________________________________________________
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.
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.
_____________________________________________________________________________________________________________________
0 comments:
Post a Comment