Source – TRAIL12@ oel6-node01 – 11.2.0.3
Target – TRAIL12@ oel6-node02 – 12.1.0.2.0
golden gate tablespace - oggdata
goldengate user - oggadm

We are upgrading TRAIL12 database from 11.2.0.3 to 12.1.0.2.0 and we want to keep the database name same so don’t get confused. For this purpose we will create a blank 12c database on target sever oel6-node02.

Install OGG 12c on source and target

Now install OGG – 12.1.2.0.0 at source and target database. Refer this document for steps

https://dbatracker.com/2020/01/26/golden-gate-install-12-1-2-0-0/

remember to select the correct option for both source and target while installing OGG

source –

oracle goldengate for oracle database 11g

target

oracle goldengate for oracle database 12c

suppose we have user data in two schemas ping and pong

SYS @ trail12 > select owner,object_type , count(*) from dba_objects where owner in ('PING','PONG') group  by owner,object_type;

OWNER                          OBJECT_TYPE           COUNT(*)
------------------------------ ------------------- ----------
PONG                           TABLE                        7
PONG                           INDEX                       19
PONG                           VIEW                         1
PONG                           SEQUENCE                     3
PONG                           PROCEDURE                    2
PONG                           TRIGGER                      2
PING                           TABLE                        4
PING                           INDEX                        2

8 rows selected.

prepare the source db

SYS @ trail12 > select name,open_mode,host_name,version from v$database, v$instance;

NAME      OPEN_MODE            HOST_NAME                      VERSION
--------- -------------------- ------------------------------ -----------------
TRAIL12   READ WRITE           oel6-node01.localdomain        11.2.0.3.0

SYS @ trail12 > SELECT log_mode FROM v$database;

LOG_MODE
------------
ARCHIVELOG

SYS @ trail12 > SELECT force_logging, supplemental_log_data_min FROM v$database;

FOR SUPPLEME
--- --------
NO  NO


SYS @ trail12 > ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Database altered.

SYS @ trail12 > ALTER DATABASE FORCE LOGGING;

Database altered.

SYS @ trail12 > ALTER SYSTEM SWITCH LOGFILE;

System altered.

SYS @ trail12 > SELECT force_logging, supplemental_log_data_min FROM v$database;


FOR SUPPLEME
--- --------
YES YES

SYS @ trail12 > create tablespace oggdata datafile '/u01/app/oracle/oradata/trail12/oggdata01.dbf' size 100M extent management local uniform size 256k;

Tablespace created.

SYS @ trail12 > CREATE USER oggadm IDENTIFIED BY Welcome1 default tablespace oggdata;

User created.


SYS @ trail12 > GRANT dba TO oggadm;

Grant succeeded.

Add tns entry on source and target

source_db =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oel6-node01.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = trail12)
    )
  )

target_db =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oel6-node02.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = trail12)
    )
  )

install ddl capture on source

SYS @ trail12 > !pwd
/home/oracle/goldengate_12c

SYS @ trail12 > @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:oggadm


Marker setup table script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to OGGADM

MARKER TABLE
-------------------------------
OK

MARKER SEQUENCE
-------------------------------
OK

Script complete.
SYS @ trail12 > @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:oggadm

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.







WARNING: Tablespace OGGDATA does not have AUTOEXTEND enabled.



Using OGGADM 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 OGGADM

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
------------------------------------------------------------------------------------------------------------------------
0

LOCATION OF DDL TRACE FILE
------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/trail12/trail12/trace/ggs_ddl_trace.log

Analyzing installation status...


VERSION OF DDL REPLICATION
------------------------------------------------------------------------------------------------------------------------
OGGCORE_12.1.2.0.0_PLATFORMS_130924.1316

STATUS OF DDL REPLICATION
------------------------------------------------------------------------------------------------------------------------
SUCCESSFUL installation of DDL Replication software components

Script complete.
SYS @ trail12 > @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:oggadm
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.
SYS @ trail12 > GRANT GGS_GGSUSER_ROLE TO oggadm;

Grant succeeded.

SYS @ trail12 > @ddl_enable.sql

Trigger altered.

SYS @ trail12 > @sequence.sql
Please enter the name of a schema for the GoldenGate database objects:
oggadm
Setting schema name to OGGADM

UPDATE_SEQUENCE STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

GETSEQFLUSH

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

SEQTRACE

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

REPLICATE_SEQUENCE STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

STATUS OF SEQUENCE SUPPORT
--------------------------------------------------------------
SUCCESSFUL installation of Oracle Sequence Replication support
SYS @ trail12 > exec DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE (grantee=>'oggadm', privilege_type=>'capture',grant_select_privileges=>true, do_grants=>TRUE);

PL/SQL procedure successfully completed.

SYS @ trail12 >

prepare the target

as our target is on 12c we need to enable ENABLE_GOLDENGATE_REPLICATION=TRUE

SYS @ trail12 > col HOST_NAME for a30
SYS @ trail12 > set lines 150
SYS @ trail12 >  select name,open_mode,host_name,version from v$database, v$instance;

NAME      OPEN_MODE            HOST_NAME                      VERSION
--------- -------------------- ------------------------------ -----------------
TRAIL12   READ WRITE           oel6-node02.localdomain        12.1.0.2.0

SYS @ trail12 > create tablespace oggdata datafile '/u01/app/oracle/oradata/TRAIL12/datafile/oggdata01.dbf' size 100M extent management local uniform size 256k;

Tablespace created.

SYS @ trail12 > CREATE USER oggadm IDENTIFIED BY Welcome1 default tablespace oggdata;

User created.

SYS @ trail12 > GRANT dba TO oggadm;

Grant succeeded.

SYS @ trail12 > ALTER DATABASE FORCE LOGGING;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION=TRUE SCOPE=BOTH;
Database altered.

SYS @ trail12 >
Database altered.

SYS @ trail12 >



System altered.


SYS @ trail12 > select LOG_MODE from v$database;
select SUPPLEMENTAL_LOG_DATA_MIN, FORCE_LOGGING from v$database;
show parameter ENABLE_GOLDENGATE_REPLICATION
LOG_MODE
------------
NOARCHIVELOG

SYS @ trail12 >
SUPPLEME FORCE_LOGGING
-------- ---------------------------------------
YES      YES

SYS @ trail12 >

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
enable_goldengate_replication        boolean     TRUE

add trandata for ping and pong schema tables on source

[oracle@oel6-node01 goldengate_12c]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.1.2.0.0 17185003 OGGCORE_12.1.2.0.0_PLATFORMS_130924.1316_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Sep 25 2013 00:31:13
Operating system character set identified as UTF-8.

Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved.



GGSCI (oel6-node01.localdomain) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING


GGSCI (oel6-node01.localdomain) 2> dblogin userid oggadm , password Welcome1
Successfully logged into database.


GGSCI (oel6-node01.localdomain) 3> info trandata ping.*

Logging of supplemental redo log data is disabled for table PING.BONUS.

Logging of supplemental redo log data is disabled for table PING.DEPT.

Logging of supplemental redo log data is disabled for table PING.EMP.

Logging of supplemental redo log data is disabled for table PING.SALGRADE.

GGSCI (oel6-node01.localdomain) 6> add trandata ping.*

2020-05-25 16:25:04  WARNING OGG-06439  No unique key is defined for table BONUS. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.

Logging of supplemental redo data enabled for table PING.BONUS.
TRANDATA for scheduling columns has been added on table 'PING.BONUS'.
Logging of supplemental redo data enabled for table PING.DEPT.
TRANDATA for scheduling columns has been added on table 'PING.DEPT'.
Logging of supplemental redo data enabled for table PING.EMP.
TRANDATA for scheduling columns has been added on table 'PING.EMP'.
2020-05-25 16:25:08  WARNING OGG-06439  No unique key is defined for table SALGRADE. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.

Logging of supplemental redo data enabled for table PING.SALGRADE.
TRANDATA for scheduling columns has been added on table 'PING.SALGRADE'.
GGSCI (oel6-node01.localdomain) 7> info trandata ping.*

Logging of supplemental redo log data is enabled for table PING.BONUS.

Columns supplementally logged for table PING.BONUS: SAL, COMM, ENAME, JOB.

Logging of supplemental redo log data is enabled for table PING.DEPT.

Columns supplementally logged for table PING.DEPT: DEPTNO.

Logging of supplemental redo log data is enabled for table PING.EMP.

Columns supplementally logged for table PING.EMP: EMPNO.

Logging of supplemental redo log data is enabled for table PING.SALGRADE.

Columns supplementally logged for table PING.SALGRADE: LOSAL, HISAL, GRADE.

GGSCI (oel6-node01.localdomain) 8> info trandata pong.*

Logging of supplemental redo log data is disabled for table PONG.COUNTRIES.

Logging of supplemental redo log data is disabled for table PONG.DEPARTMENTS.

Logging of supplemental redo log data is disabled for table PONG.EMPLOYEES.

Logging of supplemental redo log data is disabled for table PONG.JOBS.

Logging of supplemental redo log data is disabled for table PONG.JOB_HISTORY.

Logging of supplemental redo log data is disabled for table PONG.LOCATIONS.

Logging of supplemental redo log data is disabled for table PONG.REGIONS.

GGSCI (oel6-node01.localdomain) 9> add trandata pong.*

Logging of supplemental redo data enabled for table PONG.COUNTRIES.
TRANDATA for scheduling columns has been added on table 'PONG.COUNTRIES'.
Logging of supplemental redo data enabled for table PONG.DEPARTMENTS.
TRANDATA for scheduling columns has been added on table 'PONG.DEPARTMENTS'.
Logging of supplemental redo data enabled for table PONG.EMPLOYEES.
TRANDATA for scheduling columns has been added on table 'PONG.EMPLOYEES'.
Logging of supplemental redo data enabled for table PONG.JOBS.
TRANDATA for scheduling columns has been added on table 'PONG.JOBS'.
Logging of supplemental redo data enabled for table PONG.JOB_HISTORY.
TRANDATA for scheduling columns has been added on table 'PONG.JOB_HISTORY'.
Logging of supplemental redo data enabled for table PONG.LOCATIONS.
TRANDATA for scheduling columns has been added on table 'PONG.LOCATIONS'.
Logging of supplemental redo data enabled for table PONG.REGIONS.
TRANDATA for scheduling columns has been added on table 'PONG.REGIONS'.
GGSCI (oel6-node01.localdomain) 10> info trandata pong.*

Logging of supplemental redo log data is enabled for table PONG.COUNTRIES.

Columns supplementally logged for table PONG.COUNTRIES: COUNTRY_ID.

Logging of supplemental redo log data is enabled for table PONG.DEPARTMENTS.

Columns supplementally logged for table PONG.DEPARTMENTS: DEPARTMENT_ID.

Logging of supplemental redo log data is enabled for table PONG.EMPLOYEES.

Columns supplementally logged for table PONG.EMPLOYEES: EMPLOYEE_ID.

Logging of supplemental redo log data is enabled for table PONG.JOBS.

Columns supplementally logged for table PONG.JOBS: JOB_ID.

Logging of supplemental redo log data is enabled for table PONG.JOB_HISTORY.

Columns supplementally logged for table PONG.JOB_HISTORY: EMPLOYEE_ID, START_DATE.

Logging of supplemental redo log data is enabled for table PONG.LOCATIONS.

Columns supplementally logged for table PONG.LOCATIONS: LOCATION_ID.

Logging of supplemental redo log data is enabled for table PONG.REGIONS.

Columns supplementally logged for table PONG.REGIONS: REGION_ID.

extarct and pump parameter file on source

GGSCI (oel6-node01.localdomain) 13> view params src_ext

EXTRACT src_ext
USERID oggadm@source_db, PASSWORD Welcome1
EXTTRAIL /home/oracle/goldengate_12c/dirdat/ee
DDL INCLUDE ALL
TABLE ping.*;
TABLE pong.*


GGSCI (oel6-node01.localdomain) 14> view params mgr

PORT 7809


GGSCI (oel6-node01.localdomain) 16> view params src_pmp

EXTRACT src_pmp
USERID oggadm@source_db, PASSWORD Welcome1
RMTHOST oel6-node02, MGRPORT 7809
RMTTRAIL /home/oracle/golden_gate_12c/dirdat/pp
DDL INCLUDE ALL
TABLE ping.*;
TABLE pong.*;

replicat parameter file on target

GGSCI (oel6-node02.localdomain) 3> view params tgt_rep

REPLICAT tgt_rep
USERID oggadm@target_db, PASSWORD Welcome1
DDL INCLUDE ALL
DDLERROR DEFAULT IGNORE
ASSUMETARGETDEFS
MAP ping.*, TARGET ping.*;
MAP pong.*, TARGET pong.*;


GGSCI (oel6-node02.localdomain) 4> view params mgr

PORT 7809

add extract and pump

GGSCI (oel6-node01.localdomain) 17> add extract src_ext tranlog begin now
EXTRACT added.


GGSCI (oel6-node01.localdomain) 18> add exttrail /home/oracle/goldengate_12c/dirdat/ee extract src_ext
EXTTRAIL added.


GGSCI (oel6-node01.localdomain) 19> start extract src_ext

Sending START request to MANAGER ...
EXTRACT SRC_EXT starting




GGSCI (oel6-node01.localdomain) 20> add extract src_pmp exttrailsource /home/oracle/goldengate_12c/dirdat/ee
EXTRACT added.


GGSCI (oel6-node01.localdomain) 21> add rmttrail /home/oracle/golden_gate_12c/dirdat/pp extract  src_pmp
RMTTRAIL added.


GGSCI (oel6-node01.localdomain) 22> start extract src_pmp

Sending START request to MANAGER ...
EXTRACT SRC_PMP starting

add checkpoint table on target

GGSCI (oel6-node02.localdomain) 5> dblogin userid oggadm password Welcome1
Successfully logged into database.

GGSCI (oel6-node02.localdomain) 6> add checkpointtable oggadm.chkpt

Successfully created checkpoint table oggadm.chkpt.

GGSCI (oel6-node02.localdomain) 7> info checkpointtable oggadm.chkpt

Checkpoint table oggadm.chkpt created 2020-05-25 16:49:09.


GGSCI (oel6-node02.localdomain) 15> add replicat tgt_rep , exttrail /home/oracle/golden_gate_12c/dirdat/pp checkpointtable oggadm.chkpt
REPLICAT added.

initial load using scn

check current scn on source

SYS @ trail12 > col HOST_NAME for a30
SYS @ trail12 > set lines 150
SYS @ trail12 > select name,open_mode,host_name,version from v$database, v$instance;

NAME      OPEN_MODE            HOST_NAME                      VERSION
--------- -------------------- ------------------------------ -----------------
TRAIL12   READ WRITE           oel6-node01.localdomain        11.2.0.3.0

SYS @ trail12 > select current_scn from v$database;

CURRENT_SCN
-----------
    1705812

take expdp backup using flashback_scn

[oracle@oel6-node01 dpdump]$ expdp directory=DATA_PUMP_DIR schemas=ping,pong dumpfile=ping_pong.dmp logfile=ping_pong.log flashback_scn=1705812

Export: Release 11.2.0.3.0 - Production on Mon May 25 17:05:09 2020

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYS"."SYS_EXPORT_SCHEMA_01":  /******** AS SYSDBA directory=DATA_PUMP_DIR schemas=ping,pong dumpfile=ping_pong.dmp logfile=ping_pong.log flashback_scn=1705812
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 640 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "PING"."DEPT"                               5.929 KB       4 rows
. . exported "PING"."EMP"                                8.562 KB      14 rows
. . exported "PING"."SALGRADE"                           5.859 KB       5 rows
. . exported "PONG"."COUNTRIES"                          6.367 KB      25 rows
. . exported "PONG"."DEPARTMENTS"                        7.007 KB      27 rows
. . exported "PONG"."EMPLOYEES"                          16.81 KB     107 rows
. . exported "PONG"."JOBS"                               6.992 KB      19 rows
. . exported "PONG"."JOB_HISTORY"                        7.054 KB      10 rows
. . exported "PONG"."LOCATIONS"                          8.273 KB      23 rows
. . exported "PONG"."REGIONS"                            5.476 KB       4 rows
. . exported "PING"."BONUS"                                  0 KB       0 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
  /u01/app/oracle/admin/trail12/dpdump/ping_pong.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 17:05:36

copy the dmp file to target

[oracle@oel6-node01 ~]$ cd /u01/app/oracle/admin/trail12/dpdump/
[oracle@oel6-node01 dpdump]$ ls -ltr
total 159124
-rw-r-----. 1 oracle oinstall 162127872 May 25 12:24 trail12_full.dmp
-rw-r-----. 1 oracle oinstall    116816 May 25 12:24 trail12_full.log
-rw-r-----. 1 oracle oinstall    340990 May 25 15:59 trail12_ping_pong.log
-rw-r-----. 1 oracle oinstall    655360 May 25 16:55 ping_pong.dmp
-rw-r-----. 1 oracle oinstall      2927 May 25 16:55 ping_pong.log
[oracle@oel6-node01 dpdump]$ scp ping_pong.dmp oel6-node02:/u01/app/oracle/admin/trail12/dpdump/
The authenticity of host 'oel6-node02 (192.168.189.102)' can't be established.
RSA key fingerprint is 21:15:05:c1:76:e6:13:08:75:ba:66:e9:b2:22:b4:cf.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'oel6-node02' (RSA) to the list of known hosts.
oracle@oel6-node02's password:
ping_pong.dmp                                                                                                                                              100%  640KB 640.0KB/s   00:00
[oracle@oel6-node01 dpdump]$

lets suppose we created few tables and inserted records after the export backup and before import

SYS @ trail12 > select name,open_mode,host_name,version from v$database, v$instance;

NAME      OPEN_MODE            HOST_NAME                      VERSION
--------- -------------------- ------------------------------ -----------------
TRAIL12   READ WRITE           oel6-node01.localdomain        11.2.0.3.0

PING @ trail12 > create table new(id number);

Table created.

PING @ trail12 > insert into new values(1);

1 row created.

PING @ trail12 >  insert into new values(2);

1 row created.

PING @ trail12 > commit;

Commit complete.

import the ping pong schemas in target

[oracle@oel6-node02 ~]$ impdp DIRECTORY=DATA_PUMP_DIR DUMPFILE=ping_pong.dmp logfile=ping_pong_imp.log remap_SCHEMA=ping:ping remap_schema=pong:pong

Import: Release 12.1.0.2.0 - Production on Mon May 25 17:16:23 2020

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01":  /******** AS SYSDBA DIRECTORY=DATA_PUMP_DIR DUMPFILE=ping_pong.dmp logfile=ping_pong_imp.log remap_SCHEMA=ping:ping remap_schema=pong:pong
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "PING"."DEPT"                               5.929 KB       4 rows
. . imported "PING"."EMP"                                8.562 KB      14 rows
. . imported "PING"."SALGRADE"                           5.859 KB       5 rows
. . imported "PONG"."COUNTRIES"                          6.367 KB      25 rows
. . imported "PONG"."DEPARTMENTS"                        7.007 KB      27 rows
. . imported "PONG"."EMPLOYEES"                          16.81 KB     107 rows
. . imported "PONG"."JOBS"                               6.992 KB      19 rows
. . imported "PONG"."JOB_HISTORY"                        7.054 KB      10 rows
. . imported "PONG"."LOCATIONS"                          8.273 KB      23 rows
. . imported "PONG"."REGIONS"                            5.476 KB       4 rows
. . imported "PING"."BONUS"                                  0 KB       0 rows
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at Mon May 25 17:16:36 2020 elapsed 0 00:00:08

Lets see if this table goes to target after the replicat is started

start the replicat using aftercsn

GGSCI (oel6-node02.localdomain) 16> start replicat REPTGT, aftercsn 1705812
ERROR: REPLICAT REPTGT does not exist.


GGSCI (oel6-node02.localdomain) 17> start replicat tgt_rep , aftercsn 1705812

Sending START request to MANAGER ...
REPLICAT TGT_REP starting


GGSCI (oel6-node02.localdomain) 18> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    STOPPED     TGT_REP     00:00:00      00:34:09


GGSCI (oel6-node02.localdomain) 19> start replicat tgt_rep , aftercsn 1705812

Sending START request to MANAGER ...
REPLICAT TGT_REP starting


GGSCI (oel6-node02.localdomain) 25> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING     TGT_REP     00:00:00      00:00:06

we can see that table which was created in between the initial load got replicated in target

SYS @ trail12 > col HOST_NAME for a30
SYS @ trail12 > set lines 150
SYS @ trail12 > select name,open_mode,host_name,version from v$database, v$instance;

NAME      OPEN_MODE            HOST_NAME                      VERSION
--------- -------------------- ------------------------------ -----------------
TRAIL12   READ WRITE           oel6-node02.localdomain        12.1.0.2.0

SYS @ trail12 > conn ping/ping
Connected.
PING @ trail12 > select * from new;

        ID
----------
         1
         2

So now our dataabse will keep syncing with the source. Any DML /DDL on ping/pong schemas will get replicated to target. Now whenever we are ready we need to divert all connections to our target database which is on 12c and all data same as source.