1 / 23

Data Distribution & Replication

Data Distribution & Replication. Distributed Databases Definitions A single logical database that is spread physically across computers in multiple locations that are connected by a data communications link. Decentralized Database. A collection of independent databases : Homogeneous

emery-pena
Download Presentation

Data Distribution & Replication

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Data Distribution &Replication

  2. Distributed Databases Definitions • A single logical database that is spread physically across computers in multiple locations that are connected by a data communications link. • Decentralized Database. • A collection of independent databases: • Homogeneous • - Same DBMS at each node. • – Autonomous - Independent DBMSs. • – Non-autonomous - Central , coordinating DBMS. • • Heterogeneous - Different DBMSs at different nodes.

  3. $HOME/network/admin/tnsnames.ora Processo Listener Processo Server x Connessione Dedicata Oracle Client Sincrona - Asincrona SqlNet Client / Server - Server / Server ALIAS = (DESCRIPTION = (ADDRESS = (PROTOCOL= TCP) (Host= xxx.yyy.zzz.www) (Port= 1526)) (CONNECT_DATA = (SID = O920)) ) Oracle DB Server $HOME/network/admin/listener.ora $HOME/network/admin/sqlnet.ora TNS Transparent Network Substrate

  4. Tnsnames.ora Listener.ora TNS - TCP/IP Oracle DB Oracle DB Server 1 Server 2 usr1 / pwd1 T1 usr2 / pwd2 T2 Alias del tnsnames della macchina server 1 connect system/manager; grant create database link to usr1; grant create synonym to usr1; connect usr1/pwd1; create database link dbms2 connect to usr2 identified by pwd2 using ‘alias2’; create synonym S2 for T2@dbms2; Data Base Link

  5. Two-Phase Commit • • Prepare Phase • Coordinator receives a commit request • Coordinator instructs all resource managers to get ready to “go either way” on the transaction. • Each resource manager writes all updates from that transaction to its own physical log. • Coordinator receives replies from all resource managers. • If all are ok, it writes commit to its own log; • if not then it writes rollback to its log. • • Commit Phase • Coordinator then informs each resource manager of its decision and broadcasts a message to either commit or rollback (abort.) • If the message is commit, then each resource manager transfers the update from its log to its database. • A failure during the commit phase puts a transaction “in limbo.”

  6. Distribution Consolidation Data Off-loading WorkFlow Multi Site Case Study Data Distribution

  7. SnapShot Read Only SnapShot WriteAble Multi Master Site SnapShot UpDateAble Advanced Replication Oracle Offers

  8. Rows Filter Where Fields Filter Select TABLESPACE tablespace_unito PCTFREE n STORAGE (INITIAL n NEXT n PCTINCREASE 0 MINEXTENTS 1 MAXEXTENTS 121) Master Slave SnapShot Read Only CREATE SNAPSHOT snap_unito WITH [ PRIMARY KEY | ROWID ] REFRESH [ FAST | COMPLETE | FORCE ] START WITH date NEXT date AS subquery;

  9. Sempre applicabile - Nessun vincolo architetturale Se possibile adotta tecnica Fast altrimenti Complete Applicabile solo: 1) Se la select è definibile semplice ovvero: a) appoggia su un solo oggetto di tipologia tabellare b) è priva di costrutti quali group by, order by e subquery 2) Esiste lato Master un Log 3) Il Log esiste da tempo utile per supportare il nuovo refresh REFRESH [ FAST | COMPLETE | FORCE ]

  10. SnapShot WriteAble CREATE SNAPSHOT snap_unito TABLESPACE tablespace_unito PCTFREE n STORAGE (INITIAL n NEXT n PCTINCREASE 0 MINEXTENTS 1 MAXEXTENTS 121) WITH [ PRIMARY KEY | ROWID ] REFRESH [ FAST | COMPLETE | FORCE ] START WITH date NEXT date FOR UPDATE AS subquery; Note Base: Modifiche in aggiornamento mono direzionale (da Master verso Slave) Modifiche perse al successivo refresh Applicazioni in ambiti prevalentemente di simulazione

  11. Trigger After Row For Each Row Insert Update Delete Refresh Operation View of Changed SnapShot View Master Table SnapShot Base Table SnapShot Log

  12. Trigger After Row For Each Row Insert Update Delete Trigger After Row For Each Row Insert Update Delete Refresh Operation View of Changed SnapShot View Master Table SnapShot Base Table SnapShot Log Log of Change

  13. Job Ready List Next? Compatibile con l’intervallo di ri-programmazione del Job SNP Background Processes Job Schedulati Job Ready Job List Caso di Failure 1° Tentativo dopo 1 Minuto 2° Tentativo dopo 2 Minuti 3° Tentativo dopo 4 Minuti n° Tentativo dopo 2^(n-1) Minuti con 3<n<17

  14. DBMS_JOB Package To schedule and manage jobs in the job queue, use the procedures in the DBMS_JOB package. There are no database privileges associated with using job queues. Any user who can execute the job queue procedures can use the job queue. Submitting a Job to the Job Queue To submit a new job to the job queue, use the SUBMIT procedure in the DBMS_JOB package: DBMS_JOB.SUBMIT ( job OUT BINARY_INTEGER, what IN VARCHAR2, next_date IN DATE DEFAULT SYSDATE, interval IN VARCHAR2 DEFAULT ’null’, no_parse IN BOOLEAN DEFAULT FALSE ) declare jobno number; begin DBMS_JOB.SUBMIT (jobno, ’begin procedura_unito01(1,’X’); end;’, SYSDATE, ’SYSDATE + 1’); commit; end;

  15. DBMS_JOB Package Broken Jobs A job is labeled as either broken or not broken. Oracle does not attempt to run broken jobs. However, you can force a broken job to run by calling the procedure DBMS_JOB.RUN. There are two ways a job can break: * Oracle has failed to successfully execute the job after 16 attempts. * You have marked the job as broken, using the procedure DBMS_JOB.BROKEN. DBMS_JOB.BROKEN ( job IN BINARY_INTEGER, broken IN BOOLEAN, next_date IN DATE DEFAULT SYSDATE ) Once a job has been marked as broken, Oracle will not attempt to execute the job until you either mark the job as not broken, or force the job to be executed by calling the procedure DBMS_JOB.RUN.

  16. DBMS_JOB Package DBMS_JOB.CHANGE (job IN binary_integer, what IN varchar2, next_date IN date, interval IN varchar2 ); DBMS_JOB.REMOVE (job IN binary_integer, );

  17. Conflict Resolution • In modalità Asincrona possono verificarsi conflitti che possono minare la • consistenza e l’integrità dei dati. Sono prevalentemente di tre tipologie: • Update • Uniqueness • Delete • Oracle offre sistemi di Definizione & Risoluzione Automatica dei Conflitti • time stamp • minimum value • maximum value • highest priority site • highest priority value • average • ……. • custom solution

  18. Dispatcher processes Shared server processes Oracle server code program interface Request queue Response queues Multithreaded Server Client Database server Listener System Global Area Oracle background processes

  19. MTS instance parameters: mts_servers = 4 mts_dispatchers = “(PROTOCOL=ipc)(DISPATCHERS=4)” mts_max_servers = 20 mts_max_dispatchers = 20

  20. Oracle Standby Implementation • Fail over Solution • Disaster Recovery Solution (if remote) • Ease of implementation • Minimum impact on Production System • Read Only Standby Database

  21. Primary Instance Standby Instance 3 2 1 ReadOnly Mode Recovery Mode Activate T.N.S. ARCH Redo log Recovery proc DBWR Arc log Arc log RFS Primary DB Standby DB Primary control file Standby control file Overview of Managed Oracle Standby DB

More Related