Friday 29 November 2013

Oracle GoldenGate Replication (Oracle to Oracle) - RAC Database



Operating System           : RHEL5 - 64bit.
Database                          : Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production



STEP 1 :- Installation of GoldenGate Software by unzipping/untar the tar file.
**************************** SOURCE   ***********************
[oracle@rac3 goldengate]$ cd /home/oracle/gg

[oracle@rac3 gg]$ ls
fbo_ggs_Linux_x86_ora11g_32bit.tar

[oracle@rac3 gg]$ tar -xvf fbo_ggs_Linux_x86_ora11g_32bit.tar

****************************  TARGET   ***********************

[oracle@rac3 goldengate]$ cd /home/oracle/gg

[oracle@rac3 gg]$ ls
fbo_ggs_Linux_x86_ora11g_32bit.tar

[oracle@rac3 gg]$ tar -xvf fbo_ggs_Linux_x86_ora11g_32bit.tar

STEP 2:- It is mandatory to setup the Path, LD_Library_path to execute ggsci command else you will receive the highlighted error below.

 ****************************  SOURCE   ***********************

[oracle@rac3 bin-D_H]$export PATH=$PATH:/home/oracle/gg
[oracle@rac3 bin-D_H]$export LD_LIBRARY_PATH=$ORACLE_HOME/lib/:/home/oracle/gg
[oracle@rac3 bin-D_H]$cd /home/oracle/gg
[oracle@rac3 gg-D_H]$./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 (rac3.sukku.com) 1>
 

*****************************  TARGET   ***********************

[oracle@rac4 gg-D_H]$./ggsci
./ggsci: error while loading shared libraries: libnnz11.so: cannot open shared object file: No such file or directory

[oracle@rac4 gg-D_H]$export PATH=$PATH:/home/oracle/gg
[oracle@rac4 gg-D_H]$export LD_LIBRARY_PATH=$ORACLE_HOME/lib/:/home/oracle/gg
[oracle@rac4 gg-D_H]$pwd
/home/oracle/gg
[oracle@rac4 gg-D_H]$./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 (rac4.sukku.com) 1>


STEP 3:- it is mandatory to create sub directories by using “create subdirs” command at ggsci prompt.

**************************** SOURCE   ***********************

GGSCI (rac3.sukku.com) 1> create subdirs

Creating subdirectories under current directory /home/oracle/gg

Parameter files                /home/oracle/gg/dirprm: already exists
Report files                   /home/oracle/gg/dirrpt: created
Checkpoint files               /home/oracle/gg/dirchk: created
Process status files           /home/oracle/gg/dirpcs: created
SQL script files               /home/oracle/gg/dirsql: created
Database definitions files     /home/oracle/gg/dirdef: created
Extract data files             /home/oracle/gg/dirdat: created
Temporary files                /home/oracle/gg/dirtmp: created
Stdout files                   /home/oracle/gg/dirout: created


***************************** TARGET   ***********************

GGSCI (rac4.sukku.com) 1> create subdirs

Creating subdirectories under current directory /home/oracle/gg

Parameter files                /home/oracle/gg/dirprm: already exists
Report files                   /home/oracle/gg/dirrpt: created
Checkpoint files               /home/oracle/gg/dirchk: created
Process status files           /home/oracle/gg/dirpcs: created
SQL script files               /home/oracle/gg/dirsql: created
Database definitions files     /home/oracle/gg/dirdef: created
Extract data files             /home/oracle/gg/dirdat: created
Temporary files                /home/oracle/gg/dirtmp: created
Stdout files                   /home/oracle/gg/dirout: created


STEP 4:- create a user at database level to access DB from ggsci.

****************************  SOURCE   ***********************

SQL> grant connect,resource, dba, select any dictionary, select any table, create table, flashback any table, execute on dbms_flashback, execute on utl_file to ggs identified by ggs;

Grant succeeded.

SQL> conn ggs/ggs
Connected.

*****************************  TARGET   ***********************

SQL>  grant connect,resource, dba, select any dictionary, select any table, create table, flashback any table, execute on dbms_flashback, execute on utl_file to ggs identified by ggs;

Grant succeeded.

SQL> conn ggt/ggt
Connected.


 STEP 5:- It is mandatory to login to database to access date from ggsci
 
****************************  SOURCE   ***********************

GGSCI (rac3.sukku.com) 2> dblogin userid ggs, password ggs
Successfully logged into database.

*****************************  TARGET   ***********************

Note: There may be a chance of multiple DB instances running on same node. It is mandatory to export the instance name where we have created a user for GoldenGate.

GGSCI (rac4.sukku.com) 2> dblogin userid ggt, password ggt
ERROR: Unable to connect to database using user ggt. Please check privileges.
ORA-12162: TNS:net service name is incorrectly specified.

[oracle@rac4 gg-D_H]$. oraenv
ORACLE_SID = [oracle] ? ggtestdb2
The Oracle base for ORACLE_HOME=/oraeng/app/oracle/product/11.2.0 is /oraeng/app/oracle/product

GGSCI (rac4.sukku.com) 1> dblogin userid ggt, password ggt
Successfully logged into database.

##############################################################

Note: ggsci command won’t accept ‘;’ at the end of the commands.

GGSCI (rac3.sukku.com) 3> info all;               -----     ; not allowed for ggsci commands.
ERROR: Invalid command.

##############################################################

STEP 6:- To check what are the resources running on goldengate.

****************************  SOURCE   ***********************

GGSCI (rac3.sukku.com) 4> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt  --- by default Manager process will be in stopped status.

MANAGER     STOPPED 
 
Note:  Edit the parameter file.
 User authentication is not mandatory in the parameter file, but it is recommended to authenticate.

GGSCI (rac3.sukku.com) 5> edit params mgr
---------- vi editor----
port 7809
userid ggs, password ggs --- (NOT Mandatory)
------------------------

Note: To start the manager process.

GGSCI (rac3.sukku.com) 6> start manager

Manager started.

GGSCI (rac3.sukku.com) 7> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                          

*****************************  TARGET   ***********************
 
[oracle@rac4 dirprm-D_H]$pwd
/home/oracle/gg/dirprm

[oracle@rac4 dirprm-D_H]$vi mgr.prm

port 7809

GGSCI (rac4.sukku.com) 4> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     STOPPED

Note: To start the manager process.

GGSCI (rac4.sukku.com) 5> start manager

Manager started.


Note: To stop the manager process.

GGSCI (rac4.sukku.com) 6> stop manager
Manager process is required by other GGS processes.
Are you sure you want to stop it (y/n)? yes

Sending STOP request to MANAGER ...
Request processed.
Manager stopped.

GGSCI (rac4.sukku.com) 7> start manager

Manager started.

Note: To check the status of manager process.

GGSCI (rac4.sukku.com) 8> status manager

Manager is running (IP port rac4.sukku.com.7809).


GGSCI (rac4.sukku.com) 9>

****************************  SOURCE   ***********************
             --- Initial Load through extract process -------
Note: before starting the initial load replication, make sure that the structure of table exists at Target side.
SOURCEISTABLE” is the keyword/parameter to run the Initial load through GoldenGate.

GGSCI (rac3.sukku.com) 9> ADD EXTRACT initload, SOURCEISTABLE
EXTRACT added.

GGSCI (rac3.sukku.com) 10> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt   ---  Initial load extract process will not show in info all

MANAGER     RUNNING          


GGSCI (rac3.sukku.com) 11> edit params initload

EXTRACT initload
USERID ggs, PASSWORD ggs
RMTHOST rac4, MGRPORT 7809
RMTTASK replicat, GROUP repload
TABLE ggs.t;

 *****************************  TARGET   ***********************

SPECIALRUN” is the keyword to receive the initial load at replicat side.
Extract/Replicat group name must be less than 8 characters.

GGSCI (rac4.sukku.com) 8> ADD REPLICAT initload2, SPECIALRUN
ERROR: Invalid group name (must be at most 8 characters).

GGSCI (rac4.sukku.com) 9> ADD REPLICAT repload, SPECIALRUN
REPLICAT added.

GGSCI (rac4.sukku.com) 10> EDIT PARAMS  repload

REPLICAT repload
USERID ggt, PASSWORD ggt
ASSUMETARGETDEFS
MAP ggs.t, TARGET ggt.t;

****************************  SOURCE   ***********************
Example:  Create a table and insert few records..

SQL> select count(*) from t;

  COUNT(*)
----------
       160

GGSCI (rac3.sukku.com) 23> start extract initload

Sending START request to MANAGER...
EXTRACT INITLOAD starting
 
GGSCI (rac3.sukku.com) 24> info extract initload

EXTRACT    INITLOAD Last Started 2012-09-20 15:17   Status RUNNING
Checkpoint Lag       Not Available
Log Read Checkpoint Table GGS.T
                     2012-09-20 15:17:51 Record 1
Task                 SOURCEISTABLE

GGSCI (rac3.sukku.com) 25> info extract initload

EXTRACT    INITLOAD  Last Started 2012-09-20 15:20   Status STOPPED
Checkpoint Lag       Not Available
Log Read Checkpoint  Table GGS.T
                     2012-09-20 15:20:22  Record 160
Task                 SOURCEISTABLE

*****************************  TARGET   ***********************

create a table as similar to source..

GGSCI (rac4.sukku.com) 17> info replicat repload

REPLICAT   REPLOAD   Initialized   2012-09-20 15:02   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:18:04 ago)
Log Read Checkpoint  Not Available
Task                 SPECIALRUN


GGSCI (rac4.sukku.com) 18> info replicat repload

REPLICAT   REPLOAD   Initialized   2012-09-20 15:02   Status STOPPED
Checkpoint Lag       00:00:00 (updated 00:18:55 ago)
Log Read Checkpoint  Not Available
Task                 SPECIALRUN

SQL> select count(*) from t;

  COUNT(*)
----------
       160


Note: Checkpoint Table is mandatory for normal Extract & Replicat.
GGSCI (rac3.sukku.com) 26> edit params ./GLOBALS
GGSCHEMA ggs
CHECKPOINTTABLE ggs.chkpt

GGSCI (rac3.sukku.com) 27> dblogin userid ggs, password ggs
Successfully logged into database.

GGSCI (rac3.sukku.com) 28> add checkpointtable ggs.chkpt
Successfully created checkpoint table ggs.chkpt.

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
CHKPT             TABLE
CHKPT_LOX         TABLE
T                 TABLE

SQL> desc chkpt_lox
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 GROUP_NAME                                NOT NULL VARCHAR2(8)
 GROUP_KEY                                 NOT NULL NUMBER(19)
 LOG_CMPLT_CSN                             NOT NULL VARCHAR2(129)
 LOG_CMPLT_XIDS_SEQ                        NOT NULL NUMBER(5)
 LOG_CMPLT_XIDS                            NOT NULL VARCHAR2(2000)

SQL> desc chkpt
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 GROUP_NAME                                NOT NULL VARCHAR2(8)
 GROUP_KEY                                 NOT NULL NUMBER(19)
 SEQNO                                              NUMBER(10)
 RBA                                       NOT NULL NUMBER(19)
 AUDIT_TS                                           VARCHAR2(29)
 CREATE_TS                                 NOT NULL DATE
 LAST_UPDATE_TS                            NOT NULL DATE
 CURRENT_DIR                               NOT NULL VARCHAR2(255)
 LOG_CSN                                            VARCHAR2(129)
 LOG_XID                                            VARCHAR2(129)
 LOG_CMPLT_CSN                                      VARCHAR2(129)
 LOG_CMPLT_XIDS                                     VARCHAR2(2000)
 VERSION                                            NUMBER(3)


*****************************  SOURCE    ***********************

threads 2” is for RAC databases, so that Goldengate will be aware of that it should get the data from 2 sources (Node1 & Node2 online redologs).

GGSCI (rac3.sukku.com) 30> add extract ext,tranlog, threads 2, begin now
EXTRACT added.

GGSCI (rac3.sukku.com) 31> add rmttrail /home/oracle/gg/dirdat/rt, extract ext
RMTTRAIL added.

GGSCI (rac3.sukku.com) 32> edit params ext

EXTRAXT ext
USERID ggs, PASSWORD ggs
RMTHOST rac4, MGRPORT 7809
RMTTRAIL /home/oracle/gg/dirdat/rt
TABLE ggs.t;

*****************************  TARGET   ***********************

GGSCI (rac4.sukku.com) 19> add replicat rep, exttrail /home/oracle/gg/dirdat/rt
ERROR: No checkpoint table specified for ADD REPLICAT.

GGSCI (rac4.sukku.com) 20> edit params ./GLOBALS
GGSCHEMA ggt
CHECKPOINTTABLE ggt.chkpt

GGSCI (rac4.sukku.com) 21> dblogin userid ggt, password ggt
Successfully logged into database.

GGSCI (rac4.sukku.com) 22> add checkpointtable ggt.chkpt
Successfully created checkpoint table ggt.chkpt.

GGSCI (rac4.sukku.com) 23> add replicat rep, exttrail /home/oracle/gg/dirdat/rt
REPLICAT added.

GGSCI (rac4.sukku.com) 24> edit params rep
REPLICAT rep
ASSUMETARGETDEFS
USERID ggt, PASSWORD ggt
MAP ggs.t, TARGET ggt.t;

##############################################################

*****************************  SOURCE   ***********************

GGSCI (rac3.sukku.com) 36> start extract ext

Sending START request to MANAGER ...
EXTRACT EXT starting

GGSCI (rac3.sukku.com) 51> info extract ext

EXTRACT    EXT       Initialized   2012-09-20 16:10   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:49 ago)
Log Read Checkpoint  Oracle Redo Logs
                     2012-09-20 16:10:08  Thread 1, Seqno 0, RBA 0
                     SCN 0.0 (0)
Log Read Checkpoint  Oracle Redo Logs
                     2012-09-20 16:10:08  Thread 2, Seqno 0, RBA 0
                     SCN 0.0 (0)


GGSCI (rac3.sukku.com) 52> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                          
EXTRACT     RUNNING     EXT         00:00:00      00:00:58   

---------------------------------------------   without threads 2 option in RAC DB   ------------------------------------------
2012-09-20 15:57:18  ERROR   OGG-00446  Oracle GoldenGate Capture for Oracle, ext.prm:  The number of Oracle redo threads (2) is not the same as the number of checkpoint threads (1). EXTRACT groups on RAC systems should be created with the THREADS parameter (e.g., ADD EXT <group name>, TRANLOG, THREADS 2, BEGIN...).
----------------------------------------------------------------------------------------------------------------------------------

*****************************  TARGET   ***********************

GGSCI (rac4.sukku.com) 29> start replicat rep

Sending START request
REPLICAT REP starting

GGSCI (rac4.sukku.com) 30> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                          
REPLICAT    RUNNING     REP         00:00:00      00:00:00   

GGSCI (rac4.sukku.com) 31> info replicat rep

REPLICAT   REP       Last Started 2012-09-20 16:13   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:06 ago)
Log Read Checkpoint  File /home/oracle/gg/dirdat/rt000000
                     First Record  RBA 0
########################################################################################

Note: Unable to replicate.. because redo's are in ASM..

2012-09-20 16:57:33  ERROR   OGG-00446  Oracle GoldenGate Capture for Oracle, ext.prm:  No valid log files for current redo sequence 256, thread 1, error retrieving redo file name for sequence 256, archived = 0, use_alternate = 0Not able to establish initial position for begin time 2012-09-20 16:56:52.
2012-09-20 16:57:33  ERROR   OGG-01668  Oracle GoldenGate Capture for Oracle, ext.prm:  PROCESS ABENDING.

Note: create password file and grant sysasm privilege and added this parameter to read ASM logfiles to fix the above error.

EXTRACT ext
USERID ggs, PASSWORD ggs
RMTHOST rac4, MGRPORT 7809
RMTTRAIL /home/oracle/gg/dirdat/rt
tranlogoptions asmuser sys@asm1, asmpassword sys
TABLE ggs.t;

GGSCI (rac3.sukku.com) 63> stats extract ext

Sending STATS request to EXTRACT EXT ...

Start of Statistics at 2012-09-20 21:21:27.

DDL replication statistics (for all trails):

*** Total statistics since extract started     ***
        Operations                                         3.00
        Mapped operations                                  3.00
        Unmapped operations                                0.00
        Other operations                                   0.00
        Excluded operations                                0.00

Output to /home/oracle/gg/dirdat/rt:

Extracting from GGS.A to GGS.A:

*** Total statistics since 2012-09-20 18:45:06 ***
        Total inserts                                      1.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   1.00

*** Daily statistics since 2012-09-20 18:45:06 ***
        Total inserts                                      1.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   1.00

*** Hourly statistics since 2012-09-20 21:00:00 ***

        No database operations have been performed.

*** Latest statistics since 2012-09-20 18:45:06 ***
        Total inserts                                      1.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   1.00

Extracting from GGS.GGS_MARKER to GGS.GGS_MARKER:

*** Total statistics since 2012-09-20 18:45:06 ***

        No database operations have been performed.

*** Daily statistics since 2012-09-20 18:45:06 ***

        No database operations have been performed.

*** Hourly statistics since 2012-09-20 21:00:00 ***

        No database operations have been performed.

*** Latest statistics since 2012-09-20 18:45:06 ***

        No database operations have been performed.

Extracting from GGS.GGS_MARKER to GGS.GGS_MARKER:

*** Total statistics since 2012-09-20 18:45:06 ***

        No database operations have been performed.

*** Daily statistics since 2012-09-20 18:45:06 ***

        No database operations have been performed.

*** Hourly statistics since 2012-09-20 21:00:00 ***

        No database operations have been performed.

*** Latest statistics since 2012-09-20 18:45:06 ***

        No database operations have been performed.

Extracting from GGS.H to GGS.H:

*** Total statistics since 2012-09-20 18:45:06 ***
        Total inserts                                      2.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   2.00

*** Daily statistics since 2012-09-20 18:45:06 ***
        Total inserts                                      2.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   2.00

*** Hourly statistics since 2012-09-20 21:00:00 ***

        No database operations have been performed.

*** Latest statistics since 2012-09-20 18:45:06 ***
        Total inserts                                      2.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   2.00

End of Statistics.

No comments: