Oracle 11g: Change Database Name

Hi guys,

Need to do this task, here is my env:
Task: change the db name from abcd9 to abcd
Database: Oracle 11g R1 (Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production)
O/S: Red Hat Enterprise Linux Server release 5.4 (Tikanga)

can any one guide me to the best possible way to do this task.

Best regards.
el123Asked:
Who is Participating?
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
Just for grins try removing '@abcd9' and run locally.

Do you remember what .ora files you modified?  Maybe you got one you really shouldn't have.
0
 
slightwv (䄆 Netminder) Commented:
I can't remember if there is a new way in 11g to do this or not.

Here's the 10g steps:
http://sabdarsyed.blogspot.com/2007/03/changing-oracle-database-name-in-oracle.html
0
 
el123Author Commented:
it is giving me error at, yeah I got the same, when googled ;-)

oracle@vmoracle:oracle$ nid TARGET=sys/password@abcd9 DBNAME=abcd SETNAME=YES

DBNEWID: Release 11.1.0.7.0 - Production on Thu Apr 8 12:48:37 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


NID-00106: LOGIN to target database failed with Oracle error:
ORA-01017: invalid username/password; logon denied



Change of database name failed during validation - database is intact.
DBNEWID - Completed with validation errors.

where as the pwd is correct, able to connect using
sqlplus
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
slightwv (䄆 Netminder) Commented:
>>it is giving me error at, yeah I got the same, when googled
You asked for steps.  That provided them better than I could have.

>>it is giving me error at
This is a different issue.  Off the top of my head that should work.  I've only ever done this once and it worked like a charm.

The default in 11g is case sensitive passwords.  I wonder if nid does something different than sqlplus?

Check the value of the parameter: sec_case_sensitive_logon.  If you don't remember the exact case of the sys password try setting that parameter to FALSE and see if nid runs.



0
 
el123Author Commented:
no biggie ... it is still giving error, after setting it to False ... :-(
0
 
el123Author Commented:
excellent & thanks ... you are right, it works like a charm, after I removed this @... ;-)

but now I'm confused, that it did it so amazingly ... now what ... what needs to be done.

1. take the backup
2. shutdown / restart to make sure every thing is good

anything else, change all the dir names from abcd9 to abcd ... or is there any other steps that need to be taken care, please suggest.

And again, thanks for your assistance.
0
 
slightwv (䄆 Netminder) Commented:
>>works like a charm, after I removed this @

It was an educated guess.  Even the online docs say to use it.

My guess is you changed the tnsnames.ora file and it confused nid.

>>that it did it so amazingly ... now what ... what needs to be done.

I remember being impressed when I did it.  I set up a Streams replication environment and used this to 'clone' my primary one.

I have a real nice checklist to follow that I created when I was testing it.  I can't find it now to see if I did anything special after that.  I'm double check the online docs to make sure.

but #1 and #2 sound reasonable.
0
 
el123Author Commented:
but one problem, I think missing some thing here, please correct, thanks.

after doing this activity, need to shutdown the db
shutdown immediate

SQL> STARTUP MOUNT;
SQL > ALTER SYSTEM SET DB_NAME=uncdev SCOPE=spfile;
SQL> SHUDOWN IMMEDIATE;

create pwd file

right, and then startup again ... right
now when I checked
SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string
SQL> show parameter pfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string
SQL>

it is coming all null, and also when get the locations of datafiles, logfile and temp files, it is still the same old, obviously the dir, I didn't rename.

how to fix this spfile/pfile issue, and then need to correct at o/s level too, please suggest/advice.
Thanks again.
0
 
el123Author Commented:
and also then need to configure dbconsole too, as it is configure with it earlier, please advice, how to reconfig it too.
0
 
el123Author Commented:
update: I did create spfile from pfile
and then shutdown and restart

now it is running using spfile

now the other 2 tasks: how correct the locations of files
and 2nd to configure dbconsole, need your assistance, thanks.
0
 
el123Author Commented:
thanks ... I corrected the files too, and now restarting the db
0
 
el123Author Commented:
need your help for dbconsole,
I don't need it, but other dba is requested to configure,
can you please guide me to any link or any thing, from where I can reconfigure or even recreate the repos, because I've been trying to recreate, drop or reconfigure, but giving errors again and again.

Thanks again for your help & assistance.
best regards.
0
 
slightwv (䄆 Netminder) Commented:
>>and also when get the locations of datafiles, logfile and temp files, it is still the same old, obviously the dir, I didn't rename.

nid doesn't have anything to do with datafile locations.  You need to do this manually.  Either with rename datafile or recreating the controlfiles.  Either works.

>>need your help for dbconsole

Check out:
http://www.scribd.com/doc/325793/How-to-recreate-database-control-repository
0
 
el123Author Commented:
thanks for this, but I've already tried all these ... all failed
0
 
slightwv (䄆 Netminder) Commented:
Can you past the output from: emca -deconfig dbcontrol db -repos drop


I'm thinking there is still an issue in the tnsnames.ora or listener.ora files but I can't confirm this until I see the errors.
0
 
el123Author Commented:
here it is ...

oracle@vmoracle:oracle$ emca -deconfig dbcontrol db -repos drop

STARTED EMCA at Apr 13, 2010 2:08:14 PM
EM Configuration Assistant, Version 11.1.0.7.0 Production
Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Enter the following information:
Database SID: hyper
Listener port number: 1521
Password for SYS user:  
Password for SYSMAN user:  
Password for SYSMAN user:  
Do you wish to continue? [yes(Y)/no(N)]: y
Apr 13, 2010 2:08:35 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /dbms/app/oracle/cfgtoollogs/emca/HYPER/emca_2010_04_13_14_08_14.log.
Apr 13, 2010 2:08:36 PM oracle.sysman.emcp.EMDBPreConfig performDeconfiguration
WARNING: EM is not configured for this database. No EM-specific actions can be performed.
Apr 13, 2010 2:08:37 PM oracle.sysman.emcp.ParamsManager checkListenerStatusForDBControl
WARNING: Error initializing SQL connection. SQL operations cannot be performed
Apr 13, 2010 2:08:37 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Dropping the EM repository (this may take a while) ...
Apr 13, 2010 2:08:38 PM oracle.sysman.emcp.util.PlatformInterface executeCommand
WARNING: Error executing /dbms/app/oracle/product/11.1.0/db_1/sysman/admin/emdrep/bin/RepManager -connect (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=vmpawpaw)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=HYPER))) -repos_user SYSMAN -action drop -verbose -output_file /dbms/app/oracle/cfgtoollogs/emca/HYPER/emca_repos_drop_2010_04_13_14_08_37.log
Apr 13, 2010 2:08:38 PM oracle.sysman.emcp.EMReposConfig invoke
SEVERE: Error dropping the repository
Apr 13, 2010 2:08:38 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Refer to the log file at /dbms/app/oracle/cfgtoollogs/emca/HYPER/emca_repos_drop_<date>.log for more details.
Apr 13, 2010 2:08:38 PM oracle.sysman.emcp.EMConfig perform
SEVERE: Error dropping the repository
Refer to the log file at /dbms/app/oracle/cfgtoollogs/emca/HYPER/emca_2010_04_13_14_08_14.log for more details.
Could not complete the configuration. Refer to the log file at /dbms/app/oracle/cfgtoollogs/emca/HYPER/emca_2010_04_13_14_08_14.log for more details.
oracle@vmoracle:oracle$


0
 
slightwv (䄆 Netminder) Commented:
Can you paste the results of: tnsping hyper

I also suggest using IP address in the listener.ora and tnsnames.ora not machine name.
0
 
el123Author Commented:
oracle@vmoracle:admin$ tnsping hyper

TNS Ping Utility for Linux: Version 11.1.0.7.0 - Production on 13-APR-2010 15:42:19

Copyright (c) 1997, 2008, Oracle.  All rights reserved.

Used parameter files:
/dbms/app/oracle/product/11.1.0/db_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 198.10.20.78)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = hyper)))
OK (0 msec)
oracle@vmoracle:admin$
0
 
slightwv (䄆 Netminder) Commented:
Does the machine name vmpawpaw have the ipaddress 198.10.20.78?

Notice emca used hostname in the connection info and tnsping used IP address. I think there's still a config file issue somewhere.
0
 
el123Author Commented:
here is the info:

oracle@vmoracle:admin$ nslookup vmoracle
Server: 198.10.20.4
Address: 198.10.20.4#53

Name: vmoracle.<our domain>.com
Address: 198.10.20.101

oracle@vmoracle:admin$ nslookup vmpawpaw
Server: 198.10.20.4
Address: 198.10.20.4#53

Name: vmpawpaw.<our domain>.com
Address: 198.10.20.78

oracle@vmoracle:admin$ cat /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
::1 localhost.localdomain localhost
127.0.0.1 localhost
198.10.20.101 vmoracle
198.10.20.78 vmpawpaw

oracle@vmoracle:admin$

and listener.ora

# listener.ora Network Configuration File: /dbms/app/oracle/product/11.1.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 198.10.20.78)(PORT = 1521))
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC=
(SID_NAME=t11)
(ORACLE_HOME=/dbms/app/oracle/product/11.1.0/db_1)
)
(SID_DESC=
(SID_NAME=webprod)
(ORACLE_HOME=/dbms/app/oracle/product/11.1.0/db_1)
)
(SID_DESC=
(SID_NAME=hyper)
(ORACLE_HOME=/dbms/app/oracle/product/11.1.0/db_1)
)
(SID_DESC=
(SID_NAME=prod)
(ORACLE_HOME=/dbms/app/oracle/product/11.1.0/db_1)
)
)

and sqlnet.ora

# sqlnet.ora Network Configuration File: /dbms/app/oracle/product/11.1.0/db_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

don't know what is the problem:

oracle@vmoracle:admin$ lsnrctl services

LSNRCTL for Linux: Version 11.1.0.7.0 - Production on 19-APR-2010 14:32:30

Copyright (c) 1991, 2008, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=198.10.20.78)(PORT=1521)))
Services Summary...
Service "hyper" has 1 instance(s).
Instance "hyper", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "prod" has 1 instance(s).
Instance "prod", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "t11" has 1 instance(s).
Instance "t11", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "webprod" has 1 instance(s).
Instance "webprod", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:1 refused:0
LOCAL SERVER
The command completed successfully
oracle@vmoracle:admin$

all statuses are showing unknown, where as I can tnsping from my desktop to this linux box

C:\Documents and Settings\el123>tnsping prod

TNS Ping Utility for 32-bit Windows: Version 11.1.0.6.0 - Production on 19-APR-2
010 14:33:12

Copyright (c) 1997, 2007, Oracle. All rights reserved.

Used parameter files:
C:\app\el123\product\11.1.0\db_1\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = vmpawpaw
)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = prod)))
OK (20 msec)
0
 
slightwv (䄆 Netminder) Commented:
Thanks for all that but remember tnsping only talks to the listener.  not the actual instance.

I'm not sure what else to have you try.  The problem has to be in the old config files for the old repository.


check the log file produced to see if anything in there points to why it's not working:
Could not complete the configuration. Refer to the log file at /dbms/app/oracle/cfgtoollogs/emca/HYPER/emca_2010_04_13_14_08_14.log

You might try manually removing dbConsole.  There are several links out there on how to do this.

Check out:
2).Delete DB Control Configuration Files Manually:

http://arjudba.blogspot.com/2008/04/how-to-drop-create-and-recreate-db.html

also:
http://yogeedba.blogspot.com/2008/02/manual-configuration-of-dbconsole-em.html
0
 
el123Author Commented:
thank you slightwv:

but can you assist at:
http://www.experts-exchange.com/Database/Oracle/11.x/Q_25961344.html

I'm kind of lost :-(

0
All Courses

From novice to tech pro — start learning today.