Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2008-10-31
7
Medium Priority
?
2,078 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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
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.
Suggested Courses

824 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