What Is A Database Link Oracle

Database Links

Advanced Administration Tips

What is a Database Link and how do I configure one? A Database Link is a named object created in one database which describes how to connect to another, completely different, database. Using a Database Link, it is possible, whilst connected to, say, the DB8 database to issue the command SELECT * FROM EMP@DB9, and the data that is returned will be that from the EMP table in the DB9 database. Links are thus a means of performing distributed database processing –the ability to distibute data around many different databases, on many different machines, in many different physical locations. Such an approach gives you redundancy (if the Melbourne machine blows up, it takes the Melbourne data with it, but Perth and Sydney continue working as before). It also gives you, potentially, performance benefits –with tables distributed amongst many machines, no one machine has to do all the hard work of resolving Users’ queries. (On the other hand, with data having to pass back and forth across the network, which is usually relatively slow, the performance benefits of distributed processing can easily be swamped by awful networking performance). Database Links are thus at the heart of Advanced Replication, which is Oracle’s complete infrastructure for distributed database processing. Links are relatively easy to set up, but they do depend on having first configured all aspects of inter-machine and inter-database networking and communication successfully. If you don’t know one end of a LISTENER.ORA from a SQLNET.ORA, then getting Links to work reliably is going to be awkward.

Getting Started with the Network For the purposes of this discussion, I’m going to assume that we have an Oracle 8i database (called DB8) on a machine called MONTEVERDI, and an Oracle 9i database (called DB9) on a machine called MOZART. Both machines are part of a networking domain called ‘aldeburgh.local’. (For the record, this is exactly how my test machines at home are configured, using Windows 2000). We want to establish a link from the DB8 database to the DB9 one, not the other way around (we’ll come to that later!). Being such a simple network, we’ll use the ‘local naming’ method of connection, meaning that we’ll use local copies of tnsnames.ora to resolve connection requests to the two Instances. You might pause at this. Surely, tnsnames.ora is a method of connecting clients to Instances, and therefore it resides only on the client machine? No. One of the key features of a Database Link is that it is the database that you are directly connected to that has to be able to resolve the name of the database you want to link to. Therefore, tnsnames.ora must also reside on one or more of the Oracle servers themselves. Copyright © Howard Rogers 2002

3/3/2002

Page 1 of 10

Database Links

Advanced Administration Tips

Since in this example it is MONTEVERDI that will require the ability to resolve a connection request to the DB9 database on MOZART, we need a tnsnames.ora on MONTEVERDI that looks like this: DB9 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = mozart)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = db9.aldeburgh.local) ) ) Now the tnsnames.ora merely points the connection request off to the Listener which is running on MOZART. So the listener.ora on MOZART must look like this: LISTENER = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = mozart)(PORT = 1521)) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = db9.aldeburgh.local) (ORACLE_HOME = d:\oracle\ora91) (SID_NAME = DB9) ) ) This is fairly standard stuff. The Listener is listening on a standard port for TCP/IP connection requests, on behalf of the db9.aldeburgh.local database. When it receives one, it will connect to the “DB9” Instance, and we should be in business. One final configuration trap: the file sqlnet.ora is used to establish a variety of default parameters for all connections made from one machine to another –for example, whether encryption should be used. One of its parameters is NAMES.DEFAULT_DOMAIN. If this is allowed to be set to anything at all, we’ll have problems, because Oracle appears to append this default on to the end of otherwise fully-specified global database names. Since we’ve specified our global names exactly, we have no need for a default. Therefore, we need to remove that line (if it’s there) from the sqlnet.ora file. That leaves sqlnet.ora looking like this: SQLNET.AUTHENTICATION_SERVICES= (NTS) NAMES.DIRECTORY_PATH= (TNSNAMES) Copyright © Howard Rogers 2002

3/3/2002

Page 2 of 10

Database Links

Advanced Administration Tips

Instance Configuration Technically, when you come to create your Database Links, you can call them anything you like. But Oracle strongly recommends that you follow the ‘global names’ convention, because Advanced Replication (and some other advanced features) won’t work unless you do. What that convention means is that your links are named in the format .<domain name>. To do that might require some editing of your init.ora, however. Three parameters are crucial. DB_NAME is pretty self-explanatory, and has to be set whenever you create a new database. Not a lot of choice here, therefore –ours will be set to “DB9”. DB_DOMAIN is not compulsory though, yet it’s of extreme importance for linking databases. It defaults to a null string so in our case we’ll need to set it explicitly to “aldeburgh.local”. If you’ve got a registered Internet domain, then that’s probably what you’d use (I don’t, which is why the ‘.local’ is there). If you put DB_NAME and DB_DOMAIN together, you’ve got yourself a Global Database Name. In our case, it will be “db9.aldeburgh.local” –though this isn’t actually set as an init.ora parameter in its own right, but is merely constructed from the other two whenever needed. It’s important that the global database name as implied in the DB9 init.ora is what the GLOBAL_DBNAME setting in MOZART’s listener.ora is set to, and also what the SERVICE_NAME is set to in MONTEVERDI’s tnsnames.ora. If you check all these entries above, you’ll see that this is indeed the case here. Failure to make sure that they match will mean that the DB8 database will request a connection to an Instance which can’t be identified in the first place or, if identification is possible, can’t be connected to. One last parameter now needs to be set. It’s GLOBAL_NAMES, and it needs to be set to TRUE. There’s no absolute requirement for the parameter to be set in this way: but Advanced Replication won’t work without it. What it does is to force that all Database Links must have names which match the global name of the database they’re connecting to. In our case, since we’re connecting from DB8 to DB9, it needs to be set to TRUE in the init.ora for the DB8 database on MONTEVERDI. One final warning: databases are created (or should be –the default appears to be “us.oracle.com”!) with a global name that needs to match what you configure as the Global Database Name in the way described above. If you change your init.ora settings so that the DB_NAME+DB_DOMAIN combination no longer matches what was set as the global name at database creation, you’ll need to rename the database. That’s relatively easy to do: Alter database rename global_name to <something>;

Copyright © Howard Rogers 2002

3/3/2002

Page 3 of 10

Database Links

Advanced Administration Tips

In our case, we may therefore need to issue this command whilst connected as SYS to the DB9 database: Alter database rename global_name to db9.aldeburgh.local; Once all that is set up, we should be able to create our links without too much trouble.

Creating the Database Link This is the easy part. The basic syntax for creating a Database Link is: Create database link Connect to <schema> identified by <password> Using ; The real question is what goes in all those variable entries –the link name and so on? Well, the link name is the fully-qualified global database name that was discussed earlier. In our case, that’s “db9.aldeburgh.local”. There’s no rule that says it must match the global name, remember –except that, following Oracle’s advice, we’ve set GLOBAL_NAMES to be TRUE for the DB8 database. If we therefore specified anything other than “db9.aldeburgh.local” as the link name, the first time we tried to fetch data across the link we’d get the following error message: ORA-02085:

DATABASE LINK

DB9.MADEUPNAME

CONNECTS TO

DB9.ALDEBURGH.COM

The <schema> and <password> entries of the syntax are fairly self-explanatory: which bit of the DB9 database do we want to connect to? If you just want to select from Scott’s tables over the link, for example, then ‘Scott’ and ‘Tiger’ would be sensible entries. You might not want to limit yourself in this way, though: suppose you want to be able to select from tables that happen to reside in multiple schemas? Well, that can be done, too… but I’ll describe that in my paper “What are the different types of Database Links that can be created?”. For now, let’s keep it simple. Finally, the entry is hopefully pretty obvious. It’s whatever is in the tnsnames.ora file on the machine where DB8 resides as the alias for the connection to “db9.aldeburgh.local”. Being an alias, you could have set it to anything –but if you refer back to the discussion about our specific tnsnames.ora file, you’ll see that we’ve set it to “db9”. Bear in mind that it’s what is on the DB8 machine that’s important. It’s this machine, after all, that has to resolve the connection request, and pass it on to the Listener running on the DB9 machine. All of which means that we can now issue the following command:

Copyright © Howard Rogers 2002

3/3/2002

Page 4 of 10

Database Links

Advanced Administration Tips

Create database link db9.aldeburgh.local Connect to scott identified by tiger Using ‘db9’; Notice how the tnsnames.ora alias goes inside single quotes, but nothing else does.

Using the Database Link Now that the link exists, we can select data across it. In our case, whilst still connected to the DB8 database, we can issue the following command: Select * from [email protected]; The rows you see returned will be those coming from the DB9 database. Notice the syntax here. Firstly, I haven’t needed to qualify whose EMP table we’re selecting from, because the Link itself was created as pointing directly to Scott’s schema. Second, the full database link name is then supplied after an “@” symbol. Now let’s try some simple DML: SQL> update [email protected] set sal=7000 where empno=7369; 1 row updated. SQL> commit; Commit complete. So DML commands follow the same rule: fully qualify the object names you are updating, deleting from or inserting into, with an “@” and the link name. Commits, as you notice, are perfectly happy to work across the link too. (Incidentally, just in case you’re wondering, all the rollback and redo generated by this transaction was raised in the DB9 database, not the DB8 one. In other words, the command was processed exactly as if it had been issued whilst connected normally to the DB9 database). What about DDL? Let’s try this: SQL> truncate table [email protected]; truncate table [email protected] * ERROR at line 1: ORA-02021: DDL operations are not allowed on a remote database So no DDL commands of any type are permitted across the link. Try a “create index”, a “create table [email protected] as select * from [email protected]”, a “drop table” or any other piece of DDL you can think of, and the same error message will Copyright © Howard Rogers 2002

3/3/2002

Page 5 of 10

Database Links

Advanced Administration Tips

appear. (Again, in case you were wondering, a “create table newemp as select * from [email protected]” will work fine: the DDL bit of the statement is taking place in the local DB8 database, and only the select is taking place across the link). So the general rule is that queries and DML across a link are permitted, but DDL isn’t. When DML is performed, by the way, all referential integrity constraints are checked and applied as normal (though if it happens that the parent table, say, is in a different schema than the one referred to in the ‘create database link’ command you’ll have trouble). Finally, what does the link look like as far as the DB9 database is concerned? If we query v$session after the link is created, but before it is actually used, we get this sort of result: SQL> connect system/manager@db9 Connected. SQL> select sid, serial#, username from v$session 2 where username is not null; SID SERIAL# USERNAME ---------- ---------- -----------------------------8 12 SYSTEM 1 row selected. Here we see only SYSTEM’s session, in which this query has just been issued. If we now (in a different session) connect as SYSTEM on the DB8 database, and issue a select across the database link, we’ll see this: SID SERIAL# USERNAME ---------- ---------- -----------------------------7 5 SCOTT 8 12 SYSTEM Notice the new session here: it’s listed as Scott’s even though I just stressed the fact that I’d connected to the DB8 database as SYSTEM. The reason for that again lies in the way we issued the ‘create database link’ command. To remind you, it was: Create database link db9.aldeburgh.local Connect to scott identified by tiger Using ‘db9’; So the link itself dictates that, whoever we happen to be connected as on DB8, the connection to the DB9 database must be made as Scott.

Copyright © Howard Rogers 2002

3/3/2002

Page 6 of 10

Database Links

Advanced Administration Tips

Things also get interesting when you select the OSUSER column from v$session, instead of the plain old USERNAME: USERNAME -------SCOTT SYSTEM

OSUSER ---------------------ALDEBURGH\MONTEVERDI$ ALDEBURGH\howardjr

Notice that the OSUSER column contains the name of the Windows 2000 machine hosting the DB8 database (MONTEVERDI), not (as in the last line displayed) the actual operating system name of an individual user. This is about the only way to spot the difference between a ‘real’ user connected to the remote database in the normal fashion and a user connecting to it via a database link.

Managing Database Links Having created a Database Link, you need to be able to find out which links have been created, and how to modify them or get rid of them altogether. To view which links exist, select from the DBA_DB_LINKS views (there are equivalent USER_ and ALL_ views, of course): SQL> select * from dba_db_links; OWNER DB_LINK USERNAME HOST CREATED ------- --------------------- ----------- --------- --------SYSTEM DB9.ALDEBURGH.LOCAL SCOTT db9 03/MAR/02 Notice that the ‘host’ column doesn’t tell you the host at all (not in the sense of the machine being connected to, at least). Rather, it tells you the tnsnames alias being used to make the connection. Be aware that selecting from the same view in the DB9 database produces no rows at all. In other words, you can’t tell from within a database what other databases might have links connecting to it (unless those links are used, of course, at which point –as we’ve seen- it becomes obvious in v$session). You can’t actually modify a Database Link once it’s been created. Instead, you have to drop the entire thing, and re-create it from scratch: SQL> drop database link db9.aldeburgh.local; Database link dropped.

Copyright © Howard Rogers 2002

3/3/2002

Page 7 of 10

Database Links

Advanced Administration Tips

SQL> create database link db9.aldeburgh.local 2 connect to hr identified by hr 3 using 'db9'; Database link created. Finally, you need to know how to close down a database link once it’s been finished with – remember that v$session shows the link from DB8 however many hours have passed since the last ‘select * from [email protected]’! That means there’s a Server Process ticking away on DB9, chewing up some memory and CPU cycles, which we might prefer not to waste. Issue this command (again, this is done on the database to which you are directly connected –in our case, DB8): Alter session close database link db9.aldeburgh.local; That closes the link (but obviously doesn’t drop it from the database altogether). Links are re-opened by performing some select or DML activity across them again. One minor nasty with this: you can’t close a link (hopefully for obvious reasons) if someone is in the middle of doing some work across it. If you try, you’ll get an ORA-02080: database link is in use error message. What’s a bit tricky about this is that a select statement counts as doing some work –and selects have to be committed before the link is determined not to be in use. As a simple example: SQL> select empno, ename, sal from [email protected]; EMPNO ENAME SAL ---------- ---------- ---------7698 BLAKE 2850 7839 KING 5000 7844 TURNER 1500 7900 JAMES 950 7902 FORD 3000 7934 MILLER 1300 9999 ROGERS 8888 7 rows selected. SQL> Alter session close database link db9.aldeburgh.local; ERROR: ORA-02080: database link is in use SQL> commit; Commit complete. Copyright © Howard Rogers 2002

3/3/2002

Page 8 of 10

Database Links

Advanced Administration Tips

SQL> Alter session close database link db9.aldeburgh.local; Session altered. That could prove a bit awkward to remember if, like me, you’ve grown accustomed to ordinary select statements not requiring commits to make them do their stuff! Obviously, it’s much less of a hassle with ordinary DML statements. Notice, too, that the command is an ‘alter session’ one. That means the User wishing to close the link has to possess the alter session privilege. It also means that a link that one User happens to be using to perform a substantial piece of DML cannot be summarily closed by someone else (thus causing general consternation, transaction failure and massive rollback all round!). The link is session-specific, and only your own session can be used to close it.

Making Links Easier to Use One final point I’ll mention briefly here. It’s frankly a bit of a pain always to have to qualify your select statements (or DML) with long-winded fully qualified link names. Remember that public synonyms and views created in the local database can come to your rescue: SQL> 2 3 4

create view fred as select empno, ename, job from [email protected] where sal>500;

View created. SQL> select * from fred; EMPNO ---------7499 7521

ENAME ---------ALLEN WARD

JOB --------SALESMAN SALESMAN

…is one approach. You might not like having to create lots of views in DB8 to reference tables in the DB9 database, however. Instead, you could try this: SQL> create public synonym ginger 2 for [email protected]; Synonym created. Copyright © Howard Rogers 2002

3/3/2002

Page 9 of 10

Database Links

Advanced Administration Tips

SQL> select * from ginger where rownum<4; EMPNO ---------7369 7499 7521

ENAME ---------SMITH ALLEN WARD

JOB MGR HIREDATE --------- ---------- --------CLERK 7902 17/DEC/80 … SALESMAN 7698 20/FEB/81 … SALESMAN 7698 22/FEB/81 …

Both of these approaches to what is laboriously known as ‘location transparency’ mean that Users don’t get RSI everytime they want to access remotely-stored data!

Copyright © Howard Rogers 2002

3/3/2002

Page 10 of 10