Oracle Restore Points

Oracle Tips by Burleson Consulting

This is an excerpt from "Oracle 10g New Features for Administrators" by Ahmed Baraka.

� A restore point serves as an alias for an SCN or a specific point in time.

� It is stored in the control file.

� It can be used with: RECOVER DATABASE, FLASHBACK DATABASE, FLASHBACK TABLE.

� It requires the database to operate in ARCHIVEMODE and the database flashback logging enabled.

� Use V$RESTORE_POINT to obtain information about restore points CREATE RESTORE POINT before_load

Guaranteed Restore Points

� A guaranteed restore point ensures that you can perform a FLASHBACK DATABASE command to that SCN at any time.

� It can be used to restore beyond time specified by DB_FLASHBACK_RETENTION_TARGET parameter

� Even the effects of NOLOGGING operations such as direct load inserts can be reversed using guaranteed restore points.

� It must be manually deleted.

CREATE RESTORE POINT before_load GUARANTEE
FLASHBACK DATABASE

To flashback database to a restore point

SHUTDOWN IMMEDIATE
STARTUP MOUNT
FLASHBACK DATABASE TO RESTORE POINT before_load
ALTER DATABASE OPEN READ ONLY
-- check the flashed back data
SHUTDOWN IMMEDIATE
STARTUP MOUNT
ALTER DATABASE OPEN RESETLOGS;

To recover the database to the restore point

RECOVER DATABASE UNTIL RESTORE POINT
before_load

To flashback table to restore point

ALTER TABLE emp ENABLE ROW MOVEMENT
FLASHBACK TABLE emp TO RESTORE POINT
before_load

To drop a restore point

DROP RESTORE POINT before_load

Placing All Files in Online Backup Mode

� In Oracle Database 10g, with a single command you can place all the data files in the database in online backup mode. You no longer need to place each tablespace in online backup mode individually. This makes user-managed online backup easier. The requirements for implementing the user-managed online backup still apply.

ALTER DATABASE BEGIN BACKUP

� When you issue this command, any nonexistent, offline, or read-only data files are simply skipped and the processing continues.

� However, for the command ALTER DATABASE END BACKUP, if you have a data file with an OFFLINE file status, you receive a warning message. Also, with this command, if you have a tablespace in read-only mode, you do not receive any messages.