Enabling Distributed Transactions - PowerPoint PPT Presentation

Enabling distributed transactions
Download
1 / 18

  • 145 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.

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.

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 hr/hr@FINANCE


Server server connectivity

Detroit

Pittsburgh

Server-Server Connectivity

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

SELECT * FROMemployees@fin_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

hr.employees@hq.sales.acme.com;


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 * FROMemployees@fin_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 employees@pittsburgh e, jobs@detroit 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 jobs@detroit VALUES

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

UPDATE employees@pittsburgh

SET job_id = 'CEO'

WHERE last_name = 'Hart';

COMMIT;


In doubt transactions

Detroit

Toronto

Pittsburgh

In-Doubt Transactions

1. UPDATE jobs@detroit...

2. UPDATE job_history@toronto...

3. Toronto database becomes unavailable.

4. Commit fails

UPDATE jobs@detroit...;

UPDATE job_history@toronto...;

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