Oracle NID Utility : How to change the oracle database name or the database ID?

A must to know in the dba tool kit

NID utility was introduced in Oracle 10g. Prior to this utility, the alteration of the internal DBID of a database was impossible and alteration of the database name required the recreation of controlfils.

The NID utility allows to change easily the DBID and/or the database name.

The DBID is an internal, unique identifier for a database. RMAN uses this DBID to identify a database. If you restore a database on an other server and you want to backup the new database in your rman catalog, you have to change the DBID in order to register the database. You will not have to use NID if you use the duplicate command instead of the restore command.

Without rman catalog, you just want or need to rename your database ;o)

Note : This article doesn’t deal with how to change the path or your datafile or how to rename your pluggable database.

Overview

There are some steps to perform in order to change an Oracle database name or database id :

Prepared actions
Use NID
Change ORACLE_SID References
Post actions

Prepared actions

Before any action take a backup of your database (including parameters, password file, listener.ora, tnsnames.ora, …) and check parameters of the NID utility according to your release.

Note : After changing the DBID of a database, all previous backups and archived logs of the database become unusable. Changing only the database name without changing the DBID does not require you to open it with the RESETLOGS option, so database backups and archived logs are not invalidated.

Check your DBID and the database name:

SQL> select dbid, name from v$database;

Ensure that you have a recoverable whole database backup. Mount the database after a clean shutdown:

SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP MOUNT

Use NID

Invoke the NID utility on command line, specifying a valid user that has the SYSDBA privilege:

% nid TARGET=SYS

To change the database name in addition to the DBID, also specify the DBNAME parameter:

% nid TARGET=SYS DBNAME=NEW_NAME

To change only the database name, specify the SETNAME parameter:

% nid TARGET=SYS DBNAME=NEW_NAME SETNAME=YES

Sample:

oracle@XXXXXX:ORACLE-XXXXX /app/setrapack/exploitation/oracle/sql>nid TARGET=SYS DBNAME=XXXTST SETNAME=YES
DBNEWID: Release 19.0.0.0.0 - Production on Mon Apr 6 17:26:12 2020
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to database XXXDEV (DBID=XXXXX)
Connected to server version 19.6.0
Control Files in database:
/oradata/XXXXX/control01.ctl
/oradata/XXXXX/control02.ctl
Change database name of database XXXDEV to XXXTST? (Y/[N]) => Y
Proceeding with operation
Changing database name from XXXDEV to XXXTST
Control File /oradata/XXXXX/control01.ctl - modified
Control File /oradata/XXXXX/control02.ctl - modified
Datafile /oradata/XXXXX/system01.db - wrote new name
Datafile /oradata/XXXXX/pdbseed/system01.db - wrote new name
Datafile /oradata/XXXXX/sysaux01.db - wrote new name
Datafile /oradata/XXXXX/pdbseed/sysaux01.db - wrote new name
Datafile /oradata/XXXXX/undotbs01.db - wrote new name
Datafile /oradata/XXXXX/pdbseed/undotbs01.db - wrote new name
Datafile /oradata/XXXXX/users01.db - wrote new name
Datafile /oradata/XXXXX/XXXXXX/system01.db - wrote new name
Datafile /oradata/XXXXX/XXXXXX/sysaux01.db - wrote new name
Datafile /oradata/XXXXX/XXXXXX/undotbs01.db - wrote new name
Datafile /oradata/XXXXX/XXXXXX/users01.db - wrote new name
Datafile /oradata/XXXXX/setra01.db - wrote new name
Datafile /oradata/XXXXX/XXXXXX/setra01.db - wrote new name
. . .
Datafile /oradata/XXXXX/temp01.db - wrote new name
Datafile /oradata/XXXXX/XXXXXX/temp01.db - wrote new name
Datafile /oradata/XXXXX/pdbseed/temp01.db - wrote new name
Control File /oradata/XXXXX/control01.ctl - wrote new name
Control File /oradata/XXXXX/control02.ctl - wrote new name
Instance shut down
Database name changed to XXXTST.
Modify parameter file and generate a new password file before restarting.
Succesfully changed database name.
DBNEWID - Completed succesfully.

The database is left mounted but is not yet usable. So:

SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP MOUNT

If validation is not successful, then DBNEWID terminates and leaves the target database intact.

Change ORACLE_SID References

Modify the DB_NAME parameter in the initialization parameter file:

SQL> ALTER SYSTEM SET DB_NAME=NEW_NAME SCOPE=SPFILE;

Stop the instance:

SQL> SHUTDOWN IMMEDIATE

Create a new password file with :

% orapwd file=$ORACLE_HOME/dbs/orapwdNEW_NAME.ora password=password entries=10

Start database and finalize changes

Start the instance:

SQL> STARTUP MOUNT

If you change the DBID, you must open the database with the RESETLOGS option:

SQL> ALTER DATABASE OPEN RESETLOGS;

Make a new database backup mostly if you change the DBID. Previous backups and archived logs are no more usable for this incarnation.

Update your tnsnames.ora. Update your listener.ora and restart the listener if the configuration is static.

To conclude, changing database name or DBID is not really hard and can be performed with basic skills.

Références : https://docs.oracle.com/database/121/SUTIL/GUID-05B4733C-9EAA-438F-A2A4-4E84EB1E1E65.htm#SUTIL1544

Need training, support for your operation or the realization of your projects?