Linux – change the SID of an Oracle database

linuxoracleoracle-11g

The Server is Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – 64bit

Is there an easy & quick way to change the SIDs of the test databases on the server?

Dropping & recreating of the database is an option for me. But I'm looking for something requiring less time.

The other option to assign names in the clients tnsnames.ora is prone to errors, because they are not administrated centrally.

Compared with the time to drop & create a database on SQL-Server, the amount of time required to create a new Oracle database is excessively greater.
Further on SQL-Server you can rename SQL-Server instances. [ Usually you rename the server where SQL-Server is running and have some problems until you rename the server too ].

Best Answer

Since 9i dbnewid utility (nid) can be used to change database name (and DBID if required). If database name being changed only then resetlogs is not required:

1 startup database in mount mode
```
shutdown immediate
startup mount
```

2 run nid to change database name:

nid target=sys/syspassword@dbtns dbname=newname setname=YES

3 shutdown and start database in mount mode:
```
shutdown immediate
startup mount
```
4 change db_name in spfile (or in pfile editing the file):
```
alter system set db_name=newname scope=spfile;
```

5 recreate password file:

orapwd file=orapwnewname password=syspassword

6 startup the database
```
startup
```

7 post rename steps:

change SID in listener.ora
correct tnsnames.ora
remove old trace directories
change /etc/oratab (UNIX) or rename windows service using oradim

Related Solutions

Oracle 11g r2 standard edition installation – set environment variable PATH and create tnsnames.ora

As @ik_zelf mentioned, your PATH variable will need to include the "bin" directory of your Oracle software install. This will allow you to execute commonly-needed commands/utilities like "sqlplus" and "lsnrctl" without changing directories. This is typically $ORACLE_HOME/bin

Your TNS_ADMIN variable needs to be set in a similar matter, pointing to the location of your tnsnames.ora, listener.ora, etc. By default, SQL Plus checks this directory (defined by TNS_ADMIN) for tnsnames.ora. This is typically $ORACLE_HOME/network/admin

If you want to connect to your database via TNS, your tnsnames.ora file (assuming a ORACLE_SID of "myDatabase", a host of "myHostname" and the default port) would look something like this:

myDatabase = 
    (DESCRIPTION = 
        (ADDRESS = (PROTOCOL = TCP)(HOST = myHostname)(PORT = 1521))
        (CONNECT_DATA = (SERVICE_NAME = myDatabase)))

This would allow you to connect locally simply by doing the following from a command line:

sqlplus username/password@myDatabase

Of course, as SQL Plus looks for the tnsnames.ora file in the location defined by $TNS_ADMIN, this will only function locally. To access this database via TNS (Transparent Network Substrate) from a remote machine, you would have to install the Oracle client and ensure that the TNS entry for myDatabase (as described above) is included in the remote machine's tnsnames.ora file as well.

Oracle database migration or copy

The best way to achieve what you want is to do full export/import using data pump technology.

After successful import, you will probably have to recompile invalid objects.

Best Answer

Related Solutions

Oracle 11g r2 standard edition installation – set environment variable PATH and create tnsnames.ora

Oracle database migration or copy

Related Question