Thứ Ba, 1 tháng 11, 2022

Nâng cấp từ Oracle Database 11g, 12c lên 19c sử dụng Oracle Goldengate với Zero Downtime

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 oradbaviet

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

ĐỌC NHIỀU

Trần Văn Bình - Oracle Database Master