|Oracle7 Server Distributed Systems Manual, Vol. 1||
There are also operating system-specific issues the DBA must consider when planning to distribute databases on certain platforms. See your Oracle operating system-specific documentation.
Attention: Some of the issues and tasks listed in this section are specific to the SunOS platform, and are described here as a sample list of things to consider when implementing a distributed system.
For example, an online transaction processing (OLTP) database, such as a bank automated teller machine (ATM) has a high volume of transactions. Therefore when planning an OLTP database, you may need to distribute I/O across multiple disks and controllers. You usually need to split the logical database design.
A decision support database, such as an inventory system, has a relatively low number of database updates (measured in transactions per hour). Also, users tend to make few queries and may look at results of these queries for many minutes at a time. Thus a decision support database has less need to distribute I/O across multiple disks and controllers.
When planning the logical layout of your database, consider how tables should be split up among tablespaces. A larger number of smaller tablespaces tend to be more flexible than a few catchall tablespaces.
Keep in mind that different sites have different needs, and decisions should be based on a particular site's needs for a specific application.
The initialization file, INIT.ORA, contains certain default parameter values, which the DBA may need to increase for optimal performance. Many of theses values are operating system-specific and depend on multiple factors affecting the entire distributed system. See your Oracle operating system-specific documentation for information about your specific requirements.
Relinking relies on operating-system facilities that must be installed and usable before you can perform relinking successfully. For example, on the SunOS system, the commands, make and ar must be present, and the system libraries must be available.
Most sites must relink during installation. For example, you must relink when installing the distributed option or any Oracle (SQL*Net) protocol adapters.
For example, assume that table EMP is stored in a local database. Another table, DEPT, is stored in a remote database. To make the location of, and relationship between, these tables transparent to users of the system, a view named COMPANY can be created in the local database that joins the data of the local and remote servers:
CREATE VIEW company AS
SELECT empno, ename, dname
FROM scott.emp a, firstname.lastname@example.org b
WHERE a.deptno = b.deptno;
When users access this view, they do not know, or need to know, where the data is physically stored, or if data from more than one table is being accessed. Thus, it is easier for them to get required information. For example:
SELECT * FROM company;
provides data from both the local and remote database table.
Figure 4 - 1 illustrates this example of location transparency.
Figure 4 - 1. Views and Location Transparency
Database synonyms are a standard SQL feature that provide alternate names for database objects and, optionally, their locations. A synonym can be created for any table, view, snapshot, sequence, procedure, function, or package. All synonyms are stored in the data dictionary of the database in which they are created. To simplify remote table access through database links, a synonym can allow single-word access to remote data, isolating the specific object name and the location from users of the synonym. The syntax to create a synonym is:
CREATE [PUBLIC] SYNONYM_name FOR [schema.]object_name[@database_link_name]
Specifies that this synonym is available to all users. Omitting this parameter makes a synonym private, and usable only by the creator. Public synonyms can be created only by a user with CREATE PUBLIC SYNONYM system privilege.
Specifies the alternate object name to be referenced by users and applications.
Specifies the schema of the object specified in object_name. Omitting this parameter uses the creator's schema as the schema of the object.
Specifies either a table, view, sequence, or other name as appropriate.
Specifies the database link which identifies the remote username in which the object specified in object_name is located.
A synonym must be a uniquely named object for its schema. If a schema contains a database object and a public synonym exists with the same name, Oracle always finds the database object when the user that owns the schema references that name.
Because a synonym is a reference to the actual object, the security domain of the object is used when the synonym is accessed. For example, a user that has access to a synonym for a specific table must also have privileges on that table to access the data in it. If the user attempts to access the synonym, but does not have privileges on the table it identifies, an error occurs indicating that the table or view does not exist.
CREATE PUBLIC SYNONYM emp FOR email@example.com;
An employee management application can be designed without regard to where the application is used because the location of the EMP table is hidden by the public synonyms. SQL statements in the application access the table SCOTT.EMP@HQ.ACME.COM by referencing the public synonym EMP.
Furthermore, if the EMP table is moved from the HQ database to the HR database, only the public synonyms need to be changed on the nodes of the system. The employee management application continues to function properly on all nodes.
CREATE PUBLIC DATABASE LINK NY_FIN CONNECT TO REAL_ESTATE IDENTIFIED BY NOPASS; USING 'NY_FIN' CREATE PUBLIC SYNONYM FOR_SALE FOR OPEN@NY_FIN;
Figure 4 - 2. Using Synonyms for Alternate Object Names
The table OPEN on NY_FIN could be accessed from OHIO using the SQL statement:
SELECT * FROM FOR_SALE;
Using this database link, the user is logging on to NY_FIN as user REAL_ESTATE. Notice that this public synonym was created by the DBA on behalf of the REAL_ESTATE username. Without such a prefix, a table that does not exist in the database link user's schema would return an error, because it would be looking for the OPEN table owned by the REAL_ESTATE user.
CREATE PUBLIC DATABASE LINK NY_TAX CONNECT TO REAL_ESTATE IDENTIFIED BY NOPASS; DROP PUBLIC SYNONYM FOR_SALE;
CREATE PUBLIC SYNONYM FOR_SALE FOR OPEN@NY_TAX;
Figure 4 - 3. Redefining Table Location to Retain Location Transparency
To relocate the table, a second database link was created called NY_TAX that connected to a new database with the service name NY_TAX, and the synonym was recreated to reference the NY_TAX database link instead of the NY_FIN database link. Other tables that were accessed through the NY_FIN database link to NY_FIN would continue to function properly.
Oracle7 provides two mechanisms for accomplishing transparent table replication in a distributed system. The basic replication that comes with the distributed option provides asynchronous table replication through snapshots (changes to an updatable master table are replicated on the read-only or updatable snapshot tables only at timed intervals). Alternatively the advanced replication option allows you to implement synchronous table replication (changes to any table are applied to all replicated copies immediately) through the Oracle Symmetric Replication Facility. In either case, table replication is transparent to users making changes to replicated tables.
See Oracle7 Server Distributed Systems, Volume II for detailed information about all forms of replication.
CREATE PROCEDURE fire_emp (enum NUMBER) AS
DELETE FROM firstname.lastname@example.org
WHERE empno = enum;
When a user or application calls the FIRE_EMP procedure, it is not apparent that a remote table is being modified.
A second layer of location transparency is possible if the statements in a procedure indirectly reference remote data using local procedures, views, or synonyms. For example, the following statement defines a local synonym:
CREATE SYNONYM emp FOR email@example.com;
Consequently, the FIRE_EMP procedure can be defined with the following statement:
CREATE PROCEDURE fire_emp (enum NUMBER) AS
DELETE FROM emp WHERE empno = enum;
If the table JWARD.EMP@HQ is renamed or moved, only the local synonym that references the table needs to be modified. None of the procedures and applications that call the procedure require modification.
SELECT empno, ename, dname
FROM firstname.lastname@example.org a,
WHERE a.deptno = b.deptno;
UPDATE, INSERT, DELETE, and LOCK TABLE statements can reference both local and remote tables. No programming is necessary to update remote data. For example, the following statement inserts new rows into the remote table SCOTT.EMP in the SALES database by selecting rows from the JWARD.EMP table in the local database:
INSERT INTO email@example.com
SELECT * FROM jward.emp;
If a transaction is a single-site transaction, it simply commits or rolls back. If a transaction is distributed, Oracle's distributed transaction management mechanism is automatically used to commit it. Oracle's recovery management mechanism guarantees that the nodes referenced in a distributed transaction either all commit or all roll back the transaction, even if a network failure occurs while the transaction is being committed. For detailed information about Oracle's distributed transaction management mechanism, see page 5 - 4.
The RECO background process automatically recovers pending or in-doubt distributed transactions (transactions not committed or rolled back when a network failure occurs during a two-phase commit process). When a network failure is corrected, the RECO processes of the involved database servers automatically resolve the outcome of any pending distributed transactions. No work is required of the database administrator to resolve pending transactions, although the database administrator may force resolution of in-doubt transactions if he cannot wait for the network failure to be corrected. For information about failure resolution of in-doubt distributed transactions in an Oracle distributed system, see the Oracle7 Server Concepts manual.
CREATE SYNONYM emp FOR firstname.lastname@example.org;
SELECT * FROM email@example.com;
Oracle does not check, nor enforce, unique global object names when an object is created. Oracle does not store complete global object names in the distributed data dictionaries. However, Oracle does guarantee that an object name is unique within its own local database. Additionally, a distributed system can be configured so that each database within the system has a unique database name, thereby providing unique global object names.
Statement issued at remote database:
GRANT SELECT, DELETE ON scott.emp TO user1;
Statements issued at local database:
CREATE DATABASE LINK hr.acme.com
CONNECT TO user1 IDENTIFIED BY password
CREATE VIEW admin.emp_view AS
SELECT * FROM firstname.lastname@example.org;
CREATE PROCEDURE admin.fire_emp (enum NUMBER) AS
DELETE FROM email@example.com
WHERE empno = enum;
CREATE SYNONYM admin.emp_syn FOR firstname.lastname@example.org;
Views are a good choice for location transparency if unlimited local object privilege management is a requirement. For example, assume the local security administrator needs to selectively grant object privileges for several remote tables. The remote administrator can create a powerful user account that is granted many privileges for many remote tables. Then, the local administrator can create a private database link that connects to the powerful remote account and, in the same schema, create views to "mirror" the remote tables. The local administrator controls local privilege management for the remote tables by granting privileges on the local views. Also note that in this example, many users can use a private database link.
In general, procedures aid in security. Users who call a procedure can only perform the controlled operations of the procedure. Privileges for objects referenced within a procedure do not need to be explicitly granted to the calling users. Much like views, procedures are a good choice for location transparency if unlimited local privilege management is a requirement.
For example, assume the local security administrator needs to selectively allow users to query and update several remote tables. The remote administrator can create a powerful user account that grants many object privileges. Then, the local administrator can create a private database link that connects to the powerful remote account and, in the same schema, create procedures to query and modify the remote tables, as desired. The local administrator can control how local users can access the remote tables by selectively granting the EXECUTE privilege for the local procedures, thus controlling local privilege management for remote objects.
Unlike a database link referenced in a view or procedure definition, a database link referenced in a synonym is resolved by first looking for a private link owned by the schema in effect at the time the reference to the synonym is parsed. Therefore, to ensure the desired object resolution, it is especially important to specify the underlying object's schema in the definition of a synonym.
This section discusses how public and private links are created. For information on how global database links are defined, see "Creating Global Database Links in Network Manager" .
The syntax for creating public and private links is:
CREATE [PUBLIC] DATABASE LINK linkname [CONNECT TO username IDENTIFIED BY password] USING 'service_name'
In this syntax:
Specifies a database link available to all users with the CREATE SESSION privilege. If the PUBLIC option is omitted, a private link available only to the creator is created. Note that creating a public database link requires CREATE PUBLIC DATABASE LINK privilege.
Specifies the name of the database link. If the remote server is in the local server's domain, the link name does not need to include the domain name. However, if the server is in another domain, the link name must include the domain. (The domain is determined by DB_DOMAIN in the initialization parameter file).
Optionally specifies a single username and password for all users of the database link to share. If the clause is omitted, the Oracle username and password of the user account using the database link will connect to the remote database server.
Specifies a valid Oracle username on the remote database server.
Specifies the corresponding password of the username on the remote database server.
Specifies the service name defined in the TNSNAMES.ORA file or stored in Oracle Names associated with the connect descriptor for the desired database. If the remote server is in the local server's default domain, the service name does not need to include the domain name. However, if the server is in another domain, the service name must include the domain. (The default domain is determined by a parameter in the server's SQLNET.ORA file.
Before Oracle7, a database administrator could specify any linkname for a database link. However, with Oracle7 and later releases, a database link must have the same name as the global database name of the database. Remember that the service name is also the same as the global database name. Therefore, the linkname and service name are now the same. Although this may seem to make the USING clause redundant, it is still a necessary part of the syntax.
For example, the command for creating a public database link to a database that has the global database name ORCHID.HQ.ACME is as follows:
CREATE PUBLIC DATABASE LINK ORCHID.HQ.ACME CONNECT TO scott IDENTIFIED BY tiger USING 'ORCHID.HQ.ACME'
The following statement is the complete CREATE DATABASE LINK statement shown earlier. This example illustrates the creation of the SALES database link and the complete path that is specified for the link:
CREATE PUBLIC DATABASE LINK sales.division3.acme.com
CONNECT TO guest IDENTIFIED BY password
When a database link is created, a complete path (the remote account and the database string), a partial path (just the remote account or just the database string), or no path can be specified.
When a SQL statement references a global object name in the SALES database, the local Oracle node finds the corresponding SALES database link in the local database and attempts to establish a session in the remote database for the user GUEST/PASSWORD. The database string specified in the SALES database link definition (which is operating system and network dependent) is used to facilitate the remote connection.
Typically, it is the responsibility of each database administrator or application administrator to create the necessary database links to databases throughout the network. Database links are an implementation detail that should be completely transparent to applications and end-users of a database. It should appear to applications and users that a remote table is accessed by specifying the table's global object name, not by referencing an available database link. In fact, administrators should create location transparency for remote objects using views, synonyms, or procedures, so that applications do not explicitly reference remote data. Then, if the remote object is moved, only the synonym needs to be altered, not all applications.
CREATE PUBLIC DATABASE LINK NY_FIN.HQ.ACME USING 'NY_FIN.HQ.ACME'
Figure 4 - 4. Public Database Link with Default Connection
Users connected to OHIO.SALES.ACME can use the NY_FIN.HQ.ACME database link to connect to NY_FIN.HQ.ACME with the same username and password they have on OHIO.SALES.ACME. To access the table on NY_FIN.HQ.ACME called EMP, any user could issue the SQL query:
SQL> SELECT * FROM EMP@NY_FIN.HQ.ACME;
Note: If the target database were in the source database's default domain, the user would not need to include the domain in the link name or service name, or in the SELECT command.
This query would initiate a connection from OHIO to NY_FIN with the current username and password to log onto NY_FIN. The query would then be processed on NY_FIN. The data available to the current user from the table EMP would be returned to OHIO. Each user creates a separate connection to the server. Subsequent queries to that database link by that user would not require an additional logon.
CREATE PUBLIC DATABASE LINK NY_FIN CONNECT TO FINPUBLIC IDENTIFIED BY NOPASS USING 'NY_FIN'
Figure 4 - 5. Public Database Link with Specific Connection
Any user connected to OHIO can use the NY_FIN database link to connect to NY_FIN with the common username/password of FINPUBLIC/NOPASS. To access the table in the FINPUBLIC account of NY_FIN called ALL_SALES, any user could issue the SQL query:
SQL> SELECT * FROM ALL_SALES@NY_FIN;
This query initiates a connection from OHIO to NY_FIN to the common account FINPUBLIC. The query is processed on NY_FIN, and data from the table ALL_SALES are returned to OHIO.
Each user creates a separate connection to the common account on the server. Subsequent queries to that database link by that user would not require an additional logon.
A connection qualifier contains a qualifier name and, optionally, a username and password. To create a connection qualifier, use a statement similar to the following:
CREATE PUBLIC DATABASE LINK NY_FIN@PROFITS CONNECT TO ACCOUNTS IDENTIFIED BY TAXES USING 'NY_FIN'
To use the connection qualifier, you append the qualifier name to the service name of the database you want to access.
For example, the following SQL queries use three database links to the same database, using different connection qualifiers:
SELECT * FROM EMP@NY_FIN;
SELECT * FROM SCHEDULE@NY_FIN@PROFITS;
SELECT * FROM EMPSALARIES@NY_FIN@FIN;
In this example @PROFITS and @FIN are connection qualifiers.
DROP DATABASE LINK linkname;
For example, to drop the database link NY_FIN, the command would be:
DROP DATABASE LINK NY_FIN;
For example, assume that the local database's global name is MKTG.ACME.COM. Also assume that the following CREATE DATABASE LINK statements have been issued by the same user:
CREATE DATABASE LINK hq.acme.com
CONNECT TO guest IDENTIFIED BY password
CREATE DATABASE LINK sales USING 'dbstring';
The following query lists all of the private database links contained in the schema associated with the current user issuing the query:
SELECT db_link, username, host
For example, if the user that owns the previously created database links (HQ and SALES) issues the query above, results similar to those below are returned:
DB_LINK USERNAME HOST
---------------- -------- ----------
Notice that the USERNAME and HOST fields can be null if database link definitions do not indicate complete paths to the remote database.
Once a gateway is installed, Oracle7 client applications can access the non-oracle data as if it were data in Oracle tables. To do so, a system administrator creates database links and local synonyms at each integrating server for the gateway server. Database links and synonyms provide location transparency. They are created on the Oracle7 Servers that integrate the transparent gateway into the Oracle7 cooperative server environment.
Beyond the typical database administration duties, a database administrator for a distributed environment will need to deal with some administration duties specific to distributed systems. The administrator may need to coordinate with a number of other administrators including the network administrator to properly coordinate changes made to the system.
Oracle provides several utilities to aid the database administrator in maintaining and monitoring database performance.
SNMP support aligns database managements tasks with those of system or network managers. For example, DBAs can be alerted if a database file runs out of space in the middle of the night.
DBAs can monitor a number of variables about Oracle servers; every variable is defined in a Management Information Base (MIB). By monitoring key variables, such as the current number of transactions and the amount of space allocated and used, DBAs can spot potential problems more readily.
Most systems support the ability to call another program, such as Oracle Server Manager, to allow the DBA to respond to an event, such as an abnormal shutdown or out-of-control query.
Currently, MIBs are available for:
For information on configuring Oracle SNMP Support for Oracle networking products (Oracle Server and Listener, Oracle MultiProtocol Interchange, and Oracle Names), see the Oracle Network Manager Administrator's Guide. For information on using the Oracle SNMP Support feature to develop third-party SNMP-based management applications, see the Oracle SNMP Support Reference Guide.
Attention: Server Manager replaces SQL*DBA after release 7.2.
Server Manager provides administrative functionality via an easy-to-use interface. You can use Server Manager to:
Figure 4 - 6. Administration Window Version Banner
You may want to use Server Manager in line mode when a graphical device is unavailable (such as when dialing-in from a non-GUI terminal), or when performing unattended operations (such as when running nightly batch jobs or batch scripts that do not require user intervention).
Copyright © 1996 Oracle Corporation.
All Rights Reserved.