Oracle OpenLogReplicator Setup Guide

SELECT SUPPLEMENTAL_LOG_DATA_MIN, LOG_MODE FROM V$DATABASE;
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER SYSTEM ARCHIVE LOG CURRENT;
SELECT SUPPLEMENTAL_LOG_DATA_MIN, LOG_MODE FROM V$DATABASE;

SELECT FORCE_LOGGING FROM V$DATABASE;
ALTER DATABASE FORCE LOGGING;
ALTER SYSTEM ARCHIVE LOG CURRENT;
SELECT FORCE_LOGGING FROM V$DATABASE;

ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 50G; -- put your desired value
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/opt/db/fra'; -- put your desired value

ALTER SESSION SET CONTAINER = your_pdb_name; -- use this in case you're working on a PDB

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

ALTER TABLE SCHEMA1.MYSECONDTABLE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA1.MYSECONDTABLE TO GLUESYNC;

--- proceed similar to the previous example with the rest of the tables

--- ONLY FOR TESTING PURPOSES
SELECT SUPPLEMENTAL_LOG_DATA_ALL FROM V$DATABASE;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
SELECT SUPPLEMENTAL_LOG_DATA_ALL FROM V$DATABASE;
--- ONLY FOR TESTING PURPOSES

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER SYSTEM ARCHIVE LOG CURRENT;

ALTER SYSTEM SET DB_CREATE_FILE_DEST = '/opt/oracle/oradata' SCOPE = BOTH;
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = '50G' SCOPE = BOTH;
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/opt/oracle/fra' SCOPE = BOTH;

ALTER SESSION SET CONTAINER = XEPDB1;
CREATE TABLESPACE TBLS1 DATAFILE SIZE 100M AUTOEXTEND ON NEXT 100M;
ALTER TABLESPACE TBLS1 FORCE LOGGING;

CREATE USER GLUESYNC IDENTIFIED BY <password> DEFAULT TABLESPACE TBLS1 TEMPORARY TABLESPACE TEMP;
ALTER USER GLUESYNC QUOTA UNLIMITED ON TBLS1;

GRANT CONNECT TO GLUESYNC;
GRANT RESOURCE TO GLUESYNC;
GRANT SELECT, FLASHBACK ON SYS.CCOL$ TO GLUESYNC;
GRANT SELECT, FLASHBACK ON SYS.CDEF$ TO GLUESYNC;
GRANT SELECT, FLASHBACK ON SYS.COL$ TO GLUESYNC;
GRANT SELECT, FLASHBACK ON SYS.DEFERRED_STG$ TO GLUESYNC;
GRANT SELECT, FLASHBACK ON SYS.ECOL$ TO GLUESYNC;
GRANT SELECT, FLASHBACK ON SYS.LOB$ TO GLUESYNC;
GRANT SELECT, FLASHBACK ON SYS.LOBCOMPPART$ TO GLUESYNC;
GRANT SELECT, FLASHBACK ON SYS.LOBFRAG$ TO GLUESYNC;
GRANT SELECT, FLASHBACK ON SYS.OBJ$ TO GLUESYNC;
GRANT SELECT, FLASHBACK ON SYS.TAB$ TO GLUESYNC;
GRANT SELECT, FLASHBACK ON SYS.TABCOMPART$ TO GLUESYNC;
GRANT SELECT, FLASHBACK ON SYS.TABPART$ TO GLUESYNC;
GRANT SELECT, FLASHBACK ON SYS.TABSUBPART$ TO GLUESYNC;
GRANT SELECT, FLASHBACK ON SYS.TS$ TO GLUESYNC;
GRANT SELECT, FLASHBACK ON SYS.USER$ TO GLUESYNC;
GRANT SELECT ON SYS.V_$ARCHIVED_LOG TO GLUESYNC;
GRANT SELECT ON SYS.V_$DATABASE TO GLUESYNC;
GRANT SELECT ON SYS.V_$DATABASE_INCARNATION TO GLUESYNC;
GRANT SELECT ON SYS.V_$LOG TO GLUESYNC;
GRANT SELECT ON SYS.V_$LOGFILE TO GLUESYNC;
GRANT SELECT ON SYS.V_$PARAMETER TO GLUESYNC;
GRANT SELECT ON SYS.V_$STANDBY_LOG TO GLUESYNC;
GRANT SELECT ON SYS.V_$TRANSPORTABLE_PLATFORM TO GLUESYNC;
GRANT SELECT, FLASHBACK ON XDB.XDB$TTSET TO GLUESYNC;
DECLARE
CURSOR C1 IS SELECT TOKSUF FROM XDB.XDB$TTSET;
CMD VARCHAR2(2000);
BEGIN
FOR C IN C1 LOOP
        CMD := 'GRANT SELECT, FLASHBACK ON XDB.X$NM' || C.TOKSUF || ' TO GLUESYNC';
EXECUTE IMMEDIATE CMD;
CMD := 'GRANT SELECT, FLASHBACK ON XDB.X$QN' || C.TOKSUF || ' TO GLUESYNC';
EXECUTE IMMEDIATE CMD;
CMD := 'GRANT SELECT, FLASHBACK ON XDB.X$PT' || C.TOKSUF || ' TO GLUESYNC';
EXECUTE IMMEDIATE CMD;
END LOOP;
END;
/

GRANT CREATE SESSION TO GLUESYNC;

GRANT
CREATE TABLE
  TO GLUESYNC;

GRANT
SELECT
  ANY TABLE TO GLUESYNC;

GRANT CREATE ANY SEQUENCE TO GLUESYNC;

-- Connect as SYSDBA or SYSTEM and run:
GRANT CREATE PROCEDURE TO GLUESYNC;
GRANT EXECUTE ANY PROCEDURE TO GLUESYNC;

ALTER USER GLUESYNC QUOTA UNLIMITED ON USERS;

alter session
set
  current_schema = GLUESYNC;

-- Add the following line for each table that has to be synced using gluesync
ALTER TABLE GLUESYNC.<TABLENAME> ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

ALTER SESSION SET CONTAINER = CDB$ROOT;
ALTER SYSTEM ARCHIVE LOG CURRENT;

curl --location --request PUT 'http://core-hub-ip-address:1717/pipelines/{pipelineId}/agents/{agentId}/config/credentials' \
--header 'Content-Type: application/json' \
--header 'Authorization: ••••••' \
--data '{
    "customHostCredentials": {
    "defaultConnectionType": "SERVICE_NAME",
    "mountType": "VOLUME",
    "mountHost": "127.0.0.1",
    "olrHost": "127.0.0.1",
    "olrPort": 50000,
    "olrDbName": "ORA1"
  },
  "hostCredentials": {
    "connectionName": "",
    "host": "host-address",
    "port": 1521,
    "databaseName": "db-name",
    "username": "",
    "password": "",
    "disableAuth": false,
    "enableTls": false,
    "trustServerCertificate": false,
    "additionalHosts": [],
    "maxConnectionsCount": 100
  }
}'