|Oracle8 Administrator's Guide
This chapter describes how to create and maintain the archived redo log, and includes the following topics:
See Also: If you are using Oracle with the Parallel Server, see Oracle8 Parallel Server Concepts and Administration for additional information about archiving in the environment.
This chapter contains several references to Oracle Enterprise Manager. For more information about performing specific tasks using Enterprise Manager/GUI or Server Manager/LineMode, see the Oracle Enterprise Manager User's Guide.
This section describes the issues you must consider when choosing to run your database in NOARCHIVELOG or ARCHIVELOG mode, and includes the following topics:
When you run your database in NOARCHIVELOG mode, the archiving of the online redo log is disabled. Information in the database's control file indicates that filled groups are not required to be archived. Therefore, after a filled group becomes inactive and the checkpoint at the log switch completes, the group is available for reuse by LGWR.
NOARCHIVELOG mode protects a database only from instance failure, not from disk (media) failure. Only the most recent changes made to the database, stored in the groups of the online redo log, are available for instance recovery. In other words, if you are using NOARCHIVELOG mode, you can only restore (not recover) the database to the point of the most recent full database backup. You cannot recover subsequent transactions.
Also, in NOARCHIVELOG mode you cannot perform online tablespace backups. Furthermore, you cannot use online tablespace backups previously taken while the database operated in ARCHIVELOG mode. Only full backups taken while the database is closed can be used to restore a database operating in NOARCHIVELOG mode. Therefore, if you decide to operate a database in NOARCHIVELOG mode, take full database backups at regular, frequent intervals.
When you run a database in ARCHIVELOG mode, the archiving of the online redo log is enabled. Information in a database control file indicates that a group of filled online redo log files cannot be used by LGWR until the group is archived. A filled group is immediately available to the process performing the archiving after a log switch occurs (when a group becomes inactive). The process performing the archiving does not have to wait for the checkpoint of a log switch to complete before it can access the inactive group for archiving.
Figure 23-1 illustrate how the database's online redo log is generated by the process archiving the filled groups (ARCH in this illustration).
ARCHIVELOG mode enables complete recovery from disk failure as well as instance failure, because all changes made to the database are permanently saved in an archived redo log.
If all databases in a distributed database operate in ARCHIVELOG mode, you can perform coordinated distributed database recovery. However, if any database in a distributed database uses NOARCHIVELOG mode, recovery of a global distributed database (to make all databases consistent) is limited by the last full backup of any database operating in NOARCHIVELOG mode.
Also, the entire database can be open and available for normal use while you back up or recover all or part of the database in ARCHIVELOG mode. Note that extra administrative operations are required to manage the files of the archived redo log and that you must have a dedicated tape drive or additional disk space to store the archived redo log files when the database operates in ARCHIVELOG mode.
You must also decide how filled groups of the online redo log are to be archived. An instance can be configured to have Oracle automatically archive filled online redo log files, or you can manually archive filled groups.
See Also: You can also configure Oracle to verify redo log blocks when they are archived. For more information, see "Verifying Blocks in Redo Log Files".
This section describes aspect of archiving, and includes the following topics:
You set a database's initial archiving mode as part of database creation. Usually, you can use the default of NOARCHIVELOG mode at database creation because there is no need to archive the redo information generated then. After creating the database, decide whether to change from the initial archiving mode.
After a database has been created, you can switch the database's archiving mode on demand. However, you should generally not switch the database between archiving modes.
See Also: If a database is automatically created during Oracle installation, the initial archiving mode of the database is operating system specific. See your operating system-specific Oracle documentation.
When you create the database, you set the initial archiving mode of the redo log in the CREATE DATABASE statement. If you do not specify either ARCHIVELOG or NOARCHIVELOG, NOARCHIVELOG is the default.
See Also: See Chapter 2, "Creating an Oracle Database" for more information about creating a database.
To switch a database's archiving mode between NOARCHIVELOG and ARCHIVELOG mode, use the SQL command ALTER DATABASE with the ARCHIVELOG or NOARCHIVELOG option. The following statement switches the database's archiving mode from NOARCHIVELOG to ARCHIVELOG:
ALTER DATABASE ARCHIVELOG;
Before switching the database's archiving mode, perform the following operations:
An open database must be closed and dismounted and any associated instances shut down before the database's archiving mode can be switched. Archiving cannot be disabled if any datafiles need media recovery.
Before making any major alteration to a database, always back up the database to protect against any problems that might occur.
These steps may involve exiting Enterprise Manager to configure how Oracle will perform the archiving of the filled groups. Once this operation is complete, start Enterprise Manager again and continue to Step 4.
To enable or disable archiving, the database must be mounted but not open.
After using the ALTER DATABASE command to switch a database's archiving mode, open the database for normal operation. If you switched to ARCHIVELOG mode, you should also set the archiving options-decide whether to enable Oracle to archive groups of online redo log files automatically as they fill.
If you want to archive filled groups, you may have to execute some additional steps at this point, depending on your operating system; see your operating system-specific Oracle documentation for details for your system.
For more information about database backup, see the Oracle8 Backup and Recovery Guide.
See Oracle8 Parallel Server Concepts and Administration for more information about switching the archiving mode when using the Oracle Parallel Server.
If your operating system permits, you can enable automatic archiving of the online redo log. Under this option, no action is required to copy a group after it fills; Oracle automatically archives groups after they are filled. For this convenience alone, automatic archiving is the method of choice for archiving the filled groups of online redo log files.
To enable automatic archiving after instance startup, you must be connected to Oracle with administrator privileges.
Oracle does not automatically archive log files unless the database is also in ARCHIVELOG mode.
Automatic archiving can be enabled before or after instance startup.
See Also: See your operating system-specific Oracle documentation to determine whether this is a valid option for your Oracle Server.
Always specify an archived redo log destination and filename format when enabling automatic archiving; see "Specifying the Archived Redo Log Filename Format and Destination".
If automatic archiving is enabled, manual archiving is still possible; see "Performing Manual Archiving".
To enable automatic archiving of filled groups each time an instance is started, include the LOG_ARCHIVE_START parameter, set to TRUE, in the database's parameter file:
The new value takes effect the next time you start the database.
To enable automatic archiving of filled online redo log groups without shutting down the current instance, use the SQL command ALTER SYSTEM with the ARCHIVE LOG START parameter; you can optionally include the archiving destination.
ALTER SYSTEM ARCHIVE LOG START;
Using either of the options above, you do not need to shut down the instance to enable automatic archiving. However, if an instance is shut down and restarted after automatic archiving is enabled, the instance is reinitialized using the settings of the parameter file, which may or may not enable automatic archiving.
You can disable automatic archiving of the online redo log groups at any time. However, once automatic archiving is disabled, you must manually archive groups of online redo log files in a timely fashion. If a database is operated in ARCHIVELOG mode, automatic archiving is disabled, and all groups of online redo log files are filled but not archived, then LGWR cannot reuse any inactive groups of online redo log groups to continue writing redo log entries. Therefore, database operation is temporarily suspended until the necessary archiving is performed.
To disable automatic archiving after instance startup, you must be connected with administrator privilege and have the ALTER SYSTEM privilege.
Automatic archiving can be disabled at or after instance startup.
To disable the automatic archiving of filled online redo log groups each time a database instance is started, set the LOG_ARCHIVE_START parameter of a database's parameter file to FALSE:
The new value takes effect the next time the database is started.
To disable the automatic archiving of filled online redo log groups without shutting down the current instance, use the SQL command ALTER SYSTEM with the ARCHIVE LOG STOP parameter. The following statement stops archiving:
ALTER SYSTEM ARCHIVE LOG STOP;
If ARCH is archiving a redo log group when you attempt to disable automatic archiving, ARCH finishes archiving the current group, but does not begin archiving the next filled online redo log group.
The instance does not have to be shut down to disable automatic archiving. However, if an instance is shut down and restarted after automatic archiving is disabled, the instance is reinitialized using the settings of the parameter file, which may or may not enable automatic archiving.
If a database is operating in ARCHIVELOG mode, inactive groups of filled online redo log files must be archived. You can manually archive groups of the online redo log whether or not automatic archiving is enabled:
To manually archive a filled online redo log group, you must be connected with administrator privileges.
Manually archive inactive groups of filled online redo log members using the SQL command ALTER SYSTEM with the ARCHIVE LOG clause.
The following statement archives all unarchived log files:
ALTER SYSTEM ARCHIVE LOG ALL;
See Also: With both manual or automatic archiving, you need to specify a thread only when you are using the Oracle Parallel Server. See Oracle8 Parallel Server Concepts and Administration for more information.
This section describes aspects of tuning the archive process, and includes the following topics:
For most databases, the archive process has no effect on overall system performance. In some large database sites, however, archiving can have an impact on system performance. On one hand, if the archiver works very quickly, overall system performance can be reduced while the archiver runs, since CPU cycles are being consumed in archiving. On the other hand, if the archiver runs extremely slowly, it has little detrimental effect on system performance, but it takes longer to archive redo log files, and can be a bottleneck if all redo log groups are unavailable because they are waiting to be archived.
For these large database sites you can tune archiving to cause it to run either as slowly as possible without being a bottleneck or as quickly as possible without reducing system performance substantially. To do so, adjust the values of the initialization parameters LOG_ARCHIVE_BUFFERS (the number of buffers allocated to archiving) and LOG_ARCHIVE_BUFFER_SIZE (the size of each such buffer).
When you change the value of LOG_ARCHIVE_BUFFERS or LOG_ARCHIVE_BUFFER_SIZE, the new value takes effect the next time you start the instance.
To make the archiver work as slowly as possible without forcing the system to wait for redo logs, begin by setting the number of archive buffers (LOG_ARCHIVE_BUFFERS) to 1 and the size of each buffer (LOG_ARCHIVE_BUFFER_SIZE) to the maximum possible.
If the performance of the system drops significantly while the archiver is working, make the value of LOG_ARCHIVE_BUFFER_SIZE lower, until system performance is no longer reduced when the archiver runs.
If you want to set archiving to be very slow, but find that Oracle frequently has to wait for redo log files to be archived before they can be reused, consider creating additional redo log file groups. Adding groups can ensure that a group is always available for Oracle to use.
To improve archiving performance (for example, if you want to stream input to a tape drive), use multiple archive buffers, so that the archiver process can read the archive log at the same time that it writes the output log. You can set LOG_ARCHIVE_BUFFERS to 2, but for a very fast tape drive you might want to set it to 3 or more. Then, set the size of the archive buffers to a moderate number, and increase it until archiving is as fast as you want it to be without impairing system performance.
See Also: This maximum is operating system dependent; see your operating system-specific Oracle documentation.
For more information about these parameters, see the Oracle8 Reference.
To see the current archiving mode, query the V$DATABASE view:
SELECT log_mode FROM sys.v$database; LOG_MODE ------------ NOARCHIVELOG
The V$ARCHIVE and V$LOG data dictionary views also contain archiving information of a database. For example, the following query lists all log groups for the database and indicates the ones that remain to be archived:
SELECT group#, archived FROM sys.v$log; GROUP# ARC ---------- --- 1 YES 2 NO
The command ARCHIVE LOG with the LIST parameter also shows archiving information for the connected instance:
ARCHIVE LOG LIST; Database log mode ARCHIVELOG Automatic archival ENABLED Archive destination destination Oldest online log sequence 30 Next log sequence to archive 32 Current log sequence number 33
This display tells you all the necessary information regarding the redo log settings for the current instance:
You must archive all redo log groups with a sequence number equal to or greater than the Next log sequence to archive, yet less than the Current log sequence number. For example, the display above indicates that the online redo log group with sequence number 32 needs to be archived.
When the database is used in ARCHIVELOG mode, Oracle must know the archived redo log filename format and destination so that automatic or manual archiving creates uniquely named archived redo log files in the proper location.
Archived redo log files are uniquely named as specified by the LOG_ARCHIVE_FORMAT parameter. Filename format is operating system specific; for most operating systems it consists of a text string, one or more parameters, and a filename extension. When a filled online redo log group is archived, the archiving process concatenates the supplied text string with the return values of the specified parameters to create uniquely identified archived redo log files. Each parameter has an upper bound, which is operating system dependent.
Table 23-1 lists the parameters that can be included in a filename format and corresponding examples to show how the parameter affects the filenames created by the archiving process.
thread number, left-zero-padded
thread number, not padded
log sequence number,
log sequence number, not padded
The different options are provided so that you can customize the archived redo log filenames as you need. For example, you might want to take into account the operating system sorting algorithm used to list filenames.
The %T and %t are useful only when the Oracle Parallel Server is used. In a non-Parallel Server configuration, you must decide whether to use %S or %s to identify each archived redo log file uniquely. The following is a typical example of a common archived redo log filename format:
LOG_ARCHIVE_FORMAT = arch%S.arc
Here, arch is the filename, %S is the zero-padded log sequence parameter, and .arc is the file extension. Assuming the upper bound for the %S parameter is four, this filename format generates archived redo log filenames of the following format:
arch0001.arc arch0002.arc arch0003.arc . .
Take into account the maximum operating system filename length when specifying the archive filename format. If ARCH or a user process attempts to archive a file and the supplied filename format is too large, the process fails to archive the file.
If no archived filename format is specified using LOG_ARCHIVE_FORMAT, Oracle uses a default filename format that is operating system-specific.
The archived redo log destination is also operating system-specific. For most operating systems, the archive redo log destination points to a disk drive and a file directory. If permitted by your Oracle Server, this destination can also point to a tape drive dedicated to Oracle for archiving filled online redo log files.
The archived redo log destination is determined at instance startup by the LOG_ARCHIVE_DEST initialization parameter, but can be overridden while the instance is up:
See Also: See your operating system-specific Oracle documentation for more information about the LOG_ARCHIVE_FORMAT and LOG_ARCHIVE_DEST initialization parameters, and the default archived redo log filename format and destination.
For more information about filename format parameters and the term "thread" see Oracle8 Parallel Server Concepts and Administration.