GoldenGate 12c (12.2) installation and configuration

 

Preamble

This is almost 5 years since I have tested GoldenGate 11.1. At that time we have not implemented GoldenGate in production but it might change with a new coming project. So decided to revisit the latest GoldenGate 12c (12.2.0.1.1) and see what has changed…

The Oracle GoldenGate capture process, on source system, is called Extract. The Oracle GoldenGate apply process, on target system, is called Replicat.

The Data Pump process generating local trail files is only recommended on source system but you can work without it.

If working with Oracle database then your databases must be 11gR2 or 12cR1. If using new integrated extract and replicat method then you must be in 11.2.0.4 or earlier.

My testing has been done using two virtual machines running Oracle Linux Server release 7.2 64 bits and GoldenGate 12c (12.2.0.1.1). My source database is 12cR1 (12.1.0.2.0) with NLS_CHARACTERSET set to US7ASCII and my target database is 12cR1 (12.1.0.2.0) with NLS_CHARACTERSET set to AL32UTF8. This strange choice of character set has been done to match one of our legacy environment where we plan to extract figures to an Unicode target database. All issues I had will be described in a further post…

GoldenGate 12c installation

The installation is no more a zip file to un-compress but graphical similar to what we have for Oracle database (runInstaller). So installing for Oracle database 12c:

goldengate12c01
goldengate12c01

I have changed default installation directory to overwrite 12.1 by 12.2:

goldengate12c02
goldengate12c02

Summary:

goldengate12c03
goldengate12c03

Installation on-going:

goldengate12c04
goldengate12c04

If installation is successful:

goldengate12c05
goldengate12c05

At then end of the installation GoldenGate 12c manager is already up and running.

Stop and restart GoldenGate 12c manager process wen Oracle database environment variables ORACLE_HOME and ORACLE_SID have been set unless you might get below error message:

2016-03-10 15:14:32  ERROR   OGG-02550  ORACLE_HOME is not set to Oracle software directory.
 
2016-03-10 15:14:32  ERROR   OGG-01668  PROCESS ABENDING.

Please note that if you have one GoldenGate installation for multiple databases this will not be possible and you will have to set those variables in the extract and replicat processes directly using:

SETENV (ORACLE_HOME = "path to Oracle home location")
SETENV (ORACLE_SID = "SID")

But this is not my case so, I take also the opportunity to add an automatic cleaning of trail files on target system manager process:

GGSCI (server2.domain.com) 1> 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 (server2.domain.com) 3> edit params mgr
 
 
 
GGSCI (server2.domain.com) 9> view params mgr
 
PORT 7809
PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS, MINKEEPFILES 2, FREQUENCYHOURS 6
 
GGSCI (server2.domain.com) 2> start mgr
Manager started.
 
GGSCI (server2.domain.com) 5> info mgr
 
Manager is running (IP port server2.domain.com.7809, Process ID 22773).

The purge old extracts files (trail files) command says I use a checkpoint to ensure the trail files have been proceeded, I keep 2 last trail files and the purge frequency is 6 hours.

Create subdirs command is no more needed as everything is already there…

On source database I create a test account and table:

SQL> CREATE USER app1
     IDENTIFIED BY secure_password
     DEFAULT TABLESPACE users
     TEMPORARY TABLESPACE temp;
 
USER created.
 
SQL> GRANT CONNECT, RESOURCE TO app1;
 
GRANT succeeded.
 
SQL> ALTER USER app1 quota unlimited ON users;
 
USER altered.
 
SQL> CREATE TABLE APP1.TEST1 (
     VAL NUMBER NOT NULL,
     DESCR VARCHAR2(50),
     CONSTRAINT TEST1_PK PRIMARY KEY (VAL) ENABLE);
 
TABLE created.
 
SQL> INSERT INTO APP1.TEST1 VALUES (1,'spring');
 
1 ROW created.
 
SQL> COMMIT;
 
COMMIT complete.
 
SQL> SELECT * FROM app1.test1 ORDER BY val;
 
       VAL DESCR
---------- --------------------------------------------------
         1 spring

I have also created the same alias on source and target servers:

[oracle@server1 ~]$ tnsping orcl
 
TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 11-MAR-2016 16:57:15
 
Copyright (c) 1997, 2014, Oracle.  All rights reserved.
 
Used parameter files:
/u01/app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora
 
 
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = server1.domain.com)(PORT = 1531)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (10 msec)

The source database is also in archivelog mode, while my target remains in noarchivelog mode.

GoldenGate 12c configuration

On source database few things must be done:

SQL> SELECT supplemental_log_data_min, force_logging FROM v$database;
 
SUPPLEME FORCE_LOGGING
-------- ---------------------------------------
NO       NO
 
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
 
DATABASE altered.
 
SQL> ALTER DATABASE FORCE LOGGING;
 
DATABASE altered.
 
SQL> ALTER SYSTEM SWITCH LOGFILE;
 
SYSTEM altered.
 
SQL> SELECT supplemental_log_data_min, force_logging FROM v$database;
 
SUPPLEME FORCE_LOGGING
-------- ---------------------------------------
YES      YES

This parameter must be changed on source and target databases:

SQL> ALTER SYSTEM SET enable_goldengate_replication=TRUE;
 
SYSTEM altered.

Creation of GoldenGate administrator account, on source:

SQL> CREATE USER ggadmin IDENTIFIED BY secure_password;
 
USER created.
 
SQL> GRANT CREATE SESSION, CONNECT, RESOURCE, ALTER SYSTEM TO ggadmin;
 
GRANT succeeded.
 
SQL> EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE(grantee=>'ggadmin', privilege_type=>'CAPTURE', grant_optional_privileges=>'*');
 
PL/SQL PROCEDURE successfully completed.

On target:

SQL> CREATE USER ggadmin IDENTIFIED BY secure_password;
 
USER created.
 
SQL> GRANT CREATE SESSION, CONNECT, RESOURCE, CREATE TABLE, dba, LOCK ANY TABLE TO ggadmin;
 
GRANT succeeded.
 
SQL> GRANT INSERT ANY TABLE, UPDATE ANY TABLE, DELETE ANY TABLE TO ggadmin;
 
GRANT succeeded.
 
SQL> EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE(grantee=>'ggadmin', privilege_type=>'APPLY', grant_optional_privileges=>'*');
 
PL/SQL PROCEDURE successfully completed.

I enable table-level supplemental logging for my test table, you must be connected to database to do it:

GGSCI (server1.domain.com) 4> dblogin, userid ggadmin@orcl, password secure_password
Successfully logged into database.
 
GGSCI (server1.domain.com as ggadmin@orcl) 23> add trandata app1.test1
ERROR: Error (942, ORA-00942: table or view does not exist) start select in getColAttrsFromCatalog.

So again the documentation is not correct and I already faced this issue five years back. In Oracle official tutorial they continue to grant DBA to GoldenGate admin account… Apparently the missing grant is:

SQL> GRANT SELECT ANY dictionary TO ggadmin;
GGSCI (server1.domain.com as ggadmin@orcl) 4> add trandata app1.test1
 
Logging of supplemental redo data enabled for table APP1.TEST1.
TRANDATA for scheduling columns has been added on table 'APP1.TEST1'.
TRANDATA for instantiation CSN has been added on table 'APP1.TEST1'.
GGSCI (server1.domain.com as ggadmin@orcl) 5> info trandata app1.test1
 
Logging of supplemental redo log data is enabled for table APP1.TEST1.
 
Columns supplementally logged for table APP1.TEST1: VAL.
 
Prepared CSN for table APP1.TEST1: 1917543

If you do not want to put password in extract/replicat parameter files you can use a GoldenGate 12c (12.1) new feature called credential store (do it on source and target system):

GGSCI (server1.domain.com as ggadmin@orcl) 6> add credentialstore
 
Credential store created in ./dircrd/.
 
GGSCI (server1.domain.com as ggadmin@orcl) 7> alter credentialstore add user ggadmin@orcl alias ggadmin
Password:
 
Credential store in ./dircrd/ altered.
 
GGSCI (server1.domain.com as ggadmin@orcl) 8> info credentialstore
 
Reading from ./dircrd/:
 
Default domain: OracleGoldenGate
 
  Alias: ggadmin
  Userid: ggadmin@orcl

After this credential store creation you can connect simply using, same in extract and replicat processes parameter files:

GGSCI (server1.domain.com as ggadmin@orcl) 9> dblogin useridalias ggadmin
Successfully logged into database.

GoldenGate 12c initial loading

On source database get current System Change Number (SCN):

SQL> SELECT current_scn FROM v$database;
 
CURRENT_SCN
-----------
    2112154

Create initial loading capture process parameter file:

GGSCI (server1.domain.com) 1> edit params capture1
 
 
 
GGSCI (server1.domain.com) 3> view params capture1
 
extract capture1
useridalias ggadmin
rmthost server2.domain.com, mgrport 7809
rmttask replicat, group apply1
table app1.test1, sqlpredicate 'as of scn 2112154';

On target create the skeleton of table with no figures. This can be done with a creation script or with Data Pump using CONTENT=METADATA_ONLY when importing figures…

Create below replicat process parameter file:

GGSCI (server2.domain.com) 1> edit params apply1
 
 
 
GGSCI (server2.domain.com) 2> view params apply1
 
replicat apply1
assumetargetdefs
useridalias ggadmin
map app1.test1, target app1.test1;

Add extract process on source system

GGSCI (server1.domain.com) 5> add extract capture1, sourceistable
EXTRACT added.

Add replicat process on target system:

GGSCI (server2.domain.com) 3> add replicat apply1, specialrun
REPLICAT added.

Start capture process on source system:

GGSCI (server1.domain.com) 6> start extract capture1
 
Sending START request to MANAGER ...
EXTRACT CAPTURE1 starting
 
 
GGSCI (server1.domain.com) 7> view report capture1
.
.
.
Source Context :
  SourceModule            : [er.idlx]
  SourceID                : [/scratch/aime/adestore/views/aime_adc4150408/oggcore/OpenSys/src/app/er/idlx.c]
  SourceFunction          : [idlx_get_port_from_remote_mgr]
  SourceLine              : [1443]
  ThreadBacktrace         : [13] elements
                          : [/u01/app/oracle/product/12.2.0/oggcore_1/libgglog.so(CMessageContext::AddThreadContext()+0x1b) [0x7f861eb9d0bb]]
                          : [/u01/app/oracle/product/12.2.0/oggcore_1/libgglog.so(CMessageFactory::CreateMessage(CSourceContext*, unsigned int, ...)+0x134) [0x7f861eb97254]]
                          : [/u01/app/oracle/product/12.2.0/oggcore_1/libgglog.so(_MSG_ERR_IDLX_MGR_ERROR(CSourceContext*, char const*, CMessageFactory::MessageDisposition)+0x30) [0x7f861eb79da7]]
                          : [/u01/app/oracle/product/12.2.0/oggcore_1/extract(IDLX_tcp_send_data+0x61d) [0x5a5ba1]]
                          : [/u01/app/oracle/product/12.2.0/oggcore_1/extract(IDLX_send_session_begin_cmd+0xc0) [0x5a5f77]]
                          : [/u01/app/oracle/product/12.2.0/oggcore_1/extract(ggs::er::DatabaseDataSource::processSourceTables()+0xfe) [0x5fe2ce]]
                          : [/u01/app/oracle/product/12.2.0/oggcore_1/extract() [0x603d24]]
                          : [/u01/app/oracle/product/12.2.0/oggcore_1/extract(ggs::gglib::MultiThreading::MainThread::ExecMain()+0x60) [0x6e05e0]]
                          : [/u01/app/oracle/product/12.2.0/oggcore_1/extract(ggs::gglib::MultiThreading::Thread::RunThread(ggs::gglib::MultiThreading::Thread::ThreadArgs*)+0x14d) [0x6e17cd]]
                          : [/u01/app/oracle/product/12.2.0/oggcore_1/extract(ggs::gglib::MultiThreading::MainThread::Run(int, char**)+0xb1) [0x6e18b1]]
                          : [/u01/app/oracle/product/12.2.0/oggcore_1/extract(main+0x3b) [0x60500b]]
                          : [/lib64/libc.so.6(__libc_start_main+0xf5) [0x7f86168b0b15]]
                          : [/u01/app/oracle/product/12.2.0/oggcore_1/extract() [0x573069]]
 
2016-03-10 15:23:41  ERROR   OGG-01201  Error reported by MGR : Access denied.
 
2016-03-10 15:23:41  ERROR   OGG-01668  PROCESS ABENDING.

On target system mgr is reporting:

GGSCI (server2.domain.com) 8> view report mgr
.
.
.
***********************************************************************
**                     Run Time Messages                             **
***********************************************************************
 
 
2016-03-10 15:23:31  WARNING OGG-01877  Missing explicit accessrule for server collector.
 
2016-03-10 15:23:31  INFO    OGG-00960  Access granted (rule #8).
 
2016-03-10 15:23:31  INFO    OGG-00983  Manager started (port 7809).
 
2016-03-10 15:23:40  INFO    OGG-00963  Command received from EXTRACT on host [192.168.56.101]:25300 (START REPLICAT APPLY1 CPU -1 PRI -1 PARAMS ).
 
2016-03-10 15:23:40  WARNING OGG-00936  Access denied (request from 192.168.56.101, rule #0).

So modify mgr parameters to add an access rule:

GGSCI (server2.domain.com) 9> edit params mgr
 
 
 
GGSCI (server2.domain.com) 10> view params mgr
 
port 7809
accessrule, prog *, ipaddr 192.168.56.101, allow
purgeoldextracts ./dirdat/*, usecheckpoints, minkeepfiles 2, frequencyhours 6
 
 
GGSCI (server2.domain.com) 11> 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 (server2.domain.com) 12> start mgr
Manager started.

On target system:

GGSCI (server2.domain.com) 16> view report apply1
.
.
.
***********************************************************************
**                     Run Time Messages                             **
***********************************************************************
 
 
2016-03-10 15:40:07  INFO    OGG-03522  Setting session time zone to source database time zone 'GMT'.
 
2016-03-10 15:40:07  WARNING OGG-02760  ASSUMETARGETDEFS is ignored because trail file  contains table definitions.
 
2016-03-10 15:40:07  INFO    OGG-06505  MAP resolved (entry app1.test1): MAP "APP1"."TEST1", TARGET app1.test1.
 
2016-03-10 15:40:15  INFO    OGG-02756  The definition for table APP1.TEST1 is obtained from the trail file.
 
2016-03-10 15:40:15  INFO    OGG-06511  Using following columns in default map by name: VAL, DESCR.
 
2016-03-10 15:40:15  INFO    OGG-06510  Using the following key columns for target table APP1.TEST1: VAL.
 
 
2016-03-10 15:40:15  INFO    OGG-03010  Performing implicit conversion of column data from character set us7ascii to UTF-8.

And finally on target system (note for myself: wait few seconds before starting to investigate what’s wrong):

SQL> SELECT * FROM app1.test1;
 
       VAL DESCR
---------- --------------------------------------------------
         1 spring

GoldenGate 12c apply change data

I insert a new row in my test table to validate my settings:

SQL> INSERT INTO app1.test1 VALUES (2,'summer');
 
1 ROW created.
 
SQL> COMMIT;
 
COMMIT complete.
 
SQL> SELECT * FROM app1.test1 ORDER BY val;
 
       VAL DESCR
---------- --------------------------------------------------
         1 spring
         2 summer

I will keep the same extract and replicat name, I will just update them to act on an on-going flow of updates:

On source system:

GGSCI (server1.domain.com) 17> edit params capture1
 
 
 
GGSCI (server1.domain.com) 18> view params capture1
 
extract capture1
useridalias ggadmin
rmthost server2.domain.com, mgrport 7809
rmttrail ./dirdat/rt
ddl include mapped
ddloptions report
logallsupcols
updaterecordformat compact
table app1.test1;

On target system:

GGSCI (server2.domain.com) 11> edit params apply1
 
 
 
GGSCI (server2.domain.com) 12> view params apply1
 
replicat apply1
useridalias ggadmin
assumetargetdefs
discardfile ./dirrpt/apply1.dsc, purge, megabytes 10
ddl include mapped
ddloptions report
map app1.test1, target app1.test1;

Register the extract process while connected to database:

GGSCI (server1.domain.com) 8> dblogin useridalias ggadmin
Successfully logged into database.
 
GGSCI (server1.domain.com as ggadmin@orcl) 9> register extract capture1 database
 
2016-03-11 11:55:12  ERROR   OGG-08221  Cannot register or unregister EXTRACT CAPTURE1 because of the following SQL error: OCI Error 1,950.

Which is AGAIN a problem of privileges despite I have carefully followed official documentation. Everything is made to force you to grant DBA to your GoldenGate administrator account…

Error is explained in User’s Privilege Issue On Goldengate12c to Register Extract (Doc ID 2017252.1) MOS document, you have to grant quota on default tablespace of your GoldenGate administrator account on both systems. Yes, few objects will be created:

SQL> GRANT unlimited TABLESPACE TO ggadmin;
 
GRANT succeeded.

On source system:

GGSCI (server1.domain.com as ggadmin@orcl) 29> add extract capture1, integrated tranlog, scn 2112154
EXTRACT (Integrated) added.
 
 
GGSCI (server1.domain.com as ggadmin@orcl) 30> add rmttrail ./dirdat/rt, extract capture1, megabytes 10
RMTTRAIL added.
 
GGSCI (server1.domain.com as ggadmin@orcl) 31> start extract capture1
 
Sending START request to MANAGER ...
EXTRACT CAPTURE1 starting
 
 
GGSCI (server1.domain.com as ggadmin@orcl) 33> info all
 
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
 
MANAGER     RUNNING
EXTRACT     RUNNING     CAPTURE1    00:00:10      00:00:09

On target system:

GGSCI (server2.domain.com) 4> delete replicat apply1
Deleted REPLICAT APPLY1.
 
 
GGSCI (server2.domain.com) 5> add replicat apply1, integrated, exttrail ./dirdat/rt
REPLICAT (Integrated) added.
 
 
GGSCI (server2.domain.com) 6> start replicat apply1, aftercsn 2112154
 
Sending START request to MANAGER ...
REPLICAT APPLY1 starting
 
 
GGSCI (server2.domain.com) 7> info all
 
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
 
MANAGER     RUNNING
REPLICAT    RUNNING     APPLY1      00:00:00      00:00:42

On target Oracle database:

SQL> SELECT * FROM app1.test1 ORDER BY val;
 
       VAL DESCR
---------- --------------------------------------------------
         1 spring
         2 summer

And you can continue inserting in source table…

References

This entry was posted in Oracle and tagged . Bookmark the permalink.

One thought on “GoldenGate 12c (12.2) installation and configuration

Leave a Reply

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

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>