Description:
In this article we are going to see Zero downtime database upgrade from 11g to 12c using Oracle Goldengate
1)Using Goldengate we are going to upgrade from 11g to 12c database without any downtime.
2)Already Data is available source11g scheme Hari.
Environment Detail’s:
High Level Steps upgrade from 11g to 12c database
1)check network between source and target.
2)install goldengate Software both side
3)setup extract and datapump on source site
4)setup replict on target side
5)export and import initial load using SCN
6)start the replicat using on scn
Source 11g database side GG Configuration
Step1:-Login in to 11g server and connect to Goldengate
[oracle@gg-11 gghome]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 12.1.2.1.0 OGGCORE_12.1.2.1.0_PLATFORMS_140727.2135.1_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Aug 7 2014 10:21:34
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2014, Oracle and/or its affiliates. All rights reserved.
GGSCI (gg-11.2.com) dblogin userid gguser,password gguser
Successfully logged into database.
Step2:-Configure manager
GGSCI (gg-11.2.com)view param mgr
PORT 7809
Step3:-Add schematrandata
GGSCI (gg-11.2.com ) add schematrandata hari
2018-07-31 19:56:12 INFO OGG-01788 SCHEMATRANDATA has been added on schema hari.
2018-07-31 19:56:12 INFO OGG-01976 SCHEMATRANDATA for scheduling columns has been added on schema hari.
GGSCI (gg-11.2.com ) info schematrandata hari
2018-07-31 19:56:34 INFO OGG-01785 Schema level supplemental logging is enabled on schema HARI.
2018-07-31 19:56:34 INFO OGG-01980 Schema level supplemental logging is enabled on schema HARI for all scheduling columns.
Step4:-Configure EXTRACT Process
GGSCI (gg-11.2.com) view param ext1
EXTRACT ext1
SETENV (ORACLE_SID=”SOURCE”)
SETENV (ORACLE_HOME = “/u01/app/oracle/product/11.2.0/db_1”)
USERID gguser@source, PASSWORD gguser
DDL INCLUDE ALL
EXTTRAIL /u01/gghome/dirdat/ac
TABLE hari.*;
GGSCI (gg-11.2.com ) add extract ext1 tranlog begin now
EXTRACT added.
GGSCI (gg-11.2.com ) add exttrail /u01/gghome/dirdat/ac extract ext1
EXTTRAIL added.
GGSCI (gg-11.2.com as gguser@source) info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER STOPPED
EXTRACT STOPPED EXT1 00:00:00 00:00:40
Step5:-Configure Pump Process
GGSCI (gg-11.2.com ) view param dpump1
EXTRACT dpump1
USERID gguser@source, PASSWORD gguser
RMTHOST gg-12.2, MGRPORT 7810
RMTTRAIL /u01/gghome/dirdat/ad
DDL INCLUDE ALL
TABLE hari.*;
GGSCI (gg-11.2.com) add extract dpump1 exttrailsource /u01/gghome/dirdat/ac
EXTRACT added.
GGSCI (gg-11.2.com ) add rmttrail /u01/gghome/dirdat/ad extract dpump1
RMTTRAIL added.
GGSCI (gg-11.2.com ) info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER STOPPED
EXTRACT STOPPED DPUMP1 00:00:00 00:01:00
EXTRACT STOPPED EXT1 00:00:00 00:04:40
Step5:-Start all the process and Check
GGSCI (gg-11.2.com ) start ext1
Sending START request to MANAGER …
EXTRACT EXT1 starting
GGSCI (gg-11.2.com ) start dpump1
Sending START request to MANAGER …
EXTRACT DPUMP1 starting
GGSCI (gg-11.2.com ) info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING DPUMP1 00:00:00 00:04:33
EXTRACT RUNNING EXT1 00:00:00 00:00:03
Target side 12c database GG Configuration
Step1:-Configure manager
GGSCI (gg-12.2.com) 9> dblogin userid gguser,password gguser
Successfully logged into database.
GGSCI (gg-12.2.com ) info mgr
Manager is running (IP port gg-12.2.com.7810, Process ID 3999).
GGSCI (gg-12.2.com ) view param mgr
PORT 7810
Step2:-Checkpoint table creation
GGSCI (gg-12.2.com)add checkpointtable gguser.checkpoint
Successfully created checkpoint table gguser.checkpoint.
Step3:-Configure Replicat Process
GGSCI (gg-12.2.com) view param rep1
REPLICAT rep1
ASSUMETARGETDEFS
HANDLECOLLISIONS
USERID gguser@source, PASSWORD gguser
DDL INCLUDE ALL
DDLERROR DEFAULT IGNORE
MAP hari.*, TARGET hari.*;
GGSCI (gg-12.2.com ) add replicat rep1 exttrail /u01/gghome/dirdat/ad checkpointtable gguser.checkpoint
REPLICAT added.
GGSCI (gg-12.2.com ) 12> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT STOPPED REP1 00:00:00 00:00:03
Source side check before datapump starts
GGSCI (gg-11.2.com ) info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING DPUMP1 00:00:00 00:00:00
EXTRACT RUNNING EXT1 00:00:00 00:00:10
Start the initial dataload using Datapump on source side 11g database
SQL> select current_scn from v$database;
CURRENT_SCN
———–
1584462
SQL> select count(*) from hari.chennai;
COUNT(*)
———-
1835008
Step1:-Export the table “chennai” using flashback_scn
[oracle@gg-11 gghome]$ expdp system/oracle dumpfile=hari1.dmp schemas=hari directory=DATA_PUMP_DIR logfile=hari1.log FLASHBACK_SCN=1584462 Export: Release 11.2.0.3.0 - Production on Tue Jul 31 20:02:25 2018 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. 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 "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** dumpfile=hari1.dmp schemas=hari directory=DATA_PUMP_DIR logfile=hari1.log FLASHBACK_SCN=1584462 Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 88 MB 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"."CHENNAI" 72.63 MB 1835008 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/source/dpdump/hari1.dmp Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 20:02:45
Step2:-copy the datapump files to 11g server to 12c server
[oracle@gg-11 gghome]$ cd /u01/app/oracle/admin/source/dpdump/
[oracle@gg-11 dpdump]$ scp hari1.dmp oracle@192.168.2.157:/u01/app/oracle/admin/source/dpdump/
The authenticity of host ‘192.168.2.157 (192.168.2.157)’ can’t be established.
RSA key fingerprint is c2:3d:72:16:52:01:ae:5c:41:6b:34:f5:c7:a1:df:74.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added ‘192.168.2.157’ (RSA) to the list of known hosts.
oracle@192.168.2.157’s password:
hari1.dmp 100% 73MB 72.8MB/s 00:01
Step3:-After export i am inserting more records in Chennai table
[oracle@gg-11 ~]$ sqlplus hari/hari
SQL*Plus: Release 11.2.0.3.0 Production on Tue Jul 31 20:06:47 2018
Copyright (c) 1982, 2011, Oracle. All rights reserved.
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
SQL> insert into chennai select * from chennai;
1835008 rows created.
SQL> commit;
Commit complete.
SQL> select count(*) from chennai;
COUNT(*)
———-
3670016
Step4:-Now we are going to Import 12c database (1835008 Rows)
[oracle@gg-12 dpdump]$ impdp system/oracle dumpfile=hari1.dmp logfile=hari1.log directory=DATA_PUMP_DIR Import: Release 12.2.0.1.0 - Production on Tue Jul 31 20:09:23 2018 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 Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded import done in AL32UTF8 character set and AL16UTF16 NCHAR character set export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set Warning: possible data loss in character set conversions 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"."CHENNAI" 72.63 MB 1835008 rows Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Tue Jul 31 20:09:57 2018 elapsed 0 00:00:24
Step5:-Target (12c) side going to start replicat process Using CSN
GGSCI (gg-12.2.com ) start replicat rep1 aftercsn 1584462 ---(this scn number we get from 11g database) Sending START request to MANAGER ... REPLICAT REP1 starting GGSCI (gg-12.2.com )info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING REPLICAT RUNNING REP1 00:00:00 00:00:01
Note :-Already initial load was completed using datapump,
Now extract was capturing all the changes happening on the source database 11g. These changes will now get applied to the target database 12c by the replicat process
[oracle@gg-12 dpdump]$ sqlplus hari/hari SQL*Plus: Release 12.2.0.1.0 Production on Tue Jul 31 20:19:26 2018 Copyright (c) 1982, 2016, Oracle. All rights reserved. Last Successful login time: Tue Jul 31 2018 20:14:07 +05:30 Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> select count(*) from chennai; COUNT(*) ---------- 3670016
Repoint the users from 11g to 12c server.
Successfully completed without downtime upgrade.
Ref: www.oracledbwr.com