The following procedure describes a minimal Oracle Golden Gate configuration.
For a basic configuration I used two Linux VMs (Red Hat Enterprise Linux Server release 7.5) running single instance Oracle 19.3.0.0.0 databases. I created both databases using DBCA.
Prepare Databases
On GGSOURCE check that LOG_MODE is set to ARCHIVELOG.
Archivelog must be enable on source side because if we are using classic capture the extract process will capture the changes information through archivelogs only, So it is mandatory for classic capture replication.
[code]
SQL> select log_mode from v$database;
LOG_MODE
------------
NOARCHIVELOG
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1207958960 bytes
Fixed Size 8895920 bytes
Variable Size 771751936 bytes
Database Buffers 419430400 bytes
Redo Buffers 7880704 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG
[/code]
Enable Forced Logging
Forced logging should be enabled on the source database to ensure that all changes are written to the redo logs. DBCA does not enable force logging during database creation.
To check if FORCE_LOGGING is enabled in the source database use:
[code]
SQL> select force_logging from v$database;
FORCE_LOGGING
---------------------------------------
NO
[/code]
If not enabled, then enable FORCE_LOGGING using:
[code]
SQL> alter database force logging;
Database altered.
[/code]
Verify that FORCE_LOGGING has been enabled successfully using:
[code]
SQL> select force_logging from v$database;
FORCE_LOGGING
---------------------------------------
YES
[/code]
The documentation recommends performing a redo log switch to ensure that the change is applied in all subsequent redo.
Enable Minimal Supplemental Logging
If minimal supplemental logging is not enabled, the extract process fails to start. DBCA does not enable minimal supplemental during database creation.
Minimal supplemental logging only needs to be configured on the source database. It may be prudent to enable it on the target database as well.
Check if minimal supplemental logging is currently enabled:
[code]
SQL> SELECT supplemental_log_data_min FROM v$database;
SUPPLEME
--------
NO
[/code]
If not enabled then configure minimal supplemental logging:
[code]
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
Database altered.
[/code]
Verify that minimal supplemental logging is now enabled:
[code]
SQL> SELECT supplemental_log_data_min FROM v$database;
SUPPLEME
--------
YES
[/code]
Switch the log file again to ensure that all subsequent redo contains minimal supplemental logging:
[code]
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
[/code]
ENABLE_GOLDENGATE_REPLICATION should be enabled on the source and target databases.
To check if ENABLE_GOLDENGATE_REPLICATION is enabled in the source database use:
[code]
SQL> !hostname
alpha.oracledbashop.com
SQL> show parameter ENABLE_GOLDENGATE_REPLICATION
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
enable_goldengate_replication boolean FALSE
[/code]
If not enabled, then enable ENABLE_GOLDENGATE_REPLICATION using:
[code]
SQL> ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION=TRUE SCOPE=BOTH;
System altered.
[/code]
To check if ENABLE_GOLDENGATE_REPLICATION is enabled in the source database use:
[code]
SQL> show parameter ENABLE_GOLDENGATE_REPLICATION
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
enable_goldengate_replication boolean TRUE
[/code]
To check if ENABLE_GOLDENGATE_REPLICATION is enabled in the target database use:
[code]
SQL> !hostname
beta.oracledbashop.com
SQL> show parameter ENABLE_GOLDENGATE_REPLICATION
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
enable_goldengate_replication boolean FALSE
[/code]
If not enabled, then enable ENABLE_GOLDENGATE_REPLICATION using:
[code]
SQL> ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION=TRUE SCOPE=BOTH;
System altered.
[/code]
To check if ENABLE_GOLDENGATE_REPLICATION is enabled in the target database use:
[code]
SQL> show parameter ENABLE_GOLDENGATE_REPLICATION
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
enable_goldengate_replication boolean TRUE
[/code]
Prepare Test Environment
Create Test User
In order to test the GoldenGate configuration I created a new schema (US01) containing a new table (T1) as follows. The table is derived from DBA_OBJECTS.
In both databases create a user (schema) called US01. For example:
[code]
SQL> CREATE USER us01 IDENTIFIED BY us01;
User created.
[/code]
In both databases grant the following permissions to the new user. For example:
[code]
SQL> GRANT CONNECT,RESOURCE,DBA TO us01;
Grant succeeded.
[/code]
As these are test databases, security is not an issue and therefore DBA privilege has been granted to the new user.
Create Test Table
In the source database (GGSOCUCE) create the T1 table using a subset of rows from DBA_OBJECTS
[code]
SQL> conn us01/us01
Connected.
SQL> CREATE TABLE t1 AS
SELECT object_id,owner,object_name,object_type
FROM dba_objects
WHERE object_id <= 10000;
Table created.
[/code]
In the source database (GGSOURCE), add a primary key constraint and index to table T1:
[code]
SQL> ALTER TABLE t1 ADD CONSTRAINT t1_i1 PRIMARY KEY (object_id);
Table altered.
[/code]
Check/Start Listener Processes
GGSOURCE
Before attempting to configure the network, ensure that the listener processes are running on both servers.
[code]
[alpha.oracledbashop.com:GGSOURCE] lsnrctl status
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 25-JAN-2020 01:17:56
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=alpha.oracledbashop.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 24-JAN-2020 22:43:59
Uptime 0 days 2 hr. 33 min. 57 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u002/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
Listener Log File /u002/app/oracle/diag/tnslsnr/alpha/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=alpha.oracledbashop.com)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=alpha.oracledbashop.com)(PORT=5500))(Security=(my_wallet_directory=/u002/app/oracle/admin/GGSOURCE/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "GGSOURCE.oracledbashop.com" has 1 instance(s).
Instance "GGSOURCE", status READY, has 1 handler(s) for this service...
Service "GGSOURCEXDB.oracledbashop.com" has 1 instance(s).
Instance "GGSOURCE", status READY, has 1 handler(s) for this service...
The command completed successfully
[/code]
GGTARGET
[code]
[beta.oracledbashop.com:GGTARGET] lsnrctl status
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 25-JAN-2020 01:20:15
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=beta.oracledbashop.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 24-JAN-2020 22:44:01
Uptime 0 days 2 hr. 36 min. 14 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u002/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
Listener Log File /u002/app/oracle/diag/tnslsnr/beta/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=beta.oracledbashop.com)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=beta.oracledbashop.com)(PORT=5500))(Security=(my_wallet_directory=/u002/app/oracle/admin/GGTARGET/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "GGTARGET.oracledbashop.com" has 1 instance(s).
Instance "GGTARGET", status READY, has 1 handler(s) for this service...
Service "GGTARGETXDB.oracledbashop.com" has 1 instance(s).
Instance "GGTARGET", status READY, has 1 handler(s) for this service...
The command completed successfully
[/code]
Configure Network
For this basic configuration, an entry is required in the TNSNAMES.ORA file on each server describing the database on the other server.
On the source database (GGSOURCE) I added the following entry for the GGTARGET database to $ORACLE_HOME/network/admin/tnsnames.ora
[code]
[alpha.oracledbashop.com:GGSOURCE] cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u002/app/oracle/product/19.0.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
GGTARGET =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.168.190.2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = GGTARGET.oracledbashop.com)
)
)
[/code]
Verify the connection using SQL*Plus. For example:
[code]
[alpha.oracledbashop.com:GGSOURCE] sqlplus us01/us01@GGTARGET
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jan 25 01:26:47 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> select name from v$database;
NAME
---------
GGTARGET
[/code]
On the target database (GGTARGET), I added the following entry for the GGSOURCE database to $ORACLE_HOME/network/admin/tnsnames.ora
[code]
[beta.oracledbashop.com:GGTARGET] cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u002/app/oracle/product/19.0.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
GGSOURCE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.168.190.1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = GGSOURCE.oracledbashop.com)
)
)
[/code]
Verify the connection using SQL*Plus. For example:
[code]
[beta.oracledbashop.com:GGTARGET] sqlplus us01/us01@GGSOURCE
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jan 25 01:30:11 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Last Successful login time: Sat Jan 25 2020 01:14:14 -06:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> select name from v$database;
NAME
---------
GGSOURCE
[/code]
Create Database Links
On each server create a database link to the other database. This is only required to set up the test data. It is not required for GoldenGate.
On the source server (GGSOURCE) as the US01 user, create the following database link:
[code]
[alpha.oracledbashop.com:GGSOURCE] sqlplus us01/us01
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jan 25 01:35:29 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Last Successful login time: Sat Jan 25 2020 01:34:50 -06:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> create database link GGTARGET connect to us01 identified by us01 using 'GGTARGET';
Database link created.
[/code]
On the target server (GGTARGET) as the US01 user, create the following database link:
[code]
[beta.oracledbashop.com:GGTARGET] sqlplus us01/us01
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jan 25 01:39:18 2020
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Last Successful login time: Sat Jan 25 2020 01:37:12 -06:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> create database link GGSOURCE connect to us01 identified by us01 using 'GGSOURCE';
Database link created.
[/code]
Copy Test Data to Target Database
Create an initial copy of the test data on the target database
In the target database (GGTARGET) create a copy of the T1 table using the SQL*Plus command:
[code]
[beta.oracledbashop.com:GGTARGET] sqlplus us01/us01
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jan 25 01:41:33 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Last Successful login time: Sat Jan 25 2020 01:39:18 -06:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> create table t1 as select * from t1@GGSOURCE;
Table created.
[/code]
In the target database (GGTARGET), add a primary key constraint and index to table T1:
[code]
[beta.oracledbashop.com:GGTARGET] sqlplus us01/us01
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jan 25 01:43:54 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Last Successful login time: Sat Jan 25 2020 01:41:33 -06:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> ALTER TABLE t1 ADD CONSTRAINT t1_i1 PRIMARY KEY (object_id);
Table altered.
[/code]
In the source database (NORTH), verify the number of rows in the original table:
[code]
[alpha.oracledbashop.com:GGSOURCE] sqlplus us01/us01
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jan 25 01:45:23 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Last Successful login time: Sat Jan 25 2020 01:41:52 -06:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> SELECT COUNT(*) FROM t1;
COUNT(*)
----------
9980
[/code]
In the target database, verify that there is the same number of rows in the new table:
[code]
[beta.oracledbashop.com:GGTARGET] sqlplus us01/us01
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jan 25 01:46:55 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Last Successful login time: Sat Jan 25 2020 01:43:55 -06:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> SELECT COUNT(*) FROM t1;
COUNT(*)
----------
9980
[/code]
Note that the actual number of rows in table T1 should be identical for databases GGSOURCE and GGTARGET.
Configure GoldenGate
Download and Install GoldenGate software - Click
here for more details.
Create GoldenGate Tablespace
On each server create a new tablespace for the GoldenGate objects. Ensure that AUTOEXTEND is enabled.
SOURCE (GGSOURCE)
[code]
[alpha.oracledbashop.com:GGSOURCE] sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jan 25 01:55:47 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> CREATE TABLESPACE goldengate
DATAFILE '/u002/app/oracle/oradata/GGSOURCE/goldengate01.dbf'
SIZE 100M<
AUTOEXTEND ON;
Tablespace created.
[/code]
TARGET(GGTARGET)
[code]
[beta.oracledbashop.com:GGTARGET] sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jan 25 02:02:49 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> CREATE TABLESPACE goldengate
DATAFILE '/u002/app/oracle/oradata/GGTARGET/goldengate01.dbf'
SIZE 100M
AUTOEXTEND ON;
Tablespace created.
[/code]
Create the GoldenGate Schema Owner
A new user should be created to own the GoldenGate database objects.
On each server create the GoldenGate schema owner. For example:
SOURCE (GGSOURCE)
[code]
SQL> show user;
USER is "SYS"
SQL> create user gg01 identified by gg01 default tablespace goldengate quota unlimited on goldengate;
User created.
SQL> grant create session,connect,resource,alter system, dba to gg01;
Grant succeeded.
SQL> EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE(grantee=>'gg01', privilege_type=>'CAPTURE', grant_optional_privileges=>'*');
PL/SQL procedure successfully completed.
[/code]
TARGET(GGTARGET)
[code]
SQL> create user gg01 identified by gg01 default tablespace goldengate quota unlimited on goldengate;
User created.
SQL> grant create session,connect,resource,alter system, dba to gg01;
Grant succeeded.
SQL> EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE(grantee=>'gg01', privilege_type=>'CAPTURE', grant_optional_privileges=>'*');
PL/SQL procedure successfully completed.
[/code]
On each server set the GGSCHEMA in the global parameter file.
GGSOURCE
[code]
[alpha.oracledbashop.com:GGSOURCE] ./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 19c on Oct 17 2019 21:16:29
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved.
GGSCI (alpha.oracledbashop.com) 1> EDIT PARAMS ./GLOBALS
[/code]
In this example the parameters file is /u002/app/oracle/product/gg/GLOBALS
Add the following entry:
[code]
GGSCHEMA gg01
[/code]
To view GLOBALS parameter file content GGSOURCE
[code]
GGSCI (alpha.oracledbashop.com) 3> VIEW PARAMS ./GLOBALS
GGSCHEMA gg01
[/code]
GGTARGET
[code]
[beta.oracledbashop.com:GGTARGET] cd $GGATE
[beta.oracledbashop.com:GGTARGET] ./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 19c on Oct 17 2019 21:16:29
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved.
GGSCI (beta.oracledbashop.com) 1> EDIT PARAMS ./GLOBALS
[/code]
In this example the parameters file is /u002/app/oracle/product/gg/GLOBALS
Add the following entry:
[code]
GGSCHEMA gg01
[/code]
To view GLOBALS parameter file content in GGTARGET
[code]
GGSCI (beta.oracledbashop.com) 4> VIEW PARAMS ./GLOBALS
GGSCHEMA gg01
[/code]
Go to Golden Gate Installed location (in my scenario /u002/app/oracle/product/gg) and then run the following Golden Gate inbuild scripts for creating all necessary objects to support DDL replication. (GGSOURCE).
Specify the GoldenGate schema name when prompted. In my case GG01
[code]
[alpha.oracledbashop.com:GGSOURCE] ls -lrt marker_setup.sql ddl_setup.sql role_setup.sql ddl_enable.sql
-rw-rw-r--. 1 oracle oinstall 3309 Oct 17 15:37 marker_setup.sql
-rw-rw-r--. 1 oracle oinstall 292051 Oct 17 15:37 ddl_setup.sql
-rw-rw-r--. 1 oracle oinstall 88 Oct 17 15:37 ddl_enable.sql
-rw-rw-r--. 1 oracle oinstall 3187 Oct 17 15:37 role_setup.sql
[/code]
[code]
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:GG01
Marker setup table script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to GG01
MARKER TABLE
-------------------------------
OK
MARKER SEQUENCE
-------------------------------
OK
Script complete.
[/code]
[code]
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:GG01
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 GG01 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 GG01
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
------------------------------------------------------------------------------------------------------------------------
/u002/app/oracle/product/19.0.0/dbhome_1/rdbms/log/ggs_ddl_trace.log
Analyzing installation status...
VERSION OF DDL REPLICATION
------------------------------------------------------------------------------------------------------------------------
OGGCORE_19.1.0.0.0_PLATFORMS_191017.1054
STATUS OF DDL REPLICATION
------------------------------------------------------------------------------------------------------------------------
SUCCESSFUL installation of DDL Replication software components
Script complete.
[/code]
[code]
SQL> @ddl_enable.sql
Trigger altered.
[/code]
[code]
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:GG01
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> GRANT GGS_GGSUSER_ROLE TO GG01;
Grant succeeded.
[/code]
On the source server grant GGS_GGSUSER_ROLE to the GoldenGate user:
Configure Manager Parameters
On both servers configure the MGR parameters:
[code]
[alpha.oracledbashop.com:GGSOURCE] ./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 19c on Oct 17 2019 21:16:29
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved.
GGSCI (alpha.oracledbashop.com) 1> EDIT PARAMS MGR
[/code]
The above command created the file /u002/app/oracle/product/gg/dirprm/mgr.prm
Add the following parameters to the parameter file:
[code]
PORT 7809
DYNAMICPORTLIST 7810-7820
[/code]
Save and close the parameter file
To view manager parameter content
[code]
GGSCI (alpha.oracledbashop.com) 2> VIEW PARAMS MGR
PORT 7809
DYNAMICPORTLIST 7810-7820
[/code]
Configure Extract Parameters
In this example the extract process will be called "ex1"
On the source server, create the parameter file for Extract ex1:
[code]
GGSCI (alpha.oracledbashop.com) 2> EDIT PARAMS ex1
[/code]
The above command created the file /u002/app/oracle/product/gg/dirprm/ex1.prm
Add the following parameters to the new file:
[code]
EXTRACT ex1
USERID gg01, PASSWORD gg01
EXTTRAIL /u002/app/oracle/product/gg/dirdat/ex
TABLE us01.*;
[/code]
To view the ex1 parameter file, use following
[code]
GGSCI (alpha.oracledbashop.com) 3> VIEW PARAM ex1
EXTRACT ex1
USERID gg01, PASSWORD gg01
EXTTRAIL /u002/app/oracle/product/gg/dirdat/ex
TABLE us01.*;
[/code]
Configure Data Pump Parameters
In this example the Data Pump process will be called dp1
On the source server create the parameter file for Data Pump process dp1:
[code]
GGSCI (alpha.oracledbashop.com) 4> EDIT PARAMS dp1
[/code]
The above command created the file /u002/app/oracle/product/gg/dirprm/dp1.prm
Add the following parameters to the new file:
[code]
EEXTRACT dp1
USERID gg01, PASSWORD gg01
RMTHOST beta.oracledbashop.com, MGRPORT 7809
RMTTRAIL /u002/app/oracle/product/gg/dirdat/rt
TABLE us01.*;
[/code]
Save and close the parameter file
To view dp1 parameter file, use following
[code]
GGSCI (alpha.oracledbashop.com) 4> VIEW PARAMS dp1
EXTRACT dp1
USERID gg01, PASSWORD gg01
RMTHOST beta.oracledbashop.com, MGRPORT 7809
RMTTRAIL /u002/app/oracle/product/gg/dirdat/rt
TABLE us01.*;
[/code]
Create Check Point Table
The check point table should be created in the target database.
On the target server login as the GG01 user and add the check point table:
[code]
GGSCI (beta.oracledbashop.com) 1> DBLOGIN USERID gg01, PASSWORD gg01
Successfully logged into database.
GGSCI (beta.oracledbashop.com as gg01@GGTARGET) 2> ADD CHECKPOINTTABLE gg01.checkpointtable
Successfully created checkpoint table gg01.checkpointtable.
[/code]
The name of the check point table must be added to the GLOBALS file on the target server.
On the target server edit the GLOBALS file
[code]
GGSCI (beta.oracledbashop.com as gg01@GGTARGET) 3> EDIT PARAMS ./GLOBALS
[/code]
Save and close the GLOBALS parameter file.
To view GLOBALS parameter file, do the following
[code]
GGSCI (beta.oracledbashop.com as gg01@GGTARGET) 4> VIEW PARAMS ./GLOBALS
GGSCHEMA gg01
CHECKPOINTTABLE gg01.checkpointtable
[/code]
Configure Replication Parameters
On the target server create the parameter file for replication process rep1:
[code]
GGSCI (beta.oracledbashop.com as gg01@GGTARGET) 5> EDIT PARAMS rep1
[/code]
The above command created the file /u002/app/oracle/product/gg/dirprm/rep1.prm
Add the following parameters to the new file:
[code]
REPLICAT rep1
USERID gg01, PASSWORD gg01
ASSUMETARGETDEFS
DISCARDFILE /u002/app/oracle/product/gg/discards, PURGE
MAP us01.*, TARGET us01.*;
[/code]
To view rep1 parameter file, do the following
[code]
GGSCI (beta.oracledbashop.com as gg01@GGTARGET) 6> VIEW PARAM rep1
REPLICAT rep1
USERID gg01, PASSWORD gg01
ASSUMETARGETDEFS
DISCARDFILE /u002/app/oracle/product/gg/discards, PURGE
MAP us01.*, TARGET us01.*;
[/code]
Note that the DISCARDFILE parameter includes the PURGE keyword. If PURGE is not specified them the replication process will fail the second time it is started. Alternatively use the APPEND keyword to append output to the existing file.
Configure Supplemental Logging for Replicated Tables
On the source server configure supplemental logging for all tables that will be replicated. In this example there is only one table (T1)
Supplemental logging can be configured by any user that has privileges to modify the underlying database table.
[code]
GGSCI (alpha.oracledbashop.com) 1> DBLOGIN USERID us01, PASSWORD us01
Successfully logged into database.
GGSCI (alpha.oracledbashop.com as us01@GGSOURCE) 2> ADD TRANDATA us01.t1
2020-01-26 01:32:11 INFO OGG-15132 Logging of supplemental redo data enabled for table US01.T1.
2020-01-26 01:32:11 INFO OGG-15133 TRANDATA for scheduling columns has been added on table US01.T1.
2020-01-26 01:32:11 INFO OGG-15135 TRANDATA for instantiation CSN has been added on table US01.T1.
2020-01-26 01:32:13 INFO OGG-10471 ***** Oracle Goldengate support information on table US01.T1 *****
Oracle Goldengate support native capture on table US01.T1.
Oracle Goldengate marked following column as key columns on table US01.T1: OBJECT_ID.
[/code]
Add the Extract Process
On the source server add the Extract process (ex1)
[code]
GGSCI (alpha.oracledbashop.com as us01@GGSOURCE) 3> ADD EXTRACT ex1, TRANLOG, BEGIN NOW
EXTRACT added.
[/code]
Add the Extract Trail
On the source server add the Extract trail (/u002/app/oracle/product/gg/dirdat/ex)
[code]
GGSCI (alpha.oracledbashop.com) 2> ADD EXTTRAIL /u002/app/oracle/product/gg/dirdat/ex, EXTRACT ex1
EXTTRAIL added.
[/code]
Add the Data Pump Process
On the source server add the Data Pump process (dp1)
[code]
GGSCI (alpha.oracledbashop.com) 3> ADD EXTRACT dp1 EXTTRAILSOURCE /u002/app/oracle/product/gg/dirdat/ex
EXTRACT added.
[/code]
Add the Data Pump Trail
On the source server add the Data Pump trail (/u002/app/oracle/product/gg/dirdat/rt). This trail is created on the target server. However, the name is required in order to set up the Data Pump process on the source server.
[code]
GGSCI (alpha.oracledbashop.com) 4> ADD RMTTRAIL /u002/app/oracle/product/gg/dirdat/rt, EXTRACT dp1
RMTTRAIL added.
[/code]
Add the Replication Process
On the target server add the Replication process (rep1)
[code]
GGSCI (beta.oracledbashop.com) 1> ADD REPLICAT rep1, EXTTRAIL /u002/app/oracle/product/gg/dirdat/rt
REPLICAT added.
[/code]
Start GoldenGate
Start Manager
On the source server, start the GoldenGate manager:
[code]
GGSCI (alpha.oracledbashop.com) 2> START MANAGER
Manager started.
[/code]
On the target server, start the GoldenGate manager:
[code]
GGSCI (beta.oracledbashop.com) 1> START MANAGER
Manager started.
[/code]
Start Extract Process
On the source server start the Extract (ex1)
[code]
GGSCI (alpha.oracledbashop.com) 3> START EXTRACT ex1
Sending START request to MANAGER ...
EXTRACT EX1 starting
GGSCI (alpha.oracledbashop.com) 4> INFO EXTRACT ex1
EXTRACT EX1 Last Started 2020-01-26 01:51 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:05 ago)
Process ID 10486
Log Read Checkpoint Oracle Redo Logs
2020-01-26 01:52:04 Seqno 9, RBA 163836416
SCN 0.2140075 (2140075)
[/code]
The status should be RUNNING.
Start Data Pump Process
On the source server, start the Data Pump (dp1):
[code]
GGSCI (alpha.oracledbashop.com) 5> START EXTRACT dp1
Sending START request to MANAGER ...
EXTRACT DP1 starting
[/code]
Verify that the Data Pump has started successfully using INFO EXTRACT:
[code]
GGSCI (alpha.oracledbashop.com) 13> INFO extract dp1
EXTRACT DP1 Last Started 2020-01-26 01:59 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:18:09 ago)
Process ID 11147
Log Read Checkpoint File /u002/app/oracle/product/gg/dirdat/ex000000000
First Record RBA 0
[/code]
The status should be RUNNING.
Start Replication Process
On the target server, start the Replicat process (rep1):
[code]
GGSCI (beta.oracledbashop.com) 2> START REPLICAT rep1
Sending START request to MANAGER ...
REPLICAT REP1 starting
GGSCI (beta.oracledbashop.com) 3> INFO REPLICAT rep1
REPLICAT REP1 Last Started 2020-01-26 02:01 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:07 ago)
Process ID 14207
Log Read Checkpoint File /u002/app/oracle/product/gg/dirdat/rt000000000
First Record RBA 0
[/code]
The status should be RUNNING.
Test Replication
On the source server, check the number of rows in table T1:
[code]
[alpha.oracledbashop.com:GGSOURCE] sqlplus us01/us01
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jan 26 02:04:05 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Last Successful login time: Sun Jan 26 2020 01:31:59 -06:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> select count(*) from t1;
COUNT(*)
----------
9980
[/code]
On the target server, check the number of rows in table T1:
[code]
[beta.oracledbashop.com:GGTARGET] sqlplus us01/us01
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jan 26 02:05:38 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Last Successful login time: Sat Jan 25 2020 01:46:55 -06:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> select count(*) from t1;
COUNT(*)
----------
9980
[/code]
On the source server, add some rows to table T1. For example:
[code]
SQL> INSERT INTO t1 (object_id,owner,object_name,object_type)
SELECT object_id,owner,object_name,object_type
FROM dba_objects
WHERE object_id BETWEEN 10001 AND 11000; 2 3 4
999 rows created.
[/code]
On the target server check the number of rows in table T1:
[code]
SQL> select count(*) from t1;
COUNT(*)
----------
10979
[/code]
The number of rows should be identical in both tables
This completes the basic configuration.
Note: If count doesn't match Switch the log file once or twice
[code]
SQL> alter system switch logfile;
[/code]
Well expressed and good step by step documentation covering the latest version
ReplyDelete