11 September 2015

step by step goldengate installation and configuration on LinuxX86_64bit

Oracle Golden gate 12.1.2 installation on Source node






[oracle@prodnew1 golden]$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/oracle/golden
[oracle@prodnew1 golden]$ echo $LD_LIBRARY_PATH
/oracle/product/11.2.0/lib:/oracle/golden
[oracle@prodnew1 golden]$ ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 12.1.2.1.0 OGGCORE_12.1.2.1.0_PLATFORMS_140727.2135.1_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Aug  7 2014 09:14:25
Operating system character set identified as UTF-8.

Copyright (C) 1995, 2014, Oracle and/or its affiliates. All rights reserved.

GGSCI (prodnew1.muthu.com) 1> CREATE SUBDIRS

Creating subdirectories under current directory /oracle/golden

Parameter files                /oracle/golden/dirprm: already exists
Report files                   /oracle/golden/dirrpt: already exists
Checkpoint files               /oracle/golden/dirchk: already exists
Process status files           /oracle/golden/dirpcs: already exists
SQL script files               /oracle/golden/dirsql: already exists
Database definitions files     /oracle/golden/dirdef: already exists
Extract data files             /oracle/golden/dirdat: already exists
Temporary files                /oracle/golden/dirtmp: already exists
Credential store files         /oracle/golden/dircrd: already exists
Masterkey wallet files         /oracle/golden/dirwlt: already exists
Dump files                     /oracle/golden/dirdmp: already exists

GGSCI (prodnew1.muthu.com) 2> exit

[oracle@prodnew1 golden]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Thu Aug 13 14:00:24 2015

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create tablespace ggs_data datafile '/oracle/product/oradata//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 succeeded.

SQL> grant select any dictionary, select any table to ggs_owner;

Grant succeeded.

SQL> grant create table to ggs_owner;

Grant succeeded.

SQL> grant flashback any table to ggs_owner;

Grant succeeded.

SQL> grant execute on dbms_flashback to ggs_owner;

Grant succeeded.

SQL> grant execute on utl_file to ggs_owner;

Grant succeeded.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@prodnew1 golden]$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.1.2.1.0 OGGCORE_12.1.2.1.0_PLATFORMS_140727.2135.1_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Aug  7 2014 09:14:25
Operating system character set identified as UTF-8.

Copyright (C) 1995, 2014, Oracle and/or its affiliates. All rights reserved.

GGSCI (prodnew1.muthu.com) 1> DBLOGIN USERID ggs_owner, PASSWORD ggs_owner
Successfully logged into database.

GGSCI (prodnew1.muthu.com as ggs_owner@PRODNEW) 2> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
exit
[oracle@prodnew1 golden]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Thu Aug 13 14:04:20 2015

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

Database altered.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@prodnew1 golden]$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.1.2.1.0 OGGCORE_12.1.2.1.0_PLATFORMS_140727.2135.1_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Aug  7 2014 09:14:25
Operating system character set identified as UTF-8.

Copyright (C) 1995, 2014, Oracle and/or its affiliates. All rights reserved.

GGSCI (prodnew1.muthu.com) 1> DBLOGIN USERID ggs_owner, PASSWORD ggs_owner
Successfully logged into database.

GGSCI (prodnew1.muthu.com as ggs_owner@PRODNEW) 2> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING


GGSCI (prodnew1.muthu.com as ggs_owner@PRODNEW) 3> EDIT PARAMS MGR
PORT 7809
USERID ggs_owner, PASSWORD ggs_owner
PURGEOLDEXTRACTS /oracle/golden/dirdat/ex, USECHECKPOINTS

GGSCI (prodnew1.muthu.com as ggs_owner@PRODNEW) 4> stop MGR
Manager process is required by other GGS processes.
Are you sure you want to stop it (y/n)?y

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

GGSCI (prodnew1.muthu.com as ggs_owner@PRODNEW) 5> start MGR
Manager started.

GGSCI (prodnew1.muthu.com as ggs_owner@PRODNEW) 6> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING

GGSCI (prodnew1.muthu.com as ggs_owner@PRODNEW) 7> info manager

Manager is running (IP port prodnew1.muthu.com.7809, Process ID 21155).

GGSCI (prodnew1.muthu.com) 2> ADD EXTRACT ext11, TRANLOG, BEGIN NOW
EXTRACT added.

GGSCI (prodnew1.muthu.com) 3> ADD EXTTRAIL /oracle/golden/dirdat/la, EXTRACT ext11
EXTTRAIL added.

GGSCI (prodnew1.muthu.com) 4> ADD EXTRACT dpump11, EXTTRAILSOURCE /oracle/golden/dirdat/la
EXTRACT added.

GGSCI (prodnew1.muthu.com) 5> EDIT PARAMS ext11

*********************************************
EDIT PARAMS ext11

EXTRACT ext11
USERID ggs_owner, PASSWORD ggs_owner
EXTTRAIL /oracle/golden/dirdat/la                ------ Source Path
DDL INCLUDE MAPPED
TABLE scott.emp;

*********************************************

GGSCI (prodnew1.muthu.com) 6> ADD RMTTRAIL /oracle/golden/dirdat/ra, EXTRACT dpump11  ------- Target Path
RMTTRAIL added.

GGSCI (prodnew1.muthu.com) 7> edit params dpump11

*********************************************
EDIT PARAMS dpump11

EXTRACT dpump11
USERID ggs_owner, PASSWORD ggs_owner
RMTHOST localhost, MGRPORT 7810
RMTTRAIL /oracle/golden/dirdat/ra                ------ Target Path
PASSTHRU
TABLE scott.emp;
*********************************************

GGSCI (prodnew1.muthu.com) 8> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     STOPPED     DPUMP11     00:00:00      00:13:33
EXTRACT     STOPPED     EXT11       00:00:00      00:15:15


GGSCI (prodnew1.muthu.com) 9> start extract *

Sending START request to MANAGER ...
EXTRACT DPUMP11 starting

Sending START request to MANAGER ...
EXTRACT EXT11 starting


GGSCI (prodnew1.muthu.com) 10> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     DPUMP11     00:00:00      00:13:58
EXTRACT     RUNNING     EXT11       00:00:00      00:15:40

[oracle@prodnew1 golden]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Thu Aug 13 16:57:20 2015

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @marker_setup

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> alter session set recyclebin=OFF;

Session altered.

SQL> @ddl_setup

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.

WARNING: Tablespace GGS_DATA does not have AUTOEXTEND enabled.

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
------------------------------------------------------------------------------------------------------------------------
/oracle/product/11.2.0/admin/diag/rdbms/prodnew/PRODNEW/trace/ggs_ddl_trace.log

Analyzing installation status...


VERSION OF DDL REPLICATION
------------------------------------------------------------------------------------------------------------------------
OGGCORE_12.1.2.1.0_PLATFORMS_140727.2135.1

STATUS OF DDL REPLICATION
------------------------------------------------------------------------------------------------------------------------
SUCCESSFUL installation of DDL Replication software components

Script complete.
SQL> @role_setup

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

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@prodnew1 golden]$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.1.2.1.0 OGGCORE_12.1.2.1.0_PLATFORMS_140727.2135.1_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Aug  7 2014 09:14:25
Operating system character set identified as UTF-8.

Copyright (C) 1995, 2014, Oracle and/or its affiliates. All rights reserved.

GGSCI (prodnew1.muthu.com) 3> DBLOGIN USERID ggs_owner, PASSWORD ggs_owner
Successfully logged into database.

GGSCI (prodnew1.muthu.com as ggs_owner@PRODNEW) 4> ADD TRANDATA scott.emp

Logging of supplemental redo data enabled for table SCOTT.EMP.
TRANDATA for scheduling columns has been added on table 'SCOTT.EMP'.
GGSCI (prodnew1.muthu.com as ggs_owner@PRODNEW) 5> EDIT PARAM EXT1

GGSCI (prodnew1.muthu.com as ggs_owner@PRODNEW) 6> EDIT PARAM EXT11

GGSCI (prodnew1.muthu.com as ggs_owner@PRODNEW) 7> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     ABENDED     DPUMP11     00:00:00      00:25:19
EXTRACT     STOPPED     EXT11       00:00:00      00:27:01

GGSCI (prodnew1.muthu.com as ggs_owner@PRODNEW) 8> start extract *

Sending START request to MANAGER ...
EXTRACT DPUMP11 starting

Sending START request to MANAGER ...
EXTRACT EXT11 starting

GGSCI (prodnew1.muthu.com as ggs_owner@PRODNEW) 9> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     DPUMP11     00:00:00      00:25:38
EXTRACT     RUNNING     EXT11       00:27:18      00:00:02

[oracle@prodnew1 golden]$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.1.2.1.0 OGGCORE_12.1.2.1.0_PLATFORMS_140727.2135.1_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Aug  7 2014 09:14:25
Operating system character set identified as UTF-8.

Copyright (C) 1995, 2014, Oracle and/or its affiliates. All rights reserved.

GGSCI (prodnew1.muthu.com) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     ABENDED     DPUMP11     00:00:00      00:28:17
EXTRACT     RUNNING     EXT11       00:00:00      00:00:03

GGSCI (prodnew1.muthu.com) 2> start EXTRACT DPUMP11

Sending START request to MANAGER ...
EXTRACT DPUMP11 starting

GGSCI (prodnew1.muthu.com) 3> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     DPUMP11     00:00:00      00:28:32
EXTRACT     RUNNING     EXT11       00:00:00      00:00:07

GGSCI (prodnew1.muthu.com) 4> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     DPUMP11     00:00:00      00:28:55
EXTRACT     RUNNING     EXT11       00:00:00      00:00:09

GGSCI (prodnew1.muthu.com) 5> stop mgr
Manager process is required by other GGS processes.
Are you sure you want to stop it (y/n)?y

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


GGSCI (prodnew1.muthu.com) 6> start mgr
Manager started.


GGSCI (prodnew1.muthu.com) 7> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     DPUMP11     00:00:00      00:29:50
EXTRACT     RUNNING     EXT11       00:00:00      00:00:01

GGSCI (prodnew1.muthu.com) 1> edit params mgr

GGSCI (prodnew1.muthu.com) 2> stop mgr
Manager process is required by other GGS processes.
Are you sure you want to stop it (y/n)?y

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


GGSCI (prodnew1.muthu.com) 3> start mgr
Manager started.


GGSCI (prodnew1.muthu.com) 4> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     ABENDED     DPUMP11     00:00:00      00:32:07
EXTRACT     RUNNING     EXT11       00:00:00      00:00:08


GGSCI (prodnew1.muthu.com) 5> start EXTRACT DPUMP11;
ERROR: EXTRACT DPUMP11; does not exist.


GGSCI (prodnew1.muthu.com) 6> start EXTRACT DPUMP11

Sending START request to MANAGER ...
EXTRACT DPUMP11 starting


GGSCI (prodnew1.muthu.com) 7> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     DPUMP11     00:00:00      00:32:23
EXTRACT     RUNNING     EXT11       00:00:00      00:00:02

GGSCI (prodnew1.muthu.com) 3> start EXTRACT DPUMP11

Sending START request to MANAGER ...
EXTRACT DPUMP11 starting


GGSCI (prodnew1.muthu.com) 4> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     DPUMP11     00:00:00      00:36:13
EXTRACT     RUNNING     EXT11       00:00:00      00:00:09


GGSCI (prodnew1.muthu.com) 5> exit

Please follow same procedure  ON TARGET


[oracle@prodnew golden]$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.1.2.1.0 OGGCORE_12.1.2.1.0_PLATFORMS_140727.2135.1_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Aug  7 2014 09:14:25
Operating system character set identified as UTF-8.

Copyright (C) 1995, 2014, Oracle and/or its affiliates. All rights reserved.

GGSCI (prodnew.muthu.com) 4> ADD REPLICAT rep11, EXTTRAIL /oracle/golden/dirdat/ra
REPLICAT added.

GGSCI (prodnew.muthu.com) 5> EDIT PARAMS rep11

*********************************************
EDIT PARAMS rep11
REPLICAT rep11
ASSUMETARGETDEFS
USERID ggs_owner, PASSWORD ggs_owner
HANDLECOLLISIONS
MAP scott.emp, TARGET scott.emp;
*********************************************

GGSCI (prodnew.muthu.com as ggs_owner@PRODNEW) 6> EDIT PARAMS ./GLOBALS

GGSCI (prodnew.muthu.com as ggs_owner@PRODNEW) 7> DBLOGIN USERID ggs_owner, PASSWORD ggs_owner
Successfully logged into database.

GGSCI (prodnew.muthu.com as ggs_owner@PRODNEW) 8> ADD CHECKPOINTTABLE GGS_OWNER.CHKPTAB

Successfully created checkpoint table GGS_OWNER.CHKPTAB.

[oracle@prodnew golden]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Thu Aug 13 16:57:44 2015

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @marker_setup
SQL> alter session set recyclebin=OFF;
SQL> @ddl_setup
SQL> @role_setup
SQL> grant ggs_ggsuser_role to ggs_owner;
SQL> @ddl_enable
SQL> @ddl_pin GGS_OWNER

[oracle@prodnew golden]$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.1.2.1.0 OGGCORE_12.1.2.1.0_PLATFORMS_140727.2135.1_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Aug  7 2014 09:14:25
Operating system character set identified as UTF-8.

Copyright (C) 1995, 2014, Oracle and/or its affiliates. All rights reserved.

GGSCI (prodnew.muthu.com) 2> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    STOPPED     REP11       00:00:00      00:14:12

GGSCI (prodnew.muthu.com) 3> start replicat REP11

Sending START request to MANAGER ...
REPLICAT REP11 starting


GGSCI (prodnew.muthu.com) 4> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING     REP11       00:00:00      00:00:00


GGSCI (prodnew.muthu.com) 5> exit


ON Source:
__________

START EXTRACT ext11
START EXTRACT dpump11

On Target
__________
grant create any table to ggs_owner;
START REPLICAT rep11

No comments: