Supplemental Note For Backing Up Transactions To Shadow Table (Doc ID 1085170.1)

Last updated on FEBRUARY 14, 2019

Applies to:

Purpose

There are multiple other methods for backing up the transactions in the database. This note presents some sample trigger scripts that can be used to update the SptTransactionEntryShadow table after a transaction entry is saved to the SptTransactionEntry table in the IPSA DB.

The samples below present two examples of how this could be achieved:

1. Create a table based sequence number to generate a primary key.
2. Create a table using composite primary based on the 4 GUIDs.

Previously the Primary Key had been based on the Id, which we subsequently ran into an issue as this is not always unique. The old triggers provided also updated the SptTransactionEntryShadow "before insert" to the SptTransactionEntry table, the new triggers insert after.

An important characteristic of triggers is that there is no way to prevent a failing trigger from failing the operation on which it fires. Even if you change we change it from a before to an after, if the trigger fails, the operation will fail. They are atomic. Therefore, if you want to use triggers to update a transaction backup table, you must be aware that if the trigger fails, the policy server will not be able to write to the transaction table and IPSA will stop. One way to mitigate this is to ensure that the trigger does not fail.

The 4 GUIDs constitute a 128 bit number which is our implementation of the universal unique id standard. If you are concerned about a unique result maybe add an extra clause, say date deleted.

Scope

1) The shadow table and triggers are not productized code offered by Oracle. They are a sample method of backing up the transaction table. Different customers use different methods to do this.

2) The shadow table and triggers are not part of the transaction exporter utility deliverable. The transaction exporter utility reads the shadow table to get its data. The reason that we have productized this tool (even though it reads data from a non-productized table) is because it has code in it that a customer could not replicate. This code is able to read the affected concretes and operations columns and translate them into human readable form. We do not expect a customer to be able to translate the data in these columns.

The transaction exporter utility looks for specific names, regardless of the additional column(s). If I say rename Name to something else, the output file contains a blank entry and the tool does not fail.

Details

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.