How do I use LogMiner to pull data from an Oracle database?

This topic describes how to use LogMiner to pull incremental data from an Oracle database.

Scenarios

Oracle Store collects incremental data by using the LogMiner tool that comes with Oracle Database. When a data quality issue occurs due to unknown reasons, you need to check whether the issue is caused by LogMiner. You need to manually obtains logs from Oracle Server for troubleshooting.

Pull the log file with the specified ROWID

Query the corresponding archive log files

1. Query the last system change number (SCN) of the record based on the ROWID. The returned SCN is the SCN of the last change on the corresponding block, and therefore, may be inaccurate. If you can obtain a more accurate change time from the business data, we recommend that you use this change time.

   alter session set nls_timestamp_format='YYYY-MM-DD HH24:MI:SS';
   select ora_rowscn from table_name where rowid= 'xxxxxxxxx';
   select scn_to_timestamp(ora_rowscn)from table_name where rowid= 'xxxxxxxxx';
   

2. Query the archive that the specified time or SCN belongs to. If the Oracle database is a Real Application Clusters (RAC) environment, you may obtain multiple records by using the following SQL statements. You need to analyze each of these archive files. If the query result is empty, the archive logs have been deleted and cannot be analyzed.

   Query the archive by time. For example, assume that the time is 2022-06-10 14:59:00:

   SELECT THREAD#, NAME FROM V$ARCHIVED_LOG WHERE NAME IS NOT NULL AND STATUS = 'A'
   AND DELETED = 'NO' AND ARCHIVED = 'YES' AND STANDBY_DEST = 'NO'  
   AND FIRST_TIME <= TO_TIMESTAMP('2022-06-10 14:59:00','yyyy-MM-dd hh24:mi:ss')
   AND NEXT_TIME > TO_TIMESTAMP('2022-06-10 14:59:00','yyyy-MM-dd hh24:mi:ss');
   

   Query the archive by SCN. For example, assume that the SCN is 2106626:

   SELECT  THREAD#,NAME FROM V$ARCHIVED_LOG WHERE NAME IS NOT NULL AND STATUS = 'A' AND DELETED = 'NO' AND ARCHIVED = 'YES' AND STANDBY_DEST = 'NO'  
   AND FIRST_CHANGE# <= 2106626 AND NEXT_CHANGE# > 2106626;
   

Use LogMiner to analyze archive logs

1. Use LogMiner to analyze the archive logs. Replace the log file names in the following SQL statements with the names obtained in the preceding steps, and execute the SQL statements to analyze the logs.

   The primary and standby Active Data Guard (ADG) databases use different SQL statement syntax. To obtain more valid information in a query, you can analyze multiple groups of archive files at a time. We recommend that you analyze five groups of log files at a time.

   • Primary ADG database:

     begin
     sys.dbms_logmnr.add_logfile(logfilename=>'/oracle/archivedlog/filename1.arc',options=>sys.dbms_logmnr.new);
     sys.dbms_logmnr.add_logfile(logfilename=>'/oracle/archivedlog/filename2.arc',options=>sys.dbms_logmnr.addfile);
     sys.dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
     end;
     /
     

   • Standby ADG database:

     begin
     sys.dbms_logmnr.add_logfile(logfilename=>'/oracle/archivedlog/filename1.arc',options=>sys.dbms_logmnr.NEW);
     sys.dbms_logmnr.add_logfile(logfilename=>'/oracle/archivedlog/filename2.arc',options=>sys.dbms_logmnr.addfile);
     sys.dbms_logmnr.start_logmnr();
     end;
     /
     

2. Query the transaction ID in the result returned by LogMiner based on the ROWID. If multiple transaction IDs exist, repeat Step 3 for each transaction ID to find out the complete change process of the ROWID.

   select XID from V$LOGMNR_CONTENTS where ROW_ID = 'AAASauAAEAAAAKGAAA'
   order by SCN desc;
   

3. Query the complete change process of the transaction based on the transaction IDs identified in Step 2. If multiple results are found in the corresponding archive log file, use LogMiner to analyze each result until you obtain the change record of the corresponding ROWID.

   select SCN, RBASQN, OPERATION, TABLE_NAME, SEG_OWNER,  ROLLBACK, SEG_NAME,
   SEG_TYPE, TIMESTAMP, XID, ROW_ID, SQL_REDO from V$LOGMNR_CONTENTS
   where XID = 'xxxxxxxxxxxx';
   

   If the preceding SQL query returns too much information, you can select only some important information for troubleshooting and gradually increase the information to be queried when necessary. Example:

   select OPERATION,TABLE_NAME, ROW_ID, ROLLBACK from V$LOGMNR_CONTENTS
   where XID = 'xxxxxxxxxxxx';
   

Query the large transaction table in the archive files

1. Use LogMiner to analyze the archive files as described above.

2. Find the IDs of large transactions in the archive files. You can use the following SQL statement to sort the transactions in descending order of the number of transaction records. The top ones are large transactions.

   select XID, count(*) from V$LOGMNR_CONTENTS group by XID order by 2 desc;
   

3. Find the table with the most records based on the IDs of the large transactions.

   select SEG_OWNER, SEG_NAME, COUNT(*) from V$LOGMNR_CONTENTS where XID='xxxx' group by SEG_OWNER, SEG_NAME order by 3 desc;