How to Install and Setup Oracle GoldenGate 12c Environment on Linux

  • by
Suppose that you have two Oracle database 12c installed on the servers. One is for the source database (ora12c1), the other is the target database (ora12c2). After that, you should perform an initial loading for schemas that are mapped later.

Before installing OGG 12.2, we have to prepare the environment variables for OGG. In this case, we use oracle as the operational account of OGG.
[oracle@ora12c1 ~]$ mkdir -p /u01/app/ogg
[oracle@ora12c1 ~]$ vi .bash_profile
...
OGG_HOME=/u01/app/ogg
LD_LIBRARY_PATH=$ORACLE_HOME/lib:$OGG_HOME

...
export PATH ORACLE_BASE ORACLE_HOME ORACLE_SID OGG_HOME LD_LIBRARY_PATH
[oracle@ora12c1 ~]$ . .bash_profile

There're two important variables for OGG in the profile, one is $OGG_HOME, the other is $LD_LIBRARY_PATH.

Next, we can install OGG 12.2.
[oracle@ora12c1 ~]$ cd /tmp
[oracle@ora12c1 tmp]$ unzip -q fbo_ggs_Linux_x64_shiphome.zip
[oracle@ora12c1 tmp]$ cd fbo_ggs_Linux_x64_shiphome/Disk1/
[oracle@ora12c1 Disk1]$ ./runInstaller

Select the database version. In this case, we choose 12c.

Specify $OGG_HOME and $ORACLE_HOME

Review the pre-installation summary.

Save the response file.

Installing...

Installation is completed.

Configure Oracle database for OGG
[oracle@ora12c1 ~]$ cd $OGG_HOME
[oracle@ora12c1 ogg]$ sqlplus / as sysdba
...
SQL> startup mount;
...
SQL> alter database archivelog;

Database altered.

SQL> alter database add supplemental log data;

Database altered.

SQL> alter database force logging;

Database altered.

SQL> column force_logging format a5;
SQL> column supplemental_log_data_min format a5;
SQL> select force_logging, supplemental_log_data_min from v$database;

FORCE SUPPL
----- -----
YES   YES

SQL> alter database open;

Database altered.

SQL> create tablespace ogg_tbs datafile '/u01/app/oracle/oradata/ora12c/ogg_tbs01.dbf' size 1m autoextend on next 1m maxsize unlimited;

Tablespace created.

SQL> create user ogg identified by ogg123 default tablespace ogg_tbs quota unlimited on ogg_tbs;

User created.

You don't have to grant any privileges, because the later SQL will grant roles to it.

Execute setup SQL scripts for OGG
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:ogg


Marker setup table script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to OGG

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:ogg

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

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

LOCATION OF DDL TRACE FILE
------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/product/12.1.0.2/db_1/rdbms/log/ggs_ddl_trace.log

Analyzing installation status...


VERSION OF DDL REPLICATION
------------------------------------------------------------------------------------------------------------------------
OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401

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:ogg
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> @ddl_enable.sql

Trigger altered.

SQL> @ddl_pin ogg

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.

SQL> alter system set enable_goldengate_replication=true scope=both;

System altered.

After we installed OGG 12.2, we should start the manager first.
[oracle@ora12c1 ogg]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Dec 12 2015 02:56:48
Operating system character set identified as UTF-8.

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



GGSCI (ora12c1.example.com) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     STOPPED


GGSCI (ora12c1.example.com) 2> start MANAGER
Manager started.


GGSCI (ora12c1.example.com) 3> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING

Configure an EXTRACT for collecting local REDO TRAIL of the source database.
GGSCI (ora12c1.example.com) 4> add extract erpapp_e, tranlog, begin now
EXTRACT added.


GGSCI (ora12c1.example.com) 5> add exttrail ./dirdat/et, extract erpapp_e, megabytes 50
EXTTRAIL added.

GGSCI (ora12c1.example.com) 6> edit params erpapp_e

extract erpapp_e
setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
userid ogg, password ogg123
exttrail ./dirdat/et
ddl include all
table hr.*;
table sh.*;


GGSCI (ora12c1.example.com) 7> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     STOPPED     ERPAPP_E    00:00:00      00:00:45


GGSCI (ora12c1.example.com) 8> start ERPAPP_E

Sending START request to MANAGER ...
EXTRACT ERPAPP_E starting


GGSCI (ora12c1.example.com) 9> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     ERPAPP_E    00:00:00      00:00:00

Configure an EXTRACT for transporting local REDO TRAIL of the source database to the target database. Actually, it's a data pump processor.
GGSCI (ora12c1.example.com) 10> add extract erpapp_p, exttrailsource ./dirdat/et, begin now
EXTRACT added.


GGSCI (ora12c1.example.com) 11> add rmttrail ./dirdat/pt, extract erpapp_p, megabytes 50
RMTTRAIL added.

GGSCI (ora12c1.example.com) 12> edit params erpapp_p

extract erpapp_p
setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
rmthost ora12c2, mgrport 7809
rmttrail ./dirdat/pt
passthru
ddl include all
table hr.*;
table sh.*;


GGSCI (ora12c1.example.com) 13> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     ERPAPP_E    00:00:00      00:00:08
EXTRACT     STOPPED     ERPAPP_P    00:00:00      00:00:32


GGSCI (ora12c1.example.com) 14> start ERPAPP_P

Sending START request to MANAGER ...
EXTRACT ERPAPP_P starting


GGSCI (ora12c1.example.com) 15> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     ERPAPP_E    00:00:00      00:00:05
EXTRACT     RUNNING     ERPAPP_P    00:00:00      00:00:03

Don't forget to add supplemental logs for the mapping schemas.
GGSCI (ora12c1.example.com) 16> dblogin userid ogg, password ogg123
Successfully logged into database.

GGSCI (ora12c1.example.com as ogg@ora12c1) 17> add schematrandata hr
...
GGSCI (ora12c1.example.com as ogg@ora12c1) 18> add schematrandata sh
...

Now, let's go to the target database and configure a REPLICAT for receiving and applying REDO TRAIL transported from the source database.

First, start the manager.
[oracle@ora12c2 ogg]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Dec 12 2015 02:56:48
Operating system character set identified as UTF-8.

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



GGSCI (ora12c2.example.com) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     STOPPED


GGSCI (ora12c2.example.com) 2> start MANAGER
Manager started.


GGSCI (ora12c2.example.com) 3> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING

Add and configure a REPLICAT
GGSCI (ora12c2.example.com) 4> dblogin userid ogg, password ogg123
Successfully logged into database.

GGSCI (ora12c2.example.com as ogg@ora12c2) 5> add checkpointtable ogg.chkpt

Successfully created checkpoint table ogg.chkpt.

GGSCI (ora12c2.example.com as ogg@ora12c2) 6> add replicat erpapp_r, exttrail ./dirdat/pt, begin now, checkpointtable ogg.chkpt
REPLICAT added.


GGSCI (ora12c2.example.com as ogg@ora12c2) 7> edit params erpapp_r

replicat erpapp_r
assumetargetdefs
userid ogg, password ogg123
discardfile ./dirrpt/erpapp_r.dsc, append
ddl include all
map hr.*, target hr.*;
map sh.*, target sh.*;


GGSCI (ora12c2.example.com as ogg@ora12c2) 8> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    STOPPED     ERPAPP_R    00:00:00      00:00:36


GGSCI (ora12c2.example.com as ogg@ora12c2) 9> start ERPAPP_R

Sending START request to MANAGER ...
REPLICAT ERPAPP_R starting


GGSCI (ora12c2.example.com as ogg@ora12c2) 10> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING     ERPAPP_R    00:00:00      00:00:01

Now, we have installed and setup the Oracle GoldenGate environment.

Leave a Reply

Your email address will not be published. Required fields are marked *