Re: Rename an Oracle 91 database

The short story is: issue an alter database backup controlfile to trace command. That will write a trace file into the USER_DUMP_DEST (show parameter USER_DUMP to see where that is set to).

Find that trace file. In it, you will discover two versions of the 'create controlfile' command. In earlier versions of Oracle, there was only one version of the command. There will be a version called 'NORESETLOGS' and a version called 'RESETLOGS'. The two letters 'NO' are about the only real difference between them!

Strip out all the rubbish at the top of the script, so that the first line reads 'STARTUP NOMOUNT'. Then scroll down, and strip out all the rubbish that is the second version of the command. You want to be left with a single 'create controlfile command, of the NORESETLOGS variety.

Now do some editing.

The line you need to edit is: 'CREATE CONTROLFILE REUSE DATABASE 'TEST' NORESETLOGS... Change that to read 'CREATE CONTROLFILE SET DATABASE 'NEWNAME' RESETLOGS....' Now scroll down a bit. There'll be a couple of lines that read: 'recover database... alter database open'. Change that last one to read 'alter database open resetlogs'.

Now, save the edited trace file as something like 'createcon.sql'.

Edit your init.ora (and if you're using an spfile, switch back to using an init.ora just for the moment). Find the db_name parameter and change it so that it matches the new name you supplied for the database in the createcon.sql script.

Shutdown your database CLEANLY (immediate will do). Find your existing controlfiles with Windows Explorer and delete them. Back in SQL*Plus, connect as SYSDBA, but don't issue any sort of startup command. Then type @<directory>\createcon.sql -ie, run the createcon.sql script you created earlier. That will build you an instance, and very soon thereafter you should see the 'controlfile created' message, after which the database should be opened normally. Select * from v$database, and you should see the name has changed.

Take a backup before doing anything, of course.

Regards
HJR "James Culleton" <jculleton_at_eircom.net> wrote in message news:b1oevu$bbs$1_at_kermit.esat.net...
> Hi,
>
> I've installed Oracle 9i server (release 2) 9.2.0.1.0 on a Windows 2000
> server. I created a database called "test" but now I would like to rename
> the database to something else. Is this possible? or would I have to
create
> a new database and move the data across to it.
>
> Any help would be great
>
> James
>
>
Received on Tue Feb 04 2003 - 12:41:33 CST