In this article I will show the steps to set up a very simple schema replication using the latest Oracle Golden Gate software, which is 19.1 at the time of writing. Although the Microservices Architecture has been released, I will be implementing an Oracle-to-Oracle one-way replication, using the "Classic Architecture".
Servers involved:
Source server: db-server1.oric.no
Source database: td1
Oracle RDBMS version 12.2.0.1
Oracle RDBMS installation path: /u01/oracle/product/12201
Software owner: oracle
Destination server: db-server2.oric.no
Destination database: td2
Oracle RDBMS version 19.0
Oracle RDBMS installation path: /u01/oracle/product/19x00
Software owner: oracle
Source database: td1
Oracle RDBMS version 12.2.0.1
Oracle RDBMS installation path: /u01/oracle/product/12201
Software owner: oracle
Destination server: db-server2.oric.no
Destination database: td2
Oracle RDBMS version 19.0
Oracle RDBMS installation path: /u01/oracle/product/19x00
Software owner: oracle
The steps in this article are based on source from the Oracle documentation available online, as well as Document ID 1276058.1 available support.oracle.com
Step 1: Download the software from otn.oracle.com
Step 2: Create a temporary directory on your servers:
su - oracle mkdir -p /u01/oracle/tmp/softwareStep 3: Upload the software to both of your servers
Step 4: Change directory to your temporary directory and unpack the GG software on each server
cd /u01/oracle/tmp/software unzip 191004_fbo_ggs_Linux_x64_shiphome.zipStep 5: Start an X-server on your client, like Xming
Step 6: Configure your servers to forward X-windows.
If xauth is not installed, you may need to install it as root with yum install xauth and then follow the instructions in this post
Step 7: Start the installer:
cd /u01/oracle/tmp/software/fbo_ggs_Linux_x64_shiphome/Disk1 ./runInstaller
Follow the installation wizard. It is a very simple procedure consisting of 4 screens. The installation will be identical on both serveres except for the first screen, which needs to be adapted to the database version running on the server you're installing on:
When the installation is finished, you will see lots of subdirectories in the Golden Gate installation directory, such as dirchk,dirdat,dirprm etc
Step 8 (optional): Create an environment file for the golden gate software, for convenience:
cd vi .gg191 # add the following lines to the file .gg191:export ORACLE_BASE=/u01/oracle export ORACLE_SID=td1 export ORACLE_UNQNAME=td1 export TNS_ADMIN=$ORACLE_HOME/network/admin export ORACLE_HOME_LISTNER=$ORACLE_HOME PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$JAVA_HOME/bin export PATH export CDPATH=.:$HOME:$ORACLE_HOME:$ORACLE_BASE:$ORACLE_BASE/admin/$ORACLE_SID:$ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID DISPLAY=localhost:10.0 export DISPLAY export GGHOME=$ORACLE_BASE/product/gg191 export ORACLE_GG=$GGHOME export LIBPATH=$ORACLE_HOME/lib:$GGHOME:/lib:/usr/lib export LD_LIBRARY_PATH=$LIBPATH export PATH=$GGHOME:$GGHOME/OPatch:$ORACLE_HOME/bin:/usr/bin:/bin:/etc:/usr/sbin:/usr/ucb:$HOME/bin:/usr/bin/X11:/sbin:/usr/java14/bin:. set -o vi clear echo "OH is : $ORACLE_HOME" echo "GG is : $GGHOME" cd $GGHOME;pwd
Step 9: Prepare the database for integrated capture mode
a) Implement forced logging and minimal supplemental logging for your source database:
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; SQL> ALTER DATABASE FORCE LOGGING; SQL> SELECT supplemental_log_data_min, force_logging FROM v$database; SUPPLEME FORCE_LOGGING -------- --------------- YES YES
b) Enable golden gate replication in both your source and target database instance:
SQL> alter system set ENABLE_GOLDENGATE_REPLICATION=true scope=both; System altered.b) Create the Golden Gate user:
create bigfile tablespace ogg_data
datafile '/oradata/ogg_data.dbf' SIZE 128M AUTOEXTEND ON NEXT 32M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;
CREATE USER OGG
IDENTIFIED BY mypassword
DEFAULT TABLESPACE OGG_DATA
TEMPORARY TABLESPACE TEMP
PROFILE NOEXP
ACCOUNT UNLOCK;
-- 4 Roles for OGG
GRANT CONNECT TO OGG;
GRANT RESOURCE TO OGG;
GRANT SELECT_CATALOG_ROLE TO OGG;
ALTER USER OGG DEFAULT ROLE ALL;
GRANT ALTER ANY TABLE TO OGG;
GRANT ALTER SYSTEM TO OGG;
GRANT ALTER SESSION TO OGG;
GRANT BECOME USER TO OGG;
GRANT SELECT ANY DICTIONARY TO OGG;
GRANT SELECT ANY TABLE TO OGG;
GRANT SELECT ANY TRANSACTION TO OGG;
GRANT UNLIMITED TABLESPACE TO OGG;
-- base privileges for Oracle 11.2.0.4 and later
exec dbms_goldengate_auth.grant_admin_privilege('OGG','CAPTURE');
c) Edit both of the servers' GLOBALS file. This is done in the ggsci cli interface. The parameter added here is the GGSCHEMA, which specifies the schema containing the database objects that are owned by Oracle GoldenGate. Here I am showing how I did it:oracle@db-server1.oric.no # ggsci Oracle GoldenGate Command Interpreter for Oracle Version 19.1.0.0.4 OGGCORE_19.1.0.0.0_PLATFORMS_191017.1054_FBO Linux, x64, 64bit (optimized), Oracle 12c on Oct 18 2019 01:38:51 Operating system character set identified as UTF-8. Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved. GGSCI (db-server1.oric.no) 1> edit params ./GLOBALSAdd the following line, then save and close it:
GGSCHEMA oggd) Configure connection types for integrated processes. I will use a USERALIAS, which is based on the Oracle Credential Store Framework (CSF).
oracle@db-server1.oric.no # ggsci Oracle GoldenGate Command Interpreter for Oracle Version 19.1.0.0.4 OGGCORE_19.1.0.0.0_PLATFORMS_191017.1054_FBO Linux, x64, 64bit (optimized), Oracle 12c on Oct 18 2019 01:38:51 Operating system character set identified as UTF-8. Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved. GGSCI (db-server1.oric.no) 1> add credentialstore Credential store created. GGSCI (db-server1.oric.no) 2> alter credentialstore add user OGG, password mypassword alias ggadmin Credential store altered. #test it GGSCI (db-server1.oric.no) 4> dblogin useridalias ggadmin Successfully logged into database.
Repeat the same procedure on the target host.
e) Since I want DDL to be replicated, we need to enable schema-level supplemental logging:
GGSCI (db-server1.oric.no as OGG@td1) 6> add schematrandata SCOTT 2020-02-26 10:45:12 INFO OGG-01788 SCHEMATRANDATA has been added on schema "SCOTT". 2020-02-26 10:45:12 INFO OGG-01976 SCHEMATRANDATA for scheduling columns has been added on schema "SCOTT". 2020-02-26 10:45:12 INFO OGG-10154 Schema level PREPARECSN set to mode NOWAIT on schema "SCOTT".
The source server should now be ready for extract.
10. Prepare the destination database for integrated apply
a) create the Golden Gate database user.
Note: The DBA role seems to be important to make DDL replication work
create bigfile tablespace ogg_data datafile '/oradata/td2/ogg_data.dbf' SIZE 128M AUTOEXTEND ON NEXT 32M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;
CREATE USER OGG
IDENTIFIED BY mypassword
DEFAULT TABLESPACE OGG_DATA
TEMPORARY TABLESPACE TEMP
PROFILE NOEXP
ACCOUNT UNLOCK;
-- 5 Roles for OGG
GRANT CONNECT TO OGG;
GRANT RESOURCE TO OGG;
GRANT SELECT_CATALOG_ROLE TO OGG;
GRANT DBA TO OGG;
ALTER USER OGG DEFAULT ROLE ALL;
GRANT ALTER ANY TABLE TO OGG;
GRANT ALTER SYSTEM TO OGG;
GRANT ALTER SESSION TO OGG;
GRANT BECOME USER TO OGG;
GRANT SELECT ANY DICTIONARY TO OGG;
GRANT SELECT ANY TABLE TO OGG;
GRANT SELECT ANY TRANSACTION TO OGG;
GRANT UNLIMITED TABLESPACE TO OGG;
grant insert any table to ogg;
grant update any table to ogg;
grant delete any table to ogg;
-- base privileges for Oracle 11.2.0.4 and later
exec dbms_goldengate_auth.grant_admin_privilege('OGG','apply');
exit
11. Instantiate the replication environment by running an initial load
There are different methods available for loading an empty target database with data from the source. They are
- Load using Data Pump
- Loading Data from File to Replicat
- Loading Data with an Oracle GoldenGate Direct Load
- Loading Data with a Direct Bulk Load to SQL*Loader
I will use method number 1 in this article. With this method, you do not have to set up any initial-load processes on the target.
You can use either a full export or an schema export.
Check that the source server is ready for instantiation:
GGSCI (db-server1.oric.no as OGG@td1) 10> INFO SCHEMATRANDATA SCOTT 2020-02-27 09:15:52 INFO OGG-06480 Schema level supplemental logging, excluding non-validated keys, is enabled on schema "SCOTT". 2020-02-27 09:15:52 INFO OGG-01980 Schema level supplemental logging is enabled on schema "SCOTT" for all scheduling columns. 2020-02-27 09:15:52 INFO OGG-10462 Schema "SCOTT" have 23 prepared tables for instantiation.The last line shown above confirms the database has been prepared for instantiation.
12. Create a Manager parameter file on the source server
GGSCI (db-server1.oric.no as OGG@td1) 16> edit params mgr PORT 7809 DYNAMICPORTLIST 7802-7803 PURGEOLDEXTRACTS ./dirdat/et* , USECHECKPOINTS, MINKEEPHOURS 24, FREQUENCYMINUTES 60 AUTORESTART EXTRACT *, RETRIES 3, WAITMINUTES 10, RESETMINUTES 60 AUTOSTART EXTRACT * DOWNREPORTMINUTES 15 LAGCRITICALSECONDS 10 LAGINFOMINUTES 0 LAGREPORTMINUTES 15
13. Create an extract parameter file on the source server
GGSCI (db-server1.oric.no as OGG@td1) 20> edit params vkext
EXTRACT vkext --- User login USERIDALIAS ggadmin DISCARDFILE ./dirrpt/vkapps.dsc, APPEND DISCARDROLLOVER AT 01:00 ON SUNDAY EXTTRAIL ./dirdat/et REPORT AT 01:00 STATOPTIONS REPORTFETCH REPORTCOUNT every 10 minutes, RATE REPORTROLLOVER AT 01:15 ON SUNDAY --- DDL Parameters DDL INCLUDE MAPPED DDLOPTIONS REPORT TABLE SCOTT.* ;
14. Create an Extract Pump parameter file on the source server
GGSCI (db-server1.oric.no as OGG@td1) 23> edit params vkpump
EXTRACT VKPUMP USERIDALIAS ggadmin RMTHOST db-server2.oric.no, MGRPORT 7809 RMTTRAIL ./dirdat/rt ddl include mapped ddloptions report TABLE SCOTT.* ;
15. Register the extracts:
GGSCI (db-server1.oric.no as OGG@td1) 27> dblogin useridalias ggadmin Successfully logged into database. GGSCI (db-server1.oric.no as OGG@td1) 28> register extract vkext, database 2020-02-27 11:59:21 INFO OGG-02003 Extract VKEXT successfully registered with database at SCN 55440705. GGSCI (db-server1.oric.no as OGG@td1) 29> add extract vkext, integrated tranlog, begin now EXTRACT (Integrated) added. GGSCI (db-server1.oric.no as OGG@td1) 31> add exttrail ./dirdat/et, extract vkext, megabytes 128 EXTTRAIL added. GGSCI (db-server1.oric.no as OGG@td1) 32> add extract vkpump, exttrailsource ./dirdat/et EXTRACT added. GGSCI (db-server1.oric.no as OGG@td1) 34> add rmttrail ./dirdat/rt, extract vkpump, megabytes 128 RMTTRAIL added.
16. Create the parameter file for the manager on the target server:
GGSCI (db-server2.oric.no) 4> edit params mgr
PORT 7809 DYNAMICPORTLIST 7802-7803 PURGEOLDEXTRACTS ./dirdat/rt* , USECHECKPOINTS, MINKEEPHOURS 72 AUTORESTART REPLICAT *, RETRIES 3, WAITMINUTES 10, RESETMINUTES 60 AUTOSTART REPLICAT * DOWNREPORTMINUTES 15 LAGCRITICALSECONDS 10 LAGINFOMINUTES 0
17. Create the parameter file for the replicat process on the target server:
GGSCI (db-server2.oric.no) 1> dblogin useridalias ggadmin Successfully logged into database. GGSCI (db-server2.oric.no as OGG@td2) 3> edit params vkrepl
REPLICAT vkrepl DISCARDFILE ./dirrpt/vkrepl.dsc, APPEND USERIDALIAS ggadmin REPORTCOUNT EVERY 30 MINUTES, RATE REPORTROLLOVER AT 01:00 ON SUNDAY DISCARDROLLOVER AT 01:00 ON SUNDAY --- DDL Parameters DDL INCLUDE ALL DDLOPTIONS REPORT MAP SCOTT.*, TARGET SCOTT.*;
18. On the target, create a checkpoint table:
GGSCI (db-server2.oric.no as OGG@td2) 7> add checkpointtable ogg.chktbl Successfully created checkpoint table ogg.chktbl.
19. On the target, start the manager
GGSCI (db-server2.oric.no as OGG@td2) 7> start mgr
20. On the target, add the replicate process and trail files:
GGSCI (db-server2.oric.no as OGG@td2) 13> add replicat vkrepl, integrated, exttrail ./dirdat/rt, checkpointtable ogg.chktbl REPLICAT (Integrated) added.
21. Start all of the Golden Gate processes on both servers before target instantiation:
# source GGSCI (db-server1.oric.no as OGG@td1) 15> start mgr
# target GGSCI (db-server2.oric.no as OGG@td2) 15> start mgr
If this doesn't bring up all processes, check the file $GGHOME/ggserr.log for errors. Correct the errors as indicated. You could see a number of errors at this point, from a misspelled keyword, an incorrectly specified group name, to an incorrectly specified port number.
When ready to try again, you can issue start of individual golden gate processes, like this:
When ready to try again, you can issue start of individual golden gate processes, like this:
GGSCI (db-server1.oric.no as OGG@td1) 15> start extract *or
GGSCI (db-server1.oric.no as OGG@td1) 15> start extract vkext | vkpump
20. On the source server db-server1.oric.no, export the schema I'd like replicated:
a) create a directory called DATA_PUMP_DIR
create or replace directory DATA_PUMP_DIR as '/data/dpdir'; grant read,write on directory DATA_PUMP_DIR to system;
b) create a parameter file for data pump export. Add the following lines to the file expdp_instantinate.par
userid=system directory=DATA_PUMP_DIR schemas=SCOTT dumpfile=instantiate_scott.dmp logfile=instantiate_scott.log
c) export the data
expdp parfile=instantinate.par
21. On the target server create a directory similar to he one in step 20a.
22. Transfer the dumpfile to the target server db-server2.oric.no
23. Create a parameter file for data pump import. Add the following lines to the file impdp_instantinate.par:
userid=system directory=DATA_PUMP_DIR dumpfile=instantiate_scott.dmp logfile=imp_instantiate_scott4.log job_name=instantiate_scott remap_tablespace=DOCS_2014:USERS remap_tablespace=DOCS_2015:USERS remap_tablespace=DOCS_2016:USERS remap_tablespace=SCOTT_DATA:USERS remap_tablespace=DOCS_DATA:USERS remap_tablespace=LOB_DATA:USERS table_exists_action=SKIP exclude=statistics
As you can see from the above directives, I am taking the opportunity to consolidate all my replicated data into one single tablespace, USERS.
24. Test your replication.
Insert a row into your source database td1, and check if's being transferred over the network to the target database t2.
Insert into COUNTRY_TABLE
(ID, DYEAR, COUNTRY, CREATED, CREATED_BY,
LAST_CHANGED, LAST_CHANGED_BY, VERSION)
Values
('443hy70-s421-11x5-8031-0054567837649', 2018, 'Japan', TO_TIMESTAMP('02/01/2018 00:00:00,000000','DD/MM/YYYY HH24:MI:SS,FF'), 'BOB', TO_TIMESTAMP('07/03/2019 10:56:18,563307','DD/MM/YYYY HH24:MI:SS,FF'), 'JIM', 4);
COMMIT;
Tail of the $GGHOME/ggserr.log file on the target server db-server2.oric.no shows:
2020-02-27T15:14:27.981+0100 INFO OGG-02243 Oracle GoldenGate Delivery for Oracle, vkrepl.prm: Opened trail file /u01/oracle/product/gg191/dirdat/rt000000000 at 2020-02-27 15:14:27.981153. 2020-02-27T15:14:27.981+0100 INFO OGG-03506 Oracle GoldenGate Delivery for Oracle, vkrepl.prm: The source database character set, as determined from the trail file, is UTF-8. 2020-02-27T15:14:27.981+0100 INFO OGG-06506 Oracle GoldenGate Delivery for Oracle, vkrepl.prm: Wildcard MAP resolved (entry SCOTT.*): MAP "SCOTT"."COUNTRY_TABLE", TARGET SCOTT."COUNTRY_TABLE". 2020-02-27T15:14:27.992+0100 INFO OGG-02756 Oracle GoldenGate Delivery for Oracle, vkrepl.prm: The definition for table SCOTT.COUNTRY_TABLE is obtained from the trail file. 2020-02-27T15:14:27.992+0100 INFO OGG-06511 Oracle GoldenGate Delivery for Oracle, vkrepl.prm: Using following columns in default map by name: ID, DYEAR, COUNTRY, CREATED, CREATED_BY, LAST_CHANGED, LAST_CHANGED_BY, VERSION, DOCUMENT, PERIOD. 2020-02-27T15:14:27.992+0100 INFO OGG-10155 Oracle GoldenGate Delivery for Oracle, vkrepl.prm: Instantiation CSN filtering is enabled on table SCOTT.COUNTRY_TABLE at CSN 55,489,598. 2020-02-27T15:14:27.992+0100 INFO OGG-06510 Oracle GoldenGate Delivery for Oracle, vkrepl.prm: Using the following key columns for target table SCOTT.COUNTRY_TABLE: ID.
When checking the target database, the inserted row has indeed been transferred.
