Golden Gate Configuration Steps :-
1. Golden Gate Setup at Source
and Target
2. Configure Source :-
A. Configure Schema
B. Configure Manager
C. Configure Extract
D. Add Trandata
E. Configure Pump
3. Configure Target
A. Configure Manager
B. Create Check point table
C. Configure Replicat
Database And Machine Info
SOURCE Database
|
|
Oracle Release:
|
Oracle11g Release 2 – (11.2.0.1.0)
|
Machine Name:
|
VM1
|
Operating System:
|
Red Hat Linux 5
|
Oracle SID:
|
SRC
|
Replication Schema
|
GGS_OWNER
|
TARGET Database
|
|
Oracle Release:
|
Oracle11g Release 2 – (11.2.0.1.0)
|
Machine Name:
|
VM2
|
Operating System:
|
Red Hat Linux 5
|
Oracle SID:
|
TARGET
|
Replication Schema
|
GGS_OWNER
|
¨ Golden Gate Setup at Source and
Target
1. mkdir
/u01/soft/
1. $unzip
ogg112101_fbo_ggs_Linux_x86_ora11g_32bit.zip
Archive:
ogg112101_fbo_ggs_Linux_x86_ora11g_32bit.zip
inflating:
fbo_ggs_Linux_x86_ora11g_32bit.tar
1. $ tar -xf
fbo_ggs_Linux_x86_ora11g_32bit.tar
1. export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/oradata/gg
1. [oracle@VM1 soft]$ ls
bcpfmt.tpl
ddl_ora10upCommon.sql
defgen freeBSD.txt
libxml2.txt role_setup.sql
bcrypt.txt
ddl_ora11.sql demo_more_ora_create.sql
ggcmd
logdump sequence.sql
cfg
ddl_ora9.sql demo_more_ora_insert.sql ggMessage.dat marker_remove.sql server
chkpt_ora_create.sql ddl_pin.sql demo_ora_create.sql
ggsci marker_setup.sql sqlldr.tpl
cobgen ddl_purgeRecyclebin.sql demo_ora_insert.sql help.txt marker_status.sql tcperrs
convchk ddl_remove.sql demo_ora_lob_create.sql jagent.sh
mgr
ucharset.h
db2cntl.tpl
ddl_session1.sql demo_ora_misc.sql
keygen notices.txt ulg.sql
ddl_cleartrace.sql ddl_session.sql demo_ora_pk_befores_create.sql
libantlr3c.so
oggerr
UserExitExamples
ddlcob ddl_setup.sql demo_ora_pk_befores_insert.sql
libdb-5.2.so params.sql
usrdecs.h
ddl_ddl2file.sql ddl_status.sql demo_ora_pk_befores_updates.sql libgglog.so prvtclkm.plb zlib.txt
ddl_disable.sql ddl_staymetadata_off.sql
dirjar libggrepo.so pw_agent_util.sh
ddl_enable.sql ddl_staymetadata_on.sql
dirprm
libicudata.so.38 remove_seq.sql
ddl_filter.sql ddl_tracelevel.sql
emsclnt
libicui18n.so.38
replicat
ddl_nopurgeRecyclebin.sql ddl_trace_off.sql
extract libicuuc.so.38 retrace
ddl_ora10.sql ddl_trace_on.sql
fbo_ggs_Linux_x86_ora11g_32bit.tar libxerces-c.so.28 reverse
1. [oracle@VM1 soft]$ ggsci
Oracle GoldenGate Command
Interpreter for Oracle
Version 11.2.1.0.1
OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x86, 32bit
(optimized), Oracle 11g on Apr 23 2012 08:09:25
Copyright (C) 1995, 2012,
Oracle and/or its affiliates. All rights reserved.
— YOU WILL GET GG COMMAND PROMPT
1. GGSCI (VM1) 1> create
subdirs
Creating subdirectories
under current directory /u01/soft
Parameter
files
/u01/soft/dirprm: already exists
Report
files
/u01/soft/dirrpt: created
Checkpoint
files
/u01/soft/dirchk: created
Process status
files
/u01/soft/dirpcs: created
SQL script
files
/u01/soft/dirsql: created
Database definitions
files /u01/soft/dirdef: created
Extract data
files
/u01/soft/dirdat: created
Temporary
files
/u01/soft/dirtmp: created
Stdout
files
/u01/soft/dirout: created
1. Issue the following command
to exit GGSCI.
GGSCI (VM1) 2> exit
§ Configure Schema
§ create tablespace
§ create user
§ Give Grants
§ grant connect, resource to
ggs_owner;
§ grant
select any dictionary, select any table to ggs_owner;
§ grant
create table to ggs_owner;
§ grant
flashback any table to ggs_owner;
§ grant
execute on dbms_flashback to ggs_owner;
§ grant
execute on utl_file to ggs_owner;
§ grant
create any table to ggs_owner;
§ grant
insert any table to ggs_owner;
§ grant
update any table to ggs_owner;
§ grant
delete any table to ggs_owner;
§ grant
drop any table to ggs_owner;
§ Change Parameter as per
requirement
§ UNDO_MANAGEMENT=AUTO
§ UNDO_RETENTION=86400
Script :
SQL> startup
ORACLE instance started.
Total System Global
Area 422670336 bytes
Fixed
Size
1336960 bytes
Variable
Size
247466368 bytes
Database
Buffers 167772160 bytes
Redo
Buffers
6094848 bytes
Database mounted.
Database opened.
SQL>
SQL> alter system set
recyclebin=off scope=spfile;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global
Area 422670336 bytes
Fixed Size
1336960
bytes
Variable
Size
247466368 bytes
Database
Buffers 167772160 bytes
Redo
Buffers
6094848 bytes
SQL> alter database
archivelog;
Database altered.
SQL> alter database
Open;
Database altered.
SQL> create tablespace
ggs_data datafile ‘/u01/<DBNAME>ggs_data01.dbf’ size 200m;
tablespace created
SQL> create user
ggs_owner identified by ggs_owner default tablespace ggs_data temporary
tablespace temp;
User created.
SQL> grant connect,
resource to ggs_owner;
grant select any
dictionary, select any table to ggs_owner;
grant create table to
ggs_owner;
grant flashback any table
to ggs_owner;
grant execute on
dbms_flashback to ggs_owner;
grant execute on utl_file
to ggs_owner;
grant create any table to
ggs_owner;
grant insert any table to
ggs_owner;
grant update any table to
ggs_owner;
grant delete any table to ggs_owner;
grant drop any table to
ggs_owner;
grant drop any table to
ggs_owner;
SQL> show parameter undo
NAME
TYPE VALUE
———————————— ———–
——————————
undo_management
string AUTO
undo_retention
integer 900
undo_tablespace
string UNDOTBS1
SQL> alter system set
UNDO_RETENTION=86400 scope=both;
System altered.
sql> alter database add
supplemental log data;
Database altered.
— bounce the database
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global
Area 422670336 bytes
Fixed
Size
1336960 bytes
Variable
Size
251660672 bytes
Database
Buffers 163577856 bytes
Redo
Buffers
6094848 bytes
Database mounted.
Database opened.
INITIAL DATALOAD :-
EXPORT: @ SOURCE
$
expdp directory=db_dir dumpfile=schema_gg.dmp logfile=schema_gg.logschemas=ggtest Scp
from SOURCE and TARGET
$scp
–p schema_gg.dmp 172.168.10.108:/oradata
IMPORT: @ TARGET
$impdp
directory=db_dir dumpfile=schema_gg.dmp logfile=schema_imp_gg.logschemas=ggtest
Execute following script at Source by connecting SYS user which
will create required object for golden gate.
Run scripts for creating
all necessary objects for support DDL replication
[oracle@VM1 soft]$ sqlplus
/ as sysdba
SQL*Plus: Release
11.2.0.1.0 Production on Fri Apr 26 05:42:29 2013
Copyright (c) 1982, 2009,
Oracle. All rights reserved.
Connected to:
Oracle Database 11g
Enterprise Edition Release 11.2.0.1.0 – Production
With the Partitioning,
OLAP, Data Mining and Real Application Testing options
SQL>
@marker_setup.sql
Marker setup script
You will be prompted for
the name of a schema for the Oracle GoldenGate database objects.
NOTE: The schema must be
created prior to running this script.
NOTE: Stop all DDL
replication before starting this installation.
Enter
Oracle GoldenGate schema name:GGS_OWNER
Marker setup table script
complete, running verification script…
Please enter the name of a
schema for the GoldenGate database objects:
Setting schema name to
GGS_OWNER
MARKER TABLE
——————————-
OK
MARKER SEQUENCE
——————————-
OK
Script complete.
SQL>
@ddl_setup.sql
Oracle GoldenGate DDL
Replication setup script
Verifying that current user
has privileges to install DDL Replication…
You will be prompted for
the name of a schema for the Oracle GoldenGate database objects.
NOTE: For an Oracle 10g
source, the system recycle bin must be disabled. For Oracle 11g and later, it
can be enabled.
NOTE: The schema must be
created prior to running this script.
NOTE: Stop all DDL
replication before starting this installation.
Enter
Oracle GoldenGate schema name:GGS_OWNER
Working, please wait …
Spooling
to file ddl_setup_spool.txt
Checking for sessions that
are holding locks on Oracle Golden Gate metadata tables …
Check complete.
Using GGS_OWNER as a Oracle
GoldenGate schema name.
Working, please wait …
DDL replication setup
script complete, running verification script…
Please enter the name of a
schema for the GoldenGate database objects:
Setting schema name to
GGS_OWNER
CLEAR_TRACE STATUS:
Line/pos Error
———- —————————————————————–
No errors No errors
CREATE_TRACE STATUS:
Line/pos Error
———- —————————————————————–
No errors No errors
TRACE_PUT_LINE STATUS:
Line/pos Error
———- —————————————————————–
No errors No errors
INITIAL_SETUP STATUS:
Line/pos Error
———- —————————————————————–
No errors No errors
DDLVERSIONSPECIFIC PACKAGE
STATUS:
Line/pos Error
———- —————————————————————–
No errors No errors
DDLREPLICATION PACKAGE
STATUS:
Line/pos Error
———- —————————————————————–
No errors No errors
DDLREPLICATION PACKAGE BODY
STATUS:
Line/pos Error
———- —————————————————————–
No errors No errors
DDL IGNORE TABLE
———————————–
OK
DDL IGNORE LOG TABLE
———————————–
OK
DDLAUX PACKAGE
STATUS:
Line/pos Error
———- —————————————————————–
No errors No errors
DDLAUX PACKAGE BODY STATUS:
Line/pos Error
———- —————————————————————–
No errors No errors
SYS.DDLCTXINFO
PACKAGE STATUS:
Line/pos Error
———- —————————————————————–
No errors No errors
SYS.DDLCTXINFO
PACKAGE BODY STATUS:
Line/pos Error
———- —————————————————————–
No errors No errors
DDL HISTORY TABLE
———————————–
OK
DDL HISTORY TABLE(1)
———————————–
OK
DDL DUMP TABLES
———————————–
OK
DDL DUMP COLUMNS
———————————–
OK
DDL DUMP LOG GROUPS
———————————–
OK
DDL DUMP PARTITIONS
———————————–
OK
DDL DUMP PRIMARY KEYS
———————————–
OK
DDL SEQUENCE
———————————–
OK
GGS_TEMP_COLS
———————————–
OK
GGS_TEMP_UK
———————————–
OK
DDL TRIGGER CODE STATUS:
Line/pos Error
———- —————————————————————–
No errors No errors
DDL TRIGGER INSTALL STATUS
———————————–
OK
DDL TRIGGER RUNNING STATUS
———————————–
ENABLED
STAYMETADATA IN TRIGGER
———————————–
OFF
DDL TRIGGER SQL TRACING
———————————–
0
DDL TRIGGER TRACE LEVEL
———————————–
0
LOCATION OF DDL TRACE FILE
————————————————————————————————————————
/u01/app/oracle/diag/rdbms/src/SRC/trace/ggs_ddl_trace.log
Analyzing installation
status…
STATUS OF DDL REPLICATION
————————————————————————————————————————
SUCCESSFUL installation of
DDL Replication software components
Script complete.
SQL>
@role_setup.sql
GGS Role setup script
This script will drop and
recreate the role GGS_GGSUSER_ROLE
To
use a different role name, quit this script and then edit the params.sql script
to change the gg_role parameter to the preferred name. (Do not run the script.)
You will be prompted for
the name of a schema for the GoldenGate database objects.
NOTE: The schema must be
created prior to running this script.
NOTE: Stop all DDL
replication before starting this installation.
Enter
GoldenGate schema name:GGS_OWNER
Wrote
file role_setup_set.txt
PL/SQL procedure
successfully completed.
Role setup script complete
Grant this role to each
user assigned to the Extract, GGSCI, and Manager processes, by using the
following SQL command:
GRANT GGS_GGSUSER_ROLE TO
<loggedUser>
where <loggedUser> is
the user assigned to the GoldenGate processes.
SQL>
SQL> grant
ggs_ggsuser_role to ggs_owner;
Grant succeeded.
SQL> @ddl_enable
Trigger altered.
SQL> @ddl_pin GGS_OWNER
PL/SQL procedure
successfully completed.
PL/SQL procedure
successfully completed.
PL/SQL procedure
successfully completed.
Configure Source :-
1. Add Trandata
i. Create trandata statement and execute
it on gg prompt
SQL> select ‘add
trandata ‘||owner||’.’||object_name||’;’ from dba_objects where owner=’GGTEST’
and object_type=’TABLE’;
‘ADDTRANDATA’||OWNER||’.’||OBJECT_NAME||’;’
——————————————————————————–
add
trandata GGS_OWNER.SCOTT;
add
trandata GGS_OWNER.DEPT;
ii.
Syntax for reference add trandata <owner>.<tablename>
b. Configure
Manager
i. DBLOGIN
USERID ggs_owner, PASSWORD ggs_owner
ii. info
all
iii. edit params mgr
— Created By :- Sachin
Ichake
— Script :- Parameter file
for MANAGER
— Manager Parameter file
PORT 7809
–Automatically start
extract and pump as soon as manager starts
–AUTOSTART EXTRACT GGEC01G1
–If an abend occurs, try to
restart with maximum of 20 times delaying 2 minutes between start attempts
AUTOSTART EXTRACT GGEC01G1
–,RETRIES 20 ,WAITMINUTES 2
–Delete GG trails if it is
older than 3 days and there are no GG process with a checkpoint to it
PURGEOLDEXTRACTS ./DIRDAT/*
USECHECKPOINTS MINKEEPHOURS 3
–Report any lag every 60
Minutes. anytimes lag exceeds 20 Minutes immediately generate a critical
message
LAGREPORTMINUTES 60
LAGCRITICALMINUTES 20
iv. info
all
v. dblogin USERID ggs_owner, PASSWORD ggs_owner
vi. start
manager
c. Configure Extract
i. Create
Obey file if required and put below code into that or directly execute it on gg
prompt.
— Created By :- Sachin
Ichake
— Script :- To Add Extract
–Add extract for EMP
— TARGET DATABASE
DBLOGIN USERID ggs_owner,
PASSWORD ggs_owner
DELETE GGEC01G1
ADD EXTRACT GGEC01G1,
TRANLOG, BEGIN NOW
ADD EXTTRAIL
/u01/soft/dirdat/g1, EXTRACT GGEC01G1
–, MEGABYTES 5
ii. Create parameter file by “edit
params” command and put below code in the parameter file.
1. Edit params GGEC01G1
— Created By :- Sachin
Ichake
— Script :- Parameter file
for Extract
— Parameter Syntax check (
Remove after syntax Validation )
— CHECKPARAMS
EXTRACT GGEC01G1
–Source Database
USERID ggs_owner, PASSWORD
ggs_owner
–Generate SQL output
–FORMATSQL
–Discard file parameters
DISCARDFILE
/u01/soft/dirrpt/GGEC01G1.dsc, PURGE
–Transaction log parameter
–TRANLOGOPTIONS
MANAGESECONDARYTRUNCATIONPOINT
— Local Trail File
Parameters
EXTTRAIL
/u01/soft/dirdat/g1
–Output in SQL format
–To check the parameter
syntax, uncomment the below 2 lines and comment out “WILDCARDRESOLVE DYNAMIC”
parameter. start the group
–CHECKPARAMS
–NODYNAMICRESOLUTION
— Update and delete
operation parameters capture full ops image
–NOCOMPRESSDELETES
–NOCOMPRESSUPDATES
–IGNORE DELETE, UPDATE,
INSERT
— IGNOREDELETES
–IGNOREUPDATES
–IGNOREINSERTS
–Check parameter syntax
also check the table syntax
–after correcting the
syntax, remove these 2 parameters
–CHECKPARAMS
–NODYNAMICRESOLUTION
— Runtime parameters
STATOPTIONS RESETREPORTSTATS
REPORT AT 00:01
REPORTROLLOVER AT 00:01
REPORTCOUNT EVERY 1 HOUR,
RATE
–Wildcard table processing
parameters
WILDCARDRESOLVE DYNAMIC
DDL INCLUDE MAPPED
— GG data capture table
list: EMP
TABLE GGS_OWNER.EMP;
d. Configure
Pump
i. Create
Obey file if required and put below code into that or directly execute it on gg
prompt.
— Created By :- Sachin
Ichake
— Script :- To Add Extract
–Add extract for EMP
— TARGET DATABASE
DBLOGIN USERID ggs_owner,
PASSWORD ggs_owner
— EXTRACT PUMP
DELETE GGPC01G1
ADD EXTRACT GGPC01G1,
EXTTRAILSOURCE /u01/soft/dirdat/g1
ADD RMTTRAIL
/u01/soft/dirdat/1g, EXTRACT GGPC01G1
ii. Create parameter file by “edit
params” command and put below code in the parameter file.
1. Edit params GGPC01G1
— Created By :- Sachin
Ichake
— Script :- Parameter file
for Pump
— Parameter Syntax check (
Remove after syntax Validation )
— CHECKPARAMS
EXTRACT GGPC01G1
–Data Pump mode (This
should be commented)
PASSTHRU
— Control Parameters
RMTHOST 10.184.93.74,
MGRPORT 7809, COMPRESS
–Remote Trail File
parameter
RMTTRAIL
/u01/soft/dirdat/1g
–To check the parameter
syntax, uncomment the below 2 lines and comment out “WILDCARDRESOLVE DYNAMIC”
parameter. start the group
–CHECKPARAMS
–NODYNAMICRESOLUTION
–Wildcard table processing
parameters
WILDCARDRESOLVE DYNAMIC
— GG data pump table list:
EMP
TABLE GGS_OWNER.EMP;
–TABLE EMP
FILTER(ORANGE(1,2));
Configure Target :-
1. Edit GLOBAL parameter file
by connecting gg prompt
i. edit params ./GLOBAL
GGSCHEMA ggs_owner
CHECKPOINTTABLE ggs_owner.
Checkpoint
Configure Manager
i. DBLOGIN
USERID ggs_owner, PASSWORD ggs_owner
ii. info
all
iii.
edit params mgr
— Created By :- Sachin
Ichake
— Script :- Parameter file
for MANAGER
— Manager Parameter file
PORT 7809
–Automatically start
extract and pump as soon as manager starts
–AUTOSTART EXTRACT GGPC01G1
–If an abend occurs, try to
restart with maximum of 20 times delaying 2 minutes between start attempts
AUTOSTART EXTRACT GGPC01G1
–,RETRIES 20 ,WAITMINUTES 2
–Delete GG trails if it is
older than 3 days and there are no GG process with a checkpoint to it
PURGEOLDEXTRACTS ./DIRDAT/*
USECHECKPOINTS MINKEEPHOURS 3
–Report any lag every 60
Minutes. anytimes lag exceeds 20 Minutes immediately generate a critical
message
LAGREPORTMINUTES 60
LAGCRITICALMINUTES 20
iv. info
all
v. dblogin USERID ggs_owner, PASSWORD ggs_owner
vi. start manager
1. Create Check point table
i. dblogin
userid ggs_owner password ggs_owner
ii. add checkpointtable ggs_owner.checkpoint
Configure Replicat
i. Create Obey file if required and put below code into that or
directly execute it on gg prompt.
— Created By :- Sachin
Ichake
— Script :- Replicat Obey
File
— login to the database
DBLOGIN USERID ggs_owner, PASSWORD
ggs_owner
— Add replicat
DELETE GGRC01G1
ADD REPLICAT GGRC01G1,
EXTTRAIL /u01/soft/dirdat/1g, checkpointtable ggs_owner.checkpoint
ii. Create parameter file by “edit
params” command and put below code in the parameter file.
— Created By :- Sachin
Ichake
— Script :- Parameter file
for Replicat
–Following 3 options are to
display SQL statements comment out BATCHSQL
–SHOWSYNTAX
–NODYNSQL
–NOBINARYCHARS
—
–NOLIST will not expand the
macros in the report file
–INCLUDE the macro library
–LIST is end of NOLIST
—
REPLICAT GGRC01G1
— Handle the collisions
after the initial load
HANDLECOLLISIONS
— TARGET DATABASE
–#DB_Connect()
USERID ggs_owner, PASSWORD
ggs_owner
–Discard file parameters
DISCARDFILE
/u01/soft/dirrpt/GGEC01G1.dsc, PURGE
—
–Source and Target table
parameters
ASSUMETARGETDEFS
–SOURCEDEFS
C:\GG\DIRSQL\MYTABLES.SQL
—
–To check the parameter
syntax, uncomment the below 2 lines and comment out “WILDCARDRESOLVE DYNAMIC”
parameter.
–CHECKPARAMS
–NODYNAMICRESOLUTION
— Performance parameters
for <5k bytes per row data change
— Replaced “BATCHSQL”
with “BATCHSQL BATCHTRANSOPS … ”
BATCHSQL BATCHTRANSOPS
10000
–Running reports parameters
–#generate_stats()
REPORTCOUNT EVERY 1 HOUR,
RATE
STATOPTIONS
RESETREPORTSTATS
REPORT AT 00:01
REPORTROLLOVER AT 00:01
— Wildcard table processing
parameter
WILDCARDRESOLVE DYNAMIC
TRANSACTIONTIMEOUT 5 S
–ddl support
DDL INCLUDE ALL
DDLERROR DEFAULT IGNORE
RETRYOP
–Specify table mapping —
MAP
GGS_OWNER.*, TARGET GGS_OWNER.*;
MAP GGS_OWNER.EMP, TARGET GGS_OWNER.EMP;
START EXTRACT :- NEED TO BE CARRIED OUT AT SOURCE
1. START MGR
2. START EXTRACT GG*
START REPLICAT :- NEED TO BE CARRIED OUT AT TARGET
1. START MGR
2. START replicat GG*
Some more scripts
No comments:
Post a Comment