rename an instance

[jarthi]

Hi:
Can someone let me know how to know rename an already existing instance?
Is it possible to do it?


Thanks,
Arthi

 

[carp]

It depends on your OS.
For Unix, you'll need to change ORACLE_SID to the new name, and change the file name from initOLD_SID.ora to initNEW_SID.ora. Bounce the instance and you should be good to go.

With NT, you will also need to use ORADIM to delete the old instance service and create the new instance service.

 

[jarthi]

Hi Carp:
Thank you very much. That's was very simple and useful.
Oracle is running on UNIX Sun Solaris.
But one more question.If I want to rename the database name as well to the new instance name, what should I do?

Thanks,
Arthi

 

[Guest_imported]

You have to recreate the control file irrespective of platform .

Take backup of the current controlfile . ( Alter database backup controlfile to trace )

shutdown normal;
edit the backedup controlfile .Change the reuse to SET and change the old SID to NEW .

Copy the initSID.ora to initNEWSID.ora AND edit the file and change all occurences of the OLDSID to NEWSID .

export your ORACLE_SID and ORACLE_HOME environment variables .

now from the svrmgr propmt run the create control file script .

You have your Instance Name modified .

If you are not confident of doing it let me know . I will send you step by step description.

 

[jarthi]

Hi:
I am following the steps. I have a question.
In the trace file that is generated , should I change the old instance name that appears in the logfile and datafile paths to the new instance name? That means should I create those directories and files?

This is what was generated: I need to change from 'brie' to 'brie_tst'
STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "BRIE_TST" NORESETLOGS NOARCHIVELOG
MAXLOGFILES 32
MAXLOGMEMBERS 2
MAXDATAFILES 30
MAXINSTANCES 8
MAXLOGHISTORY 907
LOGFILE
GROUP 1 '/dac/oracle/oradata/brie/redobrie01.log' SIZE 2M,
GROUP 2 '/home/oracle-dac/oradata/brie/redobrie02.log' SIZE 2M,
GROUP 3 '/oracle/oradata/brie/redobrie03.log' SIZE 2M
DATAFILE
'/dac/oracle/oradata/brie/system01.dbf',
'/dac/oracle/oradata/brie/rbs01.dbf',
'/dac/oracle/oradata/brie/temp01.dbf',
'/dac/oracle/oradata/brie/tools01.dbf',
'/dac/oracle/oradata/brie/users01.dbf',
'/dac/oracle/oradata/brie/users02.dbf',
'/dac/oracle/oradata/brie/system02.dbf'
CHARACTER SET WE8ISO8859P9
;
RECOVER DATABASE
ALTER DATABASE OPEN;

Again in initbrie_tst.ora ( old initbrie.ora ) , should I change the file path and config file name to include the instance name?
# include database configuration parameters
ifile = /dac/oracle/admin/brie/pfile/configbrie.ora

Thanks,
Arthi

 

[carp]

I would leave the paths as-is. Likewise, you can leave the ifile setting stand as it was before (my philosophy has always been that the less you change at once, the better!).
It looks like your script is probably good to go.
Give it a shot and see what happens.

 

[jarthi]

I ran the script.
It asked me to do 3 things:
1) It asked me to create initbrie_tst.ora in $ORACLE_HOME/dbs directory. I created and ran the script again.
2) It asked me to change the dbname in configbrie.ora file. dbname was equal to 'brie'. Now I changed it to brie_tst and I ran the script again

3) This time it asked me to change to resetlogs instead of noresetlogs since set option is used. I did that and ran the script.

4) the final error is:

ORA-01507: database not mounted
ORACLE instance shut down.
SVRMGR> @./brie_change.sql
ORACLE instance started.
Total System Global Area 71049200 bytes
Fixed Size 69616 bytes
Variable Size 29896704 bytes
Database Buffers 40960000 bytes
Redo Buffers 122880 bytes
CREATE CONTROLFILE SET DATABASE "BRIE_TST" RESETLOGS NOARCHIVELOG
*
ORA-01503: CREATE CONTROLFILE failed
ORA-00200: controlfile could not be created
ORA-00202: controlfile: '/dac/oracle/oradata/brie/control01.ctl'
ORA-27038: skgfrcre: file exists
ORA-01507: database not mounted
ALTER DATABASE OPEN
*
ORA-01507: database not mounted
SVRMGR>

Now I am really stuck. Please help.

Thanks,
Arthi

 

[carp]

The key here is in the messages

ORA-00202: controlfile: '/dac/oracle/oradata/brie/control01.ctl'
ORA-27038: skgfrcre: file exists

Either delete or move the control01.ctl file, then try again.

 

[jarthi]

I deleted the control files. This is the latest error I got.

ORACLE instance started.
Total System Global Area 71049200 bytes
Fixed Size 69616 bytes
Variable Size 29896704 bytes
Database Buffers 40960000 bytes
Redo Buffers 122880 bytes
Statement processed.
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
ALTER DATABASE OPEN
*
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

 

[jarthi]

Hi Carp:
Please ignore my previous post. I have successfully renamed the database as well.
I removed the RECOVER DATABASE from the script.
And had to add resetlogs to ALTER DATABASE. That solved.
Then I had to change the listener.ora and tnsnames.ora files
and the GLOBAL Database name using 'ALTER DATABASE RENAME GLOBAL_NAME TO'

Thanks for the help from you and 'OraMine'.

Arthi

 

[carp]

Glad to hear you made it through! This can be a tense experience; now you can exhale!