Replication

1
Replication

• The process of copying and maintaining database
  objects, such as tables, in multiple databases
  that make up a distributed database system.

2
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.

3
Multimaster Replication
4
Read-Only Snapshot Replication
5
Updateable Snapshot Replication
6
Hybrid Replication
7
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.

8
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.

9
Create Replicated Environment Process
10
Set Up Master Sites
11
Set Up Snapshot Sites
12
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.

13
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
14
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  
15
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
16
Calling Procedure/Function

• schema.package.function_name_at_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.

17
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.

18
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.

19
(No Transcript)
20
Cascading Triggers
21
Parts of a Trigger

• A triggering event or statement
• A trigger restriction
• A trigger action

22
An Example Trigger
23
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

24
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

25
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.

26
Execution Model for Triggers

• Execute all BEFORE statement triggers.
• Loop for each row affected by the SQL statement.
• Execute all BEFORE row triggers.
• Lock and change row, and perform integrity
  constraint checking. (The lock is not released
  until the transaction is committed.)
• Execute all AFTER row triggers that apply to the
  statement.
• Complete deferred integrity constraint checking.
• Execute all AFTER statement triggers that apply
  to the statement.