Multiplexing and archiving control and log files

Learning objective

After completing this topic, you should be able to recognize how to multiplex the Oracle 10g redo log and archive redo log files.

1. Control files and redo log files

To safeguard the data in your database, you must prevent accidental damage to key assets such as

• control files
• redo log files

control files

The control file describes the structure of the database, and is used by the Oracle server whenever the database is open or mounted. The database can't be mounted without the control file, so it must be recovered or re-created if the database is to be mounted.

redo log files

The redo log files record changes to the data stored in the database. They are essential if the data ever needs to be recovered.

Control and redo log files can be protected using multiplexing - this means maintaining multiple copies of a file on separate disks.

To multiplex the file, you make a copy of it and store the versions manually on the different disks. If one of the files is lost, you will have an immediate replacement.

The Oracle database has three copies of its control files by default, and you should maintain this number, or a minimum of two, at all times.

Losing a control file makes the instance fail, because each control file listed in the initialization parameter file should always be available.

To recover the control file, you simply copy over one of the other versions that you have stored separately.

If you lose all control files, it is more difficult to recover the instance, although it is not impossible.

Redo log files are organized into groups, with each file in each group being a duplicate of the other files in that group.

You should have at least two files per group, with each one placed on separate disks or controllers. This is to prevent losing the entire group if one of the disks or controllers fails.

Losing a whole log group is very serious, as it could result in loss of data. This is why you must multiplex every redo log group in the server.

Losing a single member in a group with many members is less serious. It will not affect database operation, other than causing an alert to be published in the alert log.

Redo logs greatly affect database performance because a commit can't be made on a transaction until the information about it is written in the logs.

To speed the process up, you should place the logs on the fastest disks, with the fastest controllers. Also, you should only put redo log files on those disks.

However, you can have members from several groups on the same disk, as only one group is written to at any one time.

Question

What are Oracle's recommendations for multiplexing control and redo log files?

Options:

0. All files in the redo log group should be placed on the same disk
0. Redo log groups should have a minimum of two files per group
0. The copies of control files should be placed on different disks
0. There should be a minimum of three control files

Answer

For multiplexing control and redo log files, Oracle recommends that redo log groups have a minimum of two files per group, that the copies of control files be placed on different disks, and that there should be a minimum of three control files.

Option 1 is incorrect. To prevent the loss of the entire group, the files in the redo log group should be placed on separate disks.

Option 2 is correct. You need to have at least one duplicate of every file, so that copies can be placed in separate locations. This is to prevent the loss of data if one of the disks or controllers fails. So the minimum group is a single file and its duplicate.

Option 3 is correct. A control file is a description of the structure of the database, and is required when the server is opened or mounted. Placing it on separate disks prevents the loss of the control file in the event of media failure.

Option 4 is correct. Oracle recommends that you have three control files, and you need at least two, stored in separate locations. This gives you one backup.

To multiplex a redo log file, you need to add a new member to an existing group.

To do this, you first select Redo Log Groups in the Storage section of the Administration tabbed page.

The Redo Log Groups page lists the redo log groups that are currently available in the server.

Now, you want to open the second Redo log group for editing.

You select 2 in the Select column and click Edit.

The single log group file for the second group is listed by filename in the Edit Redo Log Group: 2 page with its file directory location.

Any additional files should have different, directory locations, preferably on separate disks.

You click Add to create a new member of the group.

In the Add Redo Log Member page there are text fields for the name of the new member and the directory location.

You enter the name of the member in the File Name text box. It is redo02_b.log in this case.

You then enter the file directory, /disk2/oracle/orcl2/redo in this case, and click Continue.

The new member now appears in the list of redo log files for the group in the Edit Redo Log Group: 2 page.

When you have added all the members you click Apply.

You continue these steps for all the groups, so that each is multiplexed.

After a new member has been added to a group, the group is automatically given the INACTIVE status. This is because a member of the group has not been written to yet.

The status changes to CURRENT when a log switch occurs, making the server write to the log.

Question

You are multiplexing all the redo log groups on your system so that they have three members. So far you have completed multiplexing on groups 1 and 2. Now you want to do it for group 3. In this case, your hardware resource limitations oblige you to create the log group members on one disk.

This task requires you to add a new member called "redo05.log" to redo log group 3, using the default file directory. Which of these options allows you to complete this task?

Options:

0. You click Redo Log Groups in the Storage section of the Administration tabbed page. You then click Add in the Redo Log Groups page. Then in the Add Redo Log Member page you type redo05.log in the File Name text box and click Continue.
0. You click Redo Log Groups in the Storage section of the Administration tabbed page. You then select 3 in the Select column in the Redo Log Groups page and click Edit. Next, you click Add in the Edit Redo Log Groups: 3 page. Then in the Add Redo Log Member page you type redo05.log in the File Name text box and click Continue. Finally, you click Apply.

Answer

You click Redo Log Groups in the Storage section of the Administration tabbed page. You then select 3 in the Select column in the Redo Log Groups page and click Edit. Next, you click Add in the Edit Redo Log Groups: 3 page. Then in the Add Redo Log Member page you type redo05.log in the File Name text box and click Continue. Finally, you click Apply.

2. Creating archive logs

Online redo log groups behave like a buffer for the instance, with each group working in series to store the transactional information. The instance stores the information in the first group until it is filled, and then moves on to the next one.

When every group has been written to, the instance starts to overwrite the first redo log group.

So that your database has full recoverability, you should configure it so that a copy of the redo log group is made before it can be overwritten.

The copy of the redo log group is called an archived log.

Before archived logs are created you must:

• provide them with a naming convention
• provide them with a destination or destinations
• put the database in ARCHIVELOG mode

However, the destinations of the archived logs must be set before the database is in ARCHIVELOG mode. Also, if a directory is used as a destination, the directory name should have a trailing slash.

Question

Which of these steps do you need to take when creating archived logs ?

Options:

0. Back up the log groups manually on a new disk
0. Place the database in ARCHIVELOG mode
0. Provide a destination for the archived logs
0. Provide a naming convention for the archived logs

Answer

When creating archived logs you need to specify a naming convention and a destination or destinations. You then need to place the database in ARCHIVELOG mode.

Option 1 is incorrect. When you have configured the database in ARCHIVELOG mode it will automatically copy the files to the destination that you have set, using the naming convention you have established.

Option 2 is correct. The destinations for the archive file must be in place before you put the database in ARCHIVELOG mode.

Option 3 is correct. If a directory is used as a destination, the directory name should be specified with a trailing slash.

Option 4 is correct. When the instance has written to all the logs it will start to overwrite the first archived redo log group. To maintain recoverability, you should set the archived log file format to ensure that each new archived log file has a unique name.

To configure an archived log you must first click the Maintenance tab in Enterprise Manager.

Now you click Configure Recovery Settings in the Backup/Recovery section of the Maintenance tabbed page.

In the Configure Recovery Settings page, you navigate to the Media Recovery section to configure a naming convention and set the destinations of the archived logs.

Each archived file must have its own unique name so that an older log file is not overwritten. To provide these names you can set the Log Archive File Format, which will use items such as the log sequence number as part of the unique name of the log.

Oracle allows you to use several wildcard characters as shorthand when setting the file format.

The wildcard characters Oracle allows in the name format are

• %s
• %t
• %r
• %d

%s

%s includes the log sequence number as part of the filename.

%t

%t inserts the thread number in the filename.

%r

%r sets the resetlogs ID. This ID maintains the uniqueness of the archive log filename even if the log sequence numbers have been reset by certain advanced recovery techniques.

%d

%d uses the ID of the database as part of the filename.

The %s, %t, and %r wildcards must be included in the file format.

The %d character remains optional. However, if two or more databases share an archive log destination, %d should be included.

The Configure Recovery Settings page provides spaces for ten different destinations.

These can be local directories, or a remote destination such as an Oracle Net alias for a standby database.

Local destinations should end with a forward slash (/). You should use a backslash (\) if using Windows.

You now enter the destination for the archive log. In this case it is

/home/oracle/orcl2/redo/archive/

The tenth destination is a default which sends archived log files to a location specified by the value of the USE_DB_RECOVERY_FILE_DEST initialization parameter.

The DB_RECOVERY_FILE_DEST is also accessible as the Flash Recovery Area at the bottom of the Configure Recovery Settings page.

You delete the USE_DB_RECOVERY_FILE_DEST entry if you don't want archive logs to be sent to that location.

You should be connected as SYSDBA or SYSOPER to change recovery settings.

Question

You are setting the file format so that each archived log has its own unique name. The archive log destination will contain logs for a single database.

Which of these file formats would be most suitable?

Options:

0. %s_%t_%d
0. %s_%t_%d_%r
0. %s_%t_%r
0. %t_%r_%d

Answer

The file format %s _ %t _ %r is correct.

Option 1 is incorrect. Although this includes the log sequence number and the thread number, it doesn't have the resetlogs ID. Also, it contains the database ID, which is not needed.

Option 2 is incorrect. This contains the required file format specifiers: the log sequence number, the thread number and the resetlogs ID. However it also contains the database ID, which is not needed, since only logfiles belonging to a single database are written to the archive log destination in this case.

Option 3 is correct. This format contains all the required file format specifiers, but omits the database ID. The resetlogs ID is needed to ensure that the archive log name remains unique after advance recovery techniques are used.

Option 4 is incorrect. This omits the log sequence number, which is required, but includes the database ID, which is not needed.

3. Placing the database in ARCHIVELOG mode

The final step in configuring the database to archive redo information is to place the database in ARCHIVELOG mode.

You can do this using either a SQL interface or Enterprise Manager.

To place the database in ARCHIVELOG mode using SQL, you execute the command: ALTER DATABASE ARCHIVELOG .

Question

Which of these commands prevents redo logs from being overwritten until they have been backed up?

Options:

0. ALTER DATABASE ARCHIVELOG
0. ALTER DATABASE BACKUP
0. ALTER DATABASE REGISTER LOGFILE
0. ALTER DATABASE OPEN

Answer

The command ALTER DATABASE ARCHIVELOG places the database in ARCHIVELOG mode.

Option 1 is correct. This is the final step after providing a naming convention. You must provide suitable destinations before putting the database in ARCHIVELOG mode.

Option 2 is incorrect. The correct command is ALTER DATABASE ARCHIVELOG. You can also set the database to ARCHIVELOG mode in Enterprise Manager.

Option 3 is incorrect. ARCHIVELOG mode is used to copy archived logs with a unique name, to a particular destination. This is to provide the database with full recovery capabilities.

Option 4 is incorrect. This command is used to open the database. The command ALTER DATABASE ARCHIVELOG is used to put the database in ARCHIVELOG mode.

As the command can only be used while the database is in MOUNT state, you must restart the instance before the database can go into ARCHIVELOG mode.

During the restart of the database you are asked for operating-system and database credentials for a user with SYSDBA-level privileges.

To place the database in ARCHIVELOG mode using Enterprise Manager, you first click Configure Recovery Settings in the Maintenance tabbed page.

The database is placed in NOARCHIVELOG mode by default. In this state, data can only be recovered up to the last backup. Any transactions made after that are lost.

However, most production databases operate in ARCHIVELOG mode, which allows you to recover data up to the last commit.

You now want to place the database in ARCHIVELOG mode.

You click the ARCHIVELOG M ode * checkbox in the Media Recovery section of the Configure Recovery Settings page and click Apply.

A confirmation message appears, telling you that the changes have been made successfully. It also mentions that to implement the changes you must restart the instance.

If you want to cancel the changes, you click No.

You click Yes to restart the instance. When it has restarted, the changes you made to the log format, the log destinations, and the archive process will take effect.

Question

You have set the file format for the archive log and the destinations for those archives. Now you want to put the database into ARCHIVELOG mode.

Which of these options allows you to complete this task?

Options:

0. You click Configure Recovery Settings in the Maintenance tabbed page. Then you click the ARCHIVELOG Mode* checkbox in the Media Recovery section of the Configure Recovery Settings and click Apply. You then click Yes in the Confirmation page.
0. You click Configure Backup Settings in the Maintenance tabbed page. Then you click the ARCHIVELOG Mode* checkbox in the Instance Recovery section of the Configure Recovery Settings and click Apply. You then click Yes in the Confirmation page.

Answer

You click Configure Recovery Settings in the Maintenance tabbed page. Then you click the ARCHIVELOG Mode* checkbox in the Media Recovery section of the Configure Recovery Settings and click Apply. You then click Yes in the Confirmation page.

Summary

Database protection must include the control file and redo log files. The server depends on the structural information in the control file when opening the database. Redo log files make data recovery possible by recording every committed transaction. You protect these key files by multiplexing - copying them to separate devices.

The instance uses redo log groups as a buffer, filling all the groups in turn, then overwriting from the first. Each log group should be archived before being overwritten. To do this you provide a naming convention using file-format specifiers, set a destination or destinations for the archived logs, then set ARCHIVELOG mode.

Whereas NOARCHIVELOG mode, the default setting, only allows data recovery up to the last backup, in ARCHIVELOG mode you can recover up to the last commit. You can place the database in ARCHIVELOG mode using a SQL command or in Enterprise Manager. You must restart the instance for changes to come into effect.