Renaming an Oracle DB using the NID command

The NID command changes the header of the datafiles, controlfiles, etc. and the db_name parameter, although you must manually generate the init.ora file, change the file paths, and recreate the spfile if it is used in the DB.

Steps to rename an Oracle DB by changing the SID

1. Stop the instance in an orderly fashion

It is not necessary to make a stop with the ABORT option to rename an Oracle DB, but it is advisable to make a checkpoint and the necessary switch logfile to clean the DB before performing the process.

[oracle@server1 admin] echo $ORACLE_SID
oldsid
[oracle@server1 admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Feb 10 13:37:36 2014

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

SQL> alter system checkpoint;
System altered.

SQL> alter system switch logfile;
System altered.

SQL> alter system switch logfile
System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

2. Set up the DB

The renaming process is done with the database in MOUNT mode.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1152450560 bytes
Fixed Size 2252584 bytes
Variable Size 352321752 bytes
Database Buffers 788529152 bytes
Redo Buffers 9347072 bytes
Database mounted.

3. Create the PFILE parameter file

You can use the SPFILE of the current DB to generate the parameter file, changing the value of the db_name parameter with the new name that the DB will have after the change.

SQL> create pfile='/tmp/init_nuevo.ora' from spfile; 
$ cp /tmp/init_nuevo.ora $ORACLE_HOME/dbs/initnewsid.ora
db_name=newsid

4. Execute the NID command

The NID command is an application found in the Oracle software installation directory, which is given by the ORACLE_HOME environment variable, and in the BIN folder.

[oracle@server1 admin]$ export ORACLE_SID=oldsid
[oracle@server1 admin]$ nid target=/ dbname=newsid

DBNEWID: Release 11.2.0.4.0 - Production on Mon Feb 10 13:40:47 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to database OLDSID (DBID=2695375495)
Connected to server version 11.2.0

Control Files in database:
+REDO1/oldsid/controlfile/current.256.838662091
+REDO2/oldsid/controlfile/current.256.838662091

Change database ID and database name OLDSID to NEWSID? (Y/[N]) => Y

Proceeding with operation
Changing database ID from 2695375495 to 4057109727
Changing database name from OLDSID to NEWSID
Control File +REDO1/oldsid/controlfile/current.256.838662091 - modified
Control File +REDO2/oldsid/controlfile/current.256.838662091 - modified
Datafile +DATA/oldsid/datafile/system.259.83866210 - dbid changed, wrote new name
Datafile +DATA/oldsid/datafile/sysaux.258.83866210 - dbid changed, wrote new name
Datafile +DATA/oldsid/datafile/undotbs1.257.83866210 - dbid changed, wrote new name
Datafile +DATA/oldsid/datafile/users.260.83866211 - dbid changed, wrote new name
Datafile +DATA/oldsid/datafile/app1tab.267.83873288 - dbid changed, wrote new name
Datafile +DATA/oldsid/datafile/app2.266.83873290 - dbid changed, wrote new name
Datafile +DATA/oldsid/tempfile/temp.256.83866210 - dbid changed, wrote new name
Control File +REDO1/oldsid/controlfile/current.256.838662091 - dbid changed, wrote new name
Control File +REDO2/oldsid/controlfile/current.256.838662091 - dbid changed, wrote new name
Instance shut down

Database name changed to NEWSID.
Modify parameter file and generate a new password file before restarting.
Database ID for database NEWSID changed to 4057109727.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.

5. Open the DB with the new name

Once the name change has been made, the database must be opened with the RESETLOGS option. Once the database has been opened, the SPFILE can be generated again with the previously created init.ora file.

[oracle@server1 ~]$ export ORACLE_SID=newsid
[oracle@server1 ~]$ sqlplus / as sysdba
SQL> startup mount
SQL> ALTER DATABASE OPEN RESETLOGS;
Database opened.
SQL> select name from v$database;

NAME
---------
NEWSID

The spfile can be created with the following instruction:

SQL> CREATE SPFILE='+DATA' from pfile;

Information on this tool can be found in the manufacturer’s official documentation at the following link:
https://docs.oracle.com/database/121/SUTIL/GUID-FA1E0D47-4907-47F5-877C-2DB109B74430.htm#SUTIL1543

We hope it has been useful to you. See you in a new installment on PostgreSQL.

Greetings.

Database Team

If you don’t want to miss tricks like this and stay up to date with this world, subscribe to our newsletter . We will keep you informed with a single email per month.

Still do not know Query Performance ? Find out how it can help you in your Oracle environment. More information on their page LinkedIn .

Follow GPS on LinkedIn