Changing global_dbname

[Howard J. Rogers]

OK, I give up.

If I ever knew, I've forgotten. How do you change the global database name?

The problem is that a database link pointed at DB9.aldeburgh.local (a
version 9 database) from DB8.aldeburgh.local (a version 8 database) keeps
popping up, whenever used in a select statement, with the error ORA-02085:
database link DB9.ALDEBURGH.LOCAL connects to DB9.US.ORACLE.COM.

In theory, global dbname is constructed from db_domain and db_name in the
relevant init.ora... well, my init.ora for DB9 says db_name=DB9,
db_domain=aldeburgh.local, so I've spent hours chasing my tail trying to
work out why the damn thing still thinks it's pointing to a us.oracle.com
domain. I confess the database was inadvertently created with global
database name set to merely DB9, the same as the service name. If I create
a brand new database specifying an appropriate 'X.aldeburgh.local' global
database name, then everything works as advertised.

Listener.ora says

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = DB9.aldeburgh.local)
(ORACLE_HOME = d:\oracle\ora91)
(SID_NAME = DB9)
)
)

Sqlnet.ora doesn't have a default domain name set. And connections made
directly in the form 'connect system/manager@db9' work perfectly fine.

What blindingly obvious nugget have I overlooked?

Regards
HJR
--
----------------------------------------------
Resources for Oracle: http://www.hjrdba.com
===============================

[Thomas Kyte]

In article <a5rnm8$qdg$1...@lust.ihug.co.nz>, "Howard says...

alter database rename global_name to NEW.NAME.AND.DOMAIN

if you leave the domain off -- it'll pick it up from the last name. Consider:

ops$tk...@ORA9I.WORLD> select * from global_name;

GLOBAL_NAME
------------------------------
ORA9I.WORLD

ops$tk...@ORA9I.WORLD> show parameter domain

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_domain string foo.bar
ops$tk...@ORA9I.WORLD> alter database rename global_name to test;

Database altered.

ops$tk...@ORA9I.WORLD> select * from global_name;

GLOBAL_NAME
------------------------------
TEST.WORLD

ops$tk...@ORA9I.WORLD> alter database rename global_name to test.foo.bar;

Database altered.

ops$tk...@ORA9I.WORLD> select * from global_name;

GLOBAL_NAME
------------------------------
TEST.FOO.BAR

ops$tk...@ORA9I.WORLD>

Just use the fully qualified name DB9.ALDEBURGH.LOCAL in your rename command.

--
Thomas Kyte (tk...@us.oracle.com) http://asktom.oracle.com/
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/
Opinions are mine and do not necessarily reflect those of Oracle Corp

[RSH]

That is what I have done, too.

It's a pain, you have to remember to cover all the basis and not leave a
change out, or you'll have a mess.

This Thomas guy, he ought to get a job with Oracle,
I think. :)

RSH

"Thomas Kyte" <tk...@oracle.com> wrote in message
news:a5rpe...@drn.newsguy.com...

[Mladen Gogala]

On Sun, 03 Mar 2002 10:57:26 -0500, RSH wrote:

> That is what I have done, too.
>
> It's a pain, you have to remember to cover all the basis and not leave a
> change out, or you'll have a mess.
>
> This Thomas guy, he ought to get a job with Oracle, I think. :)

I believe that he has one, already.
--
Mladen Gogala

[Xuequn Xu]

In addition to what Tom Kyte's suggested (which is very good), You may also
want to check the value of init.ora parameter global_names. Most likely
yours is set to TRUE, and that forces you to name the dblink the same as
the global database name (i.e. db_name.db_domain). If you change it to false
("alter system set global_names = false"), then you can freely name your
dblink with anything. However, this might not be the "recommended" approach,
because advanced replication requires you to set global_names = TRUE.

In summary, "global_names" is a init.ora parameter, with values TRUE or FALSE;
while "GLOBAL_NAME" is a data dictionary view with one row, one column, showing
the global name of the database. BOTH are dynamically changible. BOTH have
certain effects on your naming of dblinks.

This is a very confusing aspect of Oracle, I have to say.

Thomas Kyte (tk...@oracle.com) wrote:
: In article <a5rnm8$qdg$1...@lust.ihug.co.nz>, "Howard says...

[Howard J. Rogers]

Thanks, I knew about 'global_names=true' -and I deliberately wanted that
set, precisely because it's a strong Oracle recommendation that it should
be -as you say, Advanced Replication, for one, won't work without it.

I agree that the concatenation of db_name + db_domain to derive a global
database name, and the separate existence within the database of a 'fixed'
global name, can lead to all sorts of confusion and grief. On the other
hand, I shouldn't have been daft enough to leave out a properly-qualified
global name when I created the database in the first place!

Regards
HJR
--
----------------------------------------------
Resources for Oracle: http://www.hjrdba.com
===============================

"Xuequn Xu" <x...@informa.bio.caltech.edu> wrote in message
news:a60fjc$b...@gap.cco.caltech.edu...

[RSH]

Sorry, that was sarcasm and a compliment both, sorry I was too subtle with
it.

I rather imagine anyone with a login that ends in the domain .oracle.com
might work for the company.

RSH.

"Mladen Gogala" <mgo...@earthlink.net> wrote in message
news:pan.2002.03.03.13...@earthlink.net...