ILM in 12c - Flashback Data Archive (session context capture)

Capturing context information in flashback archive records and retrieving it

Discussed below is an example of how Oracle 12c can capture (and retrieve) session context information when a flashback-archive enabled table is modified.

The example has these steps:

Step 1 : Sets up a schema and a table within that schema, having flashback archiving enabled. 

Step 2 : After inserting a few rows, they are modified by connecting as a different database user. 

Step 3 : The captured context and flashback data are then queried.

Step # 1 Set up the schema

Setting up the tablespace for use by the test schema and flashback archive…

[oracle@laboms ~]$ sqlplus system/oracle@localhost:1521/noncdb3

SQL*Plus: Release 12.1.0.2.0 Production on Fri Sep 5 13:32:33 2014

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Fri Sep 05 2014 11:58:06 +08:00

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>   create tablespace users

      datafile '/u01/app/oracle/oradata/noncdb3/users.ora'

  size 10m autoextend on ;  2    3 

Tablespace created.

SQL>  create tablespace ts_arch

      datafile '/u01/app/oracle/oradata/noncdb3/ts_arch.ora'

  size 10m ;  2    3 

Tablespace created.

Setting up the schema (FDB_USER) and table which will be enabled for flashback archiving…

SQL>  create user fdb_user identified by oracle

   profile default default tablespace users;

 

User created.

SQL> alter user fdb_user quota unlimited on ts_arch;

User altered.

Granting the necessary privileges to the user…

SQL>   grant create session, create table, unlimited tablespace to fdb_user;

Grant succeeded.

SQL>   grant flashback archive administer to fdb_user;

Grant succeeded.

Creating the flashback archive in the database…

SQL> create flashback archive fdba1

        tablespace ts_arch  retention 1 year;  2 

Flashback archive created.

SQL> alter table fdb_user.flashback_trial

  2   flashback archive fdba1;

Table altered.

Setting the level of context detail captured in flashback archive… (setting to ALL)

begin

            dbms_flashback_archive.set_context_level(level => 'ALL');

 end;

 /

  2    3    4 

PL/SQL procedure successfully completed.

Step # 2 – Creating the archive-enabled table and populating it…

Creation of the actual table whose evolution will be tracked in the flashback archive and populate it with some data.

[oracle@laboms ~]$ sqlplus fdb_user/oracle@localhost:1521/noncdb3

...

SQL> create table flashback_trial as

        select object_id, object_name from all_objects where 2=1;  2 

Table created.

SQL> insert into flashback_trial select object_id,object_name from all_objects where rownum<=5;

5 rows created.

SQL> commit;

Commit complete.

Viewing the data…

SQL> select * from flashback_trial;

OBJECT_ID OBJECT_NAME

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

      133 ORA$BASE

      142 DUAL

      143 DUAL

      356 MAP_OBJECT

      453 SYSTEM_PRIVILEGE_MAP

Connecting as the system user and modifying the data…

[oracle@laboms ~]$ sqlplus system/oracle@localhost:1521/noncdb3

SQL*Plus: Release 12.1.0.2.0 Production on Fri Sep 5 14:20:07 2014

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Fri Sep 05 2014 13:32:33 +08:00

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> alter session set current_schema=fdb_user;

Session altered.

Now we will delete the record with object_id=143;

SQL> select * from fdb_user.flashback_trial where object_id=143;

 OBJECT_ID OBJECT_NAME

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

       142      DUAL

SQL> delete fdb_user.flashback_trial where object_id=143;

1 row deleted.

SQL> commit;

Commit complete.

Querying the USERENV context for some attributes from the session which deleted the record.

SQL> select sys_context('USERENV','SESSIONID') sessionid,

  2            sys_context('USERENV','SID') sid,

  3           sys_context('USERENV','CURRENT_SCHEMA') schema,

  4           sys_context('USERENV','IP_ADDRESS') ip_addr from dual;

SESSIONID            SID                  SCHEMA               IP_ADDR

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

120085                 24                     FDB_USER           192.168.1.108

Step # 3 – Querying the information stored in the flashback archive.

Retrieving the transaction id of the delete operation using flashback versions query…

SQL > select versions_xid, versions_operation, flashback_trial.*

  from fdb_user.flashback_trial versions between scn minvalue and maxvalue; 

VERSIONS_XID         V  OBJECT_ID OBJECT_NAME

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

06000A009C010000     D        143 DUAL

                              143 DUAL

                              356 MAP_OBJECT

                              453 SYSTEM_PRIVILEGE_MAP

The way to retrieve the context is by using the transaction id which marked the data change. To get the context, use the DBMS_FLASHBACK_ARCHIVE.get_sys_context() procedure as shown

Parameter	Purpose	Example value
Xid	Transaction identifier. Use the versions_xid returned from the flashback versions query	06000A009C010000
Namespace	Name of the context	‘USERENV’ typically
Parameter	The attribute to retrieve	Any of the attributes that of the USERENV context

In the example below, the context information is retrieved using the dbms_flashback_archive.get_sys_context procedure call (output reformatted):

SQL > select sys.dbms_flashback_archive.get_sys_context(xid       => hextoraw('06000A009C010000'),
                  namespace => 'USERENV',parameter => 'SESSION_USER') sess_user,
       sys.dbms_flashback_archive.get_sys_context(xid       => hextoraw('06000A009C010000'),
                 namespace => 'USERENV',parameter => 'SESSIONID') sess_id,
       sys.dbms_flashback_archive.get_sys_context(xid       => hextoraw('06000A009C010000'),
                 namespace => 'USERENV',parameter => 'IP_ADDRESS') ip_addr
  FROM DUAL;
 

SESS_USER            SESS_ID              IP_ADDR

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

SYSTEM               120085               192.168.1.108

The context details captured provide additional information about the environment in which the change took place.