Knowledge is Power – share IT



Oracle GoldenGate 18c and GoldenGate Cloud Services WorkshopHands-on Lab Exercise 17Install and Configure Oracle GoldenGate Cloud ServiceConfigure replication between On-premise and Oracle Cloud Oracle GoldenGate Cloud Service Hands-On Lab Exercise This note describes the steps used to configure Oracle GoldenGate replication between on-premise and the Oracle Cloud.The on-premise database is Oracle 12c Release 2 (CDB1/PDB1) and the Oracle Database Classic Cloud Service instance is also Oracle 12c Release 2 (OGGDB). The GoldenGate version is 18.1 on the on-premise Oracle GoldenGate environment and the Oracle GoldenGate Cloud Service is also 18.1 (Classic).The main steps involved are:Create the Oracle Database Service InstanceCreate the Oracle GoldenGate Cloud Service InstanceCreate the PuTTY private keys and configure PuTTY connections to the Cloud Database instance and GoldenGate Cloud Service instanceCreate the DEMO schema and MYOBJECTS table in the Oracle Database Service Cloud instance Configure SSH SOCKS Proxy Server for communication between on-premise server and Oracle Cloud Create the Credential Store in the Oracle GoldenGate Cloud Service environment Create the Replicat process Store in the Oracle GoldenGate Cloud Service environmentCreate the Extract and Pump processes in the on-premise Oracle GoldenGate environmentStart the on-premise and GoldenGate Cloud Service GoldenGate Extract and Replicat processesPerform DML on the on-premise MYOBJECTS table and verify data being replicated to the Cloud database Create the Oracle Cloud Database Service Instance We need to create an Oracle Database Cloud Classic instance if we want to install Oracle GoldenGate Cloud Control as part of the Oracle GoldenGate Cloud instance.We will create an Oracle 12c Release 2 database OGGDB which will host the Fusion Middleware type repository schemas for the GoldenGate Cloud Control application.The OGGDB database will also serve as the Cloud target database instance for Oracle GoldenGate replication between On-Premise and Oracle Cloud.Hence ensure that Enable Oracle GoldenGate option is checked.Make a note of the Administration Password entered. This password is the common password for users like SYS,SYSTEM and the GoldenGate administrator common user C##GGADMIN which will also be created in the database.Click on Download and then Done This will download a zipped file called sshbundle.zip on your local machine. Rename this file to sshbundle_oggdb.zip and unzip the file.Create the GoldenGate Cloud Service (GGCS) Instance We will create a GGCS instance named GGCSINST which will also have the GoldenGate Cloud Control component in addition to the standard Replication component.The GoldenGate version is 18.1 Classic.Expand the Oracle Cloud My Services menu item on the top left and select GoldenGate.Identify the Cloud Storage Container by opening the Storage Classic service page. Click on the Account tab and obtain the details from the Rest Endpoint item in the Account Information section.In this case the URL is: the database which we created earlier in the exercise (OGGDB) as this will serve as the repository for the GoldenGate Cloud Control schemas.Note – the SYS password will be the Administration Password which we entered while creating the database.Also make a note of the WebLogic Server administration password.Click on SSH Public Key Edit button Click on Download and Done Rename the downloaded zip file to sshbundle_gginst.zip on your local machine.Create the PuTTY Private Key (ppk)Launch puttygen tool Click on Load Unzip the downloaded SSH Public Key file – we had saved the file as sshbundle_gginst.zip.Open the folder containing the Private key/Public key pair of files (downloaded while creating the database or GGSC instance). Select the privateKey file and click Open.Click on Save private key Click on Yes Enter the File name gginst and ensure Save as type is PuTTY Private Key FilesNote: Follow the same process for the SSH Public Key file we have downloaded while creating the Cloud Database instanceConnect to the Cloud Database instance (OGGDB)Click on Dashboard Make a note of the Public IP address – in this case it 129.150.86.72Launch PuTTYEnter the Public IP address and oggdb for Saved SessionsSelect Connection >> Data menu Enter ‘oracle’ for the Auto-login usernameSelect Connection >> SSH>>Auth menu Open the folder containing the Private key file (oggdb.ppk) which we would have created earlier using puttygen utility.Click on Save and then OpenClick on Yes Use the dbaascli tool to verify if the database has been enabled for GoldenGate [oracle@OGGDB ~]$ dbaascliDBAAS CLI version 18.4.3.1.0DBAAS>gg statusExecuting command gg statusGolden Gate status: enabled.Note the GoldenGate admin user C##GGADMIN has been created in the database and also note the values for the database parameters STREAMS_POOL_SIZE and ENABLE_GOLDENGATE_REPLICATION.[oracle@OGGDB ~]$ sqlplus sys as sysdbaSQL*Plus: Release 12.2.0.1.0 Production on Thu Feb 7 00:41:38 2019Copyright (c) 1982, 2016, Oracle. All rights reserved.Enter password:Connected to:Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit ProductionSQL> select username from cdb_users where username like '%GGADMIN%';USERNAME--------------------------------------------------------------------------------C##GGADMINSQL> show parameter streamsNAME TYPE VALUE------------------------------------ ----------- ------------------------------streams_pool_size big integer 272MSQL> show parameter enable_goldengateNAME TYPE VALUE------------------------------------ ----------- ------------------------------enable_goldengate_replication boolean TRUECreate the DEMO schema and the MYOBJECTS tableSQL> grant dba to c##ggadmin container=all;Grant succeeded.SQL> alter session set container=pdb1;Session altered.SQL> create user demo identified by oracle default tablespace users temporary tablespace temp;User created.SQL> grant dba to demo;Grant succeeded.SQL> create table demo.myobjects as select * from all_objectswhere 1=2;Table created.SQL> alter table demo.myobjects add constraint pk_myobjectsprimary key (object_id);Table altered.Connect to the GoldenGate Cloud Service instance (OGGDB)Click on Dashboard Connect to the GGSC host via PuTTY Note: we will connect as the ‘opc’ userClick on Save and then Open.Connect as the oracle user [opc@ggcsinst-ggcs-1 ~]$ sudo su - oracleNote the environment variables which have been automatically configured as well as the various symbolic links [oracle@ggcsinst-ggcs-1 ~]$ env |grep AGENTAGENT_INSTANCE_HOME=/u02/data/ggcc/agentAGENT_HOME=/u01/app/oracle/middleware/ggccagent [oracle@ggcsinst-ggcs-1 ~]$ env |grep GGHOMEGGHOME=/u01/app/oracle/gghome[oracle@ggcsinst-ggcs-1 ~]$ cd /u01/app/oracle[oracle@ggcsinst-ggcs-1 oracle]$ ls -ltotal 12lrwxrwxrwx. 1 root root 20 Feb 9 03:48 gghome -> /u02/data/gg123ora12lrwxrwxrwx. 1 root root 21 Feb 9 03:48 gghome11g -> /u02/data/gg123ora11gdrwxr-x---. 2 oracle oracle 4096 Feb 9 03:48 gghome18cdrwxr-xr-x. 14 oracle oracle 4096 Feb 9 03:53 middlewarelrwxrwxrwx. 1 root root 13 Feb 9 03:48 oci -> /u02/data/ocilrwxrwxrwx. 1 root root 22 Feb 9 03:48 oraInventory -> /u02/data/oraInventorylrwxrwxrwx. 1 root root 13 Feb 9 03:48 rda -> /u02/data/rdadrwxr-xr-x. 6 root root 4096 Feb 9 03:43 toolsNote the location of the variables LD_LIBRARY_PATH and TNS_ADMIN which are defined via the .bashrc file[oracle@ggcsinst-ggcs-1 ~]$ vi .bashrc[oracle@ggcsinst-ggcs-1 ~]$ echo $LD_LIBRARY_PATH/u01/app/oracle/oci:/u01/app/oracle/oci:[oracle@ggcsinst-ggcs-1 ~]$ echo $TNS_ADMIN/u01/app/oracle/oci/network/adminLaunch GGSCI [oracle@ggcsinst-ggcs-1 ~]$ cd $GGHOME[oracle@ggcsinst-ggcs-1 oracle]$ ls -l ocilrwxrwxrwx. 1 root root 13 Feb 9 03:48 oci -> /u02/data/oci[oracle@ggcsinst-ggcs-1 gghome]$ ./ggsciOracle GoldenGate Command Interpreter for OracleVersion 12.2.0.2.2 27274916_FBOLinux, x64, 64bit (optimized), Oracle 12c on Dec 16 2017 03:36:13Operating system character set identified as UTF-8.Copyright (C) 1995, 2017, Oracle and/or its affiliates. All rights reserved.GGSCI (ggcsinst-ggcs-1) 1> info allProgram Status Group Lag at Chkpt Time Since ChkptMANAGER RUNNINGJAGENT STOPPEDCreate the GoldenGate Credential Store and add entries to the Credential Store Copy the tnsnames.ora entries for OGGDB and PDB1 from Database Cloud machine to GGCS machine.[oracle@OGGDB ~]$ cd $ORACLE_HOME[oracle@OGGDB dbhome_1]$ cd network/admin[oracle@OGGDB admin]$ vi tnsnames.oraOGGDB = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = pute-612164189.oraclecloud.internal)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = OGGDB.612164189.oraclecloud.internal) ) )PDB1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = pute-612164189.oraclecloud.internal)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = pdb1.612164189.oraclecloud.internal) ) )[oracle@ggcsinst-ggcs-1 admin]$ pwd/u01/app/oracle/oci/network/admin[oracle@ggcsinst-ggcs-1 admin]$ vi tnsnames.ora<add entries for PDB1 and OGGDB>[oracle@ggcsinst-ggcs-1 admin]$ cd $GGHOME [oracle@ggcsinst-ggcs-1 gghome]$ ./ggsciOracle GoldenGate Command Interpreter for OracleVersion 18.1.0.0.0 OGGCORE_18.1.0.0.0_PLATFORMS_180928.0432_FBOLinux, x64, 64bit (optimized), Oracle 12c on Sep 29 2018 04:22:21Operating system character set identified as UTF-8.Copyright (C) 1995, 2018, Oracle and/or its affiliates. All rights reserved.GGSCI (ggcsinst-ggcs-1) 1> add credentialstoreCredential store created.GGSCI (ggcsinst-ggcs-1) 2> alter credentialstore add user c##ggadmin@pdb1 alias ggadminPassword:Credential store altered.GGSCI (ggcsinst-ggcs-1) 3> dblogin useridalias ggadminSuccessfully logged into database PDB1.GGSCI (ggcsinst-ggcs-1 as c##ggadmin@OGGDB/PDB1) 4> exit[oracle@ggcsinst-ggcs-1 gghome]$Configure SSH Socks Proxy Server Copy the SSH Private key file belonging to the GGCS machine to the on-premise VM rac01 Copy private key to shared folder and from the shared folder to the $HOME/.ssh directory [oracle@rac01 .ssh]$ cp /media/sf_software/privateKey .[oracle@rac01 .ssh]$ chmod 600 privateKey [oracle@rac01 .ssh]$ ls -lrttotal 24-rw-r--r-- 1 oracle oinstall 406 Jan 4 10:47 id_rsa.pub-rw------- 1 oracle oinstall 1679 Jan 4 10:47 id_rsa-rw-r--r-- 1 oracle oinstall 812 Jan 4 10:47 authorized_keys-rw-r--r-- 1 oracle oinstall 2385 Feb 6 23:29 known_hosts-rw------- 1 oracle oinstall 1675 Feb 7 18:07 privateKey-v=Verbose Mode-N=do not execute remote command, mainly used for port forwarding -f=run ssh process in background-D=act as a SOCKS proxy server127.0.0.1=Host IP Address where SOCKS proxy will listen8088=TCP/IP port number to listen onopc=GGSC Cloud Instance server user 129.150.93.188=GGCS Cloud Instance server Public IP[oracle@rac01 .ssh]$ ssh -i /home/oracle/.ssh/privateKey -v -N -f -D 127.0.0.1:8088 opc@129.150.93.188 > /tmp/socksproxy.log 2>&1You have new mail in /var/spool/mail/oracleConfigure on-premise GoldenGate Extract and PumpGGSCI (rac01.localdomain) 10> add extract ext3 integrated tranlog begin nowEXTRACT (Integrated) added.GGSCI (rac01.localdomain) 11> add exttrail ./dirdat/onprem/lt extract ext3EXTTRAIL added.GGSCI (rac01.localdomain) 12> add extract pump3 exttrailsource ./dirdat/onprem/ltEXTRACT added.GGSCI (rac01.localdomain) 13> add rmttrail ./dirdat/onprem/rt extract pump3RMTTRAIL added.GGSCI (rac01.localdomain as C##oggadmin@cdb1_2/CDB$ROOT) 24> register extract ext3 database container (pdb1)2019-02-11 17:57:40 INFO OGG-02003 Extract EXT3 successfully registered with database at SCN 10757064.GGSCI (rac01.localdomain as C##oggadmin@cdb1_2/CDB$ROOT) 25> edit params ext3EXTRACT ext3SETENV (ORACLE_HOME='/acfs_oh/product/12.2.0/dbhome_1')USERIDALIAS oggadmin_cdb1EXTTRAIL ./dirdat/onprem/ltSOURCECATALOG pdb1TABLE demo.*;GGSCI (rac01.localdomain as C##oggadmin@cdb1_2/CDB$ROOT) 27> edit params pump3EXTRACT pump3SETENV (ORACLE_HOME='/acfs_oh/product/12.2.0/dbhome_1')USERIDALIAS oggadmin_cdb1RMTHOST 129.150.93.188 , MGRPORT 7809, SOCKSPROXY 127.0.0.1:8088RMTTRAIL ./dirdat/onprem/rtSOURCECATALOG pdb1TABLE demo.*;GGSCI (rac01.localdomain as C##oggadmin@cdb1_2/CDB$ROOT) 28> shell mkdir ./dirdat/onpremGGSCI (rac01.localdomain as C##oggadmin@cdb1_2/CDB$ROOT) 29> start extract ext3Sending START request to MANAGER ...EXTRACT EXT3 startingGGSCI (rac01.localdomain as C##oggadmin@cdb1_2/CDB$ROOT) 35> info ext3EXTRACT EXT3 Last Started 2019-02-11 18:08 Status RUNNINGCheckpoint Lag 00:00:03 (updated 00:00:00 ago)Process ID 8488Log Read Checkpoint Oracle Integrated Redo Logs 2019-02-11 18:14:11 SCN 0.10792986 (10792986)GGSCI (rac01.localdomain as C##oggadmin@cdb1_2/CDB$ROOT) 36> start pump3Sending START request to MANAGER ...EXTRACT PUMP3 startingGGSCI (rac01.localdomain as C##oggadmin@cdb1_2/CDB$ROOT) 42> info pump3EXTRACT PUMP3 Last Started 2019-02-11 18:15 Status RUNNINGCheckpoint Lag 00:00:00 (updated 00:00:06 ago)Process ID 14637Log Read Checkpoint File /acfs_oh/app/goldengate/dirdat/onprem/lt000000000 2019-02-11 18:16:15.000000 RBA 21404Configure the Cloud Replicat process [opc@ggcsinst-ggcs-1 ~]$ sudo su - oracleLast login: Thu Feb 7 06:53:41 UTC 2019 on pts/0[oracle@ggcsinst-ggcs-1 ~]$ cd $GGHOME[oracle@ggcsinst-ggcs-1 gghome]$ ./ggsciOracle GoldenGate Command Interpreter for OracleVersion 12.2.0.2.2 27274916_FBOLinux, x64, 64bit (optimized), Oracle 12c on Dec 16 2017 03:36:13Operating system character set identified as UTF-8.Copyright (C) 1995, 2017, Oracle and/or its affiliates. All rights reserved.GGSCI (ggcsinst-ggcs-1) 1> info allProgram Status Group Lag at Chkpt Time Since ChkptMANAGER RUNNINGJAGENT STOPPEDGGSCI (ggcsinst-ggcs-1 as c##ggadmin@OGGDB/PDB1) 9> dblogin useridalias ggadminSuccessfully logged into database PDB1.GGSCI (ggcsinst-ggcs-1 as c##ggadmin@OGGDB/PDB1) 11> add checkpointtable C##GGADMIN.chkptabLogon catalog name PDB1 will be used for table specification PDB1.C##GGADMIN.chkptab.Successfully created checkpoint table PDB1.C##GGADMIN.chkptab.GGSCI (ggcsinst-ggcs-1 as c##ggadmin@OGGDB/PDB1) 13> edit params ./GLOBALSENABLEMONITORINGCHECKPOINTTABLE C##GGADMIN.CHKPTABGGSCI (ggcsinst-ggcs-1 as c##ggadmin@OGGDB/PDB1) 7> exit[oracle@ggcsinst-ggcs-1 gghome]$ ./ggsciGGSCI (ggcsinst-ggcs-1) 1> add replicat rep1 exttrail ./dirdat/onprem/rtREPLICAT added.GGSCI (ggcsinst-ggcs-1) 3> edit params rep1REPLICAT rep1USERIDALIAS ggadminMAP pdb1.demo.*, TARGET pdb1.demo.*;GGSCI (ggcsinst-ggcs-1 as c##ggadmin@OGGDB/PDB1) 13> start replicat rep1Sending START request to MANAGER ...REPLICAT REP1 startingGGSCI (ggcsinst-ggcs-1 as c##ggadmin@OGGDB/PDB1) 10> info rep1REPLICAT REP1 Last Started 2019-02-11 10:21 Status RUNNINGCheckpoint Lag 00:00:18 (updated 00:00:02 ago)Process ID 101632Log Read Checkpoint File /u02/data/gg123ora12/dirdat/onprem/rt000000000 2019-02-11 10:21:19.695712 RBA 26366Test replication between on-premise database and Oracle Cloud Oracle Cloud Database Note the number of rows in the MYOBJEC TS table.[oracle@OGGDB ~]$ sqlplus demo/oracle@pdb1SQL*Plus: Release 12.2.0.1.0 Production on Mon Feb 11 10:22:03 2019Copyright (c) 1982, 2016, Oracle. All rights reserved.Last Successful login time: Mon Feb 11 2019 10:18:56 +00:00Connected to:Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit ProductionSQL> select count(*) from myobjects; COUNT(*)---------- 0On-premise Database Note the number of rows in the MYOBJEC TS table.[oracle@rac01 goldengate]$ sqlplus demo/oracle@pdb1SQL*Plus: Release 12.2.0.1.0 Production on Mon Feb 11 18:22:41 2019Copyright (c) 1982, 2016, Oracle. All rights reserved.Last Successful login time: Mon Feb 11 2019 18:17:28 +08:00Connected to:Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit ProductionSQL> select count(*) from myobjects; COUNT(*)---------- 0Insert rows into the MYOBJECTS table SQL> insert into myobjects select * from all_objects where owner='HR';41 rows created.SQL> commit;Commit complete.Verify on-premise Extract has captured the changes GGSCI (rac01.localdomain as C##oggadmin@cdb1_2/CDB$ROOT) 45> stats ext3 latest table pdb1.demo.myobjectsSending STATS request to EXTRACT EXT3 ...Start of Statistics at 2019-02-11 18:25:39.Output to ./dirdat/onprem/lt:Extracting from PDB1.DEMO.MYOBJECTS to PDB1.DEMO.MYOBJECTS:*** Latest statistics since 2019-02-11 18:11:09 *** Total inserts 41.00 Total updates 0.00 Total deletes 0.00 Total discards 0.00 Total operations 41.00End of Statistics.GGSCI (rac01.localdomain as C##oggadmin@cdb1_2/CDB$ROOT) 46> stats pump3 latest table pdb1.demo.myobjectsSending STATS request to EXTRACT PUMP3 ...Start of Statistics at 2019-02-11 18:26:02.Output to ./dirdat/onprem/rt:Extracting from PDB1.DEMO.MYOBJECTS to PDB1.DEMO.MYOBJECTS:*** Latest statistics since 2019-02-11 18:15:32 *** Total inserts 41.00 Total updates 0.00 Total deletes 0.00 Total discards 0.00 Total operations 41.00End of Statistics.Oracle GoldenGate Cloud Service environment Verify GGSC Replicat has applied the changes GGSCI (ggcsinst-ggcs-1 as c##ggadmin@OGGDB/PDB1) 19> stats rep1 latestSending STATS request to REPLICAT REP1 ...Start of Statistics at 2019-02-11 10:27:26.Replicating from PDB1.DEMO.MYOBJECTS to PDB1.DEMO.MYOBJECTS:*** Latest statistics since 2019-02-11 10:27:18 *** Total inserts 41.00 Total updates 0.00 Total deletes 0.00 Total discards 0.00 Total operations 41.00End of Statistics.Oracle Cloud Database Note the number of rows which are now present in the MYOBJEC TS table.[oracle@OGGDB ~]$ sqlplus demo/oracle@pdb1SQL*Plus: Release 12.2.0.1.0 Production on Mon Feb 11 10:28:02 2019Copyright (c) 1982, 2016, Oracle. All rights reserved.Last Successful login time: Mon Feb 11 2019 10:22:03 +00:00Connected to:Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit ProductionSQL> select count(*) from myobjects; COUNT(*)---------- 41 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download