Flashback database examples

Flashback database examples:-

Flashback database feature allows us to go back in time and may be get back dropped table/data or whatever case may be and come back to present time. We wish that same feature was available for our life as well J

Here are few links which tells you more about Flashback database and how to set this up in your database.

http://www.orafaq.com/node/1847

http://www.oracle.com/technology/oramag/oracle/06-nov/o66recovery.html

The main purpose of this article to show How to use Flashback database feature to go back point in time , open database with resetlogs and then come back to same point where we have started without losing any data.

Case 1:

Real case scenario:-

In this case, some webmaster has dropped an Oracle Portal Pages and there were more pages were created after pages were dropped. Now, question is How to get back to dropped page without compromising new pages which were created.

9:00 AM

9:30 AM Page Dropped

9:35 AM New Pages created and some other activity

10:00 AM Portal Administrator told about dropped pages and they want to get it back.

Action Plan:

Outage is required.
Flashback database to 9:29 AM before Page was dropped.
Open database with resetlogs
Start Portal Application and Using Portal Administrator Navigator GUI, Export Pages.
After Export is created, roll forward to as nothing has happened ( This is what we want to show)
Import Pages using Portal Page Import process.

Test case scenario:

Noting down current SCN, See note below when we know only time:

SCOTT@ORCL> select current_scn from v$database ;

CURRENT_SCN

-----------

3014379

SCOTT@ORCL>

Create Table and Insert some rows :

SCOTT@ORCL> Create Table Test ( Id Number );

Table created.

SCOTT@ORCL> Insert into Test values (1);

1 row created.

SCOTT@ORCL> commit;

Commit complete.

SCOTT@ORCL> select current_scn from v$database ;

CURRENT_SCN

-----------

3014616

SCOTT@ORCL>

Drop Table and Note down SCN

SCOTT@ORCL> drop table Test ;

Table dropped.

SCOTT@ORCL> select current_scn from v$database ;

CURRENT_SCN

-----------

3014727

SCOTT@ORCL>

Create Another Table

SCOTT@ORCL> create table test_afterdrop ( col1 varchar2(50));

Table created.

SCOTT@ORCL> insert into test_afterdrop values ('New Table after drop');

1 row created.

SCOTT@ORCL> commit;

Commit complete.

SCOTT@ORCL> select current_scn from v$database ;

CURRENT_SCN

-----------

3014841

SCOTT@ORCL>

**Note** At this point, now suppose users comes and said we want to get back table Test and we don't want to lose any stuff which was created afterwards.

For simplicity, we have noted down SCN numbers, in real world users wont tell you SCN number but most likely time. Based on time, you can get SCN number by using TIMESTAMP_TO_SCN function.

Actions :

Flashback database to 3014616 ( This was SCN before Table Drop)

List Incarnations of database from RMAN

Open database read only (This is simple case)

Recover Database

C:\Documents and Settings\Sanjay Gupta>rman target /

Recovery Manager: Release 11.1.0.6.0 - Production on Tue Oct 6 10:57:50 2009

connected to target database: ORCL (DBID=1190520204)

RMAN> list incarnation ;

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 ORCL 1190520204 PARENT 1 15-OCT-07

2 2 ORCL 1190520204 PARENT 886308 14-AUG-08

3 3 ORCL 1190520204 ORPHAN 2778049 09-APR-09

4 4 ORCL 1190520204 PARENT 2778049 09-APR-09

5 5 ORCL 1190520204 PARENT 2924497 05-OCT-09

6 6 ORCL 1190520204 PARENT 2924619 05-OCT-09

7 7 ORCL 1190520204 PARENT 2938497 05-OCT-09

8 8 ORCL 1190520204 ORPHAN 2939443 05-OCT-09

9 9 ORCL 1190520204 CURRENT 2939513 05-OCT-09

RMAN>

Lets FlashBack :-

Case 1 ( With no resetlogs):-

SYS@ORCL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SYS@ORCL> startup mount ;

ORACLE instance started.

Total System Global Area 535662592 bytes

Fixed Size 1334380 bytes

Variable Size 335545236 bytes

Database Buffers 192937984 bytes

Redo Buffers 5844992 bytes

Database mounted.

SYS@ORCL> flashback database to scn 3014616 ;

Flashback complete.

SYS@ORCL> alter database open read only;

Database altered.

SYS@ORCL>

C:\Documents and Settings\Sanjay Gupta>sqlplus scott/****

SQL*Plus: Release 11.1.0.6.0 - Production on Tue Oct 6 11:04:06 2009

Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SCOTT@ORCL> select * from test ;

----------

SCOTT@ORCL> select * from test_afterdrop ;

select * from test_afterdrop

ERROR at line 1:

ORA-00942: table or view does not exist

SCOTT@ORCL>

We have got test table back and you can also see that test_afterdrop does not exists. At this point, you can do traditional exp (export) to export test table and after export is saved we will go back to where we have started.

In this example, we did not open database with resetlogs so it is quite easy to go back from where we have started

C:\>exp scott/**** tables=scott.test file=test_exp.dmp

Export: Release 11.1.0.6.0 - Production on Tue Oct 6 11:11:06 2009

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...

. . exporting table TEST 1 rows exported

Export terminated successfully without warnings.

C:\Documents and Settings\Sanjay Gupta>

C:\Documents and Settings\Sanjay Gupta>sqlplus / as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Tue Oct 6 11:13:37 2009

Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@ORCL> shutdown immediate ;

Database closed.

Database dismounted.

ORACLE instance shut down.

SYS@ORCL> startup mount ;

ORACLE instance started.

Total System Global Area 535662592 bytes

Fixed Size 1334380 bytes

Variable Size 335545236 bytes

Database Buffers 192937984 bytes

Redo Buffers 5844992 bytes

Database mounted.

SYS@ORCL> recover database ;

Media recovery complete.

SYS@ORCL>

SYS@ORCL> alter database open ;

Database altered.

SYS@ORCL>

** At this point, we were at the point we are back to where we have started. This database will not have test table but will have test_beforedrop table.

If you want you can import back test table, see we did not lose any changes. Table test_afterdrop exists …

C:\Documents and Settings\Sanjay Gupta>sqlplus scott/****

SQL*Plus: Release 11.1.0.6.0 - Production on Tue Oct 6 11:18:26 2009

Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SCOTT@ORCL> select * from test_afterdrop ;

COL1

--------------------------------------------------

New Table after drop

SCOTT@ORCL> select * from test ;

select * from test

ERROR at line 1:

ORA-00942: table or view does not exist

Lets FlashBack :-

Case 2 ( With Resetlogs):-

Flashback database to 3014616 ( This was SCN before Table Drop)

List Incarnations of database from RMAN

Open database resetlogs

Export table using expdp

Reset database to previous incarnation

Flashback (Roll forward to last SCN )

Open database with resetlogs

C:\Documents and Settings\Sanjay Gupta>sqlplus / as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Tue Oct 6 11:21:57 2009

Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@ORCL> shutdown immediate ;

Database closed.

Database dismounted.

ORACLE instance shut down.

SYS@ORCL> startup mount ;

ORACLE instance started.

Total System Global Area 535662592 bytes

Fixed Size 1334380 bytes

Variable Size 335545236 bytes

Database Buffers 192937984 bytes

Redo Buffers 5844992 bytes

Database mounted.

SYS@ORCL> flashback database to scn 3014616 ;

Flashback complete.

SYS@ORCL> alter database open resetlogs ;

Database altered.

SYS@ORCL>

C:\Documents and Settings\Sanjay Gupta>rman target /

Recovery Manager: Release 11.1.0.6.0 - Production on Tue Oct 6 11:24:56 2009

connected to target database: ORCL (DBID=1190520204)

RMAN> list incarnation ;

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 ORCL 1190520204 PARENT 1 15-OCT-07

2 2 ORCL 1190520204 PARENT 886308 14-AUG-08

3 3 ORCL 1190520204 ORPHAN 2778049 09-APR-09

4 4 ORCL 1190520204 PARENT 2778049 09-APR-09

5 5 ORCL 1190520204 PARENT 2924497 05-OCT-09

6 6 ORCL 1190520204 PARENT 2924619 05-OCT-09

7 7 ORCL 1190520204 PARENT 2938497 05-OCT-09

8 8 ORCL 1190520204 ORPHAN 2939443 05-OCT-09

9 9 ORCL 1190520204 PARENT 2939513 05-OCT-09

10 10 ORCL 1190520204 CURRENT 3014618 06-OCT-09

** See another Incarnation #10 since we have opened database with resetlogs.

** We took expdp example , expdp process writes to database while exp ( traditional export) does not write to database.

C:\Documents and Settings\Sanjay Gupta>expdp scott/*** dumpfile=scott_test_expdp.dmp tables=test directory=data_pump_dir

Export: Release 11.1.0.6.0 - Production on Tuesday, 06 October, 2009 11:31:23

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SCOTT"."SYS_EXPORT_TABLE_01": scott/******** dumpfile=scott_test_expdp.dmp tables=test directory=data_pump_dir

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 64 KB

Processing object type TABLE_EXPORT/TABLE/TABLE

. . exported "SCOTT"."TEST" 5.007 KB 1 rows

Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:

C:\APP\ORACLE\ADMIN\ORCL\DPDUMP\SCOTT_TEST_EXPDP.DMP

Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 11:31:40

C:\Documents and Settings\Sanjay Gupta>

** Now we have got our table back using expdp. Now lets us go back to where we have started.

We have to use rman :

We need to go back to old Incarnation # 9

Lets us first shutdown DB and open in mount state.

SYS@ORCL> shutdown immediate ;

Database closed.

Database dismounted.

ORACLE instance shut down.

SYS@ORCL> startup mount ;

ORACLE instance started.

Total System Global Area 535662592 bytes

Fixed Size 1334380 bytes

Variable Size 343933844 bytes

Database Buffers 184549376 bytes

Redo Buffers 5844992 bytes

Database mounted.

SYS@ORCL>

** Use RMAN to reset to old incarnation # 9

C:\Documents and Settings\Sanjay Gupta>rman target /

Recovery Manager: Release 11.1.0.6.0 - Production on Tue Oct 6 11:36:53 2009

connected to target database: ORCL (DBID=1190520204, not open)

RMAN> reset database to incarnation 9 ;

using target database control file instead of recovery catalog

database reset to incarnation 9

RMAN>

Now we will flashback to SCN #3014841, this was last SCN we have saved.

SYS@ORCL> flashback database to scn 3014841;

Flashback complete.

SYS@ORCL> alter database open resetlogs ;

Database altered.

** Now we are back to point where we have started our recovery of test table. We have got test table as expdp ( dump) .

SYS@ORCL>

C:\Documents and Settings\Sanjay Gupta>sqlplus scott/****

SQL*Plus: Release 11.1.0.6.0 - Production on Tue Oct 6 11:42:09 2009

Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SCOTT@ORCL> select * from test ;

select * from test

ERROR at line 1:

ORA-00942: table or view does not exist

SCOTT@ORCL> select * from test_afterdrop ;

COL1

--------------------------------------------------

New Table after drop

Database & Data Engineering Topics

About Me

Wednesday, October 7, 2009

Flashback database examples

No comments: