Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Renaming an Oracle database

Status
Not open for further replies.

tklinz2001

IS-IT--Management
Mar 4, 2009
7
US
Hi, I need to rename an oracle 10g database but not change the database id. I have identified the steps from different articles but I had a couple questions regarding a couple of steps. I understand all the steps but need clarification with the following. My main concern is the modifications to SPFILE as I can't find too much information on my specific questions.

The step that identifies renaming the SPFILE to match the new DBNAME
Questions:
1) Do I simply rename the file from SPFILEolddatabasename.ORA to SPFILEnewdatabasename.ORA or do I need to change anything in the file? I see in the file that it has the database name in it so that is why I am questioning it. Also, is there a particular tool that I should use?

The step for using Windows says that I must recreate the service so the correct name and parameter file are used and lists the following as an example:
oradim -delete -sid TSH1
oradim -new -sid TSH2 -intpwd password -startmode a -pfile c:\oracle\920\database\spfileTSH2.ora
Question:
2) Is that all that has to be done for this step, is the syntax complete? I know that it is an example using Oracle's example database names but I just need to know if it is complete. Is this the complete process of recreating the service?

The step for altering the listener.ora and tnsnames.ora setting to match the new database name and restart the listener: lsnrctl reload
Question:
3) Best way to alter the listener.ora and tnsnames.ora setting?

Thank you so much


 

1) You need to create pfile from spfile change the names of the db and of the init.ora file.

2)
oradim -delete -sid TSH1
oradim -new -sid TSH2 -intpwd password -startmode manual

Create spfile from pfile.

oradim -edit -sid TSH2 -startmode auto -pfile c:\oracle\920\database\spfileTSH2.ora


3) netca
[3eyes]


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb
 
LKBrwnDBA
Not sure if I mentioned I was going to use DBNEWID but only change the database name not the ID.
Do I need to do all that you mentioned in #1 in this case?
If so, can you please elaborate?
 

Yes you need to do all I mentioned, check out the fine Oracle® Database Utilities manual.
[noevil]
PS: Do some research to get the correct sequence of tasks.


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb
 
Thanks LDBrwnDBA,
I have actually done a lot of research and do have the correct sequence of tasks. I know to do the following:
1. Backup the database
2. Mount the database after a clean shutdown
3. Invoke DBNEWID and use the SETNAME parameter since I only want to change the database name and not the database ID
4. Shutdown the database
5. Modify the DB_NAME parameter in the initialization parameter file
6. Create a new password file
7. Rename the SPFILE to match the new DBNAME
8. Recreate the service so the correct name and parameter file are used
9. Alter the listener.ora and tnsnames.ora setting to match the new database name and restart the listener
10. Open database without RESETLOGS since I’m only changing the database name

My issue is with number 7. I have found this sequence of tasks on numerous sites. However, they only say rename the SPFILE to match new DBNAME.

Before posting to Tek-Tips I have googled extensively on ‘renaming SPFILE’ and have not been able to find a good site that actually lists what needs to be done for this task. Google returns the same sites I have already seen explaining the DBNEWID utility, which is why I posted to Tek-Tips. The link you posted does elaborate a little bit more than others but still only says:
Note: The DBNEWID utility does not change the server parameter file (SPFILE). Therefore, if you use SPFILE to start your Oracle database, you must:
1. Re-create the initialization parameter file from the server parameter file
2. Remove the server parameter file
3. Change the DB_NAME in the initialization parameter file
4. Re-create the server parameter file.

Can you help me with these 4 steps?
1) I’m confused how #1 is using the server parameter file (SPFILE) to create the PFILE but is removed and recreated in #4. Wouldn’t the SPFILE be the old one that would be used to recreate the initialization parameter file?
2) Is #3, Change the DB_NAME in the initialization parameter file, the same task as #5, Modify the DB_NAME parameter in the initialization parameter file above (ALTER SYSTEM SET DB_NAME=newname SCOPE=spfile;)? Do I just repeat it?
3) To recreate the server parameter file (#4) would this just be:
create spfile = '/some/path/spfile-name' from pfile = '/some/other/path/init.ora';

So you listed:
You need to create pfile from spfile change the names of the db and of the init.ora file. Is that a summation of #1 - #4? If so, isn’t creating pfile from spfile #4, change the names of the db and of the init.ora #3?

I’m new to SPFILE, so I apologize in advance if my questions are elementary. I am trying to get everything ironed out so when I go to perform the steps, there is no guess work since I have limited time to complete this once I take the system down. Users will be waiting on their database
 
Okay, I think I understand what needs to happen in 1-4. Please correct if I am wrong:
1. create pfile from spfile because pfile does not exist if database is using spfile and pfile can be edited in notepad whereas spfile cannot. CREATE PFILE FROM SPFILE;

2. remove spfile - Can I just delete it in windows explorer?

3. Change DB_NAME in initialization parameter file (aka pfile). This is editing pfile in notepad where old database name is replaced with new, correct?

4. recreate spfile. Create the spfile from pfile, CREATE SPFILE FROM PFILE;

Am I correct in my understanding? Please correct me if I am wrong.
Also, you mentioned, Create spfile from pfile as part of the recreating the Windows service. Isn't this already done in the step preceeding? I'm only asking because I haven't seen that extra step in the Windows service task on other websites

Thanks so much again!
 

Between 2 and 3 you need to execute the nid.exe (db new id) utility.
[thumbsup2]


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top