Flashback

Oracle Flashback

Oracle Flashback allows you to query the database as of a specific time or System Change Number (SCN) in the past. It can be useful in recovering from a recent logical or user error – just use Flashback to query the data prior to an accidental data deletion, for example.

To use Flashback you must have already:

• Set the initialization parameter UNDO_MANAGEMENT = AUTO

(This enables a new 9i feature called Automatic Undo Management, for Oracle-controlled management of rollback segments.)

• Set the initialization parameter UNDO_RETENTION to how far back (in seconds) Oracle should retain undo information:

Example: ALTER SYSTEM SET UNDO_RETENTION = 1600 ;

• Have the execute privilege on system package DBMS_FLASHBACK

Here’s how to use Flashback:

• Run either DBMS_FLASHBACK.ENABLE_AT_TIME or DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER to enable Flashback for the session and indicate the time or SCN to return to
• Perform the query and access missing information by a PL/SQL cursor. You can not use a regular DML query for this purpose!
• Disable Flashback when done by running DBMS_FLASHBACK.DISABLE

A primary limitation in using Flashback is how much extra Undo information you want to store, and how much space you are willing to allocate to this purpose.

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;