14: Configuring the Database for Backup and Recovery in Software

Encode Code39 in Software 14: Configuring the Database for Backup and Recovery

groups) either through Database Control or from the SQL*Plus command line There are two views that will tell you the state of your redo logs V$LOG will have one row per group, and V$LOGFILE will have one row per log file member Figure 14-5 shows an example of online redo log configuration The first query shows that this database has three log file groups The current group the one LGWR is writing to at the moment is group 2; the other groups are inactive, meaning first that the LGWR is not writing to them, and second that in the event of an instance failure, SMON would not require them for instance recovery In other words, the checkpoint position has advanced into group 2 The SEQUENCE# column tells us that there have been 200 log switches since the database was created This number is incremented with each log switch The MEMBERS column shows that each group consists of only one member seriously bad news, which should be corrected as soon as possible The second query shows the individual online redo log files Each file is part of one group (identified by GROUP#, which is the join column back to V$LOG) and has a unique name The STATUS column should always be null, as shown If the member has not yet been used, typically because the database has only just been opened and no log switches have occurred, the status will be STALE; this will only be there until the first log switch If the status is INVALID, you have a problem TIP As with the controlfile, Oracle does not enforce any naming convention for log files, but most organizations will have standards for this

Then there is a command to force a log switch:

alter system switch logfile;

The log switch would happen automatically, eventually, if there were any DML in progress The last query shows that after the log switch, group 3 is now the current group that LGWR is writing to, at log switch sequence number 201 The previously current group, group 2, has status ACTIVE This means that it would still be needed by SMON for instance recovery if the instance failed now In a short time, as the checkpoint position advances, it will become INACTIVE Issuing an

alter system checkpoint;

command would force the checkpoint position to come up to date, and group 2 would then become inactive immediately The number of members per group is restricted by settings in the controlfile, determined at database creation time Turn back to 2, and the CREATE DATABASE command called by the CreateDBsql script; the MAXLOGFILES directive limits the number of groups that this database can have, and the MAXLOGMEMBERS directive limits the maximum number of members of each group The DBCA defaults for these (sixteen and three, respectively) may well be suitable for most databases, but if they prove to be inappropriate, it is possible to recreate the controlfile with different values However, as with all controlfile operations, this will require downtime TIP In fact, with the current release, the limits on the redo log specified in the controlfile are only soft limits: if you create groups and members in excess of these, the limits will be adjusted (and the size of the controlfile increased) automatically To protect the database against loss of data in the event of damage to an online redo log file group, multiplex it Continuing from the example in Figure 14-5, to add multiplexed copies to the online log, one would use a command such as this:

alter database add logfile member D:\APP\ORACLE\ORADATA\ORCL11G\REDO01Alog' to group 1;

or it can also be done through Database Control Exercise 14-1: Multiplex the Redo Log This exercise will add a member to each redo log group through Database Control and then confirm the addition from SQL*Plus The assumption is that there is currently only one member per group, and that you have three groups; if your groups are configured differently, adjust the instructions accordingly 1 Using Database Control, log on as user SYSTEM 2 From the database home page, take the Server tab, and then the Redo Log Groups link in the Storage section 3 Select the first group, and click EDIT