Steps to minimize Materialized Views redolog data generation

1. Materialized Views should be created with full refresh options.

2. Tables on which Materialized Views are based should be in nologging mode.

3. Indexes should be disabled on base tables on which M Views are created.

4. Better option to minimize redolog genaration is to use a custom job to refresh the MV, steps to this can be

a) disable indexes
b) call refresh
c) rebuild indexes with no logging




Never stop learning, because life never stops teaching.

Change Database name and DBID using "New Database ID (nid) "

New Database ID (nid) is an Oracle database utility to change  either any one of bother of the DBID and password since Oracle 9.2,

To change DBID database has to be opene via resetlogs options to reset the online redo logs sequence from 1 while only DB name is to be changed there is no need to reset the logs.

nid is a simple and useful utility to change dbid after database clone.

1. To change DBID, use below mentioned command as oracle user

$ nid target=sys/abc123

Login as sys user and bounce the DB and open with resetlogs option

SQL> startup mount;
SQL> alter database open resetlogs;


2. To change DB name, run the command

$ nid TARGET=SYS/abc123 DBNAME=orclsby1 SETNAME=Y

make a new pfile by copying existing one,

cd $ORACLE_HOME/dbs

cp initorcl.ora initorclsby1.ora

change db_name parameter in pfile

*.db_name='orclsby1'

startup database;


SQL> select database_name from v$database;

DATABASE_NAME
--------------------------------------------------------------------------------
ORCLSBY1

Step by Step GoldenGate 12.1.2.1.0 configuration at Oracle Linux 5.9

Step by Step GoldenGate  12.1.2.1.0 configuration at Oracle Linux 5.9

Oracle GoldenGate is a comprehensive software package for real-time data integration and replication in heterogeneous IT environments. The product set enables high availability solutions, real-time data integration, transactional change data capture, data replication, transformations, and verification between operational and analytical enterprise systems. 

This is a detail document about Oracle GoldenGate configuration at Oracle Linux

Source Server IP            -              192.168.56.103

Database name               -              ORCL (11.2.0.3.0)

Golden Gate Version     -              12.1.2.1.0

Source Schema              -              Scott     

Replication Schema       -              ogg_user

Operating System          -              Oracle Linux 5.9

Target Server IP            -              192.168.56.104

Database name              -              ORCL (11.2.0.3.0)

Golden Gate Version    -              12.1.2.1.0

Source Schema             -              Scott     

Replication Schema      -              ogg_user

Operating System         -              Oracle Linux 5.9

Prepare database for GoldenGate prerequisites

1.                   Turn off Recycle bin feature

2.                   Enable Supplemental log

3.                   Enable database archive log mode

4.                   Create GoldenGate admin user

5.                   Set Undoretention

Login at sqlplus as sys user and  perform above mentioned steps

Turn off recyclebin

SQL> alter system set recyclebin=off scope=spfile;

System altered.

Add Supplemental log

alter database add supplemental log data scope=spfile;

System altered.

Enable Database archivelog mode & supplemental log

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area  9999999 bytes

Fixed Size                  999999 bytes

Variable Size             9999999 bytes

Database Buffers          999999999 bytes

Redo Buffers                9999999 bytes

SQL> alter database archivelog;

Database altered.

Enable UNDO RETENTION

SQL> show parameter undo

NAME                                 TYPE        VALUE

———————————— ———– ——————————

undo_management                   string      AUTO

undo_retention                         integer     900

undo_tablespace                       string      UNDOTBS1

SQL> alter system set UNDO_RETENTION=86400 scope=both;

System altered.

Create GoldenGater user & tablespace

create tablespace ogg_data  datafile ‘/u01/<DBNAME>ogg_data01.dbf’ size 300m;

tablespace created

SQL> create user ogg_user identified by oracle default tablespace ogg_user temporary tablespace temp;

User created.

Assign required privileges to ogg_user

SQL> grant connect, resource to ogg_user;

grant select any dictionary, select any table to ogg_user;

grant create table to ogg_user;

grant flashback any table to ogg_user;

grant execute on dbms_flashback to ogg_user;

grant execute on utl_file to ogg_user;

grant create any table to ogg_user;

grant insert any table to ogg_user;

grant update any table to ogg_user;

grant delete any table to ogg_user;

grant drop any table to ogg_user;

grant drop any table to ogg_user;

Initial Load

a       

      At Source Database Server

expdp parfile=expdp_additional_tables.par

Where expdp_additional_tables.par is a parameter file, its contents are

flashback_scn=2117284

DUMPFILE=ogg_user.dmp

LOGFILE=ogg_user.log

TABLES= SCOTT.DEPT,SCOTT.EMP

DIRECTORY=DB_DIR

Here flashback_scn is a specific scn to make a consistent database backup, it can be find by following query

SQL> Select current_scn from v$database;

CURRENT_SCN

-----------

    2117284

Transfer the backup from source to target DB server using below command

scp ogg_user.dmp 192.168.56.104:/home/oracle/

b       At Target Database Server

impdp directory=db_dir dumpfile=ogg_user.dmp logfile=ogg_user.log

Configure Source :-

  Add Trandata

i.      Create trandata statement and execute it on gg prompt

SQL> select ‘add trandata ‘||owner||’.’||object_name||’;’ from dba_objects where owner=’SCOTT’ and object_type=’TABLE’;

‘ADDTRANDATA’||OWNER||’.’||OBJECT_NAME||’;’

——————————————————————————–

add trandata SCOTT.EMP;

add trandata SCOTT.DEPT;

Note: Syntax for reference add trandata <owner>.<tablename>

Ø  Configure Manager

Go to goldengate installation location and Invoke ggsci utility

1.       cd /home/oracle/app/oracle/product/11.2.0/oggcore_1/

2.       ./ggsci

3.       DBLOGIN USERID ogg_user, PASSWORD ogg_user

4.       edit params mgr

5.       edit params .. command will open manager configuration file in vi editor, add following lines     

PORT 7809

userid ogg_user, password ogg_user

Ø  Configure Base Extract Process

1.       cd /home/oracle/app/oracle/product/11.2.0/oggcore_1/

2.       ./ggsci

3.       DBLOGIN USERID ogg_user, PASSWORD ogg_user

4.       edit params ext1.prm

5.       add following lines at ext1.prm configuration file

extract ext1

SETENV (ORACLE_HOME="/home/oracle/app/oracle/product/11.2.0/dbhome_1")

SETENV (NLS_DATE_FORMAT="YYYY-MM-DD:HH24:MI:SS")

SETENV (GG_HOME="/home/oracle/app/oracle/product/11.2.0/oggcore_1")

SETENV (ORACLE_GG_HOME="/home/oracle/app/oracle/product/11.2.0/oggcore_1")

6.       userid ogg_user, password ogg_user

7.       exttrail /home/oracle/app/oracle/product/11.2.0/oggcore_1/dirdat/lt

8.       table SCOTT.EMP;

9.       table SCOTT.DEPT;

Ø  Configure Pump Process

1.       cd /home/oracle/app/oracle/product/11.2.0/oggcore_1/

2.       ./ggsci

3.       DBLOGIN USERID ogg_user, PASSWORD ogg_user

4.       edit params pump1.prm

5.       add following lines at pump1.prm configuration file

EXTRACT pump1

SETENV (ORACLE_HOME="/home/oracle/app/oracle/product/11.2.0/dbhome_1")

SETENV (NLS_DATE_FORMAT="YYYY-MM-DD:HH24:MI:SS")

SETENV (GG_HOME="/home/oracle/app/oracle/product/11.2.0/oggcore_1")

SETENV (ORACLE_GG_HOME="/home/oracle/app/oracle/product/11.2.0/oggcore_1")

userid ogg_user, password ogg_user

rmthost ggdbb2, mgrport 7809

rmttrail /home/oracle/app/oracle/product/11.2.0/oggcore_1/dirdat/lt

PASSTHRU

TABLE SCOTT.DEPT;

TABLE SCOTT.EMP;

Configure Target :-

Ø  Configure Manager

Go to goldengate installation location and Invoke ggsci utility

6.       cd /home/oracle/app/oracle/product/11.2.0/oggcore_1/

7.       ./ggsci

8.       DBLOGIN USERID ogg_user, PASSWORD ogg_user

9.       edit params mgr

10.   edit params .. command will open manager configuration file in vi editor, add following lines     

PORT 7809

userid ogg_user, password ogg_user

Configure Replication Process

1.       cd /home/oracle/app/oracle/product/11.2.0/oggcore_1/

2.       ./ggsci

3.       DBLOGIN USERID ogg_user, PASSWORD ogg_user

4.       edit params rep1.prm

5.       add following lines at rep1.prm configuration file

replicat rep1

                SETENV (ORACLE_HOME="/home/oracle/app/oracle/product/11.2.0/dbhome_1")

SETENV (NLS_DATE_FORMAT="YYYY-MM-DD:HH24:MI:SS")

SETENV (GG_HOME="/home/oracle/app/oracle/product/11.2.0/oggcore_1")

                userid ogg_user, password oracle

                ASSUMETARGETDEFS

                MAP SCOTT.EMP, TARGET SCOTT.EMP, COLMAP (USEDEFAULTS) ;

                MAP SCOTT.DEPT, TARGET SCOTT.DEPT, COLMAP (USEDEFAULTS) ;

START EXTRACT AT SOURCE DB :- Invoke ggsci utility and execute following statements

START MGR

START EXTRACT ext1

START EXTRACT pump1

START REPLICAT AT TARGET DB:- Invoke ggsci utility and execute following statements

START MGR

START replicat REP1

Query Rewrite with materialized view based on remote objects

Query Rewrite with materialized view based on remote objects

This article is about different scenarios of query rewrite with materialized view created based on remote queries or objects

First a database link is created using query

create database link remotedb_link connect to scott identified by tiger using 'tns_entry_for_remotedb';

Can verify whether database link is valid using below query

select ename,dname,e.deptno from scott.emp e,dept@APPDBTSTS_SCOTT d
where e.deptno=d.deptno;

Now create a materialized view based on above query, which include remote object

SQL>  create materialized view APP_SCOTT_MV_remote
    enable query rewrite as
    select ename,dname,e.deptno from scott.emp e,dept@APPDBTSTS_SCOTT d
    where e.deptno=d.deptno;

Materialized view created.

Execute utlxrw.sql scrip to create a table named 'rewrite_table' which holds the output of 'explain_reqrite' procedure

@/u01/app/oracle/dbhome_1/rdbms/admin/utlxrw.sql

Table created.

Excecute explain_rewrite using lines mentioned under

declare
    qrytxt varchar2(100) := 'select ename,dname,e.deptno from scott.emp e,dept@APPDBTSTS_SCOTT d where e.deptno=d.deptno';
    begin
    dbms_mview.explain_rewrite(qrytxt,'APP_SCOTT_MV_remote','ID1');
    end;
    /

To check the output of above lines, query rewrite_table

select message from rewrite_table;

MESSAGE
--------------------------------------------------------------------------------
QSM-01150: query did not rewrite
QSM-01353: no query rewrite on remote objects unless integrity mode is set to st
ale_tolerated

Above error message shows query rewrite is disabled for remote object unless and to make query rewrite happen, parameter query_rewrite_integrity needs to be set as stale_tolerated which can be done at session level using statment here under

alter session set query_rewrite_integrity=stale_tolerated;

Rerun explain_rewrite

SQL> declare
    qrytxt varchar2(100) := 'select ename,dname,e.deptno from scott.emp e,dept@APPDBTSTS_SCOTT d where e.deptno=d.deptno';
    begin
    dbms_mview.explain_rewrite(qrytxt,'APP_SCOTT_MV_remote','ID1');
    end;
    /

PL/SQL procedure successfully completed.

SQL> select message from rewrite_table;

MESSAGE
--------------------------------------------------------------------------------

QSM-01151: query was rewritten
QSM-01209: query rewritten with materialized view, APP_SCOTT_MV_REMOTE, using te
xt match algorithm

Another finding during my pratice session for this article was an error
"QSM-01355:  multi-mv rewrite not possible in the presence of remote objects"

i.e. when trying "join back" scenario with remote materialized view using code below

declare
  2  qrytxt varchar2(100) := 'select empno,e.deptno from scott.emp e,dept@APPDBTSTS_SCOTT d where e.deptno=d.deptno';
  3  begin
  4  dbms_mview.explain_rewrite(qrytxt,'APP_SCOTT_MV_remote','ID1');
  5  end;
  6  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.89
SQL> select message from rewrite_table;

Got error message as:

MESSAGE
--------------------------------------------------------------------------------
QSM-01150: query did not rewrite
QSM-01082: Joining materialized view, APP_SCOTT_MV_REMOTE, with table, EMP, not
possible

QSM-01102: materialized view, APP_SCOTT_MV_REMOTE, requires join back to table,
EMP, on column, EMPNO

QSM-01355: multi-mv rewrite not possible in the presence of remote objects

I am unable to find any valid reason about it yet, if some one knows then I will appriciate your sharing.

Oracle 11g Flashback

This article is about usage of Flashback Technology in Oracle database 11g.

Topics:

· Overview of Oracle Flashback Technology
· Configuring Your Database for Oracle Flashback Technology
· Using Oracle Flashback Query (SELECT AS OF)
· Using Oracle Flashback Version Query
· Using Oracle Flashback Transaction Query
· Using Oracle Flashback Transaction Query with Oracle Flashback Version Query
· Using DBMS_FLASHBACK Package
· Using Flashback Transaction
· Using Flashback Data Archive (Oracle Total Recall.
· General Guidelines for Oracle Flashback Technology
· Performance Guidelines for Oracle Flashback Technology

Overview of Oracle Flashback Technology
A group of Oracle Database features that allow to query past states of database objects or to return database objects to a previous state without applying point-in-time media recovery.
With flashback features, you can:
· Perform queries that return past dat
· Perform queries that return metadata that shows a detailed history of changes to the database
· Recover tables or rows to a previous point in time
· Automatically track and archive transactional data changes
· Roll back a transaction and its dependent transactions while the database remains online

Oracle Flashback features uses the Automatic Undo Management (AUM) system to obtain metadata and istorical data for transactions. They rely on undo data, which are records of the effects of individual transactions. For example, if a user runs an UPDATE statement to change a salary from 1000 to 1100, then Oracle Database stores the value 1000 in the undo data.

Undo data is persistent and survives a database shutdown. By using flashback features, you can use undo data to query past data or recover from logical damage. Besides using it in flashback features, Oracle Database uses undo data to perform these actions:

· Roll back active transactions
· Recover terminated transactions by using database or process recovery
· Provide read consistency for SQL queries

Oracle Flashback Query

Use this feature to retrieve data for an earlier time that you specify with the AS OF clause of the SELECT statement.

Oracle Flashback Version Query

Use this feature to retrieve metadata and historical data for a specific time interval (for example, to view all the rows of a table that ever existed during a given time interval). Metadata for each row version includes start and end time, type of change operation, and identity of the transaction that created the row version. To create an Oracle Flashback Version Query, use the VERSIONS BETWEEN clause of the SELECT statement.

Oracle Flashback Transaction Query

Use this feature to retrieve metadata and historical data for a given transaction or for all transactions in a given time interval. To perform an Oracle Flashback Transaction Query, select from the static data dictionary view FLASHBACK_TRANSACTION_QUERY.

DBMS_FLASHBACK Package

Use this feature to set the internal Oracle Database clock to an earlier time so that you can examine data that was current at that time, or to roll back a transaction and its dependent transactions while the database remains online.

Flashback Transaction

Use Flashback Transaction to roll back a transaction and its dependent transactions while the database remains online. This recovery operation uses undo data to create and run the corresponding compensating transactions that return the affected data to its original state.

Flashback Data Archive (Oracle Total Recall)

Use Flashback Data Archive to automatically track and archive both regular queries and Oracle Flashback Query, ensuring SQL-level access to the versions of database objects without getting a snapshot-too-old error.

Database Administration Features

These flashback features are primarily for data recovery. Typically, you use these features only as a database administrator.

Oracle Flashback Table

Use this feature to restore a table to its state at a previous point in time. You can restore a table while the database is on line, undoing changes to only the specified table.

Oracle Flashback Drop

Use this feature to recover a dropped table. This feature reverses the effects of aDROP TABLE statement.

Oracle Flashback Database

Use this feature to quickly return the database to an earlier point in time, by undoing all of the changes that have taken place since then. This is fast, because you do not have to restore database backups.

Configuring Your Database for Oracle Flashback Technology

Before you can use flashback features in your application, you or your database administrator must perform the configuration tasks described in these topics:
· Configuring Your Database for Automatic Undo Managemen.
· Configuring Your Database for Oracle Flashback Transaction Query
· Configuring Your Database for Flashback Transaction
· Enabling Oracle Flashback Operations on Specific LOB Columns
· Granting Necessary Privileges.

Configuring Your Database for Automatic Undo Management
To configure your database for Automatic Undo Management (AUM), you or your database administrator must:
· Create an undo tablespace with enough space to keep the required data for flashback operations.
The more often users update the data, the more space is required. The database administrator usually calculates the space requirement.
· To enable AUM, Set these database initialization parameters:
o UNDO_MANAGEMENT=AUTO
o UNDO_TABLESPACE=UNDOTBS1
o UNDO_RETENTION=1440
For a fixed-size undo tablespace, Oracle Database automatically tunes the system to give the undo tablespace the best possible undo retention.

For an automatically extensible undo tablespace, Oracle Database retains undo data longer than the longest query duration and the low threshold of undo retention specified by the UNDO_RETENTION parameter.
· Specify the RETENTION GUARANTEE clause for the undo tablespace to ensure that unexpired undo data is not discarded.

Configuring Your Database for Oracle Flashback Transaction Query

To configure your database for the Oracle Flashback Transaction Query feature, you or your database administrator must:
· Ensure that Oracle Database is running with version 10.0 compatibility.
· Enable supplemental logging:
· ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Configuring Your Database for Flashback Transaction
To configure your database for the Flashback Transaction feature, you or your database administrator must:
· With the database mounted but not open, enable ARCHIVELOG
· ALTER DATABASE ARCHIVELOG
· Open at least one archive log:
· ALTER SYSTEM ARCHIVE LOG CURRENT;
· If not done, enable minimal and primary key supplemental logging:
· ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
· ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
· If you want to track foreign key dependencies, enable foreign key supplemental logging:
· ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS;

Enabling Oracle Flashback Operations on Specific LOB Columns
To enable flashback operations on specific LOB columns of a table, use the ALTER TABLE statement with the RETENTION option.
Because undo data for LOB columns can be voluminous, you must define which LOB columns to use with flashback operations.

Granting Necessary Privileges
You or your database administrator must grant privileges to users, roles, or applications that must use these flashback features.

For Oracle Flashback Query and Oracle Flashback Version Query

To allow access to specific objects during queries, grant FLASHBACK and SELECT privileges on those objects.
To allow queries on all tables, grant the FLASHBACK ANY TABLE privilege.

For Oracle Flashback Transaction Query

Grant the SELECT ANY TRANSACTION privilege.

To allow execution of undo SQL code retrieved by an Oracle Flashback Transaction Query, grant SELECT, UPDATE, DELETE, and INSERT privileges for specific tables.

For DBMS_FLASHBACK Package

To allow access to the features in the DBMS_FLASHBACK package, grant the EXECUTE privilege on DBMS_FLASHBACK.

For Flashback Data Archive (Oracle Total Recall)
To allow a specific user to enable Flashback Data Archive on tables, using a specific Flashback Data Archive, grant the FLASHBACK ARCHIVE object privilege on that Flashback Data Archive to that user. To grant the FLASHBACK ARCHIVE object privilege, you must either be logged on as SYSDBA or have FLASHBACK ARCHIVE ADMINISTER system privilege.

To allow execution of these statements, grant the FLASHBACK ARCHIVE ADMINISTER system privilege:

· CREATE FLASHBACK ARCHIVE
· ALTER FLASHBACK ARCHIVE
· DROP FLASHBACK ARCHIVE
To grant the FLASHBACK ARCHIVE ADMINISTER system privilege, you must be logged on as SYSDBA.

To create a default Flashback Data Archive, using either the CREATE FLASHBACK ARCHIVE or ALTER FLASHBACK ARCHIVE statement, you must be logged on as SYSDBA

To disable Flashback Data Archive for a table that has been enabled for Flashback Data Archive, you must either be logged on as SYSDBA or have the FLASHBACK ARCHIVE ADMINISTER system privilege.

Using Oracle Flashback Query (SELECT AS OF)
To use Oracle Flashback Query, use a SELECT statement with an AS OF clause. Oracle Flashback Query retrieves data as it existed at an earlier time. The query explicitly references a past time through a time stamp or System Change Number (SCN). It returns committed data that was current at that point in time.

Uses of Oracle Flashback Query include:

· Recovering lost data or undoing incorrect, committed changes.
For example, if you mistakenly delete or update rows, and then commit them, you can immediately undo the mistake.
· Comparing current data with the corresponding data at an earlier time.
· Checking the state of transactional data at a particular time.

For example, you can verify the account balance of a certain day.
· Simplifying application design by removing the need to store some kinds of temporal data.

Oracle Flashback Query lets you retrieve past data directly from the database.
· Applying packaged applications, such as report generation tools, to past data.
· Providing self-service error correction for an application, thereby enabling users to undo and correct their errors.

With a CREATE TABLE AS SELECT or INSERT INTO TABLE SELECT statement. For example, this query reinserts into table employees the rows that existed an hour ago:

INSERT INTO employees
(SELECT * FROM employees
AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '60' MINUTE)
MINUS SELECT * FROM employees);
SYSTIMESTAMP refers to the time zone of the database host environment.

Using Oracle Flashback Version Query
Use Oracle Flashback Version Query to retrieve the different versions of specific rows that existed during a given time interval. A row version is created whenever a COMMIT statement is executed.
Specify Oracle Flashback Version Query using the VERSIONS BETWEEN clause of the SELECT statement. The syntax is:
VERSIONS {BETWEEN {SCN | TIMESTAMP} start AND end}

Oracle Flashback Version Query returns a table with a row for each version of the row that existed at any time during the specified time interval. Each row in the table includes pseudocolumns of metadata about the row version. This information can reveal when and how a particular change (perhaps erroneous) occurred to your database.

A typical use of Oracle Flashback Version Query:
SELECT versions_startscn, versions_starttime,
versions_endscn, versions_endtime,
versions_xid, versions_operation,
last_name, salary
FROM employees
VERSIONS BETWEEN TIMESTAMP
TO_TIMESTAMP('2008-12-18 14:00:00', 'YYYY-MM-DD HH24:MI:SS')
AND TO_TIMESTAMP('2008-12-18 17:00:00', 'YYYY-MM-DD HH24:MI:SS')
WHERE first_name = 'John';

You can use VERSIONS_XID with Oracle Flashback Transaction Query to locate this transaction's metadata, including the SQL required to undo the row change and the user responsible for the change.

Using Oracle Flashback Transaction Query
Use Oracle Flashback Transaction Query to retrieve metadata and historical data for a given transaction or for all transactions in a given time interval. it queries the static data dictionary view FLASHBACK_TRANSACTION_QUERY,

The column UNDO_SQL shows the SQL code that is the logical opposite of the DML operation performed by the transaction, there are cases where the SQL_UNDO code is not the exact opposite of the original transaction. For example, a SQL_UNDO INSERT operation might not insert a row back in a table at the same ROWID from which it was deleted.

This statement uses Oracle Flashback Version Query as a subquery to associate each row version with the LOGON_USER responsible for the row data change:
SELECT xid, logon_user
FROM flashback_transaction_query
WHERE xid IN (
SELECT versions_xid FROM employees VERSIONS BETWEEN TIMESTAMP
TO_TIMESTAMP('2003-07-18 14:00:00', 'YYYY-MM-DD HH24:MI:SS') AND
TO_TIMESTAMP('2003-07-18 17:00:00', 'YYYY-MM-DD HH24:MI:SS')
);
Using Oracle Flashback Transaction Query with Oracle Flashback Version Query

In this example, a database administrator does this:

DROP TABLE emp;
CREATE TABLE emp (
empno NUMBER PRIMARY KEY,
empname VARCHAR2(16),
salary NUMBER
);
INSERT INTO emp (empno, empname, salary) VALUES (111, 'Mike', 555);
COMMIT;
DROP TABLE dept;
CREATE TABLE dept (
deptno NUMBER,
deptname VARCHAR2(32)
);
INSERT INTO dept (deptno, deptname) VALUES (10, 'Accounting');
COMMIT;
Nowemp and dept have one row each. In terms of row versions, each table has one version of one row. Suppose that an erroneous transaction deletes empno 111 from table emp:

UPDATE emp SET salary = salary + 100 WHERE empno = 111;
INSERT INTO dept (deptno, deptname) VALUES (20, 'Finance');
DELETE FROM emp WHERE empno = 111;
COMMIT;

Next, a transaction reinserts empno 111 into the emp table with a new employee name:

INSERT INTO emp (empno, empname, salary) VALUES (111, 'Tom', 777);
UPDATE emp SET salary = salary + 100 WHERE empno = 111;
UPDATE emp SET salary = salary + 50 WHERE empno = 111;
COMMIT;

SELECT versions_xid XID, versions_startscn START_SCN,

versions_endscn END_SCN, versions_operation OPERATION,
empname, salary
FROM emp
VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE
WHERE empno = 111;
Results are similar to:
XID START_SCN END_SCN O EMPNAME SALARY
---------------- ---------- ---------- - ---------------- ----------
09001100B2200000 10093466 I Tom 927
The database administrator identifies transaction 030002002B210000 as the erroneous transaction and uses Oracle Flashback Transaction Query to audit all changes made by this transaction:

SELECT xid, start_scn, commit_scn, operation, logon_user, undo_sql

FROM flashback_transaction_query
WHERE xid = HEXTORAW('000200030000002D');
Results are similar to:
XID START_SCN COMMIT_SCN OPERATION LOGON_USER
---------------- ---------- ---------- --------- ----------------------------
UNDO_SQL
--------------------------------------------------------------------------------
030002002B210000 10093452 10093459 DELETE HR
insert into "HR"."EMP"("EMPNO","EMPNAME","SALARY") values ('111','Mike','655');
030002002B210000 10093452 10093459 INSERT HR
delete from "HR"."DEPT" where ROWID = 'AAATjuAAEAAAAJrAAB';
030002002B210000 10093452 10093459 UPDATE HR
update "HR"."EMP" set "SALARY" = '555' where ROWID = 'AAATjsAAEAAAAJ7AAA';
030002002B210000 10093452 10093459 BEGIN HR

To make the result of the next query easier to read, the database administrator uses these SQL*Plus commands:

COLUMN operation FORMAT A9
COLUMN table_name FORMAT A10
COLUMN table_owner FORMAT A11
To see the details of the erroneous transaction and all subsequent transactions, the database administrator performs this query:
SELECT xid, start_scn, commit_scn, operation, table_name, table_owner
FROM flashback_transaction_query
WHERE table_owner = 'HR'
AND start_timestamp >=
TO_TIMESTAMP ('2002-04-16 11:00:00','YYYY-MM-DD HH:MI:SS');
Results are similar to:
XID START_SCN COMMIT_SCN OPERATION TABLE_NAME TABLE_OWNER
---------------- ---------- ---------- --------- ---------- -----------
02000E0074200000 10093435 10093446 INSERT DEPT HR
030002002B210000 10093452 10093459 DELETE EMP HR

Using DBMS_FLASHBACK Package

The DBMS_FLASHBACK package provides the same functionality as Oracle Flashback Query, but Oracle Flashback Query is sometimes more convenient.
You must have the EXECUTE privilege on the DBMS_FLASHBACK package.
To use the DBMS_FLASHBACK package in your PL/SQL code:
1. Specify a past time by invoking either DBMS_FLASHBACK.ENABLE_AT_TIME or DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER.
2. Perform regular queries (that is, queries without special flashback-feature syntax such as AS OF). Do not perform DDL or DML operations.
The database is queried at the specified past time.
3. Return to the present by invoking DBMS_FLASHBACK.DISABLE.
You must invoke DBMS_FLASHBACK.DISABLE before invoking DBMS_FLASHBACK.ENABLE_AT_TIME or DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER again. You cannot nest enable/disable pairs.
To use a cursor to store the results of queries, open the cursor before invoking DBMS_FLASHBACK.DISABLE. After storing the results and invoking DBMS_FLASHBACK.DISABLE, you can:
· Perform INSERT or UPDATE operations to modify the current database state by using the stored results from the past.
· Compare current data with the past data. After invoking DBMS_FLASHBACK.DISABLE, open a second cursor. Fetch from the first cursor to retrieve past data; fetch from the second cursor to retrieve current data. You can store the past data in a temporary table and then use set operators such as MINUS or UNION to contrast or combine the past and current data.

Using Flashback Transaction
The DBMS_FLASHBACK.TRANSACTION_BACKOUT procedure rolls back a transaction and its dependent transactions while the database remains online. This recovery operation uses undo data to create and run the compensating transactions that return the affected data to its original state.
The transactions being rolled back are subject to these restrictions:
· They cannot have performed DDL operations that changed the logical structure of database tables.
· They cannot use Large Object (LOB) Data Types,
o BFILE

o BLOB

o CLOB

o NCLOB

· They cannot use features that LogMiner does not support.