Tuesday, March 11, 2025

How to change the DB name - DBNEWID Utility

   No comments
  • Introduction: DBNEWID is a database utility that can change the internal database identifier (DBID) and the database name (DBNAME) for an operational database. This guide will walk you through the DBNEWID utility step-by-step.

  • Ramifications of Changing the DBID and DBNAME: Before you change the DBID and DBNAME of a database with the DBNEWID utility, review these guidelines. Also post changing the DBID/DBNAME previous backups and archivelogs will not be useful.

  • Syntax:
    • o To change both DBID & DBNAME: nid TARGET=sys/password@orcl DBNAME=orcl2
    o To change only DBNAME: nid TARGET=sys/password@orcl DBNAME=orcl2 SETNAME=YES
    o To change only DBID: nid TARGET=sys/password@orcl

  • Prerequisites:
    • o Downtime required at Database end for 30 mins.
    o Full RMAN DB backup to rollback in case of any issue.

  • Environment:
    • Hostname: apex
    IP: 192.168.101.7
    ORACLE_SID : APEXDB
    DB NAME: APEX

  • Now let's use DBNEWID utility to change DBNAME only step by step:
1. Check DB status and Create pfile : 

SQL> select DBID, NAME, open_mode, database_role from v$database;
DBID        NAME      OPEN_MODE    DATABASE_ROLE
----------- --------- ------------ ---------------
3003259501  APEX      READ WRITE   PRIMARY

SQL> create pfile='/home/oracle/pfilebkp.ora' from spfile;
File created.

SQL> exit
2. Now shutdown the database and start in mount state : 

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

SQL> startup mount
ORACLE instance started.
Total System Global Area 1560280200 bytes
Fixed Size                  9178248 bytes
Variable Size            1342177280 bytes
Database Buffers          201326592 bytes
Redo Buffers                7598080 bytes
Database mounted.

SQL> select DBID, NAME, open_mode, database_role from v$database;
DBID        NAME      OPEN_MODE    DATABASE_ROLE
----------- --------- ------------ ----------------
3003259501  APEX      MOUNTED      PRIMARY

SQL>
3. Now run nid command to change DB name. Here we will change DB name from APEX to APEXDB 

[oracle@apex ~]$ nid TARGET=/ DBNAME=apexdb SETNAME=YES

DBNEWID: Release 19.0.0.0.0 - Production on Thu Mar 6 20:28:05 2025

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

Connected to database APEX (DBID=3003259501)

Connected to server version 19.24.0
Control Files in database:
    /u02/app/oracle/oradata/APEXDB/control01.ctl
    /u02/app/oracle/fast_recovery_area/APEXDB/control02.ctl

Change database name of database APEX to APEXDB? (Y/[N]) => 
Y


Proceeding with operation
Changing database name from APEX to APEXDB
    Control File /u02/app/oracle/oradata/APEXDB/control01.ctl - modified
    Control File /u02/app/oracle/fast_recovery_area/APEXDB/control02.ctl - modified
    Datafile /u02/app/oracle/oradata/APEXDB/system01.db - wrote new name
    Datafile /u02/app/oracle/oradata/APEXDB/sysaux01.db - wrote new name
    Datafile /u02/app/oracle/oradata/APEXDB/undotbs01.db - wrote new name
    Datafile /u02/app/oracle/oradata/APEXDB/pdbseed/system01.db - wrote new name
    Datafile /u02/app/oracle/oradata/APEXDB/pdbseed/sysaux01.db - wrote new name
    Datafile /u02/app/oracle/oradata/APEXDB/users01.db - wrote new name
    Datafile /u02/app/oracle/oradata/APEXDB/pdbseed/undotbs01.db - wrote new name
    Datafile /u02/app/oracle/oradata/APEXDB/apexpdb/system01.db - wrote new name
    Datafile /u02/app/oracle/oradata/APEXDB/apexpdb/sysaux01.db - wrote new name
    Datafile /u02/app/oracle/oradata/APEXDB/apexpdb/undotbs01.db - wrote new name
    Datafile /u02/app/oracle/oradata/APEXDB/apexpdb/users01.db - wrote new name
    Datafile /u02/app/oracle/oradata/APEXDB/apexpdb/apex232_01.db - wrote new name
    Datafile /u02/app/oracle/oradata/APEXDB/apexpdb/apex241_01.db - wrote new name
    Datafile /u02/app/oracle/oradata/APEXDB/apexpdb/test01.db - wrote new name
    Datafile /u02/app/oracle/oradata/APEXDB/apexpdb/statpack01.db - wrote new name
    Datafile /u02/app/oracle/oradata/APEXDB/temp01.db - wrote new name
    Datafile /u02/app/oracle/oradata/APEXDB/pdbseed/temp012024-09-25_00-10-37-758-AM.db - wrote new name
    Datafile /u02/app/oracle/oradata/APEXDB/apexpdb/temp01.db - wrote new name
    Control File /u02/app/oracle/oradata/APEXDB/control01.ctl - wrote new name
    Control File /u02/app/oracle/fast_recovery_area/APEXDB/control02.ctl - wrote new name
    Instance shut down

Database name changed to APEXDB.
Modify parameter file and generate a new password file before restarting.
Successfully changed database name.
DBNEWID - Completed successfully.
4. Now change DB name value in pfile from APEX to APEXDB. Then startup database with pfile. 

[oracle@apex ~]$ cat pfilebkp.ora
apexdb.__data_transfer_cache_size=0
apexdb.__db_cache_size=150994944
apexdb.__inmemory_ext_roarea=0
apexdb.__inmemory_ext_rwarea=0
apexdb.__java_pool_size=184549376
apexdb.__large_pool_size=16777216
apexdb.__oracle_base='/u02/app/oracle' #ORACLE_BASE set from environment
apexdb.__pga_aggregate_target=536870912
apexdb.__sga_target=1023410176
apexdb.__shared_io_pool_size=50331648
apexdb.__shared_pool_size=603979776
apexdb.__streams_pool_size=0
apexdb.__unified_pga_pool_size=0
*.archive_lag_target=1200
*.audit_file_dest='/u02/app/oracle/admin/apexdb/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/u02/app/oracle/oradata/APEXDB/control01.ctl','/u02/app/oracle/fast_recovery_area/APEXDB/control02.ctl'
*.db_block_size=8192
*.db_file_name_convert='/u02','/u01'
*.db_name='apexdb'
*.db_recovery_file_dest='/u02/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=12732m
*.db_unique_name='APEXDBDC'
*.diagnostic_dest='/u02/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=apexdbXDB)'
*.enable_pluggable_database=true
*.log_archive_max_processes=30
*.memory_target=1476m
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'

[oracle@apex ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Mar 6 20:30:19 2025
Version 19.24.0.0.0

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

Connected to an idle instance.

SQL> startup pfile='/home/oracle/pfilebkp.ora';
ORACLE instance started.

Total System Global Area 1560280200 bytes
Fixed Size                  9178248 bytes
Variable Size            1342177280 bytes
Database Buffers          201326592 bytes
Redo Buffers                7598080 bytes
Database mounted.
Database opened.

SQL> select DBID, NAME, open_mode, database_role from v$database;

DBID        NAME      OPEN_MODE            DATABASE_ROLE
----------- --------- -------------------- ----------------
3003259501  APEXDB    READ WRITE           PRIMARY

SQL>
5. Now create spfile from pfile and then restart DB with spfile 

SQL> create spfile from pfile='/home/oracle/pfilebkp.ora';

File created.

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

SQL> startup
ORACLE instance started.

Total System Global Area 1560280200 bytes
Fixed Size                  9178248 bytes
Variable Size            1342177280 bytes
Database Buffers          201326592 bytes
Redo Buffers                7598080 bytes
Database mounted.
Database opened.

SQL> select DBID, NAME, open_mode, database_role from v$database;

DBID        NAME      OPEN_MODE            DATABASE_ROLE
----------- --------- -------------------- ----------------
3003259501  APEXDB    READ WRITE           PRIMARY

SQL>
6. Now change the DB name is listener.ora & tnsnames.ora file and reload the Listener. Also take full DB backup as old backups will be not useful.

Thanks for visiting!!

0 comments:

Post a Comment