Oracle 9i Database Availability Enhancements

This is one of a series of articles on the new features of Oracle 9i and their impact on business and technical issues .   This article looks at changes made to increase database up time and reduce the time required for recovery after a failure. As Oracle is a high-end database, many changes in 9i have been made to meet the needs of large enterprises, particularly those requiring 24x7 operation of their databases with zero data loss or who have very large databases.  By building on improvements introduced with Oracle 7.3.4, Oracle 8.0 and Oracle 8i,  availability and recovery aspects are improved even further. These new features include :- • standby database enhancements including planned switchover from  to primary and back again (without having to rebuild the database) • enhancements to the Log Miner utility including the use of the on-line data dictionary • better management features in RMAN • faster and more precise database recovery (after corruption/instance failure) • ability to query the database as it was at a specific time or SCN with flashback queries

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 use 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) added • to provide support for DML on index clusters • ability to display only committed statements (redo logs may contain uncommitted changes) • detection of stale data dictionary • ability to skip corrupt blocks in redo logs

New Oracle Instance Failure Recovery Features

Reduced recovery time after instance failure

After a 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.

fast start time-based recovery

This new feature along with an associated parameter called FAST_START_MTTR_TARGET enables 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 checkpoints and the bigger the impact on performance so there is a trade off between faster recovery and degraded performance.

Standby Database Enhancements

The standby database features have been radically changed for Oracle 9i and have been given a new name - 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 are now available: 1. guaranteed zero data loss 2. instant protection 3. rapid protection 4. 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, re-synced with the temporary primary database and then become the permanent primary database again without having to be rebuilt from scratch)

Flashback Queries

This new feature in Oracle 9i 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 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 : • enable_at_time • enable_at_system_change_number • disable • 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.

RMAN Enhancements in Oracle 9i

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. See the Oracle9i Database Administrator's Guide for more details on using these features or see our new features overview. ----------------------------------------------------------- Looking to sky-rocket productivity, slash costs and accelerate innovation? Training is a highly cost-effective, proven method of boosting productivity leaving time, money and staff available for more innovation. Smartsoft offers instructor-led training in Oracle and related technologies on or off site in cities across the UK as well as self-study online training.  See our training course schedule, or let us know your requirements. Oracle tips and tricks Subscribe to our newsletter, jam-packed full of tips and tricks to help you slash costs, sky-rocket productivity and make your systems better, faster and smarter. Smartsoft Computing Ltd, Bristol, England Tel: 0845 003 1320 Contact Us View our privacy policy This site uses woopra.com to gather statistical information about our visitors. This data is aggregated to show industry trends (such as browser share). However, this data shall be the average of many thousands of visits and is in no way linked to individuals. View woopra privacy policy.  Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners. © Copyright Smartsoft Computing Ltd 2014. All rights reserved.