Network domain and database domain.
A reader, September 25, 2002 - 12:39 pm UTC
Tom:
Can we use a database domain other than the network domain? (I know we can still connect, but is it advisable?)
We have all our unix database servers under network domain domain say xyz.com (FQN like: server1.xyz.com, server2.xyz.com etc..). The db_domain of all the databases are set to .world (explicitely in init.ora, I don't know why, but people here are against changing it..). We have names.default_domain=world in sqlnet.ora and we can connect using sqlplus. Now, I was trying to install a monitoring tool and it failed to connect because the network domain and database domain does not match! The tech support confirmed that it is the problem! Why would that be a problem if Oracle sqlplus can connect, I asked. They just said that is how it works!
Can you please throw some light? What is the purpose of the domain_name init.ora parameter? Why is the difference in network domain and database name not a problem for Oracle, while it is for some? Also, are there any other problems if we set the db_domain different from network domain name?
TIA,
AR
September 25, 2002 - 1:05 pm UTC
They are confused. The db domain affects database links only.
It should have no affect on a 3rd party tools ability to connect -- IN FACT, the third party tool cannot even tell the domain until *after* they connect.
Anyway, maybe this tool just needs a tnsnames entry setup special for them. try that. Maybe they are just taking your tnsnames entry you give them and adding the domain afterwords (my domains are .worlds and some .oracle.com but my "network" domain is us.oracle.com -- I've never ever had an issue)
A reader, September 25, 2002 - 4:47 pm UTC
That was the fix! (This is pretty boring, how come you *always* have the right answers? Just kidding...)
Could you please explain the purpose of db_domain parameter? Is it just for organizing your databases heirarchically? Is there anyway we can inherit the network domain name automatically as db_domain?
Thanks for hosting this great site.
September 25, 2002 - 6:39 pm UTC
The db_domain is something I don't generally really use or even pay much attention to.
from the reference guide:
...
In a distributed database system, DB_DOMAIN specifies the logical location of the database within the network structure. You should set this parameter if this database is or ever will be part of a distributed system. The value consists of the extension components of a global database name, consisting of valid identifiers, separated by periods. Oracle Corporation recommends that you specify DB_ DOMAIN as a unique string for all databases in a domain.
<note from tom -- that means the db domain is recommended to NOT be your network domain but named after your "group">
This parameter allows one department to create a database without worrying that it might have the same name as a database created by another department. If one sales department s DB_DOMAIN is JAPAN.ACME.COM, then their SALES database (SALES.JAPAN.ACME.COM) is uniquely distinguished from another database with DB_NAME = SALES but with DB_DOMAIN = US.ACME.COM.
If you omit the domains from the name of a database link, Oracle expands the name by qualifying the database with the domain of your local database as it currently exists in the data dictionary, and then stores the link name in the data dictionary. The characters valid in a database domain name are: alphanumeric characters, underscore (_), and number sign (#).
<note from tom -- that part is a little vital -- and is the time when the db_domain most affects you>
.........
domain name in tnsnames.ora
Oracle-leo, November 08, 2002 - 8:24 am UTC
When i add an alias in tnsnames.ora using net8 configuration asst on win2k/8.1.7, the alias is suffixed with <alias>.world or <alias>.in.oracle.com.
But when i connect using system/manager@alias, it throws error as tns could not resolve name and expects full name like <alias>.in.oracle.com.
Is it normal behavior or there is some problem somewhere. How can i avoid suffixing of domain name in tnsnames.ora.
Thanks,
November 08, 2002 - 9:07 am UTC
edit your sqlnet.ora and add a
NAMES.DEFAULT_DOMAIN = in.oracle.com
and you won't have to supply alias.in.oracle.com - just alias.
Why suddently cannot work?
Junior, May 23, 2003 - 3:19 pm UTC
Tom:
I created database link and worked fine in the very begining. However, couple weeks later, this link is not working any more. I worked on both database every day, and I am able to switch between these 2 databases by using connect scott/scott@db1 and connect scott/scott@db2.
The only thing I did is I have a batch file which shoutdown db1 every night and make a cold backed up. It will bring up the db1 after the backed up is done.
Will this cause problem? Do you know why the link suddently not work? I am using Oracle 9.2. and running on Window 2000.
Thank you
May 24, 2003 - 9:54 am UTC
hey, my car won't start this morning.
its worked every day.
No, I won't tell you the make or model.
I won't tell you the symptoms.
I won't tell you anything really....
so, tell me -- why won't my car start?
(sorry, could not resist. think about it -- what possible answer could I sitting here in my office -- without the ability to see your computer screen and read the error message -- give you? no error code, no messages, no cut and paste, NOTHING)
Database v/s car
A reader, May 25, 2003 - 5:50 am UTC
HA HA HA HA HA HA HA
database links
Junior, May 27, 2003 - 8:42 am UTC
Tom:
Sorry, you are right, I should paste error message here.
SQL> select db_link from all_db_links;
DB_LINK
--------------------------------------------------------------------------
db14
SQL> desc person@db14;
ERROR:
ORA-12545: Connect failed because target host or object does not exist
I checked tnsname and I believe it should be fine. I am able to switch the connection between those 2 databases. This links used to work fine, but suddently it just not worked. I droped this link and I am able to recreate it again. However, it still not works. So...
Thanks.
May 27, 2003 - 9:35 am UTC
someone changed the hostname or ip address of your server.
goto your tnsnames.ora file, verify it is correct (and it won't be). either the hostname/ipaddress will be wrong.
[tkyte@tkyte-pc Desktop]$ oerr ora 12545
12545, 00000, "Connect failed because target host or object does not exist"
// *Cause: The address specified is not valid, or the program being
// connected to does not exist.
// *Action: Ensure the ADDRESS parameters have been entered correctly; the
// most likely incorrect parameter is the node name. Ensure that the
// executable for the server exists (perhaps "oracle" is missing.)
// If the protocol is TCP/IP, edit the TNSNAMES.ORA file to change the
// host name to a numeric IP address and try again.
[tkyte@tkyte-pc Desktop]$
database link between 7.1.5 and 9.2
John, June 05, 2003 - 9:06 pm UTC
Hi Tom,
We have a database server 9.0.1. We want to upgrad to 9.2.
We must have a database link to one oracle 7.1.5 database(our client database, they plan to upgrade it next year. We don't have the control.). Through the link, we use select statement to retrieve infomation from 2 tables frequently(almost "select" every couple of seconds), no other complicated stuff. I heard about 9.2 has issues to access v7 database. Could you please confirm that 9.2 can't "select" from v7 through the database link at regular basis?
Thanks.
June 05, 2003 - 9:26 pm UTC
you cannot, in production, connect 9.2 to 7.x
not supported.
not supportable.
Remote database users
A reader, September 09, 2003 - 5:54 am UTC
Hi Tom,
We have three oracle sites let's say site1, site2 and site3. Currently every site connects to each other using database links for transaction processing. I am working at site1.
How can I know that how many users (with details) or sessions are currently connected to site1 from site2 or site3??
Thanks in advance
September 09, 2003 - 11:44 am UTC
look at the program column in v$session
connect 9.2 to 7.x
Jones, January 15, 2004 - 11:52 pm UTC
you cannot, in production, connect 9.2 to 7.x
Is there any work around ? or any suggestions ?
example - export the data into flat files from 7.x, ftp to the 9.2 box and load the data files into 9.2
Your response is highly appreciated
January 16, 2004 - 2:50 am UTC
7.x has been out of support for so long -- so so long. There is no way to reliably connect 7.x and 9.2 (and above).
A reader, March 16, 2004 - 1:38 am UTC
I created db link using the below statement. (ver 9.2.0)
create public database link DL_DEVDMDB connect to GECIS_EDW identified by "edw~123" using
'(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = giahyd004)(PORT = 1521))
)
(CONNECT_DATA = (SID = devdmdb)(SERVER=DEDICATED))
)
'
I dont have the any entry related to this server in TNS ( Hope not required).
Pls confirm if i need to do any changes in the server setting or this is all set to create a db link.
The problem is, i created the link 2 days back, but today i dont see it. Could have been deleted by some one or i missed out anything.
Sorry for my ignorance. Thanks for your time and consideration
March 16, 2004 - 7:45 am UTC
if the link "isn't there" someone dropped it on you.
Yes, that works -- you do not need to have a tns entry, you can put it right in there like that.
Thanks ...
A reader, March 16, 2004 - 8:12 am UTC
link puzzle
robert, March 30, 2004 - 9:47 am UTC
tom, How come I have referenced link names not in the all_db_links list ?
thanks
SQL> select REFERENCED_LINK_NAME from dba_DEPENDENCIES where REFERENCED_LINK_NAME is not null order by 1;
REFERENCED_LINK_NAME
--------------------------------------------------------------------------------
CTRACK.ZITE.COM
CTRACK.ZITE.COM
DBLINK_HFOD.ZITE.COM
JSH_EPDB_LNK.ZITE.COM
JSH_EPDB_LNK.ZITE.COM
JSH_EPDB_LNK.ZITE.COM
LNK_COMPANY.ZITE.COM
LNK_COMPANY.ZITE.COM
LOADPOC.ZITE.COM
LOADPOC.ZITE.COM
10 rows selected
SQL> select db_link from all_db_links order by 1;
DB_LINK
--------------------------------------------------------------------------------
JSH_EPDB_LNK.ZITE.COM
LNK_CNTXT_USER.ZITE.COM
LNK_COMPANY.ZITE.COM
LNK_HFOD_LIVE.ZITE.COM
LOADPOC.ZITE.COM
MANDAREGION_DBLINK.ZITE.COM
NEWS_DBLINK.ZITE.COM
TEST.ZITE.COM
8 rows selected
March 30, 2004 - 10:55 am UTC
all_db_links are dblinks you are allowed to see the definition of.
scott@ORA9IR2> select count(*) from all_db_links;
COUNT(*)
----------
0
scott@ORA9IR2> @connect /
Connected.
ops$tkyte@ORA9IR2> select count(*) from all_db_links;
COUNT(*)
----------
1
different people get to see different things.
domain name
vivek saxena, April 19, 2005 - 1:17 am UTC
Tom,
I have a query for domain name. I installed oracle 9.2 on my PC (OS windows )and created a database by name of "albert". I have following setting in my init file
###########################################
# Database Identification
###########################################
db_domain=""
db_name=albert
and sqlnet.ora has
NAMES.DEFAULT_DOMAIN = www.netedge.com
whereas the global name is ALBERT.US.ORACLE.COM
vivek@ALBERT.US.ORACLE.COM> select global_name from global_name;
GLOBAL_NAME
--------------------------
ALBERT.US.ORACLE.COM
I want to know
a) from where DB has picked up the domain 'US.ORACLE.COM'
b) can i change the doamin name like global name
Regards
Vivek
April 19, 2005 - 7:25 am UTC
alter database rename global_name to a.b.c.d;
it got it when it was installed/created.
domain name
vivek, April 19, 2005 - 8:33 am UTC
Tom,
Some where in thread you mentioned that '.world' is default domain, so how come 'us.oracle.com' comes into picture. Is it stored somewhere in dictionary. Can i change domain name like you have shown for global name.
As i created a database link via OEM by name of 'sunnet' and it added 'us.oracle.com' to it, 'sunnet.us.oracle.com'.
So my domain name is "" (from init.ora) or 'us.oracle.com'
Regards
Vivek
April 19, 2005 - 9:01 am UTC
it got it from your global name, it happened during install, one of the configuration files you had -- had it in there (overriding the default). A simple rename will change it.
invalid ROWID ORA-02063
anupam pandey, October 12, 2009 - 5:24 am UTC
Hi Tom ,
I am executing a select query over a db link to dump the data from remote database .
The query was running fine but from few days back it has started throwing "ORA-01410: invalid ROWID ORA-02063:"
I fired the query from outside and it ran fine without problem .
But again when next day process was running that select again caused the problem .
Following is the query .
SELECT /*+ driving_site(a) LEADING(a b) */
a.order_id,
b.line_id,
translate(c.pos,' ','+') ,
a.id
FROM scott.a@a a1,
scott.b@a b1 ,
scott.c@a c1
WHERE a.delivery_key = b.delivery_key
AND b.key = c.key
AND a.date_id = <DATE>
Please suggest why this problem is coming .
Thanks And Regards,
Anupam
Why not skip using db_domain
A reader, April 08, 2010 - 10:10 pm UTC
Tom - You said a few posts up you basically never bother with db_domain and don't have much use for it. I feel the same way because it just makes everything more complicated. Witness all these posts about global_name and links etc., But the Oracle document you posted suggests using it so that you don't end up with duplicate databases in an enterprise environment like 'SALES' etc., I am inheriting a migrated environment that USED to use a db_domain as an account standard. Whether to use it in a new environment is up to me. I'm inclined to skip it. If someday, another organization comes up with a duplicate database name, kind of a long shot, I would think the tnsnames.ora and IPs would be different and although it might be confusing, it could be addressed later. What are your thoughts? Any real downside risk to not implementing it? I would think if it REALLY became necessary, your links would change, and any views or mviews referring to links etc., a major effort to be sure, but skipping it avoids all this confusion. Your two cents?
April 13, 2010 - 8:33 am UTC
... If someday, another organization comes up with a
duplicate database name, kind of a long shot, I would think the tnsnames.ora
and IPs would be different and although it might be confusing, it could be
addressed later....
in a distributed environment where database A talks to database B, you want them to have globally unique, permanent names - to be completely unambiguous - that is their point. And using a db_domain that is unique to your group would "help" that.
that is all.
DB Link Limitaions.
Stuar Broad, November 03, 2010 - 1:46 am UTC
Tom,
First of all thanks for enlighting us as always with your huge knowledge.
I have a question ,
1.Is is always FTS when we do Select from a remote table.
2. Can we give Partition name in select from a remote database using DB link.
3. Can you enlighten us the things which are not possible to perform using DB link which can be performed in Local database.
It would be helpful if you can guide to any oracle docs or your own book which explains operation over DB link in details.
November 03, 2010 - 9:44 am UTC
1) no, indexes are frequently used and are always available.
2) no
ops$tkyte%ORA11GR2> select * from t@ora11gr2@loopback partition(part2);
select * from t@ora11gr2@loopback partition(part2)
*
ERROR at line 1:
ORA-14100: partition extended table name cannot refer to a remote object
3) you'd have to "go to the thing" you want to do in the documentation and see if it is permitted. Most things 'are'. There is not a master list of what isn't - a feature that isn't would be (should be) documented as such.
for example:
http://docs.oracle.com/docs/cd/E11882_01/server.112/e17118/sql_elements009.htm#sthref801 documents the fact that #2's answer is "no"
DB Link Limitaions.
Stuar Broad, November 03, 2010 - 1:46 am UTC
Tom,
First of all thanks for enlighting us as always with your huge knowledge.
I have a question ,
1.Is is always FTS when we do Select from a remote table.
2. Can we give Partition name in select from a remote database using DB link.
3. Can you enlighten us the things which are not possible to perform using DB link which can be performed in Local database.
It would be helpful if you can guide to any oracle docs or your own book which explains operation over DB link in details.