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\T
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\udu
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\T
chgsid-Instructions.pdf
alertlog.txt
TEMP table space file:
D:\ORACLE8I\ORADATA\TEST\T
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\udu
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\T
chgsid-Instructions.pdf
alertlog.txt
ASKER
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.
I will open another question since that is the appropriate thing to do.
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.
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.
ASKER
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 ???
oradim -delete -sid * ?
Note if I do that won't it be impossible to connect with a listener?
e.g
svrmgrl
connect internal/intpassword ???
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).
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).
ASKER
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.
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.
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/SOFTWAR
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.
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/SOFTWAR
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.
ASKER
All I could find in the registry containing MYOLDSID looked like:
[HKEY_LOCAL_MACHINE\SYSTEM
"Service"="OracleServiceMY
[HKEY_LOCAL_MACHINE\SYSTEM
"Service"="OracleServiceMY
ASKER
Now I tried renaming the keys but I could not even when the Oracle service is down.
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.
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.
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I just remembered that I hadn't received any update on this question for a while. Do you still need help on this?
ASKER
Yes actually - The Temp Table Space needs to somehow be recreated!
The command to recreate the temp tablespace should be in the script generated from the 'backup controlfile to trace'.
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.