Enabling distributed transactions
Sponsored Links
This presentation is the property of its rightful owner.
1 / 18

Enabling Distributed Transactions PowerPoint PPT Presentation


  • 136 Views
  • Uploaded on
  • Presentation posted in: General

Enabling Distributed Transactions. Objectives. After completing this lesson, you should be able to: Verify network configuration Explain the differences between remote transactions and distributed transactions Establishing communication between databases through database links. sqlnet.ora.

Download Presentation

Enabling Distributed Transactions

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Enabling Distributed Transactions


Objectives

  • After completing this lesson, you should be able to:

    • Verify network configuration

    • Explain the differences between remote transactions and distributed transactions

    • Establishing communication between databases through database links


sqlnet.ora

tnsnames.ora

listener.ora

Oracle Net Services Review

Application

Oracle Net

RDBMS

Oracle Net

Listener

TCP/IP network

Client

Database server


Connecting to an Oracle Database

finance.us.flowers.com

Database server


finance.us.flowers.com

Client Application Connectivity to the Oracle Server

flowers-server

Oracle Database

Network

Connection

sqlplus [email protected]


Detroit

Pittsburgh

Server-Server Connectivity

CREATE DATABASE LINKfin_flowers CONNECT TO hr IDENTIFIED BY hr USING 'FINANCE';

SELECT * [email protected]_flowers;

Oracle

Net

Physical Network Link

Oracle

Net


Types of Database Links

  • Database links have three dimensions:

    • Global, public, and private

    • Fixed user, connected user, current user

    • Shared or nonshared


Creating Database Links

3

2

1


Creating Database Links

  • Create a private link that uses the net service name ‘PGH_SALES’:

  • Create a public database link for the database service ORCL:

  • Use the private database link in a query:

CREATE DATABASE LINK hq.sales.acme.com

CONNECT TO hr IDENTIFIED BY hr

USING 'pgh_sales';

CREATE PUBLIC DATABASE LINK orcl USING 'ORCL';

SELECT * FROM

[email protected];


Dropping Links

  • Drop a link when the application no longer uses it

  • Redefine a link when:

    • Security breaches demand it

    • Physical databases must be moved

    • Network protocols change

    • Node names change

DROP [PUBLIC] DATABASE LINK linkname;


Data Dictionary Views

  • DBA_DB_LINKS

  • USER_DB_LINKS

  • ALL_DB_LINKS

  • V$DBLINK


Remote Query

  • The user is connected to a local database and a database link is used to query data from a single remote database.

  • The local database sends the entire query to the remote database to be processed.


Detroit

Pittsburgh

Executing a Remote Query

SELECT * [email protected]_flowers;

Oracle

Net

Physical Network Link

Oracle

Net


Distributed Query

  • The user is connected to a local database and issues a query involving tables from at least two databases.

  • The query is always executed from a local database.

  • The local database decomposes the query into subqueries to be sent to each remote database.

  • The local database retrieves data from remote databases and performs any necessary post-processing.


Detroit

Toronto

Pittsburgh

Executing a Distributed Query

SELECT e.last_name,j.job_title FROM [email protected] e, [email protected] j, job_history hWHERE e.job_id = j.job_id AND e.employee_id = h.employee_id AND h.end_date >= SYSDATE-30;

Local database

SELECT employee_id, end_date

FROM job_history;

SELECT last_name, job_id FROM employees;

SELECT job_id, job_title FROM jobs;


Distributed and Remote Transactions

INSERT INTO [email protected] VALUES

('CEO','Chief Executive Officer',18000,34000);

UPDATE [email protected]

SET job_id = 'CEO'

WHERE last_name = 'Hart';

COMMIT;


Detroit

Toronto

Pittsburgh

In-Doubt Transactions

1. UPDATE [email protected]..

2. UPDATE [email protected]..

3. Toronto database becomes unavailable.

4. Commit fails

UPDATE [email protected]..;

UPDATE [email protected]..;

COMMIT;

Local Database


Summary

  • In this lesson, you should have learned about:

    • Client-server and server-server architectures

    • Network configuration files and processes

    • Differences between remote queries and distributed queries

    • Establishing communication between databases through database links


  • Login