Re: Changin a DB name.

On Tue, 13 Oct 1998, M. Bhatti wrote:
>
> nick wrote:
>
> > Can anybody post instructions on how to change a Oracle 7 database
> > name.
>
> Once you create a new database, you cannot change it's name. Only
> way is to drop db and recreate with new name.

This is not true! You certainly can change the name of the database. Here are the steps. Warning: If you are using NT, there are some additional steps that I won't go into here. These steps work for Unix.

0. Take a backup of the database. Any valid hot or cold backup will do.

1. On your current database, issue an "alter database backup controlfile to trace;"
2. Go to your user_dump_dest in the filesystem on the database host. If you don't know where that is, look it up this way: "select value from v$parameter where name = 'user_dump_dest';"
3. Look for the trace file you just created by sorting the trace files by date: in Unix, "ls -lt" will sort by date. In VMS, I think it is something like "dir /date"
4. Copy the trace file to a location and file name where you can more easily work on it.
5. Edit the trace file. It will have a create controlfile statement in there, among other things. Delete everything before the CREATE CONTROLFILE statement. The first line should read: CREATE CONTROLFILE REUSE DATABASE "<database-name>" NORESETLOGS <archive-mode>
6. Change this line to read: CREATE CONTROLFILE SET DATABASE "<new-database-name>" RESETLOGS <archive-mode>
7. Go tho the bottom of the file, and delete everything after the semicolon at the end of the CREATE CONTROLFILE statement. If you are using OFA-compliant file paths, take this opportunity to change the sid in the paths in the CREATE CONTROLFILE statement to your new sid.
8. Save the file and exit your editor.
9. Shut down the database using SHUTDOWN IMMEDIATE or SHUTDOWN NORMAL.
10. If you are using OFA-compliant file paths, take this opportunity to change the sid directories to be named for the new sid.
11. Rename your init<sid>.ora file, and any other pfiles to be named for your new instance name. Update any references to the SID in these files. Make sure that any file paths you change in here have also been changed at the filesystem level.
12. Update any references to the SID in your listener.ora, and distribute a new tnsnames.ora with the new database name, if you wish.
13. Set your ORACLE_SID environment variable to the new sid, and update any unix shell .login, .rc or any other shell startup files to set the ORACLE_SID to the new sid.
14. Move your old controlfiles out of the way, and start the database up in NOMOUNT mode (STARTUP NOMOUNT).
15. Run the create-coltrolfile script that you edited before, like this: SVRMGRL> @create-controlfile.sql (or whatever name you gave it). You need to be running svrmgrl from the same directory where you saved the script, or else specify the full path name in the @ command.
16. When the create-controlfile command completes (it should be quite fast) the database will be in MOUNT mode.
17. Open the database with the RESETLOGS option, like this: SVRMGRL> alter database open resetlogs;

Good luck. I think I have everything in these steps. If anyone sees anything missing, just post it here. Starting from a backup, this is a great way to set up a test database that has a different name from your production database.

--
Jeremiah Wilton http://www.wolfenet.com/~jeremiah Received on Tue Oct 13 1998 - 10:37:31 CDT