HomeOracle Page 2 - Oracle Database Backup and Recovery Practices
Multiplexing Redo Log Files - Oracle
Database failures are often a matter of when, not if. This three-part article series walks you through the best practices for backing up and recovering Oracle Database XE. It is excerpted from chapter 30 of the book Beginning PHP and Oracle: From Novice to Professional, written by W. Jason Gilmore, Bob Bryla (Apress; ISBN: 1590597702).
As you remember from Chapter 28, the online redo log files are a key component required to recover from both instance failure and media failure. By default, Oracle Database XE creates the minimum number of redo log files (two). When the first redo log file fills with committed transactions, subsequent transactions are written to the other redo log file. Whether you have two, three, or more redo log files, Oracle writes to the log files in a circular fashion. Thus, if you have ARCHIVELOGmode enabled, Oracle can write new transactions to the next redo log file while Oracle archives the previous online log file. (We show you how to enableARCHIVELOGmode in a coming section.)
Note The terms redo log file and online redo log file are often used interchangeably. However, the distinction is important when you are comparing online redo log files to archived (offline) redo log files.
To prevent loss of data if you lose one of the online redo log files, you can multiplex, or mirror, the redo log files. In other words, each redo log file, whether there are two, three, or more, has one or more identical copies. These copies are maintained automatically by Oracle processes. Writing to a specific log file occurs in parallel with all other log files in the group. While there is a very slight performance hit when the Oracle processes must write to two copies of the redo log file instead of just one, the slight overhead is easy to justify compared to the recovery time (including lost committed transactions) if a nonmultiplexed online log file is lost due to a hardware failure or other error. To see the current status of the online redo log files, start at the Oracle Database XE home page and navigate to Administration ➤ Storage. In the Tasks section on the right side of the page, click View Logging Status and you will see the names and status of the online redo log files. By default, Oracle Database XE creates two online redo log files, as you can see in Figure 40-1.
Notice the directory path for the redo log files:
Figure 40-1.Online redo log file status
This area, as you might surmise, is known as the Flash Recovery Area. The Flash Recovery Area automates the management for backups of all types of database objects such as multiplexed copies of the control file and online redo log files, archived redo log files, and datafiles. You specify the location of the Flash Recovery Area along with a maximum size, and Recovery Manager (RMAN) manages files within this area. You define the location and size of the Flash Recovery Area with two initialization parameters. From the SQL command-line prompt run this command:
show parameter db_recov
You will see all parameters in the database that begin withdb_recov:
big integer 10G
You can also view these parameters from the Home ➤ Administration ➤ About page in the Oracle Database XE GUI. To ensure prompt and easy recovery of any database object, your Flash Recovery Area should be large enough to hold at least one copy of all datafiles, incremental backups, online redo log files, control files, and any archived redo log files required to restore a database from the last full or incremental backup to the point in time of a media failure. You can check the status of the Flash Recovery Area by querying the dynamic performance viewV$RECOVERY_FILE_DEST:
select name, space_limit, space_used from v$recovery_file_dest;
Of the 10GB of space available in the Flash Recovery Area, less than 900MB is used.
Multiplexing these redo log files is easy; the only catch is that there is no GUI interface available for this operation—you must use a couple of SQL commands. You will put the multiplexed redo log files in the directory/u01/app/oracle/onlinelog. This file system is on a separate disk drive and a separate controller from the redo log files shown earlier in Figure 40-1. Connect as a user withSYSDBAprivileges, and use these SQL statements:
alter database add logfile member '/u01/app/oracle/onlinelog/g1m2.log' to group 1; alter database add logfile member '/u01/app/oracle/onlinelog/g2m2.log' to group 2;
Notice that you do not need to specify a size for the new redo log file group members; all files within the same redo log file group must have the same size, so Oracle automatically uses the file size of the files within the existing group. After you run these statements, you revisit the Database Logging page shown earlier in Figure 40-1, and you now see the same log file groups but with each having a multiplexed member, as shown in Figure 40-2.
Please check back for the next part of the series. Figure 40-2. Multiplexed online redo log files