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
-----------
1705812take 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.
DBATracker 