Oracle LogMiner Setup (multi-tenant)

Prerequisites

Before setting up LogMiner with Gluesync, ensure you have:

An Oracle Database (version 12c or higher) with CDB/PDB architecture (multi-tenat)
SYSDBA access to the Container Database (CDB)
Appropriate storage for archived redo logs

Initial Database Setup (CDB Level)

-- Connect to CDB$ROOT
alter session set container = CDB$ROOT;

-- Enable Archivelog mode if not already enabled
shutdown immediate
startup mount
alter database archivelog;
alter database open;

Verify archive log mode:

archive log list

Enable supplemental logging at the CDB level. This is the recommended approach to ensure all PDB changes are captured correctly.

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

User Setup (Common User)

We recommend creating a Common User (starting with C##) in the CDB to handle LogMiner operations. This user will be used by Gluesync to mine logs for any PDB.

-- Create Common User in CDB$ROOT
BEGIN
  EXECUTE IMMEDIATE 'CREATE USER C##GLUESYNC IDENTIFIED BY "YOUR_SECURE_PASSWORD"
  DEFAULT TABLESPACE SYSAUX
  TEMPORARY TABLESPACE TEMP
  QUOTA UNLIMITED ON SYSAUX
  CONTAINER=ALL';
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -01920 THEN
      RAISE;
    END IF;
END;
/

-- Grant privileges in CDB and all PDBs
GRANT
  CREATE SESSION,
  SET CONTAINER,
  SELECT ANY TRANSACTION,
  SELECT ANY DICTIONARY,
  EXECUTE_CATALOG_ROLE,
  LOGMINING
TO C##GLUESYNC CONTAINER=ALL;

-- CRITICAL: Ensure the user can access data from all containers
ALTER USER C##GLUESYNC SET CONTAINER_DATA = ALL CONTAINER = CURRENT;

PDB User Setup (Standard Connection)

In addition to the LogMiner user, you need a standard user in the PDB for Gluesync to connect to for schema discovery and initial snapshots.

-- Connect to your PDB
ALTER SESSION SET CONTAINER = ORCL; -- Replace with your PDB Name

-- Create the standard user
CREATE USER GLUESYNCUSER IDENTIFIED BY <password>;
GRANT CONNECT, RESOURCE TO GLUESYNCUSER;
GRANT UNLIMITED TABLESPACE TO GLUESYNCUSER;

Configure Tables for CDC (PDB Level)

You must enable supplemental logging for the specific tables you want to capture inside their respective PDBs.

-- Connect to your PDB
ALTER SESSION SET CONTAINER = ORCL; -- Replace with your PDB Name

-- Enable PDB-level supplemental logging (Optional but recommended)
-- ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

-- Replace with your actual tables
ALTER TABLE SCHEMA1.MYFIRSTTABLE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
GRANT SELECT ON SCHEMA1.MYFIRSTTABLE TO C##GLUESYNC;

ALTER TABLE SCHEMA1.MYSECONDTABLE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
GRANT SELECT ON SCHEMA1.MYSECONDTABLE TO C##GLUESYNC;

Connection Configuration

When configuring the Gluesync agent:

Host/Port: Point to the Oracle Listener.
Database Name: ORCL
Username: GLUESYNCUSER
Password: your_password
LogMiner Username: C##GLUESYNC
LogMiner Password: your_password
LogMiner Database: ORCLCDB

Important Notes

Privileges: The CONTAINER=ALL clause is critical for the common user to see redo generated by PDBs in the global redo logs.
Redo Logs: In a CDB/PDB architecture, all PDBs share the same redo logs at the CDB level. LogMiner reads these global logs.
Service Name: Ensure you connect to the correct service name. If you connect to the CDB root service, you might need to use ALTER SESSION SET CONTAINER in advanced scripts, but Gluesync typically connects to the target PDB service directly using the common user specific to that PDB context.

For Gluesync-specific configuration, see Gluesync Configuration Guide.