Mô tả:
Bài viết này chia sẻ cách Nâng cấp từ Oracle Database12c lên 19c sử dụng Oracle Goldengate với Zero Downtime
Môi trường:
Các bước chính:
Kiểm tra mạng giữa nguồn và đích
Cài đặt phần mềm goldengate cả hai bên
Thiết lập extract và datapump ở Source
Thiết lập replict ở target
Export và import để khởi tạo dữ liệu dùng SCN
Bắt đầu replicate bằng cách sử dụng trên scn
Bước:-1 Check ở đây: Cài đặt Oracle Goldengate 19c trên Linux 7
Source : Cấu hình GoldenGate trên Source database 12c
Bước 1: Vào máy chủ database 12c và kết nối với 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.
Bước 2: Cấu hình manager
GGSCI (12cr2new.localdomain as gguser@oradbaviet) view param mgr
PORT 7809
USERIDALAIS gguser
GGSCI (12cr2new.localdomain as gguser@oradbaviet) info mgr
Manager is running (IP port TCP:12cr2new.localdomain.7809, Process ID 10753).
Bước 3: Add schematrandata
GGSCI (12cr2new.localdomain as gguser@oradbaviet) 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.ORAdbaviet *****
Oracle Goldengate support native capture on table HARI.ORAdbaviet.
Oracle Goldengate marked following column as key columns on table HARI.ORAdbaviet: EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
No unique key is defined for table HARI.ORAdbaviet.
GGSCI (12cr2new.localdomain as gguser@oradbaviet) 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.
Bước 4: Cấu hình tiến trình EXTRACT
GGSCI (12cr2new.localdomain) view param ext1
EXTRACT ext1
SETENV (ORACLE_SID=”ORAdbaviet”)
SETENV (ORACLE_HOME = “/u01/app/oracle/product/12.2.0/dbhome_1”)
USERID gguser@oradbaviet, PASSWORD gguser
DDL INCLUDE ALL
EXTTRAIL /u01/app/oracle/product/gg/dirdat/ac
TABLE hari.*;
GSCI (12cr2new.localdomain as gguser@oradbaviet) add extract ext1 tranlog begin now
EXTRACT added.
GGSCI (12cr2new.localdomain as gguser@oradbaviet) add exttrail /u01/app/oracle/product/gg/dirdat/ac extract ext1
EXTTRAIL added.
GGSCI (12cr2new.localdomain as gguser@oradbaviet) info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EXT1 00:00:00 00:00:52
Bước 5: Cấu hình tiến trình Pump
GGSCI (12cr2new.localdomain) view param dpump1
EXTRACT dpump1
USERID gguser@dbaviet, 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@oradbaviet) add extract dpump1 exttrailsource /u01/app/oracle/product/gg/dirdat/ac
EXTRACT added.
GGSCI (12cr2new.localdomain as gguser@oradbaviet) add rmttrail /u01/app/oracle/product/gg/dirdat/ad extract dpump1
RMTTRAIL added.
GGSCI (12cr2new.localdomain as gguser@oradbaviet) 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
Bước 6: Bật các tiến trình và kiểm tra
GGSCI (12cr2new.localdomain as gguser@oradbaviet) start ext1
Sending START request to MANAGER …
EXTRACT EXT1 starting
GGSCI (12cr2new.localdomain as gguser@oradbaviet) start dpump1
Sending START request to MANAGER …
EXTRACT DPUMP1 starting
GGSCI (12cr2new.localdomain as gguser@oradbaviet) 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: Cấu hình GoldenGate ở đích database 19c
Bước 1: Cấu hình 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@dbaviet) info mgr
Manager is running (IP port TCP:dev19c.7809, Process ID 28541).
Bước 2: Tạo bảng Checkpoint
GGSCI (dev19c as gguser@dbaviet) 3> add checkpointtable gguser.checkpoint
Successfully created checkpoint table gguser.checkpoint.
Bước 3: Cấu hình tiến tình replicate
GGSCI (dev19c)view param rep1
REPLICAT rep1
ASSUMETARGETDEFS
HANDLECOLLISIONS
USERID gguser@dbaviet, PASSWORD gguser
DDL INCLUDE ALL
DDLERROR DEFAULT IGNORE
MAP hari.*, TARGET hari.*;
GGSCI (dev19c as gguser@dbaviet)add replicat rep1 exttrail /u01/app/oracle/product/gg/dirdat/ad checkpointtable gguser.checkpoint
REPLICAT added.
GGSCI (dev19c as gguser@dbaviet)info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT STOPPED REP1 00:00:00 00:00:12
Source: Kiểm tra các tiến trình trước khi export dữ liệu
GGSCI (12cr2new.localdomain as gguser@oradbaviet)> 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
Source: Bắt đầu khởi tạo dữ liệu dùng Datapump trên database 12c
[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.oradbaviet;
COUNT(*)
———-
14
Bước 1: Export các bảng sử dụng 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”.”ORAdbaviet” 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/oradbaviet/dpdump/hari1.dmp
Job “SYSTEM”.”SYS_EXPORT_SCHEMA_01″ successfully completed at Sun Nov 24 22:53:00 2019 elapsed 0 00:01:34
Bước 2: Copy datapump files từ server 12c tới server 19c:
[oracle@12cr2new gg]$ scp /u01/app/oracle/admin/oradbaviet/dpdump/hari1.dmp oracle@192.168.125.155:/u01/app/oracle/admin/dbaviet/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
Bước 3: Sau khi export, test thử thêm dữ liệu vào bảng oradbaviet
[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 oradbaviet select * from oradbaviet;
14 rows created.
SQL> /
28 rows created.
SQL> /
56 rows created.
SQL> /
112 rows created.
SQL> commit;
Commit complete.
SQL> select count(*) from oradbaviet;
COUNT(*)
———-
224
Target: Phía 19c tiến hành import
Bước 4: Tiến hành import vào database 19c (banr dump chỉ có 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”.”ORAdbaviet” 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
Bước 5: Bật tiến trình sử dụng SCN
GGSCI (dev19c as gguser@dbaviet) 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@dbaviet) info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP1 00:00:00 00:00:06
Bước 6: Kiểm tra số row của bảng
[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 oradbaviet;
COUNT(*)
———-
224
Như vậy là thành công khi chuyển dữ liệu từ database 12c lên 19c thành công, các bảng lớn, schema lớn phân tách ra các nhóm và làm tương tự.
Hy vọng hữu ích cho bạn.
* KHOÁ HỌC ORACLE DATABASE A-Z ENTERPRISE trực tiếp từ tôi giúp bạn bước đầu trở thành những chuyên gia DBA, đủ kinh nghiệm đi thi chứng chỉ OA/OCP, đặc biệt là rất nhiều kinh nghiệm, bí kíp thực chiến trên các hệ thống Core tại VN chỉ sau 1 khoá học.
* CÁCH ĐĂNG KÝ: Gõ (.) hoặc để lại số điện thoại hoặc inbox https://m.me/tranvanbinh.vn hoặc Hotline/Zalo 090.29.12.888
* Chi tiết tham khảo:
https://bit.ly/oaz_w
=============================
KẾT NỐI VỚI CHUYÊN GIA TRẦN VĂN BÌNH:
📧 Mail: binhoracle@gmail.com
☎️ Mobile: 0902912888
⚡️ Skype: tranbinh48ca
👨 Facebook: https://www.facebook.com/BinhOracleMaster
👨 Inbox Messenger: https://m.me/101036604657441 (profile)
👨 Fanpage: https://www.facebook.com/tranvanbinh.vn
👨 Inbox Fanpage: https://m.me/tranvanbinh.vn
👨👩 Group FB: https://www.facebook.com/groups/DBAVietNam
👨 Website: https://www.tranvanbinh.vn
👨 Blogger: https://tranvanbinhmaster.blogspot.com
🎬 Youtube: http://bit.ly/ytb_binhoraclemaster
👨 Tiktok: https://www.tiktok.com/@binhoraclemaster?lang=vi
👨 Linkin: https://www.linkedin.com/in/binhoracle
👨 Twitter: https://twitter.com/binhoracle
👨 Địa chỉ: Tòa nhà Sun Square - 21 Lê Đức Thọ - Phường Mỹ Đình 1 - Quận Nam Từ Liêm - TP.Hà Nội
=============================
học oracle database, Tự học Oracle, Tài liệu Oracle 12c tiếng Việt, Hướng dẫn sử dụng Oracle Database, Oracle SQL cơ bản, Oracle SQL là gì, Khóa học Oracle Hà Nội, Học chứng chỉ Oracle ở đầu, Khóa học Oracle online,khóa học pl/sql, học dba, học dba ở việt nam, khóa học dba, khóa học dba sql, tài liệu học dba oracle, Khóa học Oracle online, học oracle sql, học oracle ở đâu tphcm, học oracle bắt đầu từ đâu, học oracle ở hà nội, oracle database tutorial, oracle database 12c, oracle database là gì, oracle database 11g, oracle download, oracle database 19c, oracle dba tutorial, oracle tunning, sql tunning , oracle 12c, oracle multitenant, Container Databases (CDB), Pluggable Databases (PDB), oracle cloud, oracle security, oracle fga, audit_trail, oracle dataguard, oracle goldengate, mview, oracle exadata, oracle oca, oracle ocp, oracle ocm , oracle weblogic, middleware, hoc solaris, hoc linux, hoc aix, unix, securecrt, xshell, mobaxterm, putty
