Tuesday, August 24, 2010

Using Flashback Database to perform PITR

Flashback database is great alternative for incomplete recovery because it is much less time consuming in some cases. You can find in documentation nice explanations how flashback database works so I won’t concentrate much on theory.

In this blog post I will perform simple demo case of point in time recovery using flashback database feature.

Database must be in ARCHIVELOG mode and FLASHBACK should be enabled.

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - Prod
PL/SQL Release 10.1.0.4.0 - Production
CORE    10.1.0.4.0      Production
TNS for Linux: Version 10.1.0.4.0 - Production
NLSRTL Version 10.1.0.4.0 - Production





SQL> select log_mode,flashback_on from v$database;

LOG_MODE     FLASHBACK_ON
------------ ------------------
ARCHIVELOG   YES


For testing purposes I will use SCOTT schema where I will create three tables in different time periods with my “sensitive data”.

Created first table at 21:35.
SQL> conn scott/tiger
Connected.

SQL> select to_char(sysdate,'dd.mm.yyyy hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'DD
--------
23.08.2010 21:35:19

SQL> create table t1 (a number);

Table created.

SQL> insert into t1 values (1);

1 row created.

SQL> commit;

Commit complete.

Second table at 21:45.
SQL> select to_char(sysdate,'dd.mm.yyyy hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'DD
--------
23.08.2010 21:45:05

SQL> create table t2 (b number);

Table created.

SQL> insert into t2 values (2);

1 row created.

SQL> commit;

Commit complete.

Third table at 22:05.
SQL> select to_char(sysdate,'dd.mm.yyyy hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'DD
--------
23.08.2010 22:05:48

SQL> create table t3 (c number);

Table created.

SQL> insert into t3 values (3);

1 row created.

SQL> commit;

Commit complete.


Lets assume that SCOTT is important production schema and DBA drops that schema by mistake. It is very important to recover lost schema as quickly as possible. DBA isn’t sure about correct time when he/she dropped schema.

Drop SCOTT schema:
$ sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Aug 23 22:13:13 2010

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


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> drop user scott cascade;

User dropped.


RMAN uses flashback logs to undo changes to point before target time and then applies redo logs that are needed to make database consistent.

I will shutdown database and start in mount mode. As you can see I will use “shutdown abort” opinion - simply to minimize downtime.
SQL> shutdown abort;
ORACLE instance shut down.


SQL> startup mount;
ORACLE instance started.

Total System Global Area  297795584 bytes
Fixed Size                  1219064 bytes
Variable Size              83887624 bytes
Database Buffers          209715200 bytes
Redo Buffers                2973696 bytes
Database mounted.
SQL>

Flashback database to 21:40 because DBA said that he/she dropped schema somewhere around that time.
Before opening database with resetlogs opinion I want to check for my sensitive data. So I will temporary open flashbacked database in read only mode and do some checks.
SQL> flashback database to timestamp
 2  to_timestamp('23.08.2010 21:40:00','dd.mm.yyyy hh24:mi:ss');

Flashback complete.


SQL> alter database open read only;

Database altered.

My SCOTT schema is here but some data is missing.
SQL> select * from scott.t1;

        A
----------
        1

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


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

I will recover database to 21:50 and do some checks in read only mode:
SQL> shutdown abort;
ORACLE instance shut down.
SQL>
SQL>
SQL> startup mount;
ORACLE instance started.

Total System Global Area  297795584 bytes
Fixed Size                  1219064 bytes
Variable Size              83887624 bytes
Database Buffers          209715200 bytes
Redo Buffers                2973696 bytes
Database mounted.
SQL>


SQL> recover database until time '2010-08-23:21:50:00';

ORA-00279: change 609683 generated at 08/23/2010 21:40:13 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_
recovery_area/ORA10G/archivelog/2010_08_23/o1_mf_1_10_%u_.
arc
ORA-00280: change 609683 for thread 1 is in sequence #10


Specify log: {=suggested | filename | AUTO | CANCEL}

Log applied.
Media recovery complete.

This is better situation but still some data is missing.
SQL> alter database open read only;

Database altered.

SQL> select * from scott.t1;

        A
----------
        1

SQL> select * from scott.t2;

        B
----------
        2

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

This time I will recover to 22:07.
SQL> shutdown abort;
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.

Total System Global Area  297795584 bytes
Fixed Size                  1219064 bytes
Variable Size              83887624 bytes
Database Buffers          209715200 bytes
Redo Buffers                2973696 bytes
Database mounted.
SQL>
SQL>
SQL> recover database until time '2010-08-23:22:07:00';
Media recovery complete.

Now everything is here.
SQL> alter database open read only;

Database altered.

SQL> select * from scott.t1;

        A
----------
        1

SQL> select * from scott.t2;

        B
----------
        2

SQL> select * from scott.t3;

        C
----------
        3


As I recovered all my sensitive data it is safe to open database with RESETLOGS opinion.

SQL> shutdown abort;
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.

Total System Global Area  608174080 bytes
Fixed Size                  1268896 bytes
Variable Size             209716064 bytes
Database Buffers          390070272 bytes
Redo Buffers                7118848 bytes
Database mounted.

SQL> alter database open resetlogs;
Database altered.

Point in time recovery with flashback database lasted only several minutes with minimal loss of data.




REFERENCE
http://www.comp.dit.ie/btierney/oracle11gdoc/backup.111/b28273/rcmsynta023.htm

6 comments:

  1. Hi!

    db_flashback_retention_target and
    db_recovery_file_dest_size params are crucial to have proper flashback strategy according db size and change. This is very inconvenient in a case of OLTP database and smaller storage.

    And then, when all is setup properly, you need take care for flashback backup.... special kind of backup which must be a part of database backup strategy. I have seen a db with flashback as part of db backup that was unrecoverable because of lack of flashback part of backup.

    All other nice and this is where Oracle is going for sure...

    Damir Vadas
    http://damir-vadas.blogspot.com

    ReplyDelete
  2. Question:
    Is db backup is done on the same disk (not tape) as flashback (let us suppose ASM), what is the benefit to have flashback recover instead of classic RMAN one?
    Lother

    ReplyDelete
  3. Damir,

    enabling flashback logging brings slight overhead on disk usage and storage but this is the price you have to pay if you want to have extended undo functionality. You are correct, it is not always convenient to enable this feature.

    Btw, I think that flashback logs are not essential for recovery and I've never noticed that database was unrecoverable because of missing flashback logs. Full backup and archived redo logs should be enough.

    I will check online - maybe someone reproduced error you are talking about...

    Best regards,
    Marko

    ReplyDelete
  4. Lother,

    flashback database is faster and easier in some cases because it doesn't require restoring the whole database.

    I've added this line at the beginning my blog post because there was written that flashback database is always much less time consuming which is not true.

    Thanks for noticing.

    Regards,
    Marko

    ReplyDelete