Cerberus ® - business approach to monitoring
Taking control over Oracle database links
- SATS Remote DBAis dedicated to optimizing database uptime and performance, and to reducing the cost of IT ownership. Our Like Clockwork RDBA service saves you time, recourses, continuity, and helps you to ally IT tasks with the business goals of your organization. Many other companies chose SATS for our dependability, cost-effectiveness, and expert technological support. SATS RDBA advantages help our customers achieve costs savings of up to 75% while increasing the quality of DBA coverage.
- SATS Remote DBA could be a one stop service provider for all this platforms:
- Oracle, PostgreSQL, Hadoop, MS SQL, MySQL, Mongo DB
For specific inquiries please contact SATS RDBA Services or call 650 889-5533.
Global database links with Oracle SQL Net
PROBLEM CASE
We have multiple databases with the same database name, but located on different servers. We want to linke them together so the data will be shared on demand. To do this we must setup so called global database links.
SAMPLE SOLUTION STEPS
The following creates a global link between two databases which have identical ORACLE_SID names (db_name)
Scenario: (link is build from 'countach' --> to 'diablo')
- Two db servers are named 'countach' and 'diablo'
- Two identical Oracle databases on each machine with ORACLE_SID set to 'yp4' on each.
- Two identical Oracle users in each database user1/pass1.
-
Set SQL*Net with following syntax in listener.ora and tnsnames.ora:
------- listener on diablo: ------------------------
LISTENER =
(ADDRESS_LIST =
(ADDRESS=
(PROTOCOL=IPC)
(KEY= yp4.diablo)
)
(ADDRESS =
(PROTOCOL = TCP)
(Host = diablo)
(Port = 1521)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = yp4)
(GLOBAL_NAME = yp4.diablo)
(ORACLE_HOME = /usr/local/oracle/product/10.2.0)
)
)
STARTUP_WAIT_TIME_LISTENER = 0
CONNECT_TIMEOUT_LISTENER = 10
TRACE_LEVEL_LISTENER = OFF
--- tnsnames.ora fragment on countach --------------- diablo =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP)
(Host = diablo)
(Port = 1521)
)
)
(CONNECT_DATA =
(SID = yp4)
(GLOBAL_NAME = yp4.diablo)
))
----------------------------------------------------- -
Make sure your init.ora has parameter 'global_names = true' set.
- Rename a yp4 on diablo - from sqlplus as system, issue:
'alter database rename global_name to yp4.diablo;'
-
Now we ready to setup link from coutach to diablo. Issue from countach server:
drop database link yp4.diablo;
create database link yp4.diablo
connect to user1 identified by pass1 USING 'diablo';
-
Link usage sample:
select count(*) from tab@yp4.diablo;
Questions/Comments? - feel free to contact Cerberus (which is me) on
RDBA.net forums under Oracle DBA corner.