Meeting #1: March 10, 2008

 

This will be a hands-on meeting during which we'll cooperatively explore Oracle Flashback technology.  An Oracle 11g database will be available to spindle, fold, mutilate, and tear.

 

To connect to the Amazing Travelling DAY-O Server, put this in your tnsnames.ora:

 

DAYO =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.1.182)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = dayo.wpafb.af.mil)

    )

  )

 

To do on the whole database:

 

alter database archivelog on;

alter database flashback on;
To throw a random silly change into your JOBS table:
update jobs j set job_title = ( select job_title from random_job ) where job_id = ( select job_id from random_job );
Tasks
Query the values of JOBS a few minutes ago.
SELECT * FROM jobs AS OF TIMESTAMP SYSDATE - 0.01;
Find out what SCN the database is at now: 
SELECT current_scn FROM v$database;
Query the values of JOBS a few transactions ago, using SCNs.
Flashback Table To SCN xxxxxx
Flashback Table (tablename) To Before Drop
Investigate user_recyclebin; purge recyclebin;
Set a restore point and roll the database back to it:  Restore to the Point by Arup Nanda
 
View history of a table with Flashback Version Query
 
Set up long-term flashback archiving with Flashback Data Archive
 
Understand ORA_ROWSCN, SCN_TO_TIMESTAMP
 
Other relevant articles:
If I can get the OEM server going:
 

Oracle By Example: Backing Out Transactions with Flashback

 

 

Oracle Enterprise Manager is being served at https://l172.16.33.1:5500/em

 

 

Version of flatxn_setup1 for your own schema:

flatxn_setup1.sql

 

REM "******************************************* "

REM "For demo purposes ONLY:"

REM "  * Setup for Flashback Transaction"

REM "Execute script as SYSDBA" 

 

set echo on

set serveroutput on

set term on

set lines 200

set pause on

 

/*== Set up the &&username database account for this OBE ==*/

 

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

GRANT EXECUTE ON dbms_flashback TO &&username;

GRANT select any transaction TO &&username;

pause Press [Enter] to continue...

 

/*== Create test data for flashback transaction ==*/

connect &&username/&&username

 

/*== Test transaction 1 ==*/

INSERT INTO &&username.regions VALUES (10,'Pole');

INSERT INTO &&username.regions VALUES (20,'Moon');

INSERT INTO &&username.regions VALUES (30,'Venus');

INSERT INTO &&username.regions VALUES (40,'Mars');

INSERT INTO &&username.regions VALUES (50,'Saturn');

COMMIT;

pause Press [Enter] to continue...

 

/*== Test transaction 2 ==*/

/*== Region 10 and 20 has a WAW dependency on transaction 1 ==*/

UPDATE &&username.regions SET region_name='Two Poles'  WHERE region_id = 10;

UPDATE &&username.regions SET region_name='Many Moons' WHERE region_id = 20;

COMMIT;

pause Press [Enter] to continue...

 

/*== Test transaction 3 ==*/

/*== Region 10 has a WAW dependency on transaction 1 and 2 ==*/

/*== Region 40 and 50 has a WAW dependency on transaction 1 ==*/

UPDATE &&username.regions SET region_name='No star'  WHERE region_id = 10;

UPDATE &&username.regions SET region_name='Red star' WHERE region_id = 40;

UPDATE &&username.regions SET region_name='Big star' WHERE region_id = 50;

COMMIT;

pause Press [Enter] to continue...

 

/*== Test transaction 4 ==*/

/*== Region 30 has a WAW dependency on transaction 1 ==*/

UPDATE &&username.regions SET region_name='Still called Venus' WHERE region_id = 30;

COMMIT;

 

pause Press [Enter] to continue...

 

connect / as sysdba

ALTER SYSTEM ARCHIVE LOG CURRENT;

 

prompt "Setup for Flashback Transaction completed"

pause Press [Enter] to continue...

 

exit


Page Information

  • 3 months ago [history]
  • View page source
  • You're not logged in
  • No tags yet learn more

Wiki Information

Recent PBwiki Blog Posts