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

Enabling Distributed Transactions PowerPoint PPT Presentation


  • 119 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

Enabling Distributed Transactions


Objectives

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


Oracle net services review

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

Connecting to an Oracle Database

finance.us.flowers.com

Database server


Client application connectivity to the oracle server

finance.us.flowers.com

Client Application Connectivity to the Oracle Server

flowers-server

Oracle Database

Network

Connection

sqlplus [email protected]


Server server connectivity

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

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

Creating Database Links

3

2

1


Creating database links1

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

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

Data Dictionary Views

  • DBA_DB_LINKS

  • USER_DB_LINKS

  • ALL_DB_LINKS

  • V$DBLINK


Remote query

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.


Executing a remote query

Detroit

Pittsburgh

Executing a Remote Query

SELECT * [email protected]_flowers;

Oracle

Net

Physical Network Link

Oracle

Net


Distributed query

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.


Executing a distributed query

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

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;


In doubt transactions

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

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