How to flashback database across versions

How to flashback database across versions

This procedure can be used to restore to the previous version of RDBMS if the upgrade fails.

Prior to upgrading, create a guaranteed restore point. Make sure your disk where the flashback recovery area is is not full.

If flashback database is disabled, the db must be in mounted state to create the restore point.

ex. startup mount pfile='/u0d/oracle/oradata/csdhq/initcsdhq10.ora';

create restore point pre_upgrade guarantee flashback database;

shutdown

do your upgrade

To restore (steps from SQL Apply Rolling Upgrade paper)

1. Shutdown the database

2. Startup mount the database under the new ORACLE_HOME

3. Flashback to the guaranteed restore point taken prior to the upgrade

SQL> flashback database to restore point PRE_LOGICAL_UPGRADE;

4. Shutdown the database

5. Startup mount the database under the old ORACLE_HOME

6. Open resetlogs the database

SQL> alter database open resetlogs

Requirements for Using Guaranteed Restore Points

To support the use of guaranteed restore points, the database must satisfy the following requirements:

· The COMPATIBLE initialization parameter must be set to 10.2 or greater.

· The database must be running in ARCHIVELOG mode. The FLASHBACK DATABASE operation used to return your database to a guaranteed restore point requires the use of archived redo logs from around the time of the restore point.

· A flash recovery area must be configured, as described in "Setting Up a Flash Recovery Area for RMAN". Guaranteed restore points use a mechanism similar to flashback logging, and as with flashback logging, Oracle must store the required logs in the flash recovery area.

· If flashback database is not enabled, then the database must be mounted, not open, when creating the first guaranteed restore point (or if all previously created guaranteed restore points have been dropped).

Note:

There are no special requirements for using normal restore points.

Creating Normal and Guaranteed Restore Points

To create normal or guaranteed restore points, use the CREATE RESTORE POINT statement in SQL*Plus, providing a name for the restore point and specifying whether it is to be a guaranteed restore point or a normal one (the default).

The database can be open or mounted when creating restore points. If it is mounted, then it must have been shut down cleanly (unless this is a physical standby database).

This example shows how to create a normal restore point:

SQL> CREATE RESTORE POINT before_upgrade;

Restore point created.

This example shows how to create a guaranteed restore point:

SQL> CREATE RESTORE POINT before_upgrade GUARANTEE FLASHBACK DATABASE;

Restore point created.

Listing Restore Points

To see a list of the currently defined restore points, use the V$RESTORE_POINT control file view, by means of the following query:

SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,

        GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE

        FROM V$RESTORE_POINT;

You can view the name of each restore point, the SCN, wall-clock time and database incarnation number at which the restore points were created, whether each restore point is a guaranteed restore point, and how much space in the flash recovery area is being used to support information needed for Flashback Database operations to that restore point.

You can also use the following query to view only the guaranteed restore points:

SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,

        GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE

        FROM V$RESTORE_POINT

      WHERE GUARANTEE_FLASHBACK_DATABASE='YES';

For normal restore points, STORAGE_SIZE is zero. For guaranteed restore points, STORAGE_SIZE indicates the amount of disk space in the flash recovery area used to retain logs required to guarantee FLASHBACK DATABASE to that restore point.

Full information about the columns of V$RESTORE_POINT can be found in the Oracle Database Reference.

Dropping Restore Points

When you are satisfied that you do not need an existing restore point, or when you want to create a new restore point with the name of an existing restore point, you can drop the restore point, using the DROP RESTORE POINT SQL*Plus statement. For example:

SQL> DROP RESTORE POINT before_app_upgrade;

Restore point dropped.

The same statement is used to drop both normal and guaranteed restore points.

Note:

Normal restore points eventually age out of the control file, even if not explicitly dropped. The rules governing retention of restore points in the control file are:

· The most recent 2048 restore points are always kept in the control file, regardless of their age.

· Any restore point more recent than the value of CONTROL_FILE_RECORD_KEEP_TIME is retained, regardless of how many restore points are defined.

Normal restore points that do not meet either of these conditions may age out of the control file.

Guaranteed restore points never age out of the control file. They remain until they are explicitly dropped.

Monitoring Space Usage For Guaranteed Restore Points

When guaranteed restore points are defined on your database, you should monitor the amount of space used in your flash recovery area for files required to meet the guarantee. Use the query for viewing guaranteed restore points in "Listing Restore Points" and refer to the STORAGE_SIZE column to determine the space required for files related to each guaranteed restore point. To see the total usage of your flash recovery area, use the query provided in "Using V$RECOVERY_FILE_DEST and V$FLASH_RECOVERY_AREA_USAGE".

Restore_point

CREATE RESTORE POINT

Purpose

Use the CREATE RESTORE POINT statement to create a restore point, which is a name associated with a timestamp or an SCN of the database. A restore point can be used to flash back a table or the database to the time specified by the restore point without the need to determine the SCN or timestamp. Restore points are also useful in various RMAN operations, including backups and database duplication. You can use RMAN to create restore points in the process of implementing an archival backup.

See Also:

· Oracle Database Backup and Recovery User's Guide for more information on creating and using restore points and guaranteed restore points, for information on database duplication, and for information on archival backups

· FLASHBACK DATABASE, FLASHBACK TABLE, and DROP RESTORE POINT for information on using and dropping restore points

Prerequisites

To create a normal restore point, you must have either SELECT ANY DICTIONARY or FLASHBACK ANY TABLE privilege. To create a guaranteed restore point, you must have the SYSDBA system privileges.To view or use a restore point, you must have the SELECT ANY DICTIONARY or FLASHBACK ANY TABLE system privilege or the SELECT_CATALOG_ROLE role.

You can create a restore point on a primary or standby database. The database can be open or mounted but not open. If the database is mounted, then it must have been shut down consistently before being mounted unless it is a physical standby database.

You must have created a flash recovery area before creating a guaranteed restore point. You need not enable flashback database before you create the restore point. However, if flashback database is not enabled, then the first guaranteed restore point you create on this database must be created when the database is mounted. The database must be in ARCHIVELOG mode if you are creating a guaranteed restore point.

create_restore_point::=

Specify the name of the restore point. The name is a character value of up to 128 characters.

The database can retain up to 2048 restore points. Restore points are retained in the database for at least the number of days specified for the CONTROL_FILE_RECORD_KEEP_TIME initialization parameter. The default value of that parameter is 7 days. Guaranteed and preserved restore points are retained in the database until explicitly dropped by the user.

If you specify neither PRESERVE nor GUARANTEE FLASHBACK DATABASE, then the resulting restore point enables you to flash the database back to a restore point within the time period determined by the DB_FLASHBACK_RETENTION_TARGET initialization parameter. The database automatically manages such restore points. When the maximum number of restore points is reached, according to the rules described in restore_point above, the database automatically drops the oldest restore point. Under some circumstances the restore points will be retained in the RMAN recovery catalog for use in restoring long-term backups. You can explicitly drop a restore point using the DROP RESTORE POINT statement.

AS OF Clause

Use this clause to create a restore point at a specified datetime or SCN in the past. If you specify TIMESTAMP, then expr must be a valid datetime expression resolving to a time in the past. If you specify SCN, then expr must be a valid SCN in the database in the past. In either case, expr must refer to a datetime or SCN in the current incarnation of the database.

PRESERVE

Specify PRESERVE to indicate that the restore point must be explicitly deleted. Such restore points are useful when created for use with the flashback history feature.

GUARANTEE FLASHBACK DATABASE

A guaranteed restore point enables you to flash the database back deterministically to the restore point regardless of the DB_FLASHBACK_RETENTION_TARGET initialization parameter setting. The guaranteed ability to flash back depends on sufficient space being available in the flash recovery area.

Guaranteed restore points guarantee only that the database will maintain enough flashback logs to flashback the database to the guaranteed restore point. It does not guarantee that the database will have enough undo to flashback any table to the same restore point.

Guaranteed restore points are always preserved. They must be dropped explicitly by the user using the DROP RESTORE POINT statement. They do not age out. Guaranteed restore points can use considerable space in the flash recovery area. Therefore, Oracle recommends that you create guaranteed restore points only after careful consideration.

Examples

Creating and Using a Restore Point: Example The following example creates a normal restore point, updates a table, and then flashes back the altered table to the restore point. The example assumes the user hr has the appropriate system privileges to use each of the statements.

CREATE RESTORE POINT good_data;

SELECT salary FROM employees WHERE employee_id = 108;

    SALARY

----------

     12000

UPDATE employees SET salary = salary*10

   WHERE employee_id = 108;

SELECT salary FROM employees

   WHERE employee_id = 108;

    SALARY

----------

    120000

COMMIT;

FLASHBACK TABLE employees TO RESTORE POINT good_data;

SELECT salary FROM employees

   WHERE employee_id = 108;

    SALARY

----------

     12000

Keywords and Parameters

Syntax Element	Description
DEVICE TYPE deviceSpecifier	Allocates automatic channels for the specified device type only. For example, if you configure automatic disk and tape channels, and issue FLASHBACK...DEVICE TYPE DISK, then RMAN allocates only disk channels. RMAN may need to restore redo logs from backup during the flashback database process. Changes between the last flashback log and the target time must be re-created based on the archived redo log. If no automatic channels are allocated for tape and a needed redo log is on tape, the FLASHBACK operation will fail. See Also: "deviceSpecifier"
DATABASE	Returns the database to the specified point. Query OLDEST_FLASHBACK_SCN and OLDEST_FLASHBACK_TIME in V$FLASHBACK_DATABASE_LOG to display the approximate lowest SCN and time to which you can flash back. View the current database SCN in V$DATABASE.CURRENT_SCN.
TO RESTORE POINT = ' restore_point_name	Returns the database to the SCN associated with the specified restore point. This can be an ordinary restore point or a guaranteed restore point.
TO SCN = integer	Returns the database to the point up to (and including) the specified SCN. By default, the provided SCN resolves to the current or ancestor incarnation. The DBA can override the default by using the RMAN RESET DATABASE INCARNATION command to set the recovery target incarnation.
TO BEFORE SCN = integer	Returns the database to its state just before the specified SCN. Any changes at an SCN lower than that specified are applied, but if there is a change associated with the specified SCN it is not applied. By default, the provided SCN resolves to the current or ancestor incarnation. The DBA can override the default by using the RMAN RESET DATABASE INCARNATIONcommand to set the recovery target incarnation.
TO SEQUENCE = integer THREAD = integer	Specifies a redo log sequence number and thread as an upper limit. RMAN applies changes up to (and including) the last change in the log with the specified sequence and thread number.
TO BEFORE SEQUENCE = integer [THREAD = integer]	Specifies a redo log sequence number and thread as an upper limit. RMAN applies changes up to (but not including) the last change in the log with the specified sequence and thread number.
TO TIME = 'date_string'	Returns the database to its state at the specified time. You can use any SQL DATE expressions to convert the time to the current format, for example, FLASHBACK DATABASE UNTIL TIME 'SYSDATE-7'.
TO BEFORE TIME = 'date_string'	Similar to the TO TIME clause, but returns the database to its state including all changes up to but not including changes at the specified time.
TO BEFORE RESETLOGS	Returns the database to its state including all changes up to the SCN of the lastOPEN RESETLOGS.

Examples

FLASHBACK DATABASE to a Specific SCN: Example The following command uses Flashback Database to return the database to the specified SCN:

RMAN> FLASHBACK DATABASE TO SCN 46963;

FLASHBACK DATABASE to One Hour Ago: Example The following command uses Flashback Database to return the database to 1/24 of a day (one hour) in the past:

RMAN> FLASHBACK DATABASE TO TIMESTAMP (SYSDATE-1/24);

FLASHBACK DATABASE to a Specific Time: Example The following command uses SQL date conversion functions to specify the target time:

RMAN> FLASHBACK DATABASE TO TIMESTAMP

   TO_TIMESTAMP('2002-03-11 16:00:00', 'YYYY-MM-DD HH24:MI:SS');

FLASHBACK DATABASE to a Restore Point: Example The following command uses a restore point to identify the target time for a Flashback Database operation:

RMAN> FLASHBACK DATABASE TO RESTORE POINT "before_batch";

FLASHBACK DATABASE to Before the Last Resetlogs: Example The following command returns the database to immediately before the most recent OPEN RESETLOGS:

RMAN> FLASHBACK DATABASE TO BEFORE RESETLOGS;