Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

What do I modify in my procedure to rename an Oracle SID in an Oracle 8i SQL Server

Posted on 2008-11-11
16
Medium Priority
?
864 Views
Last Modified: 2013-12-19
Following the attached file instructions from oracle I ran into a problem with respect to the
TEMP table space file:
D:\ORACLE8I\ORADATA\TEST\TEMP01.DBF  which corresponds to the TEMP_TABLESPACE
After removing this entry from the list of datafiles in th elist of V$Datafile name(s)
from the file: chgname.sql which I modified according the instructions given and  once the line was removed
this chgname.sql  almost all steps seemed to work as no errors occurred when I re-run the chgname.sql with the above line removed, however, I receieved an error like:
Tue Nov 11 14:39:50 2008
Errors in file D:\Oracle8i\admin\TEST\udump\ORA00644.TRC:
ORA-00600: internal error code, arguments: [3668], [1], [2], [99767], [99767], [4], [], []

ORA-600 signalled during: alter database open
It occurs to me that there is a step missing here.  

note
The above  error appeared in the alert.log when performing the alter database open resetlogs step #11
from the chgsid instructions.
Now my question is how should the TEMP_TABLE space data file be handled. The chgname.sql script
or step #10 and all the steps leading up to that seemed to work except for the the issue with the TEMP_TABLE file and again once that line was removed from the chgname.sql file the script appeared to work. Only after attempting  step #11  alter database open resetlogs;
did the above error occur.  Someone out there must have experienced this in this procedure
Any help would be appreciated.  I attached the procedure for changing the SID, however,  the procedure appears to be incomplete because of the issue of the identity of a temp table file:
D:\ORACLE8I\ORADATA\TEST\TEMP01.DBF


chgsid-Instructions.pdf
alertlog.txt
0
Comment
Question by:Robert Silver
  • 8
  • 7
15 Comments
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 22953266
OK, now that I've taken off my admin hat, back to the question.

I take it you are just 'testing' the SID change procedure?

In the email you sent me you mentioned that you can't even re-create the database.  Which would you like help with?  

If you are just testing the name change, I suggest we start with a brand new instance.

Do a shutdown abort.  Remove the current controlfiles and datafiles and then run your create script.  It should create a new database.  Then you just need to run ORADIM for create the service.
0
 
LVL 2

Author Comment

by:Robert Silver
ID: 22954386
I would like to say both but since I just  mangled my database
I will open another question since that is the appropriate thing to do.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 22954519
I wouldn't say a new question is appropriate just yet.  I have moderators monitoring this one.  If I can't help you they'll send out a call for help.

Let's get back to a known state.  Blow away the existing database and create a new one.

I suggest using DBCA to create a new one on Windows.
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 2

Author Comment

by:Robert Silver
ID: 22956303
How should I do that blowing away?
oradim -delete -sid  * ?
Note if I do that won't it be impossible to connect with a listener?
e.g
svrmgrl
connect internal/intpassword ???


0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 22959100
1: shut down the instance
2: use oradim to remove the service
3: delete the datafiles and controlfiles

It's been a real long time since I messed with 8i but if memory serves there is still a GUI to create a database.  It's now called the Database Configuration Assistant or DBCA.  That will recreate the service and database for you.

If you still want to create the command line then just recreate the service with oradim (I'd go ahead and remove the existing one just in case it was messed up in the renaming process).
0
 
LVL 2

Author Comment

by:Robert Silver
ID: 22965260
I just tried to do what you told me. Evidently Windows 2000 based Oracle 8i keeps older SIDs
in the registry.  Do you know how to really delete the SID so it can be reused?

Its very annoying that the Oradim program does not work better. It should have removed all associated files and certainly allow the recreation of a database with the same SID name.
Can you tell me how I might do this. The configuration tool seems to know nothing about what SIDS exist in the registry and thus far I have been unable to really properly remove a SID so it could be reused or recreated


Again any help would be appreciated.

0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 22965301
I tend to agree about oradim.  It isn't that easy to use.  It's only purpose is to work with services in a Windows environment.  That's it.

Again, it's been a real long time since I messed with 8i.  I don't remember anything in the registry related to SIDs.  Check %ORACLE_HOME%/dbs for files related to the old SID.  Remove those as well.  I thought those would get overwritten when you recreated.  Maybe not.

If that doesn't work, search the registry for any key containing the SID and let me know what they are.  You should just be able to look in the HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE section and not search the whole registry.

On a side note:  It's about 9:00 PM on a Friday for me.  I'm rarely on during the weekend.  I hope this can wait for a couple days.
0
 
LVL 2

Author Comment

by:Robert Silver
ID: 22973180
All I could find in the registry containing MYOLDSID looked like:

[HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Enum\Root\LEGACY_ORACLESERVICEMYOLDSID\0000]
"Service"="OracleServiceMYOLDSID"


0
 
LVL 2

Author Comment

by:Robert Silver
ID: 22973184
Now I tried renaming the keys but I could not even when the Oracle service is down.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 22975301
That registry key isn't keeping you from recreating your database.

There has to be a left over file keeping you from recreating the database.  Tripple check there aren't any database files still around.  Especially in the dbs directory.
0
 
LVL 2

Author Comment

by:Robert Silver
ID: 22981718
I was able to get past that issue by removing the Legacy entry and then rerunning the oracle configuration utility. After doing that and specifying to delete any datafiles  necessary the instance was created. Now back to my  original question:
How do you deal with the issue of TEMP TABLESPACE. It seems that the change SID procedure is flawed because it does not handle the TEMP TABLES space properly
What must I do to handle the issue of the TEMP TableSpace which appears to be the big problem in bringing back up a database with the changed SID value.
0
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1500 total points
ID: 22984524
Glad you're back to a baseline. Before playing again, I suggest you shutdown and do a cold backup of the database. This should get you back quicker in case the rename goes bad again.

When you create the backup controlfile script and edit it for the SID rename, did you leave the line re-creating the TEMP file?
If possible can you backup the controlfile to trace and post that file before you edit it?
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 23098106
I just remembered that I hadn't received any update on this question for a while.  Do you still need help on this?
0
 
LVL 2

Author Comment

by:Robert Silver
ID: 23581327
Yes actually - The Temp Table Space needs to somehow be recreated!
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 23589807
The command to recreate the temp tablespace should be in the script generated from the 'backup controlfile to trace'.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses
Course of the Month14 days, 8 hours left to enroll

578 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