How to Flashback a Logical error

We can able to flashback a database
to the previous point based on SCN,Timestamp and Log sequence number

Scenario 1: (with scn)
SQL> select current_scn from v$database;
 
CURRENT_SCN
———–
    2751136

SQL> select count(*) from boss.itemmast;

  COUNT(*)
———-
    124605

SQL> truncate table boss.itemmast;

Table truncated.

SQL> select current_scn from v$database;

CURRENT_SCN
———–
    2751924

SQL> flashback database to scn  2751136;
flashback database to scn  2751136
*
ERROR at line 1:
ORA-38757: Database must be mounted and not open to FLASHBACK.
SQL> shutdown immediate;
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  536870912 bytes
Fixed Size                  1250044 bytes
Variable Size             339741956 bytes
Database Buffers          192937984 bytes
Redo Buffers                2940928 bytes
Database mounted.

SQL> flashback database to scn  2751136;

Flashback complete.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs;

Database altered.

SQL> select count(*) from boss.itemmast;

  COUNT(*)
———-
    124605
scenario 2: (with timestamp)

SQL> set time on
12:55:48 SQL> select flashback_on from v$database;

FLASHBACK_ON
——————
YES

12:56:06 SQL> select count(*) from boss.itemmast;

  COUNT(*)
———-
    124605

12:56:20 SQL> truncate table boss.itemmast;

Table truncated.

12:56:32 SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
12:58:01 SQL>
12:58:01 SQL> startup mount
ORACLE instance started.

Total System Global Area  536870912 bytes
Fixed Size                  1250044 bytes
Variable Size             343936260 bytes
Database Buffers          188743680 bytes
Redo Buffers                2940928 bytes
Database mounted.
13:00:22 SQL> FLASHBACK DATABASE TO TIMESTAMP TO_TIMESTAMP(‘2009-01-08 12:56:00’, ‘YYYY-MM-DD HH:MI:SS’);
  

Flashback complete.

or

13:00:22 SQL>FLASHBACK DATABASE TO TIMESTAMP (SYSDATE-1/24);————-it will take to 1 hour before

13:02:50 SQL> alter database open resetlogs;

Database altered.

13:04:23 SQL> select count(*) from boss.itemmast;

  COUNT(*)
———-
    124605

 

References:

http://www.coug.ab.ca/Resources/TomKyte_FlashBackQ_Mar-05.zip

http://download.oracle.com/oowsf2005/317.pdf

http://www.oracle.com/technology/deploy/availability/pdf/TWP_HA_FlashbackOverview_10g_111503.pdf

Metalink notes:

Note 249319.1 – Configure flashback database
Note 270060.1 – Use Flashback Table Feature and Resolve errors
Note 270535.1 – Restrictions on Flashback Table Feature
Note:435998.1 – What Do All 10g Flashback Features Rely on and what are their Limitations
Note 317499.1 – 10G Oracle Flashback Transaction Query – Introduction and usage
Note 369755.1 – Flashback Logs-Space management

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s