Flashback in SQL Server 2008

SQL Server and DB2 still dont have features like Flashback Table or Flashback Query. SQL Server has snapshots mimicking similar behaviour of Flashback bit it isn’t as fine grained as Oracle. IBM DB2 has time travel query which again isn’t on the same level  as Flashback. But all other RDBM’s are playing quick catchup to Oracle.Get your act together Oracle or get left behind.

Advertisements

Guaranteed Restore Points Oracle 11g

Guaranteed Restore Points are a life-saver when it comes to Application changes.It can ensure that you can rewind the database to a time without tradional point in time recovery. Guaranteed restore points are basically alias’es for SCN’s

A normal restore point assigns a restore point name to an SCN or specific point in time.The control file can maintain a record of thousands of normal restore points with no significant effect on database performance. Normal restore points eventually age out of the control file if not manually deleted, so they require no ongoing maintenance.

SQL> CREATE RESTORE POINT before_upgrade;

Creating a normal restore point eliminates manually recording an SCN in advance or determine the correct SCN after the fact by using features such as Flashback Query.

Like a normal restore point, a guaranteed restore point serves as an alias for an SCN in recovery operations. A principal difference is that guaranteed restore points never age out of the control file and must be explicitly dropped. In general, you can use a guaranteed restore point as an alias for an SCN with any command that works with a normal restore point. Except as noted, the information about where and how to use normal restore points applies to guaranteed restore points as well.

A guaranteed restore point ensures that you can use Flashback Database to rewind a database to its state at the restore point SCN, even if the generation of flashback logs is disabled. If flashback logging is enabled, then a guaranteed restore point enforces the retention of flashback logs required for Flashback Database to any SCN after the earliest guaranteed restore point.

This example shows how to create a guaranteed restore point:

SQL> CREATE RESTORE POINT before_upgrade GUARANTEE FLASHBACK DATABASE;

Listing Restore Points
______________________

You can use the LIST command to list either a specific restore point or all restore points known to the RMAN repository. The variations of the command are as follows:

LIST RESTORE POINT restore_point_name;
LIST RESTORE POINT ALL;

RMAN indicates the SCN and time of the restore point, the type of restore point, and the name of the restore point. The following example shows sample output:

RMAN> LIST RESTORE POINT ALL;

using target database control file instead of recovery catalog
SCN              RSP Time  Type       Time      Name
—————- ——— ———- ——— —-
341859           28-JUL-06            28-JUL-06 NORMAL_RS
343690           28-JUL-06 GUARANTEED 28-JUL-06 GUARANTEED_RS

To see a list of all currently defined restore points (normal and guaranteed), 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

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.
Guaranteed restore points never age out of the control file. They remain until they are explicitly dropped.

Flashing back database till Restore Point
_________________________________________

Login to RMAN and bring the database to mount mode.

FLASHBACK DATABASE TO RESTORE POINT ‘before_upgrade’;

FLASHBACK DATABASE TO SCN 202381;

Open the database.

I normally create guaranteed retore points before doing a Dataguard switchover. It helps you assure you can go back to the state before you started (in case anything goes wrong).

Flashback Table to Timestamp 11gR2

Flashback Technology provided by Oracle is one of the most useful in a production environment. It is a life saver. You can even rollback a query to a point in time without requiring traditional database restore. I will demonstrate below how to do flashback a table to a point in time. But first you have to enable flashback.

From 11gR2 onwards you don’t have to shutdown the database to enable Flashback. You can do it online. To enable flashback in 11gR2 do the below steps.

1.  alter system set db_recovery_file_dest=’+FRA’ scope=both sid=’*’;

2. alter system set db_recovery_file_dest_size=’400G’ scope=both sid=’*’;

3.  select flashback_on from v$database;

FLASHBACK_ON
——————
NO

4. alter database flashback on;

select flashback_on from v$database;

FLASHBACK_ON
——————
YES

_______________________________________________________

— For a table to be flash-backed you need to have row movement enabled on it. We have a table here called Employees in Schema HR

ALTER TABLE HR.EMPLOYEES ENABLE ROW MOVEMENT;

 

— Check current SCN and Database Time

SELECT current_scn, TO_CHAR(SYSTIMESTAMP, ‘YYYY-MM-DD HH24:MI:SS’) FROM v$database;

4784136875    2012-04-30 8:30:00

 

— Check the current total no. of records in the table

SELECT COUNT(*) FROM HR.EMPLOYEES

Count(*)

126

 

— Check the current total no. of records in the table as it existed half an hour back at 8:00 AM

SELECT COUNT(*) FROM HR.EMPLOYEES AS OF TIMESTAMP TO_TIMESTAMP(‘2012-04-30 8:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)

Count(*)

127

This means there is one record was deleted. We need to flashback the table as it existsed at 8:00AM

 

FLASHBACK TABLE HR.EMPLOYEES TO TIMESTAMP TO_TIMESTAMP(‘2012-04-30 8:00:00’, ‘YYYY-MM-DD HH24:MI:SS’);

 

SELECT COUNT(*) FROM HR.EMPLOYEES

Count(*)

127

 

Now we can see that the record are displaying as 127. Which means table is back to the state before the record was deleted.

ORA-38701: Flashback database log seq thread

The database does not startup, and gives errors like:
ORA-38737: Expected sequence number 2483 doesn’t match 2304
ORA-38701: Flashback database log 143 seq 2483 thread 1:

These errors can be seen in the Alert.log file. These errors occur and prevent the database from starting up because, at some point prior, we were not able to write to the flashback area.

Review the alert.log file prior to these errors on startup. You may see other information indicating an issue writing to the flashback area. For example, you may see something like:

ORA-38701: Flashback database log 36 seq 2305 thread 1…..
ORA-27041: unable to open file
OSD-04002: unable to open file

To get the database open,  try:

1. Mount the database:
SQL> startup mount;

2. Turn off flashback.
SQL> alter database flashback off;

3. Open the database:
SQL> alter database open;

4. Shutdown
SQL> Shutdown immediate;

5. Mount the database:
SQL> Startup mount;

6. Turn flashback back on.
SQL> alter database flashback on;

7. Open the database:
SQL> alter database open;

Note : 1 If your are on Oracle  10.2.0.4  your instance will not hang but the alert log will display information like below.

************************************************************
Thu May 20 08:32:43 2010
Errors in file /u1/oracle/OraSWX/admin/SWX/bdump/swx_rvwr_17519.trc:
ORA-38701: Flashback database log 1 seq 1 thread 1: “/u5/flashback/SWX/flashback/o1_mf_5z66yxwt_.flb”
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3
*************************************
RVWR encountered an error when writing flashback database logs.
See error stack in alert log.  To avoid crashing the instance,
this instance has turned off flashback database.
*************************************

The database will disable flashback automatically to prevent the instance from hanging.

Enable Flashback Database Oracle 10g

SQL> select * from v$flashback_database_log;

SQL> show parameter db_file

SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE=2g;

System altered.

SQL>  alter system set db_recovery_file_dest=’/rman/ora_flashback’;

System altered.

shutdown immediate;

startup mount;

SQL> alter system set db_flashback_retention_target=4320;
#for retaining for 3 days

System altered.

SQL> alter database flashback on;

Database altered.

SQL> alter database open;

Database altered.