Availability and Recovery

Oracle Database 10g comes with a number of new features such as new features in flashback, Data Guard and transaction recovery monitoring. Also covered here are RMAN improvements and general database recovery improvements. (From the book, Oracle Database 10g New Features, by Robert Freeman, McGraw/Hill-Osborne, 2004, ISBN: 0072229470.)

New FeaturesOracle Database 10g comes with a number of new features that are designed to provide enhanced availability and recoverability within the Oracle database. This chapter covers the following topics such as general database recovery improvements, RMAN improvements, new flashback features, new transaction recovery monitoring features, and new Data Guard features.

 

General Database Recovery Improvements

Several changes have been made in Oracle Database 10g that relate to backup and recovery:

  • Easier recovery through the resetlogs command

  • Changes to the alter database archivelog command

  • New and changed Oracle Database 10g backup commands

Easier Recovery Through the resetlogs Command

One of the downsides to incomplete recovery of an Oracle database in Oracle Database versions prior to Oracle Database 10g was the requirement to use the resetlogs command when opening the database. Because recovery through resetlogs was not supported in Oracle9i, you needed to perform a backup of the database in order for it to be recoverable, though there were some goofy and complex (and altogether unreliable) means to recover the database through resetlogs prior to Oracle Database 10g. Now, Oracle Database 10g makes recovery through the resetlogs command easier than ever.

The nice thing about being able to recover through the resetlogs command is that there is really nothing new that you have to do, it’s all internal to Oracle Database 10g. You can use the recover database, recover tablespace, or recover datafile commands, just as you always have. The RMAN restore and recover database commands are also unchanged, and support the ability to recover beyond the point of the last resetlogs command.

Supporting Changes to the log_archive_format Parameter

Also associated with the ability to recover through the resetlogs command, is a change in the log_archive_format parameter. Oracle Database 10g now requires that a new format specification be used when the compatible parameter is set to 10.0 or greater. This format specification,%r, identifies the logical incarnation of the database and changes each time the resetlogs command is issued. The default format for log_archive_format in Oracle Database 10g is %t_%s_%r.dbf. You could modify the log_archive_format parameter setting in Oracle Database 10g so that it might look something like this:

Log_archive_format=”mydb_%t_%s_%r.arc“

And the resulting physical file might look something like this:

/u01/oracle/arch/mydb/mydb_01_01_2035.arc

Supporting Changes to Oracle Dynamic Views

To support the ability to recover through the use of the resetlogs command, the dynamic views V$LOG_HISTORY and V$OFFLINE_RANGE have been modified. First, the data in these views is not cleared out after a successful resetlogs operation. Second, two new columns have been added to each of these views:

  • RESETLOGS_CHANGE# Indicates the system change number (SCN) associated with the execution of the resetlogs command

  • RESETLOGS_TIME Indicates the time associated with the execution of the resetlogs command

Also, the V$DATABASE_INCARNATION and V$ARCHIVED_LOG views have been changed such that they will not be cleared after the execution of the resetlogs command.

Supporting Changes to Oracle Standby Databases

One other bit of functionality that you might want to be aware of is in regard to Oracle standby databases. When a resetlogs operation is detected on a standby database, the managed recovery process will be canceled. At this point, you have two options:

  • Maintain recovery following the new logical database branch created by the use of the resetlogs command.

  • Maintain the same logical database branch, through the use of the resetlogs command.

Either way, you can continue to apply all redo generated after the use of the resetlogs command on the primary database server.

Changes to the alter database archivelog Command

When you issue the alter database archivelog command in Oracle Database 10g, archiving will be started by default. Thus, you don’t need to use the log archive start command. The V$DATABASE view column LOG_MODE indicates AUTOMATIC if archiving is enabled in this fashion and indicates MANUAL if you have decided to use the new manual parameter to override the default behavior.

This chapter is from Oracle Database 10g New Features, by Robert Freeman (McGraw-Hill/Osborne, 2004, ISBN: 0072229470). Check it out at your favorite bookstore today. Buy this book now.

{mospagebreak title=New and Changed Oracle Database 10g Database Backup Commands}

Oracle Database 10g offers a new way to start database backups, with the alter database begin backup command. Also, Oracle Database 10g offers new functionality with the alter database end backup command. Let’s look at each of these features in a bit more detail next.

The New alter database begin backup Command

Are you tired of issuing alter tablespace begin backup over and over? Now, putting the entire set of database tablespaces in hot backup mode is as simple as issuing the alter database begin backup command, as shown in this example:

SQL> alter database begin backup;

Jonathan Says…

Of course, the traditional advice is to never put your entire database into hot backup mode at once—so you have to ask yourself why Oracle has now made it very easy to do exactly that. Moreover, why do you need this command at all, when RMAN doesn’t require the database to be in hot backup mode at all?

As ever, Oracle is giving you lots of options, and you have to decide which one is relevant to your system. If your backup strategy ignores Oracle features and uses a simple “split mirror” approach, you are the one customer who should really be pleased with this new command.

If there are already tablespaces in hot backup mode, then the Oracle database will raise an error (ORA-01146). Also, the following conditions cause an error to be returned by the alter database begin backup command:
  • One or more datafiles are offline or missing

  • An RMAN backup is ongoing

Once you have issued the alter database begin backup command successfully, you can proceed to back up your Oracle database. As is the case when you put individual tablespaces in hot backup mode, you will be unable to perform a normal or immediate shutdown on the database after issuing this command. However, you can issue shutdown abort to terminate the instance if that is required (although one would hope this would never be required).

If the instance crashes or you use the shutdown abort command, you need to take the database out of hot backup mode by using the alter database end backup command … and that is a nice segue to the next topic!

Changes to the alter database end backup Command

The alter database end backup command has been around since Oracle9i Database, and its functionality is enhanced in Oracle Database 10g. Previously, the alter database end backup command could be used only when the database was mounted. In Oracle Database 10g, you can use this command to end backups with the database mounted or the database open—your choice! Here is an example of the use of this command:

SQL> alter database end backup;

NOTE — A warning will be issued if any datafiles/tablespaces are not in hot backup mode, but the command will complete successfully.

This chapter is from Oracle Database 10g New Features, by Robert Freeman (McGraw-Hill/Osborne, 2004, ISBN: 0072229470). Check it out at your favorite bookstore today. Buy this book now.

{mospagebreak title=RMAN Improvements}

Oracle Database 10g comes with a plethora of improvements (I like that word, plethora!) for RMAN. These include the following:

  • Using flash recovery area

  • Using backup copies and fast recovery

  • Using the catalog and uncatalog commands

  • Dropping a database in RMAN

  • Unregistering a database in RMAN

  • Making and using RMAN backup copies

  • Configuring default disk backup types

  • Changes to incremental backups

  • Recovering datafiles not backed up

  • Changes in error reporting

  • Compressing RMAN backups

  • Using RMAN-related tablespace point-in-time related recovery changes

Using the Flash Recovery Area

Oracle Database 10g offers the flash recovery area, which allows you to centralize storage of all recovery-related files. The flash recovery area is an area of disk that is defined for use for recovery-related files. The flash recovery area can use locally attached storage, Clustered File Systems, or Oracle Database 10g’s new Automatic Storage Management (ASM) features.

Table 5-1 lists the file types that are backed up within the flash recovery area.

Why Use the Flash Recovery Area?

The flash recovery area helps with the management of overall disk space allocation and provides a centralized storage area for all related recovery files. It provides for much faster backup and restore operations as well. The flash recovery area is created in a specific location (defined by a file system, or use of ASM). You define the maximum size of the flash recovery area via database parameters. As files are added or removed from the flash recovery area, records of these events are logged in the database alert log. You can check the new DBA view, DBA_ OUTSTANDING_ALERTS, for information on outstanding issues with the flash recovery area, as shown in this example:

Select * from dba_outstanding_alerts;

File Type

Notes

Control file

One copy of the control file is created in the flash recovery area when the database is created.

Archived redo logs

When you configure the flash recovery area (as described later in this section), the parameter log_archive_dest_10 is automatically configured, and archived redo logs are archived to that destination, as well as any other archive log destinations.

Flashback logs

Flashback logs (discussed later in this chapter) are stored in the flash recovery area, if it is defined.

Control file autobackups

The default location for the RMAN control file autobackups is the flash recovery area, if it is defined.

RMAN datafile copies

The default location for the RMAN datafile copies is the flash recovery area, if it is defined.

RMAN backup and other related files

The default location for the RMAN files in general (backup-set pieces, etc.) is the flash recovery area, if it is defined.

TABLE 5-1. File Types Backed Up Within the Flash Recovery Area

Jonathan Says…

I really like the basic concept of the flash recovery area. Disks are much too big these days, and the most important thing you can do with big disks is find a good excuse for not using more than about 10GB per disk as “active space.” In the past I have advised people to take a couple of generations of backup to disk (then copy to tape) as one way of restricting the amount of “real” data stored per disk. Now it’s legal! (Of course, I did have other, more official-sounding arguments in favor of this strategy.)

Retention for files in the flash recovery area is determined by the RMAN retention policy. This is set via the RMAN configure retention policy command, a feature which is in and of itself not new in Oracle Database 10g. If a file does not have a retention policy associated with it, or it’s a permanent file, then it will never be deleted. If a file is not yet obsolete under the RMAN retention policy, then it will not be deleted. Finally, archived logs are eligible for deletion once they are obsolete.

Once the amount of space in the flash recovery area starts to reduce to unsafe levels, Oracle Database 10g issues a warning to the alert log (at 90 percent used and at 95 percent used). Also, when there is less than 10 percent free space available in the flash recovery area, Oracle Databse 10g removes files that are on the obsolete file list.

NOTE– Running out of space in the flash recovery area can be troublesome if that area is your only archive log destination, as this can cause your database to eventually halt. If the flash recovery area is to be your only archive log destination, monitor space availability carefully.

This chapter is from Oracle Database 10g New Features, by Robert Freeman (McGraw-Hill/Osborne, 2004, ISBN: 0072229470). Check it out at your favorite bookstore today. Buy this book now.

{mospagebreak title=Setting Up the Flash Recovery Area}

To set up the flash recovery area, you need to configure the following parameters (which are new in Oracle Database 10g):

db_recovery_file_dest_size

Example:

Alter system set db_recovery_file_dest_size=20G scope=both;

Purpose:

This parameter sets the allocated size of the flash recovery area, and must be defined in order to enable the flash recovery area. This allows you to control how much disk space will be allocated to the flash recovery area.

You should not set this value to a size that is greater than the total amount of available disk space that is available to you. Otherwise, backups will fail.

db_recovery_file_dest

Example:

Alter system set db_recovery_file_dest= ‘/u01/oracle/flash_recovery’ scope=both;

Purpose:

This is the location of the flash recovery area. The parameter can be set to any valid file system, or you can use Oracle Database 10g Automatic Storage Management (ASM) disk group.

Note that you must specify the db_recovery_file_dest_size parameter before you specify the db_recovery_file_dest parameter. Failure to do so will result in an ORA32001 error message. In a similar fashion, you must disable the db_recovery_file_ dest parameter before you reset the db_recovery_file_dest_size parameter. Leaving db_recovery_file_dest empty disables the flash recovery area. Here is an example of disabling the flash recovery area by resetting the db_recovery_file_dest parameter:

Alter system set db_recovery_file_dest=’ ‘ scope=both;

Finally, in an Oracle Real Application Clusters environment, you cannot specify these settings for a specific instance; they must be consistent throughout the whole cluster.

Flash Recovery Area Views

The V$RECOVERY_FILE_DEST view, new in Oracle Database 10g, provides an overview of the recovery area that is defined in your database. It provides the size that the flash recovery area is configured for, the amount of space used, how much space can be reclaimed, and the number of files in the flash recovery area.

A new column, IS_RECOVERY_DEST_FILE, can be found in a number of Oracle Database 10g’s V$ views, such as V$CONTROLFILE, V$LOGFILE, V$ARCHIVED_ LOG, V$DATAFILE_COPY, and V$BACKUP_PIECE. This column is a Boolean that indicates whether or not the file is in a flash recovery area.

Another new column, BYTES, can be found in the views V$BACKUP_PIECE and RC_BACKUP_PIECE (an RMAN recovery catalog view). This column indicates the size, in bytes, of the backup-set piece. This can be used to help you determine how much of the flash recovery area your backups are already consuming.

NOTE – Manually removing fixed files from the flash recovery area can have unexpected consequences. Oracle Database 10g does not immediately detect the removal of these files, and thus the space is not reclaimed. If you end up manually removing files (or lose a disk perhaps), use the RMANcrosscheck command along with thedeletecommand to cause Oracle Database 10g to update the current control file information on the flash recovery area.

RMAN Commands Related to Flash Recovery Areas

RMAN has been enhanced with new commands that allow you to back up and restore the flash recovery area. The RMAN command backup recovery area allows you to back up all files required to restore the database via RMAN from a recovery area to an sbt (tape) device. The following types of files are backed up with this command:

  • Full and incremental backup sets

  • Control file autobackups

  • Archive logs

  • Datafile copies

Note that this command does not back up the following:

  • Flashback logs

  • Incremental bitmaps

  • The current control file

  • Online redo logs

As you have seen, the RMAN command backup recovery area backs up all files needed for recovery in the flash recovery area. There is a second command, backup recovery files, that backs up all recovery files that are on the disk, wherever they may be (in flash recovery areas or otherwise). The backup recovery files command must also go to an sbt device and cannot go to disk.

NOTE – The backup recovery area and backup recovery files commands are nice commands to have available when you do your primary backups to disk but want to later back up those backup sets to tape!

RMAN Backup and Restore to a Flash Recovery Area

When a flash recovery area is defined (via the db_recovery_file_dest parameter), RMAN sends backups directly to the flash recovery area. If you are using a local or CFS file system, you will find that RMAN creates a directory structure for the flash recovery area. Typically, the structure includes a directory for the database being backed up and, underneath that directory, another directory for the type of backup.

Recoveries also use the flash recovery area if the appropriate backup set is within the flash recovery area. Also, you can specify a recovery area to use when restoring a control file or SPFILE from an autobackup by using the new recovery area clause, as shown in this example:

RMAN> Restore controlfile from autobackup using recovery area ‘c:recovery';

Other Flash Recovery Area Features

The alter database add logfile and alter database add standby logfile commands, by default, now create an online redo log member in the flash recovery area if the OMF-related parameter db_create_online_log_dest_n is not set. The alter database drop logfile and alter database rename file commands also support files in the flash recovery area.

During database creation, Oracle Database 10g can use the flashback recovery area to store the database control file and online redo logs. If the OMF-related parameter db_create_online_log_dest_n is defined, then the control file and redo logs will be created in those locations, but will not be created in the flash recovery area, even if the flash recovery area is defined. If db_create_online_log_dest_n is not defined but create_file_dest is defined, then the control file and online redo logs will be created in the location defined by create_file_dest. If the parameter db_ recovery_file_dest is also defined, then a copy of the control file and online redo logs will get created there as well. Finally, if only db_recovery_file_dest is defined, then the control file will get created in that location. If none of these parameters is defined, then the control file and online redo logs will be created to a default location, which is OS specific.

This chapter is from Oracle Database 10g New Features, by Robert Freeman (McGraw-Hill/Osborne, 2004, ISBN: 0072229470). Check it out at your favorite bookstore today. Buy this book now.

{mospagebreak title=Using Backup Copies and Fast Recovery}

RMAN allows you to back up the entire database now as a copy image via the backup as copy command. In addition, RMAN allows you to easily switch over to these copy images during a recovery situation, speeding up your recoveries significantly. Let’s look at these new features in a bit more detail.

Using the RMAN backup as copy Command RMAN introduces the backup as copy command, which allows you to create file image copies of the database datafiles, rather than creating backup sets (which is the default). You can use this command for the following kinds of backups:

  • Database

  • Tablespace

  • Datafile

Examples of the use of these commands are shown here:

RMAN> backup as copy database plus archivelog;
RMAN> backup as copy datafile 1;
RMAN> backup as copy tablespace users;

The file backup copies are exact duplicates of the database datafiles, thus they are larger than normal RMAN backup sets. Backup copies can be made to any disk location (and only to disk) via the format parameter. You can also configure a default device for disk copies with the configure command (discussed in more detail shortly) or, if you have configured a flash recovery area (also covered shortly), RMAN will create your backup copies in that area.

NOTE — An error will be generated if the datafiles are in backup mode during a backup copy.

RMAN Backup File Image Copies: The Up Side and the Down Side

So, what are the benefits of RMAN backup file image copies? As you will see later in this section, RMAN backup copies can reduce your mean time to recover significantly. Use of image copies comes with a price, however, chiefly the requirement for additional disk space to store the copies, as opposed to RMAN backup sets. Since copies are not compressed, each full database copy essentially requires that an amount of space equivalent to the size of your database be allocated.

Another risk with datafile copies is the fact that these copies are stored on disk by RMAN. This leads to a pretty obvious single point of failure that you will want to address in your overall backup and recovery scheme.

Configure the Default Device Type to Copy

Oracle Database 10g allows you to configure your default backup type to be a copy backup with the configure device type command, as shown here:

RMAN> Configure device type disk backup type to copy;

Fast Recovery Using RMAN Copies

Previously, if you wanted to restore a database from an RMAN backup, you had a couple of options. The first was to restore the backup-set pieces via RMAN and then recover the database. Even if the backup-set pieces were on disk, this could take a significant amount of time. The second option was to make RMAN copies of each individual datafile and then manually restore from those copies, or locate those copies on a different set of disks and then rename the datafiles from within Oracle Database 10g. This was time- and labor-intensive.

Oracle Database 10g allows you to create a backup copy of the database to a different disk location and then quickly switch over to that disk location via the switch database to copy RMAN command. When the switch database command is issued, RMAN resets the location of the datafiles in the control file to the location of the datafile backup copies previously made via RMAN. After this switch is complete, you then recover the database, using those copies. This method of recovering the database can significantly reduce the overall mean time to recover your database.

Note also that when you switch your database to your datafile copies, you effectively lose one backup of your database. Also, the entire database is then located in one file system, such as the flash recovery area, which can be problematic.

Use RMAN to Back Up the Current Control File

In Oracle Database 10g, RMAN offers a new command, backup current controlfile, which is effectively the same as the alter database backup controlfile command that you would issue from the SQL prompt. The result is a copy of the control file, stored in the location defined (e.g., a location defined via the format command, the default copy value, or the flash recovery area). RMAN also allows you to create a standby control file with the backup current controlfile for standby command. The backup controlfilecopy command allows you to create backups of previous control file copies.

Using the catalog and uncatalog Commands

Oracle Database 10g offers additional commands that allow you to manipulate the catalog entries of backup sets. The catalog command allows you to enter new backup set–related information into the catalog. RMAN will overwrite any preexisting catalog information that conflicts with the information being cataloged. This command can be handy if you need to move the location of your backup-set pieces. Here is an example of the use of this command:

RMAN> catalog backuppiece
‘/opt/oracle/oracle-10.0.0/dbs/backup';

The change backuppiece uncatalog command will remove backup-set pieces from the catalog. If the change backuppiece uncatalog command removes the last remaining backup-set piece, then it will also remove the backup-set record. Here is an example of using the change backuppiece uncatalog command:

RMAN> Change backuppiece ‘/u01/oracle/RMAN/mydb/mydb_user01_01.bak’ uncatalog;

One of the nice uses of the catalog command is to allow you to catalog moved backup-set pieces. If you have moved a large number of backup-set pieces, then it can be a great deal of work to generate a bunch of catalog statements to catalog the moved pieces. Instead, you can use the catalog command with the start with option. The start with option allows you to define the directory that contains the RMAN backup-set pieces to be cataloged. RMAN will then catalog all backup-set pieces in that directory. Here is an example of using the catalog command this way:

RMAN> catalog start with ‘/u01/oracle/RMAN/mydb';

Once you press ENTER, this command prompts you with a list of files to catalog, and asks if you wish to catalog the files listed. If you respond in the affirmative, RMAN catalogs all the backup-set pieces listed (which will be contained in the /u01/oracle/RMAN/mydb directory). This command also allows you to catalog several like-named backup-set pieces. For example, if you want to catalog several backup-set pieces that start with the name backup (e.g., backupset01, backupset02, etc.), then you could issue the following command:

RMAN> catalog start with ‘/u01/oracle/RMAN/mydb/backup';

When you use the catalog start with command, it is indiscriminate about the files it tries to catalog; it will try to catalog everything that matches the argument list. However, as the catalog process proceeds, files that are not backup-set pieces will fail the catalog process and an error will occur. Files that are backup-set pieces will be cataloged successfully, in spite of other errors.

Dropping a Database in RMAN

In Oracle Database 10g, RMAN provides the capability to drop a database to remove all physical database files, by using the drop database command. To drop the database, first log in to the database to be dropped. Then, make sure the database is mounted in exclusive mode, but is not open. Finally, issue the drop database command. If you also want to remove all RMAN database-related backups (but not recovery catalog information), you can include the including backups option when you issue the drop database command. Here is an example call:

RMAN> shutdown
RMAN> startup mount
RMAN> drop database;

Unregistering a Database in RMAN

Prior to Oracle Database 10g, unregistering a database from the recovery catalog was a manual process. Now, Oracle Database 10g makes removing a database from the recovery catalog as easy as issuing the command unregister database. Here is an example:

RMAN> Unregister database mydb;

Note that the backup files for this database, and any associated control file records, are not deleted by this command; only the recovery catalog references to those backup files are deleted. Also note that you only need to be connected to the recovery catalog to issue this command.

This chapter is from Oracle Database 10g New Features, by Robert Freeman (McGraw-Hill/Osborne, 2004, ISBN: 0072229470). Check it out at your favorite bookstore today. Buy this book now.

{mospagebreak title=Changes to Incremental Backups}

Oracle Database 10g offers some new improvements to incremental backups in RMAN. First, the block change tracking file is introduced to allow for better and faster incremental backups. Also, new features allow you to apply incremental backups to file image copies (another Oracle Database 10g new feature introduced earlier in this chapter) for recovery purposes. Let’s look at each of these new features in more detail.

The Block Change Tracking File

Oracle Database 10g improves incremental backups through the introduction of block change tracking. With block change tracking, a block change tracking file is created and used to track all changed blocks. The block change tracking file is updated on a regular basis as redo is generated in the database. The file is then used by RMAN during incremental backups so that, rather than having to read through each datafile to determine whether a block has changed, Oracle Database 10g only needs to read the block change tracking file to know which blocks within a datafile need to be backed up. So, now the time required to back up an Oracle Database 10g database with an incremental backup is more a factor of how much data has changed than a factor of how big the datafiles are. Also, the use of block change tracking has the benefit of reducing the overall size of your incremental backup sets.

By default, Oracle Database 10g does not record block change information in the database. Using the alter database enable block change tracking command, you can enable recording of block change information. Once this command is executed, Oracle Database 10g creates the block change tracking file and keeps it current. To disable block change tracking, you can use the alter database disable block change tracking command.

The block change tracking file is maintained in the directory defined by the db_ create_file_dest parameter, if it is configured. You can also define the location of the block change tracking file via the using file clause of the alter database enable block change tracking command. You can rename the block change tracking file with the alter database rename file command, just as you would with any normal database file. Here is an example of turning on block change tracking, using the using file clause to define where the block change tracking file should be

Alter database enable block change tacking using file ‘/u01/oracle/RMAN/blocktrack/my_db_tracking.chg';

Jonathan Says…

In Chapter 6 we will introduce you to a new feature in Oracle Database 10g, bigfile tablespaces. These tablespaces allow you to store up to 8 exabytes, a rather large amount of data to back up. You probably wondered how you (or RMAN) were going to handle a hot backup of a file that is 8 exabytes—after all, earlier versions of RMAN have to scan the entire file to find out which blocks have changed since the last RMAN backup. Using the block change tracking file is the answer—but it does suggest that if you start thinking about bigfiles, you really ought to be looking at RMAN as your strategic backup mechanism. By the way, this is yet another I/O load on your system—do you have the bandwidth to handle it? Better check the impact carefully.

The block change tacking file size is a factor of a number of things, including the number of enabled redo threads, the size of the database, and the number of RMAN backups for which change data needs to be stored. The formula looks like this:

R=((T * 2)+ B) * (S/250000)

where the following are the values:

  • R The size of the block change tracking file, in bytes

  • T The number of enabled redo threads

  • S The size of the database, in bytes

  • B The number of incremental backups for which RMAN needs to store change tracking data

In a test 800MB database, the tracking file (with one backup) was about 10MB. Note that Oracle Database 10g will only track change tracking data required for a maximum of eight incremental backups.

If you want to see the status of the block change tracking file, you can use the V$BLOCK_CHANGE_TRACKING view, which gives you the name and location of the block change tracking file, its size, and its status.

The V$BACKUP_DATAFILE view can be used to determine how effective the use of the block change tracking file is. This view can tell you what percentage of blocks in the tablespace that RMAN will be reading (via the BLOCKS_READ column). If the percentage is high, then RMAN will take longer to back up the database. If this is the case, you may want to schedule your incremental backups more frequently.

Applying Incremental Backups to Restore Datafile Image Copies

Earlier, this chapter introduced datafile image copies, a new feature in Oracle Database 10g. If you perform incremental backups in Oracle Database 10g, another new feature that might interest you is that you can restore your database using a combination of datafile image copies and incremental backups. To apply incremental backups to datafile copies, you restore the datafile copies (using the RMAN restore command), and then use the recover copy of datafile command to finish the recovery process. Here is an example:

RMAN> Recover copy of datafile 6;
RMAN> recover copy of database;
RMAN> recover copy of tablespace users;

Note that RMAN will return a warning, not an error, if it cannot restore the database forward to the specified or current time with incremental backups.

Recovering Datafiles Not Backed Up

Previous to Oracle Database 10g, a datafile had to have been backed up for RMAN to restore and recover it. In Oracle Database 10g, RMAN allows you to perform recovery of a datafile without it having first been backed up. If the datafile is not available in a backup set, RMAN uses the control file to create an empty copy of that datafile. RMAN can then use archived redo logs to recover the datafile. This implies that, to successfully complete the recovery, Oracle Database 10g requires all archived redo to be available since the datafile was created. RMAN performs this type of recovery automatically if required.

NOTE — You can only restore missing datafiles with the restore datafile command. Therestore database and restore tablespace commands do not support this functionality.

Automatic Channel Failover

In Oracle Database 10g, the behavior of RMAN changes with regard to the failure of a channel during an RMAN backup. In Oracle Database 10g, if a channel fails, the backup process on that channel fails and will not be restarted. However, backups on other remaining channels will continue to run. Once the backup process is complete, RMAN will report errors that occurred during the backup process. This feature ensures that as many datafiles get backed up as possible, in spite of the error condition.

This chapter is from Oracle Database 10g New Features, by Robert Freeman (McGraw-Hill/Osborne, 2004, ISBN: 0072229470). Check it out at your favorite bookstore today. Buy this book now.

{mospagebreak title=Compressing RMAN Backups}

Prior to Oracle Database 10g, RMAN reduced the size of backup images by backing up only the blocks that had been used. That was great if you had a database that was way oversized, but it was of little use for larger databases or databases that had little free space available in the tablespaces.

Oracle Database 10g offers real-life compression (Yea!) of RMAN backup sets through the use of the compressed parameter when issuing the backup command. Here is an example:

RMAN> Backup as compressed backupset database;

Note that only backup sets can be compressed (e.g., database, tablespace, and datafile backups). Specifically, image copies cannot be compressed. Also, by default, compression is disabled. You can use the configure command to define the default disk device to use compression, as shown in this example:

RMAN> configure device type disk backup type to 2> compressed backupset;

NOTE — I found that backup sets were compressed by about 80 percent when compared to a regular backup set of a rather empty database.

Jonathan Says…

The trade-off between bottlenecks can be tricky. In general, you probably want to reduce the I/O load, so compression sounds like a good idea. But if the compression factor is poor and the CPU cost is relatively high, then you may want to switch the compression off to save CPU.

Controlling Backup Rates and Duration

RMAN allows you to control the rate and duration of RMAN backups. This allows you to back up the database with some control over the impact that that backup has on the operating system. The duration parameter of the backup command causes RMAN to back up the database files in question at a rate that allows the backup to complete in the time stated. This allows you to spread the backup over a longer period of time, reducing CPU and disk I/O workloads. By default, if the backup is not completed in the time required, an error occurs. You can use the partial parameter to indicate that Oracle Database 10g should not return an error at the end of the time duration, but rather should execute normally. In any event, a partial backup will be marked as unusable. The minimize load parameter allows you to control the rate of the backup even further. The minimize load parameter indicates that RMAN should monitor the speed of the backup process and adjust the throughput rate as required if the backup appears it will consume less time than the stated duration. Here is an example of the use of these commands:

RMAN> backup as copy duration 5:00 minimize load database;

RMAN-Related TSPITR Changes

Oracle Database 10g removes some of the manual work associated with the use of tablespace point-in-time recovery. You no longer need to create the Oracle auxiliary instance manually; rather, RMAN creates the auxiliary instance for you, performs the TSPITR, and then removes the auxiliary instance.

RMAN Enhanced Scripts

RMAN in Oracle Database 10g allows you to store text-based scripts in the RMAN catalog repository, and these scripts can be used by any database that connects to the RMAN catalog repository. With the enhanced script features, you can load scripts into the recovery catalog with either the create script command (for a local script that is only visible to the database that loaded it) or the create global script command (for a global RMAN script that is accessible to all databases). Here is an example of creating an enhanced global script:

Create global script full_db_backup from file ‘/u01/admin/RMAN/backup/backup.rman';

You can replace scripts with the replace script or replace global script command, and you can remove scripts with the delete script command. You can also reverse the process, saving a stored script to a file via the print script or print global script command. The list script names and list global script names commands provide a listing of all global scripts. Also, the list all script names command provides a listing of all scripts in the recovery catalog.

To execute the stored scripts, use the execute script or execute global script command. It is possible for a private and a global script with the same name to exist. In this case, the execute script command executes the private script first; if a private script does not exist, then it executes the global script. A global script is the only script executed when the execute global script command is used.

This chapter is from Oracle Database 10g New Features, by Robert Freeman (McGraw-Hill/Osborne, 2004, ISBN: 0072229470). Check it out at your favorite bookstore today. Buy this book now.

{mospagebreak title=New Flashback Features}

Oracle Database 10g offers some new features associated with flashback query. The first is Flashback Database, which allows you to flash back the entire database to a specific point in time. The second feature is Flashback Drop, which allows you to undrop an object in the Oracle Database 10g database. We will also look at flashback version query, which allows you to look at a set of changes that have occurred in the database between two sets of times. Also, Oracle Database 10g allows you to configure a tablespace for guaranteed retention, ensuring that the undo in the tablespace will be retained. The following sections discuss these features in more detail.

Flashback Database

Flashback query is a very powerful feature in Oracle Database that was introduced in Oracle9i and enhanced in Oracle9i Release 2. Now, Oracle Database 10g offers even more functionality in the form of Flashback Database. Flashback Database allows you to flash back the entire database to a specific point in time. This can be useful to resolve problems such as logical data corruption that might be caused by wayward application code. Also, Flashback Database can help in the resolution of user errors that might cause the loss or unintentional change in data (we all know that never happens!).

NOTE — Flashback Database is not a means of recovering the database in the event of some physical loss, or recovering from some form of physical corruption.

One of the principle benefits of Flashback Database is that it can be a much faster method of recovering data than other recovery methods (e.g., via tablespace point-in-time recovery or logical backups or Log Miner). Let’s look in a bit more detail at the architecture associated with Flashback Database, and then look at how to use Oracle Database 10g’s Flashback Database.

Flashback Database Architecture

Flashback Database involves some new Oracle Database 10g architectural components that you will want to be aware of: Flashback Database logs, and RVWR, one of the new Oracle Database 10g background processes.

Flashback Database Logs – A Flashback Database log is a new kind of log file that contains the before image of Oracle database blocks. The Flashback Database logs must be created in the database recovery area (and thus a flashback recovery area must be configured). Flashback logs are not archived, as are database redo logs, and they are not used for physical database recovery.

The RVWR Background Process – When Flashback Database is enabled, a new background process, RVWR, is started. This process is responsible to write Flashback Database data to the Flashback Database logs.

This chapter is from Oracle Database 10g New Features, by Robert Freeman (McGraw-Hill/Osborne, 2004, ISBN: 0072229470). Check it out at your favorite bookstore today. Buy this book now.

{mospagebreak title=Using Flashback Database}

You can use Oracle Database 10g’s Flashback Database features from three different tools: SQL*Plus, RMAN, and OEM. Let’s look at some basic requirements for using Oracle Flashback Database.

Basic Flashback Database Use Requirements – To use Flashback Database, the database must first be in archivelog mode. Also, you must have any Real Application Clusters databases mounted in exclusive mode. You must also have a flashback recovery area configured, as described earlier in this chapter (done by setting the db_recovery_file_dest and db_recovery_file_dest_size database parameters). The compatible parameter should be set to 10.0 or higher.

Jonathan Says…

Oh dear, another Oracle process generating yet more I/O. You had better have a good reason for wanting flashback logging switched on. You should always be very reluctant about introducing expensive run-time features without some very solid justification.

Flashback Database from SQL*Plus To use Flashback Database from the SQL*Plus prompt, you must connect to the database by using the AS SYSDBA login. To enable Flashback Database from SQL*Plus, follow these steps:
  1. Shut down the database.

  2. Mount the database (if it is a Real Application Clusters database, mount it in exclusive mode).

  3. Via the alter system command, set the database parameter db_flashback_ retention_target to a value that defines how far you want to flash back the database. Although this indicates to Oracle Database 10g how far you wish to flash back your database, the availability of undo back to that point in time is critical. Here is an example:

    alter system set
    db_flashback_retention_target=2880;
  4. Enable Flashback Database with the alter database command using the new Oracle Database 10g syntax, flashback on:

    alter database flashback on;
  5. Now, open the database with the alter database open command. You are now prepared to use flashback database! Make some changes to your database at this point. Once you have made some changes, record the current SCN (from the CURRENT_SCN column in the V$DATABASE view if you like!).

  6. Shutdown and then mount the database again.

  7. Use the Flashback Database to timestamp or the Flashback Database to scn command to enable Database Flashback:

    SQL> Flashback Database to timestamp (sysdate-2);
    SQL> Flashback Database to scn 2034455;

  8. Open the database in read-only mode with the alter database open read only command, and make sure that you have flashed back the database to the point in time you are interested in.

  9. Once you are sure the database is at the point in time that you wish to restore it to, open the database using the alter database open resetlogs command.

  10. You can flash forward or backwards via the flashback database command as much as you like until you have opened the database with the alter database open resetlogs command. Once the alter database open resetlogs command has been issued, the database changes cannot be altered.

NOTE — It’s always a good idea to record the SCN of the database before you perform a flashback operation. That way you can recover to that SCN if need be.

You can disable database flashback through the use of the alter database flashback off command (the database must be mounted and in exclusive mode). If you wish to disable flashback logging for a specific tablespace, use the alter tablespace flashback off command; you can re-enable flashback logging with the alter tablespace flashback on command. If you disable flashback for a specific tablespace, you must also take its associated datafiles offline.

Flashback Database from RMAN – In Oracle Database 10g RMAN supports the use of Flashback Database to restore the database back to a specific point in time, using the database SCN or log sequence number. Using the RMAN Flashback Database command allows you to restore the database as it looked at a specific point in time, as long as the undo is available to flash back to that point in time. This makes for much quicker restores. Here is an example of using RMAN to restore the database using Flashback Database (note that the database must be mounted, and not open, to execute these RMAN operations)

– Flashback to a specific date and time RMAN> Flashback Database to_time = to_date(‘2003-12-01 01:05:00′,’YYYY-MM-DD HH24:MI:SS’);

– Flashback up to but not including a specific SCN RMAN> Flashback Database to scn = 302223;

– flashback up to but not including a specific sequence — number. RMAN> Flashback database until sequence=1022 thread=1;

Once RMAN has completed its Flashback Database operation, you can open the database in read-only mode and make sure it’s recovered to the point you want it recovered to. If it is, simply open the database using the alter database open resetlogs command to open the database for write operations.

This chapter is from Oracle Database 10g New Features, by Robert Freeman (McGraw-Hill/Osborne, 2004, ISBN: 0072229470). Check it out at your favorite bookstore today. Buy this book now.

{mospagebreak title=Flashback Database Views}

Oracle Database 10g offers several views that are associated with database flashback:

  • V$DATABASE

  • V$FLASHBACK_DATABASE_LOG

  • V$FLASHBACK_DATABASE_STAT

V$DATABASE View – The V$DATABASE view has a new column associated with it for Flashback Database called FLASHBACK_ON. This column is a Boolean value that indicates whether or not Flashback Database is enabled.

V$FLASHBACK_DATABASE_LOG View – The V$FLASHBACK_DATABASE_LOG view is a new view in Oracle Database 10g that allows you to monitor the Flashback Database retention target. This view can help you to estimate the amount of space in the recovery area that will be required for the flashback workload. This view contains columns that allow you to query the following:

  • The lowest Flashback Database SCN or time (OLDEST_FLASHBACK_SCN and OLDEST_FLASHBACK_TIME columns)

  • The retention target time (RETENTION_TARGET column)

  • The current size of the flashback data, in bytes (FLASHBACK_SIZE column)

  • The estimated size of flashback data that you need for your current target retention (ESTIMATED_FLASHBACK_SIZE column)

V$FLASHBACK_DATABASE_STAT View – The V$FLASHBACK_DATABASE_STAT view can be used to monitor the overhead of maintaining the flashback data in the Flashback Database logs. From this view, you can estimate the total amount of space that will be required for future Flashback Database operations.

Flashback Database Limitations

Some limitations to Flashback Database exist. You cannot use Flashback Database to flash back a database to a point in time before the following operations occurred:

  • The database control file has been restored or re-created

  • The object that you need to query belongs in a tablespace that has been dropped

  • The database datafile that contains the object to be queried has been shrunk

  • A recovery through the resetlogs command has occurred

As an example, suppose that you drop the TOOLS tablespace and re-create it and the objects within it. You cannot flash back the database to a point before you dropped the TOOLS tablespace and successfully query objects in that tablespace (though you can query objects in other tablespaces at that time). You can, however, flash back the database to a point-in-time after you re-created the TOOLS tablespace, and perform a query against any changes that occurred in that tablespace after it was re-created.

Flashback Drop

Oracle Database 10g offers Flashback Drop. This feature allows you to undo the effects of a drop table command via the new flashback table SQL command using the to before drop syntax. This section looks at the Flashback Drop of a table via the flashback table command. First, it introduces you to the recycle bin. Then, it shows how to undrop tables with the flashback table command. Finally, it describes the data dictionary views associated with Flashback Drop.

NOTE — You do not need to enable Flashback Database in order to use this feature.

Recycle Bin

To support Flashback Drop, Oracle Database 10g introduces the concept of a recycle bin. So, now when a table is removed, it is moved to the recycle bin and remains there until the recycle bin is purged. Thus, a statement like drop table parts causes the parts table to be moved to the recycle bin. If you want to drop the parts table and not have it get moved into the recycle bin, use the new purge parameter of the drop table command:

SQL> Drop table parts purge;

Note that objects that are dropped and moved into the recycle bin do not have their extents deallocated, so they will continue to take up space in your database until you purge them from the recycle bin or free space is consumed, in which case Oracle Database 10g will purge older objects from the recycle bin automatically. Use the purge table command to purge objects from the recycle bin, as shown in this example:

Purge table rb$$44022$table$0;

You can also use the purge tablespace command to purge all objects in the recycle bin associated with a specific tablespace, as shown in this example:

Purge tablespace all_users;

Note that this command will not remove objects in the tablespace (not already in the recycle bin) or drop the tablespace itself.

You might have noticed in the purge table command example that the command referenced a rather odd-looking object name, rb$$44022$table$0. The reason for this odd name is that Oracle Database 10g renames objects when they are moved to the recycle bin, to avoid naming conflicts. The naming convention takes the form

rb$$OBJ#$$ObjectType$Version

where:

  • OBJ# is the dictionary object number of the object

  • ObjectType is the type of the object. This might be table, normal index, or bitmap index.

  • Version makes sure the object name is unique.

Flashback Drop Data Dictionary Views

You can find a list of objects in the recycle bin, and their associated object names, through a query against the USER_RECYCLEBIN view. Here is an example of a query against this view that demonstrates the relationship between the object name of the objects in the recycle bin and the old database objects:

SELECT object_name, original_name, type
from user_recyclebin;

What’s really cool about the recycle bin is that you can continue to query an object after you have dropped it! You simply query the object based on its new object name, as shown in this example:

Select * from rb$$44022$table$0;

The flashback table to before drop Command

The benefit of the recycle bin is that it enables you to recover a table that you have dropped. To do this, you issue the flashback table command using the to before drop parameter:

SQL> flashback table mytab to before drop;

This is a very easy way to recover your table from the recycle bin. If you have created and dropped the same object multiple times, then the one most recently dropped will be recovered. You can use the recycle bin name for the table if you prefer, which allows you to restore the specific version of the table that you wish to restore. Also, you can use the rename to clause to rename the table that is being recovered as seen in this example:

SQL> flashback table mytab to before drop rename to test;

Once the table is recovered by the flashback table command, it is removed out of the recycle bin (though other, older versions of that object may still exist in the recycle bin).

When you retrieve a table from the recycle bin, all associated indexes will be retrieved as well, except bitmap join indexes, which are not saved in the recycle bin after a drop table operation. If the recycle bin starts running out of space, Oracle Database 10g will remove index entries before it removes table entries. Thus, flashback table operations might not restore all indexes.

Triggers and constraints are restored as well except foreign key constraints. Note that all restored, table-related objects will be restored with their recycle bin names, rather than their original names. So, you might want to make a note of the original names before you do the restore. Also, materialized views (Mviews) that are dependent on the tables being dropped are dropped and are not saved in the recycle bin, so they are lost forever.

Flashback Versions Query

Can you conceive of a case where you would like to see all the versions of data for a given row over a given point in time? Perhaps you would like to see how much a specific employee’s salary has changed over the last month for auditing purposes. Oracle Database 10g offers Flashback Versions Query for just such an occasion. With this feature, you can define a minimum and maximum time (using the versions between timestamp clause of the select statement) or scn range (using the versions between scn clause of the select statement), and then query a specific row or set of rows in a table to see a list of all values assigned to those rows.

Jonathan Says…

Don’t forget to ask your application suppliers, the ones who create and drop temporary tables all over the place, how they plan to handle the side effects of this feature. It’s a good thing that items in the recycle bin will be purged automatically on an “out of space” condition.

Here is an example of using Flashback Versions Query. In this example, we want to look at a list of employee salary values and how they have changed between 30 minutes ago and 1 minute ago.

SELECT ename, sal FROM emp
 VERSIONS BETWEEN TIMESTAMP
   SYSTIMESTAMP – INTERVAL ’30’ MINUTE AND
   SYSTIMESTAMP – INTERVAL ‘1’ MINUTE
   WHERE empno=22;

Note that the changes that are shown are only committed changes (changes committed or rolled back). Also note that you cannot use the versions between clause of the select statement when querying a view, but this clause can be used within the definition of a view. Also, the ability to use Flashback Versions Query is dependent on the availability of undo records, which also implies that it’s dependent on the setting of the undo_retention parameter. If the time or SCN listed in the BETWEEN clause represents a point in time beyond the undo_retention parameter setting, then an error will be returned.

Flashback version query requires that the flashback and select privileges be granted to any nonprivileged user who you wish to allow to use flashback version queries. Also, if you will be using Flashback Versions Query or other Oracle flashback features frequently on specific objects, you might want to consider another new feature, Guaranteed Undo Retention, which is the next topic.

This chapter is from Oracle Database 10g New Features, by Robert Freeman (McGraw-Hill/Osborne, 2004, ISBN: 0072229470). Check it out at your favorite bookstore today. Buy this book now.

{mospagebreak title=Configuring Guaranteed Undo Retention}

With Oracle Flashback Query and all the new derivatives of Flashback Query in Oracle Database 10g, dependency on the presence of undo can be critical. If you have a database that you will be performing frequent flashback operations on, then you might want to consider setting the UNDO Tablespace to guarantee the retention of all undo information until it has expired (as defined by the undo_ retention parameter). Note that if the UNDO Tablespace is set to guarantee retention of undo, then operations that need to generate undo will fail if sufficient undo area is not available, because Oracle Database 10g will not expire undo before its time when undo retention is specified.

You can use the retention guarantee parameter of the create UNDO Tablespace or alter UNDO Tablespace command, as shown in these examples:

– Assumes OMF is configured.
Create UNDO Tablespace undotbs01
size 200m autoextend on
retention guarantee;
alter tablespace undotbs01 retention guarantee;

To reset an UNDO Tablespace so that undo retention is no longer guaranteed, use the alter tablespace retention noguarantee command.

The create database command also supports the retention guarantee clause. You can tell if you have put the UNDO Tablespace in retention mode through a query against the RETENTION column of the DBA_TABLESPACES view. Here is an example:

Select tablespace_name, retention from dba_tablespaces;

Flashback Transaction Query

Oracle Database 10g provides the ability to easily reconstruct SQL statements that have been previously executed by the database. Previously, you would need to use Log Miner to generate SQL redo statements that could be used to replicate SQL statements executed in the database. Now, Flashback Transaction Query can be used to reconstruct the SQL statements used to make changes in the database, and those that can be used to undo the change.

Flashback Transaction Query is supported through the new Oracle Database 10g view FLASHBACK_TRANSACTION_QUERY. The use of this view is dependent on the presence of undo, so the undo_retention parameter needs to be set in such a way as to preserve the undo that you will need. Likewise, you may want to configure guaranteed undo retention (as described earlier in this chapter) as well.

The following is an example of using the FLASHBACK_TRANSACTION_QUERY view. In this case, suppose that records got inserted into the mytab table in the last hour, and we want to remove all of those records. We can query the FLASHBACK_ TRANSACTION_QUERY view for all SCNs between 21553 and 44933 (we could use a timestamp as well) and, using the UNDO_SQL column, extract the SQL that will be required to undo those operations:

SELECT undo_sql FROM flashback_transaction_query
WHERE table_owner=’ROBERT’ and table_name=’MYTAB’
AND start_scn between 21553 and 44933;

NOTE — The indexing on the FLASHBACK_TRANSACTION_QUERY view seems to be lacking, and some queries (like the one in the example) can take some time to return.

Flashback Table

Restoring a table to a point in time different than that of the rest of the database can be a messy operation. If you are lucky, you will have an export from the point in time right before the data change. The other possibility is to use tablespace point in time recovery, but that is time consuming and messy. What if, instead, we could just flash back our table to the point in time we are interested in. Well, with Oracle Database 10g we can! Now, with the flashback table command, you can flash back an Oracle Database 10g table based on timestamp or database SCN. All flashback table operations must be at the beginning of any transaction, and flashback table operations are not supported for the SYS user. Here is an example:

SQL> Commit;
SQL> Flashback table emp to SCN 220360;
SQL> flashback table emp to TIMESTAMP
to_timestamp(‘2003-09-30 09:00:00′,
     ‘YYYY-MM-DD HH:MI:SS’);

To be able to flash back a table, the table must have row movement enabled via the alter table enable row movement command (row movement is disabled by default). Enabling row movement is something that can be done just before you issue the flashback table command, so you don’t need it enabled all the time. The flashback table command allows you to flash back or flash forward; thus, you can undo the effects of a previous flashback table command. You cannot flash back to a time prior to most DDL operations on the table being flashed back, or before the last time that the database was opened with the resetlogs command. Also, when you execute a flashback table statement, the execution of that statement is recorded in the alert log.

NOTE — Since you can flash forward, it is a good idea to record the current SCN of the database before you flash back a table. The current SCN is available in the CURRENT_SCN column in the V$DATABASE view. Record the SCN before you issue the flashback tablecommand, not after.

This chapter is from Oracle Database 10g New Features, by Robert Freeman (McGraw-Hill/Osborne, 2004, ISBN: 0072229470). Check it out at your favorite bookstore today. Buy this book now.

{mospagebreak title=New Transaction Recovery Monitoring Features}

Oracle Database 10g offers enhancements to monitoring the rollback of transactions by SMON, and offers historical information about transaction recovery and rollback operations. This allows you to determine how much work remains during recovery operations. This monitoring is supported via the V$FAST_START_TRANSACTIONS view, which provides information about transactions that Oracle Database 10g is recovering as well as transactions that have been recovered. Also, the V$FAST_ START_SERVERS view provides information about recovery operations that the database server is performing.

The following SQL provides a report that allows you to track transaction recovery during instance recovery:

Select state, undoblocksdone, undoblockstotal, cputime
FROM v$fast_start_transactions;

Transactions that are recovered will have a state of RECOVERED, thus these views provide historical information on rollbacks and recoveries. This information is available until the next time the instance is cycled.

NOTE — Smaller recovery operations do not show up in the views V$FAST_START_TRANSACTIONS and V$FAST_START_SERVERS.

The V$SESSION_LONGOPS view can now be used to monitor transaction rollback operations. This can assist you in determining how long a given rollback operation will take to complete.

New Data Guard Features

In Oracle Database 10g, Data Guard comes with a number of new features that you will want to be aware of. These include

  • The ability to apply redo in real time on both physical and logical standby databases

  • A new attribute, valid_for

  • New features related to transmission of database generated redo

  • New parameters associated with Oracle Database 10g standby databases

  • Changes to the way that Oracle Database 10g will start a standby database

  • The ability of the ARCH process to write directly to the standby redo logs

  • The ability to assign threads to standby redo log groups

  • Enhancements to Oracle Database 10g logical standby database

Apply Redo in Real Time

Prior to Oracle Database 10g, at best, redo could be shipped to a physical standby database, in real time, and it would be stored in standby redo logs. Unfortunately, that redo would still not be applied to the physical standby database until a log switch occurred on the primary database. Thus, the data in the primary and standby databases was always physically divergent. Logical standby databases were even worse, since they only supported application of redo from archived redo logs on the remote database after a log switch. As a result, not only was the logical standby database data divergent from that of the primary, but the redo data was really at risk too. This lag in applying the redo also has implications with mean time to recover, since the standby has to apply the remaining redo in the standby redo log before it can come up. This can delay the opening of the standby database as the new primary database.

Oracle Database 10g solves both of these problems! Now the log apply services can be configured to apply redo data almost in real time from the standby redo logs. When configured for real-time application, redo will be written to the standby database itself at nearly the same time (by the Managed Recovery Process [MRP] or Logical Standby Process [LSP]) that the primary database LGWR and Remote File System (RFS) processes write the redo to the standby redo log files. Let’s look in a bit more detail at the use of real-time log apply and how it works on both physical and logical standby databases.

NOTE — The Dataguard Broker also supports real-time apply.

Real-Time Log Apply: Physical Standby Database

If the standby database is a physical standby database, real-time log apply can only occur while the physical database is in log apply mode. If the physical standby database is in read-only mode, then no application of redo data will occur. Once the physical standby database is put back into application mode, the log apply service will catch up and return to real-time apply as quickly as possible.

For a physical standby database, the MRP is responsible for application of the redo log files. The application of the redo log file data occurs once the RFS process has actually written the redo. To start the real-time application process on the standby database, use the alter database recover managed standby database command along with the new using current logfile parameter, as shown in this example:

Alter database recover managed standby database using current logfile;

Real-Time Log Apply: Logical Standby Database

The LSP is responsible for the application of redo on the standby database. Again, once the RFS process has finished writing the redo from the primary database, the LSP will pick up that redo and apply it. To start the real-time application of redo, use the alter database start logical standby command with the new apply immediate clause, as shown in this example:

Alter database start logical standby apply immediate;

Monitoring Real-Time Log Apply

You can determine if the logical standby database is running in real-time apply mode by querying the V$ARCHIVE_DEST_STATUS view on the standby database. The column RECOVERY_MODE will indicate MANAGED REAL TIME APPLY if the standby database is in real-time apply mode.

NOTE — It is not possible to configure a delay if you are using real-time apply mode. Oracle Database 10g will simply ignore the delay attribute if it is used.

This chapter is from Oracle Database 10g New Features, by Robert Freeman (McGraw-Hill/Osborne, 2004, ISBN: 0072229470). Check it out at your favorite bookstore today. Buy this book now.

{mospagebreak title=The valid_for Attribute}

The valid_for attribute is a new attribute that can be associated with the log_ archive_dest_n parameter in Oracle Database 10g. This attribute allows you to define when an archive log destination will be used and the role of the archived redo at that destination.

The valid_for attribute takes two parameters, the archival_source parameter and the database_role parameter. The archival_source parameter defines when the destination is used. Valid settings for this parameter are as follows:

  • ONLINE_LOGFILE This destination is used only when archiving online redo log files. This destination is not used when archiving standby redo log files, or if log files are being received from another database.

  • STANDBY_LOGFILE This destination is used only when standby redo log files are being archived or if archived redo logs are being received from another database.

  • ALL_LOGFILES This destination can be used in either role, archiving online or standby redo log files.

The database_role parameter is the second parameter of the valid_for attribute. As the name implies, this parameter defines the role of the database that the parameter is associated with and, thus, when the archive log destination will be used. Valid settings for this parameter are as follows:

  • PRIMARY_ROLE This destination is used only when the database is in the primary database role.

  • STANDBY_ROLE This destination is used only when the database is in a standby role. This applies to both physical and logical standby databases.

  • ALL_ROLES Open the barn doors Bessie! This destination is used if the database is in primary or standby mode.

The parameters for the valid_for attribute can be specified in any order, but only specific combinations are valid for the attribute. For example, the combination of STANDBY_LOGFILE and PRIMARY_ROLE is invalid and will generate an error at database startup.

Here is an example of an archivelog destination having been set, using the valid_for attribute of the log_archive_dest_1 parameter:

LOG_ARCHIVE_DEST_1= service=STANDBY_DB VALID_FOR = (STANDBY_LOGFILE, STANDBY_ROLE)

Data Dictionary Views and valid_for

The V$ARCHIVE_DEST data dictionary view has a new column, VALID_NOW, that indicates if the archivelog destination will be used. Valid values include

  • YES The destination is properly defined and will be used.

  • WRONG VALID_TYPE The archivelog destination is properly defined, but in the current role it cannot be used. This might occur if the archive log destination was defined for a standby database, and the database was currently configured as a primary database.

  • WRONG VALID_ROLE The archivelog destination is not defined correctly for the current database role (primary or standby).

  • UNKNOWN Indicates that the archivelog destination is not defined.

Also, the V$ARCHIVE_DEST view provides two additional new columns, VALID_TYPE and VALID_ROLE, that allow you to see how the valid_for parameter for each archivelog destination is configured.

Redo Transmission Enhancements

Oracle Database 10g provides two new enhancements with regard to redo transmission. First of all, database authentication must now be set up for all databases. This means that the remote_login_passwordfile parameter must be set to either SHARED or EXCLUSIVE at both the primary and all standby sites. Also, a password file must be generated, and all SYS passwords must be set the same at all sites. If you change the SYS password, it will be automatically changed at all sites.

Oracle Database 10g now also supports encryption of the redo stream to the standby databases as an optional feature. You must have the Oracle Advanced Security option installed at all database sites, and have configured Oracle Net for encryption and integrity checksumming.

This chapter is from Oracle Database 10g New Features, by Robert Freeman (McGraw-Hill/Osborne, 2004, ISBN: 0072229470). Check it out at your favorite bookstore today. Buy this book now.

{mospagebreak title=New Standby Database Parameters}

Oracle Database 10g depreciates the lock_name_space parameter in favor of a new parameter, db_unique_name. Going forward, you should use the db_unique_name parameter to assign unique names to each of your standby databases. The name can be up to 30 characters long, and each Real Application Clusters instance should use the same name.

NOTE — Deprecation of a parameter indicates that it is still available for use. If a parameter is obsolete, then it is no longer available for use in the database.

The remote_archive_enable parameter is replaced with the log_archive_config parameter (Oracle recommends replacing remote_archive_enable with log_ archive_config in Oracle Database 10g). The log_archive_config parameter allows you to define the standby database configuration currently in use, and update it dynamically. The db_unique_name parameter contains the name of each database in the standby database configuration, and then defines the role in the configuration as one of these four values:

  • send Indicates that the database, when in primary database mode, can send redo logs to the standby database (default).

  • nosend Opposite of send, the database cannot send redo logs to the standby.

  • receive Indicates that when running in standby mode, the database can receive redo logs from the primary database (default).

  • noreceive Opposite of receive, the database cannot receive redo logs from the primary database.

The log_archive_config parameter should be the same for each Real Application Clusters instance. Also, the log_archive_config parameter has an attribute, db_config, that lists all databases in the standby database configuration. You can dynamically add databases to the configuration by changing this setting dynamically. This eliminates the need to shut down the database when running in maximum availability or maximum protection mode.

Here is an example of the configuration of the log_archive_config parameter, using the db_config parameter:

LOG_ARCHIVE_CONFIG=(‘SEND,RECEIVE, DB_CONFIG=(‘MAIN_DB_DFW’,’STBY_NY’,’STBY_LAX’)’)

Changes to Standby Database Startups

Prior to Oracle Database 10g, you would first need to start the database instances (startup nomount) and then either mount it as a standby database and start managed recovery, or open the database in read-only mode.

In Oracle Database 10g, if you issue the startup mount command, Oracle Database 10g reads the database control file and, if the database is a standby database, mounts the database as a standby database in preparation for managed recovery to be started. You still need to start managed recovery. Also, if you have issued the startup command and the database is a standby database, it will open the standby database in read-only mode.

ARCH Process Writes to Standby Redo Logs

In Oracle Database 10g, the ARCH process now has the ability to write to standby redo logs. This helps with the registration of partially archived redo logs and allows for the configuration of an almost unlimited number of cascaded redo log destinations.

Assign Threads to Standby Redo Log Groups

Oracle Database 10g allows you to assign standby redo logs to specific redo threads if you are running a Real Application Clusters configuration. This is supported with the new thread parameter of the alter database add standby logfile command. The assignment of a thread is optional, however, and Oracle Database 10g will assign the standby redo log to a thread as required.

Logical Standby Database Enhancements

Oracle Database 10g offers a number of improvements in logical standby databases. These include the following:

  • The ability to instantiate your logical standby database with zero downtime

  • The ability of logical standby databases to support maximum protection mode

  • New SQL Apply support for data types

  • Optimized switchover operations on logical standby databases

  • New data dictionary views to manage standby databases

  • The ability to bypass the SQL Apply services to make changes to the logical standby database

  • The ability to skip a failed transaction

Instantiate a Logical Standby Database with Zero Downtime

Prior to Oracle Database 10g, instantiation of a logical standby database would likely require an outage of the primary database, because the primary database would need to be quiesced, an operation that required that Resource Manager be enabled at database startup. Since many production databases operate without Resource Manager enabled, this would require a cycle of the database. Also, a quiesce of a database could take a long time, particularly in databases with a great deal of activity. Oracle Database 10g removes the requirement to quiesce the database before making the online backup that is the source of the logical standby database. This makes the creation of the logical standby database possible without any downtime at all. This change is supported by changes to the standby control file in Oracle Database 10g.

The following is a quick highlight of the steps needed to create a logical standby database, to demonstrate the changes in the procedure. I recommend that you look at the Oracle documentation (“Oracle Data Guard Concepts and Administration”) for more detail on this procedure (as it may change in interim releases of the database). The general steps are as follows:

  1. Take an online backup of the primary database (no quiesce is required; also note there is no need to record the SCN at the end of the backup).

  2. After the backup is complete, create a logical standby database control file on the primary by using the alter database create standby logical control file command:

    Alter database Create standby logical control file AS ‘/tmp/control_logical.fil';
  3. Copy the backed-up datafiles, archived redo logs, and the logical standby control file to the location where you will create the logical standby database.

  4. Restore the database at the standby site with the logical standby control file. Do not open the database.

  5. Configure log transport services on the primary and standby database sites so that redo can be shipped from the primary site to the standby sites as it’s generated.

  6. Start managed recovery on the standby database with the alter database command:

    Alter database recover managed standby database;

    Note that this is the same method of recovering a physical standby database.

  7. Activate the standby database with the alter database command:

    Alter database activate standby database;
  8. Using the DBNEWID program, change the DBNAME and DBID of the standby database. Follow the instructions in the Oracle Database 10g Database Utilitiesguide on how to do this.

  9. Start the logical standby database log application services:

    Alter database start logical standby apply;

Once these steps are complete, you have created an Oracle Database 10g logical standby database!

Logical Standby Database Support for Maximum Protection Mode

Previously, logical standby databases did not support maximum protection mode. This implied that there was always some level of data divergence between the primary and the logical standby database, which meant that there was a risk of data loss during an unplanned switchover operation.

Oracle Database 10g allows you to configure a logical standby database in maximum protection mode. You can now create standby redo logs for a logical standby database, which is required for maximum protection mode, and you can configure the primary database to send redo to the logical standby database in maximum protection mode.

This chapter is from Oracle Database 10g New Features, by Robert Freeman (McGraw-Hill/Osborne, 2004, ISBN: 0072229470). Check it out at your favorite bookstore today. Buy this book now.

{mospagebreak title=New SQL Apply Support for Data Types}

SQL Apply now supports a number of new data types. These include CLOB, NCLOB, LONG, and LONG_RAW. Also, two new Oracle Database 10g data types, BINARY_FLOAT and BINARY_DOUBLE, are supported by SQL Apply. SQL Apply also supports tables with columns set to UNUSED. Note that index organized tables with overflow segments or BLOB columns are still not supported.

Optimized Switchover Operations on Logical Standby Databases

Switchover operations to logical standby databases are optimized with the new prepare to switchover to command. With the alter database prepare to switchover to primary command, the logical standby database will proceed to build the Log Miner dictionary before the actual switchover operation occurs from a standby to a primary configuration. The alter database prepare to switchover to standby command notifies the primary database that it will soon find itself converted to a standby role. After the alter database prepare to switchover command is executed, it should be followed by the alter database commit to switchover command.

As a result of these new commands, the process to switch over between primary and standby mode has changed. I recommend that you look at the Oracle documentation (“Oracle Data Guard Concepts and Administration”) for more detail on this procedure (as it may change in interim releases of the database). However, in summary, the new steps are as follows:

  1. Issue the alter database prepare switchover to standby command on the primary database.

  2. Issue the alter database prepare to switchover to primary command on the logical standby database. Watch for any errors that might be raised by Oracle Database 10g during the execution of this command.

  3. On the primary database, start the switchover operation via the alter database commit to switchover to logical standby command.

  4. On the logical standby database, after the apply commits, finish the switchover operation with the alter database commit to switchover command.

  5. On the new logical standby database, start the SQL Apply service using the alter database start logical standby apply command.

NOTE — After you have used the prepare switchover commands, you should complete the commit to switchover operations as soon as possible. The longer you wait, the longer the actual switchover will take.

New Data Dictionary Views to Manage Standby Databases

Oracle Database 10g offers new and updated data dictionary views to assist you in managing your standby database. These include

  • DBA_LOGSTDBY_UNSUPPORTED This view defines unsupported object storage attributes.

  • DBA_LOGSTDBY_LOG This view allows you to determine which archived redo logs have been applied to the standby database.

  • DBA_LOGSTDBY_PROGRESS This view has new columns that provide more detail on the progress of the SQL Apply service. These columns include:

    • APPLIED_SEQUENCE# Sequence number of the log that contains the APPLIED_SCN column, which existed prior to Oracle Database 10g

    • APPLIED_THREAD# Thread number for the log associated with the APPLIED_SCN column

    • READ_SEQUENCE# Sequence number of the log associated with the READ_SCN column

    • READ_THREAD# Thread number of the log that is associated with the READ_SCN column

    • NEWEST_SEQUENCE# Sequence number of the log associated with the NEWEST_SCN column

    • NEWEST_THREAD# Thread number of the log that is associated with the NEWEST_SCN column

Bypassing the SQL Apply Services to Make Changes to the Logical Standby Database

If you need to make changes to your logical standby database (adding indexes, etc.), you need to bypass the Data Guard process. New commands that supercede the use of the Oracle-supplied procedures dbms_logstdby.guard_bypass_on and guard_ bypass_off are available in Oracle Database 10g. The new commands are alter session enable guard and alter session disable guard.

Skipping a Failed Transaction

In many cases, if the SQL Apply operation halts, all you really want to do is skip the transaction that failed and continue on with the next transaction. Oracle Database 10g makes this easy with the skip failed transaction option of the alter database start standby apply command. This command is identical to the dbms_logstby.skip_ transaction stored procedure, but will find the transaction automatically and then restart the SQL Apply operation.

This chapter is from Oracle Database 10g New Features, by Robert Freeman (McGraw-Hill/Osborne, 2004, ISBN: 0072229470). Check it out at your favorite bookstore today. Buy this book now.

[gp-comments width="770" linklove="off" ]
antalya escort bayan antalya escort bayan