Oracle Database Discussions

Hi my friend;
I wanna share information about how to change database SID in unix system.I found many documentation on net but they are little confusing for beginner.I hope this information will help for my friend...
Before start we have to take backup of our system(of course we should stop database first)

1. After backup we open db and run this command

SQL> alter database backup controlfile to trace;

After run this we can go and check .trc file under udump directory.You will see there is some path about our redolog and datafile...

2. Close database and then rename spfileSID.ora, we are doing this coz while db is opening its go and check this file... we want our system open wiht initSID.ora..

3. Change db name from initSID.ora...For instance in my system my db name is orcl and i wanna make it helios,so i change this like that:

old: db_name='orcl'
new:'helios'
old:.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)
new:.dispatchers='(PROTOCOL=TCP) (SERVICE=heliosXDB)

4. Delete all control file from oradata

5. We have to create new control file wiht this script:

CREATE CONTROLFILE SET DATABASE "HELIOS" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/redo01.log' SIZE 50M,
GROUP 2 '/u01/app/oracle/oradata/redo02.log' SIZE 50M,
GROUP 3 '/u01/app/oracle/oradata/redo03.log' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/system01.dbf',
'/u01/app/oracle/oradata/undotbs01.dbf',
'/u01/app/oracle/oradata/sysaux01.dbf',
'/u01/app/oracle/oradata/users01.dbf',
'/u01/app/oracle/oradata/example01.dbf'
CHARACTER SET WE8ISO8859P9 <<< u have to set ur character set here
;

ALTER DATABASE OPEN RESETLOGS;

save this script like a.sql

5. connect db wiht sysdba privileges

SQL> startup nomount;
SQL> @/a.sql <<< u have to type here where u create ur a.sql

if u have error here u have to change ORACLE_SID in .bash_profile whihc is ur oracle user.

After run a.sql it will say u:
SQL> @/a.sql

Control file created.

Database altered.

6.Shutdown database;

7. startup
8.select name from v$database;
NAME
--------
HELIOS

I hope it will help for beginner or other friends

Take care
Helios