I am in the process of putting my notes into web pages, please keep checking in later for more contents

top   prev   next  

Install Oracle GoldenGate Software

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



top   prev   next  

database work in sqlplus mygguser     mydg

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;


top   prev   next  

Supplemental Logging

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;


top   prev   next  

Golden Gate Login

# test gguser login
ggsci> dblogin userid mygguser password mygguser_password_01


top   prev   next  

ggsci commands

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  .

Example: HELP ADD REPLICAT

edit params GLOBALS
dirprm/globals.prm



top   prev   next  

edit param and view param

edit param GLOBALS
edit param MGR

view param GLOBALS
view param MGR

view param ext1


top   prev   next  

GLOBALS

edit params GLOBALS
view params GLOBALS
shell cat GLOBALS

GGSCHEMA mygguser


top   prev   next  

MGR or MANAGER

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


top   prev   next  

Change Start Point

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


top   prev   next  

Handle Collisions


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;


top   prev   next  

Start And Stop

stop mgr
start mgr

stop ext1
start ext1
stats ext1

stop extract ext1
start extract ext1


top   prev   next  

info

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


top   prev   next  

stats

stats ext1
stats extract ext1
stats replicat rep1

stats extract ext2 reportrate hr


top   prev   next  

status

STATUS MANAGER
STATUS ER *

STATUS EXTRACT ext1
STATUS REPLICAT rep1

send extract ext1 status
SEND REPLICAT  STATUS


top   prev   next  

view errorlog and view report

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


top   prev   next  

List

List Tables gg*


top   prev   next  

send

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


top   prev   next  

extract.prm

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.*;


top   prev   next  

pump.prm

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.*;


top   prev   next  

replicat.prm

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


top   prev   next  

Performance Tuning

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; 


top   prev   next  

TDE: Encryption

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


top   prev   next  

Turn On Active Dataguard

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


top   prev   next  

logdump

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


top   prev   next  

home  goldengate