DBNEWID – NID Utility – Change DBNAME and DBID

_____________________________________________________________________________________________________________________


Using this command line utility we can make following 3 changes to the oracle database.

   1.  Only the DBID of a database

   2.  Only the DBNAME of a database
   3.  Both the DBNAME and DBID of a database
Change DBID & DBNAME
Syntax:- nid TARGET=SYS DBNAME=<dbname>

SQL> select dbid, name from v$database;

      DBID NAME
---------- --------------------------------------------------
2136590175 CDB

$ nid TARGET=SYS DBNAME=TEST

DBNEWID: Release 19.0.0.0.0 - Production on Wed Oct 2 02:13:07 2019

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

Password:
Connected to database CDB (DBID=2136590175)

Connected to server version 19.3.0

Control Files in database:
    /u02/oradata/CDB/control01.ctl
    /u02/oradata/CDB/control02.ctl

Change database ID and database name CDB to TEST? (Y/[N]) => Y

Proceeding with operation
Changing database ID from 2136590175 to 2318690238
Changing database name from CDB to TEST
    Control File /u02/oradata/CDB/control01.ctl - modified
    Control File /u02/oradata/CDB/control02.ctl - modified
    Datafile /u02/oradata/CDB/system01.db - dbid changed, wrote new name
    Datafile /u02/oradata/CDB/sysaux01.db - dbid changed, wrote new name
    Datafile /u02/oradata/CDB/undotbs01.db - dbid changed, wrote new name
    Datafile /u02/oradata/CDB/user_data01.db - dbid changed, wrote new name
    Datafile /u02/oradata/CDB/users01.db - dbid changed, wrote new name
    Datafile /u02/oradata/CDB/temp01.db - dbid changed, wrote new name
    Control File /u02/oradata/CDB/control01.ctl - dbid changed, wrote new name
    Control File /u02/oradata/CDB/control02.ctl - dbid changed, wrote new name
    Instance shut down

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

$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Oct 2 02:21:18 2019
Version 19.3.0.0.0

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

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area  838858176 bytes
Fixed Size              8902080 bytes
Variable Size         515899392 bytes
Database Buffers      310378496 bytes
Redo Buffers            3678208 bytes
ORA-01103: database name 'TEST' in control file is not 'CDB'


SQL> alter system set db_name='TEST' scope=spfile;

System altered.

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


ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  838858176 bytes
Fixed Size              8902080 bytes
Variable Size         515899392 bytes
Database Buffers      310378496 bytes
Redo Buffers            3678208 bytes
Database mounted.
SQL> alter database open;    
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> alter database open resetlogs;

Database altered.


SQL> select dbid, name from v$database;

      DBID NAME
---------- ---------
2318690238 TEST


Make a new database backup. Because you reset the online redo logs, the old backups and archived logs are no longer usable in the current incarnation of the database.

Change DBID Only – Do not specify the dbname in the command
Syntax:- nid TARGET=SYS
SQL> select dbid, name from v$database;

      DBID NAME
---------- ---------
2318690238 TEST
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  838858176 bytes
Fixed Size              8902080 bytes
Variable Size         515899392 bytes
Database Buffers      310378496 bytes
Redo Buffers            3678208 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@TESTBOX dbs]$ nid TARGET=SYS

DBNEWID: Release 19.0.0.0.0 - Production on Wed Oct 2 03:03:11 2019

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

Password:
Connected to database TEST (DBID=2318690238)

Connected to server version 19.3.0

Control Files in database:
    /u02/oradata/CDB/control01.ctl
    /u02/oradata/CDB/control02.ctl

Change database ID of database TEST? (Y/[N]) => y

Proceeding with operation
Changing database ID from 2318690238 to 2318676857
    Control File /u02/oradata/CDB/control01.ctl - modified
    Control File /u02/oradata/CDB/control02.ctl - modified
    Datafile /u02/oradata/CDB/system01.db - dbid changed
    Datafile /u02/oradata/CDB/sysaux01.db - dbid changed
    Datafile /u02/oradata/CDB/undotbs01.db - dbid changed
    Datafile /u02/oradata/CDB/user_data01.db - dbid changed
    Datafile /u02/oradata/CDB/users01.db - dbid changed
    Datafile /u02/oradata/CDB/temp01.db - dbid changed
    Control File /u02/oradata/CDB/control01.ctl - dbid changed
    Control File /u02/oradata/CDB/control02.ctl - dbid changed
    Instance shut down

Database ID for database TEST changed to 2318676857.
All previous backups and archived redo logs for this database are unusable.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database ID.
DBNEWID - Completed succesfully.
SQL> startup
ORACLE instance started.

Total System Global Area  838858176 bytes
Fixed Size              8902080 bytes
Variable Size         515899392 bytes
Database Buffers      310378496 bytes
Redo Buffers            3678208 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> alter database open resetlogs;

Database altered.

SQL> select dbid, name from v$database;

      DBID NAME
---------- ---------
2318676857 TEST

Change DBNAME only –
Use setname option along with dbname.
Syntax:- nid TARGET=SYS DBNAME=<newDBNAME> setname=yes

SQL> select dbid, name from v$database;

      DBID NAME
---------- ---------
2318676857 TEST

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

Total System Global Area  838858176 bytes
Fixed Size              8902080 bytes
Variable Size         515899392 bytes
Database Buffers      310378496 bytes
Redo Buffers            3678208 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@TESTBOX dbs]$ nid TARGET=SYS DBNAME=cdb setname=yes

DBNEWID: Release 19.0.0.0.0 - Production on Wed Oct 2 03:09:04 2019

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

Password:
Connected to database TEST (DBID=2318676857)

Connected to server version 19.3.0

Control Files in database:
    /u02/oradata/CDB/control01.ctl
    /u02/oradata/CDB/control02.ctl

Change database name of database TEST to CDB? (Y/[N]) => y

Proceeding with operation
Changing database name from TEST to CDB
    Control File /u02/oradata/CDB/control01.ctl - modified
    Control File /u02/oradata/CDB/control02.ctl - modified
    Datafile /u02/oradata/CDB/system01.db - wrote new name
    Datafile /u02/oradata/CDB/sysaux01.db - wrote new name
    Datafile /u02/oradata/CDB/undotbs01.db - wrote new name
    Datafile /u02/oradata/CDB/user_data01.db - wrote new name
    Datafile /u02/oradata/CDB/users01.db - wrote new name
    Datafile /u02/oradata/CDB/temp01.db - wrote new name
    Control File /u02/oradata/CDB/control01.ctl - wrote new name
    Control File /u02/oradata/CDB/control02.ctl - wrote new name
    Instance shut down

Database name changed to CDB.
Modify parameter file and generate a new password file before restarting.
Succesfully changed database name.
DBNEWID - Completed succesfully.
[oracle@TESTBOX dbs]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Oct 2 03:12:49 2019
Version 19.3.0.0.0

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

sConnected to an idle instance.

SQL>      
SQL> startup
ORACLE instance started.

Total System Global Area  838858176 bytes
Fixed Size              8902080 bytes
Variable Size         515899392 bytes
Database Buffers      310378496 bytes
Redo Buffers            3678208 bytes
ORA-01103: database name 'CDB' in control file is not 'TEST'


SQL> alter system set db_name=cdb scope=spfile;

System altered.

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


ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  838858176 bytes
Fixed Size              8902080 bytes
Variable Size         515899392 bytes
Database Buffers      310378496 bytes
Redo Buffers            3678208 bytes
Database mounted.
Database opened.

DBID remains the same and the name has been changed.
SQL> select dbid, name from v$database;

      DBID NAME
---------- ---------
2318676857 CDB

_____________________________________________________________________________________________________________________

0 comments:

Post a Comment

 

acehints.com Copyright 2011-19 All Rights Reserved | Site Map | Contact | Disclaimer | Google