The following is the example environment:
On-Premises - Source
|
Cloud - Target
|
- âSet up the Target Database
- Set up Replicat in GGCS
- Send Data via GoldenGate Pump to Cloud
â
1. Setting up the Target Database
First, you need to provision a DBaaS Instance. How to Setup Oracle Database Cloud Service for GGCS Replications explains the DBaaS provisioning steps and how you can connect to a DBaaS instance.
After you have the connection to a DBaaS instance, you can then create schemas and tables in the target database. You also need to do the followings:
- Confirm the setup in the target database
- Disable triggers on the target database
- Turn off indexes for initial data loading
ssh -i jinyu_opc [email protected] ... >sqlplus /nolog SQL*Plus: Release 11.2.0.4.0 Production on Fri Jun 10 02:01:26 2016 Copyright (c) 1982, 2013, Oracle. All rights reserved. SQL> connect / as sysdba Connected. SQL> create user tpcadb identified by *****; User created. SQL> grant connect, resource to tpcadb; Grant succeeded. SQL>exec dbms_goldengate_auth.grant_admin_privilege('tpcadb','apply'); PL/SQL procedure successfully completed.
SQL> connect tpcadb/tpcadb SQL> @tpcadb.sql SQL> select table_name from user_tables; TABLE_NAME ------------------------------ ACCTN ACCTS BRANCH HISTORY TELLER SUSPECT 6 rows selected.
âAfter the target database is created, we can provision the GGCS instance. Please refer to How to Provision a GoldenGate Cloud Service (GGCS) Instance for the details. After the GGCS provisioning, you can setup an replicat to deliver data. In the dirprm directory, the following template files are created by default to help you get started.
ssh -i jinyu_opc [email protected] ... sudo su oracle cd $GGHOME > ./ggsci Oracle GoldenGate Command Interpreter for Oracle Version 12.1.2.1.10 21604177 21427144_FBO Linux, x64, 64bit (optimized), Oracle 11g on Nov 16 2015 19:12:31 Operating system character set identified as UTF-8. Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved. GGSCI (dbaas11-ggcs-1) 10> sh ls dirprm jagent.prm mgr.prm REPCL.prm REPCO.prm REPIN.prm replicat_obey.oby
The following example mgr.prm specifies the PORT for the manager process and configure the autorestart policy, which is to auto restart replicat processes and retry after 2 minutes for 3 times. The retry entry is reset every 2 hour (default):
-- Manager port number --GGCS Added default port number to 7744 PORT 7744 -- As a Manager parameter, PURGEOLDEXTRACTS allows to manage trail -- files in a centralized fashion and take into account multiple -- processes. -- For allocate dynamicportlist. Here the range is starting from port n1 through n2. -- Dynamicportlist 7740-7760 --PURGEOLDEXTRACTS , USECHECKPOINTS, MINKEEPHOURS <"x" hours> MINKEEPFILES <"y" number of files> -- we will have predefine path to the trail file -- If using DDL trigger then use the following parameters to purge the DDL history table. -- USERID , PASSWORD -- Start one or more Extract and Replicat processes automatically after they fail. -- AUTORESTART provides fault tolerance when something -- Temporary interferes with a process, such as intermittent network -- outages or programs that interrupt access to transaction logs. AUTORESTART REPLICAT *, RETRIES 3, WAITMINUTES 2, RESETMINUTES 120 -- This is to specify a lag threshold that is considered critical, -- and to force a warning message to the error log. Lagreport parameter -- specifies the interval at which manager checks for extract / replicat --lag. -- LAGREPORTMINUTES -- LAGCRITICALMINUTES -- Reports down processes -- DOWNREPORTMINUTES -- DOWNCRITICAL
GGSCI> start mgr Manager started. GGSCI> info mgr Manager is running (IP port dbaas11-ggcs-1.7744, Process ID 5017).
$ pwd /u01/app/oracle/oci/network/admin [oracle@dbaas11-ggcs-1 admin]$ more tnsnames.ora #GGCS generated file target = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = DBaaS11g)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = ORCL11g.a228251.oraclecloud.internal) ) ) $ sqlplus tpcadb/*****@target SQL*Plus: Release 11.2.0.4.0 Production on Thu Jun 9 22:50:58 2016 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production SQL> select table_name from user_tables; TABLE_NAME ------------------------------ ACCTN ACCTS BRANCH HISTORY TELLER SUSPECT 6 rows selected.
Details: An example error message is:
GGSCI (dbaas11-ggcs-1) 3> dblogin userid tpcadb@target password tpcadb ERROR: Unable to connect to database using user tpcadb@target. Please check privileges. ORA-00942: table or view does not exist.
Answer: Login as SYS in the database and run "exec dbms_goldengate_auth.grant_admin_privilege('tpcadb','apply'); " will resolve the issue.
GGSCI (dbaas11-ggcs-1 as ggadmin@ORCL11g) 5> dblogin userid tpcadb@target password ***** Successfully logged into database.
GGSCI> add credentialstore Credential store created in ./dircrd/.
You can start with preparing the replicat parameter file (REPIN.prm) shown as follows:
-- ################################################################### -- Runcmd: ADD REPLICAT , INTEGRATED EXTTRAIL -- Name of the replicat process. Limited to 8 charecters. --GGCS Added default REPLICAT name REPLICAT REPIN -- OGG database user login --GGCS Added default useridalias name to ggcsuser_alias useridalias ggcsuser_alias --Integrated parameter DBOPTIONS INTEGRATEDPARAMS (parallelism 2) --Discard file location. --GGCS Added default Discard file location DISCARDFILE ./dirrpt/REPIN.dsc, APPEND Megabytes 50 --DDL replication parameters DDL INCLUDE ALL -- , EXCLUDE OBJNAME ".
--DBLOGIN (IF integrated or coordinated replicat) --DBLOGIN userid , password - encrypted password --GGCS Added default USERIDALIAS name to ggcsuser_alias ALTER CREDENTIALSTORE ADD USER tpcadb@target PASSWORD tpcadb ALIAS ggcsuser_alias DBLOGIN USERIDALIAS ggcsuser_alias --Add replicat (IF integrated) --GGCS Added default as p1 Add replicat REPIN integrated,EXTTRAIL ./dirdat/p1 --Add checkpoint table (if Classic or coordinated) -- IF coordinated, uncomment the below line. GGCS Added default as REPCO_CHKPT --add replicat REPCO, exttrail ./dirdat/p1, COORDINATED, MAXTHREADS <20>, checkpointtable REPCO_CHKPT --add checkpointtable REPCO_CHKPT -- If Classic, uncomment the below 2 lines. GGCS Added default as REPCL_CHKPT --add replicat REPCL, exttrail ./dirdat/p1 --add checkpointtable REPCL_CHKPT
GGSCI> obey dirprm/replicat_obey.oby GGSCI> start repin Sending START request to MANAGER ... REPLICAT REPIN starting GGSCI (dbaas11-ggcs-1 as tpcadb@ORCL11g) 63> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING REPLICAT RUNNING REPIN 00:00:00 00:15:05
To simplify the testing, a GoldenGate extract pump is created to send pre-generated trail files to GGCS.
3.1 Creating a SOCK5 Proxy
ssh -N -f -i jinyu_opc -CD 1080 [email protected]
extract pumpopc RMTHOST 129.144.2.87, MGRPORT 7744,socksproxy 127.0.0.1:1080 discardfile ./dirrpt/pumgdop.dsc, purge RMTTRAIL ./dirdat/p1 FORMAT RELEASE 12.1 PASSTHRU TABLE TPCADB.*;
GGSCI> add extract pumpopc exttrailsource ./dirdat/ea EXTRACT added. GGSCI> add rmttrail ./dirdat/p1 extract pumpopc RMTTRAIL added. GGSCI> start pumpopc GGSCI> stats pumpopc
Sending STATS request to EXTRACT PUMPOPC1 ... Start of Statistics at 2016-06-15 21:13:18. Output to ./dirdat/p1: Extracting from TPCADB.ACCTN to TPCADB.ACCTN: *** Total statistics since 2016-06-15 17:57:00 *** Total inserts 334290.00 Total updates 660000.00 Total deletes 334290.00 Total discards 0.00 Total operations 1328580.00 *** Daily statistics since 2016-06-15 17:57:00 *** Total inserts 334290.00 Total updates 660000.00 Total deletes 334290.00 Total discards 0.00 Total operations 1328580.00 *** Hourly statistics since 2016-06-15 17:57:00 *** Total inserts 334290.00 Total updates 660000.00 Total deletes 334290.00 Total discards 0.00 Total operations 1328580.00 *** Latest statistics since 2016-06-15 17:57:00 *** Total inserts 334290.00 Total updates 660000.00 Total deletes 334290.00 Total discards 0.00 Total operations 1328580.00 ... End of Statistics.