Oracle DRM (Data Relationship Management) Application Logs

To access Oracle DRM (Data Relationship Management) application related activities log, there are two methods:

1. Auditing Transactions, Jobs, and Requests through DRM Web Client
2. Extracting DRM (Data Relationship Management) application log from backend DRM application database schema

Auditing Transactions, Jobs, and Requests through DRM (Data Relationship Management) Web Client

Oracle Data Relationship Management records a history of different activities performed within an application for audit purposes. 

• The transaction history logs all operations performed including changes to version data, application metadata, and user security. 
• The job history tracks the completion of long running processes such as imports, blenders, and exports. 
• The request history maintains a record of change requests submitted from external applications.
  

For further details you can read DRM (Oracle Data Relationship Management) Suite User's Guide.

You can query and view transactions, requests, and job history using the Audit task wizard in DRM Web Client navigating through Home --> Audit as shown below:

The audit wizard is used to define the parameters and criteria for the query. The wizard content is organized across Source, Filters, and Columns tabs to guide input. Although the wizard tabs are ordered to gather input in a sequence, you can navigate between any tabs.

Extracting DRM (Data Relationship Management) application log from backend DRM application database schema

Extracting DRM Transaction History:

In DRM application database schema, there is a table name RM_TRANSACTION_HISTORY that contains a row for each transactional update made to your DRM repository.

Below is all the columns listed what RM_TRANSACTION_HISTORY table consists of: 

Name                                       Null                     Type            

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

I_TRANSACTION_ID           NOT NULL         NUMBER(38)      

I_DETAIL_ID                         NOT NULL         NUMBER(38)      

D_TIMESTAMP                                                 TIMESTAMP(6)    

I_SESSION_ID                                                   NUMBER(38)      

C_USER_NAME                    NOT NULL        NVARCHAR2(100)  

C_ACTION                                                         NVARCHAR2(40)   

C_ACTION_LEVEL                                          NVARCHAR2(40)   

C_VERSION_ABBREV                                    NVARCHAR2(255)  

I_VERSION_ID                                                  NUMBER(38)      

C_HIERARCHY_ABBREV                              NVARCHAR2(255)  

I_HIERARCHY_ID                                           NUMBER(38)      

C_NODE_ABBREV                                           NVARCHAR2(255)  

I_NODE_ID                                                       NUMBER(38)      

C_NODE_DESCR                                             NVARCHAR2(255)  

C_PROPERTY_ABBREV                                  NVARCHAR2(255)  

I_PROPERTY_ID                                               NUMBER(38)      

B_LEAF                                     NOT NULL     NUMBER(38)      

C_ACTION_DESCRIPTION                             NVARCHAR2(1024) 

X_FROM_VALUE                                                NCLOB           

C_FROM_ORIGIN                                               NVARCHAR2(1024) 

X_TO_VALUE                                                      NCLOB           

C_TO_ORIGIN                                                     NVARCHAR2(1024) 

I_ORIGINATING_ID                                             NUMBER(38)      

I_ORIGINATING_DETAIL_ID                             NUMBER(38)      

C_OBJECT_NAME                                                NVARCHAR2(255)  

I_DOMAIN_ID                                                       NUMBER(38)      

C_DOMAIN_ABBREV                                          NVARCHAR2(255)  

I_WORKFLOW_REQUEST_ID                              NUMBER(38)      

I_WORKFLOW_REQUEST_ITEM_ID                  NUMBER(38)      

C_NODE_TYPE                                                       NVARCHAR2(255)  

As we see, RM_TRANSACTION_HISTORY columns include: Action (e.g. Add Node, Insert Node, Delete Node, and ChangeProp), Node, Hierarchy, Version and Property Name values as well as Node Description, Leaf flag, to and from Property values for ChangeProp operations.

You can query this RM_TRANSACTION_HISTORY table to extract DRM application related logs for a particular time period.

For example, if you want to extract DRM Application log from 13-Mar-2021 to 19-Mar-2021, run following query:

select * from RM_Transaction_History where D_TIMESTAMP between to_date('2021-03-13 00:00:01', 'YYYY-MM-DD HH24:MI:SS') and to_date('2021-03-19 23:59:59', 'YYYY-MM-DD HH24:MI:SS') order by D_TIMESTAMP;

Output:

Note: Its preferable to use SQL Developer to extract DRM application logs from backend schema. Make sure in SQL Developer, you have set the date and time format same as mentioned in above query.

Extracting DRM Job History:

DRM Job history is stored in a table name RM_JOB_HISTORY that consists of following columns:

You can query this table RM_JOB_HISTORY to extract all job run details.

For example, if you want to extract DRM Job History log from 13-Mar-2021 to 19-Mar-2021, run following query:

select * from RM_Job_History where D_TIME_STARTED between to_date('2021-03-13 00:00:01', 'YYYY-MM-DD HH24:MI:SS') and to_date('2021-03-19 23:59:59', 'YYYY-MM-DD HH24:MI:SS') order by D_TIME_STARTED;

Output:

Extracting DRM Request History:

DRM Request history is stored in a table name RM_REQUEST that consists of following columns:

You can query this table RM_REQUEST to extract all change requests submitted from external applications.

select * from RM_REQUEST;

That's all for this post.
I hope this article has helped you.
Your suggestions/feedback are most welcome.
Keep learning and Have a great day!!!