Extracting transnational entry from an archivelog file
Getting the general information
First we have to location the information for our report:
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
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:
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
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:
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
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
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
The output will be something similar:
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