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>
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>
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>
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 ~]$
[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>
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>
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
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>
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