Description:-
In this article we are going to see Zero downtime database upgrade from 12c to 19c using Oracle Goldengate
Environment Details:-
High Level Steps:-
- Check network between source and target
- Install goldengate Software both side
- Setup extract and datapump on source site
- Setup replict on target side
- Export and import initial load using SCN
- Start the replicat using on scn
Step:-1 Check here :- Install Oracle Goldengate 19c On Linux 7
Source 12c oradbwr database side GG Configuration
Step1:-Login in to 12c server and connect to Goldengate
[oracle@12cr2new gg]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 19.1.0.0.4 OGGCORE_19.1.0.0.0_PLATFORMS_191017.1054_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Oct 18 2019 01:38:51
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved.
GGSCI (12cr2new.localdomain) dblogin userid gguser, password gguser
Successfully logged into database.
Step2:-Configure manager
GGSCI (12cr2new.localdomain as gguser@oradbwr) view param mgr
PORT 7809
USERIDALAIS gguser
GGSCI (12cr2new.localdomain as gguser@oradbwr) info mgr
Manager is running (IP port TCP:12cr2new.localdomain.7809, Process ID 10753).
Step3:-Add schematrandata
GGSCI (12cr2new.localdomain as gguser@oradbwr) add schematrandata hari
2019-11-24 22:22:39 INFO OGG-01788 SCHEMATRANDATA has been added on schema “hari”.
2019-11-24 22:22:39 INFO OGG-01976 SCHEMATRANDATA for scheduling columns has been added on schema “hari”.
2019-11-24 22:22:39 INFO OGG-10154 Schema level PREPARECSN set to mode NOWAIT on schema “hari”.
2019-11-24 22:22:41 INFO OGG-10471 ***** Oracle Goldengate support information on table HARI.ORADBWR *****
Oracle Goldengate support native capture on table HARI.ORADBWR.
Oracle Goldengate marked following column as key columns on table HARI.ORADBWR: EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
No unique key is defined for table HARI.ORADBWR.
GGSCI (12cr2new.localdomain as gguser@oradbwr) info schematrandata hari
2019-11-24 22:23:10 INFO OGG-06480 Schema level supplemental logging, excluding non-validated keys, is enabled on schema “HARI”.
2019-11-24 22:23:10 INFO OGG-01980 Schema level supplemental logging is enabled on schema “HARI” for all scheduling columns.
2019-11-24 22:23:10 INFO OGG-10462 Schema “HARI” have 1 prepared tables for instantiation.
Step4:-Configure EXTRACT Process
GGSCI (12cr2new.localdomain) view param ext1
EXTRACT ext1
SETENV (ORACLE_SID=”ORADBWR”)
SETENV (ORACLE_HOME = “/u01/app/oracle/product/12.2.0/dbhome_1”)
USERID gguser@oradbwr, PASSWORD gguser
DDL INCLUDE ALL
EXTTRAIL /u01/app/oracle/product/gg/dirdat/ac
TABLE hari.*;
GSCI (12cr2new.localdomain as gguser@oradbwr) add extract ext1 tranlog begin now
EXTRACT added.
GGSCI (12cr2new.localdomain as gguser@oradbwr) add exttrail /u01/app/oracle/product/gg/dirdat/ac extract ext1
EXTTRAIL added.
GGSCI (12cr2new.localdomain as gguser@oradbwr) info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EXT1 00:00:00 00:00:52
Step4:-Configure Pump Process
GGSCI (12cr2new.localdomain) view param dpump1
EXTRACT dpump1
USERID gguser@dbwr, PASSWORD gguser
RMTHOST 192.168.125.155, MGRPORT 7809
RMTTRAIL /u01/app/oracle/product/gg/dirdat/ad
DDL INCLUDE ALL
TABLE hari.*;
GGSCI (12cr2new.localdomain as gguser@oradbwr) add extract dpump1 exttrailsource /u01/app/oracle/product/gg/dirdat/ac
EXTRACT added.
GGSCI (12cr2new.localdomain as gguser@oradbwr) add rmttrail /u01/app/oracle/product/gg/dirdat/ad extract dpump1
RMTTRAIL added.
GGSCI (12cr2new.localdomain as gguser@oradbwr) info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED DPUMP1 00:00:00 00:00:21
EXTRACT STOPPED EXT1 00:00:00 00:10:09
Step5:-Start all the process and Check
GGSCI (12cr2new.localdomain as gguser@oradbwr) start ext1
Sending START request to MANAGER …
EXTRACT EXT1 starting
GGSCI (12cr2new.localdomain as gguser@oradbwr) start dpump1
Sending START request to MANAGER …
EXTRACT DPUMP1 starting
GGSCI (12cr2new.localdomain as gguser@oradbwr) info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING DPUMP1 00:00:00 00:00:07
EXTRACT RUNNING EXT1 00:00:00 00:00:00
Target side 19c database GG Configuration
Step1:-Configure manager
[oracle@dev19c gg]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 19.1.0.0.4 OGGCORE_19.1.0.0.0_PLATFORMS_191017.1054_FBO
Linux, x64, 64bit (optimized), Oracle 19c on Oct 17 2019 21:16:29
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved.
GGSCI (dev19c) dblogin userid gguser, password gguser
Successfully logged into database.
GGSCI (dev19c as gguser@dbwr) info mgr
Manager is running (IP port TCP:dev19c.7809, Process ID 28541).
Step2:-Checkpoint table creation
GGSCI (dev19c as gguser@dbwr) 3> add checkpointtable gguser.checkpoint
Successfully created checkpoint table gguser.checkpoint.
Step3:-Configure Replicat Process
GGSCI (dev19c)view param rep1
REPLICAT rep1
ASSUMETARGETDEFS
HANDLECOLLISIONS
USERID gguser@dbwr, PASSWORD gguser
DDL INCLUDE ALL
DDLERROR DEFAULT IGNORE
MAP hari.*, TARGET hari.*;
GGSCI (dev19c as gguser@dbwr)add replicat rep1 exttrail /u01/app/oracle/product/gg/dirdat/ad checkpointtable gguser.checkpoint
REPLICAT added.
GGSCI (dev19c as gguser@dbwr)info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT STOPPED REP1 00:00:00 00:00:12
Source side 12c oradbwr check before datapump starts
GGSCI (12cr2new.localdomain as gguser@oradbwr)info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING DPUMP1 00:00:00 00:00:07
EXTRACT RUNNING EXT1 00:00:00 00:00:04
Start the initial dataload using Datapump on source side 12c database
[oracle@12cr2new ~]$ sqlplus ‘/as sysdba’
SQL*Plus: Release 12.2.0.1.0 Production on Sun Nov 24 22:49:11 2019
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
SQL> select current_scn from v$database;
CURRENT_SCN
———–
1547381
SQL> select count(*) from hari.oradbwr;
COUNT(*)
———-
14
Step1:-Export the table oradbwr using flashback_scn
[oracle@12cr2new gg]$ expdp system/oracle dumpfile=hari1.dmp schemas=hari directory=DATA_PUMP_DIR logfile=hari1.log FLASHBACK_SCN=1547381
Export: Release 12.2.0.1.0 – Production on Sun Nov 24 22:51:02 2019
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
FLASHBACK automatically enabled to preserve database integrity.
Starting “SYSTEM”.”SYS_EXPORT_SCHEMA_01″: system/******** dumpfile=hari1.dmp schemas=hari directory=DATA_PUMP_DIR logfile=hari1.log FLASHBACK_SCN=1547381
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
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/TABLE/PROCACT_INSTANCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . exported “HARI”.”ORADBWR” 8.781 KB 14 rows
Master table “SYSTEM”.”SYS_EXPORT_SCHEMA_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/u01/app/oracle/admin/oradbwr/dpdump/hari1.dmp
Job “SYSTEM”.”SYS_EXPORT_SCHEMA_01″ successfully completed at Sun Nov 24 22:53:00 2019 elapsed 0 00:01:34
Step2:-Copy the datapump files to 12c server to 19c server
[oracle@12cr2new gg]$ scp /u01/app/oracle/admin/oradbwr/dpdump/hari1.dmp oracle@192.168.125.155:/u01/app/oracle/admin/dbwr/dpdump/
The authenticity of host ‘192.168.125.155 (192.168.125.155)’ can’t be established.
ECDSA key fingerprint is f8:69:0d:e3:68:0d:24:30:cf:e3:17:6c:7a:59:05:94.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added ‘192.168.125.155’ (ECDSA) to the list of known hosts.
oracle@192.168.125.155’s password:
hari1.dmp 100% 348KB 348.0KB/s 00:00
Step3:-After export i am inserting more records in oradbwr table
[oracle@12cr2new gg]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Sun Nov 24 22:56:02 2019
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
SQL> conn hari/hari
Connected.
SQL> insert into oradbwr select * from oradbwr;
14 rows created.
SQL> /
28 rows created.
SQL> /
56 rows created.
SQL> /
112 rows created.
SQL> commit;
Commit complete.
SQL> select count(*) from oradbwr;
COUNT(*)
———-
224
Target side 19c dbwr start importing
Step4:-Now we are going to Import 19c database (14 rows)
[oracle@dev19c ~]$ impdp system/oracle dumpfile=hari1.dmp logfile=hari1.log directory=DATA_PUMP_DIR
Import: Release 19.0.0.0.0 – Production on Sun Nov 24 22:57:34 2019
Version 19.4.1.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Master table “SYSTEM”.”SYS_IMPORT_FULL_01″ successfully loaded/unloaded
Starting “SYSTEM”.”SYS_IMPORT_FULL_01″: system/******** dumpfile=hari1.dmp logfile=hari1.log directory=DATA_PUMP_DIR
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/TABLE/PROCACT_INSTANCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported “HARI”.”ORADBWR” 8.781 KB 14 rows
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job “SYSTEM”.”SYS_IMPORT_FULL_01″ completed with 1 error(s) at Sun Nov 24 22:58:41 2019 elapsed 0 00:00:44
Step5:-Target (19c) side going to start replicat process Using CSN
GGSCI (dev19c as gguser@dbwr) start replicat rep1 aftercsn 1547381 —(this scn number we get from 12c database)
Sending START request to MANAGER …
REPLICAT REP1 starting
GGSCI (dev19c as gguser@dbwr) info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP1 00:00:00 00:00:06
Note :-Already initial load was completed using datapump,
Now extract was capturing all the changes happening on the source database 12c. These changes will now get applied to the target database 19c by the replicat process
Step:-6 Check the count of table
[oracle@dev19c ~]$ sqlplus hari/hari
SQL*Plus: Release 19.0.0.0.0 – Production on Sun Nov 24 22:59:46 2019
Version 19.4.1.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.4.1.0.0
SQL> select count(*) from oradbwr;
COUNT(*)
———-
224
Successfully data transferred from 12c to 19c database…
Connect with me:-
Telegram App:https://t.me/oracledbwr
LinkedIn:https://www.linkedin.com/in/hariprasathdba
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page : https://www.facebook.com/dbahariprasath/?
Twitter : https://twitter.com/hariprasathdba
