Need to set ORACLE_SID of a database.

What if the person who installed database can't remember what it was, is it the same as the instance_name in the init.ora file?

ORACLE_SID is the instance name, check init.ora for instance_name parameter.

Check /etc/oratab file (or depending on Oracle version /var/opt/oracle/oratab) for a list of instances on the server.

ORACLE_SID MAY BE or MAY NOT BE the same as the database name, check init.ora for db_name parameter.

In need of a Unix/Solaris shell script that would rename the Oracle SID of our Oracle 9.2 database instance running on Solaris 8 platform.

Rename the sid:

1) Shutdown database,
2) In $ORACLE_HOME/dbs directory copy initOLDDB.ora to initNEWDB.ora
3) In DB startup script replace ORACLE_SID
export ORACLE_SID=NEWDB
4) Rename sid in /etc/oratab (or /var/opt/oracle/oratadb)
5) Startup database.

Change my SID name after Creating Database

You need to REcreate the Control file to achieve this .

1. SVRMGR>Alter Database backup controlfile to trace;
# this will generate an Ascii Trace file in $USER_DUMP_DEST directory which will have the Control File Creation Script.

2. Shutdown the Database and Do a Physical Backup of all the Datafiles,Controlfiles,RedoLog files,Archived Redo log files etc etc...for Safety.

3. Rename the Init<oldSID>.ora and config<OLDSID>.ora to Init<NEWSID>.ora and Config<NewSid>.ora files in $ORACLE_HOME/dbs This is to prevent any errors during Database Startups looking for default 'pfile' names.

4. Rename the Old Controlfiles to say control01.old etc This is to Create New Controlfile and not reuse the existing one.

5. Edit the Control File creation Script ..It should read like:
Startup nomount;
Create Controlfile set Database 'NEW_SID' Resetlogs
...... <all others remain the Same>
;

6. Open your database:
alter database open resetlogs;

What is the difference between Oracle SIDs and Oracle SERVICE NAMES. One config tool looks for SERVICE NAME and then the next looks for SIDs! What's going on?!

Oracle SID is the unique name that uniquely identifies your instance/database where as Service name is the TNS alias that you give when you remotely connect to your database and this Service name is recorded in Tnsnames.ora file on your clients and it can be the same as SID and you can also give it any other name you want.

SERVICE_NAME is the new feature from oracle 8i onwards in which database can register itself with listener. If database is registered with listener in this way then you can use SERVICE_NAME parameter in tnsnames.ora otherwise - use SID in tnsnames.ora.

Also if you have OPS (RAC) you will have different SERVICE_NAME for each instance.

SERVICE_NAMES specifies one or more names for the database service to which this instance connects. You can specify multiple services names in order to distinguish among different uses of the same database. For example:

SERVICE_NAMES = sales.acme.com, widgetsales.acme.com

You can also use service names to identify a single service that is available from two different databases through the use of replication.

In an Oracle Parallel Server environment, you must set this parameter for every instance.

Have a Oracle Question
Do you have an Oracle Question?

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Oracle Application
Oracle Application Hints and Tips

Oracle Home
Oracle Database, SQL, Application, Programming Tips