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
No comments:
Post a Comment