Oracle Tips and Tricks -
How To Make Your Oracle
Systems Your Most Valuable
Asset

Oracle 9i Backup and Recovery Enhancements

21/11/2002 John Seaman, Editor To subscribe just go to www.asktheoracle.net/oracle-tips-signup.html

What's changed in Oracle 9i to make backup and recovery quicker and easier and what this means to you as a dba and to the business you support.

 

 

 

 

 

 

 

New subscribers can read the previous Oracle 9i New Features articles here:

http://www.smart-soft.co.uk/Oracle/oracle9i-new-features-part1.htm
http://www.smart-soft.co.uk/Oracle/oracle9i-new-features-part2.htm

or, for the articles on general performance tuning , just go here:
http://www.smart-soft.co.uk/Oracle/oracle-tips-and-tricks.htm

Book reviews, suggestions for further reading and links to other resources can be found in the resources section.

Just to give a quick overview of what we'll be discussing - the new features in terms of backup and recovery enhancements include:

• use of on-line data dictionary by the Log Miner utility
• reduction in recovery time after instance failure
• better management features in RMAN
• planned switchover from standby database to primary and back again (without having to rebuild the database)
• ability to query the database as it was at a specific time or SCN

2. Oracle 9i Backup and Recovery Improvements

Oracle have made great strides since the release of Oracle 7.3.4 to improve the availability and recovery of Oracle database to meet the needs of companies who need their database running all day, every day, or who have very large databases and therefore need to reduce recovery time. Oracle 9i builds on the improvements first introduced with Oracle 8.0 and Oracle 8i to improve even further the availability and recovery aspects.

2.1 Log Miner Enhancements

Log Miner is a tool that allows analysis of the redo log files to see what changes have been made, by whom and to which objects and to see exactly how to reverse those changes. This tool was first introduced with Oracle 8i and has now been enhanced as follows:

• to allow the use of the on-line data dictionary or to extract the data dictionary to the redo logs for analysis off-line.
• support for DDL statements (create table ..., create index ... etc)
• support for DML on index clusters
• option to display only committed statements (redo logs may contain uncommitted changes)
• detection of stale data dictionary
• option to skip corrupt blocks in redo logs

2.2 New Instance Failure Recovery Features

Changes have been made to reduce the recovery time after instance failure - on crash or instance recovery, 2 passes are now made through the redo logs: the 1st pass just identifies which blocks need to be recovered and the 2nd pass recovers the data files by applying the redo needed for recovery. As the 1st pass is only reading the redo logs (in a sequential manner), slow random access of the data files is avoided.

Oracle have also introduced a feature called fast start time-based recovery along with an associated parameter called FAST_START_MTTR_TARGET, the purpose of which is to allow the DBA to enter into service level agreements regarding the length of time needed to recover after a crash or instance failure. This parameter can be set to a value between 0 and 3600 seconds and causes the database to checkpoint frequently enough to meet the target. The lower the value, the more frequent the checkpointing and the bigger the impact on performance so you have to find the right balance between being able to recover quickly and maintaining performance.

2.3 Standby Database Enhancements

The standby database features have been radically changed for Oracle 9i and have been given a new name - Oracle 9i Data Guard - to reflect the increase in scope of these features. The major changes are:

• separation of log transport and log application processes (logs may now be transferred by LGWR or ARCH processes)
• 4 protection modes now available: guaranteed zero data loss; instant protection; rapid protection; and delayed protection - the same as Oracle 8i. Obviously the reduced risk of data loss comes at the price of performance, due to the increased load on the server.
• planned switchover from standby to primary and back again can now be performed without having to rebuild the standby database (this means the primary database can be shut down for planned maintenance or upgrades, get back in sync with the temporary primary database and then become the permanent primary database again without having to be rebuilt from scratch)

2.4 Flashback Queries

This endows the dba with the powers to travel back in time and query the database as it was in the past - either at a specified time or specified SCN. This requires automated undo (rollback) management and the undo tablespace must be large enough to hold all of the redo. Use of flashback queries is enabled by use of the DBMS_FLASHBACK package which contains the following routines :

• DBMS_FLASHBACK.enable_at_time
• DBMS_FLASHBACK.enable_at_system_change_number
• DBMS_FLASHBACK.disable
• DBMS_FLASHBACK.get_system_change_number

Once flashback is enabled any query made in that session will return data as it was at the specified time or SCN until flashback is disabled or the session ends.

2.5 RMAN Enhancements

Improvements to RMAN consist of :

• enhanced management features : "run" command no longer required in most cases; new configure command to specify default channels, retention policy, etc; new show command to show retention policy, default settings, default channels etc.
• support for multiple block sizes
• enhanced backup features: backup/restore optimization (don't backup/restore files that have not changed; re-startable backups from point of failure)
• block media recovery - new "blockrecover" command to enable restoration of just blocks that have been corrupted.

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

Looking for more Oracle tips and tricks ?

If you're looking for no frills, no fluff, just solid, reliable technical information, take a short cut now and subscribe to our ezine. Published monthly, it's jam-packed full of tips and tricks to help you make more of your Oracle systems and save you hours of blood, sweat and tears searching for information. Subscribe today - there's no charge - and your first issue will soon be winging its way to your mailbox.

Smartsoft Computing Ltd
Bristol, England
Tel: 0845 0031320

Contact Us

Click here to view our privacy policy .

This site uses hitslink.com to gather statistical information about our visitors. View hitslink privacy policy .

Oracle is a registered trademark of Oracle Corporation and/or its affiliates. UNIX is a registered trademark of The Open Group in the United States and other countries.

© Copyright Smartsoft Computing Ltd 2001-2008. All rights reserved.

Search for: