Solved

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

Posted on 2008-10-31
7
1,851 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
  • 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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Via a live example, show how to take different types of Oracle backups using RMAN.

707 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now