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
- Same DBMS at each node.
– Autonomous - Independent DBMSs.
– Non-autonomous - Central , coordinating DBMS.
• Heterogeneous - Different DBMSs at different nodes.
Oracle Client
Sincrona - Asincrona
Client / Server - Server / Server
$HOME/network/admin/tnsnames.ora
Oracle DB
Server
ALIAS =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL= TCP)
(Host= xxx.yyy.zzz.www)
(Port= 1526))
(CONNECT_DATA = (SID = O920))
)
$HOME/network/admin/listener.ora
Processo Listener
Processo Server x Connessione Dedicata
$HOME/network/admin/sqlnet.ora
Tnsnames.ora
Oracle DB
Server 1
Listener.ora
TNS - TCP/IP
usr1 / pwd1
T1
connect system/manager;
grant create database link to usr1;
grant create synonym to usr1;
Oracle DB
Server 2
usr2 / pwd2
T2
Alias del tnsnames
della macchina
server 1
connect usr1/pwd1;
create database link dbms2 connect to usr2 identified by pwd2 using ‘alias2’;
create synonym S2 for T2@dbms2;
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.”
Case Study Data Distribution
Oracle
Offers
SnapShot Read Only
SnapShot WriteAble
Multi Master Site
SnapShot UpDateAble
Rows Filter
Where
Fields Filter Select
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
AS subquery;
Sempre applicabile - Nessun vincolo architetturale
REFRESH [ FAST | COMPLETE | FORCE ]
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
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
Trigger
After Row
For Each Row
Insert Update Delete
SnapShot View
SnapShot
Base Table
Master Table
Refresh Operation
SnapShot Log
View of
Changed
Trigger
After Row
For Each Row
Insert Update Delete
Trigger
After Row
For Each Row
Insert Update Delete
SnapShot View
SnapShot
Base Table
Master Table
Refresh Operation
SnapShot Log
View of
Changed
Log of
Change
SNP Background Processes
Job Schedulati
Job Ready
Job List
Job Ready List
Next?
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
Compatibile con l’intervallo di ri-programmazione del Job
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;
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.
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,
);
In conclusione citiamo le viste del dizionario dati che consentono un monitoring della
schedulazione:
USER_JOBS
DBA_JOBS
JOB
LOG_USER
PRIV_USER
SCHEMA_USER
LAST_DATE
LAST_SEC
THIS_DATE
THIS_SEC
NEXT_DATE
NEXT_SEC
TOTAL_TIME
BROKEN
INTERVAL
FAILURES
WHAT
CURRENT_SESSION_LABEL
CLEARANCE_HI
CLEARANCE_LO
NLS_ENV
MISC_ENV
NOT
NOT
NOT
NOT
NULL
NULL
NULL
NULL
NUMBER
VARCHAR2(30)
VARCHAR2(30)
VARCHAR2(30)
DATE
VARCHAR2(8)
DATE
VARCHAR2(8)
NOT NULL DATE
VARCHAR2(8)
NUMBER
VARCHAR2(1)
NOT NULL VARCHAR2(200)
NUMBER
VARCHAR2(2000)
RAW MLSLABEL
RAW MLSLABEL
RAW MLSLABEL
VARCHAR2(2000)
RAW(32)
DBA_JOBS_RUNNING
SID
JOB
FAILURES
LAST_DATE
LAST_SEC
THIS_DATE
THIS_SEC
NUMBER
NUMBER
NUMBER
DATE
VARCHAR2(8)
DATE
VARCHAR2(8)
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
Multithreaded Server
Dispatcher processes
Oracle server
code program
interface
Request queue
Shared
server
processes
Response queues
System Global Area
Oracle background processes
Client
Database server
Listener

MTS instance parameters:
mts_servers = 4
mts_dispatchers = “(PROTOCOL=ipc)(DISPATCHERS=4)”
mts_max_servers = 20
mts_max_dispatchers = 20
Oracle Standby
Implementation
Fail over Solution
Disaster Recovery Solution (if remote)
Ease of implementation
Minimum impact on Production System
Read Only Standby Database
Overview of Managed Oracle Standby DB
Primary Instance
ARCH
Standby Instance
T.N.S.
Redo log
Recovery proc
DBWR
Arc log
Arc log
RFS
Primary DB
Standby DB
Primary
control file
Standby
control file
1
Recovery Mode
2
ReadOnly Mode
3
Activate