ORA-00257

Cannot connect to database (ORA-00257: Archiver error. Connect AS SYSDBA only until resolved. )

Error : Cannot connect to database (ORA-00257: 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 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.alter database flashback on

There is two possible way to solution.
1. without increasing DB_RECOVERY_FILE_DEST_SIZE.
2. by increasing DB_RECOVERY_FILE_DEST_SIZE.
3. archivelog mode disable

Without increasing DB_RECOVERY_FILE_DEST_SIZE.

1. Check whether the database is in archive log mode and automatic archiving is enabled.

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

2. If archive destination is defined by USE_DB_RECOVERY_FILE_DEST, find the archive destination by:

SQL> show parameter db_recovery_file_dest;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      C:\oracle\product\10.2.0/flash_recovery_area
db_recovery_file_dest_size           big integer 2G
Check what the value for db_recovery_file_dest_size.

3. Find the space used in flash recovery area by using following SQL:

col ROUND(SPACE_LIMIT/1048576) heading “Space Allocated (MB)” format 999999
col round(space_used/1048576) heading “Space Used (MB)” format 99999
col name format a40
select name, round(space_limit/1048576) As space_limit,round(space_used/1048576) As space_used
from v$RECOVERY_FILE_DEST;

4. if SPACE_USED is equal to SPACE_LIMIT of db_recovery_file_dest, move the archive logs to different destination.

5. Archive all the log files

SQL> alter system archive log all;
6. Just switch the logs to verify

 SQL> alter system switch logfile;

7. DB_RECOVERY_FILE_DEST_SIZE is to delete (archive log) files from DB_RECOVERY_FILE_DEST if you are sure you have backups and the archived logs
are no longer necessary.

 $rman target /
 RMAN>delete archivelog until time 'SYSDATE-1';
 or,
 RMAN>delete archivelog all;

By increasing DB_RECOVERY_FILE_DEST_SIZE.

1. See the path of flash recovery area.

 SQL> show parameter db_recovery_file_dest;

2. Increase the Flash Recovery Area

SQL> ALTER SYSTEM SET db_recovery_file_dest_size='50G' SCOPE=BOTH;
Sytem Altered.



[oracle@oraprod pfile]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Feb 22 16:22:35 2018

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 1.5032E+10 bytes
Fixed Size                 12349192 bytes
Variable Size            2348810488 bytes
Database Buffers         1.2616E+10 bytes
Redo Buffers               54759424 bytes
Database mounted.
SQL> alter database clear unarchived logfile group 1;

Database altered.

SQL> alter database clear unarchived logfile group 2;

Database altered.

SQL> alter database clear unarchived logfile group 3;

Database altered.

SQL> shutdown immediate

ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1.5032E+10 bytes
Fixed Size                 12349192 bytes
Variable Size            2348810488 bytes
Database Buffers         1.2616E+10 bytes
Redo Buffers               54759424 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL>
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PROD                         MOUNTED

SQL> alter pluggable database PROD open;

Pluggable database altered.

********************************************************************

archivelog mode disable

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

[oracle@oraprod ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Mar 20 16:52:35 2018

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to an idle instance.

SQL>
SQL> startup mount
ORACLE instance started.

Total System Global Area 1.5032E+10 bytes
Fixed Size                 12349192 bytes
Variable Size            2348810488 bytes
Database Buffers         1.2616E+10 bytes
Redo Buffers               54759424 bytes
Database mounted.

SQL> alter database noarchivelog;

Database altered.

SQL>
SQL>
SQL> alter database open;

Database altered.