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