1 / 18

Enabling Distributed Transactions

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.

amil
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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Enabling Distributed Transactions

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

  3. sqlnet.ora tnsnames.ora listener.ora Oracle Net Services Review Application Oracle Net RDBMS Oracle Net Listener TCP/IP network Client Database server

  4. Connecting to an Oracle Database finance.us.flowers.com Database server

  5. finance.us.flowers.com Client Application Connectivity to the Oracle Server flowers-server Oracle Database Network Connection sqlplus hr/hr@FINANCE

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

  7. Types of Database Links • Database links have three dimensions: • Global, public, and private • Fixed user, connected user, current user • Shared or nonshared

  8. Creating Database Links 3 2 1

  9. 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;

  10. 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;

  11. Data Dictionary Views • DBA_DB_LINKS • USER_DB_LINKS • ALL_DB_LINKS • V$DBLINK

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

  13. Detroit Pittsburgh Executing a Remote Query SELECT * FROMemployees@fin_flowers; Oracle Net Physical Network Link Oracle Net

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

  15. 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;

  16. 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;

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

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

More Related