Oracle Database 10g: Top Features for DBAs
Release 2 Features Addendum

Oracle ACE Arup Nanda presents his list of the top new Oracle Database 10g Release 2 features for database administrators

Part 5: Backup and Availability Features

Oracle Database 10g Release 2 adds capabilities to make backup and recovery more automatic—including new Oracle Secure Backup for databases as well as filesystems.

Features Covered in This Installment:
· Oracle Secure Backup
· Dynamic RMAN Views for Past and Current Jobs
· Dynamic Channel Allocation for Oracle RAC Clusters
· Tempfiles Recovery via RMAN
· Flashback Database/Query Through RESETLOGS
· Flashback Database Restore Points
· Flash Recovery Area View

Oracle's Own Backup

By now, many of you have realized the potential of RMAN and its utility as a database backup tool. You may recall that RMAN can back up data to disk or tape directly. When a tape solution is involved, RMAN uses an API called Media Management Library (MML) to manipulate the tape subsystem.

This MML is specific to the tape management system and the hardware involved. (For instance, if Tivoli Storage Manager is involved, you have to use the specific MML—Tivoli Data Protector—that RMAN needs to manage tapes via Tivoli.) Although RMAN is a feature of the database engine, the MML, which comes from an outside party, is not; indeed, the price can be considerable. Furthermore, if your primary purpose is to back-up an Oracle database, the extra investment in MML seems unjustified.

In Oracle Database 10g Release 2, a new tool called Oracle Secure Backup (OSB), available in the first quarter of 2006, makes this requirement much more affordable by replacing the MML specific to third-party tape management systems. OSB can back up to a tape library directly, so you don't need any other media management layer. And, best of all, OSB is tightly integrated with the database engine and thus can be controlled and administered via Oracle Enterprise Manager.

But what about the other non-database backup, such as backing up Oracle Home, initialization files, Cluster Registry files (in case of RAC), and other crucial files? Shouldn't you need a backup tool for those, you may ask?

The answer is "No." OSB can also perform filesystem backups, just like any standalone tool. Clearly, eliminating the need for MMLs for RMAN backups, combined with this ability to back up filesystems, provides a lower-cost, less-complex alternative for backup and recovery.

Here's how you would use the MML component using Oracle Enterprise Manager. First, choose Maintenance tab in the Oracle Enterprise Manager GUI:

From the above menu, select the hyperlink titled "Configure Backup Settings," which brings up a screen such as the following:

On this screen, note the "Tape Settings" section, which is where you would configure the Oracle Backup tool.

The Oracle Backup Administrative software can run on a separate host where the software is managed with agents running on the database servers. In this case, the Administrative host is installed and running on host and the Oracle Backup tool has been installed in /bin/obt directory.

Many DBAs, of course, still like to use the command line and write scripts. OSB provides a command line tool called obtool. You can invoke the command line version of the tool by typing:

which brings up the OSB prompt ob>. You can type "help" here to see the commands available.
ob > help
Or, you could use the keyword "glossary" after a command name to get more details on the command:
ob> help restore glossary
To backup your Oracle Home, you could use:
ob> backup --level incr --at 2005/03/29.09:00 
--priority 1 --family Pool1 --privileged --dataset OracleHome --expirerequest 7days
The above command needs some explanation. The first parameter (level)indicates the level of the backup. You specified an incremental backup here to backup all files changed since the last incremental. The second one, 2005/03/29.09:00, specifies when the backup should run: 9AM on March 29, 2005.

If you have several backup jobs, in what order should they run? This order is specified by the priority option, which is set to 1 here, meaning "highest priority." You can specify a value up to 100 to specify lower priorities.

You have also specified several media pools for different types of backups. For instance, you may have a media pool for data file backups, one for archived logs, and one for other non-database backups. Here, you specified the pool named Pool1 as the one to be used for this backup.

You have specified via the parameter dataset the files to be backed up. And you have asked, via the parameter expirerequest, to expire this backup in 7 days, when you expect another incremental backup to occur.

My intention here is to offer a very brief introduction to the new tool; a full description would require a book. For more information about OSB, refer to the documentation set when it is available.

Dynamic RMAN Views for Past and Current Jobs

Like numerous other DBAs, I fell in love with RMAN soon after it was introduced in Oracle8. Nevertheless, I never felt that I understood its activities as thoroughly as I should.

In Oracle Database 10g Release 2, the new dynamic views provided for RMAN jobs make it extremely easy to peek into these activities—current as well as past.

The first new view, V$RMAN_BACKUP_JOB_DETAILS, records the history of all backups. In addition to revealing simple details like how long the backup took, the view shows a slew of other details that are important to analyze after the fact. Let's examine some important ones and how they can help you analyze RMAN sessions.

Let's say that you want to know more or less everything about that history: how many RMAN jobs have been issued, the status of each job, what time they started and completed, what types of jobs they were, and so on. You would issue a query as follows:

SQL> col STATUS format a9
SQL> col hrs format 999.99
SQL> select
  3     to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
  4     to_char(END_TIME,'mm/dd/yy hh24:mi')   end_time,
  5     elapsed_seconds/3600                   hrs
  7* order by session_key
The output may resemble the one shown below:
-----------    -------------   --------  -------------- -------------     -------
          1    DATAFILE FULL   COMPLETED 03/25/05 00:48 03/25/05 00:48    .00
          4    DB FULL         COMPLETED 03/27/05 02:09 03/27/05 02:11    .04
          7    DB FULL         FAILED    03/27/05 02:18 03/27/05 02:24    .10
The SESSION KEY column is the key to the other views showing other relevant information. (More on that in a moment.) The columns START_TIME and END_TIME are fairly intuitive. The column ELAPSED_SECONDS shows the elapsed time in seconds, which I have converted to hour format for easy reading. The STATUS column shows the status of the RMAN jobs. When the job is in progress, the status column shows RUNNING.

Another important piece of information recorded is the rate of the backup produced and how fast data was read and written by the process. This information helps you diagnose any slowness in the RMAN jobs.

SQL> col ins format a10
SQL> col outs format a10
  2     OPTIMIZED,
  8  order by session_key;  

----------- --- ----------------- ---------- ---------- ----------
          1 NO         2.23776224      3.33M      1.49M  00:00:06
          4 NO         1.31065794      6.92M      5.28M  00:02:16
          7 NO         1.32363058      3.68M      2.78M  00:06:00
Note how the time is displayed in a human-readable format: in hours:minutes:seconds format. The columns INS and OUTS display the data input or output per seconds, in the easier-to-read format such as M for megabytes. In the above example, you can see that the job marked by session key 4 saw a read rate of 6.92MB/s and 5.2MB/s. You can now examine the output for several RMAN executions and look for a pattern emerging from them. This pattern analysis will help you identify any potential bottlenecks revealed through variations.

The backup information can also be filtered by backup type. The new view V$RMAN_BACKUP_JOB_DETAILS provides the type of backups RMAN performs and how the output can be organized.

SQL> select * from V$RMAN_BACKUP_TYPE;

---------- -------------
         1 BACKUPSET
         2 SPFILE
         3 CONTROLFILE
         4 ARCHIVELOG
         5 DATAFILE INCR
         6 DATAFILE FULL
         7 DB INCR
         8 RECVR AREA
         9 DB FULL

The object type  
weight determines how the records in the view are ordered.

Another very useful view is the RMAN output. Say you have run an RMAN job via a shell script but something failed. Sure, you have an output file that records the RMAN output, but unfortunately, you have lost it. What can you do? Fortunately, the new view V$RMAN_OUTPUT records the output from the RMAN jobs viewing later. This view is useful for scripted RMAN jobs as well as ad-hoc jobs.

SQL> select output
  2  from v$rman_output
  3  where session_key = 4
  4  order by recid;


connected to target database: TEST (DBID=1849323268)

Starting backup at 27-MAR-05
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=201 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u01/app/oracle/oradata/TEST/system01.dbf
input datafile fno=00003 name=/u01/app/oracle/oradata/TEST/sysaux01.dbf
input datafile fno=00002 name=/u01/app/oracle/oradata/TEST/undotbs01.dbf
input datafile fno=00004 name=/u01/app/oracle/oradata/TEST/users01.dbf
input datafile fno=00005 name=/u01/app/oracle/oradata/TEST/accdata01.dbf
channel ORA_DISK_1: starting piece 1 at 27-MAR-05
channel ORA_DISK_1: finished piece 1 at 27-MAR-05
piece handle=/u01/app/oracle/product/10.2.0/db_1/dbs/07ggc7qr_1_1 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:46
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current controlfile in backupset
channel ORA_DISK_1: starting piece 1 at 27-MAR-05
channel ORA_DISK_1: finished piece 1 at 27-MAR-05
piece handle=/u01/app/oracle/product/10.2.0/db_1/dbs/08ggc7u6_1_1 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 27-MAR-05

As you can see, the entire output from the RMAN job is captured here. This is an in-memory view and is cleared when the instance is shut down. If you want the RMAN output to be persistent, the rows can be copied to a permanent table. The column SESSION_KEY shows the records associated with the RMAN jobs shown in the view V$RMAN_BACKUP_JOB_DETAILS. Now you will never lose the output from the RMAN job again.

Via Oracle Enterprise Manager you can utilize the new views to create a new backup report. This report provides an instantaneous overview of backup operations that have been made in your enterprise. You can filter the data by backup type and status.

Dynamic Channel Allocation for Oracle RAC Clusters

In an Oracle RAC environment, of course, there is more than one instance of a database running on more than one host. However, when you invoke RMAN in such an environment you have to connect to only one instance (using TARGET=/), making one node do all the work while the other node remains relatively idle.

Prior to Oracle Database 10g Release 2, one way to make both nodes do the work is to create multiple channels connecting to multiple instances. An example of the associated RMAN command is shown here:

allocate channel = c1 type sbt_tape connect = 'rman/rmanpass@inst1';
allocate channel = c2 type sbt_tape connect = 'rman/rmanpass@inst2';
This command assumes you have two instances, inst1 and inst2. However this option is not exactly desirable because it will not account for failures in one node; when a node fails, the entire RMAN job fails. It also does not create a true load-balancing configuration.

In Oracle Database 10g Release 2, it is no longer necessary to explicitly allocate a channel for each RAC node to perform a backup; you only need to define the degree of parallelism for the operation. RMAN automatically creates multiple parallel streams and connects to least loaded instances based on the cluster resource manager. In addition to load balancing, it provides channel failover capability so that a connection to one instance fails over to a surviving node. This new feature enhances the robustness of the RMAN process.

Tempfiles Recovery via RMAN

When you restore the database from an RMAN backup, the first thing you need to do is recreate the temporary tablespace files. Because temporary tablespaces do not contain permanent objects to recover, RMAN does not back them up—no reason to waste backup resources on non-permanent objects. However, the Oracle Database needs temporary tablespaces for many operations to run efficiently. So, wouldn't it be nice if RMAN backed them up as well?

In Oracle Database 10g Release 2, it does. When you recover a database, the temporary tablespace files are automatically recreated too. Here's an excerpt from the alert log file:

ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
ORA-01110: data file 201: '/u01/app/oracle/oradata/TEST/temp01.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Sun Mar 27 20:29:00 2005
Errors in file /u01/app/oracle/admin/TEST/bdump/test_dbw0_15457.trc:
ORA-01186: file 201 failed verification tests
ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
ORA-01110: data file 201: '/u01/app/oracle/oradata/TEST/temp01.dbf'
Sun Mar 27 20:29:00 2005
File 201 not verified due to error ORA-01157
Sun Mar 27 20:29:00 2005
Dictionary check complete
Sun Mar 27 20:29:00 2005
SMON: enabling tx recovery
Sun Mar 27 20:29:00 2005
Re-creating tempfile /u01/app/oracle/oradata/TEST/temp01.dbf

Flashback Database/Query Through RESETLOGS

Oracle Database 10g introduced Flashback Database, which rolls back an entire database by undoing the changes as they are stored in flashback logs. But consider this scenario:

  1. Database activity is normal. Records are updated.
  2. Database crashes due to a physical corruption in redo log files.
  3. Database is restored from backup using backup controlfile.
  4. Database is opened using RESETLOGS option.
  5. Database activity resumes. Records are updated normally. "Help!", cries a developer! He updated the wrong set of records. He asks to flashback the database.
When the database was opened with RESETLOGS option, the database started with the SCN number 1. Consequently, the new controlfile has no knowledge about the SCN numbers updated in the past. Flashback Database depends on SCN numbers, so will that feature work in this situation?

In Oracle Database 10g Release 2, it will, because the database stores the previous incarnation of the database in the controlfile and uses it extensively. In this case the previous incarnation is queried and used to flashback the database to a different time, even if the SCN numbers were reset in the meantime.

Let's look at an example. First, check the name of the account holder of account number 3.

SQL> select first_name, last_name
  2  from accounts
  3  where acc_no = 3;

FIRST_NAME                     LAST_NAME
------------------------------ -----------
Alan                           Smith
Now update the name:
SQL> update accounts
  2  set first_name = 'John'
  3  where acc_no = 3;
Now destroy the database, restore from a backup, and open the restored database in RESETLOGS option.

Imagine that after some time has passed, someone down the hall emits a very loud and serious "Oops!" and then asks you to flashback the database to an earlier point in time, which happens to be just prior to the RESETLOGS operation.

Simply issue the following command.

SQL> flashback database to before resetlogs;
It flashes the database back to the exact SCN just before the RESETLOGS. After the command is executed, check the table again.
SQL> select first_name, last_name
  2  from accounts
  3  where acc_no = 3;

FIRST_NAME                     LAST_NAME
------------------------------ -----------
Alan                           Smith
As you can see, the RESETLOGS operation did not affect the flashback operation.

This feature makes Flashback Database very powerful and useful. And its behavior holds true for Flashback Queries as well.

Restore Point in Flashback Database

Remember the concept of savepoints in SQL? In a transaction, you can create a savepoint, make some modifications, create another savepoint, and so on. If the changes are not what you expected, all you have to do is roll them back to a specific savepoint.

Now pan over to a new functionality introduced in Oracle Database 10g, Flashback Database, which allows you to rewind the database to a previous point in time. Wouldn't it be nice to have functionality similar to savepoint in this situation—that is, to be able to rewind to a specific named point, not just a point in time?

In Oracle Database 10g Release 2, you can do that using a new functionality called restore points. Here's how it works. Suppose you have a long month-end processing involving several batch programs you have to run sequentially. Here is the sequence of events:

  1. Create a restore point rp1
  2. Run batch job 1
  3. Create a restore point rp2
  4. Run batch job 2
and so on. The batch job 2 fails in the middle of execution, and you need to take the database to a consistent state. You don't have to take it all the way to the beginning of the run. Because the restore point rp2 was created before the execution of the batch job, you can simply flashback the database to that restore point.

You create a restore point with

create restore point before_monthend_200503;
Restore point BEFORE_MONTHEND_200503 is now created based on the current database time and SCN. If you want to ensure that the database can be flashed back to a particular restore point, you can specify a guarantee by creating guaranteed restore points as shown below:
create restore point before_monthend_200503
guarantee flashback database;
You can confirm the existence of this restore point by SELECTing from a dynamic performance view V$RESTORE_POINT:
SQL> select * from v$restore_point;

---------- --------------------- --- ------------

   1429811                     1 YES      8192000
27-MAR-05 PM
Later when you want to flashback the database to that restore point, you could simply issue:
flashback database to restore point before_monthend_200503;
If you examine the alert log, it will show a line similar to:
Media Recovery Applied UNTIL CHANGE 1429814
Restore points—especially guaranteed restore points—are quite useful in many database-related tasks. A good example is QA databases, where you may want to establish a restore point, run some tests, and flashback to the restore point to make the database look as if nothing happened. Then you can perform another round of testing and again restore it to the restore point.

Peek into the Flash Recovery Area

You may have configured Flash Recovery Area to back up different types of files. But how do you know what types of backups are available there?

A new view, V$FLASH_RECOVERY_AREA_USAGE, shows what's available in the flashback area.


        ------------ ------------------ ------------------------- ---------------
        CONTROLFILE                   0                         0               0
        ONLINELOG                     0                         0               0
        ARCHIVELOG                  .02                       .02               1
        BACKUPPIECE               68.98                      1.02              10
        IMAGECOPY                     0                         0               0
        FLASHBACKLOG                .95                         0               3
Using this view you can immediately see what kind of files are available in the Flash Recovery Area. It only shows a percentage however, so how do you determine actual values? Simply query the view $RECOVERY_FILE_DEST.
SQL> select * from V$RECOVERY_FILE_DEST;

----------- ---------- ----------------- ---------------
 2147483648 1502122496          22201856              14
This query shows that the total size of the recovery area is 16384000. Flashback logs occupy 0.95% of the column SPACE_LIMIT as shown in the previous query, so you can calculate the actual size of the space occupied. It also shows you how much space can be reclaimed from the different types of backups in the Flash Recovery Area. For instance, you can reclaim 1.02% of the space occupied by backup pieces, perhaps due to obsolete backups. Using this view you can make intelligent predictions about Flash Recovery Area usage and sizing.

Oracle Enterprise Manager utilizes the new V$RECOVERY_FILE_DEST view by adding a pie chart to the Recovery Settings page that shows the breakdown of files in the Flash Recovery Area:

One of the unique aspects of a DBA's job—especially a production support DBA's job—is the ability to successfully, reliably, and efficiently backup and restore. In Oracle Database 10g Release 2, enhancements in this area make the DBA's job much easier and more reliable.

Back to Series Index