Solved

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

Posted on 2008-10-31
7
1,867 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
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…
In a previous video, we went over how to export a DynamoDB table into Amazon S3.  In this video, we show how to load the export from S3 into a DynamoDB table.

808 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