1.9 How do I…Enable archive log mode?Problem
I need to make sure that I can recover all changes to data contained within my Oracle8 instance. I know that I can do this by activating Archive Log mode. How do I determine whether my database is in Archive mode, and how do I activate and deactivate this capability?
Technique
The Oracle8 server stores all changes to the database, even uncommitted changes, in the redo log files. Without this ongoing record of database changes, the most complete recovery will only include database transactions committed prior to the last image backup. The complication is that the LGWR (log writer) process writes to the redo logs in a cyclic fashion. So, if the database includes two redo log files, LGWR starts to writes to the first redo log file until it is full, then writes to the second redo log file until it is full, and then writes to the first redo log file again, overwriting its contents.
The archive process preserves redo logs by copying them to a safe place after LGWR fills them. Use the alter database command to enable Archive Log mode for the database; then set parameters in the parameter initialization file to automate the archive process.
Steps
1. Select the LOG_MODE attribute from the V$DATABASE Dynamic Performance view. This brief command and its output are shown in Figure 1.22.
2. Use one of the database administration tools to shut down the instance.
3. Edit the parameter initialization file to assign values to the parameters log_archive_start, log_archive_dest, and log_archive_ format. Sample values for these parameters appear here:
log_archive_start = true # if you want automatic archiving
log_archive_dest = c:\orant\database\archive # archive destination
log_archive_format = “ARCH%S.LOG” # name format for archived files
4. Use one of the database administration tools to restart and mount the database. Don’t open the database yet.
5. Issue the alter database archivelog command to put the database in ARCHIVELOG mode.
6. Use the alter database open command to open the database for normal operation.
7. Execute the command from step 1 to query the V$DATABASE table again to verify that the database is now operating in ARCHIVELOG mode.
8. From Server Manager or the SQL Worksheet in Enterprise Manager, issue the command show parameter log_archive to verify the values of the parameters set in step 3 (see Figure 1.23).
How It Works
Step 1 queries the V$DATABASE dynamic performance table data and reports the archive mode for the database. Figure 1.22 indicates that the database is not in ARCHIVELOG mode.
In Step 2, you prepare the database for the alter database command that cannot be issued when the database is open. In Step 3, you edit the parameter initialization file to enable automatic archiving. Without these modifications to the parameter file, the DBA must manually archive filled redo log files. Steps 5 and 6 establish ARCHIVELOG mode for the database and open it for production operation. Steps 7 and 8 verify the modifications made in the preceding steps. Figure 1.23 shows the results of these steps.
Comments
In almost all database installations, automatic archiving is the best configuration. The alternative, manual archiving, is only provided for special circumstances such as a media failure of the automatic archiving destination. If a database in Archive Log mode requires manual archiving, the database will halt if the database administrator neglects to manually archive filled redo logs when their status is inactive.
There are two special format parameters that appear in the LOG_ARCHIVE_DEST parameter. The first is %t, for which the Oracle8 server will substitute the thread number. This is only meaningful in Oracle parallel server configurations. The second is %s, which Oracle8 will resolve to the log sequence number. Uppercase specifications of %S or %T instruct Oracle8 to left pad the resulting integer with zeros. Thus, a log_archive_format specification of ARCH%s.LOG becomes, for example, ARCH00454.LOG when the ARCH process copies log sequence number 454 to the archive destination specified in log_archive_dest.