Troubleshooting Database Links
Each Oracle DBA knowns this story of course ... sorry, but not every Oracle user is a DBA. One early morning your management staff complains about missing sales data, which must be ready for the daily trend analysis. What's happen ... nobody changed the calculation procedure during the night ... here it is ... the database link is down. Why ... well the remote DBA changed all passwords. This is only one of the famous database link trobleshooting stories. Read the following article and you have less annoyance with database links.
Oracle enforces the requirement that the database.domain portion of the database link name must match the complete global name of the remote database by setting GLOBAL_NAMES to TRUE in the initialization parameter file initSID.ora.
Example: Local DB is 'SOL3' (Oracle 8.1.6), remote DB is 'SOL1' (Oracle 7.3.4)
# Parameter file initSOL3.ora for Database SOL3
# Parameter file initSOL1.ora for Database SOL1
Our database link points from the local database SOL3 to the remote database SOL1. Therefore we need the global database name for SOL1. Ask the remote database administrator for these information or connect to SOL1 and execute the following query on SOL1:
SQL> select GLOBAL_NAME from GLOBAL_NAME;
We found the database link name 'SOL1.WORLD' for our local database SOL3. Now connect to the local database database SOL3 as a user, who has the privilege to create a database link and create the following named database link to SOL1.
$ sqlplus jones/lion@SOL3
SQL> CREATE DATABASE LINK sol1.world
With this DB-Link, you (jones/lion) can connect to the remote database SOL1 as user scott/tiger. This user must exist on the remote database SOL1. Test the database link as user jones/lion from SOL3.
SQL> SELECT * FROM emp@SOL1.WORLD;
You may ask, what's this strange 'SOL1' in ..... using 'SOL1' means ?. Well this is the so called connect_string (or net_connect_string in Oracle8i). This string has nothing common with the DB-Link name, but very often the same name is used. The connect string must be defined in the Net8 configuration file TNSNAMES.ORA, if you don't use Oracle Names.
Well, now you understand our short story at the beginning of this article. If the DBA on SOL1 changes scott's password to snake, we have the disaster with our missing sales data ... poor management.
Oracle allows three kinds of external references to DB-links, which are resolved as follows:
Besides these often used DB-Links, you can create a database link as PUBLIC. Be very careful with PUBLIC database links, they may open a door for everybody to a remote database. We suggest, NOT TO USE public database links without Authentication.
Shared PUBLIC DB-Link with Authentication
A shared PUPLIC DB-Link with Authentication uses a single network connection to create a PUBLIC database link that can be shared between multiple users with more security. This DB-Link is available only with the multi-threaded server configuration.
SQL> CREATE SHARED PUBLIC DATABASE LINK sol1.world
The AUTHENTICATED clause specifies the username and password (JONES/LION) on the target instance (SOL1). This clause authenticates the user to the remote server and is required for security. The specified username and password (JONES/LION) must be a valid username and password on the remote instance (SOL1). The username and password are used only for authentication. No other operations are performed on behalf of this user.