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
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/software
Step 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.zip
Step 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 ./GLOBALS
Add the following line, then save and close it:
GGSCHEMA ogg
d) 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:
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
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.
