Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.|
DeepDiveintoAutomatingOracleGoldenGateusingtheNewMicroservicesVolkerKuhr,SeniorPrincipalProductManagerJingLiu,DirectorofDevelopmentNickWagner,DirectorProductManagement
OracleGoldenGateDevelopmentOctober2,2017
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.|
SafeHarborStatementThefollowingisintendedtooutlineourgeneralproductdirection.Itisintendedforinformationpurposesonly,andmaynotbeincorporatedintoanycontract.Itisnotacommitmenttodeliveranymaterial,code,orfunctionality,andshouldnotberelieduponinmakingpurchasingdecisions.Thedevelopment,release,andtimingofanyfeaturesorfunctionalitydescribedforOracle’sproductsremainsatthesolediscretionofOracle.
2
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.|
OracleGoldenGate
RealtimePerformance
Extensible&Flexible
Proven&Reliable
OracleGoldenGateprovideslow-impactcapture,routing,transformation,anddeliveryofdatabasetransactionsacrosshomogeneousandheterogeneousenvironmentsinreal-timewithnodistancelimitations.
MostDatabases Data
EventsTransactionStreams
Cloud
DBs
BigData
SupportsDatabases,BigDataandNoSQL:
*Themostpopularenterpriseintegrationtoolinhistory
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.|
Agenda
1
2
NewGoldenGateMicroservicesArchitecture
AutomatingandEmbeddingGoldenGate
4
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.|
NewGoldenGateMicroservicesArchitecture
• GoldenGatecomponentsasmicroserviceswithcomprehensiveRESTfulinterfaces.
• Enables remote andsecureconfiguration,administration,andmonitoringcapabilities.
• EnablesApplicationstoembed,automate,andorchestrateGoldenGate.
5
Simplifieslargescaleandclouddeployments
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.|
Administration,Distribution,Receiver,MetricsServiceswithRESTful ServiceInterfacesNewServicesArchitectureforCloudandLarge-scaleDeployments
Source Target
TrailFiles
Extract
ManagerGGSCIAdminServer
TrailFiles
Replicat
PumpsDistribution
Server
CollectorsReceiverServer
SourceDatabase
TargetDatabase
HTTPSRESTFulServiceInterfacesManagerGGSCI
AdminServer
MetricsServer
ServiceManager Service
Manager
MetricsServer
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.|
NewGoldenGateMicroservices• AdministrationService– ReplacesGGSCIandManagerwithasingleadministrationserviceformanagingreplicationprocesses
• Multi-threadedDistributionService– Replacesmultiplesource-sideExtractPumpswithasingleinstanceservice.– Lightweightfilteringonly(notransformations)
• Multi-threadedReceiverService– Replacesthemultiplediscretetarget-sideServer/Collectorswithasingleinstanceservice
• PerformanceMetricsService– Newserviceformonitoringmetricsrelatedtoaparticulardeployment– Alsoavailableinthetraditionalarchitecture
• ServiceManager– Newserviceformanagingmultipledeploymentsonalocalhost
7
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.| 8
CommandLine,Browsers,ProgrammaticRESTful InterfacesAdministerGoldenGate withVarietyofClients
ServiceInterface
Anything viaService IF
Scripts & Interpreters
PLSQL
Browsers
AdminClient
AdminServer
TrailFiles
ReplicatReceiverServer
TrailFiles
ExtractDistributionServer
HTTPS
ServiceManager
MetricsServer
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.| 9
RESTbasedclientsforRemoteandSecureGoldenGate Administration
• UsebrowsertoadministerandmonitorGoldenGate
• EachservicehasanembeddedHTML5app
• ThinCommandLineclient- similartoGGSCI
• Connectandadministerlocalandremotedeployments
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.|
ExampleRESTful ServiceCalltoCreateExtract
POSThttps://xyz.us.oracle.com:9101/services/v2/processes/extracts/e001JSONPayload:
10
SingleCalltocreate,updateparameterfile,register,andstartintegratedExtract
{"$schema":"ogg:extract","credentials":{"domain":"OracleGoldenGate","alias":"gg1"},"config":["-- Parameterfileforprimaryextract:e001","extracte001","useridalias gg1","exttrail aa,formatrelease12.3","tranlogoptions excludetag +","eofdelaycsecs 10","tableu1.*;",""
],
"status":“running","source":{"tranlogs":"integrated"},"registration":{"csn":"0.0","share":true},"targets":[{"name":"aa"}]}
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.|
SecurityModel
ClientAuthorization• IdentityviaSSLusercertificatesorviausername/password– IntegratewithSSOconfiguredinproxy/middleware.
• Roles– SecurityUser– Administrator–Operator– User
11
StandardTLS/SSLbasedauthenticationandClientAuthorization
GoldenGateServices
SSLServerCertificate
AuthorizedClients
Clients
SSLUserCertificate
HTTPS
User/Passwd
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.| 12
IndustrystandardHTTP(S)initiatedfull-duplexstreamingprotocolWebSockets -- DefaultDataCommunicationProtocol
TrailFiles
Extract TrailFiles
Replicat
DistributionService Receiver
Service
wss://……/
• SSLbasedsecurity
• CanseamlesslytraversethroughHTTPforward/reverseproxyservers
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.| 13
Easilybuildself-serviceapplicationswhichautomatesGoldenGateAllowsApplicationstoEmbedandAutomateGoldenGate
GoldenGateServices
Deployment
GoldenGateServices
Deployment
GoldenGateServices
Deployment
GoldenGateServices
Deployment
RESTcallstoconfigure,administer,andmonitorGoldenGate
CatalogofRESTful APIsareavailableforallservices
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.| 14
N-wayactive-activereplicationautomaticallysetupGoldenGate AutomatedinOracleDatabaseSharding
• Automaticcreationofreplicationprocesses• Extracts,Replicats,DistributionPaths• AutomaticCDRforresolvingconflicts
• Replicationtopologyisautomaticallyreconfigureduponsharding changes
ShardCatalog/Coordi
nator
AppTier
RoutingTier
DataTier
ConnectionPool
ShardDirectors
Shardingkey
…
…
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.|
Sharding - AutomaticOracleGoldenGateConfiguration
15
2.Sendcommandtoshards
ShardCatalog
Shards
1.Definereplicationtopology
......
GDSCTL >DEPLOY
GDSCTL>createshardcatalog …. -repl OGG….
GDSCTL>addshardgroup ..shgrp1-repfactor 3
GDSCTL>addshard-shardgroup shgrp1….gg_service host01:9000/deploy1….GDSCTL>addshard-shardgroup shgrp1….-gg_service host02:9000/deploy1GDSCTL>addshard-shardgroup shgrp1….-gg_service host03:9000/deploy1…..…….GDSCTL>DEPLOY
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.|
Sharding - AutomaticOracleGoldenGateConfiguration
16
2.Sendcommandtoshards
ShardCatalog
Shards
OGGServices
3.https
Extract
1.Definereplicationtopology
Replicat
DistributionPath
4.Setupreplication
OGGShardingOrchestration
Logic
5.Update status
OGGsharding orchestrationlogicissuesRESTcallstosetup
GoldenGate replication
......
GDSCTL >DEPLOY
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.|
ProgramAgenda
NewGoldenGateMicroservicesArchitecture
AutomatingandEmbeddingGoldenGate
17
1
2
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.|
SetupReplicationusingsimplePL/SQLbuildingsblocks
18
one_way_replication
HTTPS
create_extractcreate_replicatcreate_distpath
….
gg_send_request
DatabasePL/SQLProcedures
Note:WeplantopublishexamplebuildingblocksonOTN
GoldenGateServices
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.|
SEND_GG_REQUEST
19
UsesUTL_HTTPtomakeRESTcallsprocedure send_gg_request( gg_uri IN varchar2, verb IN varchar2, payload IN varchar2, resp_code OUT number, resp_text OUT NOCOPY varchar2)
•URIofGoldenGateServiceEndpoint:https://<gghost>:9001/services/v2/processes/extracts/e001• HTMLVerb :POST,PATCH,DELETE,GET• Payload :JSONobjectspecifyingthereplicationprocess• Response_Code :200(OK),201(CREATED),…• Response_Text :<detailedResponsefromService>
UTL_HTTP
GoldenGateServicesHTTPS
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.|
procedure create_[extract|replicat]( [ext|rep]_name IN varchar2, adminsrv_uri IN varchar2, db_credentials IN varchar2, [ext|rep]_trail IN varchar2, [ext|rep]_params IN varchar2)
ProcedurestocreateGoldenGateProcesses
20
procedure create_distpath( path_name IN varchar2, ds_uri IN varchar2, src_trail IN varchar2, rs_uri IN varchar2, tgt_trail IN varchar2 )
JSON
Payload
send_gg_request
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.| 21
CustomizeasneededOrchestrationProceduretosetupOne-wayReplication
procedure one_way_replication
(db_src varchar2,gg_src varchar2,gg_trg varchar2,db_trg varchar2,tables varchar2,instantiation varchar2 [YES|NO],auto_CDR varchar2 [YES|NO] )
create_extractcreate_distpathcreate_replicat…
Database UtilitiesDBMS_DATAPUMPDBMS_FILE_TRANSFERDBMS_TTS...
DBMS_GOLDENGATE_ADMADD_AUTO_CDR(…)
Requires DB 12.2+ & OGG 12.3
Generate ER process namesGenerate Trail file names…
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.| 22
SetupOneWayReplication
• OrchestrationPackage• Databaseexport/importtoinstantiatetarget• DatabasesandGoldengateServicesareup
OrchestrationDatabase
GoldenGateServices
GoldenGateServices
REPDB01 REPDB02
BEGINadd_oneway_replication(db_src => ′REPDB01′,gg_src => ′GG_Inst01′,gg_trg => ′GG_Inst02′,db_trg => ′REPDB02′,tables => ′HR.EMP′,instantiation => ′YES′,auto_CDR => ’NO’
);END;
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.|
CreateandStartIntegratedExtract
{"config":["Extract E01AA","ExtTrail et","UseridAlias gg_src","Table HR.EMP;"],"source":{"tranlogs":"integrated"
},"credentials":{"alias":"gg_src"
},"registration":"default","begin":"now","targets":[{"name": "et"
}]}
23
OrchestrationDatabase
GoldenGateServices
GoldenGateServices
1.CreatingExtract2.CreatingDistributionPath3.DatapumpExport4.DatapumpImport5.CreatingReplicat
Extract
POSTHTTPS://gg_src:8001/....
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.|
CreateandStartDistributionPath
24
OrchestrationDatabase
GoldenGateServices
GoldenGateServices
1.CreatingExtract2.CreatingDistributionPath3.DatapumpExport4.DatapumpImport5.CreatingReplicat
REPDB01 REPDB02Extract
{"$schema":"ogg:distPath","name":"AAtoAB","description":"distPath1","source":{ "uri":"trail://ggsource:8002/
dirdat/et"},"target":{"uri":"ogg://ggtarget:9003/
dirdat/rt"},"begin":{"sequence":0,"offset":0},"status":"running"}
POSTHTTPS://gg_src:8002/....
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.| 25
Datapump Export
OrchestrationDatabase
GoldenGateServices
GoldenGateServices
1.CreatingExtract2.CreatingDistributionPath3.DatapumpExport4.DatapumpImport5.CreatingReplicat
DatapumpExport(EXPDP)
v_dp_handle:=dbms_datapump.opendbms_datapump.add_file…dbms_datapump.set_parameterdbms_datapump.metadata_filterdbms_datapump.start_jobdbms_datapump.detach
REPDB01 REPDB02Extract
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.|
Datapump Import
26
OrchestrationDatabase
GoldenGateServices
1.CreatingExtract2.CreatingDistributionPath3.DatapumpExport4. DatapumpImport5.CreatingReplicat
REPDB01 REPDB02
DatapumpImport(IMPDP)
v_dp_handle:=dbms_datapump.opendbms_datapump.add_file…dbms_datapump.set_parameterdbms_datapump.metadata_filterdbms_datapump.start_jobdbms_datapump.detach
Extract
GoldenGateServices
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.| 27
GoldenGateinstantiationSCNfeaturesautomaticallyfilterspre-instantiationchangesCreateandStartReplicat tocompletesetup
OrchestrationDatabase
GoldenGateServices
GoldenGateServices
1.CreatingExtract2.CreatingDistributionPath3.DatapumpExport4.DatapumpImport5.CreatingReplicat
REPDB01 REPDB02Extract Replicat
{"config":["Replicat R01BA","UseridAlias ggadmin","Map HR.EMP,","Target HR.EMP;"],"source":{"name":"rt"
},"credentials":{"alias":"gg_trg"
},"checkpoint":{"table":"ggadmin.ckpt"
}}
POSTHTTPS://gg_trg:9001/....
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.|
SetupBidirectionalActive-ActiveReplicationfromPL/SQL
28
UsingthesamehighlevelbuildingblockOrchestrationDatabase
GoldenGateServices
GoldenGateServices
REPDB01 REPDB02
BEGINadd_oneway_replication(db_src => ′REPDB01′,gg_src => ′GG_Inst01′,gg_trg => ′GG_Inst02′,db_trg => ′REPDB02′,tables => ′HR.EMP′,instantiation => ′YES′,auto_CDR => ’YES’ );
END;
BEGINadd_oneway_replication(db_src => ′REPDB02′,gg_src => ′GG_Inst02′,gg_trg => ′GG_Inst01′,db_trg => ′REPDB01′,tables => ′HR.EMP′,instantiation => ′NO′,auto_CDR => ’YES’ );
END;
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.|
Hub&SpokeConfiguration
29
TwocallsforeveryHub/Spokepair
GG GG
GG
GG
BEGINadd_oneway_replication(db_src => ′HUB′,gg_src => ′GG_Inst_HUB′,gg_trg => ′GG_Inst_01′,db_trg => ′Spoke01′,tables => ′HR.EMP′,instantiation => ′YES′,auto_CDR => ’YES’ );
END;
BEGINadd_oneway_replication(db_src => ′Spoke01′,gg_src => ′GG_Inst_01′,gg_trg => ′GG_Inst_HUB′,db_trg => ′HUB′,tables => ′HR.EMP′,instantiation => ′NO′,auto_CDR => ’YES’ );
END;
GG
Spoke
Hub
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.|
GlobalReplicationCatalog
• ControlReplicationEnvironmentsinreplicationcatalog• Create,modifyorremoveReplicationTopologiesfromthiscatalogtable• CanmanagereplicationwithDMLtotable– HaveaDMLtriggerexecutethePL/SQLreplicationorchestrationlogic
KeepTrackofReplicationDeployments
DMLTriggerOrchestration
Logic
GoldenGateServices
HTTPS
POSTPATCHDELETE( )
Replication CatalogSourceDatabase
Extract DistPath
Replicat TargetDatabase
...
REPDB01 E01AB A_to_B R01BA REPDB02...
REPDB01 E02AC A_to_C R01CA REPDB03...
….. ... ... ... ......
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.| 31
RetrieveinformationaboutReplicationenvironmentsMonitorReplicationfromtheDatabase
Replication CatalogSourceDatabase
Extract DistPath
Replicat TargetDatabase
... ... Status LAG[s]
Throughput[Changes/s]
REPDB01 E01AB A_to_B R01BA REPDB02... ...
OK 1.8 54,673
REPDB01 E02AC A_to_C R01CA REPDB03... ...
OK 1.8 78,924
….. ... ... ... ...... ...
... ... ...
• UseGETRESTful calls• Checkstatus,warnings/errors(ifany),throughput,lag,…
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.|
ClassicArchitecture
✖Combinationofscripts– ShellScripts,SQLscripts,…–ObeyFiles,ParameterFiles,…
✖RequiresOSaccesstoDBhosts– Notsecure
GoldenGateMicroservices
✔SimpleandsecureRESTcallsforallGoldenGateoperations
32
EasyOrchestrationwithNewGoldenGateMicroservices
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.|
NewGoldenGateMicroservicesArchitecture
• GoldenGatecomponentsasmicroserviceswithcomprehensiveRESTfulinterfaces.
• Enablesremoteandsecureconfiguration,administration,andmonitoringcapabilities.
• EnablesApplicationstoembed,automate,andorchestrateGoldenGate.
33
Simplifieslargescaleandclouddeployments
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.| 34
AdditionalsessionsandDemos
Sunday,October1• LiftandShiftWorkloadstoCloudwithOracleDataIntegrationPlatform
Cloud[SUN6653]• DataMovementbetweenOn-Prem,FusionERPCloud,FusionHCMCloud
andSalesforce[SUN7286]• AccelerateMigrationtoCloudInfrastructurewithDataIntegrationPlatform
[SUN6896]
Monday,October2• OracleDataIntegrationPlatformStrategyandRoadmap[CON6646]• FillingYourDataLakewithPotableData,UsingDataIntegration[CON5465]• GoldenGate:
DeepDiveintoAutomatingOGGusingthenewMicroservices[CON6569]• OracleDataIntegrationPlatform:FoundationforCloudIntegration
[CON6650]• OracleDataIntegrationPlatformEmpowersEnterpriseGradeBigData
Solutions[CON6893]• OracleDataIntegrationPlatformCloudDeepDive[CON6651]• OracleGoldenGate CloudService:Real-TimeDataReplicationintheCloud
[HOL7715]
Tuesday,October3• OracleDataIntegratorProductUpdateandStrategy[CON6654]• OracleEnterpriseDataQuality:ProductOverviewandRoadmap[CON6656]• AccelerateCloudOn-BoardingUsingOracleGoldenGate CloudService
[CON6894]• OracleEnterpriseDataQualityforAllTypesofData[HOL7653]• OracleDataIntegrationPlatform:aCornerstoneforBigData[CON6655]• GoldenGate:MAAandBestPracticesforOracle
GoldenGateMicroservices[CON6570]• OracleGoldenGateProductUpdateandStrategy[CON6897]
Wednesday,October4• APracticalPathtoEnterpriseDataGovernancewithEnterpriseDataQuality
[CON6657]• OracleDataIntegratorandOracleGoldenGate forBigData[HOL7708]• IntroductiontoOracleDataIntegrationPlatformCloud[HOL7673]• AnEnterpriseDatabus:GoldenGate intheCloudWorkingwithKafkaand
Spark(CON6895]• GoldenGate:BestPractices&DeepDiveonOGG12.3
MicroservicesatCloud[CON6568]• OracleGoldenGate forBigData[CON6898]• OracleDataIntegrationPlatformCloudServiceGovernanceEdition
[CON6652]• OracleSharding:LinearScalability,ExtremeAvailability,andGeo-Distribution
[CON6673]