con6569 automating new goldengate microservices...oracle goldengate provides low-impact capture,...
TRANSCRIPT
Deep Dive into Automating OracleGoldenGate using the NewMicroservices Volker Kuhr, Senior Principal Product ManagerJing Liu, Director of DevelopmentNick Wagner, Director Product Management
Oracle GoldenGate DevelopmentOctober 2, 2017
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
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]