Golden Gate:-
A) Setting up Unidirectional replication - Initial Load
Secnario 1)
Replicating between two database on the same host.
Source db - test11
Target db - layatest
HostNam - rhel.manzoor.com
Steps :-
------
a) Go to OTN http://edelivery.oracle.com
b) Select Oracle Fusion Middleware and select the platform accordinfly.
c) Download the latest version of GG software.
d) ftp, winscp the gg software to the linux machine.
e) Create a gg home directory
$ mkdir -p /u01/oracle/product/11.2.0/gg
d) Add the below environment variables in .bash_profile.
$ vi .bash_profile
export ORACLE_GG=/u01/oracle/product/11.2.0/gg
export PATH=$ORACLE_HOME/bin:$ORACLE_GG:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_GG
e) Copy the gg software to gg home and unzip the file and then untar the file
$ tar -xvf fbo_ggs_Linux_x86_ora11g_32bit.tar
f) Connect to golden gate command line interface and create the necessary directories.
[oracle@rhel u01] cd $ORALE_GG
[oracle@rhel gg]$ 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.
GGSCI (rhel.manzoor.com) 2> CREATE SUBDIRS
Creating subdirectories under current directory /u01/oracle/product/11.2.0/gg
Parameter files /u01/oracle/product/11.2.0/gg/dirprm: created
Report files /u01/oracle/product/11.2.0/gg/dirrpt: created
Checkpoint files /u01/oracle/product/11.2.0/gg/dirchk: created
Process status files /u01/oracle/product/11.2.0/gg/dirpcs: created
SQL script files /u01/oracle/product/11.2.0/gg/dirsql: created
Database definitions files /u01/oracle/product/11.2.0/gg/dirdef: created
Extract data files /u01/oracle/product/11.2.0/gg/dirdat: created
Temporary files /u01/oracle/product/11.2.0/gg/dirtmp: created
Stdout files /u01/oracle/product/11.2.0/gg/dirout: created
GGSCI (rhel.manzoor.com) 3> exit
g) Create a golden gate user which will be used by Manager / Extract / Replicate Process. Create the user and grant the necessary privileges.
DB : Execute the below on both the database (test11 & layatest)
sql> create tablespace gg_tblspc datafile '/orafiles/test11/ggs_tblspc01.dbf' size 100m;
sql> create user ggowner identified by ggowner default tablespace gg_tblspc temporary tablespace temp;
sql> grant create session to ggowner;
sql> grant connect, resource to ggowner;
sql> grant select any dictionary, select any table, insert any table to ggowner;
sql> grant select any table, update any table, delete any table, insert any table to ggowner;
sql> grant flashback any table to ggowner;
sql> grant execute on dbms_flashback to ggowner;
sql> grant execute on utl_file to ggowner;
sql> grant create table to ggowner;
-- Enable the supplemental logging, If supplemental log is not enabled it may cause extract process to handle key update incorrectly if key column is not in first row piece.
sql> alter database add supplemental log data (all) columns;
Database altered
h) Test whether you can able to connect to the db using the above crediantials.
GGSCI (rhel.manzoor.com) 3> dblogin userid ggowner@test11, password ggowner
Successfully logged into database.
GGSCI (rhel.manzoor.com) 4> dblogin userid ggowner@layatest, password ggowner
Successfully logged into database.
i) Configure the Manager process and start it.
Port -
The only mandatory parameter that we need to specify is the PORT which defines the port on the local system where the manager process is running. The default port is 7809 and we can either specify the default port or some other port provided the port is available and not restricted in any way.
AUTOSTART -
Some other recommended optional parameters are AUTOSTART which which automatically
start the Extract and Replicat processes when the Manager starts.
USERID PASSWORD
The USERID and PASSWORD parameter and required if you enable GoldenGate DDL support and this is the Oracle user account that we created for the Manager(and Extract/Replicat).
PURGEOLDEXTRACTS
The Manager process can also clean up trail files from disk when GoldenGate has finished processing them via the PURGEOLDEXTRACTS parameter. Used with the USECHECKPOINTS clause, it will ensure that until all processes have fnished using the data contained in the trail files, they will not be deleted.
GGSCI (rhel.manzoor.com) 5> EDIT PARAMS MGR
PORT 7809
USERID ggowner, PASSWORD ggowner
PURGEOLDEXTRACTS /u01/oracle/product/11.2.0/gg/dirdat/ex, USECHECKPOINTS
- The Manager process can be started using - START MANAGER
and stopped using - STOP MANAGER
and the status can be checked using - INFO MANAGER
Now start the Manager process.
GGSCI (rhel.manzoor.com) 2> START MANAGER
Manager started.
GGSCI (rhel.manzoor.com) 3> INFO MANAGER
Manager is running (IP port rhel.manzoor.com.7809).
## Initial data load.
Here we are going to replicate the manzoor.emp table which is in test11 to manzoor.emp in layatest
On source db we need to create a extract process, the process should be defined with
group name, in this example we use the group name as LDTESTDB
db: test11
SQL> select count(*) from manzoor.emp;
COUNT(*)
----------
100000
db : layatest
SQL> select count(*) from manzoor.emp;
COUNT(*)
----------
0
GGSCI (rhel.manzoor.com) 8> ADD EXTRACT LDTESTDB, SOURCEISTABLE
EXTRACT added.
-- Here the data is present in the table and not in the archive/redo log files, hence
we need to mention SOURCEISTABLE.
Create parameter file for the extract process.
EXTRACT : name of the extract group
USERID/PASSWORD : the database user which has been configured earlier for Extract ( this user is created in the source database)
RMTHOST : This will be the IP address or hostname of the target system
MGRPORT : the port where the Manager process is running
TABLE : specify the table which is being extracted and replicated. This can be specified in a number of ways
using wildcard characters to include or exclude tables as well as entire schemas.
GGSCI (rhel.manzoor.com) 10> EDIT PARAMS LDTESTDB
EXTRACT LDTESTDB
USERID ggowner@test11, PASSWORD ggowner
RMTHOST rhel.manzoor.com, MGRPORT 7809
RMTTASK replicat, GROUP LDLAYADB
TABLE manzoor.emp;
## Now create the replicate proces, the group should be the name which we have speicified in the extract process parameters using RMTTASK , GROUP. which is LDLAYADB
GGSCI (rhel.manzoor.com) 15> ADD REPLICAT LDLAYADB, SPECIALRUN
REPLICAT added.
-- Since it is a special run we need to include the keyword SPECIALRUN.
Create the parameter file for the replicat process.
REPLICAT : name of the Replicat group created for the initial data load
USERID/PASSWORD : database credentials for the Replicat user (this user is created in the target database)
ASSUMETARGETDEFS : this means that the source table structure exactly matches the target database table structure.
MAP : with GoldenGate we can have the target database structure entirely differ from that of the source in terms of table names as well as the column definitions of the tables. This parameter provides us the mapping of the source and target tables which is same in this case
GGSCI (rhel.manzoor.com) 4> EDIT PARAMS LDLAYADB
REPLICAT LDLAYADB
USERID ggowner@layatest, PASSWORD ggowner
ASSUMETARGETDEFS
MAP manzoor.emp, TARGET manzoor.emp;
## Now we can start the extract process for the initial load.
GGSCI (rhel.manzoor.com) 1> START EXTRACT LDTESTDB
Sending START request to MANAGER ...
EXTRACT LDTESTDB starting
## Check the status using the info command.
GGSCI (rhel.manzoor.com) 2> INFO EXTRACT LDTESTDB
EXTRACT LDTESTDB Initialized 2012-07-27 19:35 Status RUNNING
Checkpoint Lag Not Available
Log Read Checkpoint Not Available
First Record Record 0
Task SOURCEISTABLE
## On the info we can see that the status shows as RUNNING.
## For any error we can view the error by viewing the report.
GGSCI (rhel.manzoor.com) 3> VIEW REPORT LDTESTDB
GGSCI (rhel.manzoor.com) 1> INFO EXTRACT LDTESTDB
EXTRACT LDTESTDB Last Started 2012-07-28 09:19 Status STOPPED
Checkpoint Lag Not Available
Log Read Checkpoint Table MANZOOR.EMP
2012-07-28 09:22:18 Record 100000
Task SOURCEISTABLE
## Now the status shows stopped and the record shows 100000, let have a check
on record cound on the target table.
SQL> select count(*) from manzoor.emp;
COUNT(*)
----------
100000
-- Target table has been replicated correctly as per the source.
