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
Copyright (c) 1982, 2007, Oracle. All rights reserved.
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
Copyright (c) 1982, 2007, Oracle. All rights reserved.
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 ;
ID
----------
1
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
Copyright (c) 1982, 2007, Oracle. All rights reserved.
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
Copyright (c) 1982, 2007, Oracle. All rights reserved.
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
Copyright (c) 1982, 2007, Oracle. All rights reserved.
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
Copyright (c) 1982, 2007, Oracle. All rights reserved.
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
Copyright (c) 1982, 2007, Oracle. All rights reserved.
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
Copyright (c) 2003, 2007, Oracle. All rights reserved.
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
Copyright (c) 1982, 2007, Oracle. All rights reserved.
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
Copyright (c) 1982, 2007, Oracle. All rights reserved.
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
No comments:
Post a Comment