Rename an Oracle DATABASE using the NID command

Hello everyone, today we are going to share the way that exists in Oracle to rename a database. To perform this action, you use a command-line application found in the installation binaries, which is the NID command.

Rename an Oracle DATABASE

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

Steps to rename an Oracle database by changing the SID

1. Stop the instance in an orderly manner

It is not necessary to make a stop with the ABORT option to rename an Oracle database, but it is advisable to make a checkpoint and the logfile switches necessary for the database to be cleaned before carrying out 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 database

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 database to generate the parameter file, changing the value of the parameter db_name with the new name that the database 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. Run the NID command

The NID command is an application that is located in the Oracle software installation directory, which is given by the environment variable ORACLE_HOME, 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 database with the new name

Once the name change is done, you have to open the database with the RESETLOGS option. Once the database is opened, the SPFILE can be regenerated 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

You can create the spfile with the following statement:

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

You can consult the information of this tool in the official documentation of the manufacturer, in the following link:
https://docs.oracle.com/database/121/SUTIL/GUID-FA1E0D47-4907-47F5-877C-2DB109B74430.htm#SUTIL1543

We hope you found it useful. See you in a new installment about PostgreSQL.

Greetings.

Database computer

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 only one email per month.

Tags: No tags

Comments are closed.