이 활동에서는 GoldenGate 소프트웨어가 이미 설치되어 있다고 가정합니다.
우리는 다음 서버에서 스키마 HR을 복제할 예정입니다.
| 소스(PDB): | 대상(CDB 아님): |
| 호스트 이름: racnode1 | 호스트 이름: Standbyracnode |
| IP: 192.168.24.1 | IP: 192.168.24.3 |
| RDBMS: 19c | RDBMS: 19c |
| SID: cdb19c1 | SID: devdbnoncdb |
| PDB: CDB19C_PDB | PDB: 해당 없음 |
| ORACLE_HOME: /u01/app/oracle/product/19c/dbhome_1 | ORACLE_HOME: /u01/app/oracle/product/19c/db_1 |
| 골든게이트 홈: /u01/app/goldengate/19.1.0.0/ | 골든게이트 홈: /u01/app/goldengate/19.1.0.0/ |
PDB로 작업하고 있으므로 INTEGRATED 엑스트라를 사용해야 한다는 점에 유의하세요.
이 활동을 구현하는 단계는 다음과 같습니다.
1:- 초기 로드/대상 DB로 데이터 가져오기:
EXPORT:
[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]$
Make sure pdb is pingable and set in 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:
[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:- 소스 DB 준비:
Enable minimal supplemental logging:
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>
Create 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:- 타겟 DB 준비:
Enable RDBMS to be used by GoldenGate:
SQL> ALTER SYSTEM SET enable_goldengate_replication=TRUE SCOPE=BOTH;
System altered.
Create 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:- 소스에서 GoldenGate 준비:
Configure 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
Create 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.
Configure 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.* ;
Configure 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.* ;
Create 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>
Register EXTRACT in 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:- 대상에서 GoldenGate 준비:
Configure 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
Create 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.
Configure 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.*;
Create REPLICAT group and 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:- 추출 시작:
Restart Manager (since we edited parameter)
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 and 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:- REPLICAT 시작:
Restart Manager (since we edited parameter):
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> 테스트:
1:- 삽입:
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 on 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 in 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(*)
----------
202:- 삭제:
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 on 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(*)
----------
193:- 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
Create table in 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 on 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)
그리고 짜잔! 우리는 지금 GoldenGate를 가동하고 있습니다!
'ORACLE > 이중화(HA)' 카테고리의 다른 글
| [OGG] COLMATCH (0) | 2024.01.20 |
|---|---|
| [OGG] FILTER 데이터 선택 (0) | 2024.01.20 |
| OGG 파일 설정 (0) | 2022.11.30 |
| OGG 12c 설치 구성 (0) | 2019.10.18 |
| integrated extract 구성 (0) | 2019.10.18 |
