Wednesday, June 26, 2013

Find Transaction SQL from RBA

Links to this post
logdump> open 
logdump> GHDR ON
logdump> DETAIL DATA
logdump> HEADERTOKEN
logdump> GGSTOKEN detail
logdump> pos  
TokenID x47 'G' Record Header    Info x01  Length 1180
TokenID x48 'H' GHDR             Info x00  Length   55
 4504 0041 0417 0fff 02f2 052c 5ca7 4670 0000 0000 | E..A.......,\.Fp....
 05c2 b45c 0001 83f6 0352 0000 0001 4e53 5044 4241 | ...\.....R....dummy
 2e4e 5350 5f49 4e56 454e 544f 5259 00             | .DUMMY_INVENTORY.
TokenID x44 'D' Data             Info x00  Length 1047
TokenID x54 'T' GGS Tokens       Info x00  Length   58
TokenID x5a 'Z' Record Trailer   Info x01  Length 1180
___________________________________________________________________
Hdr-Ind    :     E  (x45)     Partition  :     .  (x04)
UndoFlag   :     .  (x00)     BeforeAfter:     A  (x41)
RecLength  :  1047  (x0417)   IO Time    : 2013/06/12 14:08:51.000.944
IOType     :    15  (x0f)     OrigNode   :   255  (xff)
TransInd   :     .  (x03)     FormatType :     R  (x52)
SyskeyLen  :     0  (x00)     Incomplete :     .  (x00)
---------------------------------------------------------------
---------------------------------------------------------------
--AuditRBA is Redo sequence Number , AuditPos is SCN
AuditRBA   :      99318       AuditPos   : 96646236   
---------------------------------------------------------------
---------------------------------------------------------------
Continued  :     N  (x00)     RecCount   :     1  (x01)

2013/06/12 14:08:51.000.944 FieldComp            Len  1047 RBA 433498317
Name: DUMMYDBA.DUMMY_INVENTORY
After  Image:                                             Partition 4   G  s
 0000 000e 0000 000a 4d37 3266 3833 6464 3034 0001 | ........M72f83dd04..
 0003 0000 4e00 0200 0c00 0000 0847 5244 4e43 4130 | ....N........GRDNCA0
 3100 0300 0900 0000 0531 3256 5450 0004 0009 0000 | 1........12VTP......
 0005 3131 5437 5600 0500 0a00 0000 0633 3130 3231 | ..11T7V........31021
 3700 0600 0a00 0000 0634 3130 3630 3600 0700 0300 | 7........410606.....
 004e 0008 0003 0000 4e00 0900 0300 004e 000a 0008 | .N......N......N....
 0000 0004 4654 544e 000b 0015 ffff 3139 3030 2d30 | ....FTTN......1900-0
Column     0 (x0000), Len    14 (x000e)
 0000 000a 4d37 3266 3833 6464 3034                | ....M72f83dd04
Column     1 (x0001), Len     3 (x0003)
 0000 4e                                           | ..N
Column     2 (x0002), Len    12 (x000c)
 0000 0008 4752 444e 4341 3031                     | ....GRDNCA01
Column     3 (x0003), Len     9 (x0009)
 0000 0005 3132 5654 50                            | ....12VTP
Column     4 (x0004), Len     9 (x0009)
 0000 0005 3131 5437 56                            | ....11T7V
Column     5 (x0005), Len    10 (x000a)
 0000 0006 3331 3032 3137                          | ....310217
Column     6 (x0006), Len    10 (x000a)
 0000 0006 3431 3036 3036                          | ....410606
Column    57 (x0039), Len     4 (x0004)
 ffff 3139 3030 2d30 312d 3031 3a30 303a 3030 3a30 | ..1900-01-01:00:00:0
 30                                                | 0
Column    78 (x004e), Len    21 (x0015)
 ffff 3139 3030 2d30 312d 3031 3a30 303a 3030 3a30 | ..1900-01-01:00:00:0
 30                                                | 0
Column    79 (x004f), Len     4 (x0004)
 ffff 0000                                         | ....
Column    80 (x0050), Len     5 (x0005)
 0000 0001 59                                      | ....Y

GGS tokens:
TokenID x52 'R' ORAROWID         Info x00  Length   20
 4141 4338 6c39 4143 6d41 4141 7a68 3141 4148 0001 | AAC8l9ACmAAAzh1AAH..
TokenID x4c 'L' LOGCSN           Info x00  Length   14
 3133 3332 3539 3536 3332 3432 3337                | 13325956324237
------XID Transactions. 
TokenID x36 '6' TRANID           Info x00  Length   12
 3332 2e32 382e 3831 3139 3133                     | 32.28.811913 


Transaction XID will be at end of Logdump record format.


This way we can find XID,redo log sequence and SCN of Record.

Now how to find Original transaction.

Go to source database.

Find Location of Archivelog sequence which is in AuditRBA in our case it is 99318.

spool output.txt
ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
set numformat 9999999999999999999999
set long 999999
set lines 190
set pages 555
exec dbms_logmnr.add_logfile(' complete path of arc seq 99318 '); 
exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
select 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 xidusn || '.' || xidslt || '.' || xidsqn='32.28.811913';
EXECUTE DBMS_LOGMNR.END_LOGMNR;
spool off;

Wednesday, June 19, 2013

Goldengate User Creation.

Links to this post
This is SQL script to Create GG Admin User. Script Run DBMS_PACKAGE and grants according to Oracle database Version.

It also created Dedicated Tablespace for Goldengate User.

prompt "Enter Goldengate Tablespace Name"
def GGATE_ADMIN_TABLESPACE_NAME=&1
prompt "Enter Goldengate Tablespace Location"
def GG_ADMIN_TABLESPACE_LOCATION=&2
prompt "Enter Goldengate Admin UserName"
def GOLDENGATE_ADMIN_USER=&3
prompt "Enter Goldengate username password"
def GOLDENGATE_ADMIN_USER_PWD=&4
set serveroutput on;
prompt "-------------------------------------------------------------------------"
prompt "This script first drop Old tablespace and Goldengate user if same name exist"
prompt "Provide DISKGROUP NAME LIKE +DATA if Tablespace location is on ASM  "
PROMPT "OR"
prompt "provide filesystem with datafile name like /u01/app/datafile/GGADMIN.dbf"
prompt "-------------------------------------------------------------------------"
spool GG_ADMIN_USER_CREATION.LOG
prompt "Goldengate User must have its own Tablespace it can not be shared with"
Prompt "shared with any other user or ddl_setup script will fail"
--prompt "=======first read above instruction clearly============"
prompt "-------------------------------------------------------------------------"
prompt "------------------------Disable DDL--------------------------------------"
@ddl_disable.sql
SET VERIFY OFF;
prompt "-----------------------Droping any existing Tablespace-------------------"
DROP TABLESPACE &GGATE_ADMIN_TABLESPACE_NAME INCLUDING CONTENTS AND DATAFILES;
prompt "-----------------------Droping any existing User-------------------------"
DROP USER &GOLDENGATE_ADMIN_USER CASCADE;
Prompt "-----------------------creating new Tablespace --------------------------"
CREATE TABLESPACE &GGATE_ADMIN_TABLESPACE_NAME  datafile '&GG_ADMIN_TABLESPACE_LOCATION' SIZE 1500M autoextend on;
--PROMPT "ENTER PASSWORD FOR NEW GOLDENGATE ADMIN USER"
prompt "-----------------------creating new Goldengate Admin User----------------"
CREATE USER &GOLDENGATE_ADMIN_USER IDENTIFIED BY &GOLDENGATE_ADMIN_USER_PWD DEFAULT TABLESPACE &GGATE_ADMIN_TABLESPACE_NAME TEMPORARY TABLESPACE TEMP;
GRANT CONNECT,alter session,CREATE SESSION,RESOURCE,CREATE TABLE TO &GOLDENGATE_ADMIN_USER;
GRANT FLASHBACK ANY TABLE TO &GOLDENGATE_ADMIN_USER;
grant SELECT ANY TRANSACTION to &GOLDENGATE_ADMIN_USER;
grant SELECT ANY TABLE to &GOLDENGATE_ADMIN_USER;
grant SELECT ANY DICTIONARY to &GOLDENGATE_ADMIN_USER;
grant CREATE TABLE to &GOLDENGATE_ADMIN_USER;
grant ALTER SESSION to &GOLDENGATE_ADMIN_USER;
grant UNLIMITED TABLESPACE  to &GOLDENGATE_ADMIN_USER;
grant EXECUTE ON utl_file to &GOLDENGATE_ADMIN_USER;
grant select on v_$instance to &GOLDENGATE_ADMIN_USER;
grant select on v_$database to &GOLDENGATE_ADMIN_USER;
grant LOCK ANY TABLE to &GOLDENGATE_ADMIN_USER;
PROMPT "----------DBA PRIVILEGES WILL BE GRANTED TO GOLDENGATE_ADMIN_USER--------"
GRANT DBA TO &GOLDENGATE_ADMIN_USER;
Prompt "-------------------------------------------------------------------------"
Prompt "-------------------------------------------------------------------------"
Prompt " Running PLSQL BLOCK to execute procedure and grants according to Oracle version"
Prompt "-------------------------------------------------------------------------"
Prompt "-------------------------------------------------------------------------"
Prompt
Prompt


declare
v1 VARCHAR2(200);
v2 varchar2(200);
V3 varchar2(1000);
l_sql varchar2(1000);
begin
select version into v1 from DBA_REGISTRY WHERE COMP_NAME LIKE '%Catalog Views%';
 CASE
      WHEN v1 like '10.2%' THEN
DBMS_OUTPUT.PUT_LINE('it is Oracle version 10.2');
execute immediate 'BEGIN dbms_streams_auth.grant_admin_privilege(''&GOLDENGATE_ADMIN_USER''); END;';
EXECUTE IMMEDIATE 'grant insert on system.logmnr_restart_ckpt$ to &GOLDENGATE_ADMIN_USER';
EXECUTE IMMEDIATE 'grant update on sys.streams$_capture_process to &GOLDENGATE_ADMIN_USER';
EXECUTE IMMEDIATE 'grant become user to &GOLDENGATE_ADMIN_USER';
dbms_output.put_line('Executing dbms_streams_auth.grant_admin_privilege');
WHEN v1 like '11.1%' THEN
DBMS_OUTPUT.PUT_LINE('it is Oracle version 11.1');
execute immediate 'BEGIN dbms_streams_auth.grant_admin_privilege(''&GOLDENGATE_ADMIN_USER''); END;';
EXECUTE IMMEDIATE 'grant become user to &GOLDENGATE_ADMIN_USER';
dbms_output.put_line('Executing dbms_streams_auth.grant_admin_privilege');
WHEN v1 like '11.2.0.3%' THEN
DBMS_OUTPUT.PUT_LINE('it is Oracle version 11.2.0.3');
EXECUTE IMMEDIATE 'begin dbms_goldengate_auth.grant_admin_privilege(''&GOLDENGATE_ADMIN_USER''); END;';
dbms_output.put_line('Executing dbms_goldengate_auth.grant_admin_privilege');
    END CASE;
exception when others
then
dbms_output.put_line('ERROR IS ' || SQLCODE || SQLERRM);
end;
/
prompt "---------------------script end-------------------------------------------"
prompt
prompt
prompt "~~~~~~~~please verify GG_ADMIN_USER_CREATION.LOG for Errors ~~~~~~~~~~~~~~"
prompt
prompt
prompt
spool OFF;

Friday, June 7, 2013

OGG-01930 Errors In Datastore

Links to this post
Few days back we got below error on few instance of GG.

2013-06-07 01:25:06 WARNING OGG-01930 Oracle GoldenGate Delivery for Oracle, dummy1.prm: Datastore error in 'dirbdb': BDB0113 Thread/process 17080/1125251392 failed: BDB1507 Thread died in Berkeley DB library.


Later found that one of the Teammate is trying to configure Monitoring using OEM plugin for OGG.

If your version of GoldenGate from Version 11.2.0.0.0 to 11.2.1.0.4 then You must upgrade till OGG 11.2.1.0.5_02 and re-create datastore.

you may found more reference document on.
GoldenGate Monitor: BDB0060 BDB1581 BDB1582 BDB1507 BDB0118 BDB2027 BDB0113 BDB0087 Errors In Datastore [ID 1495998.1]