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

Open

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

Following is the structure of the Orders table:

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.

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-4 of the setup wizard is used to configure MINE and APPLY processes as shown here. Config 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.

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.

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.

Start MINE processes

Start APPLY process.

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

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

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.

Replication Console:

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.

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.