Page 710
TNSNAMES.ORA must be on the client and the server. With this syntax, the user must have a current login on both Germany's database and Detroit's database. Assuming that the schema name is the same for both Germany and Detroit, the syntax for accessing the table SITE_SPECIFICATION would be the following:
SELECT * FROM SITE_SPECIFICATION@GERMANY.BWC.COM;
NOTE |
The link within the SQL statement is not case sensitive.n |
TIP |
To ensure transparency, create a synonym or view to hide the database link from the users:CREATE SYNONYM germany_specification FOR site_specification@germany.bwc.com; |
Creating a database link can be as simple or as complex as the DBA chooses to make it. For example, a link can be created establishing a connection to Europe. This could be advantageous in the event that BWC expands to other countries in Europe. The database link for both Milan and Frankfurt would be
CREATE DATABASE LINK europe using `EUROPE.BWC';
This initial connect would not work, however, when creating a synonym; the DBA could then expand on the name by adding the country:
CREATE SYNONYM germany_specification FOR site_specification@europe@germany;
NOTE |
As in previous versions of Oracle, Oracle8 does not support selecting a LONG datatype from a remote database.n |
Using Initialization Parameters for Distributed SystemsIn addition to the parameters specified in the distributed database section, the parameters in Table 28.1 are also affected by the deferred transaction database.
Table 28.1 Parameters Affected by Deferred Transaction DatabaseParameter | Description |
COMMIT_POINT_STRENGTH (0_255) | This parameter is used to set the commit point site in the two-phased commit. The site with the highest commit point strength will be the commit point site. Each of the sites using this as the commit point site must be on the same node. The factors determining which database should be the commit point site should be ownership (driver) of data, criticality of |
Page 711
Parameter | Description |
data and availability of the system. Information concerning the status of the commit is located on the commit point site. It is recommended that not all sites have the same value or only one with a higher value. This ensures that in the event of a failure, other sites can record this data. This parameter's defaults are operating system specific. Refer to your operating system_specific Oracle8 documentation for these values. | |
DISTRIBUTED_TRANSACTIONS (0_TRANSACTIONS) | Limits the number of concurrent distributed transactions. If set to zero, the process RECO (Oracle's recovery process) is not activated, and distributed capabilities of the database are disabled. |
GLOBAL_NAMES | If set to true, the name referenced in the link must match the name of the database and not the alias. This must be used in order to utilize advanced replication features of Oracle8. |
DML_LOCKS (20_unlimited) | Limits the number of DML locks in a single transaction. |
ENQUEUE RESOURCES (10_65535) | Allows several concurrent processes to share resources. To determine whether this value is set appropriately, look at the enqueue_waits in the v$sysstat table. If this is a non-zero value, increase the enqueue resources. |
MAX_TRANSACTION_BRANCHES (1_32) | The maximum value for this parameter has been increased from 8 to 32. Branches are the numbers of different servers (or server groups) that can be accessed in a single distributed transaction. Reducing this number may decrease the amount of shared pool used. |
OPEN_LINKS (0_255) | The number of concurrent open connections to other databases by one session. In a distributed environment, care must be taken to ensure that this value is not less than the number of remote tables that can be referenced in a single SQL statement. If the value is set to 0, there can be no distributed transactions. |
OPEN_LINKS_PER_INSTANCE (0_UB4MAXVAL) | This is new to Oracle8. It limits the number of open links created by an external transaction manager. For more information, refer to your Oracle8 documentation. |
Page 712
System changes are the biggest problem in managing a simple distributed system. That is not much different than managing a single, autonomous database. The difference is that each database can see objects in other databases. This creates a system that depends on continuity. Below are the changes within a system that must be coordinated within a distributed system.
NOTE |
If a user drops a table that is referenced by links from another system, there will not be a referential integrity warning. These warnings occur only when the table contains elements that are referenced.n |
Communication between organizations is extremely important, particularly among developers and the DBA. A change in the structure of one database or IP address can affect the entire company and the integrity of the distributed system. This coordination usually is in the hands of the DBA(s).
Another problem can occur if the connection between databases is lost. The responsibility in determining what has happened if connectivity is lost will begin with the DBA. The DBA must be able to easily identify error messages, particularly associated with SQL*Net or Net8. For more information concerning troubleshooting connection problems, please refer to Chapter 20.
Tuning methods for a distributed database should be the same as those for an autonomous database. Please refer to Part VIII, "Performance Tuning," and keep the following rules in mind: