Change SID of the Oracle database using DBNEWID

Change SID of the Oracle database using DBNEWID

Let’s check DBID and instance name.

SQL> select instance_name from v$instance;

INSTANCE_NAME

SQL> select dbid,open_mode from v$database;

DBID OPEN_MODEv ---------- ----------

1597268947 READ WRITE

CRMP is production database and I want to change SID from CRMP to CRMD.

First step is to shutdown database and start in mount mode.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 730714112 bytes

Fixed Size 2163280 bytes

Variable Size 192941488 bytes

Database Buffers 528482304 bytes

Redo Buffers 7127040 bytes

Database mounted.

Now let’s run nid to change database name.

$ nid target=sys/oracle@test_crmp dbname=crmd setname=YES

DBNEWID: Release 11.1.0.7.0 - Production on Tue Apr 9 11:59:56 2013

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

Connected to database CRMP (DBID=1597268947)

Connected to server version 11.1.0

Control Files in database:

/u01/app/devcrmd/oradata/control01.ctl

/u01/app/devcrmd/oradata/control02.ctl

Change database name of database CRMP to CRMD? (Y/[N]) => Y

Proceeding with operation

Changing database name from CRMP to CRMD

Control File /u01/app/devcrmd/oradata/control01.ctl - modified Control File /u01/app/devcrmd/oradata/control02.ctl - modified Datafile /u01/app/devcrmd/oradata/datafiles/system01.db - wrote new name Datafile /u01/app/devcrmd/oradata/datafiles/sysaux01.db - wrote new name Datafile /u01/app/devcrmd/oradata/datafiles/users01.db - wrote new name Datafile /u01/app/devcrmd/oradata/datafiles/data01.db - wrote new name Datafile /u01/app/devcrmd/oradata/datafiles/indx01.db - wrote new name Datafile /u01/app/devcrmd/oradata/datafiles/tools01.db - wrote new name Datafile /u01/app/devcrmd/oradata/datafiles/undotbs02.db - wrote new name