Note: Technical Talk being moved to:
www.nazimcricket.com/wiki


Monday, 31 August 2009

Oracle 10g - Flashback (Schema)

Technically there is no schema level flashback feature available. However, provided your recovery period does not include any data dictionary changes (DDL) then you could actually use datapump to flashback export the schema and import it back in.

To achieve this you would use syntax like ...

expdp username/password directory=data_pump_dir dumpfile=data_pump_dir:dumpfile.dmp logfile=data_pump_dir:logfile.log schemas=SCHEMA flashback_time=\"to_timestamp('timestamp', 'yyyy-mm-dd hh24:mi')\"

The import process would use syntax like ...

impdp username/password directory=data_pump_dir dumpfile=DUMPFILE.DMP remap_schema=SCHEMA:SCHEMA

In this instance, I'm remapping the schema to another schema, so I can keep both in play for comparison purposes. You may choose to overwrite it instead.

Thursday, 27 August 2009

Oracle 10g - Flashback (Table)

The ability to flashback transactions to a point in time is quite a handy feature. Here is how it all works ....

First of all, the oracle init.ora parameter db_flashback_retention_target needs to be set. This parameter specifies the upper limit (in minutes) on how far back in time the database may be flashed back. The default value is 1440 minutes (24 hours).

How far back one can flash back a database depends on how much flashback data Oracle has kept in the recovery area.

To execute a FLASHBACK TABLE command, you need the FLASHBACK TABLE or FLASHBACK ANY TABLE privilege. In addition, you must have INSERT, DELETE and ALTER privileges on all the specified tables.

Retrieving a Table with Flashback Query

SQL> SELECT * FROM PAKISTAN_CRICKET;

FIRST_NAME           LAST_NAME           
-------------------- --------------------
Khan                 Imran               
Akram                Wasim               
Younis               Waqar               
Afridi               Shahid              
Miandad              Javed               

5 rows selected.

Drop the table.

SQL> DR0P TABLE PAKISTAN_CRICKET;

Table dropped.

The default action associated with the drop command in 10g is to place the object in a recycle bin.  You can view the contents in the recycle bin using ..

SQL> SELECT * FROM RECYCLEBIN;

You may choose to permanently delete the table by purging it from the recyclebin or you could recover it via the following flashback command .. 

SQL> FLASHBACK TABLE PAKISTAN_CRICKET TO BEFORE DROP;

Flashback successful.

Other options available with the FLASHBACK TABLE command are illustrated below .. 



SQL> SELECT * FROM PAKISTAN_CRICKET;

FIRST_NAME           LAST_NAME           
-------------------- --------------------
Khan                 Imran               
Akram                Wasim               
Younis               Waqar               
Afridi               Shahid              
Miandad              Javed               

5 rows selected.


Oracle 10g - Flashback (Row)

The ability to flashback transactions to a point in time is quite a handy feature. Here is how it all works ....

First of all, the oracle init.ora parameter db_flashback_retention_target needs to be set. This parameter specifies the upper limit (in minutes) on how far back in time the database may be flashed back. The default value is 1440 minutes (24 hours).

How far back one can flash back a database depends on how much flashback data Oracle has kept in the recovery area.

Retrieving a Row with Flashback Query

SQL> SELECT * FROM AUSTRALIA_CRICKET;

FIRST_NAME           LAST_NAME
-------------------- --------------------
Ricky                Ponting
Michael              Clarke
Stuart               Clark
Brad                 Haddin
Brett                Lee

5 rows selected.

Delete a row of data - make a note of the deletion time for the purpose of this exercise.

SQL> DELETE FROM AUSTRALIA_CRICKET WHERE FIRST_NAME='Brett';

1 row deleted.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM AUSTRALIA_CRICKET;

FIRST_NAME           LAST_NAME           
-------------------- --------------------
Ricky                Ponting             
Michael              Clarke              
Stuart               Clark               
Brad                 Haddin              

4 rows selected.

Now to use a flashback query to view the data as it was prior to the delete.

SQL> SELECT * FROM AUSTRALIA_CRICKET AS OF TIMESTAMP TO_TIMESTAMP('2009-08-27 15:26','YYYY-MM-DD HH24:MI');


FIRST_NAME           LAST_NAME           
-------------------- --------------------
Ricky                Ponting             
Michael              Clarke              
Stuart               Clark               
Brad                 Haddin              
Brett                Lee                 

5 rows selected.

You may restore the deleted row as follows

SQL> INSERT INTO AUSTRALIA_CRICKET(SELECT * FROM AUSTRALIA_CRICKET AS OF TIMESTAMP TO_TIMESTAMP('2009-08-27 15:26','YYYY-MM-DD HH24:MI') WHERE FIRST_NAME='Brett');

1 row inserted.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM AUSTRALIA_CRICKET;

FIRST_NAME           LAST_NAME           
-------------------- --------------------
Ricky                Ponting             
Michael              Clarke              
Stuart               Clark               
Brad                 Haddin              
Brett                Lee                 

5 rows selected.