Blog

How to change database name using NID Utility

Follow below steps for changing the oracle database name using nid utility.
1. Mount the database

SQL> startup mount
ORACLE instance started.
Total System Global Area 1426059984 bytes
Fixed Size                  9134800 bytes
Variable Size             352321536 bytes
Database Buffers         1056964608 bytes
Redo Buffers                7639040 bytes
Database mounted.
SQL>
SQL>
SQL> select name, open_mode from v$database;
NAME      OPEN_MODE
--------- --------------------
PRIM      MOUNTED

SQL>2. Run the NID utility
SYNTAX – nid sys/password@CURRENT_DBNAME DBNAME=NEW_DBNAME

[oracle@test admin]$ nid target=sys/oracle@prim dbname=dev
DBNEWID: Release 19.0.0.0.0 - Production on Sat Oct 15 12:29:03 2022
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
Connected to database PRIM (DBID=115414704)
Connected to server version 19.3.0
Control Files in database:
    /data/app/oracle/oradata/PRIM/control01.ctl
    /data/app/oracle/oradata/PRIM/control02.ctl
Change database ID and database name PRIM to DEV? (Y/[N]) => Y
Proceeding with operation
Changing database ID from 115414704 to 4196487951
Changing database name from PRIM to DEV
    Control File /data/app/oracle/oradata/PRIM/control01.ctl - modified
    Control File /data/app/oracle/oradata/PRIM/control02.ctl - modified
    Datafile /data/app/oracle/oradata/PRIM/system01.db - dbid changed, wrote new name
    Datafile /data/app/oracle/oradata/PRIM/sysaux01.db - dbid changed, wrote new name
    Datafile /data/app/oracle/oradata/PRIM/undotbs01.db - dbid changed, wrote new name
    Datafile /data/app/oracle/oradata/PRIM/pdbseed/system01.db - dbid changed, wrote new name
    Datafile /data/app/oracle/oradata/PRIM/pdbseed/sysaux01.db - dbid changed, wrote new name
    Datafile /data/app/oracle/oradata/PRIM/users01.db - dbid changed, wrote new name
    Datafile /data/app/oracle/oradata/PRIM/pdbseed/undotbs01.db - dbid changed, wrote new name
    Datafile /data/app/oracle/oradata/PRIM/pdbprim/system01.db - dbid changed, wrote new name
    Datafile /data/app/oracle/oradata/PRIM/pdbprim/sysaux01.db - dbid changed, wrote new name
    Datafile /data/app/oracle/oradata/PRIM/pdbprim/undotbs01.db - dbid changed, wrote new name
    Datafile /data/app/oracle/oradata/PRIM/pdbprim/users01.db - dbid changed, wrote new name
    Datafile /data/app/oracle/oradata/PRIM/temp01.db - dbid changed, wrote new name
    Datafile /data/app/oracle/oradata/PRIM/pdbseed/temp012022-10-14_17-55-45-849-PM.db - dbid changed, wrote new name
    Datafile /data/app/oracle/oradata/PRIM/pdbprim/temp01.db - dbid changed, wrote new name
    Control File /data/app/oracle/oradata/PRIM/control01.ctl - dbid changed, wrote new name
    Control File /data/app/oracle/oradata/PRIM/control02.ctl - dbid changed, wrote new name
    Instance shut down
Database name changed to DEV.
Modify parameter file and generate a new password file before restarting.
Database ID for database DEV changed to 4196487951.
All previous backups and archived redo logs for this database are unusable.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.

3. change the db_name parameter in the parameter file.

SQL> shutdown immediate;
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.

Total System Global Area 1426059984 bytes
Fixed Size                  9134800 bytes
Variable Size             352321536 bytes
Database Buffers         1056964608 bytes
Redo Buffers                7639040 bytes
SQL>
SQL>
SQL> alter system set db_name=DEV scope=spfile;
System altered.
SQL> shu immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL>SQL>
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1426059984 bytes
Fixed Size                  9134800 bytes
Variable Size             352321536 bytes
Database Buffers         1056964608 bytes
Redo Buffers                7639040 bytes
SQL>
SQL>
SQL> show parameter db_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      DEV
SQL>
SQL>
SQL> shu immediate
ORA-01507: database not mounted
ORACLE instance shut down.

4. Rename the spfile to new db name

[oracle@test dbs]$ cp spfileprim.ora spfileDEV.ora
[oracle@test dbs]$ export ORACLE_SID=DEV
[oracle@test dbs]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Oct 15 13:29:27 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.

Total System Global Area 1426059984 bytes
Fixed Size                  9134800 bytes
Variable Size             352321536 bytes
Database Buffers         1056964608 bytes
Redo Buffers                7639040 bytes
SQL>
SQL>
SQL> alter database mount;
Database altered.
SQL>
SQL>
SQL> alter database open resetlogs;
Database altered.
SQL> show parameter local
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string      LISTENER_PRIM
parallel_force_local                 boolean     FALSE
SQL>
SQL>
SQL> alter system register;
System altered.
SQL> select name, open_mode from v$database;
NAME      OPEN_MODE
--------- --------------------
DEV       READ WRITE

Follow me

Contact us for Training/ Job Support

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.