=====Extracting transnational entry from an archivelog file===== 


====Getting the general information====
First we have to location the information for our report:


<sxh>
ggsci$ view report job_name
--------
Last log location read:
     FILE:      /u01/app/oracle/product/12.1.2/ebanking_gg/dirdat/rt000000004   <- Retail file
     SEQNO:     4
     RBA:       117363564    <- Position
     TIMESTAMP: 2018-01-12 02:42:11.997012
     EOF:       NO
     READERR:   0
</sxh>



====Extract the ROW ID====
Once we have the retail file and the position number, we can use the logdump utility, located in the folder as the ggsci utility:

<sxh>

Logdump 65 >open /u01/app/oracle/product/12.1.2/ebanking_gg/dirdat/rt000000004
Current LogTrail is /u01/app/oracle/product/12.1.2/ebanking_gg/dirdat/rt000000004 
Logdump 66 >ghdr on
Logdump 67 >detail on
Logdump 68 >detail data on
Logdump 69 >ggstoken detail
Logdump 70 >pos 117363564
Reading forward from RBA 117363564 
Logdump 71 > n

Hdr-Ind    :     E  (x45)     Partition  :     .  (x0c)  
UndoFlag   :     .  (x00)     BeforeAfter:     B  (x42)  
RecLength  :   357  (x0165)   IO Time    : 2018/01/12 02:42:11.997.012   
IOType     :     3  (x03)     OrigNode   :   255  (xff) 
TransInd   :     .  (x01)     FormatType :     R  (x52) 
SyskeyLen  :     0  (x00)     Incomplete :     .  (x00) 
AuditRBA   :       2551    <--- Archivelog file   AuditPos   : 1477664 
Continued  :     N  (x00)     RecCount   :     1  (x01) 

2018/01/12 02:42:11.997.012 Delete               Len   357 RBA 117364029 
Name: IZOLAPROP.IB_SESSION  (TDR Index: 11) 
Before Image:                                             Partition 12   G  m   
 0000 0a00 0000 0000 0000 0000 45a9 0100 0d00 0000 | ............E.......  
 0900 6e6f 7465 7869 7374 7302 0028 0000 0024 0031 | ..notexists..(...$.1  
 3061 3461 6137 362d 3533 3231 2d34 6630 392d 3866 | 0a4aa76-5321-4f09-8f  
 3434 2d66 3539 3736 3037 6163 3464 3003 000a 00ff | 44-f597607ac4d0.....  
 ff00 0000 0000 0000 0004 000a 00ff ff00 0000 0000 | ....................  
 0000 0005 000a 0000 0000 0000 0000 0000 0006 0022 | ..................."  
 0000 001e 0032 3133 2e31 3635 2e31 3835 2e31 3738 | .....213.165.185.178  
Column     0 (x0000), Len    10 (x000a)  
 0000 0000 0000 0000 45a9                          | ........E.  
Column     1 (x0001), Len    13 (x000d)  
 0000 0900 6e6f 7465 7869 7374 73                  | ....notexists  
Column     2 (x0002), Len    40 (x0028)  
 0000 2400 3130 6134 6161 3736 2d35 3332 312d 3466 | ..$.10a4aa76-5321-4f  
 3039 2d38 6634 342d 6635 3937 3630 3761 6334 6430 | 09-8f44-f597607ac4d0  
Column     3 (x0003), Len    10 (x000a)  
 ffff 0000 0000 0000 0000                          | ..........  
Column     4 (x0004), Len    10 (x000a)  
 ffff 0000 0000 0000 0000                          | ..........  
Column     5 (x0005), Len    10 (x000a)  
 0000 0000 0000 0000 0000                          | ..........  
Column     6 (x0006), Len    34 (x0022)  
 0000 1e00 3231 332e 3136 352e 3138 352e 3137 382c | ....213.165.185.178,  
 2031 302e 3230 302e 3231 2e31 3133                |  10.200.21.113  
Column     7 (x0007), Len     4 (x0004)  
 ffff 0000                                         | ....  
Column     8 (x0008), Len    82 (x0052)  
 0000 4e00 4d6f 7a69 6c6c 612f 352e 3020 2857 696e | ..N.Mozilla/5.0 (Win  
 646f 7773 204e 5420 3130 2e30 3b20 5769 6e36 343b | dows NT 10.0; Win64;  
 2078 3634 3b20 7276 3a35 372e 3029 2047 6563 6b6f |  x64; rv:57.0) Gecko  
 2f32 3031 3030 3130 3120 4669 7265 666f 782f 3537 | /20100101 Firefox/57  
 2e30                                              | .0  
Column     9 (x0009), Len    31 (x001f)  
 0000 3230 3138 2d30 312d 3131 3a31 313a 3039 3a35 | ..2018-01-11:11:09:5  
 392e 3030 3030 3030 3030 30                       | 9.000000000  
Column    10 (x000a), Len    10 (x000a)  
 ffff 0000 0000 0000 0000                          | ..........  
Column    11 (x000b), Len    31 (x001f)  
 ffff 3139 3030 2d30 312d 3031 3a30 303a 3030 3a30 | ..1900-01-01:00:00:0  
 302e 3030 3030 3030 3030 30                       | 0.000000000  
Column    12 (x000c), Len     4 (x0004)  
 ffff 0000                                         | ....  
Column    14 (x000e), Len     4 (x0004)  
 ffff 0000                                         | ....  
Column    15 (x000f), Len     4 (x0004)  
 ffff 0000                                         | ....  
  
GGS tokens: 
TokenID x52 'R' ORAROWID         Info x00  Length   20 
 4141 4157 3661 4141 4841 4141 7153 4641 4142 0001 | **AAAW6aAAHAAAqSFAAB**..  <-Row ID
   
</sxh>


====Extract the archivelog Using LogMiner====

Once we have the row ID and the archivelog file, both described up, we can extract the records from the archivelog file, using the LogMiner from the source database, as described below:

<sxh>
SET MARKUP HTML ON SPOOL ON HEAD "<TITLE>LOGMNR - INFO </title> - <STYLE TYPE='TEXT/CSS'><!--BODY {background: ffffc6} --></STYLE>" 
SET ECHO ON 
REM Spooling to html file 

spool LOGMNR_Info#1.html 
show parameter comp; 
select global_name, sysdate from global_name; 
select * from v$version; 
select * from v$nls_parameters where parameter in ('NLS_LANGUAGE','NLS_TERRITORY', 'NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET'); 
ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'; 
set numformat 9999999999999999999999 
exec dbms_logmnr.add_logfile('fullpatch_to_archivelog'); 
exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog); 
select xidusn || '.' || xidslt || '.' || xidsqn as XID, username, os_username, DATA_OBJ#, rbasqn, RBABLK, rbabyte, RS_ID, row_id, rollback,seg_name,seg_owner,operation, timestamp, sql_redo, scn, cscn from v$logmnr_contents where row_id='AAAW6aAAHAAApYvAAE'; 
EXECUTE DBMS_LOGMNR.END_LOGMNR; 

spool off 

SET MARKUP HTML OFF 
SET ECHO OFF 
</sxh>


You can also use the following select to see the exact archivelog when the change happened. Remember that the archivelogs are keeping data for ALREADY Done changes, therefore, if the redo logs was created at 16:30, then this redo log holds the changes prior 16:30

<sxh bash>
alter session SET NLS_DATE_FORMAT = 'YYYY/MM/DD HH24:MI:SS';
set linesize 400
set pagesize 100
col name format a100
col SEQ format 999,999,999
select NAME
,SEQUENCE# SEQ
,to_date(COMPLETION_TIME, 'YYYY-MM-DD HH24:MI:SS') Applied_date
,APPLIED
from v$archived_log
</sxh>



The output will be something similar:


<sxh bash>
NAME                                                                                                          SEQ APPLIED_DATE        APPLIED
---------------------------------------------------------------------------------------------------- ------------ ------------------- ---------
/db/salesprd/arch/SALESPRD_1_442177_881750047.arc                                                         442,177 2019/11/19 15:10:48 NO
/db/salesprd/arch/SALESPRD_1_442178_881750047.arc                                                         442,178 2019/11/19 15:14:28 NO
/db/salesprd/arch/SALESPRD_1_442179_881750047.arc                                                         442,179 2019/11/19 15:17:40 NO
/db/salesprd/arch/SALESPRD_1_442180_881750047.arc                                                         442,180 2019/11/19 15:18:57 NO
/db/salesprd/arch/SALESPRD_1_442181_881750047.arc                                                         442,181 2019/11/19 15:23:02 NO
/db/salesprd/arch/SALESPRD_1_442182_881750047.arc                                                         442,182 2019/11/19 15:25:10 NO
/db/salesprd/arch/SALESPRD_1_442183_881750047.arc                                                         442,183 2019/11/19 15:27:13 NO
/db/salesprd/arch/SALESPRD_1_442184_881750047.arc                                                         442,184 2019/11/19 15:28:23 NO
/db/salesprd/arch/SALESPRD_1_442185_881750047.arc                                                         442,185 2019/11/19 15:30:02 NO

</sxh>