Oracle Rename Database using NID Utility

I will explain Oracle Rename Database using NID Utility in this post.

 

Oracle Rename Database with DBNEWID utility (nid)

Sometimes you need renaming Oracle database name or Oracle SID.

Database name is very important parameter in Oracle especially for backup operations and Preprod, UAT databases.

 

You need to rename database name of Oracle when we prepare testing or preprod database to seperate from Production database.

 

Database should be in mount mode for this operation. So shutdown database and startup mount mode as follows.

 

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

SQL> startup mount
ORACLE instance started.

Total System Global Area 2.6867E+11 bytes
Fixed Size 30150224 bytes
Variable Size 2.5770E+10 bytes
Database Buffers 2.4267E+11 bytes
Redo Buffers 207720448 bytes
Database mounted.
SQL>

You can rename database name ( db_name parameter) parameter with nid tool like following.

oracle:deveci01:/home/oracle:> nid TARGET=SYS/oracle DBNAME=CLONE

DBNEWID: Release 18.3.0.0.0 - Production on Mon Sep 8 14:18:07 2018

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

Connected to database DEVECI18C (DBID=2402765690)

Connected to server version 18.3.0

Control Files in database:
+DATA/DEVECI18C/controlfile/control02
+RECO/DEVECI18C/controlfile/control02

Change database ID and database name DEVECI18C to CLONE? (Y/[N]) => y

Proceeding with operation
Changing database ID from 2402765690 to 1056563552
Changing database name from DEVECI18C to CLONE
Control File +DATA/DEVECI18C/controlfile/control02 - modified
Control File +RECO/DEVECI18C/controlfile/control02 - modified
Datafile +DATA/DEVECI18C/datafile/system01.db - dbid changed, wrote new name
Datafile +DATA/DEVECI18C/datafile/sysaux01.db - dbid changed, wrote new name
Datafile +DATA/DEVECI18C/datafile/undotbs01.db - dbid changed, wrote new name
Datafile +DATA/DEVECI18C/datafile01.db - dbid changed, wrote new namew
Datafile +DATA/DEVECI18C/datafile/temp01.db - dbid changed, wrote new name
Control File +DATA/DEVECI18C/controlfile/control02 - dbid changed, wrote new name
Control File +RECO/DEVECI18C/controlfile/control02 - dbid changed, wrote new name
Instance shut down

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

 

 

Database name and ID has changed like above.

 

If database name has changed then you should change db_name parameter in pfile or spfile otherwise you can get following error.

 

 

 

oracle:deveci01:/home/oracle:>export ORACLE_SID=CLONE
oracle:deveci01:/home/oracle:>sqlplus / as sysdba

SQL*Plus: Release 18.3.0.0.0 Production on Mon Sep 8 14:18:54 2018

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

Connected to an idle instance.

SQL> startup mount;
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/oracle/product/18.3.0.0/db/dbs/initCLONE.ora'
SQL> startup mount;
ORACLE instance started.

Total System Global Area 2.1379E+10 bytes
Fixed Size 2230912 bytes
Variable Size 1.0469E+10 bytes
Database Buffers 1.0872E+10 bytes
Redo Buffers 36106240 bytes
ORA-01103: database name 'CLONE' in control file is not 'DEVECI18C'

 

 

Shutdown database and change pfile with new db_name. like following.

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

ORACLE instance shut down.

 

I have changed pfile name with initCLONE.ora and db_name is changed from DEVECI18C to CLONE.

SQL> 
SQL> startup mount;
ORACLE instance started.

Total System Global Area 2.1379E+10 bytes
Fixed Size 2230912 bytes
Variable Size 1.0469E+10 bytes
Database Buffers 1.0872E+10 bytes
Redo Buffers 36106240 bytes
Database mounted.

 

 

 

Now we need to open database with resetlogs like following. And we will check database open mode and name.

SQL> alter database open resetlogs;

Database altered.



SQL> select open_mode, name from v$database;

OPEN_MODE NAME
-------------------- ---------
READ WRITE   CLONE

SQL>

 

 

 

 

Do you want to learn Oracle Database for Beginners, then read the following articles.

Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )