Change SID / Instance ID

It is eazy to change ORACLE_SID / SID. Really, only two files are involved.

1. Identify the current SID:

SQL> select instance from v$thread;

INSTANCE
---------
pdb

SQL> select value from v$parameter where name = 'spfile';

VALUE
--------------------------------------------------------------------
/u01/app/oracle/product/10.2.0/db_1/dbs/spfilepdb.ora

SQL> select value from v$parameter where name = 'control_files';

VALUE
-------------------------------------------------------------------------
/u08/app/oracle/oradata/pdb/control01.ctl, /u09/app/oracle/oradata/pdb/control02.ctl

SQL> alter database backup controlfile to trace;
SQL> shutdown immediate;

2. Rename SPFILE and password file:

$ cd /u01/app/oracle/product/10.2.0/db_1/dbs
$ cp spfilepdb.ora spfilepdb.ora_bk
$ mv spfilepdb.ora spfilepdbs.ora
$ cp orapwpdb orapwpdb_bk
$ mv orapwpdb orapwpdbs

3. Optional: back up the control files. Once the instance is started, the control file will be updated with the new SID.

4. Start the instance and see the new SID:

$ export ORACLE_SID=pdbs
$ sqlplus /nolog
SQL> connect / as sysdba
SQL> startup open
SQL> select instance from v$thread;

INSTANCE
--------
pdbs

SQL> select value from v$parameter where name = 'spfile';

VALUE
--------------------------------------------------------------------
/u01/app/oracle/product/10.2.0/db_1/dbs/spfilepdbs.ora

SQL> select value from v$parameter where name = 'control_files';

VALUE
---------------------------------------------------------------------------
/u08/app/oracle/oradata/pdb/control01.ctl, /u09/app/oracle/oradata/pdb/control02.ctl

5. Update oratab, tnsnames.ora, .profile with the new SID
6. If necessary, all folders for the instance on the file system can be changed to reflect the new SID.