goldengate: classic mode ( non-integreted mode ) vs. goldengate integreted mode

integrated capture 

Instead of the Extract process directly capturing the transactions from the database, 
in here, the Logmining Server plays the role. 
A LogMining server will be attached with this Integrated Capture process 
in the database to mine the redo or archive logs and captures the transactions. 
This process mainly works on the streams concepts. 
So, Integrated Capture has the below sub-processes.
READER --- Reads logfiles and splits into regions
PREPARER --- Scans regions of logfiles and pre-filters based on extract parameters
BUILDER --- Merges prepared records in SCN order
CAPTURE --- Formats Logical Change Records (LCRs) and Passes to Oracle GoldenGate Extract

Register with database 
GGSCI> DBLOGIN USER dbuser PASSWORD dbpasswd 
GGSCI> REGISTER EXTRACT ext1 DATABASE
GGSCI> ADD EXTRACT XINT1, INTEGRATED TRANLOG, BEGIN NOW
#Benefit 1: Transparent with RAC.

EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE( -
 grantee    => 'gg_admin',   -
 privilege_type => 'capture', -
 grant_select_privileges=> true, -
 do_grants => TRUE);

# cdb
# DDL INCLUDE MAPPED

# pdb
# DDL INCLUDE MAPPED SOURCECATALOG pdb1 INCLUDE MAPPED SOURCECATALOG pdb2

# INFO EXTRACT ext1
# Log Read Checkpoint  Oracle Integrated Redo Logs 

cd /u01/oracle/goldengate11.2
Source
Download the Goldengate software and unzip in the GG_Home directory
unzip p21158243_1121031_AIX64-5L.zip 	 
unzip p21158243_1121031_SOLARIS64.zip

Target
Untar the GG tar file.
tar -xvof ggs_AIX_ppc_ora11g_64bit.tar
tar -xvof fbo_ggs_Solaris_sparc_ora11g_64bit.tar

export GG_HOME=/u01/oracle/goldengate11.2
export PATH=$ORACLE_HOME/bin:$GG_HOME:$PATH
export LIBPATH=$ORACLE_HOME/lib:$GG_HOME:$LIBPATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$GG_HOME:$LD_LIBRARY_PATH

cat GLOBALS
GGSCHEMA mygguser
USEANSISQLQUOTES
MGRSERVNAME my_src_servername

ggsci
ggsci> CREATE SUBDIRS

REM The following sub-directories can use symbolic links to shared storage.
br
dirchk
dirdat
dirtmp

create tablespace ggsdata  datafile  '/u03/oracle/data/ggsdata01.dbf' size 200m 
AUTOEXTEND ON NEXT 20M MAXSIZE 4001M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M SEGMENT SPACE MANAGEMENT AUTO;

create user mygguser identified by mygguser_password_01  default tablespace myggsdata  temporary tablespace temp;
grant connect,resource to mygguser;
grant select any dictionary, select any table to mygguser;
grant create table to mygguser;
grant flashback any table to mygguser;
grant execute on dbms_flashback to mygguser;
grant execute on utl_file to mygguser;
GRANT SELECT ON V_$DATABASE TO mygguser;
exec dbms_goldengate_auth.grant_admin_privilege('mygguser');  #For 11.2.0.3 and later

ALTER USER mygguser QUOTA UNLIMITED ON ggsdata;

rem at least on target db
grant create any table to mygguser;
grant insert any table to mygguser;
grant update any table to mygguser;
grant delete any table to mygguser;
grant drop any table to mygguser;

rem ddl replication related
sqlplus / as sysdba
@marker_setup.sql
rem Enter Oracle GoldenGate schema name: mygguser
@ddl_setup.sql
rem Enter Oracle GoldenGate schema name:mygguser
@role_setup.sql
rem GRANT GGS_GGSUSER_ROLE TO mygguser
@ddl_enable.sql
@ddl_pin mygguser

REM ALTER SYSTEM SET RECYCLEBIN=OFF 
purge dba_recyclebin;
alter system set recyclebin=off
alter system set recyclebin=off scope=spfile;
select * from dba_recyclebin;

rem enable sequence replication
sqlplus / as sysdba
GRANT DBA TO mygguser;
@sequence.sql --> Will prompt user information: mygguser
rem GRANT EXECUTE on DDLuser.updateSequence TO mygguser;
rem GRANT EXECUTE on DDLuser.replicateSequence TO mygguser;
ALTER TABLE sys.seq$ ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

SELECT supplemental_log_data_min,supplemental_log_data_pk,supplemental_log_data_ui,supplemental_log_data_fk,supplemental_log_data_all , force_logging FROM v$database;

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
alter database add supplemental log data (unique) columns;
alter database add supplemental log data (foreign key) columns;
ALTER DATABASE FORCE LOGGING; 

REM ALTER DATABASE DROP SUPPLEMENTAL LOG DATA ;
REM ALTER DATABASE DROP SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

# test gguser login
ggsci> dblogin userid mygguser password mygguser_password_01

ggsci
ggsci>help

GGSCI Command Summary:

Object:          Command:
SUBDIRS          CREATE
DATASTORE        ALTER, CREATE, DELETE, INFO, REPAIR
ER               INFO, KILL, LAG, SEND, STATUS, START, STATS, STOP
EXTRACT          ADD, ALTER, CLEANUP, DELETE, INFO, KILL,
                 LAG, REGISTER, SEND, START, STATS, STATUS, STOP
                 UNREGISTER
EXTTRAIL         ADD, ALTER, DELETE, INFO
GGSEVT           VIEW
JAGENT           INFO, START, STATUS, STOP
MANAGER          INFO, SEND, START, STOP, STATUS
MARKER           INFO
PARAMS           EDIT, VIEW
REPLICAT         ADD, ALTER, CLEANUP, DELETE, INFO, KILL, LAG, SEND,
                 START, STATS, STATUS, STOP
REPORT           VIEW
RMTTRAIL         ADD, ALTER, DELETE, INFO
TRACETABLE       ADD, DELETE, INFO
TRANDATA         ADD, DELETE, INFO
SCHEMATRANDATA   ADD, DELETE, INFO
CHECKPOINTTABLE  ADD, DELETE, CLEANUP, INFO, UPGRADE

Commands without an object:
(Database)       DBLOGIN, LIST TABLES, ENCRYPT PASSWORD, FLUSH SEQUENCE
                 MININGDBLOGIN
(DDL)            DUMPDDL
(Miscellaneous)  DEFAULTJOURNAL, FC, HELP, HISTORY, INFO ALL, OBEY,
                 SET EDITOR, SHELL, SHOW, VERSIONS, !
                 (note: type the word COMMAND after the ! to display the
                 ! help topic, for example: GGSCI (sys1)> help ! command



For help on a specific command, type HELP  

edit param GLOBALS
edit param MGR

view param GLOBALS
view param MGR

view param ext1

edit params GLOBALS
view params GLOBALS
shell cat GLOBALS

GGSCHEMA mygguser

edit param mgr
shel more ./dirprm/mgr.prm

EDIT PARAMS MGR
VIEW PARAMS MGR
INFO MANAGER 
info manager
info mgr
info mgr detail
status MANAGER

eg:
PORT 7809
USERID mygguser, PASSWORD mygguser_password_01
PURGEOLDEXTRACTS /u01/oracle/goldengate/dirdat/ex, USECHECKPOINTS
# AUTOSTART

1): change start point

select STANDBY_BECAME_PRIMARY_SCN from v$database;

column current_scn format 999999999999
select current_SCN from v$database;

ALTER EXTRACT ext1, scn 1470345
ALTER EXTRACT dpump1, scn 1470345

START REPLICAT rep1, ATCSN 1470345
START REPLICAT rep1 skiptransaction

source: alter extract ext1, EXTSEQNO 454 EXTRBA 4356859
target: alter replicat rep1, EXTSEQNO 19 EXTRBA 0

REPERROR (1403, Discard) 
REPERROR (0001, Discard)

# 1): skip one error transaction
START REPLICAT SKIPTRANSACTION

# 2): 
SEND REPLICAT rep_trg, HANDLECOLLISIONS

# 3): 
# logdump
logdump
logdump>open trail_file
logdump>pos<>
logdump>n
# after you get the next rba, run the following command
ALTER REPLICAT rep_trg extseqno <> extrba X
HANDLECOLLISIONS MAP src.tbl_t1, TARGET hr.tbl_t1;

stop mgr
start mgr

stop ext1
start ext1
stats ext1

stop extract ext1
start extract ext1

info all
info mgr
info mgr detail
INFO MANAGER

Info CheckpointTable
info checkpointtable GGS_OWNER.CHKPTAB
info checkpointtable mygguser.CHECKPOINTTABLE

INFO EXTRACT ext1
info extract ext1, detail
info ext1, detail

info extract dpump
info dpump
info dpump, detail

info rep1, detail

INFO REPLICAT group

stats ext1
stats extract ext1
stats replicat rep1

stats extract ext2 reportrate hr

STATUS MANAGER
STATUS ER *

STATUS EXTRACT ext1
STATUS REPLICAT rep1

send extract ext1 status
SEND REPLICAT  STATUS

VIEW GGSEVT  : Contents of the Oracle GoldenGate error log
view report mgr
VIEW REPORT ext1
view report rep1
VIEW REPORT group

SEND EXTRACT ext1, SHOWTRANS
SEND EXTRACT ext1 REPORT
SEND REPLICAT  group REPORT

List Tables gg*

SEND REPLICAT rep1 STATUS

SEND EXTRACT group, SHOWTRANS
SEND EXTRACT ext1 REPORT
SEND REPLICAT  group REPORT

send manager childstatus

send extract ext1, getlag
lag extract ext*
lag replicat rep1

# send a command, not report
SEND REPLICAT rep1 NOHANDLECOLLISIONS

1): command
# Create the Extract process
ADD EXTRACT ext1, TRANLOG, BEGIN NOW
ADD extract ext1, tranlog, scn 
ADD EXTTRAIL /u01/oracle/goldengate/dirdat/et, EXTRACT ext1
EDIT PARAMS ext1

2):parameter file
SHOW PARAMETER NLS_LANGUAGE
SHOW PARAMETER NLS_TERRITORY
SELECT name, value$ from SYS.PROPS$ WHERE name = 'NLS_CHARACTERSET';
SHOW PARAMETER NLS_LENGTH_SEMANTICS

1):
EXTRACT ext1
USERID mygguser, PASSWORD mygguser_password_01
EXTTRAIL /u98/oracle/mydg/goldengate/dirdat/et
SETENV (ORACLE_SID=mydg)
DDL INCLUDE MAPPED 
DISCARDFILE /u98/oracle/mydg/goldengate/dirtmp/ext1.dsc, APPEND
REPORTCOUNT EVERY 1 MINUTES, RATE
TRANLOGOPTIONS ARCHIVEDLOGONLY
TRANLOGOPTIONS ALTARCHIVELOGDEST PRIMARY /u99/oracle/mydg/archives
TRANLOGOPTIONS ALTARCHIVEDLOGFORMAT mydgarch_%t_%s_%r.arc
TRANLOGOPTIONS COMPLETEARCHIVEDLOGONLY
FETCHOPTIONS, NOUSESNAPSHOT, NOUSELATESTVERSION, MISSINGROW REPORT
STATOPTIONS REPORTFETCH
TABLE MYPRDSCHEMA.*;

1): command
eg1:
ADD EXTRACT dpump, EXTTRAILSOURCE /u01/oracle/goldengate/dirdat/et
ADD RMTTRAIL /u01/oracle/goldengate/dirdat/rt, EXTRACT dpump

1):
EXTRACT dpump
USERID mygguser, PASSWORD mygguser_password_01
RMTHOST 10.10.1.2, MGRPORT 7809
RMTTRAIL /u98/oracle/mydg/goldengate/dirdat/rt
PASSTHRU
TABLE MYPRDSCEHMA.*;

1): Commands
ADD CHECKPOINTTABLE gguser.CHECKPOINT
ADD REPLICAT rep1, EXTTRAIL dirdat/rt, CHECKPOINTTABLE gguser.GG_CHKPT

rep1.prm
REPLICAT rep1
ASSUMETARGETDEFS
USERID mygguser, PASSWORD AACAAAAAAAAAAAKATIQHAFNFAJAEDBOBTCEBHHNDRGYHPHYA, BLOWFISH, ENCRYPTKEY DEFAULT
DDLOPTIONS REPORT
-- DDLERROR DEFAULT IGNORE RETRYOP
DDLERROR 1917 IGNORE
-- ignore ddl for duplicated  index name
DDLERROR 1927 IGNORE
-- ignore error for REVOKE privileges you did not grant
DDLERROR 955 IGNORE
-- ignore ddl for CREATE OR REPLACE PROCEDURE compilation error
DDLERROR 24344 IGNORE
-- DDLERROR 942 IGNORE
DDLERROR DEFAULT ABEND
HANDLECOLLISIONS
APPLYNOOPUPDATES
DBOPTIONS Suppresstriggers;
DBOPTIONS DEFERREFCONST
DISCARDFILE /u01/oracle/goldengate11.2/dirrpt/rep1.dsc, append
MAP  MYPRDSCHEMA.*, TARGET MYPRDSCHEMA.*;
TABLEEXCLUDE MYPRDSCHEMA.MYTABLE

Performance testing also confirmed that the logmining server runs faster when 
_log_buffer_size
 is set to 128 (the default is 8) when 
the source database runs in a system 
with high redo volume. To set this parameter, use the following command.
SQL> alter system set _log_buffer_size=128; 

TDE: Encryption
@?/app/oracle/product/orcl111/rdbms/admin/prvtclkm.plb
GRANT EXECUTE ON DBMS_INTERNAL_CLKM TO psmith;

rem shutdown immediate;
rem startup mount;
rem alter database recover managed standby database disconnect from session;

alter database recover managed standby database cancel;
alter database open; 
alter database recover managed standby database disconnect from session;

SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY WITH APPLY

Before we can see the contents of the file, we must setup a view in LOGDUMP. The following table of commands will provide the necessary detail depending on your requirements:

Description
FILEHEADER [on | off | detail]  Controls whether or not the trail file header is displayed and how much detail.

GHDR [on | off] Controls whether or not the record header is displayed with each record.

DETAIL [on | off | data] Displays a list of columns that includes the column ID, length, plus values in hex and ASCII. DATA adds hex and ASCII data values to the column list.

USERTOKEN [detail] Displays the actual token data.

RECLEN [<# of bytes>] Controls how much of the record data is displayed in characters
So, working through the list, enable the file header detail, GDHR, user token detail and record length options.

Logdump 3 >fileheader detail
Logdump 4 >ghdr on
Logdump 6 >detail on
Logdump 7 >usertoken detail
Logdump 8 >reclen 128
Reclen set to 128
.. ..............................................................................................................................

logdump:
get sql statement using logdump
/*
configure below setting on logdump
fileheader detail
ghdr on
detail on
usertoken detail
reclen 128
*/

cd $GG_DIR/bin
logdump
logdump> open app/oracle/gg/trail/test/dirdat/pt0001
logdump> pos 100000
logdump> fileheader detail
logdump> ghdr on
logdump> detail on
logdump> usertoken detail
logdump> reclen 128
logdump> n