ORA-00257

Posted on | Edited on | In

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

  1. login in sys
    sqlplus sys/pass@tt as sysdba

  2. find archiv log position
    SQL> show parameter log_archive_dest;

  3. if value is null, check directory
    SQL> archive log list;

  4. check flash recovery area use condition
    SQL> select * from V$FLASH_RECOVERY_AREA_USAGE;

  5. find recovery directory
    SQL> show parameter recover;

  6. remove log file in no use.
    caution: reserver file remain in time.

  7. rman maintain
    rman target sys/pass

  8. check archivelog in no use
    RMAN> crosscheck archivelog all;

  9. delete expired file in db
    RMAN> delete expired archivelog all;
    RMAN> delete archivelog until time 'sysdate-1';

  10. check again
    SQL> select * from V$FLASH_RECOVERY_AREA_USAGE;

Others

if archive log model can’t startup,
recover to noarchive log startup then shutdown.

1
2
3
4
5
shutdown immediate;
startup mount;
alter database noarchivelog;
alter database open;
shutdown immediate;

turn to archive log again

1
2
3
4
5
6
shutdown immediate;
startup mount;
show parameter log_archive_dest;
alter database archivelog;
archive log list;
alter database open;

If still can’t modify startup.
Try to delete more log or expand space.

1
2
3
4
5
6
7
8
9
10
11
12
13
## check log group
SQL> select group#,sequence# from v$log;

## clean more space
SQL> alter database clear unarchived logfile group 1;
alter database open;

## change storage position
select name from v$datafile;
alter system set log_archive_dest='/opt/app/oracle/oradata/usagedb/arch' scope=spfile

## expand space
SQL> alter system set db_recovery_file_dest_size=3G scope=both;