Part 5: Backup and Availability Features
Oracle Database 10g Release 2 adds capabilities to make backup and recovery more automaticincluding new Oracle Secure Backup for databases as well as filesystems.
Features Covered in This Installment:
obtoolwhich brings up the OSB prompt ob>. You can type "help" here to see the commands available.
ob > helpOr, you could use the keyword "glossary" after a command name to get more details on the command:
ob> help restore glossaryTo 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 7daysThe 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 activitiescurrent 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 2 SESSION_KEY, INPUT_TYPE, STATUS, 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 6 from V$RMAN_BACKUP_JOB_DETAILS 7* order by session_keyThe output may resemble the one shown below:
SESSION_KEY INPUT_TYPE STATUS START_TIME END_TIME HRS ----------- ------------- -------- -------------- ------------- ------- 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 .10The 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 SQL> select SESSION_KEY, 2 OPTIMIZED, 3 COMPRESSION_RATIO, 4 INPUT_BYTES_PER_SEC_DISPLAY ins, 5 OUTPUT_BYTES_PER_SEC_DISPLAY outs, 6 TIME_TAKEN_DISPLAY 7 from V$RMAN_BACKUP_JOB_DETAILS 8 order by session_key; SESSION_KEY OPT COMPRESSION_RATIO INS OUTS TIME_TAKEN ----------- --- ----------------- ---------- ---------- ---------- 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:00Note 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; WEIGHT INPUT_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; OUTPUT ---------------------------------------------------------------------- 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-05As 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 upno 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:
SQL> select first_name, last_name 2 from accounts 3 where acc_no = 3; FIRST_NAME LAST_NAME ------------------------------ ----------- Alan SmithNow 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 SmithAs 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 situationthat 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:
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; SCN DATABASE_INCARNATION# GUA STORAGE_SIZE ---------- --------------------- --- ------------ TIME --------------------------------------------------- NAME --------------------------------------------------- 1429811 1 YES 8192000 27-MAR-05 05.18.39.000000000 PM BEFORE_MONTHEND_200503Later 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 1429814Restore pointsespecially guaranteed restore pointsare 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.
SQL> select * from V$FLASH_RECOVERY_AREA_USAGE; FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES ------------ ------------------ ------------------------- --------------- 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 3Using 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; NAME ---------------------------------------------------------- SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES ----------- ---------- ----------------- --------------- /home/oracle 2147483648 1502122496 22201856 14This 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 jobespecially a production support DBA's jobis 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