Docstoc

replication

Document Sample
replication Powered By Docstoc
					                      Replication
   The process of copying and maintaining database
    objects, such as tables, in multiple databases that
    make up a distributed database system.




                                       Dr. Ouyang, CS 643, Spring 2001
                 Reasons for Replication
   Availability – it provides application with alternative data
    access options. If one site becomes unavailable, users can
    continue to query or even update the remaining locations.
   Performance – users can access data from the replication site
    that has the lowest access cost.
   Disconnected Computing – snapshots enable users to work on a
    subset of a database while disconnected from the central
    database server.
   Network Load Reduction – applications can access various
    regional servers instead of accessing one central server.
   Mass Deployment – deployment templates enable you to create
    multiple snapshot environments quickly.
                                              Dr. Ouyang, CS 643, Spring 2001
Multimaster Replication




                    Dr. Ouyang, CS 643, Spring 2001
Read-Only Snapshot Replication




                       Dr. Ouyang, CS 643, Spring 2001
Updateable Snapshot Replication




                        Dr. Ouyang, CS 643, Spring 2001
Hybrid Replication




                 Dr. Ouyang, CS 643, Spring 2001
            Preparation for Replication
In your initialization parameter file,
   Ensure that GLOBAL_NAMES is set to TRUE in
    your initialization parameter file.
   Ensure that you have allocated enough job processes
    at each master site.




                                          Dr. Ouyang, CS 643, Spring 2001
         Replication Management API
   A command line alternative to Replication Manager.
   A set of PL/SQL packages that encapsulate
    procedures and functions that you can use to
    configure an Oracle replication environment.




                                     Dr. Ouyang, CS 643, Spring 2001
    Create
    Replicated
    Environment
    Process




Dr. Ouyang, CS 643, Spring 2001
Set Up Master Sites




   Dr. Ouyang, CS 643, Spring 2001
Set Up
Snapshot
Sites




           Dr. Ouyang, CS 643, Spring 2001
           Stored Procedure/Function
A PL/SQL unit that:
   Has a name.
   Can take parameters, and can return values.
   Is stored in the data dictionary.
   Can be called by many users.




                                        Dr. Ouyang, CS 643, Spring 2001
                An Example Procedure
CREATE TABLE Emp_tab
       ( Empno NUMBER(4) NOT NULL,
       Ename VARCHAR2(10), Job VARCHAR2(9),
       Mgr NUMBER(4), Hiredate DATE, Sal NUMBER(7,2),
       Comm NUMBER(7,2), Deptno NUMBER(2));


CREATE OR REPLACE PROCEDURE fire_emp1(Emp_id NUMBER) AS
BEGIN
       DELETE FROM Emp_tab WHERE Empno = Emp_id;
END;


VARIABLE Empnum NUMBER;


                                           Dr. Ouyang, CS 643, Spring 2001
             An Example Function

CREATE TABLE Tax_table
       ( Ss_no NUMBER,
       Sal NUMBER);


CREATE OR REPLACE FUNCTION tax_rate (ssn IN NUMBER,
       salary IN NUMBER) RETURN NUMBER AS
       sal_out NUMBER;
BEGIN
       sal_out := salary * 1.1;
END;




                                    Dr. Ouyang, CS 643, Spring 2001
   Using Default Values in Parameter

CREATE OR REPLACE FUNCTION Gross_pay (Emp_id IN
    NUMBER, St_hrs IN NUMBER DEFAULT 40, Ot_hrs IN
    NUMBER DEFAULT 0) RETURN NUMBER AS
     St_rate NUMBER;
     Ot_rate NUMBER;
BEGIN
     SELECT Srate, Orate INTO St_rate, Ot_rate FROM Payroll
         WHERE Acctno = Emp_id;
     RETURN St_hrs * St_rate + Ot_hrs * Ot_rate;
END Gross_pay;




                                        Dr. Ouyang, CS 643, Spring 2001
            Calling Procedure/Function
[[schema.]package.]function_name[@dblink][(param_1...param_n)]


   A procedure can be called within the body of another
    procedure or a trigger.
   A procedure can be interactively called by a user
    using an Oracle tool.
   A procedure can be explicitly called within an
    application.
   A stored function can be called from a SQL
    statement.

                                         Dr. Ouyang, CS 643, Spring 2001
                       Triggers
   Procedures that execute implicitly when an INSERT,
    UPDATE, or DELETE statement is issued against
    the associated table or, in some cases, against a view,
    or when database system actions occur.




                                        Dr. Ouyang, CS 643, Spring 2001
            Guidelines in Using Triggers
   Use triggers to guarantee that when a specific operation is
    performed, related actions are performed.
   Do not define triggers that duplicate the functionality already
    built into Oracle.
   Limit the size of triggers. Use procedure when needed.
   Do not create recursive triggers.
   Use triggers on DATABASE judiciously. They are executed for
    every user every time the event occurs on which the trigger is
    created.




                                              Dr. Ouyang, CS 643, Spring 2001
Dr. Ouyang, CS 643, Spring 2001
 Cascading
 Triggers




Dr. Ouyang, CS 643, Spring 2001
                 Parts of a Trigger
   A triggering event or statement
   A trigger restriction
   A trigger action




                                      Dr. Ouyang, CS 643, Spring 2001
An Example Trigger




                 Dr. Ouyang, CS 643, Spring 2001
             Events that Fire Triggers
   An INSERT, UPDATE, or DELETE statement on a
    specific table (or view, in some cases)
   A CREATE, ALTER, or DROP statement on any
    schema object
   A database startup or instance shutdown
   A specific error message or any error message
   A user logon or logoff




                                        Dr. Ouyang, CS 643, Spring 2001
                 Types of Triggers
   Row triggers – once for every row affected by the
    triggering statement, such as a trigger fired by an
    UPDATE statement that updates many rows
   Statement triggers – once for the triggering
    statement, no matter how many rows it affects




                                       Dr. Ouyang, CS 643, Spring 2001
                Timing of Triggers
   Whether the trigger action is to be executed
    BEFORE or AFTER the triggering statement.
   BEFORE and AFTER triggers fired by DDL
    statements can be defined only on the database or a
    schema, not on particular tables.
   BEFORE and AFTER triggers fired by DML
    statements can be defined only on tables, not on
    views.



                                        Dr. Ouyang, CS 643, Spring 2001
             Execution Model for Triggers
1.   Execute all BEFORE statement triggers.
2.   Loop for each row affected by the SQL statement.
      a.   Execute all BEFORE row triggers.
      b. Lock and change row, and perform integrity constraint
           checking. (The lock is not released until the transaction is
           committed.)
      c.   Execute all AFTER row triggers that apply to the
           statement.
3.   Complete deferred integrity constraint checking.
4.   Execute all AFTER statement triggers that apply to the
     statement.

                                                Dr. Ouyang, CS 643, Spring 2001

				
DOCUMENT INFO