Backup and Recovery (RMAN)

Backup and Recovery Enhancements

Recovery Manager (RMAN) Enhancements

Block Media Recovery (BMR)

By default, RMAN’s backup/recovery method operates on the datafile level. 9i now allows you to recover individual data blocks, called block media recovery (BMR). BMR is quicker than full file recovery where you have a small number of corrupt blocks that need to be recovered. Plus you do not have to take either the datafile or the database offline to perform BMR. BMR minimizes Mean Time to Recover (MTTR).

Determine what blocks are bad from views V$BACKUP_CORRUPTION and V$COPY_CORRUPTION. Oracle writes information about block corruption in both the Alert Log and the user trace files.

Here’s the command to recover one or more data blocks:

BLOCKRECOVER DATAFILE filename_1 BLOCK b1 [, b2, b3 ...]

[ DATAFILE filename_2 BLOCK b4, b5 ... ]

Restrictions:

• You can only do BMR from RMAN (not from SQL*Plus)
• You can only do BMR off full backups (not incremental backups)
• You can only recover complete blocks

The CONFIGURE Command

The new 9i RMAN CONFIGURE command lets you permanently configure many aspects of RMAN. Configure the window for the retention policy:

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

Configure the redundancy policy:

CONFIGURE RENTENTION POLICY TO REDUNDANCY 5;

Configure the default backup device type:

CONFIGURE DEFAULT DEVICE TYPE TO DISK;

Clear the previous command:

CONFIGURE DEFAULT DEVICE TYPE CLEAR;

You can also use the CONFIGURE command to assign attributes to channels. As well, you can configure things so that control files are automatically backed up without having to specify the INCLUDING CONTROL FILE phrase on the BACKUP command.

You can configure to exclude certain tablespaces from backups:

CONFIGURE EXCLUDE FOR TABLESPACE my_ts ;

Append keyword CLEAR to that command to remove the exclusion.

Turn on backup optimization with:

CONFIGURE BACKUP OPTIMIZATION ON;

This skips files that already have a backup on the backup device with the same header status (they don’t need to be backed up, because the same header means the original files have not been changed).

Other RMAN Enhancements

Archivelog Backup – Include any archivelogs not yet backed up with a datafile backup by this command: BACKUP DATAFILE n PLUS ARCHIVELOG ;

Restartable Backup - Back up files that have not been backed up since a particular date and time with the new clause NOT BACKED UP [SINCE TIME...]. Example:

BACKUP DATABASE NOT BACKED UP SINCE TIME ‘sysdate – 5 ;

Smarter Restoration – Oracle only restores files whose headers do not match those of the target. This saves needlessly restoring certain files that are unchanged.

Other Improvements -

• 9i backs up tablespaces of different blocksizes in one command
• Report obsolete backups not needed in the recovery period specified: REPORT OBSOLETE RECOVERY WINDOW 7 DAYS;
• The RECOVERY WINDOW clause was added here too:

REPORT NEED BACKUP RECOVERY WINDOW 7 DAYS:

• Use the CROSSCHECK command to check if backup sets or file copies exist
• Display RMAN session configuration by the SHOW or SHOW ALL commands
• The LIST command has new parameters BY BACKUP and BY FILE
• You can now execute the BACKUP and RESTORE commands directly from the RMAN prompt, instead of only from within a RUN command

Backup and Recovery

Backup and Recovery Enhancements

Trial Recovery

The new 9i Trial Recovery feature does not write changes to files, just to the data buffers. Errors are written to the Alert Log. Trial Recovery allows you to test out recovery and find any corrupt blocks without failing at a real recovery and leaving files in an inconsistent state. Use the keyword TEST for a trial recovery:

RECOVER DATABASE . . . TEST ;

Also new in 9i is the ability to continue even past n corrupt blocks:

RECOVER DATABASE ALLOW n CORRUPTION;

LogMiner

LogMiner Enhancements

9i improves LogMiner in a variety of ways. The purpose of LogMiner is to allow you to view entries in the redo log (the log that records database changes).

LogMiner now has a GUI called the LogMiner Viewer. Use this new GUI in viewing the contents of the redo log files via the view V$LOGMNR_CONTENTS. LogMiner Viewer has Display Options that allow you to specify what information you want to view from the logs. Two of the key fields are SQL_REDO and SQL_UNDO. The former contains the redo log statement, while the latter has its “inverse” – a statement that would reverse its effects (or “undo” it).

9i now records DDL statements in the redo logs. (You no longer have to reverse-engineer them from the complex DML Oracle runs against its internal tables, as you had to when using LogMiner under 8i). You can track (but not) undo DDL commands. That is, you can still not recover from a table DROP or TRUNCATE using this feature.

Remember that LogMiner requires access to the data dictionary in order to translate object identifiers into their names and data according to proper data types. Without dictionary access, LogMiner returns object identifiers and hex data representations – not too useful. You can now extract the data dictionary to either a flat file or to the redo log files by using the procedure DBMS_LOGMNR_D.BUILD. Specify the OPTIONS parameter in this procedure as either STORE_IN_FLAT_FILE or STORE_IN_REDO_LOGS.

There is also a third OPTION – DICT_FROM_ONLINE_CATALOG. This uses the online data dictionary, which could possibly be inaccurate if the database changed since the logs were generated.

If you extract the dictionary to either a flat file or the redo logs, you can specify the additional option DDL_DICT_TRACKING. This prompts LogMiner to keep its extracted dictionary in sync with any changes to the real data dictionary by applying DDL it encounters in the redo logs. LogMiner detects that its dictionary export is stale (obsolete) through its use of object version numbers.

LogMiner stops whenever it encounters a corruption in the redo log it is viewing. Use the OPTION called SKIP CORRUPTIONS to force it to continue beyond corruptions.

Oracle DataGuard

Data Guard

Oracle’s Standby Database feature has been upgraded and renamed to Data Guard in 9i. Data Guard allows you to set up a standby database (locally or at a remote site) and keep it either completely or approximately “in sync” with the primary database by automatically shipping and applying redo logs to the standby database. This provides for quick disaster and/or off-site recovery should the primary database be lost or destroyed. Data Guard components are:

• Data Guard Manager – The new administrative Data Guard GUI (part of OEM)
• Data Guard Command Line Interface (CLI) – For issuing commands
• Data Guard Monitor (DMON) – A monitor process that supports the Data Guard administration

Update the standby database by one of four Data Guard modes:

• Guaranteed – Keeps primary and standby totally in sync at all times. Primary transactions are not committed until verification that the change has been applied to the standby
• Instant – Ensures changes are shipped to the standby but does not require they immediately be applied and confirmed
• Rapid – Log Writer (LGWR) on the primary sends changes to the standby as soon as it can
• Delayed – the Archiver Process (ARCH) process on the primary send changes to the standby. You can specify a time lag prior to propagation (this can help avoid propagating errors)

Guaranteed mode is the most conservative and keeps the standby totally synchronized with the primary, but impacts primary performance the most. The other modes are progressively “looser” in the coupling between the primary and standby but cause less performance impact on the primary. You configure the mode (and other Data Guard attributes) by the initialization parameters LOG_ARCHIVE_DEST_n and LOG_ARCHIVE_DEST_STATE_n.

Role Management Service (RMS) helps you to perform database transitions (switching the roles of the primary and standby databases). A switchover is a planned transition whereby you purposefully make the primary the standby, and vice versa. Among the commands you use during a switchover are these key ones:

ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;

ALTER DATABASE MOUNT STANDBY DATABASE;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;

ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL PRIMARY;

A switchback is the reverse of the switchover operation. It puts the two databases back to their original roles as primary and standby.

A graceful failover is performed when the primary becomes unavailable but you have its redo logs to apply to the standby. Apply any and all possible logs to the standby:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

then issue:

ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL PRIMARY;

A forced failover occurs when the primary fails and redo logs are not all available. Some redo may be lost as you activate the standby. Key commands here include:

ALTER DATABASE SET STANDBY DATABASE UNPROTECTED;

ALTER DATABASE ACTIVATE STANDBY DATABASE;

An archive log gap occurs whenever a primary database archives the online redo log, but it is not also archived to the standby database. 9i automatically recovers archive log gaps for you. Set this up by initialization parameters that assign service names to the primary and standby databases:

FAL_CLIENT = ‘standby_name’

FAL_SERVER = ‘primary_name’

9i includes a Managed Recovery Process (MRP), a process that automatically applies archived redo logs on the standby database server to the standby database. Put a standby database into MRP mode by running:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;

Resumable Space Allocation

Resumable Space Allocation

Resumable space allocation suspends a long-running operation in event of a space allocation error so that you can fix the problem. Then Oracle automatically resumes the long-running operation. Operations that are resumable are:

• Queries that run out of temporary sort space
• DML – INSERT, UPDATE, and DELETE statements
• DDL – CREATE TABLE AS SELECT, ALTER TABLE, CREATE INDEX, ALTER INDEX, and statements that create materialized views or materialized view logs
• Import/Export – SQL*Loader operations

The space problems resumable space allocation addresses are:

• Out of space
• Maximum extents reached
• Space quota exceeded

To use resumable space allocation, set your session as resumable, and ensure you have the RESUMABLE system privilege:

ALTER SESSION ENABLE RESUMABLE ;

GRANT RESUMABLE TO my_id ;

If an operation is suspended, Oracle writes an error to its Alert log. Views USER_RESUMABLE and DBA_RESUMABLE contain info on the error, or you can run DBMS_RESUMABLE.SPACE_ERROR_INFO for error details. Fix the error, then Oracle will automatically resume the suspended statement.

Oracle also provides the AFTER SUSPEND system event to handle resumable space errors. You could use this, for example, in a trigger with custom code:

CREATE OR REPLACE TRIGGER my_space_handler

AFTER SUSPEND ON DATABASE...