************************************************************************
* Description: Oracle GoldenGate supplemental LogMiner with table ROWID
* Date: 10:32 AM EST, 01/07/2021                         
************************************************************************

		 
<1> Oracle GoldenGate with Supplemental Log:
     |
     |__ o. When configuring Oracle GoldenGate, supplemental logging is required to be enabled in source database.
            Therefore, the question is what is Supplemental Logging?

                 



				 
<2> Followed question is why supplemental log is needed, and what are the purposes:

                 



				 
<3> Then, why software such as GoldenGate/DataGuard based on REDO stream for standby SQL reconstrucated cannot rely on ROWID only:
     |
     |__ o. To resolve above question, firstly is understanding what is ROWID Pseudocolumn:
     |
     |__ o. Secondly, what ROWID looks like, will it be changed after being applied in target/standby DB so cannot depend on it?
            To find answer for above questions, we need to see the content within each REDO log, since it is the media to transfer the changes between primary and standby DB.

                 




<4> Oracle Log Mining:
     |
     |__ SQL> create directory UTL_FILE_DIR as '/u01/emeralit/lmtest';
     |
     |__ SQL> BEGIN
     |             DBMS_LOGMNR_D.build (
     |                dictionary_filename => 'lmtest.ora',
     |                dictionary_location => 'UTL_FILE_DIR'
     |             );
     |        END;
     |        /
     |
     |
     |
     |
     |__ SQL> BEGIN
     |              DBMS_LOGMNR.add_logfile (
     |                  options     => DBMS_LOGMNR.new,
     |                  logfilename => '/u01/app/oracle/fast_recovery_area/EMERALIT/archivelog/2021_01_06/o1_mf_1_8_hzdf5256_.arc'
     |              );
     |          END;
     |          /
     |
     |		  
     |		  
     |		  
     |__ SQL> BEGIN
     |             DBMS_LOGMNR.start_logmnr (
     |                  dictfilename => '/u01/emeralit/lmtest/lmtest.ora'
     |             );
     |        END;
     |        /		  
     |
     |  
     |  
     |
     |__ SQL> SELECT operation, sql_redo, sql_undo FROM v$logmnr_contents where TABLE_NAME='STORES'; 		  

             
			  
			  

			  
			  
<5> Compare the ROWID between Source & Target DB:
     |
     |__ SQL> select store_id, rowid from stores where store_id=38;
     |
     |               STORE_ID ROWID
     |             ---------- ------------------
     |                     38 AAAR38AAHAAAAFcAAK
     | 
     |__ o. As compared, the ROWID got changed when this UPDATE got applied on target DB via GoldenGate. 
            So, when a row got transferred to Target DB, the ROWID will be changed. That is the reason why supplemental log is needed for storing additional info.


	 


	  
Reference:
     |
     |__ o. Oracle Supplemental Log - https://docs.oracle.com/database/121/SUTIL/GUID-D857AF96-AC24-4CA1-B620-8EA3DF30D72E.htm#SUTIL1582
     |
     |__ o. Oracle Log Mining - https://oracle-base.com/articles/8i/logminer
     |
     |__ o. Oracle 19c Log Mining - https://docs.oracle.com/en/database/oracle/oracle-database/19/sutil/oracle-logminer-utility.html#GUID-3417B738-374C-4EE3-B15C-3A66E01AE2B5