Solved

How do I edit a control file to use the new database name?

Posted on 2008-10-31
7
1,889 Views
Last Modified: 2013-12-19
How do I edit a control file to use the new database name? I recently created a database and was then asked to change the ORACLE SID/database name. Everything seems to be ok I was able to restart the database but got the error...

ORA-01103: database name 'xxx' in controlfile is not 'yyy'.


xxx is the old name and yyy is the new database name I am trying to change it to.
0
Comment
Question by:la_colibri
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 10

Expert Comment

by:dfke
ID: 22853123
The control file still has the  xxx.so Database Identifier so it needs to be changed by creating
a new control file from a backup control file.

Take a backup control file when the database is up.

SQL> alter database backup controlfile to trace;
Database altered.

Go to UDUMP location and edit the control file backup and save to CONTROL.SQL

Change to be made is
CREATE CONTROLFILE SET DATABASE "yyy" NORESETLOGS ARCHIVELOG


4)Startup database in nomount
5)Execute CONTROL.SQL
SQL>@CONTROL.SQL
0
 
LVL 68

Expert Comment

by:woolmilkporc
ID: 22853130
Hi again, la_colibri,

the steps needed to accomplish this are roughly as follows -
1. Create a script to make a new controlfile (the db will do this for you).
2. Edit this script to meet your needs (new dbname).
3. Start your DB with 'nomount' and run the script.
4. Do some housekeeping concerning additional copies of the controlfile (if any).
5. Start the DB.
 
One must be very careful not to make any mistake.
I could guide you, but only step by step, and we will check more than once
what we have done so far.

And, you should always have a valid, tested backup!

Decide whether you'd like to do it this way, and let me know!

wmp


0
 

Author Comment

by:la_colibri
ID: 22867711
Thanks for your suggestions... however getting different errors this morning from on Friday. Is this related? If not I will open a new question.

I log into the server:

SIDS on this machine are: ADCORDEV
ORACLE_SID = [ADCORPRD] ? ADCORDEV
/home/oracle/>sqlplus /nolog

SQL*Plus: Release 9.2.0.1.0 - Production on Mon Nov 3 09:35:02 2008

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

SQL> connect /as sysdba
Connected.

It looks like the instance is started so I exit SQLPlus to check the status of the LISTENER:

SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

/home/oracle/>lsnrctl status LISTENER

LSNRCTL for IBM/AIX RISC System/6000: Version 9.2.0.1.0 - Production on 03-NOV-2                            008 09:35:29

Copyright (c) 1991, 2002, Oracle Corporation.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=)(PORT=))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for IBM/AIX RISC System/6000: Version 9.2.0.1.                            0 - Production
Start Date                30-OCT-2008 09:51:49
Uptime                    4 days 0 hr. 44 min. 2 sec
Trace Level               off
Security                  OFF
SNMP                      OFF
Listener Parameter File   /r00/app/oracle/product/9.2.0/network/admin/listener.o                            ra
Listener Log File         /r00/app/oracle/product/9.2.0/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=)(HOST=)(PORT=)))
Services Summary...
Service "ADCORDEV" has 1 instance(s).
  Instance "ADCORDEV", status BLOCKED, has 1 handler(s) for this service...
The command completed successfully


When I try to log into database via OEM or Toad I get the error "ORA-12519:TNS:no appropriate service handler found"
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 10

Expert Comment

by:dfke
ID: 22868130
Run "lsnrctl services" to ensure that the instance(s) have registered with the listener, and are accepting connections.
0
 

Author Comment

by:la_colibri
ID: 22868319
This was the output.... says the DB is "blocked"... any suggestions?

home/oracle/>lsnrctl services

LSNRCTL for IBM/AIX RISC System/6000: Version 9.2.0.1.0 - Production on 03-NOV-2008 11:21:38

Copyright (c) 1991, 2002, Oracle Corporation.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=)(PORT=))
Services Summary...
Service "ADCORDEV" has 1 instance(s).
  Instance "ADCORDEV", status BLOCKED, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
The command completed successfully
0
 
LVL 10

Accepted Solution

by:
dfke earned 500 total points
ID: 22868633
There are normally 5 reasons for an instance to be blocked:

1.The remote_listener parameter is set in the init/sp file, but thelocal_listener is not running.
2.The instance is not fully mounted.
3.The instance is shutting down.
4.The maximum number of concurrent sessions has been reached.
5.The node load is at a maximum.




0
 

Author Comment

by:la_colibri
ID: 22870026
I restarted the database and listener and now it's saying "listener supports no services". Before the restart it was blocked and now it's not even there. I will try your suggestions for the control files and see if it fixes this.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Authenticate using sesu from script 7 160
error starting form builder in 11g 2 49
Read CLOB data from Oracle using JAVA 3 44
Performance Issue in Oracle 3 47
Attention: This article will no longer be maintained. If you have any questions, please feel free to mail me. jgh@FreeBSD.org Please see http://www.freebsd.org/doc/en_US.ISO8859-1/articles/freebsd-update-server/ for the updated article. It is avail…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to recover a database from a user managed backup

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question