Home > DBA > Using Flashback Database

Using Flashback Database

 

I have played again with Oracle Flashback on Oracle database 11.2, last time I used it on Oracle database 10g. It is good to double security your production database before huge database upgrades on application level. Though we use storage snapshots we can also use and Oracle flashback database before big upgrades. You know, safety first.

As the Oracle documentation says(both in 10.2 and 11.2 Oracle keeps this section) :

“In practice, guaranteed restore points provide a useful alternative to storage snapshots. Storage snapshots are often used to protect a database before risky operations such as large-scale database updates or application patches or upgrades. Rather than creating a snapshot or duplicate database to test the operation, you can create a guaranteed restore point on a primary or physical standby database. You can then perform the risky operation with the certainty that the required flashback logs are retained.”

 

So here is what we can do step by step:
1. As a prerequisite the Oracle database should be in archivelog

[oracle@orclbox ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.2.0 Production

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

SQL> conn / as sysdba
Connected.
SQL> alter system set log_archive_dest_1=’location=/oracle/test_flash’;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1653518336 bytes
Fixed Size                  2227032 bytes
Variable Size            1275069608 bytes
Database Buffers          369098752 bytes
Redo Buffers                7122944 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /oracle/test_flash
Oldest online log sequence     2
Next log sequence to archive   4
Current log sequence           4
SQL>

 

1.1 Configure FRA

SQL> alter system set db_recovery_file_dest_size=4g;

System altered.

SQL> alter system set db_recovery_file_dest=’/oracle/test_flash’;

System altered.

 

Note: If it is a RAC database the FRA should point to the shared storage. Ensure you estimate appropriately db_recovery_file_dest_size and have enough space. STORAGE_SIZE column from V$RESTORE_POINT can help you for that.

 

2. Flashback configuration

SQL> shutdown immediate
SQL> alter system set db_flashback_retention_target=1440; /*1 day retention*/
SQL> alter database flashback on;
SQL> alter database open;

 

3. Flashback time – as in the song Calvin Harris – This is like a flashback 🙂

3.1 Create guaranteed restore point

Before we start the upgrade, create a guaranteed restore point

SQL> create restore point before_upg guarantee flashback database;

Restore point created.

— to view the restore points, can filter them by type – guaranteed or normal
SQL> select NAME, SCN, TIME, GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE
2  from V$RESTORE_POINT
3  where GUARANTEE_FLASHBACK_DATABASE=’YES’;

 

3.2 Monitor flashback  v$flashback_database_log

SQL> select oldest_flashback_scn from v$flashback_database_log;

OLDEST_FLASHBACK_SCN
——————–
2882481

 

4. Flashback database example

SQL> create table flashback_test ( col1 number);

Table created.

SQL> insert into flashback_test values(1);

1 row created.

SQL> insert into flashback_test values(2);

1 row created.

SQL> insert into flashback_test values(3);

1 row created.

SQL> commit;

Commit complete.

SQL> select current_scn, scn_to_timestamp(current_scn) from v$database;

CURRENT_SCN SCN_TO_TIMESTAMP(CURRENT_SCN)
———– ————————————————————-
2955736 2011-06-08 13:05:53.000000000

SQL> truncate table flashback_test;

Table truncated.

SQL> select current_scn, scn_to_timestamp(current_scn) from v$database;

CURRENT_SCN SCN_TO_TIMESTAMP(CURRENT_SCN)
———– —————————————————————-
2955809 2011-06-08 13:07:41.000000000

— table flashback_test is dropped by mistake
SQL> drop table flashback_test;

Table dropped.

SQL> select * from flashback_test;
select * from flashback_test
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> select current_scn, scn_to_timestamp(current_scn) from v$database;

CURRENT_SCN SCN_TO_TIMESTAMP(CURRENT_SCN)
———– —————————————————————–
2955891 2011-06-08 13:09:35.000000000

 

If  you want to revert the database to SCN=2955736 where flashback_test table has 3 rows

SQL> shutdown immediate
SQL> startup mount
SQL> flashback database to scn 2955736;
SQL> alter database open resetlogs;

SQL> select * from flashback_test;

COL1
———-
1
2
3

 

Done, reverse operation is successful. You can use FLASHBACK TABLE in this case also. I will show that later in this post.

Now let’s try and revert the database up to the moment after the truncate has been executed, SCN=2955809

SQL> shutdown immediate
SQL> startup mount
SQL> flashback database to scn 2955809;
SQL> alter database open resetlogs;

SQL> select * from flashback_test;

COL1
———-
1
2
3

 

Note: Rows are still here.

Now let’s try with delete:

SQL> delete from flashback_test;

3 rows deleted.

SQL> commit;

Commit complete.

SQL> select current_scn, scn_to_timestamp(current_scn) from v$database;

CURRENT_SCN SCN_TO_TIMESTAMP(CURRENT_SCN)
———– —————————————————————–
2956216 2011-06-08 13:24:39.000000000

SQL> insert into flashback_test values(4);

1 row created.
SQL> commit;

SQL> select * from flashback_test;

COL1
———-
4

— Now let’s try and reverse up to the moment after the delete
SQL> shutdown immediate
SQL> startup mount
SQL> flashback database to scn 2956216;
SQL> alter database open resetlogs;

SQL> select * from flashback_test;

no rows selected

SQL>

 

Note: Correct!

 

When finished and all went well with the upgrade, drop the guaranteed restore point

SQL> drop restore point before_upg;

Restore point dropped.

Note: Don’t forget to switch flashback database off when finished so that flashback logs don’t fill up your FRA area.
SQL> select flashback_on from v$database;

FLASHBACK_ON
——————
YES

SQL> alter database flashback off;

Database altered.

SQL> select flashback_on from v$database;

FLASHBACK_ON
——————
NO

 

5. You can use flashback table, let’s use user test and create the same table

SQL> create table test.flashback_test ( col1 number);

Table created.

— in order not to get ORA-08189: cannot flashback the table because row movement is not enabled
SQL> alter table test.flashback_test enable row movement;

Table altered.

SQL> insert into test.flashback_test values(1);

1 row created.

SQL> insert into test.flashback_test values(2);

1 row created.

SQL> insert into test.flashback_test values(3);

1 row created.

SQL> commit;

SQL> select current_scn, scn_to_timestamp(current_scn) from v$database;

CURRENT_SCN SCN_TO_TIMESTAMP(CURRENT_SCN)
———– ——————————————————————–
2957732 2011-06-08 13:58:15.000000000

SQL> delete from test.flashback_test;

3 rows deleted.

SQL> commit;

Commit complete.

SQL> select * from test.flashback_test;

no rows selected

SQL> flashback table test.flashback_test to scn 2957732;

Flashback complete.

SQL> select * from test.flashback_test;

COL1
———-
1
2
3

 

You don’t need to enable row movement for flashback drop as the objects are recovered from the recycle bin:

SQL> alter table test.flashback_test disable row movement;

Table altered.

SQL> select table_name,row_movement FROM all_tables where owner=’TEST’ and table_name=’FLASHBACK_TEST’;

TABLE_NAME                     ROW_MOVE
—————————— ——–
FLASHBACK_TEST                 DISABLED

SQL> FLASHBACK TABLE test.flashback_test TO BEFORE DROP;

Flashback complete.

SQL> select * from test.flashback_test;

COL1
———-
1
2
3

 

Enjoy 🙂

 

 

Regards,

Maria

 

Advertisements
Categories: DBA Tags:
  1. markrgi
    November 4, 2011 at 8:00 pm

    Nice article!

    One of our DBAs put together a short primer on 10g Flashback that mirrors what you have here. http://www.commitdba.com/blog2/dbaspeak/oracle-flashback-10g-primer.html

  1. No trackbacks yet.

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

%d bloggers like this: