When in production, you should run Oracle in Archivelog mode, multiplex redo log files and make regular backups of your archived logs. In this post, we’ll present such files, how to enable archivelod mode and the connect internal only error.
Redo log files
Oracle DBMS stores redo records required for instance recovery into two or more preallocated files called redo log files. Such redo records consist of all changes made to the database and are written into redo files as they occur. Redo log files are also useful to update standby databases and to provide history information about the database. You can use utilities like LogMiner to analyze redo log files’ contents.
Due to the criticity of its data, you should organize redo log files in groups and also multiplex them. When you multiplex redo log files, the system maintains identical copies of them in separate locations, what would be specially interesting in case of disks failures.
Hence, planning the number, organization and location of redo logs is crucial for disaster recovery, availability and performance.
Archivelog mode and archived redo logs
When a redo log file (or group) is full, Oracle writes information in the next redo log available. Then, when its full, Oracle uses the next file (or group) and so on. If the instance is running in Archivelog mode (which is highly recomendable if you want to reduce data loss in case of a failure or disaster), Oracle must archive (i.e. create a copy of its data elsewhere) a redo log before it can reuse the log file.
Oracle automatically creates archived redo logs (or simply archive log) when running in Archivelog mode. You would use archived redo logs together with the last database recovery and with the online redo log to recover the database in case of a disaster or failure. See more here.
You can verify if your database is in archivelog mode using the archive log list command or querying the V$Database view:
To enable Archivelog mode you should mount the database whithout opening it. Also, reset the parameter db_recovery_file_dest. and specify log_archive_dest (in SQL Plus, use alter system set log_archive_dest=’ … ‘ ). In the following, how to enable using the alter database command:
Connect internal only Error
If you get ORA-00257: archiver error. Connect internal only, until freed, then the archive process received an error when trying to archive a redo log. Usually, the destination location that stores your archived logs got out of space. So, as the destination of archived logs is full, Oracle does not archive redo log files.
Hence, as Oracle cannot reuse a redo log when running in Archivelog mode until the log is archived, Oracle stops accepting user transactions and you get ORA-00257. Only users with admin privileges can log into the database.
To solve the error, the first step is to find where Oracle is storing the archive logs. Look for the parameter log_archive_dest (in SQL Plus, use show parameter log_archive_dest). If such parameter is empty, then look for db_recovery_file_dest.
Then, you should verify is there is physical space available in the location used to store archive logs and clean the location removing (or moving) old files that would not be required to recover the database. Use RMAN to maintain your catalog up to date.
You may also get ORA-00257 if you run out of logical space reserved to store archive logs (and some other backup files). Such logical space can be verified in the db_recovery_file_dest_size parameter.
But don’t forget: if you get ORA-00257, then there would be an error when planning file and space allocation, or there may be a failure in your backup and clean routine.