oraclestream.docx
- 文档编号:5930445
- 上传时间:2023-01-02
- 格式:DOCX
- 页数:53
- 大小:33.03KB
oraclestream.docx
《oraclestream.docx》由会员分享,可在线阅读,更多相关《oraclestream.docx(53页珍藏版)》请在冰豆网上搜索。
oraclestream
OracleBi-DirectionalDataReplication
VincentChan,vkchan99@
Dataavailabilityisanimportantfactortothesuccessofanybusinesses.Inadistributedenvironment,OracleStreamsimprovesdataavailabilityandaccessibilitybysharingandreplicatinginformationtomultiplesitesquicklyandreliably.OracleStreamswasintroducedinOracle9iandhassincematuredintorobustandpowerfulsoftwareisusedtoperformdatareplication,datawarehousetransformationandmessaging.
Thisispartoneofatwo-partseriesofillustratingthesetupofbi-directionalreplicationusingOracle10gStreams.Partoneprovidestheproceduresforsettingupbi-directionalschema-levelreplicationandconflictresolutionbetweentwodatabases.Parttwowilldemonstratehowanewsourcedatabasecanbeeasilyaddedintothereplicatedenvironment.
StreamsOverview
TherearethreebasicprocessesofOracleStreams:
Capture,StagingandConsumption(Apply).
ThecaptureprocessatthesourcesitecaptureseventssuchasDMLandDDLfromeithertheonlineredologsorarchivedlogfiles,formatsthechangesintoLogicalChangeRecords(LCRs)andqueuesthemintoastagingarea(queue).TheLCRsarethenpropagatedtoanapplyqueueatthedestinationsitewherethechangesareeventuallydequeuedandappliedbytheapplyprocess.
Inabi-directionalreplicationsetup,eachsitecaptures,propagatesandapplieschangestotheothersite,keepingthedatabasesatbothsitescurrent.Eachsiteactsasasourcedatabaseandasadestinationdatabase.Becausesimultaneousupdatescanbeperformedonthesamerecordsbybothsites,conflictscanarise.Inthefollowingsections,wewilldiscussStreamspre-builtupdateconflictresolutionhandlerandhowconflictscanbeavoidedorminimized.
ConflictDetection
WhenanapplyprocessappliesrowLCRatthedestinationdatabase,conflictsareautomaticallydetectedwhen
1. Anupdateconflictoccurs–thesamerecordisupdatedsimultaneouslybytwositesresultinginamismatchofthecurrentcolumnvalueatthedestinationdatabaseandtheoldcolumnvalueatthesourcedatabase.
2. Adeleteconflictoccurs–therecordtobedeleteddoesnotexistinthedestinationdatabase.
3. Auniquenessconflictoccurs–arecordinsertedinthedestinationdatabaseresultsinauniqueorprimarykeyconstraintviolation.
4. Aforeignkeyconflictoccurs–arecordinsertedorupdatedinthedestinationdatabaseresultsinaforeignkeyconstraintviolation.
Ideally,youshoulddesignyoursystemtoavoidorminimizeconflicts.Therearevariouswaysofachievingthatsuchasusinguniquesequencekeysateachreplicatedsite,horizontalsubsettingofdatatoensureeachsitecanonlymakechangestoitsowndataandavoidingphysicaldeletionofrecordsbylogicallymarkingtherecordsdeletedandpurgingthematalatertime.
ConflictResolution
Oracleonlyprovidestheupdateconflicthandler.Ifthepre-builtconflicthandlerisinsufficient,youcanalsocreateacustomconflicthandlertoresolvepossibleconflicts.
Thefourtypesofpre-builtupdateconflicthandlersare
1. Discard–RowLCRpropagatedfromthesourcesiteisdiscardedatthedestinationsitewhenaconflictisdetected.
2. Overwrite–RowLCRpropagatedfromthesourcesiteoverwritesthedataatthedestinationsitewhenaconflictisdetected.
3. Minimum–Thecolumnvalueoriginatedfromthesourcesiteiscomparedwiththecolumnvalueatthedestinationsite.Theapplyprocessappliesorretainsthelowervalueatthedestinationsite.
4. Maximum–Thecolumnvalueoriginatedfromthesourcesiteiscomparedwiththecolumnvalueatthedestinationsite.Theapplyprocessappliesorretainsthehighervalueatthedestinationsite.
Thetypeofupdateconflicthandlertoselectfromisdrivenbyyourbusinessrulesandrequirements.Forexample,ifyoudesignateonesiteastheauthoritativesite,youmaywanttodiscardchangespropagatedfromothersiteswhenaconflictoccurs.Inthisarticle,wewillusethediscardandoverwritemethodstoresolveconflicts.
Designingyoursystemtoavoidandresolvingpossibleconflictsisoneofthemostcriticalaspectsforasuccessfulimplementationofbi-directionalreplication.DetailedinformationonConflictResolutioncanbeobtainedfromhttp:
//download-.
SettingUpBi-DirectionalReplication
AnoverviewoftheOracleStreamsenvironment:
HostName
InstanceName
GlobalDatabaseName
DatabaseRole
trout3
ladb
ladb.world
Source/Destinationdatabase
trout4
sfdb
sfdb.world
Source/Destinationdatabase
Step1:
Configurearchivelogmode
Archivedredologsarerequiredbythecaptureprocesstoextractchanges.Verifybothladbandsfdbareinarchivelogmodeandconfigureyourlogarchivedestinationorflashrecoveryarea.
Step2:
Modifyinitializationparameters
Parameter
ladb
sfdb
Description
compatible
10.2.0
10.2.0
BothdatabasesarerunningOracle10gR2.
global_names
true
true
Databaselinknamemustmatchglobalname.
job_queue_processes
2
2
Themaximumnumberofjobqueueprocessestopropagatemessages.Setthisparametertoatleast2.
db_recovery_file_dest_size
10G
10G
Specifiesthestoragesizeofdatabaserecoveryfilessuchasarchivelogs.
db_recovery_file_dest
/u01/app/oracle/flashdest
/u01/app/oracle/flashdest
Specifiesthelocationofdatabaserecoveryfilessuchasarchivelogs.
parallel_max_servers
6
6
Themaximumnumberofparallelexecutionprocessesthatcanbeusedbythecaptureandapplyprocesses.
Youmayencounterora-1372orora-16081duringStreamscaptureandapplyifthevalueissettoolow.Setthisparametertoatleast6.
sga_target
500M
500M
EnablesAutomaticSharedMemoryManagement(ASMM).InOracle10gR2,ASMMautomaticallymanagesthestreams_pool_size.streams_pool_sizeprovidesbufferareasforstreamsprocessing.
Ifthisparameterisnotset,youshouldmanuallysetstreams_pool_areatoatleast200M.Otherwise,memorywillbeallocatedforStreamsfromthesharedpoolarea.
Step3:
Setuptnsnames.ora
AddtheTNSentriesontrout3andtrout4.
LADB.WORLD=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST=trout3)(PORT=1521))
(CONNECT_DATA=
(SERVER=DEDICATED)
(SERVICE_NAME=ladb)
)
)
SFDB.WORLD=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST=trout4)(PORT=1521))
(CONNECT_DATA=
(SERVER=DEDICATED)
(SERVICE_NAME=sfdb)
)
)
Step4:
Createtablespaces
CreatethetablespacesfortheapplicationschemaandStreamsadministrator.
Onladb
createtablespaceappstsdatafile'/u02/oradata/ladb/appsts01.dbf'size100M;
createtablespacestreamstsdatafile'/u02/oradata/ladb/streamsts01.dbf'size100M;
Onsfdb
createtablespaceappstsdatafile'/u02/oradata/sfdb/appsts01.dbf'size100M;
createtablespacestreamstsdatafile'/u02/oradata/sfdb/streamsts01.dbf'size100M;
Step5:
CreateStreamsadministrator
CreatetheStreamsadministratorinbothladbandsfdb.
createuserstrmadminidentifiedbystrmadmindefaulttablespacestreamststemporarytablespacetemp;
grantdba,select_catalog_roletostrmadmin;
execdbms_streams_auth.grant_admin_privilege('strmadmin',true);
Step6:
Createapplicationschema
Createtheapplicationschemainbothladbandsfdb.
createuserappsidentifiedbyappsdefaulttablespaceappststemporarytablespacetemp;
grantconnect,resourcetoapps;
grantselectonv_$databasetoapps;
Step7:
Createapplicationschema’sobjects
Createtheapplicationschema’sobjectsinladb.TheobjectsinsfdbarecreatedlaterusingOracle’sDataPumputility.
connectapps/apps@ladb.world
createtabledept(
deptnonumber(10)notnull,
dnamevarchar2(20)notnull,
sitevarchar2(10)notnull
);
createorreplacetriggerdept_site_trg
beforeinsertondeptforeachrow
begin
if:
new.siteisnullthen
selectnameinto:
new.sitefromv$database;
endif;
end;
/
altertabledeptaddconstraintdept_pkprimarykey(deptno);
createsequencedeptno_laseqstartwith1incrementby5;
createsequencedeptno_sfseqstartwith2incrementby5;
insertintodeptvalues(deptno_laseq.nextval,'FINANCE','LADB');
insertintodeptvalues(deptno_laseq.nextval,'HR','LADB');
insertintodeptvalues(deptno_laseq.nextval,'RESEARCH','LADB');
commit;
createtableemp(
empnonumber(10)notnull,
enamevarchar2(20)notnull,
sitevarchar2(10)notnull,
jobvarchar2(10),
deptnonumber(10)
);
createorreplacetriggeremp_site_trg
beforeinsertonempforeachrow
begin
if:
new.siteisnullthen
selectnameinto:
new.sitefromv$database;
endif;
end;
/
altertableempaddconstraintemp_pkprimarykey(empno);
altertableempaddconstraintemp_fkforeignkey(deptno)referencesdept(deptno);
createsequenceempno_laseqstartwith1incrementby5;
createsequenceempno_sfseqstartwith2incrementby5;
insertintoempvalues(empno_laseq.nextval,'MIKE','LADB','CLERK',1);
insertintoempvalues(empno_laseq.nextval,'JANE','LADB','ANALYST',1);
insertintoempvalues(empno_laseq.nextval,'ZACH','LADB','ENGINEER',11);
commit;
Step8:
Enabletablelevelsupplementallogging
Setupsupplementalloggingastheappsuseronladb.Supplementallogginglogsadditionalcolumnsinformationintotheredologsforrowidentification.
Therearetwotypesofsupplementalloggroups:
1. Unconditionalsupplementalloggroup–alwayslogsthebeforeimagesofthespecifiedcolumnsintheloggroupwheneveranytablecolumnisupdated.
2. Conditionalsupplementloggroup–onlylogsthebeforeimagesofthespecifiedcolumnsintheloggroupwhenatleastonecolumninthe
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- oraclestream
![提示](https://static.bdocx.com/images/bang_tan.gif)