On-Premises - Source
1. Oracle Database 12c (12.1.0.2) 2. Oracle GoldenGate 12.2: Manager running in port 7809 with the following setup:
PORT 7809
DYNAMICPORTLIST 7840, 7841 |
Cloud (Iaas) - Target
1. Oracle Database 12c (12.1.0.2) 2. Oracle GoldenGate 12.2 : Manager running in port 7809 with the following setup:
PORT 7809
DYNAMICPORTLIST 7840-7849 ACCESSRULE, PROG SERVER, ALLOW, PRI 1 |
1. On-Premises GoldenGate Extract and Pump Setup
- Creating a GoldenGate Administrator
- Creating the Source Database
- Starting a SOCKS5 Proxy
- Configuring the GoldenGate Extract
- Configuring the GoldenGate Pump to Deliver Data Using the SOCK5 proxy
>sqlplus / as sysdba SQL>create user c##ggu identifeid by *** User created. SYS@gdop> grant connect,resource to c##ggu; Grant succeeded. SQL> execute dbms_goldengate_auth.grant_admin_privilege('c##ggu',container=>'all'); PL/SQL procedure successfully completed.
>sqlplus / as sysdba SQL> alter session set container=pdb1; Session altered. SQL> create user panu identified by ***; User created. SQL> grant connect, resource to panu; Grant succeeded. SQL> connect panu/***@pdb1; Connected. SQL> create table world_region (region_id number(4), region_name varchar2(25)); Table created.
sudo ssh -N -i id_rsa -v -D 127.0.0.1:9000 [email protected]
Please have the following parameter setup in the /etc/ssh/sshd_config file:
AllowTCPForwarding yes
nc -v 127.0.0.1 9000 Ncat: Version 6.40 ( http://nmap.org/ncat ) Ncat: Connected to 127.0.0.1:9000.
The following example creates an Integrated Extract and uses USERIDALIAS to specify the database logins. Using USERIDALIAS eliminates the need to specify user names and clear-text passwords in the Oracle GoldenGate parameter files. It is implemented as an autologin wallet within the Oracle Credential Store Framework (CSF). It's a best practice for Oracle GoldenGate setup.
1.4.1 Creating Credential Store and USERIDALIAS
./GLOBALS: CREDENTIALSTORELOCATION /oracle/gdcstore
Answer: This is because you need to restart the GGCSI process.
GGSCI> add credentialstore Credential store created in /oracle/gdcstore. GGSCI> alter credentialstore add user c##ggu alias ggu_root Password: (You type password for c##ggu) Credential store in /u01/app/oracle/gdcstore altered. GGSCI2> dblogin useridalias ggu_root Successfully logged into database CDB$ROOT. GGSCI> alter credentialstore add user c##ggu@pdb1 alias ggu_pdb1 Password: (You type password for c##ggu) Credential store in /oracle/gdcstore/ altered. GGSCI (ericxchen7.us.oracle.com) 17> dblogin useridalias ggu_pdb1 Successfully logged into database.
GGSCI (ericxchen7.us.oracle.com DBLOGIN as c##ggadmin) 35> info credentialstore Reading from /oracle/gdcstore/: Default domain: OracleGoldenGate Alias: ggu_root Userid: c##ggu Alias: ggu_pdb1 Userid: c##ggu@pdb1
EXTRACT extgdop USERIDALIAS ggu_root1 EXTTRAIL /oracle/gdtrail/gdop/op TRANLOGOPTIONS use_root_container_timezone TABLE pdb1.panu.*;
GGSCI> DBLOGIN USERALIAS ggu_pdb1 Successfully logged into database PDB1. GGSCI> ADD TRANDATA panu.world_geography ALLCOLS Logging of supplemental redo data enabled for table PDB1.PANU.WORLD_GEOGRAPHY. TRANDATA for scheduling columns has been added on table 'PANU.WORLD_GEOGRAPHY'.TRANDATA for all columns has been added on table 'PDB1PANU.WORLD_GEOGRAPHY'. GGSCI> DBLOGIN USERALIAS ggu_root Successfully logged into database CDB$ROOT. GGSCI> REGISTER EXTRACT extgdop DATABASE CONTAINER (pdb1) Extract EXTGDOP successfully registered with database at SCN 3779081. GGSCI> ADD EXTRACT extgdop INTEGRATED TRANLOG, BEGIN NOW EXTRACT added. GGSCI> ADD EXTTRAIL /oracle/gdtrail/gdop/op EXTRACT extgdop EXTTRAIL added.
The following is an example parameter file of the Oracle GoldenGate pump (pumgdop.prm)
EXTRACT pumgdop RMTHOST gdgt, MGRPORT 7809,socksproxy 127.0.0.1:9000 discardfile ./dirrpt/pumgdop.dsc, purge RMTTRAIL /u01/app/oracle/gdtrail/gdgt/op PASSTHRU TABLE pdb1.panu.*;
[oracle@ericxchen7 gdcstore]$ more /etc/hosts 127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4 ::1 localhost localhost.localdomain localhost6 localhost6.localdomain6 129.152.144.177 gdgt.compute-metcsgse00288.oraclecloud.internal gdgt
GGSCI> ADD EXTRACT pumgdop EXTTRAILSOURCE /oracle/gdtrail/gdop/op BEGIN NOW GGSCI> ADD RMTTRAIL /u01/app/oracle/gdtrail/gdgt/op EXTRACT pumgdop
2. In Cloud GoldenGate Replicat Setup
ssh -i id_rsa [email protected]
2.1. Target Database Configuration
>sqlplus / as sysdba SQL>create user c##ggu identifeid by *** User created. SQL> execute dbms_goldengate_auth.grant_admin_privilege('c##ggu', container=>'all'); PL/SQL procedure successfully completed. SQL> create user panu identified by panu; User created. SQL> grant connect, resource to panu; Grant succeeded. SQL> connect panu/panu@pdb1; Connected. SQL> create table world_region (region_id number(4), region_name varchar2(25)); Table created.
CREDENTIALSTORELOCATION /u01/app/oracle/gdcstore ALLOWOUTPUTDIR /u01/app/oracle/gdtrail/gdgt
Details:The error message is shown as follows:
OGG-01031 There is a problem in network communication, a remote file problem, encryption keys for target and source do not match (if using ENCRYPT) or an unknown error. (Reply received is Output file /u01/app/oracle/gdtrail/gdgt/op000000 is not in any allowed output directories.).Answer:This is because in 12.2 you need to setup the ALLOWOUTPUTDIR. This is setup in the GLOBALS file on the replicat side shown as follows:
ALLOWOUTPUTDIR /u01/app/oracle/gdtrail/gdgt
GGSCI> add credentialstore Credential store created in /u01/app/oracle/gdcstore. GGSCI> alter credentialstore add user c##ggu@pdb1 alias ggu_pdb1 Password: (You type password for c##ggu) Credential store in /u01/app/oracle/gdcstore altered. GGSCI (gdgt) 10> info credentialstore Reading from /u01/app/oracle/gdcstore/: Default domain: OracleGoldenGate Alias: ggu_pdb1 Userid: c##ggu@pdb1 GGSCI (gdgt) 51> dblogin useridalias ggu_pdb1 Successfully logged into database PDB1.
REPLICAT repgdgt USERIDALIAS ggu_pdb1 DBOPTIONS INTEGRATEDPARAMS(parallelism 2) MAP pdb1.panu.*, TARGET pdb1.panu.*;
Answer: You can use cat /proc/cpuinfo on linux to check the CPU info and then assign the parallelism based on the number of cores and the replicat workload.
GGSCI>ADD REPLICAT repgdgt INTEGRATED EXTTRAIL /u01/app/oracle/gdtrail/gdgt/op GGSCI>Start repgdgt