Showing posts with label flashback database. Show all posts
Showing posts with label flashback database. Show all posts

Monday, June 9, 2014

Flashback database to before resetlogs

1.After opening the database with resetlogs, If there is need to revert back the database before resetlogs.Bounce the database in mount mode. 
[oracle@ol6-112-rac1 trace]$ rman target="/"

Recovery Manager: Release 11.2.0.3.0 - Production on Mon Jun 9 13:50:36 2014

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

connected to target database: CVRMAN (DBID=659771233)

RMAN> list incarnation of database;

using target database control file instead of recovery catalog

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       CVRMAN   659771233        PARENT  1          17-SEP-11
2       2       CVRMAN   659771233        PARENT  995548     06-JUN-14
3       3       CVRMAN   659771233        PARENT  1158202    08-JUN-14
4       4       CVRMAN   659771233        ORPHAN  1158656    08-JUN-14
5       5       CVRMAN   659771233        PARENT  1158656    08-JUN-14
6       6       CVRMAN   659771233        PARENT  1161659    09-JUN-14
7       7       CVRMAN   659771233        CURRENT 1164623    09-JUN-14

RMAN> shutdown immediate;

database closed
database dismounted
Oracle instance shut down

RMAN> startup mount ;

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area     275578880 bytes

Fixed Size                     2227584 bytes
Variable Size                180355712 bytes
Database Buffers              88080384 bytes
Redo Buffers                   4915200 bytes

2.Flashback the database to before resetlogs 
RMAN> flashback database to before resetlogs;

Starting flashback at 09-JUN-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=33 device type=DISK


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

Finished flashback at 09-JUN-14

3.Open the database with resetlogs 
RMAN>
RMAN> sql "alter database open resetlogs";

sql statement: alter database open resetlogs

RMAN> list incarnation of database;


List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       CVRMAN   659771233        PARENT  1          17-SEP-11
2       2       CVRMAN   659771233        PARENT  995548     06-JUN-14
3       3       CVRMAN   659771233        PARENT  1158202    08-JUN-14
4       4       CVRMAN   659771233        ORPHAN  1158656    08-JUN-14
5       5       CVRMAN   659771233        PARENT  1158656    08-JUN-14
6       6       CVRMAN   659771233        PARENT  1161659    09-JUN-14
7       7       CVRMAN   659771233        ORPHAN  1164623    09-JUN-14
8       8       CVRMAN   659771233        CURRENT 1164623    09-JUN-14

Posted by at No comments:
Labels: ,

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


Posted by at No comments:
Labels: ,

Sunday, June 8, 2014

flashback database using scn


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 demo table and insert records to the demo table 

SQL> create table flashback_demo( a number);

Table created.

SQL> insert into flashback_demo values (10);

1 row created.

SQL> /

1 row created.

SQL>
SQL> /

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> commit;

Commit complete.
SQL> select * from flashback_demo;

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

5.Record the current scn from the v$database table 
SQL> select current_Scn from v$database;

CURRENT_SCN
-----------
    1161657
4. Drop those records using the truncate table command 
 SQL> truncate table flashback_demo;

Table truncated.

SQL> select * from flashback_demo;

no rows selected

5. Shutdown and mount the database to flashback the database to scn 1161657. 
[oracle@ol6-112-rac1 trace]$ export ORACLE_SID=cvrman
[oracle@ol6-112-rac1 trace]$ rman target="/"

Recovery Manager: Release 11.2.0.3.0 - Production on Mon Jun 9 11:09:15 2014

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

connected to target database: CVRMAN (DBID=659771233)

RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name CVRMAN

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    720      SYSTEM               ***     +DATA/cvrman/datafile/system.304.8495
2    540      SYSAUX               ***     +DATA/cvrman/datafile/sysaux.286.8495
3    70       UNDOTBS1             ***     +DATA/cvrman/datafile/undotbs1.270.84
4    5        USERS                ***     +DATA/cvrman/datafile/users.391.84955
5    345      EXAMPLE              ***     +DATA/cvrman/datafile/example.314.849

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767       +DATA/cvrman/tempfile/temp.318.84

RMAN> shutdown immediate

database closed
database dismounted
Oracle instance shut down

RMAN> startup mount

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area     275578880 bytes

Fixed Size                     2227584 bytes
Variable Size                180355712 bytes
Database Buffers              88080384 bytes
Redo Buffers                   4915200 bytes

RMAN>

RMAN>

RMAN>

RMAN> flashback database to scn 1161657;

Starting flashback at 09-JUN-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=35 device type=DISK


starting media recovery

archived log for thread 1 with sequence 1 is already on disk as file +DATA/cvrman/archivelog/2014_06_09/thread_1_seq_1.461.849783045
archived log for thread 1 with sequence 2 is already on disk as file +DATA/cvrman/archivelog/2014_06_09/thread_1_seq_2.462.849783049
archived log for thread 1 with sequence 3 is already on disk as file +DATA/cvrman/archivelog/2014_06_09/thread_1_seq_3.463.849783065
media recovery complete, elapsed time: 00:00:07
Finished flashback at 09-JUN-14

6.Check the table flashback_demo has records that were previously truncated by opening the database in read only mode 
[oracle@ol6-112-rac1 trace]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jun 9 11:15:13 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> alter database open read only;

Database altered.

SQL> select * from flashback_demo;

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

SQL> select current_Scn, RESETLOGS_CHANGE# from v$database;

CURRENT_SCN RESETLOGS_CHANGE#
----------- -----------------
    1161657           1158656

7.Able to see the records and now open the database with resetlogs 
[oracle@ol6-112-rac1 trace]$ rman target="/"

Recovery Manager: Release 11.2.0.3.0 - Production on Mon Jun 9 11:57:58 2014

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

connected to target database: CVRMAN (DBID=659771233)

RMAN> shutdown immediate

using target database control file instead of recovery catalog
database closed
database dismounted
Oracle instance shut down

RMAN> startup mount

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area     275578880 bytes

Fixed Size                     2227584 bytes
Variable Size                180355712 bytes
Database Buffers              88080384 bytes
Redo Buffers                   4915200 bytes

RMAN> alter database open resetlogs;

database opened

9.Now able to see the records dropped after flashback database to scn 

SQL> select * from flashback_Demo;

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


Posted by at No comments:
Labels: , ,
Subscribe to: Posts (Atom)