HI,
Many times it is necessary for the two or more databases to talk to each other so that they can share few tables or in general data between each other.
In oracle the two database can communicate with each other by means of database objects called as database link or also called as DB LINKS. A database link is an object in the
local database that allows you to access objects on a remote database or to mount a secondary database in read-only mode. The remote database can be either an Oracle or a non-Oracle database.
The syntax to create a DB LINK is as shown
CREATE [PUBLIC] DATABASE LINK dblink
[CONNECT TO user IDENTIFIED BY password]
[USING 'connect_string']
where:
PUBLIC
creates a public database link available to all users. If you omit
this option, the database link is private and is available only to
you.
dblink
is the complete or partial name of the database link. For
guidelines for naming database links, see the section Referring to
Objects in Remote Databases.
CONNECT TO user
IDENTIFIED BY password
is the username and password used to connect to the remote database.
If you omit this clause, the database link uses the username and
password of each user who uses the database link.
USING
specifies either:
* the database specification of a remote database
* the specification of a secondary database for a read-only
mount.
For information on specifying remote databases, see the SQL*Net
User’s Guide for your specific SQL*Net protocol.
Read-only mounts are only available in Trusted Oracle and can only
be specified for public database links.
PREREQUISITES:
To create a private database link, you must have CREATE DATABASE
LINK system privilege. To create a public database link, you must
have CREATE PUBLIC DATABASE LINK system privilege. Also, you must
have CREATE SESSION privilege on a remote database. SQL*Net must be
installed on both the local and remote databases.
For e.g
create public database link ITALIC2UTS
connect to utsav identified by utsav
using ‘UTS’;
Once the DB LINK is Created you can verify the it from the data dictionary view DBA_DB_LINKS.
SQL> select * from dba_db_links;
OWNER DB_LINK USERNAME HOST CREATED
——- ———- ———- ———- —————
PUBLIC ITALIC2UTS UTSAV uts 22-JUN-07
To view the tables from other database under the schema utsav we can use the following query.
SQL> select * from emp@italic2uts;
EMPID
———-
1
2
3
4
Common Errors while creating a DB LINKS are
ORA-02019: “connection description for remote database not found”
Cause: The user attempted to connect or log in to a remote
database using a connection description that could not
be found.
This error can be reported for the following reasons:
1. You are trying to use a database link created by another user as a private
database link. If the ‘PUBLIC’ option is not specified in the create
database link statement, the database link will be created as a private
database link and will only be accessible to the user who created it.
2. You are trying to use a database link that was created without a connect
string. Although you will not receive an error when creating the database
link, at the moment you try to use the newly created database link you will
receive the error.
3. You are trying to use a database link that does not exist.
Another Common error is
ORA-2085 “database link %s connects to %s”
Created a new database link pointing to a schema in another database. The db-link creation went through successfully.
SQL statement using the db-link is returning error ORA-2085
Cause
GLOBAL_NAMES is set to true in both instances.
The instance where the link is being created has a domain name specified.
The target instance does not.
IMPORTANT NOTE: “If there is no domain in the target’s global_name table, the domain would be derived from the source database’s global_name table.”
Solution
Alter the global_name of the remote instance to match what is expected of from the local instance.
SQL> alter database rename global_name to dbname.domain;
OR
If GLOBAL_NAMES is not required, set this parameter to FALSE.
References
Note 210630.1 – Database Links: Troubleshooting ORA-2085 “database link %s connects to %s”
Hi,
Consider the Following environment.
Nodenames : node1.us.oracle.com node1
node2.us.oracle.com node2
node3.us.oracle.com node3
Node to be removed : node3.us.oracle.com node3.
Database name : racdb
Instance names : racdb1, racdb2, racdb3
Do this on node1
Login as the Oracle user and set the CRS_HOME to the Oracle clusterware home
Cd $CRS_HOME/oui/bin
./addNode.sh
It will recognise the existing nodes and ask you to enter the new node name. Enter the “short†node names for public, private and VIP nodename
e.g node3, node3-priv, node3-vip
The installer will copy the software to node3 and at the end will ask you to run a few scripts as the root user on node1 and node3. Make sure that you run the correct script on the correct node as specified. Run the scripts one after the other.
Run this on node1
Login as the oracle user
Cat $CRS_HOME/opmn/conf/ons.config
Look at the remote port entry e.g 6200
Run $CRS_HOME/bin/racgons add_config node3:6200
2. Adding Oracle database software on node3.
Do this on node1
Login as the oracle user
Set the environment variables
Cd $ORACLE_HOME/oui/bin
./addNode.sh
Select node3 from the first screen
The installer will copy the software to node3 and ask you to run root.sh at the end. Run it on the correct node as specified.
Do this node3
Login as the oracle user and set the environment to the DBHOME
$ORACLE_HOME/bin/netca
Choose Cluster management
Choose Listener
Choose ADD
Choose the name as LISTENER
This will add a listener on node3 with the name LISTENER_node3.
3. Creating a database instance on node3
Do this on node1
Login as the oracle user and set the environment to DBHOME
$ORACLE_HOME/bin/dbca
Choose RAC
Choose Instance Management
Choose Add Instance
Select racdb as the database and enter the sys username and password at the bottom of the screen.
It will display the existing instances. Click next and on the following screen enter racdb3 as the instance name and choose node3.
This will create a database instance (racdb3 on node3). During creation it will ask if the ASM instance should be extended to node3. Choose Yes.
This finishes the node addition procedure. To verify do the following:
On node1 : srvctl status nodeapps –n node3
There should be 4 services running
Srvctl status instance –d racdb –I racdb3
It should show the instance racdb3 running on node3.
