Re: Changing global_dbname

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 2 Mar 2002 16:06:27 -0800
Message-ID: <a5rpe302p1g@drn.newsguy.com>

In article <a5rnm8$qdg$1_at_lust.ihug.co.nz>, "Howard says...
>
>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_at_db9' work perfectly fine.
>
>What blindingly obvious nugget have I overlooked?
>
>Regards
>HJR
>--
>----------------------------------------------
>Resources for Oracle: http://www.hjrdba.com
>===============================
>
>
>
>

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$tkyte_at_ORA9I.WORLD> select * from global_name;

GLOBAL_NAME

---

ORA9I.WORLD ops$tkyte_at_ORA9I.WORLD> show parameter domain

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_domain                            string      foo.bar

ops$tkyte_at_ORA9I.WORLD> alter database rename global_name to test;

Database altered.

ops$tkyte_at_ORA9I.WORLD> select * from global_name;

GLOBAL_NAME

---

TEST.WORLD ops$tkyte_at_ORA9I.WORLD> alter database rename global_name to test.foo.bar;

Database altered.

ops$tkyte_at_ORA9I.WORLD> select * from global_name;

GLOBAL_NAME

---

TEST.FOO.BAR ops$tkyte_at_ORA9I.WORLD>

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

--
Thomas Kyte (tkyte@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 

Received on Sat Mar 02 2002 - 18:06:27 CST