How to Change Oracle Database Name Using NID Utility

<<Back to DB Administration Main Page

There are situations when the need arise to change the database name. The requirement could be one of the following

• Change the DBID and Database Name (DB_NAME) both
• Change  Only the Database ID
• Change Only the Database Name
  

The goal of this post is demonstrate you how to change only the database name without affecting the DBID of the database.

Changing Only the Database Name of Oracle Database

Background Info: I want to change the TEST1D database to ORCL1D

Old Database Name: TEST1D

Changed Database Name: ORCL1D

Old Database Name: TEST1D

Changed Database Name: ORCL1D

Step0: Ensure the application is down and no user is connected to database

SQL> select distinct username, count(1) from v$session where username not in ('SYS','SYSTEM','DBSNMP') group by username

no rows selected

Step1: Prepare for full database backup

SQL> alter system checkpoint;

SQL> alter system switch logfile;

SQL> alter system switch logfile;

SQL> alter system switch logfile;

Step2: Perform full database and archivelog backup

$rman target / catalog rman@catalog

RMAN> backup database plus archivelog all delete input;

Step3: Source DB environment variables

$export ORACLE_HOME=<Path of the Oracle Home>

$export PATH=$ORACLE_HOME/bin:$PATH

Step4: Clean Mount the database

SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

SQL> startup mount;

ORACLE instance started.

Total System Global Area 3221222272 bytes

Fixed Size                  8901504 bytes

Variable Size             838860800 bytes

Database Buffers         2365587456 bytes

Redo Buffers                7872512 bytes

Database mounted.

SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE

--------- -------------------- ----------------

TEST1D   MOUNTED              PRIMARY

Step6: Use NID to Change the database name

$nid TARGET=SYS DBNAME=ORCL1D SETNAME=YES

Step7: Update DB_NAME parameter in pfile/spfile

$sqlplus "/as sysdba"

Connected to an idle instance.

SQL> create pfile='/u01/product/18c/dbd1/dbs/initORCL1D.ora' from spfile;

File created.

SQL> exit

Disconnected

Edit the file /u01/product/18c/dbd1/dbs/initORCL1D.ora in vi and update the parameter DB_NAME 

save and quite 

Step8: Create New password file

$orapwd file=/u01/product/18c/dbd1/dbs/orapwORCL1D entries=5 sys=y

Enter password for SYS:

ls -lrt /u01/product/18c/dbd1/dbs/orapwORCL1D

-rw-r----- 1 oracle dba 6144 Aug  9 14:50 /u01/product/18c/dbd1/dbs/orapwORCL1D

Step9: Start the database with updated Pfile

$export ORACLE_SID=ORCL1D

$sqlplus "/as sysdba"

Connected to an idle instance.

SQL> startup pfile='/u01/product/18c/dbd1/dbs/initORCL1D.ora';

ORACLE instance started.

Total System Global Area 3221222272 bytes

Fixed Size                  8901504 bytes

Variable Size             838860800 bytes

Database Buffers         2365587456 bytes

Redo Buffers                7872512 bytes

Database mounted.

Database opened.

Step10: Create spfile and start the database from spfile

SQL> create spfile from pfile;

File created.

SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

SQL> startup;

ORACLE instance started.

Total System Global Area 3221222272 bytes

Fixed Size                  8901504 bytes

Variable Size             838860800 bytes

Database Buffers         2365587456 bytes

Redo Buffers                7872512 bytes

Database mounted.

Database opened.

SQL> show parameter spfile;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

spfile                               string      /ora_app/product/18c/dbd1/dbs/

                                                 spfileORCL1D.ora

SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE

--------- -------------------- ----------------

ORCL1D   READ WRITE           PRIMARY

Step11: Optionally Register the database in  RMAN catalog

$rman target / catalog rman@catalog

RMAN> register database;

Step12: Optionally remove the unwanted files related to old name from $ORACLE_HOME/dbs

$rm hc_TEST1D.dat snapcf_TEST1D.f orapwTEST1D lkTEST1D

Step13: Perform Full Database backup

$rman target / catalog rman@catalog

RMAN> backup database plus archivelog all delete input;

Step14: Update /etc/oratab for New DB Name
open  /etc/oratab using vi and replace the Old DB Name (TEST1D) with New DB Name (ORCL1D)

Step15: Optionally Rename the datafile, controlfile, redolog files etc

• datafiles you can rename using alter database move datafile command as shown below

 SQL> alter database move datafile '/u01_data1/TEST1D/dbfile/pdbseed/TEST1D/SYSTEM_01.DBF' to '/u01_data1/ORCL1D/dbfile/pdbseed/ORCL1D/SYSTEM_01.DBF';

• For controlfiles

find the current controlfile name
SQL>select name from v$controlfile;
Update the control_files parameter in spfile 
SQL> ALTER SYSTEM SET control_files= <new controlfile location>/control01.ctl scope=spfile;
Shut down the database
SQL> shut immediate
Move the controlfile from Old location to New location using mv command
start the database
SQL> startup

• Renaming Online Redo Log files

To rename the online redo logfiles just add the new online redo log group in new location and drop the old one.