Deploying OGG 12.2 Replication from Oracle 11g RAC to Oracle 11g RAC

OGG 12.2.0.1.1

1 Environment Preparation

Source:

IP: 192.168.3.156
Hostname: asm-scan
Database: 11.2.0.4.0 - 64bit
SID: orcl
OS: Oracle Linux Server 7.4

Target:

IP: 192.168.3.165
Hostname: rac-scan
Database: 11.2.0.4.0 - 64bit
SID: orcl
OS: Oracle Linux Server 7.4

Requirements:

DB size > 1TB, blob column
Disable DDL replicate, encrypt password
Using expdp/impdp to initialize target database
Unidirectional replication

2 Installing OGG 12.2 Software

2.1 Creating the OGG software installation directory.

~]# mkdir /ggs
~]# chown -R oracle:dba /ggs

2.2 Unzip the OGG software.

~]# unzip /soft/fbo_ogg12.2.0.1.1_Linux_x64_shiphome.zip -d /soft
~]# chown -R oracle:oinstall /soft/fbo_ggs_Linux_x64_shiphome

2.3 Installing OGG software.

~]$ /soft/fbo_ggs_Linux_x64_shiphome/Disk1/runInstaller

・ OGG Installation Appendix

2.4 (Option) Adding environment variables.

~]$ cat >> ~/.bash_profile <<'EOF'
# OGG
PATH=/ggs:$PATH
LD_LIBRARY_PATH=/ggs:$LD_LIBRARY_PATH
if command -v rlwrap >/dev/null 2>&1; then
  alias ggsci='rlwrap ggsci'
fi
EOF

2.5 Configuring TNS names.

~]$ cat >> $TNS_ADMIN/tnsnames.ora << EOF
TO_SOURCE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.3.156)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

TO_TARGET =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.3.165)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )
EOF

3 Configuring Source Database

3.1 Enabling archive mode, supplemental log data min, force logging.

SQL> select log_mode,supplemental_log_data_min,force_logging from v$database;

SQL> alter database add supplemental log data;
SQL> alter database force logging;

SQL> alter system archive log current;

3.2 (Option, 11g no requires) Purge recycle.

SQL> show parameter recyclebin
SQL> select * from dba_recyclebin;
SQL> purge dba_recyclebin;

3.3 Creating the OGG tablespace and user.

create tablespace ogg_tbs datafile '+data' size 200m autoextend on next 10m maxsize 1024m extent management local segment space management auto;

create user goldengate identified by goldengate default tablespace ogg_tbs temporary tablespace temp quota unlimited on ogg_tbs;

grant connect to goldengate;
grant alter any table to goldengate;
grant alter session to goldengate;
grant create session to goldengate;
grant flashback any table to goldengate;
grant select any dictionary to goldengate;
grant select any table to goldengate;
grant resource to goldengate;
grant drop any table to goldengate;
grant dba to goldengate;

3.4 Enabling goldengate replication parameter.

SQL> show parameter enable_goldengate_replication
SQL> alter system set enable_goldengate_replication=true scope=both;

3.5 Create the user jove for production simulator.

create tablespace jove_tbs datafile '+data' size 1m autoextend on next 10m maxsize unlimited;
create user jove identified by jove default tablespace jove_tbs temporary tablespace temp;
grant dba to jove;

・ Oracle Database Test Data

4 Configuring Source OGG Processes

4.1 Configure the mgr process.

GGSCI> edit params mgr
port 7809
autostart er *
autorestart er *, waitminutes 3, retries 15
purgeoldextracts ./dirdat/*, usecheckpoints, minkeepdays 7
-- userid goldengate@to_target, password goldengate

GGSCI> start mgr
GGSCI> info mgr

Login to the target database using the userid, and access the checkpoint table to clear the extract files.

4.2 Login to source database.

GGSCI> dblogin userid goldengate, password goldengate

4.3 Add schematrandata to enable supplemental logging.

GGSCI> add schematrandata jove
GGSCI> add schematrandata user01
GGSCI> add schematrandata user02
GGSCI> add schematrandata user03

GGSCI> info schematrandata jove
...

4.4 Configure the classic capture process.

~]$ cd /ggs
~]$ mkdir -p ./dirdat/capt00
~]$ mkdir -p ./dirrpt/capt00

GGSCI> edit params capt00
extract capt00
userid goldengate,password goldengate
exttrail ./dirdat/capt00/ct
tranlogoptions excludeuser goldengate
tranlogoptions dblogreader
warnlongtrans 12h, checkinterval 30m
discardfile ./dirrpt/capt00/capt00.dsc, append, megabytes 200
table jove.*;
table user01.*;
table user02.*;
table user03.*;

GGSCI> add extract capt00, tranlog, begin now, threads 2
GGSCI> add exttrail ./dirdat/capt00/ct, extract capt00, megabytes 200

GGSCI> info capt00

4.5 Configure the datapump process.

~]$ cd /ggs
~]$ mkdir -p ./dirrpt/pump00

GGSCI> edit params pump00
extract pump00
passthru
rmthost 192.168.3.165, mgrport 7809
rmttrail ./dirdat/rept00/rt
discardfile ./dirrpt/pump00/pump00.dsc, append, megabytes 200
table jove.*;
table user01.*;
table user02.*;
table user03.*;

GGSCI> add extract pump00, exttrailsource ./dirdat/capt00/ct
GGSCI> add rmttrail ./dirdat/rept00/rt, extract pump00, megabytes 200

GGSCI> info pump00

Do not start the capture and datapump processes here! Keep it stopped!

5 Configuring Target Database

5.1 Creating the production tablespace.

SQL> create tablespace user01 datafile '+data' size 100m autoextend off;
SQL> create tablespace user02 datafile '+data' size 100m autoextend off;
SQL> create tablespace user03 datafile '+data' size 100m autoextend off;
SQL> create tablespace jove_tbs datafile '+data' size 1m autoextend on next 10m maxsize unlimited;

5.2 Creating the OGG tablespace and user.

create tablespace ogg_tbs datafile '+data' size 200m autoextend on next 10m maxsize 1024m extent management local segment space management auto;

create user goldengate identified by goldengate default tablespace ogg_tbs temporary tablespace temp quota unlimited on ogg_tbs;

grant connect to goldengate;
grant alter any table to goldengate;
grant alter session to goldengate;
grant create session to goldengate;
grant flashback any table to goldengate;
grant select any dictionary to goldengate;
grant select any table to goldengate;
grant resource to goldengate;
grant drop any table to goldengate;
grant dba to goldengate;

5.3 Enabling goldengate replication parameter.

SQL> show parameter enable_goldengate_replication
SQL> alter system set enable_goldengate_replication=true scope=both;

5.5 Disable job queue processes.

SQL> show parameter job_queue_processes
SQL> alter system set job_queue_processes=0 scope=both;

5.6 Disable archive mode and delete all archive logs.

SQL> shutdown immediate
SQL> startup mount
SQL> alter database noarchivelog;
SQL> alter database open;

RMAN> run {
  crosscheck archivelog all;
  delete noprompt expired archivelog all;
  delete noprompt archivelog all completed before 'sysdate';
}

5.7 Start the capture process on source before export data.

GGSCI> start capt00
GGSCI> info capt00

5.8 Query target database SCN.

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    4747793

5.9 Using datapump to export data from source database and import into target database.

・ Oracle Database DataPump EXPDP/IMPDP

~]$ expdp system/oracle directory=dpdir dumpfile=expdp_`date +%Y%m%d`.dmp logfile=expdp_`date +%Y%m%d`.log \
schemas=jove,user01,user02,user03 flashback_scn=4747793

~]$ impdp system/oracle directory=dpdir dumpfile=expdp_20260607.dmp logfile=impdp_`date +%Y%m%d`.log full=y table_exists_action=truncate

5.10 Disable the constraints/triggers/jobs.

・ How to disable,enable the constraints,triggers,jobs for oracle database migration

6 Configuring Target OGG Processes

6.1 Configure the mgr process.

GGSCI> edit params mgr
port 7809
autostart er *
autorestart er *, waitminutes 3, retries 15
purgeoldextracts ./dirdat, usecheckpoints, minkeepdays 7

GGSCI> start mgr
GGSCI> info mgr

6.2 Login to target database.

GGSCI> dblogin userid goldengate, password goldengate

6.3 Add the checkpointtable.

GGSCI> edit params ./GLOBALS
checkpointtable goldengate.checkpoint

GGSCI> add checkpointtable goldengate.checkpoint

6.4 Configure the replicat process.

~]$ cd /ggs
~]$ mkdir -p ./dirdat/rept00
~]$ mkdir -p ./dirrpt/rept00

GGSCI> edit params rept00
replicat rept00
userid goldengate, password goldengate
handlecollisions
assumetargetdefs
discardfile ./dirrpt/rept00/rept00.dsc, append, megabytes 200
map jove.*, target jove.*;
map user01.*, target user01.*;
map user02.*, target user02.*;
map user03.*, target user03.*;

GGSCI> add replicat rept00 exttrail ./dirdat/rept00/rt, checkpointtable goldengate.checkpoint

GGSCI> info rept00

Do not start the replicat process here! Keep it stopped!

7 Start Replication for Production

7.1 Start the datapump process on source.

GGSCI> start pump00
GGSCI> info all

7.2 Start the replicate process on target.

GGSCI> start rept00, aftercsn 4747793    -- 5.8 target database SCN
GGSCI> view report rept00

GGSCI> info all

7.3 Verify the production table has been replicated.

SQL> conn jove/jove
SQL> create public database link to_source connect to jove identified by jove using '192.168.3.156:1521/orcl';

SQL> select count(*) from jove.tbl01@to_source where time <= timestamp '2026-06-07 18:22:59'
union all
select count(*) from jove.tbl01 where time <= timestamp '2026-06-07 18:22:59';

  COUNT(*)
----------
     95727
     95727

SQL> select sum(amount) from jove.tbl01@to_source where time <= timestamp '2026-06-07 18:22:59'
union all
select sum(amount) from jove.tbl01 where time <= timestamp '2026-06-07 18:22:59';

SUM(AMOUNT)
-----------
  479547964
  479547964

8 Switchover

8.1 Deny all clients connections on source.

echo "all:all:deny" >> /etc/hosts.deny
echo "all:192.168.3.165:allow" >> /etc/hosts.allow

8.2 Disable the job queue processes on source.

SQL> show parameter job_queue_processes
SQL> alter system set job_queue_processes=0 scope=both;

8.3 Verify the consistency of the data.

SQL> conn jove/jove
SQL> select count(*) from jove.tbl01@to_source where time <= sysdate
union all
select count(*) from jove.tbl01 where time <= sysdate;

  COUNT(*)
----------
    152883
    152883

SQL> select sum(amount) from jove.tbl01@to_source where time <= sysdate
union all
select sum(amount) from jove.tbl01 where time <= sysdate;

SUM(AMOUNT)
-----------
  765845524
  765845524

8.4 Stop OGG replication.

GGSCI> stop capt00
GGSCI> stop pump00
GGSCI> stop mgr

GGSCI> stop rept00
GGSCI> stop mgr

8.5 Enable the constraints/triggers/jobs on target.

・ How to disable,enable the constraints,triggers,jobs for oracle database migration

8.6 Enable the job queue processes on target.

SQL> show parameter job_queue_processes
SQL> alter system set job_queue_processes=1000 scope=both;

8.7 Enable archive mode.

SQL> archive log list
SQL> alter system set db_recovery_file_dest='+FRA' sid='*' scope=spfile;

~]$ srvctl stop database -d orcl
~]$ srvctl start instance -d orcl -i orcl1 -o mount

SQL> alter database archivelog;
SQL> alter database open;

~]$ srvctl start instance -d orcl -i orcl2