ORA-00257
A scheduler job which is refreshing materialized views threw an error ORA-00257 in the alert log like this:
ORA-12012: error on auto execute of job 78450
ORA-00257: archiver error. Connect internal only, until freed.
...
Rationale on ORA-00257
The first action we should take is to check all the archived log destinations LOG_ARCHIVE_DEST_n or DB_RECOVERY_FILE_DEST to make sure they have enough space. If they are in good conditions, the possible causes could be from the remote database that is connected by local database links for refreshing local materialized views. Either local or remote database could cause the error, you should release some space to make the database move.
Solutions to ORA-00257
1. Remove ALL Archived Logs in RMAN
If you're in an urgent situation, you can delete all archived logs without asking anything.
$ rman target /
...
RMAN> delete noprompt archivelog all;
This RMAN command will delete all archived logs without prompting you the confirmation. That is to say, we keep none of online archived logs.
2. Remove Some Archived Logs in RMAN
The following command will keep archived logs only latest 3 days.
RMAN> delete archivelog until time 'sysdate - 3';
The following command will keep archived logs only 1 hour.
RMAN> delete archivelog until time 'sysdate - 1/24';
The following command will keep archived logs only 5 minutes.
RMAN> delete archivelog until time 'sysdate - 5/1440';
3. Remove Some Archived Logs at OS-level
Sometimes, you might be not able to access RMAN in the first place, to free up the space of archived log destinations manually, you can refer to following steps in order to solve ORA-00257.
Find Candidates
List and make sure all the target files are available to move or delete.
For example, we'd like to keep the files newer than 7 days. The following Unix command find will list 7 and 7+ days old files.
$ cd /path/to/archived_log_destination
$ find . -mtime +6 -exec ls -l {} ;
Please make sure the listed file are allowed to be moved or deleted. Furthermore, you should make sure the standby databases have received or applied the archived logs.
Remove Files
Remove all the target files.
$ find . -mtime +6 -exec rm {} ;
So far, the database is not aware of the resulting free space that you just did on OS-level. So we need to notify the database.
Connect RMAN
Connect to the backup database.
$ rman target / catalog sys/password@backupdb
Find Expired Archive Logs
Notify RMAN to check the current status of all archived log files.
RMAN> crosscheck archivelog all;
RMAN will mark the deleted backups as EXPIRED, but their records are not removed from the catalog automatically, this is because some DBA might move these backups back to the original destination at a later time. Their status will be back to AVAILABLE again.
Now, the database knows that the space is freed up, it should be no more ORA-00257. If the database is still unresponsive, you can decide to delete these records.
Delete Expired Archive Logs
Delete the records of non-existent archived log files from the catalog.
RMAN> delete expired archivelog all;
Same procedure can apply to the following error: How to Resolve ORA-19809 Limit Exceeded for Recovery Files