Table of contents
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:
I have changed default installation directory to overwrite 12.1 by 12.2:
Summary:
Installation on-going:
If installation is successful:
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
- Oracle GoldenGate 12c Tutorial Oracle to Oracle Replication with Oracle Multitenant Version 12.1 (Doc ID 1960719.1)
- Oracle GoldenGate Best Practices: Instantiation from an Oracle Source Database (Doc ID 1276058.1)
- MGR report shows “WARNING OGG-01877 Missing explicit accessrule for server collector” or “WARNING OGG-00936 Access denied (request from
, rule #0).” (Doc ID 2098284.1) - Initial Direct Load Access Denied in Oracle GoldenGate 12.2
- Oracle GoldenGate for Windows and UNIX 12c (12.2.0.1)
- Oracle GoldenGate 12c New Features Overview
rehan says:
add trandata app1.test1
ERROR: The catalog name is missing. Fully qualified name for table app1.test1 is required for root database.
Yannick Jaquier says:
This is most probably an issue related to container and pluggable databases…
rehan says:
the database which you are using in above practical is it is 11g or 12c ?
Yannick Jaquier says:
Source and target database releases are written clearly in preamble section.