Solution to ORA-00257 archive logs being too large to store

ORA-00257 archiving log error Solution

In the actual project encountered a ORA-00257 error (insufficient space error), by looking for information, the vast majority of said this is due to archiving too many logs, occupying all the hard disk space caused, you can simply delete logs or increase storage space.

Diagnosis process:

(1) view the REDOLOG status of the database:

SQL> conn/@ dmsasc as sysdba

SQL> select * from v $ log;
If the ARC field value is NO, the system cannot perform automatic archiving.

Check the archive directory and log sequence:

SQL> archive log list;

(2) manually switch logs:

SQL> alter system switch logfile;

ORA-01013: user request to cancel current action

After waiting for a long time for no response, the operation is interrupted, and the manual switch log is not successful.

(3) view the space usage of FLASH_RECOVERY_AREA:

SQL> select * from v $ recovery_file_dest;

Computing space occupied by flash recovery area:

SQL> select sum (percent_space_used) * 3/100 from v $ flash_recovery_area_usage;

FLASH_RECOVERY_AREA space 2.35 GB

SQL> select * from v $ flash_recovery_area_usage;
The percentage of ARCHIVELOG space used is found to be nearly 93.96%, so that the FLASH_RECOVERY_AREA space is fully occupied.

(4) Find the recovery directory:

SQL> show parameter recover;

Solution Process:

(1) Change the FLASH_RECOVERY_AREA space to 20 GB based on the actual storage space of the database being 200 GB and 8 GB.

SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE = 20g;

SQL> select * from v $ recovery_file_dest;

(2) Check the log status and find that the REDOLOG is in the normal archiving status: ARC YES

SQL> select * from v $ log;
SQL> select * from v $ flash_recovery_area_usage;

(3) transfer or clear the corresponding archive log, delete some files in the unused date directory, pay attention to keep the last few files, note that after deleting the archive log, you must use RMAN to maintain the control file, otherwise, the space will not be released.

Rman target sys/XXXX @ XXX (database username/password)

RMAN> crosscheck archivelog all; // check for useless archivelog
RMAN> delete expired archivelog all; // delete an expired Archive

Delete archivelog until time 'sysdate-1'; // delete All archivelog

(4) Check again and find that the usage is normal:

SQL> select * from v $ flash_recovery_area_usage;

(5) Restart Oracle (note that the startup cannot be started normally in archive log Mode)

SQL> shutdown immediate;

SQL> startup mount;

SQL> alter database noarchivelog;
SQL> alter database open;
SQL> shutdown immediate;

SQL> startup mount;
SQL> show parameter log_archive_dest;

SQL> alter database archivelog; // restore to archive log Mode
SQL> archive log list;
SQL> shutdown immediate;

SQL> startup;

SPFILE error causing database startup failure (ORA-01565)

ORA-01172, ORA-01151 error handling

ORA-00600 [2662] troubleshooting

Troubleshooting for ORA-01078 and LRM-00109

Notes on ORA-00471 Processing Methods