Flashback database to time

1. Check the database is flashback enabled

[oracle@ol6-112-rac1 ~]$ export ORACLE_SID=cvrman
[oracle@ol6-112-rac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jun 9 10:36:37 2014

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

sel
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> select name,flashback_on from v$database;

NAME      FLASHBACK_ON
--------- ------------------
CVRMAN    YES

2.If the flashback is disabled follow the below steps to enable flashback, by shutting down the database and starting in mount mode and enabling the flashback on

[oracle@ol6-112-rac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jun 9 10:40:29 2014

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> define
DEFINE _DATE           = "09-JUN-14" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "cvrman" (CHAR)
DEFINE _USER           = "SYS" (CHAR)
DEFINE _PRIVILEGE      = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1102000300" (CHAR)
DEFINE _EDITOR         = "ed" (CHAR)
DEFINE _O_VERSION      = "Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options" (CHAR)
DEFINE _O_RELEASE      = "1102000300" (CHAR)
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  275578880 bytes
Fixed Size                  2227584 bytes
Variable Size             180355712 bytes
Database Buffers           88080384 bytes
Redo Buffers                4915200 bytes
Database mounted.
SQL>
SQL>
SQL> alter database flashback on;

Database altered.
SQL> alter database open;

Database altered.

SQL>    select name,flashback_on from v$database;

NAME      FLASHBACK_ON
--------- ------------------
CVRMAN    YES


SQL>

3.Create a test table and insert rows into the table

SQL> create table flashback_time(a number);

Table created.

SQL> insert into table flashback_time values(10);
insert into table flashback_time values(10)
            *
ERROR at line 1:
ORA-00903: invalid table name


SQL> insert into flashback_time values (10);

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> commit;

Commit complete.

SQL> select * from flashback_time;

         A
----------
        10
        10
        10
        10

4.Record the system time of the database

SQL> select sysdate from dual;

SYSDATE
--------------------
09-JUN-2014 13:22:46

5.Truncate the test table

SQL> truncate table flashback_time;

Table truncated.
SQL> select * from flashback_time;

no rows selected

6.bounce the database to mount mode

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

Total System Global Area  275578880 bytes
Fixed Size                  2227584 bytes
Variable Size             180355712 bytes
Database Buffers           88080384 bytes
Redo Buffers                4915200 bytes
Database mounted.

7.Flashback the database the time before truncating the table and open the database in resetlogs

[oracle@ol6-112-rac1 trace]$ rman target="/"

Recovery Manager: Release 11.2.0.3.0 - Production on Mon Jun 9 13:35:26 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: CVRMAN (DBID=659771233, not open)

RMAN> flashback database to time "to_date('09-JUN-2014 13:22:46','dd-mon-yyyy hh24:mi:ss')";

Starting flashback at 09-JUN-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=30 device type=DISK


starting media recovery
media recovery complete, elapsed time: 00:00:16

Finished flashback at 09-JUN-14

RMAN> sql "alter database open resetlogs";

sql statement: alter database open resetlogs

8.Check the table contents exist after flashback to time.

[oracle@ol6-112-rac1 trace]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jun 9 13:41:16 2014

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> select * from flashback_time;

         A
----------
        10
        10
        10
        10