Re: Changing global_dbname

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" <xux_at_informa.bio.caltech.edu> wrote in message
news:a60fjc$b4g_at_gap.cco.caltech.edu...


> 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 (tkyte_at_oracle.com) wrote:

> : 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

>