How do I change my NID database name?

Hello friends, in this article we are going to learn how to rename a database using the NID utility step by step. So in this demo, we are going to rename the db11g database name to ocptech.

Before the NID utility, we can rename the database using creating a new control file that was the manual method, but after the NID utility, our task is now easy to change the database name.

Rename Database using NID utility

Follow the below steps carefully to rename the oracle database.

STEP 1. Create a pfile from spfile.

SQL> create pfile from spfile;

STEP 2. Shutdown the database if already running.

SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

STEP 3. Startup mount the database.

SQL> startup mount

ORACLE instance started.

Total System Global Area  839282688 bytes
Fixed Size		    2233000 bytes
Variable Size		  507514200 bytes
Database Buffers	  322961408 bytes
Redo Buffers		    6574080 bytes
Database mounted.

STEP 4. Check Database status.

SQL> select instance_name,status from v$instance;

INSTANCE_NAME	 STATUS
---------------- ------------
db11g		 MOUNTED

How do you change the name of a database?

STEP 5. Start NID utility using the below syntax.

syntax:

nid target=sys/password@your_current_db_name dbname=new_db_name

In My case, my Database name is db11g, and going to rename it as ocptech.

$nid target=sys/oracle@db11g dbname=ocptech

DBNEWID: Release 11.2.0.3.0 - Production on Thu Mar 4 23:53:50 2021

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


NID-00002: Parse error: LRM-00108: invalid positional parameter value 'sys/oracle@db11g'


Change of database ID failed during validation - database is intact.
DBNEWID - Completed with validation errors.

[oracle@rhel6 ~]$ nid target=sys/oracle@db11g dbname=ocptech

DBNEWID: Release 11.2.0.3.0 - Production on Thu Mar 4 23:54:16 2021

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to database DB11G (DBID=536055463)

Connected to server version 11.2.0

Control Files in database:
    /u01/app/oracle/oradata/db11g/control01.ctl
    /u01/app/oracle/fast_recovery_area/db11g/control02.ctl

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

Proceeding with operation
Changing database ID from 536055463 to 3015766568
Changing database name from DB11G to OCPTECH
    Control File /u01/app/oracle/oradata/db11g/control01.ctl - modified
    Control File /u01/app/oracle/fast_recovery_area/db11g/control02.ctl - modified
    Datafile /u01/app/oracle/oradata/db11g/system01.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/db11g/sysaux01.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/db11g/undotbs01.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/db11g/users01.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/db11g/example01.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/db11g/temp01.db - dbid changed, wrote new name
    Control File /u01/app/oracle/oradata/db11g/control01.ctl - dbid changed, wrote new name
    Control File /u01/app/oracle/fast_recovery_area/db11g/control02.ctl - dbid changed, wrote new name
    Instance shut down

Database name changed to OCPTECH.
Modify parameter file and generate a new password file before restarting.
Database ID for database OCPTECH changed to 3015766568.
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.

STEP 6. Change the db_name parameter value as ocptech (New Database Name).

$sqlplus / as sysdba

SQL> startup nomount
ORACLE instance started.

Total System Global Area  839282688 bytes
Fixed Size		    2233000 bytes
Variable Size		  507514200 bytes
Database Buffers	  322961408 bytes
Redo Buffers		    6574080 bytes


SQL> alter system set db_name=ocptech scope=spfile;

SQL> shu immediate

STEP 7. Change current database spfile name as new database name like spfiledb11g rename as spfileocptech.ora, also rename the password file.

$cd /u01/app/oracle/product/11.2.0.4/db_1/dbs/

$ ls -l

-rw-r-----. 1 oracle oinstall 1536 Mar  4 22:52 orapwdb11g
-rw-r-----. 1 oracle oinstall 2560 Mar  5 00:04 spfiledb11g.ora



$cp spfiledb11g.ora spfileocptech.ora
$cp orapwdb11g orapwocptech.ora

STEP 8. Now start your new database ocptech

$ export ORACLE_SID=ocptech

$ sqlplus / as sysdba

SQL> startup mount
ORACLE instance started.

Total System Global Area  839282688 bytes
Fixed Size		    2233000 bytes
Variable Size		  494931288 bytes
Database Buffers	  335544320 bytes
Redo Buffers		    6574080 bytes
Database mounted.


SQL> show parameter db_name

NAME				     TYPE	 VALUE
------------------------------------ ----------- -----------
db_name 			     string	 OCPTECH



SQL> alter database open resetlogs;

Database altered.



SQL> select name,open_mode from v$database;

NAME	  OPEN_MODE
--------- --------------------
OCPTECH   READ WRITE

Note:- After startup of the database, we need to update Listener.ora and tnsname.ora files.

Listener.ora

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ocptech)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/db_1)
      (SID_NAME = ocptech)
    ))

tnsname.ora

OCPTECH =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rhel6.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ocptech)
    )
  )

I hope you really enjoyed this article, write your feelings in the comment box.

Read: How to create a Database manually?