Skip to content

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

16/11/2012

When connecting to a database via SQL*Plus (similar error would be displayed via other products) the following error is thrown:
SQL*Plus: Release 10.2.0.3.0 – Production on Fri Nov 16 10:15:15 2012

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

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

Cause:
The area used as the Archive Destination is out of space.

Resolution:
Identify the destination:

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

Here we can see that the archive destination is set to USE_DB_RECOVERY_FILE_DEST so we can find this by checking the system parameter.

SQL> select * from v$recovery_file_dest;

NAME
——————————————————————————–
SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
———– ———- —————– —————
/db03/OraFlash
1.6106E+11 1.6104E+11 0 3642

So, /db03/OraFlash is where the problem is and we can see that the SPACE_USED is the same as SPACE_LIMIT.

SQL> show parameter recovery;
NAME TYPE VALUE
———————————— ———– ——————————
db_recovery_file_dest string /db03/OraFlash
db_recovery_file_dest_size big integer 150G
recovery_parallelism integer 0

This shows me that I have allocated 150G to the recovery area.

By checking the available space on the mountpoint where the destination is you are able to determine whether there is sufficient space to increase the size of the recovery destination.

In my case I had sufficient space available so I increased the size:

SQL> alter system set db_recovery_file_dest_size = 180G SCOPE=both;

System altered.

Checking the system again confirms that the space is now available:

SQL> select * from v$recovery_file_dest;

NAME
——————————————————————————–
SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
———– ———- —————– —————
/db03/OraFlash
1.9327E+11 1.6126E+11 0 3647

If you do not have additional space available to increase the size of the recovery destination then it will be necessary to move the archive logs to a new location.

SQL> ALTER SYSTEM SET db_recovery_file_dest=’/db02/logs’ SCOPE=spfile;

System altered.

It is then necessary to restart the database instance. Once the database is available check the value has changed:

SQL> SHOW PARAMETER db_recovery_file_dest;
NAME TYPE VALUE
———————————— ———– ——————————
db_recovery_file_dest string /db02/logs
db_recovery_file_dest_size big integer 150G

After doing this you need to archive the log files:

SQL> alter system archive log all;

Finally, verify the change by changing the log files:

SQL> alter system switch logfile;

Advertisements

From → ORA Errors, Oracle

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: