ORA-00257: archiver error. connect as sysdba only until resolved

Sometimes you can get ” ora-00257: archiver error. connect as sysdba only until resolved  ” error, while connecting to Oracle database.

 

ORA-00257: archiver error. connect as sysdba only until resolved

Details of error are as follows.

 

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

00257, 00000, "Archiver error. Connect AS SYSDBA only until resolved."
// *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 that the destination device is out of space to store the
// redo log file. Another possible cause is that a destination marked
// as MANDATORY has failed.
// *Action: Check the alert log and trace files for detailed error
// information.

 

 

 

ORA-00257 archiver error. connect as sysdba

Here is output of Alertlog

Errors in file /u01/app/oracle/diag/rdbms/MSD/MSD/trace/MSD_tt00_12641.trc:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 53477376 bytes disk space from 107374182400 bytes limit
2020-01-27T13:45:36.261480+03:00
Errors in file /u01/app/oracle/diag/rdbms/MSD/MSD/trace/MSD_tt00_12641.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 107374182400 bytes is 100.00% used, and has 0 remaining bytes available.
2020-01-27T13:45:36.261565+03:00
************************************************************************
You have following choices to free up space from recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
system command was used to delete files, then use RMAN CROSSCHECK and
DELETE EXPIRED commands.
************************************************************************
2020-01-27T13:45:36.262505+03:00
Errors in file /u01/app/oracle/diag/rdbms/MSD/MSD/trace/MSD_tt00_12641.trc:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 53477376 bytes disk space from 107374182400 bytes limit

 

 

 

Archiver error connect as sysdba

If you got this ORA-00257: archiver error, it means Archivelog and Redolog file destination disk is out of space to store these files.

You have 2 option to solve this problem.

 

Firstly, connect RMAN and Delete Old archivelogs if you have already backed up them Or you don’t need their backups.

Use delete archivelog command to delete Archivelogs, you can delete them until time sysdate -1 ( Keep Last 1 day Archivelog, delete older than 1 day ) or sysdate-1/8 ( Keep Last 3 hours Archivelogs, delete older than 3 hours ) according to your need.

 

delete noprompt force archivelog until time 'sysdate -1';

[MSD]/home/oracle $ rman target /

Recovery Manager: Release 18.0.0.0.0 - Production on Mon Jan 27 13:58:09 2020
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.

connected to target database: MSD (DBID=1475860412)

RMAN> delete noprompt force archivelog until time 'sysdate -1';

 

If you cannot delete archivelogs, then you need to increase the size of either Recovery_dest_size or Log_archive_dest_size as follows.

 

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

SQL> show parameter recovery

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string +DATA
db_recovery_file_dest_size big integer 100G
recovery_parallelism integer 0
remote_recovery_file_dest string
SQL>
SQL>
SQL> alter system set db_recovery_file_dest_size=200G scope=both sid='*';

System altered.

 

Problem will be solved.

 

 

 

 

 

Do you want to learn Oracle Database for Beginners, then Click and read the following articles.

Oracle Database Tutorials for Beginners ( Junior Oracle DBA )