Error ora 00257 archiver error connect internal only until freed

A scheduler job which is refreshing materialized views threw an error ORA-00257 in the alert log like this:

ORA-12012: error on auto execute of job 78450
ORA-00257: archiver error. Connect internal only, until freed.
...

Rationale on ORA-00257

The first action we should take is to check all the archived log destinations LOG_ARCHIVE_DEST_n or DB_RECOVERY_FILE_DEST to make sure they have enough space. If they are in good conditions, the possible causes could be from the remote database that is connected by local database links for refreshing local materialized views. Either local or remote database could cause the error, you should release some space to make the database move.

Solutions to ORA-00257

1. Remove ALL Archived Logs in RMAN

If you’re in an urgent situation, you can delete all archived logs without asking anything.

$ rman target /
...
RMAN> delete noprompt archivelog all;

This RMAN command will delete all archived logs without prompting you the confirmation. That is to say, we keep none of online archived logs.

2. Remove Some Archived Logs in RMAN

The following command will keep archived logs only latest 3 days.

RMAN> delete archivelog until time 'sysdate - 3';

The following command will keep archived logs only 1 hour.

RMAN> delete archivelog until time 'sysdate - 1/24';

The following command will keep archived logs only 5 minutes.

RMAN> delete archivelog until time 'sysdate - 5/1440';

3. Remove Some Archived Logs at OS-level

Sometimes, you might be not able to access RMAN in the first place, to free up the space of archived log destinations manually, you can refer to following steps in order to solve ORA-00257.

Find Candidates

List and make sure all the target files are available to move or delete.

For example, we’d like to keep the files newer than 7 days. The following Unix command find will list 7 and 7+ days old files.

$ cd /path/to/archived_log_destination
$ find . -mtime +6 -exec ls -l {} ;

Please make sure the listed file are allowed to be moved or deleted. Furthermore, you should make sure the standby databases have received or applied the archived logs.

Remove Files

Remove all the target files.

$ find . -mtime +6 -exec rm {} ;

So far, the database is not aware of the resulting free space that you just did on OS-level. So we need to notify the database.

Connect RMAN

Connect to the backup database.

$ rman target / catalog sys/password@backupdb

Find Expired Archive Logs

Notify RMAN to check the current status of all archived log files.

RMAN> crosscheck archivelog all;

RMAN will mark the deleted backups as EXPIRED, but their records are not removed from the catalog automatically, this is because some DBA might move these backups back to the original destination at a later time. Their status will be back to AVAILABLE again.

Now, the database knows that the space is freed up, it should be no more ORA-00257. If the database is still unresponsive, you can decide to delete these records.

Delete Expired Archive Logs

Delete the records of non-existent archived log files from the catalog.

RMAN> delete expired archivelog all;

Same procedure can apply to the following error: How to Resolve ORA-19809 Limit Exceeded for Recovery Files

Ошибка ORA-00257: archiver error. Connect internal only, until freed

ORA-00257: archiver error. Connect internal only, until freed. (Ошибка архиватора. Не могу подсоедениться пока занят ресурс)

Эта ошибка может быть вызвана несколькими причинами:

1. Закончилось место на дисковом томе, куда пишутся архивные логи

Для начала нужно понять, куда пишутся архивлоги. Для этого возьмем значения следующих параметров в представлении V$PARAMETER:

• LOG_ARCHIVE_DEST (Устаревший, используется для БД редакции не Enterprise)
• LOG_ARCHIVE_DEST_n
• DB_RECOVERY_FILE_DEST. Этот параметр используется, если не установлено значение для любого параметра LOG_ARCHIVE_DEST_n, либо если для параметра LOG_ARCHIVE_DEST_1 установлено значение USE_DB_RECOVERY_FILE_DEST.

И проверим, по каким из путей нет дискового пространства. Для этого можно воспользоваться командой df -Pk. Далее либо чистим место, на тех томах, где пространство занято на 100 процентов, либо командой ALTER изменяем том на который пишутся архивлоги.

Варианты минимизации ошибок:

1. Если используется параметр LOG_ARCHIVE_DEST, то можно указать дополнительно параметры LOG_ARCHIVE_DUPLEX_DEST и LOG_ARCHIVE_MIN_SUCCEED_DEST.

• LOG_ARCHIVE_DUPLEX_DEST — в этом параметре указываем каталог на дисковом томе, отличном от используемого в параметре LOG_ARCHIVE_DEST.
• LOG_ARCHIVE_MIN_SUCCEED_DEST — значение этого параметра указываем равным 1. В этом случае, если том указанный в LOG_ARCHIVE_DEST будет заполнен на 100 процентов, но при этом архивлог будет записан в каталог указанный в LOG_ARCHIVE_DUPLEX_DEST, мы не получим ошибку.

И перезапускаем БД.

2. Если используются параметры LOG_ARCHIVE_DEST_n. В данном случае нам может помочь опция ALTERNATE этого параметра. В случае, если недоступен путь для архивирования лог файла, то архивирование идет по альтернативно указанному пути:

3. Если используется параметр DB_RECOVERY_FILE_DEST, желательно перейти на использование LOG_ARCHIVE_DEST_n.

Для просмотра текущих путей копирования архивлогов можно воспользоваться следующим представлением: V$ARCHIVE_DEST.

2. Закончилось место выделенное под FRA

Если архивлоги настроены на запись в DB_RECOVERY_FILE_DEST_SIZE, то можно так же словить сообщение ORA-00257, в alert.log при этом будет сообщение с ошибкой ORA-19815:

Тут же дают и варианты решения:

1. Изменить политику удержания и удаления архивлогов rman.
2. Сделать бекап архивлогов на ленту с удалением с диска.
3. Добавить дисковое пространство во FRA командой: ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 1024g SCOPE=both;
4. Удалить ненужные архивлоги командами RMAN:

При удалении архивлогов можем получить ошибку:

Это означает, что rman не может найти файлы для удаления. Тут же нам предлагают воспользоваться командой CROSSCHECK перед удалением: CROSSCHECK ARCHIVELOG ALL;

Чтобы избежать возникновения ошибки переполнения места выделенного под FRA используем для архивлогов параметр LOG_ARCHIVE_DEST_n.

Проверить насколько заполнена FRA можно следующей командой:

Источник

How to Resolve ORA-00257: archiver error. Connect internal only, until freed.

ORA-00257

A scheduler job which is refreshing materialized views threw an error ORA-00257 in the alert log like this:

ORA-12012: error on auto execute of job 78450
ORA-00257: archiver error. Connect internal only, until freed.
.

Rationale on ORA-00257

The first action we should take is to check all the archived log destinations LOG_ARCHIVE_DEST_n or DB_RECOVERY_FILE_DEST to make sure they have enough space. If they are in good conditions, the possible causes could be from the remote database that is connected by local database links for refreshing local materialized views. Either local or remote database could cause the error, you should release some space to make the database move.

Solutions to ORA-00257

1. Remove ALL Archived Logs in RMAN

If you’re in an urgent situation, you can delete all archived logs without asking anything.

$ rman target /
.
RMAN> delete noprompt archivelog all;

This RMAN command will delete all archived logs without prompting you the confirmation. That is to say, we keep none of online archived logs.

2. Remove Some Archived Logs in RMAN

The following command will keep archived logs only latest 3 days.

RMAN> delete archivelog until time ‘sysdate — 3’;

The following command will keep archived logs only 1 hour.

RMAN> delete archivelog until time ‘sysdate — 1/24’;

The following command will keep archived logs only 5 minutes.

RMAN> delete archivelog until time ‘sysdate — 5/1440’;

3. Remove Some Archived Logs at OS-level

Sometimes, you might be not able to access RMAN in the first place, to free up the space of archived log destinations manually, you can refer to following steps in order to solve ORA-00257.

Find Candidates

List and make sure all the target files are available to move or delete.

For example, we’d like to keep the files newer than 7 days. The following Unix command find will list 7 and 7+ days old files.

$ cd /path/to/archived_log_destination
$ find . -mtime +6 -exec ls -l <> ;

Please make sure the listed file are allowed to be moved or deleted. Furthermore, you should make sure the standby databases have received or applied the archived logs.

Remove Files

Remove all the target files.

$ find . -mtime +6 -exec rm <> ;

So far, the database is not aware of the resulting free space that you just did on OS-level. So we need to notify the database.

Connect RMAN

Connect to the backup database.

$ rman target / catalog sys/password@backupdb

Find Expired Archive Logs

Notify RMAN to check the current status of all archived log files.

RMAN> crosscheck archivelog all;

RMAN will mark the deleted backups as EXPIRED , but their records are not removed from the catalog automatically, this is because some DBA might move these backups back to the original destination at a later time. Their status will be back to AVAILABLE again.

Now, the database knows that the space is freed up, it should be no more ORA-00257. If the database is still unresponsive, you can decide to delete these records.

Delete Expired Archive Logs

Delete the records of non-existent archived log files from the catalog.

RMAN> delete expired archivelog all;

Источник

ORA-00257 archiver error. connect internal only, until freed

I tried to connect to oracle 11g, and got the above error. and found this link,
http://www.dba-oracle.com/sf_ora_00257_archiver_error_connect_internal_only_until_freed.htm
but the current problem is no way to get into SQLPLUS.

Thanks for your help.

Answers

user11017933 wrote:
Hi, all,

I tried to connect to oracle 11g, and got the above error. and found this link,
http://www.dba-oracle.com/sf_ora_00257_archiver_error_connect_internal_only_until_freed.htm
but the current problem is no way to get into SQLPLUS.

Thanks for your help.

Thanks for your reply!
1. tried conn / as sysdba, denied.
2. try to find alert file, but did not find yet. where it is located on windows server.

It should be somehting like Drive:OracleProductadminbdumpalert_SID.log.
I posted it for 10g for 11g it should be similar to what is mentioned by previous poster.

Edited by: Virendra.k.Yadav on Oct 18, 2011 12:31 PM

Thanks.
The alert log is found. and the latest errors :
1. RA-19809: limit exceeded for recovery files
2 ORA-19804: cannot reclaim 32046592 bytes disk space form 4102029312 limit

Also, I used sysdba to get in but got different error:

ORA-12504 TNS: listener was not given the SERICE_NAME in CONNECT_DATA

«Make sure your sqlnet.ora file is having NTS value set for SQLNET.AUTHENTICATION_SERVICES parameter.»

SQLNET.AUTHENTICATION_SERVICES= (NONE) (this is from the file, what should I put for none? thanks)

Edited by: user11017933 on Oct 18, 2011 12:58 PM

In alert log find out value for log_archive_dest_1 and log_archive_format parameter and post values here. You can cut some old files (5-10) from log_archive_dest_1 location put it on other drive and try to connect to database.

Before setting below on command prompt set ORACLE_SID=
put SQLNET.AUTHENTICATION_SERVICES= (NTS) in sqlnet.ora file and try again.

Backup archivelog out of recovery area then delete the archivelog to free the space
or

Источник

How to Troubleshoot and Fix ORA-00257: archiver error

The ORA-00257 error is a very common Oracle database error. The error is basically trying to tell us that we have run out of logical or physical space on our ASM diskgroup, mount, local disk, or db_recovery_file_dest location where our archivelogs are being stored.

When this issue crops up it usually prevents all connections to the database except for admin level access to allow for the DBA to fix the problem. Usually this is easily resolved in most cases and the below steps outline exactly how to fix the problem.

The first step is to determine where the archivelogs are being stored. Once we know the location we will be able to confirm if this is a logical or physical space shortage.

To find out where our archivelogs are being stored let’s log into SQLPLUS and run a few commands.

Note: Depending on configuration, there could be multiple destinations for archivelogs, each one will need to be checked if there are more than one destination set.

From the first command we run here, we can see that the archiver is indeed enabled and the archivelog destination for this particular database is being derived from another parameter called db_recovery_file_dest.

When we look at the db_recovery_file dest parameter we see that the archivelogs are being written to the +RECO diskgroup and the size of that space is 20TB. This means it can hold up to 20TB of space before filling up.

In this particular database we can see that the value of the log_archive_dest parameter is empty because we are using the db_recovery_file_dest parameter to state where the logs are being stored.

If we were using a regular filesystem location on a local disk or mount it might look something like the below.

You will see at least these two parameters on Oracle 10g, 11g, 12c, or 19c. The first parameter ‘db_recovery_file_dest’ is where our archivelogs will be written to and the second parameter is how much logical space we’re allocating for not only those files, though also other files like backups, redo logs, controlfile snapshots, and a few other files that could be created here by default if we don’t specify a specific location.

So now that we know the location(s) of our archivelogs we can now check to see if it’s a logical or physical space issue. For physical this is easy enough as the diskgroup, disk, or mount point where the archivelogs are being stored would be at 100% capacity.

Physical – If it’s a physical issue, we can take one of the below steps to fix the issue

1. Add more space to the mount where the logs are going.
2. Take a backup of the logs via RMAN and have it delete input. (Note: If your backups are going to the same place your archivelogs are going, we will need to take a backup on a different mount point/drive.)
3. Move the archivelog location to another mount/drive/location temporarily while you do one of the above.
4. Delete the archivelogs if we don’t need the ability to restore the database. This could be useful for a dev instance that is refreshed nightly or something similar.

Anyone of the above three will clear the error and allow users to log into the database again almost instantly.

Logical – If it’s a logical issue we simply need to take one of the below actions.

1. increase the db_recovery_file_dest_size to a larger size to allow for more archivelogs to be written to the archivelog location. (Note: Be sure to check the underlying disk/mount point before increasing this parameter to ensure there is proper space on the disk/mount.)
2. As with the physical issue, simply take an RMAN backup of the logs and have it delete input to clear space.
3. Change the db_recovery_file_dest or log_archive_dest to another location that has space
4. Delete the archivelogs if they are not needed for recovery

I would recommend using rman for either the backup or delete methods as this will keep your catalog of backups up to date and clean. Also just to point out, that we if we need to ever recover this database we should be taking regular scheduled RMAN backups with a retention policy that also include archivelogs to help keep the archivelog location free for writing of more logs.

Please comment below if this helped you fix your ORA-00257: archiver error. Connect internal only, until freed issue. If you require more help please contact us to speak with a certified Oracle DBA support expert.

Источник

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

ORA-00257 is one of the commonest error in Oracle DBA life. He/She often has to deal with it.Oracle Database almost got frozen because of it and all transaction get stopped .Let us see how to deal with ORA-00257 error

Error Message

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

As per The oerr ORA-00257 error means

ORA-00257: 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 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.

Where do you see this error message

1) You will see below type of error message pertaining in alert log

Following is some if the information from the alert log:

2) If you try to login with non sysdba user, you will the below error message

3) If you try to login with sysdba user and check the wait event for the session , you will find session waiting log archive switch event
Why ORA-00257 error occurs

This error happens as the target location for archive log is either full or not available. The Oracle ARCH background process is responsible for taking the redo logs from the online redo log file system and writing them to the flat file is not able to write to the filesystem

How to resolve ORA-00257 errors

1) First of all we should find the archive log destination for the database

you can also find archive destinations by either USE_DB_RECOVERY_FILE_DEST

Also get the size of the db_recovery_file_dest

2) The next step in resolving ORA-00257 is to find out what value is being used for db_recovery_file_dest_size, use:

You may find that the SPACE_USED is the same as SPACE_LIMIT, if this is the case, to resolve ORA-00257 should be remedied by moving the archive logs to some other destination.

3) We can have many solutions in these situation

a) Increase the size of db_recovery_file_dest if you have space available in Oracle ASM or filesystem whatever you are using

b) We can delete the archive log which have been already backed up

c) If you have not taken the backup,then it is advise to take backup and then delete the archive log files

d) Sometimes old backup piece ,flashback logs may be occupying space in the db_recovery_file_dest, you check the content of db_recovery_file_dest

Sometimes old guaranteed restore point might be present, Dropping will release the space.

e) If we dont need archive log , then simply deleting will also serve the purpose

Other useful command in this case

Important Note

Please dont delete archive log file manually from the filesystem, it will not update control file and it will not clear the issue. Then you have do crosscheck all in rman and delete obsolete and expired

f) We can specify alternate path for archiving

Archiving is automatically performed to the alternate dest2

4) Once space is available in db_recovery_file_dest, Please check the system by doing alter system switch logfile

Also try connecting with non sysdba user to make sure ,issue is resolved

It is advisable to take regular backup of archive log and delete them. We should put a monitoring script to keep a check on the flash recovery area space.

Источник

This post has been updated from the original content here.

The ORA-00257 error is a very common Oracle database error. The error is basically trying to tell us that we have run out of logical or physical space on our ASM diskgroup, mount, local disk, or db_recovery_file_dest location where our archivelogs are being stored.

When this issue crops up it usually prevents all connections to the database except for admin level access to allow for the DBA to fix the problem. Usually this is easily resolved in most cases and the below steps outline exactly how to fix the problem.

The first step is to determine where the archivelogs are being stored. Once we know the location we will be able to confirm if this is a logical or physical space shortage.

To find out where our archivelogs are being stored let’s log into SQLPLUS and run a few commands.

Note: Depending on configuration, there could be multiple destinations for archivelogs, each one will need to be checked if there are more than one destination set.

From the first command we run here, we can see that the archiver is indeed enabled and the archivelog destination for this particular database is being derived from another parameter called db_recovery_file_dest.

SQL> archive log list
Database log mode          	Archive Mode
Automatic archival         	Enabled
Archive destination        	USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 	8190
Next log sequence to archive   8192
Current log sequence       	8192

When we look at the db_recovery_file dest parameter we see that the archivelogs are being written to the +RECO diskgroup and the size of that space is 20TB. This means it can hold up to 20TB of space before filling up.

SQL> show parameter db_recovery_file

NAME                             	TYPE    	VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest            	string  	+RECO
db_recovery_file_dest_size       	big integer 20T

In this particular database we can see that the value of the log_archive_dest parameter is empty because we are using the db_recovery_file_dest parameter to state where the logs are being stored.

SQL> show parameter log_archive_dest

NAME                             	TYPE    	VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest                 	string

If we were using a regular filesystem location on a local disk or mount it might look something like the below.

SQL> archive log list
Database log mode          	Archive Mode
Automatic archival         	Enabled
Archive destination        	/u02/oracle/db01/archivelogs/
Oldest online log sequence 	8190
Next log sequence to archive   8192
Current log sequence       	8192

SQL> show parameter db_recovery_file

NAME                             	TYPE    	VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest            	string  	/u02/oracle/db01/archivelogs/
db_recovery_file_dest_size       	big integer 20T

You will see at least these two parameters on Oracle 10g, 11g, 12c, or 19c. The first parameter ‘db_recovery_file_dest’ is where our archivelogs will be written to and the second parameter is how much logical space we’re allocating for not only those files, though also other files like backups, redo logs, controlfile snapshots, and a few other files that could be created here by default if we don’t specify a specific location.

SQL> show parameter log_archive_dest
NAME                             	TYPE    	VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest                	 string  	/u02/oracle/db01/archivelogs/

So now that we know the location(s) of our archivelogs we can now check to see if it’s a logical or physical space issue. For physical this is easy enough as the diskgroup, disk, or mount point where the archivelogs are being stored would be at 100% capacity.

Physical – If it’s a physical issue, we can take one of the below steps to fix the issue

1. Add more space to the mount where the logs are going.
2. Take a backup of the logs via RMAN and have it delete input. (Note: If your backups are going to the same place your archivelogs are going, we will need to take a backup on a different mount point/drive.)
3. Move the archivelog location to another mount/drive/location temporarily while you do one of the above.
4. Delete the archivelogs if we don’t need the ability to restore the database. This could be useful for a dev instance that is refreshed nightly or something similar.

Anyone of the above three will clear the error and allow users to log into the database again almost instantly.

Logical – If it’s a logical issue we simply need to take one of the below actions.

1. increase the db_recovery_file_dest_size to a larger size to allow for more archivelogs to be written to the archivelog location. (Note: Be sure to check the underlying disk/mount point before increasing this parameter to ensure there is proper space on the disk/mount.)
2. As with the physical issue, simply take an RMAN backup of the logs and have it delete input to clear space.
3. Change the db_recovery_file_dest or log_archive_dest to another location that has space
4. Delete the archivelogs if they are not needed for recovery

I would recommend using rman for either the backup or delete methods as this will keep your catalog of backups up to date and clean. Also just to point out, that we if we need to ever recover this database we should be taking regular scheduled RMAN backups with a retention policy that also include archivelogs to help keep the archivelog location free for writing of more logs.

Please comment below if this helped you fix your ORA-00257: archiver error. Connect internal only, until freed issue. If you require more help please contact us to speak with a certified Oracle DBA support expert.