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

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
LVL 2
Robert SilverSr. Software EngineerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
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
Robert SilverSr. Software EngineerAuthor Commented:
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
slightwv (䄆 Netminder) Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Robert SilverSr. Software EngineerAuthor Commented:
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
slightwv (䄆 Netminder) Commented:
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
Robert SilverSr. Software EngineerAuthor Commented:
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
slightwv (䄆 Netminder) Commented:
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
Robert SilverSr. Software EngineerAuthor Commented:
All I could find in the registry containing MYOLDSID looked like:

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


0
Robert SilverSr. Software EngineerAuthor Commented:
Now I tried renaming the keys but I could not even when the Oracle service is down.
0
slightwv (䄆 Netminder) Commented:
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
Robert SilverSr. Software EngineerAuthor Commented:
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
slightwv (䄆 Netminder) Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
slightwv (䄆 Netminder) Commented:
I just remembered that I hadn't received any update on this question for a while.  Do you still need help on this?
0
Robert SilverSr. Software EngineerAuthor Commented:
Yes actually - The Temp Table Space needs to somehow be recreated!
0
slightwv (䄆 Netminder) Commented:
The command to recreate the temp tablespace should be in the script generated from the 'backup controlfile to trace'.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.