Recall from the discussion earlier in my blog that archive redo logs are created only if your Oracle database 12C is in archivelog mode. If you want to preserve your database transaction history to facilitate point-in-time and other types of recovery, you need to enable that mode.
In normal operation, changes to your data generate entries in the database redo log files. As each online redo log group fills up, a log switch is initiated. When a log switch occurs, the log writer process stops writing to the most recently filled online redo log group and starts writing to a new online redo log group. The online redo log groups are written to in a round-robin fashion—meaning the contents of any given online redo log group will eventually be overwritten. Archivelog mode preserves redo data for the long term by employing an archiver background process to copy the contents of a filled online redo log to what is termed an archive redo log file. The trail of archive redo log files is crucial to your ability to recover the Oracle database 12C with all changes intact, right up to the precise point of failure.
Making Architectural Decisions
When you implement archivelog mode, you also need a strategy for managing the archived log files. The archive redo logs consume disk space. If left unattended, these files will eventually use up all the space allocated for them. If this happens, the archiver can’t write a new archive redo log file to disk, and your Oracle database 12C will stop processing transactions. At that point, you have a hung database. You then need to intervene manually by creating space for the archiver to resume work. For these reasons, there are several architectural decisions you must carefully consider before you enable archiving:
- Where to place the archive redo logs and whether to use the FRA to store them
- How to name the archive redo logs
- How much space to allocate to the archive redo log location
- How often to back up the archive redo logs
- When it’s okay to permanently remove archive redo logs from disk
- How to remove archive redo logs (e.g., have RMAN remove the logs, based on a retention policy)
- Whether multiple archive redo log locations should be enabled
- (When to schedule the small amount of downtime that’s required (if a production database)
As a general rule of thumb, you should have enough space in your primary archive redo location to hold at least a day’s worth of archive redo logs. This lets you back them up on a daily basis and then remove them from disk after they’ve been backed up.
If you decide to use a FRA for your archive redo log location, you must ensure that it contains sufficient space to hold the number of archive redo logs generated between backups. Keep in mind that the FRA typically contains other types of files, such as RMAN backup files, flashback logs, and so on. If you use a FRA, be aware that the generation of other types of files can potentially impact the space required by the archive redo log files.
You need a strategy for automating the backup and removal of archive redo log files. For user-managed backups, this can be implemented with a shell script that periodically copies the archive redo logs to a backup location and then removes them from the primary location. As you will see in later in my blog, RMAN automates the backup and removal of archive redo log files.
If your business requirements are such that you must have a certain degree of high availability and redundancy, then you should consider writing your archive redo logs to more than one location. Some shops set up jobs to copy the archive redo logs periodically to a different location on disk or even to a different server.
Setting the Archive Redo File Location
Before you set your database mode to archiving, you should specifically instruct Oracle where you want the archive redo logs to be placed. You can set the archive redo log file destination with the following techniques:
- Set the LOG_ARCHIVE_DEST_N database initialization parameter.
- Implement a FRA.
These two approaches are discussed in detail in the following sections.
Tip If you don’t specifically set the archive redo log location via an initialization parameter or by enabling the FRA, then the archive redo logs are written to a default location. For Linux/Unix the default location is ORACLE_HOME/dbs. For Windows the default location is ORACLE_HOME\database. For active production database systems, the default archive redo log location is rarely appropriate.
Setting the Archive Location to a User-Defined Disk Location (non-FRA)
If you’re using an init<SID>.ora file, modify the file with an OS utility (such as vi). In this example the archive redo log location is set to /u01/oraarch/O12C:
log_archive_dest_1='location=/u01/oraarch/O12C'
log_archive_format='O12C_%t_%s_%r.arc'In the prior line of code, my standard for naming archive redo log files includes the ORACLE_SID (in this example, O12C to start the string); the mandatory parameters %t, %s, and %r; and the string .arc, to end. I like to embed the name of the ORACLE_SID in the string to avoid confusion when multiple databases are housed on one server. I like to use the extension .arc to differentiate the files from other types of database files.
Tip If you don’t specify a value for LOG_ARCHIVE_FORMAT, Oracle uses a default, such as %t_%s_%r.dbf. One aspect of the default format that I don’t like is that it ends with the extension .dbf, which is widely used for data files. This can cause confusion about whether a particular file can be safely removed because it’s an old archive redo log file or shouldn’t be touched because it’s a live data file. Most DBAs are reluctant to issue commands such as rm *.dbf for fear of accidentally removing live data files.
If you’re using an spfile, use ALTER SYSTEM to modify the appropriate initialization variables:
SQL> alter system set log_archive_dest_1='location=/u01/oraarch/O12C' scope=both;
SQL> alter system set log_archive_format='O12C_%t_%s_%r.arc' scope=spfile;You can dynamically change the LOG_ARCHIVE_DEST_n parameters while your Oracle database 12C is open. However, you have to stop and start your database for the LOG_ARCHIVE_FORMAT parameter to take effect.
RECOVERING FROM SETTING A BAD SPFILE PARAMETER
Take care not to set the LOG_ARCHIVE_FORMAT to an invalid value; for example,
SQL> alter system set log_archive_format='%r_%y_%dk.arc' scope=spfile;
If you do so, when you attempt to stop and start your database, you won’t even get to the nomount phase (because the spfile contains an invalid parameter):
SQL> startup nomount;
ORA-19905: log_archive_format must contain %s, %t and %rIn this situation, if you’re using an spfile, you can’t start your instance. The easiest thing to do at this point is to create a text based init.ora file from the contents of the spfile. You can use the Linux/Unix strings command to accomplish this:
$ cd $ORACLE_HOME/dbs
$ strings spfile$ORACLE_SID.oraThe prior command will extract the text out of the binary spfile and display it on your screen. You can then cut and paste that text into an init.ora file and use that to start your Oracle database 12C. If you’re using Windows, you can use a utility such as write.exe to display the text in a binary file.
When you specify LOG_ARCHIVE_FORMAT, you must include %t (or %T), %s (or %S), and d% in the format string. Table 1 lists the valid variables you can use with the LOG_ARCHIVE_FORMAT initialization parameter.
Table 1. Valid Variables for the Log Archive Format String
Format String | Meaning |
|---|---|
| Log sequence number |
| Log sequence number padded to the left with zeros |
| Thread number |
| Thread number padded to the left with zeros |
| Activation ID |
| Database ID |
| Resetlogs ID required to ensure uniqueness across multiple incarnations of the database |
You can view the value of the LOG_ARCHIVE_DEST_N parameter by running the following:
SQL> show parameter log_archive_destHere is a partial listing of the output:
NAME TYPE VALUE
---------------------- ----------- --------------------------
log_archive_dest string
log_archive_dest_1 string location=/u01/oraarch/O12C
log_archive_dest_10 stringFor Oracle 11g and higher you can enable up to 31 different locations for the archive redo log file destination. For most production systems one archive redo log destination location is usually sufficient. If you need a higher degree of protection, you can enable multiple destinations. Keep in mind that when you use multiple destinations, the archiver must be able to write to at least one location successfully. If you enable multiple mandatory locations and set LOG_ARCHIVE_MIN_SUCCEED_DEST to be higher than 1, then your Oracle database 12C may hang if the archiver can’t write to all mandatory locations.
You can check the details regarding the status of archive redo log locations via this query:
SQL> select dest_name, destination, status, binding from v$archive_dest;
DEST_NAME DESTINATION STATUS BINDING
-------------------- -------------------- --------- ---------
LOG_ARCHIVE_DEST_1 /u01/archive/O12C VALID OPTIONAL
LOG_ARCHIVE_DEST_2 INACTIVE OPTIONAL
...Using the FRA for Archive Log Files
The FRA is an area on disk—specified via database initialization parameters—that can be used to store files, such as archive redo logs, RMAN backup files, flashback logs, and multiplexed control files and online redo logs. To enable the use of a FRA, you must set two initialization parameters (in this order):
- DB_RECOVERY_FILE_DEST_SIZE specifies the maximum space to be used for all files that are stored in the FRA for a database.
- DB_RECOVERY_FILE_DEST specifies the base directory for the FRA.
When you create a FRA, you’re not really creating anything—you’re telling Oracle which directory to use when storing files that go in the FRA. For example, say 200GB of space are reserved on a mount point, and you want the base directory for the FRA to be /u01/fra. To enable the FRA, first set DB_RECOVERY_FILE_DEST_SIZE:
SQL> alter system set db_recovery_file_dest_size=200g scope=both;Next, set the DB_RECOVERY_FILE_DEST parameter:
SQL> alter system set db_recovery_file_dest='/u01/fra' scope=both;If you’re using an init.ora file, modify it with an OS utility (such as vi) with the appropriate entries.
After you enable a FRA, by default, Oracle writes archive redo logs to subdirectories in the FRA.
Note If you’ve set the LOG_ARCHIVE_DEST_N parameter to be a location on disk, archive redo logs aren’t written to the FRA.
You can verify that the archive location is using a FRA:
SQL> archive log list;If archive files are being written to the FRA, you should see output like this:
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DESTYou can display the directory associated with the FRA like this:
SQL> show parameter db_recovery_file_destWhen you first implement a FRA, there are no subdirectories beneath the base FRA directory (specified with DB_RECOVERY_FILE_DEST). The first time Oracle needs to write a file to the FRA, it creates any required directories beneath the base directory. For example, after you implement a FRA, if archiving for your database is enabled, then the first time a log switch occurs, Oracle creates the following directories beneath the base FRA directory:
<SID>/archivelog/<YYYY_MM_DD>Each day that archive redo logs are generated results in a new directory’s being created in the FRA, using the directory name format YYYY_MM_DD. Archive redo logs written to the FRA use the OMF format naming convention (regardless of whether you’ve set the LOG_ARCHIVE_FORMAT parameter).
If you want archive redo logs written to both a FRA and a non-FRA location, you can enable that, as follows:
SQL> alter system set log_archive_dest_1='location=/u01/oraarch/O12C';
SQL> alter system set log_archive_dest_2='location=USE_DB_RECOVERY_FILE_DEST';The archive destination of USE_DB_RECOVERY_FILE_DEST indicates a FRA is in use. If you want to disable the use of a FRA, simply set the db_recovery_file_dest parameter to a null string:
SQL> alter system set db_recovery_file_dest='';I’m not going to discuss all aspects of enabling and managing a FRA. Just be aware that once enabled, you’ll need to take care when issuing RMAN backup commands and ensure that you have a strategy in place for regularly removing old backups.
Enabling Archivelog Mode
After you’ve set the location for your archive redo log files, you can enable archiving. To enable archiving, you need to connect to the Oracle database 12C as SYS (or a user with the SYSDBA privilege) and do the following:
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;You can confirm archivelog mode with this query:
SQL> archive log list;You can also confirm it as follows:
SQL> select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG
Disabling Archivelog Mode
Usually, you don’t disable archivelog mode for a production database. However, you may be doing a big data load and want to reduce any overhead associated with the archiving process, and so you want to turn off archivelog mode before the load begins and then reenable it after the load. If you do this, be sure you make a backup as soon as possible after reenabling archiving.
To disable archiving, do the following as SYS (or a user with the SYSDBA privilege):
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database noarchivelog;
SQL> alter database open;
Reacting to a Lack of Disk Space in Your Archive Log Destination
The archiver background process writes archive redo logs to a location that you specify. If, for any reason, the archiver process can’t write to the archive location, your database hangs. Any users attempting to connect receive this error:
ORA-00257: archiver error. Connect internal only, until freed.As a production-support DBA, you never want to let your database get into that state. Sometimes, unpredictable events happen, and you have to deal with unforeseen issues.
Note DBAs who support production databases have a mindset completely different from that of architect DBAs, who get new ideas from flashy presentations or regurgitated documentation.
In this situation your database is as good as down and completely unavailable. To fix the issue, you have to act quickly:
- Move files to a different location.
- Compress old files in the archive redo log location.
- Permanently remove old files.
- Switch the archive redo log destination to a different location (this can be changed dynamically, while the database is up and running).
Moving files is usually the quickest and safest way to resolve the archiver error. You can use an OS utility such as mv to move old archive redo logs to a different location. If they’re needed for a subsequent restore and recovery, you can let the recovery process know about the new location. Be careful not to move an archive redo log that is currently being written to. If an archived redo log file appears in V$ARCHIVED_LOG, that means it has been completely archived.
You can use an OS utility such as gzip to compress archive redo log files in the current archive destination. If you do this, you have to remember to uncompress any files that may be later needed for a restore and recovery. Be careful not to compress an archive redo log that is currently being written to.
Another option is to use an OS utility such as rm to remove archive redo logs from disk permanently. This approach is dangerous because you may need those archive redo logs for a subsequent recovery. If you do remove archive redo log files, and you don’t have a backup of them, you should make a full backup of your database as soon as possible. Again, this approach is risky and should only be done as a last resort; if you delete archive redo logs that haven’t been backed up, then you chance not being able to perform a complete recovery.
If another location on your server has plenty of space, you can consider changing the location to which the archive redo logs are being written. You can perform this operation while the Oracle database 12C is up and running; for example,
SQL> alter system set log_archive_dest_1='location=/u02/oraarch/O12C';After you’ve resolved the issue with the primary location, you can switch back the original location.
For most databases, writing the archive redo logs to one location is sufficient. However, if you have any type of disaster recovery or high-availability requirement, then you should write to multiple locations. Sometimes, DBAs set up a job to back up the archive redo logs every hour and copy them to an alternate location or even to an alternate server.
Backing Up Archive Redo Log Files
Depending on your business requirements, you may need a strategy for backing up archive redo log files. Minimally, you should back up any archive redo logs generated during a backup of a database in archivelog mode. Additional strategies may include
- periodically copying archive redo logs to an alternate location and then removing them from the primary destination
- copying the archive redo logs to tape and then deleting them from disk
- using two archive redo log locations
- using Data Guard for a robust disaster recovery solution
Keep in mind that you need all archive redo logs generated since the begin time of the last good backup to ensure that you can completely recover your Oracle database. Only after you’re sure you have a good backup of your database should you consider removing archive redo logs that were generated prior to the backup.
If you’re using RMAN as a backup and recovery strategy, then you should use RMAN to backup the archive redo logs. Additionally, you should specify an RMAN retention policy for these files and have RMAN remove the archive redo logs only after the retention policy requirements are met (e.g., back up files at least once before removing from disk).
