One of the common mistakes in the Oracle database is ORA-00257, which occurs when you run out of logical or physical space to store archive logs. When this issue arises, all connections to the database are blocked, except for users with administrator-level access who can resolve the error.
The ORA-00257 archiver error occurs when attempting to transfer to an archive log file, and the local disk, ASM disk group, or db_recovery_file_dest location has run out of storage space. This issue is typically accompanied by the message "archiver error, connect internal only until freed."
Archiver Error Tips
Before addressing the problem, you must determine whether your database uses logical or physical storage space. The resolution steps for ORA-00257 will depend on this distinction.
To find the exact location where archives are stored, run the SQL command: "show parameter recovery." This will display the necessary parameters. If you're using Oracle 10g, 11g, or 12c, pay attention to at least two of them:
⚫️ db_recovery_file_dest: Specifies where files are saved.
⚫️ db_recovery_file_dest_size: Indicates the available space for archive logs, backups, and other files.
You can resolve the issue once you identify the location and check the available memory in the database.
How to Resolve and Fix
If a physical issue causes the ORA-00257 archiver error, you can take one of the following actions:
✅ Add more space to the mount where logs are stored.
✅ Move the backup archive logs to another disk or mount point if they are in the same space as the logs.
✅ Delete archive logs that won't be used for recovery.
Note: If you don't need archive logs for recovery and want to delete them, use RMAN for log file backup. Installing this utility allows you to remove unnecessary archive logs, keeping the backup catalog clean. You can also set up scheduled backups with storage options to avoid overloading the space and to write only the necessary logs.
If a logical problem causes the ORA-00257 error, you can resolve it through the following methods:
✅ Increase the size of db_recovery_file_dest_size after checking the available space on the base disk or mount point.
✅ Create backup archive logs using RMAN and delete unnecessary files.
✅ Change db_recovery_file_dest or log_archive_dest to a space with available memory.
✅ Delete archive logs that are not needed for recovery.
By using one of these methods, you will free up space and resolve the error. As a result, access to the Oracle database will be restored for non-administrator users, allowing your company to continue its operations smoothly.
Conclusion
ORA-00257 archiver error. connect as sysdba only until resolved is a common issue in the Oracle database that arises due to insufficient space and is displayed when attempting to archive logs. However, it can be easily resolved by determining where files are stored and clearing physical or logical space.
If you encounter difficulties in resolving this error, DB Serv specialists will assist you promptly in resolving it and restoring the functionality of the Oracle database.
DBserv > Blog > ORA-00257 Archiver Error: How to Troubleshoot and Fix