Trong bài viết này, chúng tôi sẽ giả sử rằng chúng tôi đã cài đặt phần mềm GoldenGate, nếu chưa, bạn có thể làm theo các bước sau tại đây:
Chúng tôi sẽ sao chép schema HR từ source tới target:
SOURCE (PDB): | TARGET (KHÔNG CÓ CDB): |
tên máy chủ: racnode1 | tên máy chủ: standbyracnode |
IP: 192.168.24.1 | IP: 192.168.24.3 |
RDBMS: 19c | RDBMS: 19c |
SID: cdb19c1 | SID: devdbnoncdb |
PDB: CDB19C_PDB | PDB: N/A |
ORACLE_HOME: /u01/app/oracle/product/19c/dbhome_1 | ORACLE_HOME: /u01/app/oracle/product/19c/db_1 |
GoldenGate Home: /u01/app/goldengate/19.1.0.0/ | GoldenGate Home: /u01/app/goldengate/19.1.0.0/ |
Xin lưu ý rằng vì chúng tôi đang làm việc với PDB, nên chúng tôi cần sử dụng phần INTERGRATED extract
Dưới đây là các bước để thực hiện hoạt động này:
1:- Initial load/Import dữ liệu vào DB đích (target):
EXPORT Ở SOURCE:
[oracle@RACnode1 ~]$ mkdir /tmp/EXPORT
[oracle@RACnode1 ~]$ chown oracle:oinstall /tmp/EXPORT
[oracle@RACnode1 ~]$
SQL> alter session set container=CDB19C_PDB;
Session altered.
SQL> create or replace directory HR_SYNC as '/tmp/EXPORT';
Directory created.
[oracle@RACnode1 ~]$ cd /tmp/EXPORT
[oracle@RACnode1 EXPORT]$ vi expdp_script.sh
[oracle@RACnode1 EXPORT]$ ls -tlr
total 4
-rw-r--r--. 1 oracle oinstall 236 Oct 1 10:11 expdp_script.sh
[oracle@RACnode1 EXPORT]$ chmod 744 expdp_script.sh
[oracle@RACnode1 EXPORT]$ cat expdp_script.sh
#!/bin/bash
SYS_PWD=Pas5w0rd
export ORACLE_HOME=/u01/app/oracle/product/19c/dbhome_1
nohup $ORACLE_HOME/bin/expdp \"sys/$SYS_PWD@CDB19C_PDB as sysdba\" directory=HR_SYNC dumpfile=HR_%U.dmp logfile=HR_EXPORT.log cluster=NO schemas=HR &
[oracle@RACnode1 EXPORT]$
Đảm bảo rằng CDB19C_PDB được khai báo trong tnsnames.ora:
[or acle@RACnode1 EXPORT]$ tnsping cdb19c_pdb
...
Use d TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDR ESS = (PROTOCOL = TCP)(HOST = racnode1)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = CDB19C_PDB)))
OK (10 msec)
[oracle@RACnode1 EXPORT]$
[oracle@RACnode1 EXPORT]$ ./expdp_script.sh
[oracle@RACnode1 EXPORT]$ tail HR_EXPORT.log
. . exported "HR"."JOB_HISTORY" 7.195 KB 10 rows
. . exported "HR"."JOBS" 7.109 KB 19 rows
. . exported "HR"."DEPARTMENTS" 7.125 KB 27 rows
. . exported "HR"."COUNTRIES" 6.367 KB 25 rows
. . exported "HR"."REGIONS" 5.546 KB 4 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
/tmp/EXPORT/HR_01.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Fri Oct 1 10:39:48 2021 elapsed 0 00:04:12
[oracle@RACnode1 EXPORT]$
IMPORT VÀO TARGET:
[oracle@standbyracnode ~]$ mkdir /tmp/IMPORT
[oracle@standbyracnode ~]$
[oracle@RACnode1 EXPORT]$ scp -p HR_01.dmp oracle@standbyracnode:/tmp/IMPORT
oracle@standbyracnode's password:
HR_01.dmp 100% 716KB 32.3MB/s 00:00
[oracle@RACnode1 EXPORT]$
SQL> create or replace directory HR_SYNC as '/tmp/IMPORT';
Directory created.
SQL>
[oracle@standbyracnode IMPORT]$ cat import_script.sh
#!/bin/bash
SYS_PWD=Pas5w0rd
export ORACLE_HOME=/u01/app/oracle/product/19c/db_1
nohup $ORACLE_HOME/bin/impdp \"sys/$SYS_PWD@DEVDB_NONCDB as sysdba\" directory=HR_SYNC dumpfile=HR_%U.dmp logfile=HR_IMPORt.log cluster=NO schemas=HR &
[oracle@standbyracnode IMPORT]$
[oracle@standbyracnode IMPORT]$ ./import_script.sh
[oracle@standbyracnode IMPORT]$ tail HR_IMPORt.log
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
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/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "SYS"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at Fri Oct 1 13:51:55 2021 elapsed 0 00:02:03
[oracle@standbyracnode IMPORT]$
**ERROR for HR schema already exist, can be ignored
2:- Chuẩn bị SOURCE DB:
Bật supplemental log:
SQL> ALTER DATABASE FORCE LOGGING;
Database altered.
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
Database altered.
SQL> ALTER SYSTEM SET enable_goldengate_replication=TRUE SCOPE=BOTH;
System altered.
SQL>
SQL> alter session set container=CDB19C_PDB;
Session altered.
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
Database altered.
SQL>
Tạo common user:
SQL> CREATE USER c##ggadmin IDENTIFIED BY ggadmin
DEFAULT TABLESPACE users
QUOTA UNLIMITED ON users;
User created.
SQL> GRANT DBA to c##ggadmin CONTAINER=ALL;
Grant succeeded.
SQL> exec dbms_goldengate_auth.grant_admin_privilege( grantee => 'c##ggadmin',container => 'ALL');
PL/SQL procedure successfully completed.
SQL>
3:- Chuẩn bị TARGET DB :
Bật RDBMS để sử dụng GoldenGate:
SQL> ALTER SYSTEM SET enable_goldengate_replication=TRUE SCOPE=BOTH;
System altered.
Tạo common user:
SQL> CREATE USER ggadmin IDENTIFIED BY ggadmin
DEFAULT TABLESPACE users
QUOTA UNLIMITED ON users;
User created.
SQL> GRANT DBA TO ggadmin;
Grant succeeded.
SQL> EXEC dbms_goldengate_auth.grant_admin_privilege('ggadmin');
PL/SQL procedure successfully completed.
SQL>
4:- Chuẩn bị GoldenGate ở NGUỒN:
Tạo Manager parameter:
GGSCI (RACnode1.localdomain) 1> EDIT PARAM mgr
PORT 7809
PURGEOLDEXTRACTS ./dirdat/et* , USECHECKPOINTS, MINKEEPHOURS 72
AUTORESTART ER *, RETRIES 3, WAITMINUTES 5, RESETMINUTES 60
DOWNREPORTMINUTES 15
LAGCRITICALSECONDS 10
LAGINFOMINUTES 0
LAGREPORTMINUTES 15
ACCESSRULE, PROG *, IPADDR 192.168.24.3, ALLOW
Tạo credentialstore:
GGSCI (RACnode1.localdomain) 5> add credentialstore
Credential store created.
GGSCI (RACnode1.localdomain) 6> alter credentialstore add user c##ggadmin@192.168.24.1:1521/cdb19c alias cdb19c
Password:
Credential store altered.
GGSCI (RACnode1.localdomain) 7> info credentialstore
Reading from credential store:
Default domain: OracleGoldenGate
Alias: cdb19c
Userid: c##ggadmin@192.168.24.1:1521/cdb19c
GGSCI (RACnode1.localdomain) 8>
GGSCI (RACnode1.localdomain) 8> dblogin USERIDALIAS cdb19c
Successfully logged into database CDB$ROOT.
Cấu hình EXTRACT parameter file:
GGSCI (RACnode1.localdomain) 2> EDIT PARAM exthr
EXTRACT exthr
SETENV (ORACLE_SID='CDB19C_PDB')
SETENV (ORACLE_HOME = '/u01/app/oracle/product/19c/dbhome_1')
DISCARDFILE ./dirrpt/exthr.dsc, APPEND
EXTTRAIL ./dirdat/et
--- User login
USERIDALIAS cdb19c
--- DDL Parameters
LOGALLSUPCOLS
DDL INCLUDE MAPPED
DDLOPTIONS REPORT
SOURCECATALOG CDB19C_PDB
TABLE HR.* ;
Cấu hình EXTRACT PUMP parameter file:
GSCI (xtivia12) 3> EDIT PARAM ephr
EXTRACT ephr
RMTHOST 192.168.24.3, MGRPORT 7809
PASSTHRU
RMTTRAIL ./dirdat/rt
SOURCECATALOG CDB19C_PDB
TABLE HR.* ;
Tạo EXTRACT group, EXTRAIL, PUMP AND RMTTRAIL:
GGSCI (RACnode1.localdomain) 17> ADD EXTRACT exthr, INTEGRATED TRANLOG, BEGIN NOW
EXTRACT (Integrated) added.
GGSCI (RACnode1.localdomain) 18> ADD EXTTRAIL ./dirdat/et, EXTRACT exthr, MEGABYTES 5
EXTTRAIL added.
GGSCI (RACnode1.localdomain) 19> ADD RMTTRAIL ./dirdat/rt, EXTRACT exthr, MEGABYTES 5
RMTTRAIL added.
GGSCI (RACnode1.localdomain) 20> ADD EXTRACT ephr, EXTTRAILSOURCE ./dirdat/et
EXTRACT added.
GGSCI (RACnode1.localdomain) 21> ADD RMTTRAIL ./dirdat/rt, EXTRACT ephr, MEGABYTES 5
RMTTRAIL added.
GGSCI (RACnode1.localdomain) 22> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EPHR 00:00:00 00:00:07
EXTRACT STOPPED EXTHR 00:00:00 00:00:15
GGSCI (RACnode1.localdomain) 22>
Đăng ký EXTRACT trong DB:
GGSCI (RACnode1.localdomain) 10> dblogin userid c##ggadmin, password ggadmin
Successfully logged into database CDB$ROOT.
GGSCI (RACnode1.localdomain as c##ggadmin@cdb19c1/CDB$ROOT) 11> register extract exthr database container (CDB19C_PDB)
2021-10-01 16:20:35 INFO OGG-02003 Extract EXTHR successfully registered with database at SCN 2985612.
GGSCI (RACnode1.localdomain as c##ggadmin@cdb19c1/CDB$ROOT) 12>
5:- Chuẩn bị GoldenGate ở TARGET:
Cấu hình Manager parameter:
GGSCI (standbyracnode.localdomain) 2> EDIT PARAM mgr
PORT 7809
PURGEOLDEXTRACTS ./dirdat/rt* , USECHECKPOINTS, MINKEEPHOURS 72
AUTORESTART ER *, RETRIES 3, WAITMINUTES 5, RESETMINUTES 60
DOWNREPORTMINUTES 15
LAGCRITICALSECONDS 10
LAGINFOMINUTES 0
LAGREPORTMINUTES 15
ACCESSRULE, PROG *, IPADDR 192.168.24.3, ALLOW
ACCESSRULE, PROG *, IPADDR 192.168.24.1, ALLOW
Tạo credentialstore:
GGSCI (standbyracnode.localdomain) 3> add credentialstore
Credential store created.
GGSCI (standbyracnode.localdomain) 5> alter credentialstore add user ggadmin@DEVDBNONCDB alias DEVDBNONCDB
Password:
Credential store altered.
GGSCI (standbyracnode.localdomain) 5> info credentialstore
Reading from credential store:
Default domain: OracleGoldenGate
Alias: DEVDBNONCDB
Userid: ggadmin@DEVDBNONCDB
GGSCI (standbyracnode.localdomain) 6>
GGSCI (standbyracnode.localdomain) 7> dblogin USERIDALIAS DEVDBNONCDB
Successfully logged into database.
Cấu hình REPLICAT parameter file:
GGSCI (standbyracnode.localdomain) 2> EDIT PARAM rephr
REPLICAT rephr
DISCARDFILE ./dirrpt/rephr.dsc, APPEND
DBOPTIONS ENABLE_INSTANTIATION_FILTERING
ASSUMETARGETDEFS
--- User login
USERIDALIAS DEVDBNONCDB
DDL INCLUDE ALL
DDLOPTIONS REPORT
MAP CDB19C_PDB.HR.*, TARGET HR.*;
Tạo REPLICAT group và Trail Files:
GGSCI (standbyracnode.localdomain) 3> dblogin USERIDALIAS DEVDBNONCDB
Successfully logged into database.
GGSCI (standbyracnode.localdomain as ggadmin@devdbnoncdb) 4> ADD CHECKPOINTTABLE ggadmin.chktbl
Successfully created checkpoint table ggadmin.chktbl.
GGSCI (standbyracnode.localdomain as ggadmin@devdbnoncdb) 5> ADD REPLICAT rephr, INTEGRATED, EXTTRAIL ./dirdat/rt, CHECKPOINTTABLE ggadmin.chktbl
REPLICAT (Integrated) added.
GGSCI (standbyracnode.localdomain as ggadmin@devdbnoncdb) 9> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT STOPPED REPHR 00:00:00 00:00:03
GGSCI (standbyracnode.localdomain as ggadmin@devdbnoncdb) 10>
6:- BẮT ĐẦU EXTRACT:
Restart Manager (từ tham số đã được sửa ở trên)
GGSCI (RACnode1.localdomain) 1> stop mgr
Manager process is required by other GGS processes.
Are you sure you want to stop it (y/n)?y
Sending STOP request to MANAGER ...
Request processed.
Manager stopped.
GGSCI (RACnode1.localdomain) 2> start mgr
Manager started.
Start EXTRACT và PUMP:
GGSCI (RACnode1.localdomain) 3> start extract exthr
Sending START request to MANAGER ...
EXTRACT EXTHR starting
GGSCI (RACnode1.localdomain) 4> start extract ephr
Sending START request to MANAGER ...
EXTRACT EPHR starting
GGSCI (RACnode1.localdomain) 5> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EPHR 00:00:00 00:00:34
EXTRACT RUNNING EXTHR 00:00:00 00:00:27
GGSCI (RACnode1.localdomain) 6>
7:- Bắt đầu REPLICAT:
Restart Manager (từ cấu hình ở trên):
GGSCI (standbyracnode.localdomain) 1> stop mgr
Manager process is required by other GGS processes.
Are you sure you want to stop it (y/n)?y
Sending STOP request to MANAGER ...
Request processed.
Manager stopped.
GGSCI (standbyracnode.localdomain) 2> start mgr
Manager started.
Start REPLICAT:
GGSCI (standbyracnode.localdomain) 3> start replicat REPHR
Sending START request to MANAGER ...
REPLICAT REPHR starting
GGSCI (standbyracnode.localdomain) 4> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REPHR 00:00:00 00:00:10
GGSCI (standbyracnode.localdomain) 5>
KIỂM TRA:
1:- INSERT:
Source DB prev. State:
SQL> show parameter instance_name
NAME TYPE VALUE
------------------------ ------- ----------------------
instance_name string cdb19c1
SQL> alter session set container=CDB19C_PDB;
Session altered.
SQL> select count(*) from HR.jobs;
COUNT(*)
----------
19
SQL> select * from HR.jobs where JOB_TITLE like '%DBA%';
no rows selected
Target DB prev. State:
SQL> show parameter instance_name
NAME TYPE VALUE
------------------------ ------- ----------------------
instance_name string devdbnoncdb
SQL> select count(*) from HR.jobs;
COUNT(*)
----------
19
SQL> select * from HR.jobs where JOB_TITLE like '%DBA%';
no rows selected
Insert vào SOURCE DB:
SQL> show con_name
CON_NAME
------------------------------
CDB19C_PDB
SQL> INSERT INTO HR.jobs
(JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY )
VALUES
('DB_MAN', 'DBA Administrator',40000,70000);
1 row created.
SQL> commit;
Commit complete.
REPLICATED ở target DB??
SQL> show parameter instance_name
NAME TYPE VALUE
------------------------ ------- ----------------------
instance_name string devdbnoncdb
SQL> select * from HR.jobs where JOB_TITLE like '%DBA%';
JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
DB_MAN DBA Administrator 40000 70000
SQL> select count(*) from HR.jobs;
COUNT(*)
----------
20
2:- DELETE:
Source DB prev. State:
SQL> show parameter instance_name
NAME TYPE VALUE
------------------------ ------- ----------------------
instance_name string cdb19c1
SQL> alter session set container=CDB19C_PDB;
Session altered.
SQL> select count(*) from HR.jobs;
COUNT(*)
----------
20
SQL> select * from HR.jobs where JOB_TITLE like '%DBA%';
JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
DB_MAN DBA Administrator 40000 70000
Target DB prev. State:
SQL> show parameter instance_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
instance_name string devdbnoncdb
SQL> select * from HR.jobs where JOB_TITLE like '%DBA%';
JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
DB_MAN DBA Administrator 40000 70000
SQL> select count(*) from HR.jobs;
COUNT(*)
----------
20
Delete ở Source DB:
SQL> delete from HR.JOBS where JOB_TITLE like '%DBA%';
1 row deleted.
SQL> commit;
Commit complete.
SQL>
REPLICATED on target DB??
SQL> show parameter instance_name
NAME TYPE VALUE
------------------------ ------- ----------------------
instance_name string devdbnoncdb
SQL> select * from HR.jobs where JOB_TITLE like '%DBA%';
no rows selected
SQL> select count(*) from HR.jobs;
COUNT(*)
----------
19
3:- DDL:
Source DB prev. State:
SQL> show parameter instance_name
NAME TYPE VALUE
------------------------ ------- ----------------------
instance_name string cdb19c1
SQL> alter session set container=CDB19C_PDB;
Session altered.
SQL> desc HR.TEST
ERROR:
ORA-04043: object HR.TEST does not exist
Target DB prev. State:
SQL> show parameter instance_name
NAME TYPE VALUE
------------------------ ------- ----------------------
instance_name string devdbnoncdb
SQL> desc HR.TEST
ERROR:
ORA-04043: object HR.TEST does not exist
Tạo table ở Source:
SQL> CREATE TABLE HR.TEST
( test_id NUMBER PRIMARY KEY,
test__name VARCHAR2(30) ); 2 3
Table created.
SQL> desc HR.TEST
Name Null? Type
------------------------- -------- ---------------------
TEST_ID NOT NULL NUMBER
TEST__NAME VARCHAR2(30)
REPLICATED ở target DB??
SQL> show parameter instance_name
NAME TYPE VALUE
------------------------ ------- ----------------------
instance_name string devdbnoncdb
SQL> desc HR.TEST
Name Null? Type
------------------------- -------- ---------------------
TEST_ID NOT NULL NUMBER
TEST__NAME VARCHAR2(30)
Thế là GoldenGate đã được cấu hình rồi.
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/Zalo: 0902912888
👨 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: https://www.youtube.com/@binhguru
👨 Tiktok: https://www.tiktok.com/@binhguru
👨 Linkin: https://www.linkedin.com/in/binhoracle
👨 Twitter: https://twitter.com/binhguru
👨 Podcast: https://www.podbean.com/pu/pbblog-eskre-5f82d6
👨 Đị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
=============================
oracle tutorial, 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,sql tutorial, khóa học pl/sql tutorial, 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 RAC, ASM, oracle dataguard, oracle goldengate, mview, oracle exadata, oracle oca, oracle ocp, oracle ocm , oracle weblogic, postgresql tutorial, mysql tutorial, mariadb tutorial, sql server tutorial, nosql, mongodb tutorial, oci, cloud, middleware tutorial, hoc solaris tutorial, hoc linux tutorial, hoc aix tutorial, unix tutorial, securecrt, xshell, mobaxterm, putty