changing the instance name for single instance

To change the instance name:

Enter user-name: / as sysdba

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area  418484224 bytes

Fixed Size                  1375004 bytes

Variable Size             310379748 bytes

Database Buffers          100663296 bytes

Redo Buffers                6066176 bytes

Database mounted.

Database opened.

SQL> spool C:\Users\jayva\Desktop\work\oracle_sid_change\sid.txt

SQL> show parameter db_name

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_name                              string      target

SQL> show parameter instance_name

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

instance_name                        string      target

SQL> create pfile='C:\Users\jayva\Desktop\work\oracle_sid_change\pfile.ora' from

 spfile;

File created.

SQL> shu immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Alter the instance_name parameter in newly created pfile and also creating the new service (using oradim utility).

C:\Windows\system32>oradim -new -sid target1

Instance created.

C:\Users\jayva>set ORACLE_SID=target1

Now starting the database using altered pfile.

C:\Users\jayva>sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Thu Apr 26 19:56:11 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Enter user-name: / as sysdba

Connected to an idle instance.

SQL> startup nomount pfile=C:\Users\jayva\Desktop\work\oracle_sid_change\pfile.ora

ORACLE instance started.

Total System Global Area  418484224 bytes

Fixed Size                  1375004 bytes

Variable Size             281019620 bytes

Database Buffers          130023424 bytes

Redo Buffers                6066176 bytes

SQL> alter database mount;

Database altered.

SQL> alter database open;

Database altered.

SQL> show parameter db_name

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_name                              string      target

SQL> show parameter instance_name

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

instance_name                        string      target1

SQL>

Here we go, instance name is change to target1 and database name is target.