In pre-23c GoldenGate days, quite a few steps had to be manually performed in the Oracle database which were prerequisites for a GoldenGate configuration.
In GoldenGate 23c now all the same steps can be automated when we use the option “Create database connection“.
Prerequisites are automatically checked and validated at the time of creating the connection to the database (source or target).
- ARCHIVELOG enabled
- Database in FORCE LOGGING mode
- Enabled Supplemental Logging
- ENABLE_GOLDENGATE_REPLICATION parameter is set to TRUE
- STREAMS_POOL_SIZE parameter is set
- GoldenGate admin user created in the database
- Privileges granted to the GoldenGate admin user
In this post we will see how to create a connection to a source Oracle 23c Database (Free Edition).
Note – we do not create a Common User in the Root Container database anymore in 23c!
-- --######################################################################################## -- Database Information --######################################################################################## --Database Name: FREE --Database Host Name: db23c.localdomain --Database Instance Name: FREE --Database Unique Name: FREE --Database Version: 23 --Database is Container (CDB): YES --Database CDB Service Name: FREE.LOCALDOMAIN --Database PDB Service Name: FREEPDB1.LOCALDOMAIN --Database CDB User Exist: NO (User Name: ) --Database PDB User Exist: NO (User Name: GGADMIN) -- --######################################################################################## -- Database GoldenGate Status --######################################################################################## --Database Restart Required: YES --Database Archived Log Mode: NO (Required value for GoldenGate: YES) --Database Force Logging Mode: NO (Required value for GoldenGate: YES) --Database Supplemental Mode: NO (Required value for GoldenGate: YES) --Database Stream Pool Size Mb: 0 (Recommended value for GoldenGate: 512Mb) --GoldenGate Enable Parameter: FALSE (Required value for GoldenGate: TRUE) -- --######################################################################################## -- SQL Script to Enable GoldenGate in the FREE Database --######################################################################################## -- -- The database is non-RAC, its not in Archived Log Mode and a database Restart is required. -- Recommended Process: --SHUTDOWN IMMEDIATE; --STARTUP MOUNT; --ALTER DATABASE ARCHIVELOG; --ALTER DATABASE OPEN; -- -- Database FREE STREAMS_POOL_SIZE current size is 0Mb and it will be modified to 512Mb -- The STREAMS_POOL_SIZE value helps determine the size of the Streams pool. -- -- Property Description -- Parameter type Big integer -- Syntax STREAMS_POOL_SIZE = integer [K | M | G] -- Default value 0 -- Modifiable ALTER SYSTEM -- Modifiable in a PDB No -- Range of values Minimum: 0 -- Maximum: operating system-dependent -- Basic No -- -- Oracle's Automatic Shared Memory Management feature manages the size of -- the Streams pool when the SGA_TARGET initialization parameter is set to -- a nonzero value. If the STREAMS_POOL_SIZE initialization parameter also -- is set to a nonzero value, then Automatic Shared Memory Management uses -- this value as a minimum for the Streams pool. -- Oracle GoldenGate recommends streams_pool_size to be set at 1G or 10% of allocated SGA, whichever is smaller ALTER SYSTEM SET STREAMS_POOL_SIZE=512M SCOPE=BOTH SID='FREE'; -- -- Database FREE is not in the recommended Force Logging Mode, alter database is required -- -- Use this clause to put the database into or take the database out of FORCE LOGGING mode. -- The database must be mounted or open. -- -- In FORCE LOGGING mode, Oracle Database logs all changes in the database except changes in -- temporary tablespaces and temporary segments. This setting takes precedence over and is -- independent of any NOLOGGING or FORCE LOGGING settings you specify for individual -- tablespaces and any NOLOGGING settings you specify for individual database objects. -- Oracle strongly recommends putting the Oracle source database into forced logging mode. -- Forced logging mode forces the logging of all transactions and loads, overriding any user -- or storage settings to the contrary. This ensures that no source data in the Extract configuration gets missed. -- -- If you specify FORCE LOGGING, then Oracle Database waits for all ongoing unlogged operations to finish. -- ALTER DATABASE FORCE LOGGING; -- -- Database FREE GoldenGate Replication Parameter is not ENABLED, alter database is required -- -- Property Description -- Parameter type Boolean -- Default value false -- Modifiable ALTER SYSTEM -- Modifiable in a PDB No -- Range of values true | false -- Basic No -- Oracle RAC All instances must have the same setting -- -- This parameter primarily controls supplemental logging required to support logical -- replication of new data types and operations. The redo log file is designed to be -- applied physically to a database, therefore the default contents of the redo log file -- often do not contain sufficient information to allow logged changes to be converted -- into SQL statements. Supplemental logging adds extra information into the redo log -- files so that replication can convert logged changes into SQL statements without -- having to access the database for each change. Previously these extra changes -- were controlled by the supplemental logging DDL. Now the ENABLE_GOLDENGATE_REPLICATION -- parameter must also be set to enable the required supplemental logging for any -- new data types or operations. -- ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION=TRUE SCOPE=BOTH; -- -- Database FREE does not have SUPPLEMENTAL LOGGING enabled and an alter database is required. -- -- In addition to force logging, the minimal supplemental logging, a database-level option, is required for an Oracle source database -- when using Oracle GoldenGate. This adds row chaining information, if any exists, to the redo log for update operations. -- ALTER DATABASE ADD SUPPLEMENTAL LOG DATA FOR PROCEDURAL REPLICATION; ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; -- -- -- CDB User already exist or it is not needed for PDB Extract in databases greater then 19c, no action required. -- -- --####################################################################### --#### Create and Grant Privileges to the PDB GoldenGate Admin user. #### --###################################################################### -- -- GoldenGate PDB User does not exist, create PDB user is required to extract transactions from the database. ALTER SESSION SET CONTAINER = FREEPDB1; CREATE TABLESPACE GG_ADMIN_DATA DATAFILE '/u01/oracle/oradata/FREE/FREEPDB1/ggadmin_data.dbf' SIZE 100m AUTOEXTEND ON NEXT 100m; CREATE USER GGADMIN IDENTIFIED BY "DreamLiner787##" CONTAINER=CURRENT DEFAULT TABLESPACE GG_ADMIN_DATA QUOTA UNLIMITED ON GG_ADMIN_DATA; -- --####################################################################### --#### Grant Privileges to the PDB GoldenGate Admin user. #### --####################################################################### -- GRANT CONNECT TO GGADMIN CONTAINER=CURRENT; GRANT RESOURCE TO GGADMIN CONTAINER=CURRENT; GRANT CREATE SESSION TO GGADMIN CONTAINER=CURRENT; GRANT SELECT_CATALOG_ROLE TO GGADMIN CONTAINER=CURRENT; GRANT ALTER SYSTEM TO GGADMIN CONTAINER=CURRENT; GRANT ALTER USER TO GGADMIN CONTAINER=CURRENT; GRANT ALTER DATABASE TO GGADMIN CONTAINER=CURRENT; GRANT DATAPUMP_EXP_FULL_DATABASE TO GGADMIN CONTAINER=CURRENT; GRANT DATAPUMP_IMP_FULL_DATABASE TO GGADMIN CONTAINER=CURRENT; GRANT SELECT ANY DICTIONARY TO GGADMIN CONTAINER=CURRENT; GRANT SELECT ANY TRANSACTION TO GGADMIN CONTAINER=CURRENT; GRANT INSERT ANY TABLE TO GGADMIN CONTAINER=CURRENT; GRANT UPDATE ANY TABLE TO GGADMIN CONTAINER=CURRENT; GRANT DELETE ANY TABLE TO GGADMIN CONTAINER=CURRENT; GRANT LOCK ANY TABLE TO GGADMIN CONTAINER=CURRENT; GRANT CREATE ANY TABLE TO GGADMIN CONTAINER=CURRENT; GRANT CREATE ANY INDEX TO GGADMIN CONTAINER=CURRENT; GRANT CREATE ANY CLUSTER TO GGADMIN CONTAINER=CURRENT; GRANT CREATE ANY INDEXTYPE TO GGADMIN CONTAINER=CURRENT; GRANT CREATE ANY OPERATOR TO GGADMIN CONTAINER=CURRENT; GRANT CREATE ANY PROCEDURE TO GGADMIN CONTAINER=CURRENT; GRANT CREATE ANY SEQUENCE TO GGADMIN CONTAINER=CURRENT; GRANT CREATE ANY TRIGGER TO GGADMIN CONTAINER=CURRENT; GRANT CREATE ANY TYPE TO GGADMIN CONTAINER=CURRENT; GRANT CREATE ANY SEQUENCE TO GGADMIN CONTAINER=CURRENT; GRANT CREATE ANY VIEW TO GGADMIN CONTAINER=CURRENT; GRANT ALTER ANY TABLE TO GGADMIN CONTAINER=CURRENT; GRANT ALTER ANY INDEX TO GGADMIN CONTAINER=CURRENT; GRANT ALTER ANY CLUSTER TO GGADMIN CONTAINER=CURRENT; GRANT ALTER ANY INDEXTYPE TO GGADMIN CONTAINER=CURRENT; GRANT ALTER ANY OPERATOR TO GGADMIN CONTAINER=CURRENT; GRANT ALTER ANY PROCEDURE TO GGADMIN CONTAINER=CURRENT; GRANT ALTER ANY SEQUENCE TO GGADMIN CONTAINER=CURRENT; GRANT ALTER ANY TRIGGER TO GGADMIN CONTAINER=CURRENT; GRANT ALTER ANY TYPE TO GGADMIN CONTAINER=CURRENT; GRANT ALTER ANY SEQUENCE TO GGADMIN CONTAINER=CURRENT; GRANT CREATE DATABASE LINK TO GGADMIN CONTAINER=CURRENT; GRANT OGG_CAPTURE TO GGADMIN CONTAINER=CURRENT; GRANT OGG_APPLY TO GGADMIN CONTAINER=CURRENT; -- --######################################################################################## -- Database Configuration Status for GoldenGate: REQUIRE ATTENTION --######################################################################################## --
