Concepts related to distributed databases include distributed database architecture, database links, transaction processing, application development, and character set support.
A distributed database system allows applications to access data from local and remote databases. In a homogenous distributed database system , each database is an Oracle Database. In a heterogeneous distributed database system , at least one of the databases is not an Oracle Database. Distributed databases use a client/server architecture to process information requests.
A homogenous distributed database system includes only Oracle databases.
A homogenous distributed database system is a network of two or more Oracle Databases that reside on one or more systems.
Figure 31-1 illustrates a distributed system that connects three databases: hq , mfg , and sales . An application can simultaneously access or modify the data in several databases in a single distributed environment. For example, a single query from a Manufacturing client on local database mfg can retrieve joined data from the products table on the local database and the dept table on the remote hq database.
For a client application, the location and platform of the databases are transparent. You can also create synonyms for remote objects in the distributed system so that users can access them with the same syntax as local objects. For example, if you are connected to database mfg but want to access data on database hq , creating a synonym on mfg for the remote dept table enables you to issue this query:
SELECT * FROM dept;
In this way, a distributed system gives the appearance of native data access. Users on mfg do not have to know that the data they access resides on remote databases.
Figure 31-1 Homogeneous Distributed Database
Description of "Figure 31-1 Homogeneous Distributed Database"
An Oracle Database distributed database system can incorporate Oracle Databases of different releases. All supported releases of Oracle Database can participate in a distributed database system. Nevertheless, the applications that work with the distributed database must understand the functionality that is available at each node in the system. A distributed database application cannot expect an Oracle7 database to understand the SQL extensions that are only available with Oracle Database.
The terms distributed database and distributed processing are closely related, yet have distinct meanings.
There definitions are as follows:
Distributed database systems employ a distributed processing architecture. For example, an Oracle Database server acts as a client when it requests data that another Oracle Database server manages.
The terms distributed database system and database replication are related, yet distinct.
In a pure (that is, not replicated) distributed database, the system manages a single copy of all data and supporting database objects. Typically, distributed database applications use distributed transactions to access both local and remote data and modify the global database in real-time.
This book discusses only pure distributed databases.
The term replication refers to the operation of copying and maintaining database objects in multiple databases belonging to a distributed system. While replication relies on distributed database technology, database replication offers applications benefits that are not possible within a pure distributed database environment.
Most commonly, replication is used to improve local database performance and protect the availability of applications because alternate data access options exist. For example, an application may normally access a local database rather than a remote server to minimize network traffic and achieve maximum performance. Furthermore, the application can continue to function if the local server experiences a failure, but other servers with replicated data remain accessible.
A heterogeneous distributed database system includes both Oracle databases and non-Oracle databases.
In a heterogeneous distributed database system, at least one of the databases is a non-Oracle Database system. To the application, the heterogeneous distributed database system appears as a single, local, Oracle Database. The local Oracle Database server hides the distribution and heterogeneity of the data.
The Oracle Database server accesses the non-Oracle Database system using Oracle Heterogeneous Services with an agent . If you access the non-Oracle Database data store using an Oracle Transparent Gateway, then the agent is a system-specific application. For example, if you include a Sybase database in an Oracle Database distributed system, then you must obtain a Sybase-specific transparent gateway so that the Oracle Database in the system can communicate with it.
Alternatively, you can use generic connectivity to access non-Oracle Database data stores so long as the non-Oracle Database system supports the ODBC or OLE DB protocols.
Other than the introductory material presented in this chapter, this book does not discuss Oracle Heterogeneous Services. See Oracle Database Heterogeneous Connectivity User's Guide for more detailed information about Heterogeneous Services.
Heterogeneous Services (HS) is an integrated component within the Oracle Database server and the enabling technology for the current suite of Oracle Transparent Gateway products.
HS provides the common architecture and administration mechanisms for Oracle Database gateway products and other heterogeneous access facilities. Also, it provides upwardly compatible functionality for users of most of the earlier Oracle Transparent Gateway releases.
For each non-Oracle Database system that you access, Heterogeneous Services can use a transparent gateway agent to interface with the specified non-Oracle Database system. The agent is specific to the non-Oracle Database system, so each type of system requires a different agent.
The transparent gateway agent facilitates communication between Oracle Database and non-Oracle Database systems and uses the Heterogeneous Services component in the Oracle Database server. The agent executes SQL and transactional requests at the non-Oracle Database system on behalf of the Oracle Database server.
Your Oracle-supplied gateway-specific documentation for information about transparent gateways
Generic connectivity enables you to connect to non-Oracle Database data stores by using either a Heterogeneous Services ODBC agent or a Heterogeneous Services OLE DB agent.
Both are included with your Oracle product as a standard feature. Any data source compatible with the ODBC or OLE DB standards can be accessed using a generic connectivity agent.
The advantage to generic connectivity is that it may not be required for you to purchase and configure a separate system-specific agent. You use an ODBC or OLE DB driver that can interface with the agent. However, some data access features are only available with transparent gateway agents.
A database server is the Oracle software managing a database, and a client is an application that requests information from a server. Each computer in a network is a node that can host one or more databases. Each node in a distributed database system can act as a client, a server, or both, depending on the situation.
In Figure 31-2, the host for the hq database is acting as a database server when a statement is issued against its local data (for example, the second statement in each transaction issues a statement against the local dept table), but is acting as a client when it issues a statement against remote data (for example, the first statement in each transaction is issued against the remote table emp in the sales database).
Figure 31-2 An Oracle Database Distributed Database System
A client can connect directly or indirectly to a database server. A direct connection occurs when a client connects to a server and accesses information from a database contained on that server. For example, if you connect to the hq database and access the dept table on this database as in Figure 31-2, you can issue the following:
SELECT * FROM dept;
This query is direct because you are not accessing an object on a remote database.
In contrast, an indirect connection occurs when a client connects to a server and then accesses information contained in a database on a different server. For example, if you connect to the hq database but access the emp table on the remote sales database as in Figure 31-2, you can issue the following:
SELECT * FROM emp@sales;
This query is indirect because the object you are accessing is not on the database to which you are directly connected.
The central concept in distributed database systems is a database link . A database link is a connection between two physical database servers that allows a client to access them as one logical database.
A database link is a pointer that defines a one-way communication path from an Oracle Database server to another database server.
For public and private database links, the link pointer is actually defined as an entry in a data dictionary table. To access the link, you must be connected to the local database that contains the data dictionary entry. For global database links, the link pointer is defined in a directory service. The different types of database links are described in more detail in "Types of Database Links" .
A database link connection is one-way in the sense that a client connected to local database A can use a link stored in database A to access information in remote database B, but users connected to database B cannot use the same link to access data in database A. If local users on database B want to access data on database A, then they must define a link that is stored in the data dictionary of database B.
A database link connection allows local users to access data on a remote database. For this connection to occur, each database in the distributed system must have a unique global database name in the network domain. The global database name uniquely identifies a database server in a distributed system.
Figure 31-3 shows an example of user scott accessing the emp table on the remote database with the global name hq.example.com :
Figure 31-3 Database Link
Description of "Figure 31-3 Database Link"
Database links are either private or public. If they are private, then only the user who created the link has access; if they are public, then all database users have access.
One principal difference among database links is the way that different link definitions determine how the link connection is authenticated. Users access a remote database through the following types of links:
Connected user link
Users connect as themselves, which means that they must have an account on the remote database with the same user name and password as their account on the local database.
Fixed user link
Users connect using the user name and password referenced in the link. For example, if Jane uses a fixed user link that connects to the hq database with the user name and password scott/ password, then she connects as scott , Jane has all the privileges in hq granted to scott directly, and all the default roles that scott has been granted in the hq database.
Current user link
A user connects as a global user. A local user can connect as a global user in the context of a stored procedure, without storing the global user's password in a link definition. For example, Jane can access a procedure that Scott wrote, accessing Scott's account and Scott's schema on the hq database.
Create database links using the CREATE DATABASE LINK statement. After a link is created, you can use it to specify schema objects in SQL statements.
Oracle Database SQL Language Reference for syntax of the CREATE DATABASE statement
Parent topic: Database Links
A shared database link is a link between a local server process and the remote database. The link is shared because multiple client processes can use the same link simultaneously.
When a local database is connected to a remote database through a database link, either database can run in dedicated or shared server mode. The following table illustrates the possibilities:
A shared database link can exist in any of these four configurations. Shared links differ from standard database links in the following ways:
Parent topic: Database Links
The great advantage of database links is that they allow users to access another user's objects in a remote database so that they are bounded by the privilege set of the object owner. In other words, a local user can access a link to a remote database without having to be a user on the remote database.
For example, assume that employees submit expense reports to Accounts Payable (A/P), and further suppose that a user using an A/P application must retrieve information about employees from the hq database. The A/P users should be able to connect to the hq database and execute a stored procedure in the remote hq database that retrieves the desired information. The A/P users should not need to be hq database users to do their jobs; they should only be able to access hq information in a controlled way as limited by the procedure.
Parent topic: Database Links
To understand how a database link works, you must first understand what a global database name is. Each database in a distributed database is uniquely identified by its global database name.
The database forms a global database name by prefixing the database network domain, specified by the DB_DOMAIN initialization parameter at database creation, with the individual database name, specified by the DB_NAME initialization parameter.
For example, Figure 31-4 illustrates a representative hierarchical arrangement of databases throughout a network.
Figure 31-4 Hierarchical Arrangement of Networked Databases
Description of "Figure 31-4 Hierarchical Arrangement of Networked Databases"
The name of a database is formed by starting at the leaf of the tree and following a path to the root. For example, the mfg database is in division3 of the example_tools branch of the com domain. The global database name for mfg is created by concatenating the nodes in the tree as follows:
While several databases can share an individual name, each database must have a unique global database name. For example, the network domains us.americas.example_auto.com and uk.europe.example_auto.com each contain a sales database. The global database naming system distinguishes the sales database in the americas division from the sales database in the europe division as follows:
See Also: "Managing Global Names in a Distributed System" to learn how to specify and change global database names
Parent topic: Database Links
You can use the global name of a database as a loopback database link without explicitly creating a database link. When the database link in a SQL statement matches the global name of the current database, the database link is effectively ignored.
For example, assume the global name of a database is db1.example.com . You can run the following SQL statement on this database:
SELECT * FROM hr.employees@db1.example.com;
In this case, the @db1.example.com portion of the SQL statement is effectively ignored.
Parent topic: Database Links
Typically, a database link has the same name as the global database name of the remote database that it references.
For example, if the global database name of a database is sales.us.example.com , then the database link is also called sales.us.example.com .
When you set the initialization parameter GLOBAL_NAMES to TRUE , the database ensures that the name of the database link is the same as the global database name of the remote database. For example, if the global database name for hq is hq.example.com , and GLOBAL_NAMES is TRUE , then the link name must be called hq.example.com . Note that the database checks the domain part of the global database name as stored in the data dictionary, not the DB_DOMAIN setting in the initialization parameter file (see "Changing the Domain in a Global Database Name" ).
If you set the initialization parameter GLOBAL_NAMES to FALSE , then you are not required to use global naming. You can then name the database link whatever you want. For example, you can name a database link to hq.example.com as foo .
Oracle recommends that you use global naming because many useful features require global naming.
After you have enabled global naming, database links are essentially transparent to users of a distributed database because the name of a database link is the same as the global name of the database to which the link points. For example, the following statement creates a database link in the local database to remote database sales :
CREATE PUBLIC DATABASE LINK sales.division3.example.com USING 'sales1';
Oracle Database Reference for more information about specifying the initialization parameter GLOBAL_NAMES
Parent topic: Database Links
Oracle Database lets you create private , public , and global database links.
These basic link types differ according to which users are allowed access to the remote database:
User who created the link. View ownership data through:
Creates link in a specific schema of the local database. Only the owner of a private database link or PL/SQL subprograms in the schema can use this link to access database objects in the corresponding remote database.
User called PUBLIC. View ownership data through views shown for private database links.
Creates a database-wide link. All users and PL/SQL subprograms in the database can use the link to access database objects in the corresponding remote database.
No user owns the global database link. The global database link exists in a directory service.
Creates a network-wide link. When an Oracle network uses a directory server and the database is registered in the directory service, this information can be used as a database link. Users and PL/SQL subprograms in any database can use a global database link to access objects in the corresponding remote database. Global database links refer to the use of net service names from the directory server.
Determining the type of database links to employ in a distributed database depends on the specific requirements of the applications using the system. Consider these features when making your choice:
Private database link
This link is more secure than a public or global link, because only the owner of the private link, or subprograms within the same schema, can use the link to access the remote database.
Public database link
When many users require an access path to a remote Oracle Database, you can create a single public database link for all users in a database.
Global database link
When an Oracle network uses a directory server, an administrator can conveniently manage global database links for all databases in the system. Database link management is centralized and simple.
There is no user data associated with a global database link definition. A global database link must operate as a connected user database link.
Parent topic: Database Links
Users of database links include connect user, current user, and fixed user.
Parent topic: Database Links
When creating the link, you determine which user should connect to the remote database to access the data.
The following table explains the differences among the categories of users involved in database links:
A local user accessing a database link in which no fixed username and password have been specified. If SYSTEM accesses a public link in a query, then the connected user is SYSTEM , and the database connects to the SYSTEM schema in the remote database.
Note: A connected user does not have to be the user who created the link, but is any user who is accessing the link.
CREATE PUBLIC DATABASE LINK hq USING 'hq';
A global user in a CURRENT_USER database link. The global user must be authenticated by an X.509 certificate (an SSL-authenticated enterprise user) or a password (a password-authenticated enterprise user), and be a user on both databases involved in the link.
CREATE PUBLIC DATABASE LINK hq CONNECT TO CURRENT_USER using 'hq';
A user whose username/password is part of the link definition. If a link includes a fixed user, the fixed user's username and password are used to connect to the remote database.
CREATE PUBLIC DATABASE LINK hq CONNECT TO jane IDENTIFIED BY password USING 'hq';
The following users cannot be target users of database links: SYS and PUBLIC .
"Specifying Link Users" to learn how to specify users when creating links
Connected user links have no connect string associated with them. The advantage of a connected user link is that a user referencing the link connects to the remote database as the same user, and credentials do not have to be stored in the link definition in the data dictionary.
Connected user links have some disadvantages. Because these links require users to have accounts and privileges on the remote databases to which they are attempting to connect, they require more privilege administration for administrators. Also, giving users more privileges than they need violates the fundamental security concept of least privilege: users should only be given the privileges they need to perform their jobs.
The ability to use a connected user database link depends on several factors, chief among them whether the user is authenticated by the database using a password, or externally authenticated by the operating system or a network authentication service. If the user is externally authenticated, then the ability to use a connected user link also depends on whether the remote database accepts remote authentication of users, which is set by the REMOTE_OS_AUTHENT initialization parameter.
The REMOTE_OS_AUTHENT parameter operates as follows:
TRUE for the remote database
An externally-authenticated user can connect to the remote database using a connected user database link.
FALSE for the remote database
An externally-authenticated user cannot connect to the remote database using a connected user database link unless a secure protocol or a network authentication service option is used.
If the connected user database link is accessed from within a definer's rights function, procedure, or package, then the definer's authorization ID is used to connect as a remote user. For example, if user jane calls procedure scott.p (a definer's rights procedure created by scott ), and the link appears inside procedure scott.p , then scott is the connected user. To run a definer's rights function, procedure, or package that includes a connected user database link, the user who invokes the function, procedure, or package must be granted the INHERIT REMOTE PRIVILEGES privilege.
The REMOTE_OS_AUTHENT initialization parameter is deprecated. It is retained for backward compatibility only.
A benefit of a fixed user link is that it connects a user in a primary database to a remote database with the security context of the user specified in the connect string.
For example, local user joe can create a public database link in joe 's schema that specifies the fixed user scott with password password . If jane uses the fixed user link in a query, then jane is the user on the local database, but she connects to the remote database as scott/ password .
Fixed user links have a user name and password associated with the connect string. The user name and password are stored with other link information in data dictionary tables.
Current user database links make use of a global user. A global user must be authenticated by an X.509 certificate or a password, and be a user on both databases involved in the link.
The user invoking the CURRENT_USER link does not have to be a global user. For example, if jane is authenticated (not as a global user) by password to the Accounts Payable database, she can access a stored procedure to retrieve data from the hq database. The procedure uses a current user database link, which connects her to hq as global user scott . User scott is a global user and authenticated through a certificate over SSL, but jane is not.
Note that current user database links have these consequences:
Create database links using the CREATE DATABASE LINK statement.
The table gives examples of SQL statements that create database links in a local database to the remote sales.us.americas.example_auto.com database:
CREATE DATABASE LINK sales.us.americas.example_auto.com USING 'sales_us';
sales using net service name sales_us
Private connected user
CREATE DATABASE LINK foo CONNECT TO CURRENT_USER USING 'am_sls';
sales using service name am_sls
Current global user
Private current user
CREATE DATABASE LINK sales.us.americas.example_auto.com CONNECT TO scott IDENTIFIED BY password USING 'sales_us';
sales using net service name sales_us
scott using password password
Private fixed user
CREATE PUBLIC DATABASE LINK sales CONNECT TO scott IDENTIFIED BY password USING 'rev';
sales using net service name rev
scott using password password
Public fixed user
CREATE SHARED PUBLIC DATABASE LINK sales.us.americas.example_auto.com CONNECT TO scott IDENTIFIED BY password AUTHENTICATED BY anupam IDENTIFIED BY password1 USING 'sales';
sales using net service name sales
scott using password password , authenticated as anupam using password password1
Shared public fixed user
Parent topic: Database Links
After you have created a database link, you can execute SQL statements that access objects on the remote database. You must also be authorized in the remote database to access specific remote objects.
For example, to access remote object emp using database link foo , you can issue:
SELECT * FROM emp@foo;
Constructing properly formed object names using database links is an essential aspect of data manipulation in distributed systems.
If you call a procedure using a remote database link, and the procedure contains a ROLLBACK command, only DML operations performed at the remote site are rolled back. Any DML changes made at the originating site are not rolled back.
Parent topic: Database Links
Oracle Database uses the global database name to name the schema objects globally.
Global database names are in the following form:
For example, using a database link to database sales.division3.example.com , a user or application can reference remote data as follows:
SELECT * FROM scott.emp@sales.division3.example.com; # emp table in scott's schema SELECT loc FROM scott.dept@sales.division3.example.com;
If GLOBAL_NAMES is set to FALSE , then you can use any name for the link to sales.division3.example.com . For example, you can call the link foo . Then, you can access the remote database as follows:
SELECT name FROM scott.emp@foo; # link name different from global name
To access a remote schema object, you must be granted access to the remote object in the remote database.
Further, to perform any updates, inserts, or deletes on the remote object, you must be granted the READ or SELECT privilege on the object, along with the UPDATE , INSERT , or DELETE privilege. Unlike when accessing a local object, the READ or SELECT privilege is necessary for accessing a remote object because the database has no remote describe capability. The database must do a SELECT * on the remote object to determine its structure.
Oracle Database lets you create synonyms so that you can hide the database link name from the user.
A synonym allows access to a table on a remote database using the same syntax that you would use to access a table on a local database. For example, assume you issue the following query against a table in a remote database:
SELECT * FROM emp@hq.example.com;
You can create the synonym emp for emp@hq.example.com so that you can issue the following query instead to access the same data:
SELECT * FROM emp;
"Using Synonyms to Create Location Transparency" to learn how to create synonyms for objects specified using database links
To resolve application references to schema objects (a process called name resolution ), the database forms object names hierarchically.
For example, the database guarantees that each schema within a database has a unique name, and that within a schema each object has a unique name. As a result, a schema object name is always unique within the database. Furthermore, the database resolves application references to the local name of the object.
In a distributed database, a schema object such as a table is accessible to all applications in the system. The database extends the hierarchical naming model with global database names to effectively create global object names and resolve references to the schema objects in a distributed database system. For example, a query can reference a remote table by specifying its fully qualified name, including the database in which it resides.
For example, assume that you connect to the local database as user SYSTEM :
CONNECT SYSTEM@sales1
You then issue the following statements using database link hq.example.com to access objects in the scott and jane schemas on remote database hq :
SELECT * FROM scott.emp@hq.example.com; INSERT INTO jane.accounts@hq.example.com (acc_no, acc_name, balance) VALUES (5001, 'BOWER', 2000); UPDATE jane.accounts@hq.example.com SET balance = balance + 500; DELETE FROM jane.accounts@hq.example.com WHERE acc_name = 'BOWER';
