We're here to help!

Audit the Oracle Database Without Compromising Performance: Dbvisit Replicate’s Hidden “Gem”

Follow

Dbvisit-Replicates-Hidden-Gem-is-the-audit-feature.jpg

Auditing production database activity is an important administrative process, which should not be ignored.  Those who consider auditing unnecessary ignore it at their peril!. There are several techniques offered by Oracle to audit the database, in both Standard Edition (SE) and Enterprise Edition (EE), from the audit command and fine grained auditing, through to manual audit triggers. 

Regardless of the method used, auditing a database always impacts its performance. Auditing requires forethought about what exactly needs to be audited because, when enabled, it adds overhead in terms of resource utilisation and  space consumption. Poorly designed or unplanned approaches can lead to catastrophe. For example: AUD$ extends throughout the SYSTEM tablespace and could hang the database.

The audit feature of Dbvisit Replicate, one of its hidden gems, is mainly used in conjunction with ELT processing to load data changes in real time into the data warehouse.  It reduces the primary system load, and provides real time updates to the data warehouse. But this can also be employed to capture data changes made to specified schemas and tables in the primary database, for the purpose of analysis.

Along with recording data changes, Dbvisit Replicate also captures auditing information such as the type of DML operation, SCN, timestamp of DML, commit timestamp of DML, transition id, Oracle session ID, Oracle user and client information (hostname, process id, OS username, Application name, OS terminal). One of the key features, unlike traditional auditing options however, is that Dbvisit Replicate can be configured to audit data changes without impacting the performance of the database. And this harvested audit trail can then be written to a different database.

In short Dbvisit Replicate can be seen as the smart alternative for audit requirements over against the traditional approaches. So let’s take a look at how this works... By default the MINE process of Dbvisit Replicate runs on the primary database server, reads the redo (and archive logs if processing falls behind) to capture the data changes. Due to Dbvisit Replicate’s flexible architecture, we can also offload the MINE process to an intermediate server and instruct MINE to read only redo logs transferred by the lightweight FETCHER process, which has negligible impact on the source database/server. 

Another option can also be to have MINE read the archive, rather than redo, logs. So there are a number of different possible configurations that can be employed. In the following example, I have described the step-by-step configuration of Dbvisit Replicate for auditing data changes made to a table called ORDERS in my test environment, without impacting the performance of the source database. Environment

  Server (Linux) Database Schema Table
Source dbvlin203 dbl203 test1 Orders
Target dbvlin204 dbl204 test1 Orders_audit

Following is the structure of the Orders table:

SQL> desc orders;
Name Null? Type
-----------------------------------------
ORDER_DATE NOT NULL TIMESTAMP(6) WITH LOCAL TIME ZONE
ORDER_MODE VARCHAR2(8)
CUSTOMER_ID NOT NULL NUMBER(12)
ORDER_STATUS NUMBER(2)
ORDER_TOTAL NUMBER(8,2)
SALES_REP_ID NUMBER(6)
PROMOTION_ID NUMBER(6)
WAREHOUSE_ID NUMBER(6)
DELIVERY_TYPE VARCHAR2(15)
COST_OF_DELIVERY NUMBER(6)
WAIT_TILL_ALL_AVAILABLE VARCHAR2(15)
DELIVERY_ADDRESS_ID NUMBER(12)
CUSTOMER_CLASS VARCHAR2(30)
CARD_ID NUMBER(12)
INVOICE_ADDRESS_ID NUMBER(12)
SQL>

Config 1. Running the Setup Wizard:  In the first step of the Dbvisit Replicate setup wizard we specify source and target databases as usual, as we would do for “standard” (non-audit) replication. It is Steps 2 and 3 , however, which are critical for configuring Dbvisit Replicate for auditing. Setup Wizard - Step 2: -       Choose “ddl-only” in “Lock and copy the data initially one-by-one or at a single SCN?” question. -       Choose “ddl-run” in “What data instantiation script to create?” question.

Step 2 - Replication pairs
========================================
The second step is to set source and targets for each replication pair.
Let's configure the replication pair, selecting source and target.
Following databases are described:
1: dbl203 (Oracle)
2: dbl204 (Oracle)
Select source database: [1]
Select target database: [2]
Will DDL replication be enabled? [yes]
Use fetcher to offload the mining to a different server? (yes/no) [no]
Would you like to encrypt the data across the network (yes or no) [no]
Would you like to compress the data across the network (yes or no) [no]
How long do you want to set the network timeouts. Recommended range between 60-300 seconds [60]
Lock and copy the data initially one-by-one or at a single SCN?
one-by-one : Lock tables one by one and capture SCN
single-scn : One SCN for all tables
ddl-only : Only DDL script for target objects
resetlogs : Use SCN from last resetlogs operation (standby activation, rman incomplete recovery)
no-lock : Do not lock tables. Captures previous SCN of oldest active transaction. Requires pre-requisite running of pre-all.sh script (one-by-one/single-scn/ddl-only/resetlogs/no-lock) [single-scn] ddl-only

What data instantiation script to create?
dp_networklink : Data Pump with network link. No export datafile is created (APPLY.sh)
dp_exp : Data Pump with export datafile. Creates an export datafile (APPLY.sh)
exp : Pre-datapump exp/imp with export datafile (APPLY.sh)
ddl_file : DDL file created (APPLY.sql)
ddl_run : DDL is automatically executed on target
load : All replicated data is created and loaded automatically
none (dp_networklink/dp_exp/exp/ddl_file/ddl_run/load/none) [ddl_file] ddl_run

Following replication pairs are now configured:
1: dbl203 (Oracle) ==> dbl204 (Oracle), DDL: yes, fetcher: no, process suffix: (no suffix), compression: no, encryption: no, network timeout: 60, prepare type: ddl-only, data load: ddl_run
Enter number of replication pair to modify it, or "add", or "done": [done]

Selecting these two options instructs Dbvisit Replicate to create the audit table in target database when the APPLY process is started. It will do this automatically, and does not require additional manual configuration. Setup Wizard - Step 3: In step 3 of the setup wizardwe have the option to rename the target table if desired, as by default Dbvisit Replicate will assign it the same name as the source. We also activate the audit feature and select particular audit information we want to retrieve.

Step 3 - Replicated tables
========================================
The third step is to choose the schemas and tables to be replicated. If the databases are reachable, the tables are checked for existence, datatype support, etc., schemas are queried for tables. Note that all messages are merely
hints/warnings and may be ignored if issues are rectified before the scripts are actually executed.

Following tables are defined for replication pairs:
1: dbl203 (Oracle) ==> dbl204 (Oracle), DDL: yes, suffix: (no suffix), prepare: ddl-only
No tables defined.
Enter number of replication pair to modify it, or "done": [1]

Please enter list of all individual tables to be replicated. Enter schema name(s) only to replicate all tables in that schema. Use comma or space to delimit the entries.
Enter the tables and schemas: [] test1.orders
Selected tables: TEST1.ORDERS
Add more tables or schemas? (YES/NO) [NO]

To replicate changes of PL/SQL objects in schema(s), please enter the schemas to be replicated. Note that specifying any entry will cause additional privileges to be granted to dbvrep. Enter through a comma or space-delimited
list.
Enter the list of schemas (PL/SQL): []

You can also specify some advanced options:
1. Exclude some tables from schema-level replication
2. Rename schemas or tables.
3. Specify filtering conditions.
4. (Tables only) Configure Change Data Capture; this does not maintain a copy of the source table, but logs all operations as separate entries. This is useful for ETL or as an audit trail. This usually requires adding of new
columns (timestamps, old/new values etc.) to the target table.
Specify rename name, filter condition, CDC/Audit/ETL for any of the specified tables (yes/no): [no] yes
Rename SCHEMA name for TEST1.ORDERS (empty means no rename): []
Rename TABLE name for TEST1.ORDERS (empty means no rename): [] orders_audit
Filter the data to be replicated? If yes, use single condition for ALL DML operations or use CUSTOM conditions for each operation? (NO/ALL/CUSTOM) [NO]
Configure change data capture for change auditing or real-time BI? (NO/YES) [NO] yes
Capture DELETE operations? (YES/NO) [YES]
Capture UPDATE operations - old values? (YES/NO) [YES]
Capture UPDATE operations - new values? (YES/NO) [YES]
Capture INSERT operations? (YES/NO) [YES]
The columns at the target table can be called the same as on the source table, or they can be prefixed to indicate whether they contain old or new values. As both new and old values for update are specified, at least one prefix
has to be defined. Prefix for columns with OLD values: [] old_
Prefix for columns with NEW values: [] new_
Dbvisit Replicate can include additional information into the target table; this can be used to identify type of change, when it occurred, who initiated the change etc.
Add basic additional information about the changes? (SCN, time, operation type) (YES/NO) [YES]
Add more transactional information? (transaction id, commit time) (YES/NO) [NO] yes
Add auditing columns? (login user, machine, OS user...) (YES/NO) [NO] yes
Setup wizard chose following default names for the columns; you can use them or choose own names. Note that empty answer confirms the proposed default; use "-" (minus) to remove the column from the CDC.
CLIENT_INFO: Client info (cliinfo)
CURRENT_USER: Current user (cuser)
LOGON_USER: Logon user (luser)
MACHINE: Client machine name (machine)
OPERATION: Operation code (U/I/D) (opcol)
OS_PROC_ID: OS process id (osproc)
OS_PROG: OS program name (osprog)
OS_TERM: OS terminal (osterm)
OS_USER: OS user (osuser)
SCN: SCN at source (scn)
SERIAL: Oracle session serial# (serial)
SID: Oracle session ID (sid)
DATE_CHANGE: Date and time of the change (timestamp_change)
DATE_COMMIT: Date and time of transaction commit (timestamp_commit)
TRANSACTION_ID: Transaction ID (mandatory if timestamp_commit is used; please define an index on transaction ID in such case) (xidcol)
TRANSACTION_NAME: Transaction name (xidname)
Accept these settings? (YES/NO) [YES]

Following tables are defined for replication pairs:
1: dbl203 (Oracle) ==> dbl204 (Oracle), DDL: yes, suffix: (no suffix), prepare: ddl-only
TEST1.ORDERS => TEST1.ORDERS_AUDIT
Enter number of replication pair to modify it, or "done": [done]

Step-4 of the setup wizard is used to configure MINE and APPLY processes as shown hereConfig 2: Running  *-all.sh script After completing the setup wizard, we can run the *-all.sh script to execute the configuration scripts generated by the setup wizard. Following is the screenshot of my test system *-all.sh script.

oracle@dbvlin203[/home/oracle/dbl203]: ./dbl203-all.sh
Setting up Dbvisit Replicate configuration
Configure database dbl203...
This check fails if the DBID is not the expected one...
Ok, check passed.
Configure database dbl204...
This check fails if the DBID is not the expected one...
Ok, check passed.
Object grants for database dbl203...
Object grants for database dbl204...
Setting up the configuration
Initializing......done
WARN-1850: No DDC DB available, dictionary table does not exist.
DDC loaded from database (0 variables).
Dbvisit Replicate version 2.7.02.4333
Copyright (C) Dbvisit Software Limited. All rights reserved.
DDC file /home/oracle/dbl203/config/dbl203-onetime.ddc loaded.
MINE: Cannot determine Dbvisit Replicate dictionary version. (no
dictionary exists)
APPLY: Cannot determine Dbvisit Replicate dictionary version. (no
dictionary exists)
dbvrep> #clear the no-DDC-DB-available warning
dbvrep> process clear previous warnings
dbvrep> set ON_WARNING SKIP
Variable ON_WARNING set to SKIP for process *.
dbvrep> set ON_ERROR EXIT
Variable ON_ERROR set to EXIT for process *.
dbvrep> PROCESS SWITCH_REDOLOG
dbvrep> PROCESS SETUP MINE DROP DICTIONARY
0 dictionary objects dropped.
dbvrep> PROCESS SETUP MINE CREATE DICTIONARY
dbvrep> PROCESS SETUP MINE LOAD DICTIONARY
Supplemental logging on database set.
Loading dictionary table DBRSCOL$
Loading dictionary table DBRSIND$
Loading dictionary table DBRSOBJ$
Loading dictionary table DBRSTAB$
Loading dictionary table DBRSUSER$
Loading dictionary table DBRSV_$DATABASE
dbvrep> PROCESS SETUP APPLY DROP DICTIONARY
0 dictionary objects dropped.
dbvrep> PROCESS SETUP APPLY CREATE DICTIONARY
dbvrep> PROCESS SETUP APPLY LOAD DICTIONARY
dbvrep> PROCESS PREPARE_DP SETUP CLEAR
dbvrep> PROCESS SETUP PAIR MINE AND APPLY
Applier SCN set (start=1157061, current=1157061).
dbvrep> SET APPLY.INSTANTIATE_SCN NOW
Variable INSTANTIATE_SCN set to NOW for process APPLY.
dbvrep> EXCLUDE CREATE TABLE %.DBMS_TABCOMP_TEMP_UNCMP #Ignore tables
created by Compression Advisor
Exclude rule created.
dbvrep> EXCLUDE CREATE TABLE %.DBMS_TABCOMP_TEMP_CMP #Ignore tables
created by Compression Advisor
Exclude rule created.
dbvrep> memory_set IGNORE_APPLY_DDL_DIFFERENCES YES
Variable IGNORE_APPLY_DDL_DIFFERENCES set to YES for process *.
dbvrep> PROCESS SUPPLEMENTAL LOGGING TABLE "TEST1"."ORDERS" ENABLE
PRIMARY KEY
dbvrep> PROCESS SWITCH_REDOLOG
dbvrep> PROCESS WAIT_SCN_FLIP
Waited 0 seconds until scn_to_timestamp changed.
dbvrep>
dbvrep> #prepare the tables (we use OFFLINE as neither MINE not APPLY
is running; with OFFLINE we won't wait on network timeout)
dbvrep> PREPARE OFFLINE TABLE "TEST1"."ORDERS" NODDL RENAME TO
"TEST1"."ORDERS_AUDIT"
Table TEST1.ORDERS instantiated at SCN 1159926
dbvrep> #CDC/Audit settings
dbvrep> CDCAUDIT TABLE "TEST1"."ORDERS" INSERT YES
dbvrep> CDCAUDIT TABLE "TEST1"."ORDERS" DELETE YES
dbvrep> CDCAUDIT TABLE "TEST1"."ORDERS" UPDATE YES
dbvrep> CDCAUDIT TABLE "TEST1"."ORDERS" OLDCOL_PREFIX old_
dbvrep> CDCAUDIT TABLE "TEST1"."ORDERS" NEWCOL_PREFIX new_
dbvrep> CDCAUDIT TABLE "TEST1"."ORDERS" OPCOL OPERATION
dbvrep> CDCAUDIT TABLE "TEST1"."ORDERS" SCN SCN
dbvrep> CDCAUDIT TABLE "TEST1"."ORDERS" TIMESTAMP_CHANGE DATE_CHANGE
dbvrep> CDCAUDIT TABLE "TEST1"."ORDERS" XIDCOL TRANSACTION_ID
dbvrep> CDCAUDIT TABLE "TEST1"."ORDERS" TIMESTAMP_COMMIT DATE_COMMIT
dbvrep> CDCAUDIT TABLE "TEST1"."ORDERS" SID SID
dbvrep> CDCAUDIT TABLE "TEST1"."ORDERS" SERIAL SERIAL
dbvrep> CDCAUDIT TABLE "TEST1"."ORDERS" CUSER CURRENT_USER
dbvrep> CDCAUDIT TABLE "TEST1"."ORDERS" LUSER LOGON_USER
dbvrep> CDCAUDIT TABLE "TEST1"."ORDERS" CLIINFO CLIENT_INFO
dbvrep> CDCAUDIT TABLE "TEST1"."ORDERS" OSUSER OS_USER
dbvrep> CDCAUDIT TABLE "TEST1"."ORDERS" MACHINE MACHINE
dbvrep> CDCAUDIT TABLE "TEST1"."ORDERS" OSTERM OS_TERM
dbvrep> CDCAUDIT TABLE "TEST1"."ORDERS" OSPROC OS_PROC_ID
dbvrep> CDCAUDIT TABLE "TEST1"."ORDERS" OSPROG OS_PROG
dbvrep> CDCAUDIT TABLE "TEST1"."ORDERS" XIDNAME TRANSACTION_NAME
dbvrep>
dbvrep> PROCESS SWITCH_REDOLOG
dbvrep> #prepare script for instantiation
dbvrep> DDL DROP REPLICATE "TEST1"."ORDERS"
OK-1926: Execute failed on SQL 'DROP TABLE "TEST1"."ORDERS_AUDIT"
cascade constraints' with error ORA-00942: table or view does not exist
(DBD ERROR: error possibly near <*> indicator at char 19 in 'DROP TABLE
"TEST1".<*>"ORDERS_AUDIT" cascade constraints')
dbvrep> DDL CREATE_FROM_DICT REPLICATE "TEST1"."ORDERS"
dbvrep> create ddcdb from ddcfile
DDC loaded into database (345 variables).
dbvrep> set ON_WARNING SKIP
Variable ON_WARNING set to SKIP for process *.
dbvrep> set ON_ERROR SKIP
Variable ON_ERROR set to SKIP for process *.
OK-0: Completed successfully.
Some apply tables were (re)created by the .dbvrep script; now we run the grant scripts again.
Object grants for database dbl203...
Object grants for database dbl204...
These steps are required after the dbl203-all.sh script runs:

1) Create the necessary directory(ies) on the servers:
dbvlin203: /home/oracle/dbl203
dbvlin204: /home/oracle/dbl203

2) Copy the DDC files to the server(s) where the processes will run:
dbvlin204: /home/oracle/dbl203/dbl203-APPLY.ddc
dbvlin203: /home/oracle/dbl203/dbl203-MINE.ddc

3) Review that path to dbvrep executable is correct in the run scripts:
/home/oracle/dbl203/dbl203-run-dbvlin203.sh
/home/oracle/dbl203/dbl203-run-dbvlin204.sh

4) Copy the run script to the server(s) where the processes will run:
dbvlin203: /home/oracle/dbl203/dbl203-run-dbvlin203.sh
dbvlin204: /home/oracle/dbl203/dbl203-run-dbvlin204.sh

5) Ensure firewall is open for listen interfaces 0.0.0.0:7902, 0.0.0.0:7901 used by the processes.

6) Make sure the data on apply are in sync as of time when setup was run.

7) Start the replication processes on all servers:
dbvlin203: /home/oracle/dbl203/dbl203-run-dbvlin203.sh
dbvlin204: /home/oracle/dbl203/dbl203-run-dbvlin204.sh

8) Start the console to monitor the progress:
/home/oracle/dbl203/start-console.sh

The above list is stored in /home/oracle/dbl203/Nextsteps.txt.
oracle@dbvlin203[/home/oracle/dbl203]:

Config 3: Starting MINE and APPLY processes.  The next step is to copy the APPLY ddc file and the script to start the APPLY process to the target server.

oracle@dbvlin203[/home/oracle/dbl203]: scp dbl203-APPLY.ddc dbl203-run-dbvlin204.sh oracle@dbvlin204:/home/oracle/dbl203/
dbl203-APPLY.ddc 100% 527 0.5KB/s 00:00
dbl203-run-dbvlin204.sh 100% 99 0.1KB/s 00:00
oracle@dbvlin203[/home/oracle/dbl203]:

Then, as we want to keep the workload on the source system to a minimum, we instruct MINE to read from archives only by adding “set REDO_READ_METHOD=ARCHONLY” command in the MINE ddc file.

memory_set CHECKVARS ON
memory_set ON_WARNING SKIP
memory_set ON_ERROR EXIT
memory_set DDC_ID 1
memory_set DDC_DATABASE dbl203
memory_set DDC_DATABASE_DBID 1047371926
memory_set DDC_PASSWORD 53616c7465645f5fdada65305132ded8f1882ca8ce131ffb00200065dc654fd6
memory_set DDC_USER dbvrep
memory_set TNS_ADMIN /u01/app/oracle/product/11.2.0/db_1/network/admin

#load rest of the settings from database
load ddcdb
memory_set ON_WARNING SKIP
memory_set ON_ERROR SKIP
########## Any manual settings should go below this line ##########
set REDO_READ_METHOD=ARCHONLY

Start MINE processes

oracle@dbvlin203[/home/oracle/dbl203]: ./dbl203-run-dbvlin203.sh
Initializing......done
DDC loaded from database (345 variables).
Dbvisit Replicate version 2.7.02.4333
Copyright (C) Dbvisit Software Limited. All rights reserved.
DDC file /home/oracle/dbl203/dbl203-MINE.ddc loaded.
Starting process MINE...Created directory /home/oracle/dbl203/ddc_backup
Created directory /home/oracle/dbl203/log/trace/
Created directory /home/oracle/dbl203/mine/
started
oracle@dbvlin203[/home/oracle/dbl203]:

Start APPLY process.

oracle@dbvlin204[/home/oracle/dbl203]: ./dbl203-run-dbvlin204.sh
Initializing......done
DDC loaded from database (345 variables).
Dbvisit Replicate version 2.7.02.4333
Copyright (C) Dbvisit Software Limited. All rights reserved.
DDC file /home/oracle/dbl203/dbl203-APPLY.ddc loaded.
Starting process APPLY...Created directory /home/oracle/dbl203/ddc_backup
Created directory /home/oracle/dbl203/log/
Created directory /home/oracle/dbl203/log/trace/
Created directory /home/oracle/dbl203/apply
started
oracle@dbvlin204[/home/oracle/dbl203]:

You can now see Replication console with current status of MINE and APPLY by running start-console.sh script.

\ Dbvisit Replicate 2.7.02.4333(MAX edition) - Evaluation License expires in 30 days
MINE is waiting for next redo log. Currently at plog 128 and SCN 0 (08/08/2014 15:30:55).
APPLY IS running. Currently at plog 128 and SCN 1162458 (08/08/2014 15:30:55).
Progress of replication dbl203:MINE->APPLY: total/this execution
-------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------
dbvrep>

Following is the structure of the orders_audit table, created in the test1 schema of the target database by the APPLY process.

SQL> desc ORDERS_AUDIT
 Name					   Null?    Type
 ----------------------------------------- -------- ------------
 OLD_ORDER_ID					        NUMBER(12)
 NEW_ORDER_ID					        NUMBER(12)
 OLD_ORDER_DATE 				      TIMESTAMP(6) WITH LOCAL TIME ZONE
 NEW_ORDER_DATE 				      TIMESTAMP(6) WITH LOCAL TIME ZONE
 OLD_ORDER_MODE 				      VARCHAR2(8)
 NEW_ORDER_MODE 				      VARCHAR2(8)
 OLD_CUSTOMER_ID				      NUMBER(12)
 NEW_CUSTOMER_ID				      NUMBER(12)
 OLD_ORDER_STATUS				      NUMBER(2)
 NEW_ORDER_STATUS				      NUMBER(2)
 OLD_ORDER_TOTAL				      NUMBER(8,2)
 NEW_ORDER_TOTAL				      NUMBER(8,2)
 OLD_SALES_REP_ID				      NUMBER(6)
 NEW_SALES_REP_ID				      NUMBER(6)
 OLD_PROMOTION_ID				      NUMBER(6)
 NEW_PROMOTION_ID				      NUMBER(6)
 OLD_WAREHOUSE_ID				      NUMBER(6)
 NEW_WAREHOUSE_ID				      NUMBER(6)
 OLD_DELIVERY_TYPE				    VARCHAR2(15)
 NEW_DELIVERY_TYPE				    VARCHAR2(15)
 OLD_COST_OF_DELIVERY				  NUMBER(6)
 NEW_COST_OF_DELIVERY				  NUMBER(6)
 OLD_WAIT_TILL_ALL_AVAILABLE	VARCHAR2(15)
 NEW_WAIT_TILL_ALL_AVAILABLE	VARCHAR2(15)
 OLD_DELIVERY_ADDRESS_ID			NUMBER(12)
 NEW_DELIVERY_ADDRESS_ID			NUMBER(12)
 OLD_CUSTOMER_CLASS				    VARCHAR2(30)
 NEW_CUSTOMER_CLASS				    VARCHAR2(30)
 OLD_CARD_ID					        NUMBER(12)
 NEW_CARD_ID					        NUMBER(12)
 OLD_INVOICE_ADDRESS_ID 			NUMBER(12)
 NEW_INVOICE_ADDRESS_ID 			NUMBER(12)
 OPERATION					          CHAR(1)
 TRANSACTION_ID 				      VARCHAR2(30)
 DATE_CHANGE					        DATE
 DATE_COMMIT					        DATE
 SID						              NUMBER
 SERIAL 					            NUMBER
 CURRENT_USER					        VARCHAR2(40)
 LOGON_USER					          VARCHAR2(40)
 CLIENT_INFO					        VARCHAR2(1000)
 OS_USER					            VARCHAR2(1000)
 MACHINE					            VARCHAR2(1000)
 OS_TERM					            VARCHAR2(1000)
 OS_PROC_ID					          VARCHAR2(1000)
 OS_PROG					            VARCHAR2(1000)
 SCN						              NUMBER
 TRANSACTION_NAME				      VARCHAR2(256)

SQL>

Now let’s see auditing in action. I am going to update, delete and insert a record with order_id=148 in the Orders table on the source database.

SQL> select  order_id,order_date,customer_id, delivery_type from test1.orders where order_id=148;
  ORDER_ID ORDER_DATE								       CUSTOMER_ID DELIVERY_TYPE
---------- --------------------------------------------------------------------------- ----------- ---------------
       148 19-JAN-09 10.00.00.000000 PM 						    822251 Next_Day
SQL> update test1.orders set delivery_type='Express' where order_id=148;
1 row updated.
SQL> commit;
Commit complete.
SQL> delete from test1.orders where order_id=148;
1 row deleted.
SQL> commit;
Commit complete.
SQL> insert into test1.orders values(148,sysdate,'online',822251,3,3454,623,437,780,'Next_Day',4,'ship_when_ready',6,'Occasional',177868,4);
1 row created.
SQL> commit;
Commit complete.
SQL> alter system archive log current;
System altered.
SQL>

Replication Console:

\ Dbvisit Replicate 2.7.02.4333(MAX edition) - Evaluation License expires in 30 days
MINE is waiting for next redo log. Currently at plog 137 and SCN 0 (08/08/2014 15:41:31).
APPLY IS running. Currently at plog 137 and SCN 1165470 (08/08/2014 15:41:29).
Progress of replication dbl203:MINE->APPLY: total/this execution
--------------------------------------------------------------------------------------------------------------------------------------------
TEST1.ORDERS:                 100%  Mine:3/3             Unrecov:0/0         Applied:3/3         Conflicts:0/0       Last:08/08/2014 15:40:29/OK
--------------------------------------------------------------------------------------------------------------------------------------------
1 tables listed.
dbvrep>

Dbvisit Replicate has captured these DMLs and stored three records in the orders_audit table. There are many columns in the ORDERS_AUDIT table, therfore I am first showing values populated from DML execution on source and then audit information.

SQL> select OLD_ORDER_ID,NEW_ORDER_ID,OLD_DELIVERY_TYPE,NEW_DELIVERY_TYPE from orders_audit;

OLD_ORDER_ID NEW_ORDER_ID OLD_DELIVERY_TY NEW_DELIVERY_TY
------------ ------------ --------------- ---------------
	 148	     148           Next_Day	        Express
	 148		                 Express
	 148		                                  Next_Day
SQL>select OPERATION,TRANSACTION_ID,DATE_CHANGE,DATE_COMMIT,SID,SERIAL,CURRENT_USER,LOGON_USER,OS_USER,OS_TERM,OS_PROC_ID,OS_PROG,SCN from orders_audit;

OPERATION TRANSACTION_ID     DATE_CHANGE   DATE_COMMIT  SID  SERIAL CURRENT_USER  LOGON_USER  OS_USER  OS_TERM  OS_PROC_ID         OS_PROG                                 SCN
--------- -----------------  -----------  ------------ ---- ------- ------------ ------------ -------- -------- ------------  -----------------------------------------  ----------
  U	      0007.006.0000038f	  08/08/14	   08/08/14	    33	  1479	   SYS	         TEST1	    oracle	  pts/1	   17174	    sqlplus@dbvlin203.dbvisit.co.nz (TNS V1-V3)	1164752
  D	      0003.007.00000457	  08/08/14	   08/08/14	    33	  1479	   SYS	         TEST1	    oracle	  pts/1	   17174	    sqlplus@dbvlin203.dbvisit.co.nz (TNS V1-V3)	1164777
  I

The orders_audit table can be used to monitor user activities, as well as enabling us to make use of both the old and new datasets in the ETL process of data warehousing. Conclusion: Dbvisit Replicate provides a cost effective logical data replication solution from Oracle to Oracle and out to other RDBMS platforms, allowing you to complete migrations & upgrades, provide offload reporting and manage distributed data. But don’t forget this additional hidden gem, as the audit feature is there for you to explore and embed in different data analysis solutions. If you want to know more about this audit feature or want to discuss any use cases contact us now.

Have more questions? Submit a request

Comments