How to Rename Database and DBID using DBNEWID in Oracle

Rename Database and DBID using DBNEWID in Oracle

In this article, we are going to demonstrate rename of the database along with DBID using utility DBNEWID in Oracle. The question comes in mind why we require to rename DB? Whenever we do clone a database to a target (clone DB) server from the source (production DB) it has the same DBID after clone and becomes mandatory to rename if source and target database using the same RMAN catalog, since RMAN recognize by DBID. To avoid this situation we do rename of database along with DBID where both source and target DB has the same DBID. Other reasons to rename the database or DBID depend on requirements. DBNEWID utility does modify control file with new database name along with DBID depends or accordingly used parameter while executing NID utility.

Using DBNEWID utility we can rename as below:

1. Both Database & DBID rename.
2. Only DBID rename.
3. Only Database rename.

Let’s do practically one by one above three options.

1. Both Database & DBID rename.

1.1. Backup of database and spfile: Take a full database backup and create pfile using spfile. Click here for RMAN Full Database Backup Scripts in Oracle.

SQL> CREATE PFILE FROM SPFILE;

File created.

1.2. Database name & DBID along with physical files: Capture database name & DBID before executing utility DBNEWID along with basics details of DB. Click here to get the command for How to find all physicals files in Oracle.

SQL> SELECT DBID,NAME FROM V$DATABASE;

      DBID NAME
---------- ---------
 259564834 DBSGURU

1.3. Restart database: Stop the database and start in the mount stage.

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

Total System Global Area  788526632 bytes
Fixed Size                  9139752 bytes
Variable Size             499122176 bytes
Database Buffers          276824064 bytes
Redo Buffers                3440640 bytes
Database mounted.

1.4. Rename database and DBID in control file: Follow the below command to rename the database along with DBID using utility DBNEWID. Make sure before executing the command you have a valid full database backup. After renaming of database all previous backups will be unusable along with archived redo logs. Click here to view the complete log of utility DBNEWID.

nid target=/ DBNAME=gurunew LOGFILE=/home/oracle/dbsguru_renameDB_DBID_nid1.log

TARGET: Username/Password
DBNAME: New database name
LOGFILE: Output Log

[oracle@DBsGuruN admin]$ nid TARGET=/ DBNAME=gurunew LOGFILE=/home/oracle/dbsguru_renameDB_DBID_nid1.log
DBNEWID: Release 19.0.0.0.0 - Production on Sat Jun 26 21:32:32 2021

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

Connected to database DBSGURU (DBID=259564834)
----------------Trimmed Data----------------
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.

1.5. Change database new name in SPFILE/PFILE: Rename database in SPFILE/PFILE. Here we are performing via SPFILE.

SQL> startup nomount
ORACLE instance started.

Total System Global Area  788526632 bytes
Fixed Size                  9139752 bytes
Variable Size             499122176 bytes
Database Buffers          276824064 bytes
Redo Buffers                3440640 bytes
SQL> show parameter db_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      dbsguru
SQL> ALTER SYSTEM SET DB_NAME=gurunew SCOPE=SPFILE;

System altered.

SQL> shu immediate
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@DBsGuruN admin]$ cd /u01/app/oracle/product/19.0.0/db_1/dbs/
[oracle@DBsGuruN dbs]$ ls -lrt spfiledbsguru.ora
-rw-r-----. 1 oracle oinstall 3584 Jun 26 21:41 spfiledbsguru.ora
[oracle@DBsGuruN dbs]$ cp spfiledbsguru.ora spfiledbsguru.ora.bkp
[oracle@DBsGuruN dbs]$ mv spfiledbsguru.ora spfilegurunew.ora
[oracle@DBsGuruN dbs]$ ls -lrt spfilegurunew.ora
-rw-r-----. 1 oracle oinstall 4608 Jun 26 21:45 spfilegurunew.ora

1.6. Add entry in oratab: Using any editor command-line tool add an entry in the next line for new name DB in /etc/oratab.

[oracle@DBsGuruN dbs]$ vi /etc/oratab

gurunew:/u01/app/oracle/product/19.0.0/db_1:N

1.7. Start database: Start the database in mount state and open it with the RESETLOGS option.

SQL> startup mount
ORACLE instance started.

Total System Global Area  788526632 bytes
Fixed Size                  9139752 bytes
Variable Size             499122176 bytes
Database Buffers          276824064 bytes
Redo Buffers                3440640 bytes
Database mounted.
SQL> ALTER DATABASE OPEN RESETLOGS;

Database altered.

1.8. Validate database new name: Now Validate the database new name along with new DBID where new DBID changed from 259564834 to 3936556209.

SQL> select dbid,name from v$database;

      DBID NAME
---------- ---------
3936556209 GURUNEW

1.9. Validate database services and update TNS entries: Validate database services in LISTENER. Optionally you can reload LISTENER only if all desire services are not started/visible for the database and update/add TNS wherever require.

[oracle@DBsGuruN dbs]$ lsnrctl reload

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 26-JUN-2021 21:46:18

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=DBsGuruN.dbsguru.com)(PORT=1521)))
The command completed successfully
[oracle@DBsGuruN dbs]$ lsnrctl status

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 26-JUN-2021 21:46:20

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=DBsGuruN.dbsguru.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                26-JUN-2021 20:36:26
Uptime                    0 days 1 hr. 9 min. 54 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/19.0.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/DBsGuruN/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=DBsGuruN.dbsguru.com)(PORT=1521)))
Services Summary...
Service "GURUNEW" has 1 instance(s).
  Instance "gurunew", status READY, has 1 handler(s) for this service...
Service "c40921a8bbf963f9e055515375a76b71" has 1 instance(s).
  Instance "gurunew", status READY, has 1 handler(s) for this service...
Service "dbsguruXDB" has 1 instance(s).
  Instance "gurunew", status READY, has 1 handler(s) for this service...
Service "dbsguru_pdb" has 1 instance(s).
  Instance "gurunew", status READY, has 1 handler(s) for this service...
The command completed successfully

1.10. Create a password file: Create a new password file.

[oracle@DBsGuruN dbs]$ orapwd file=$ORACLE_HOME/dbs/orapwgurunew password=P@ssw0rd#567 entries=10
[oracle@DBsGuruN dbs]$ ls -lrt orapwgurunew
-rw-r-----. 1 oracle oinstall 6144 Jun 26 22:08 orapwgurunew

1.11. Backup of database and SPFILE: It’s recommended to take a full database backup and create PFILE using SPFILE since all previous backups are unusable due to the new name and DBID. Click here for RMAN Full Database Backup Scripts in Oracle.

SQL> CREATE PFILE FROM SPFILE;

File created.

1.12: Application checkout: Handover to application to do checkout for database and don’t forget to share new connection strings as required.

2. Only DBID rename.

In this section we will chnage only database ID 3936556209 (DBID) where all steps will remain same as previous section except NID command 1.4. Below are steps to change DBID.

Follow same steps from 1.1 to 1.12 except below two steps.

2.4. Rename DBID in control file: Follow the below command to rename DBID using utility DBNEWID. Make sure before executing the command you have a valid full database backup. After renaming of DBID all previous backups will be unusable along with archived redo logs. Click here to view the complete log of utility DBNEWID.

nid TARGET=/ LOGFILE=/home/oracle/gurunew_DBID_nid1.log

TARGET: Username/Password
LOGFILE: Output Log

[oracle@DBsGuruN ~]$ nid TARGET=/ LOGFILE=/home/oracle/gurunew_DBID_nid1.log
DBNEWID: Release 19.0.0.0.0 - Production on Sat Jun 26 22:23:44 2021

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

Connected to database GURUNEW (DBID=3936556209)

Connected to server version 19.3.0
----------------Trimmed Data----------------
Succesfully changed database ID.
DBNEWID - Completed succesfully.

2.8. Validate database new ID: Now Validate the database new DBID where the new DBID changed from 3936556209 to 3936528048.

SQL> SELECT DBID,NAME FROM V$DATABASE;

      DBID NAME
---------- ---------
3936528048 GURUNEW

3. Only Database rename.

In this section we will rename database name only from GURUNEW to DBDGURU. Below are steps.

3.1. Backup of database and spfile: Take a full database backup and create pfile using spfile. Click here for RMAN Full Database Backup Scripts in Oracle.

SQL> CREATE PFILE FROM SPFILE;

File created.

3.2. Database name & DBID: Capture database name & DBID before execute utility DBNEWID.

SQL> SELECT DBID,NAME FROM V$DATABASE;

      DBID NAME
---------- ---------
3936528048 GURUNEW

3.3. Restart database: Stop the database and start in the mount stage.

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

Total System Global Area  788526632 bytes
Fixed Size                  9139752 bytes
Variable Size             499122176 bytes
Database Buffers          276824064 bytes
Redo Buffers                3440640 bytes
Database mounted.

3.4. Rename database and DBID in control file: Follow the below command to rename the database only using utility DBNEWID. Make sure before executing the command you have a valid full database backup. Click here to view the complete log of utility DBNEWID.

nid target=/ DBNAME=dbsguru setname=YES logfile=/home/oracle/gurunew_renameDB_nid1.log

TARGET: Username/Password
SETNAME: Set a new database name only
LOGFILE: Output Log

[oracle@DBsGuruN ~]$ nid target=/ DBNAME=dbsguru setname=YES logfile=/home/oracle/gurunew_renameDB_nid1.log
DBNEWID: Release 19.0.0.0.0 - Production on Fri Jul 2 12:12:25 2021

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

Connected to database GURUNEW (DBID=3936528048)
----------------Trimmed Data----------------
Succesfully changed database name.
DBNEWID - Completed succesfully.

3.5. Change database new name in SPFILE/PFILE: Rename database in SPFILE/PFILE. Here we are performing via PFILE. Edit PFILE which was taken in step 3.1 with a new DB name for parameter DB_NAME=DBSGURU.

[oracle@DBsGuruN ~]$ cd $ORACLE_HOME/dbs
[oracle@DBsGuruN dbs]$ ls -lrt initgurunew.ora
-rw-r--r--. 1 oracle oinstall 1031 Jun 26 22:17 initgurunew.ora
[oracle@DBsGuruN dbs]$ cp initgurunew.ora initdbsguru.ora
[oracle@DBsGuruN dbs]$ vi initdbsguru.ora
*.db_name='dbsguru'

3.6. Add entry in oratab: Using any editor command-line tool add an entry in the next line for new name DB in /etc/oratab.

[oracle@DBsGuruN dbs]$ vi /etc/oratab

dbsguru:/u01/app/oracle/product/19.0.0/db_1:N

3.7. Start database and create SPFILE: Start the database using PFILE and then create SPFILE.

[oracle@DBsGuruN dbs]$ . oraenv
ORACLE_SID = [gurunew] ? dbsguru
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@DBsGuruN dbs]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jul 2 12:22:40 2021
Version 19.3.0.0.0

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

Connected to an idle instance.

SQL> startup pfile='initdbsguru.ora'
ORACLE instance started.

Total System Global Area  788526632 bytes
Fixed Size                  9139752 bytes
Variable Size             499122176 bytes
Database Buffers          276824064 bytes
Redo Buffers                3440640 bytes
Database mounted.
Database opened.
SQL> CREATE SPFILE FROM PFILE;

File created.

SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup
ORACLE instance started.

Total System Global Area  788526632 bytes
Fixed Size                  9139752 bytes
Variable Size             499122176 bytes
Database Buffers          276824064 bytes
Redo Buffers                3440640 bytes
Database mounted.
Database opened.
SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/19.0.0/db_1/dbs/spfiledbsguru.ora

3.8. Validate database new name: Now Validate the database new name changed to DBSGURU but still DBID is 3936528048.

SQL> select dbid,name from v$database;

      DBID NAME
---------- ---------
3936528048 DBSGURU

3.9. Validate database services and update TNS entries: Validate database services in LISTENER. Optionally you can reload LISTENER only if all desire services are not started for the database and update/add TNS wherever require.

[oracle@DBsGuruN ~]$ lsnrctl status

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 02-JUL-2021 12:46:54

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=DBsGuruN.dbsguru.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                02-JUL-2021 12:42:57
Uptime                    0 days 0 hr. 4 min. 32 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/19.0.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/DBsGuruN/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=DBsGuruN.dbsguru.com)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "86b637b62fdf7a65e053f706e80a27ca" has 1 instance(s).
  Instance "dbsguru", status READY, has 1 handler(s) for this service...
Service "c40921a8bbf963f9e055515375a76b71" has 1 instance(s).
  Instance "dbsguru", status READY, has 1 handler(s) for this service...
Service "dbsguru" has 1 instance(s).
  Instance "dbsguru", status READY, has 1 handler(s) for this service...
Service "dbsguruXDB" has 1 instance(s).
  Instance "dbsguru", status READY, has 1 handler(s) for this service...
Service "dbsguru_pdb" has 1 instance(s).
  Instance "dbsguru", status READY, has 1 handler(s) for this service...
The command completed successfully

3.10. Create a password file: Create a new password file.

[oracle@DBsGuruN ~]$ orapwd file=$ORACLE_HOME/dbs/orapwdbsguru password=P@ssw0rd#567 force=Y
[oracle@DBsGuruN ~]$ ls -lrt $ORACLE_HOME/dbs/orapwdbsguru
-rw-r-----. 1 oracle oinstall 6144 Jul  2 12:50 /u01/app/oracle/product/19.0.0/db_1/dbs/orapwdbsguru

3.11. Backup of database and SPFILE: It’s recommended to take a full database backup and create a PFILE using SPFILE. Click here for RMAN Full Database Backup Scripts in Oracle.

SQL> CREATE PFILE FROM SPFILE;

File created.

3.12: Application checkout: Handover to application to do checkout for database and don’t forget to share new connection strings as required.

Click here to know more about Rename Database using CONTROLFILE in Oracle

This document is just for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.

Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the most recent update. Click here to understand more about our pursuit.

BACKUP, BACKUP AND RECOVERY, DATABASE, ORACLE

DBsGuru