1.8 How do I…Create and multiplex redo log groups?Problem
I want to review the redo log configuration. I also want each redo log group to consist of more than one member to protect the database’s redo logs from disk failures. How do I review and modify the redo log configuration?
Technique
The redo logs keep a running record of all database changes. They are used during recovery operations primarily to ensure that transactions committed since the last backup are re-applied to the database. This process is called rolling forward.
The LGWR process writes to redo log groups. There is at least one physical redo log file in each group. If there is more than one redo log file in a redo log group, the group is multiplexed. As soon as the DBA establishes this configuration, Oracle8 writes simultaneously to all members of the redo log group.
This How-To contains a query that accesses the dynamic performance tables V$LOG and V$LOGFILE (Figure 1.18 shows the structures of these) to summarize the current redo log configuration; the use of the alter database command to create additional redo log groups and members is also covered.
Steps
1. Run SQL*Plus and connect as the WAITE user account. Use the START command to load and execute the script in CHP1_5.SQL. The script appears here, and Figure 1.19 contains the script’s output.
SQL> SELECT vl.group#, members,
2 vl.status groupstat,
3 vf.member, vf.status filestat
4 FROM v$log vl, v$logfile vf
5 WHERE vf.group# = vl.group#;
Line 1 returns the redo log group number and the number of members in the group. Line 3 selects the physical filename of the redo log group member. The status returned from both the V$LOG and V$LOGFILE views indicates the status of the group and of the individual member, respectively. Table 1.2 summarizes some of the most common values for column STATUS in the V$LOG table.
Table 1.2 Common values of the STATUS column in the V$LOG dynamic performance table UNUSED Oracle8 has never written to this group. CURRENT This is the active group. ACTIVE Oracle8 has written to this log before; it is needed for instance recovery. INACTIVE Oracle8 has written to this log before; it is not needed for instance recovery. 2. Use the alter database add logfile command to add additional redo log groups to the database. Figure 1.20 shows the results of this command in the script CHP1_6.SQL. This script also calls CHP1_5.SQL to verify the success of the alter database command. Note that the status of the new log group (group 3) is UNUSED because Oracle8 has never written to this group.
3. Use the alter database add logfile member command to add additional redo log members to existing redo log groups. Figure 1.21 shows the results of this command. The script resides in CHP1_7.SQL, which also calls CHP1_5.SQL to verify that the new member was added successfully. Note that the status of the new log member is INVALID because Oracle has never written to it.
How It Works
Step 1 queries the data dictionary to summarize the current status of the redo log groups and members. In Figure 1.19, this query indicates that there are two redo log groups (the minimum) and that these groups are not multiplexed.
Comments
It is good practice to place redo log members of the same group on different disks so that a single disk failure will not destroy all redo information and make complete up-to-the-second recovery difficult or impossible. The LGWR process writes redo information nearly continuously in an OLTP environment, in addition, so spreading the members of the same group across disks will expedite LGWR operation.
Oracle8 does not permit specification of the size of a new redo log group member. It must be the same as the size of the existing members in the group.
The status of new log members remains INVALID until the group containing the new member becomes the current group. If you add a new member to the current group, the status of the new member remains INVALID until the group becomes current again.