Oracle7 Server Distributed Systems Manual, Vol. 1 Go to Product Documentation Library
Library
Go to books for this product
Product
Go to Contents for this book
Contents
Go to Index
Index



Go to previous file in sequence Go to next file in sequence

Database Administration


This chapter discusses issues of concern to the database administrator (DBA) implementing or maintaining databases in a distributed system. Topics covered include:

The database administrator must also be responsible for implementing distributed update recovery management (see Chapter 5) and basic or advanced replication features. See Oracle7 Server Distributed Systems, Volume II.


Implementing Databases in a Distributed System

There are a number of factors the DBA should consider before implementing databases in a distributed system. Many of the planning decisions will require the cooperation and transfer of information between the network administrator and the DBA.

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.

Purpose of the Database

The purpose and size of the database will determine how you plan and structure the database.

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.

Size of the Database

Consider the size of a machine relative to the database that will run on it. For example, keep in mind that a machine with more physical memory and more processors can support a larger database. A large database (typically over one Gb) is more likely to need to split I/O and the logical database design. It is more likely to need larger initialization file parameter values.

Physical and Logical Layout of the Database

You need to determine several aspects of database physical layout. For example, you need to estimate the number of disks and controllers required for optimal performance from your Oracle7 system. Many smaller disks tend to give better performance than a few larger disks.

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.

File Locations and Initialization Parameters

The network or database administrator must determine locations for database files. For example, for Oracle on a SunOS platform, it is recommended that database files be stored according to the Oracle Optimal Flexible Architecture (OFA). OFA is a specification for configuring Oracle systems at sites demanding high performance with low maintenance under continually evolving requirements. The OFA makes configuration recommendations regarding aspects of your operating system, such as mount points, login home directories, and user profiles. It also makes configuration recommendations regarding Oracle software and administrative files, and database files.

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.

Backup Strategies

The purpose of the database and how frequently it is used determine which backup methods are chosen, as well as the frequency of backups. The DBA must develop a plan to produce and store archive tapes for each database server in the distributed system, or his region of responsibility, if responsibility for parts of the distributed system is divided between several DBAs.

Memory Requirements

When designing your database, consider the following:

For more information about these issues, see your operating system-specific documentation.

Relinking Product Executables

Most Oracle products provide relinkable executables. Relinking lets you regenerate a program from its component parts. Relinking also lets you add options to the Oracle7 Server, such as the distributed option, PL/SQL, and Oracle (SQL*Net) Protocol Adapters.

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.


Views and Location Transparency

Local views can provide location transparency for local and remote tables in a distributed system.

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, jward.dept@hq.acme.com 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


Synonyms

Synonyms are very useful in both distributed and non-distributed environments because they hide the identity of the underlying object, including its location in a distributed system. If the underlying object must be renamed or be moved, only the synonym needs to be redefined; applications based on the synonym continue to function without modification. Synonyms can also simplify SQL statements for users in a distributed system.

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] 

where:

[PUBLIC]

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.

synonym_name

Specifies the alternate object name to be referenced by users and applications.

schema

Specifies the schema of the object specified in object_name. Omitting this parameter uses the creator's schema as the schema of the object.

object_name

Specifies either a table, view, sequence, or other name as appropriate.

database_link_name

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.

A Simple Example

Assume that in every database in a distributed system, a public synonym is defined for the SCOTT.EMP table stored in the HQ database:

CREATE PUBLIC SYNONYM emp	FOR scott.emp@hq.acme.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.

A More Complex Example

Figure 4 - 2 shows two servers, OHIO and NY_FIN, in which a database link from OHIO to NY_FIN and the synonym FOR_SALE provide an alternate object name for use in OHIO to reference the OPEN table in NY_FIN. The database link and the synonym are created as follows:

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.

Relocating a Table

If the OPEN table in the example above were to be moved from one database server to another, only the synonym or the database link would need to be changed to identify the new location. The applications would continue to reference the same object name, although they would be connecting to a new location to access the data in that table. Figure 4 - 3 shows the most common method of redefining the location of a table to retain location transparency. The command would be:

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.


Replication Transparency

The ability to insure reliable data replication is an extremely important (and potentially complex) factor in a distributed system. Data replication means that any given data object can have several stored representatives at several different sites and that, if each representative is potentially updatable, there must be a mechanism for insuring that all representatives reflect the changes.

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.


Procedures and Location Transparency

Location transparency is also provided by PL/SQL program units called procedures that contain SQL statements that reference remote data. For example, consider the procedure created by the following statement:

CREATE PROCEDURE fire_emp (enum NUMBER) AS 
BEGIN 
	DELETE FROM jward.emp@hq.acme.com 
		WHERE empno = enum; 
END; 

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 jward.emp@hq.acme.com; 

Consequently, the FIRE_EMP procedure can be defined with the following statement:

CREATE PROCEDURE fire_emp (enum NUMBER) AS 
BEGIN 
	DELETE FROM emp WHERE empno = enum; 
END; 

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.


Query and Update Transparency

Oracle allows the following standard DML statements to reference remote data:

A query, including joins, aggregates, subqueries, and SELECT ... FOR UPDATE, can reference any number of local and remote tables and views. For example, the following query joins information from two remote tables:

SELECT empno, ename, dname 
  FROM scott.emp@sales.acme.com a, 
	jward.dept@hq.acme.com b 
  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 scott.emp@sales.division3.acme.com 
  SELECT * FROM jward.emp; 

Restrictions

Within a single SQL statement, all referenced LONG and LONG RAW columns, sequences, updated tables, and locked tables must be located at the same node. Oracle does not allow remote Data Definition Language (DDL) statements (for example, CREATE, ALTER, and DROP). Also, the LIST CHAINED ROWS clause of an ANALYZE statement cannot reference remote tables.


Transaction Transparency and Distributed Transaction Management

Transactions in Oracle, single-site or distributed, generally are terminated with a COMMIT or ROLLBACK statement. SAVEPOINT and ROLLBACK TO SAVEPOINT statements are also supported by the Oracle distributed architecture.

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.


Failure Resolution Transparency and the RECO Background Process

Network or computer hardware failure is always possible. Oracle's distributed system architecture guarantees that if a network or system failure occurs during the commit of a distributed transaction, the transaction is automatically and transparently resolved globally. When the network or system is restored, either all nodes commit, or all roll back.

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.


Performance Transparency

All of Oracle's underlying performance optimizations are transparent in a distributed system, including shared SQL, cost-based SQL statement optimization, Oracle's array interface (for fetch and insert), and PL/SQL (for reduced network traffic).


Object Resolution and Location Transparency

When defining views, synonyms, and procedures that reference remote objects, consider the following issues so that your views, synonyms, and procedures always access the intended objects with the intended privileges:

		CREATE SYNONYM emp FOR scott.emp@hq.acme.com;


Schema Object Names and Data Access

Oracle allows schema objects (for example, tables, views, and procedures) throughout a distributed system to be referenced in SQL statements using global object names. In Oracle, a schema object's global name consists of the name of the schema that contains the object, the object name, followed by an "at" sign (@), and a database name. For example, the following query selects information from the table named SCOTT.EMP in the SALES database:

SELECT * FROM scott.emp@sales.division3.acme.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.


Balancing Location Transparency and Security

The choice of using a view, synonym, or procedure for location transparency determines the degree to which the local and remote administrators are responsible for object security. The following example statements and sections outline the issues to consider when choosing among the options for location transparency.

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
   USING 'db_string';
CREATE VIEW admin.emp_view AS
   SELECT * FROM scott.emp@hr.acme.com;
CREATE PROCEDURE admin.fire_emp (enum NUMBER) AS
BEGIN
   DELETE FROM scott.emp@hr.acme.com
      WHERE empno = enum;
END;
CREATE SYNONYM admin.emp_syn FOR scott.emp@hr.acme.com;

Privilege Management With Views

Assume a local view (ADMIN.EMP_VIEW) references a remote table or view. The owner of the local view can grant only the object privileges on his view that have been granted the remote user referenced in the database link. This is similar to privilege management for views that reference local data. Therefore, local privilege management is possible when views are used for location transparency. For example, the user ADMIN can successfully grant the SELECT and DELETE privileges, but not the INSERT and UPDATE privileges for EMP_VIEW.

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.

Privilege Management With Procedures

Assume a local procedure includes a statement that references a remote table or view (see example on previous page). The owner of the local procedure can grant the EXECUTE privilege to any user, thereby giving that user the ability to execute the procedure and access remote data.

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.

Privilege Management With Synonyms

Assume a local synonym is an alias for a remote object. The owner of the local synonym cannot grant any object privileges on the synonym to any other local user. This behavior is different from privilege management for synonyms that are aliases for local tables or views; in the case where a synonym is an alias for a remote object, local privileges for the synonym cannot be granted because this would amount to granting privileges for the remote object, which is not allowed. Therefore, no local privilege management can be performed when synonyms are used for location transparency; security for the base object is controlled entirely at the remote node. For example, the user ADMIN cannot grant any object privileges for the EMP_SYN synonym.

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.


Public and Private Database Links

Public and private database links are typically created by DBAs or individual users. Global database links are typically created by a network administrator. This section discusses some topics related to public and private database links, such as creating, viewing, and dropping database links from the data dictionary. For general information on database links, see page 2 - 29.

Manually Creating Database Links

The DBA and application user typically create public and private database links manually. Global database links are created automatically for every database defined in Network Manager by a network administrator or DBA.

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:

[PUBLIC]

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.

linkname

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

CONNECT TO

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.

username

Specifies a valid Oracle username on the remote database server.

password

Specifies the corresponding password of the username on the remote database server.

service_name

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 
	USING 'dbstring'; 

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.

Public Database Links with a Default Connection

Figure 4 - 4 shows a public database link created by the DBA user SYSTEM using the service name NY_FIN.HQ.ACME. The link is created by entering:

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.

Public Database Links with a Specific Connection

Figure 4 - 5 shows the database link created by the user SYSTEM with the service name NY_FIN:

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.

Connection Qualifiers

You can also define connection qualifiers to database links. Connection qualifiers provide a way to create more than one link to a given database. Alternate links are a useful way to access different accounts on the same database with different sets of access privileges. The alternate link created by a connection qualifier must include a reference to a database by its global database name (or service name).

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.

Dropping a Database Link

You can drop a database link just as you can drop a table or view. The command syntax is:

DROP DATABASE LINK linkname; 

For example, to drop the database link NY_FIN, the command would be:

DROP DATABASE LINK NY_FIN; 

Examining Available Database Links

The data dictionary of each database stores the definitions of all the database links in that database. The USER/ALL/DBA_DB_LINKS data dictionary views show the database links that have been defined.

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
   FROM user_db_links;

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
----------------  --------  ----------
HQ.ACME.COM       GUEST
SALES.ACME.COM              dbstring

Notice that the USERNAME and HOST fields can be null if database link definitions do not indicate complete paths to the remote database.

Finding Available Database Links

Any user can query the data dictionary to determine what database links are available to that user. For information on viewing the data dictionary, see Oracle7 Server Concepts or the Oracle7 Server Administrator's Guide.

Limiting the Number of Active Database Links

You can limit the number of connections from a user process to remote databases with the parameter OPEN_LINKS. This parameter controls the number of remote connections that a single user process can use concurrently within a single SQL statement. To improve application performance, increase the value of this parameter if users need to access more databases at the same time. This allows the user to access all the required remote data without waiting for the local instance to close and open connections.


Database Links and Oracle Transparent Gateways

An Oracle Transparent Gateway provides access to non-Oracle data and services, such as cooperative server connectivity to IBM DB2 through a transparent gateway.

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.


Tools for Managing and Monitoring the Database

The database administrator for a distributed system must deal with added levels of complexity not faced by administrators of dedicated host systems.

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.

Third-Party Vendor Support

There are currently more than 60 companies producing more than 150 products that help manage Oracle databases and networks providing a truly open environment.

SNMP Support

Besides its network administration capabilities, Oracle Simple Network Management Protocol (SNMP) support allows an Oracle server to be located and queried by any SNMP-based network management system. SNMP is the accepted standard underlying many popular network management systems such as:

These graphical systems display various views of a network, allowing administrators to zoom in to provide more detail about an individual service or device. Oracle SNMP Support allows DBAs to:

DBAs administering multiple databases no longer must repeat basic tasks for every database instance. For example, logging sequentially into multiple machines to check the status of each Oracle database. After DBAs configure SNMP support in Network Manager, they can use it to monitor current activity for all Oracle databases on a network and request more detail when desired. DBAs can use SNMP support to verify normal activity and spot abnormal situations faster, allowing more time for other, less automatic tasks.

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:

Note: Oracle SNMP Support is not intended to replace Oracle Server Manager which offers a different set of functions for managing and controlling individual Oracle servers and applications.

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.

Server Manager

Server Manager is Oracle's database administration tool. The graphical component of Server Manager (Server Manager/GUI) allows you to perform database administration tasks with the convenience of a graphical user interface (GUI). The line mode component of Server Manager provides a line-mode interface.

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

Portability

Server Manager/GUI is available for multiple GUI environments, yet adopts the native look and feel of the platform on which it is running. So Server Manager running on Motif looks like a Motif application, and Server Manager running on Windows looks like a Windows application.

Supported Oracle Server Releases

You can use Server Manager to administer any database running Oracle7 release 7.0 or later. You can also simultaneously administer different databases running different releases of Oracle7.

Server Manager in Line Mode

For those environments that do not support a graphical user interface, or for those times when a command line interface is desirable, Server Manager in line mode provides a conversational line mode. In line mode, you can explicitly execute commands on a command line.

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


Connecting Between Oracle Server Versions

In administering a distributed processing network, you must be aware of the version of Oracle software running at networked sites:

Note: SQL*Net version 2 cannot be used to connect to Oracle version 6 servers, only Oracle7 Servers. For information on issues to consider when migrating from an earlier version of SQL*Net, and coexistence of SQL*Net version 1 and 2, see Understanding SQL*Net.




Go to previous file in sequence Go to next file in sequence
Prev Next
Oracle
Copyright © 1996 Oracle Corporation.
All Rights Reserved.
Go to Product Documentation Library
Library
Go to books for this product
Product
Go to Contents for this book
Contents
Go to Index
Index