- 15 Oct, 2022
- 0 Comments
- 3 Mins Read
How to change database name using NID Utility
How to change database name using NID Utility
Follow below steps for changing the oracle database name using nid utility.
1. Mount the database
SQL> startup mount ORACLE instance started. Total System Global Area 1426059984 bytes Fixed Size 9134800 bytes Variable Size 352321536 bytes Database Buffers 1056964608 bytes Redo Buffers 7639040 bytes Database mounted. SQL> SQL> SQL> select name, open_mode from v$database; NAME OPEN_MODE --------- -------------------- PRIM MOUNTED
SQL>2. Run the NID utility
SYNTAX – nid sys/password@CURRENT_DBNAME DBNAME=NEW_DBNAME
[oracle@test admin]$ nid target=sys/oracle@prim dbname=dev
DBNEWID: Release 19.0.0.0.0 - Production on Sat Oct 15 12:29:03 2022
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to database PRIM (DBID=115414704)
Connected to server version 19.3.0
Control Files in database:
/data/app/oracle/oradata/PRIM/control01.ctl
/data/app/oracle/oradata/PRIM/control02.ctl
Change database ID and database name PRIM to DEV? (Y/[N]) => Y
Proceeding with operation
Changing database ID from 115414704 to 4196487951
Changing database name from PRIM to DEV
Control File /data/app/oracle/oradata/PRIM/control01.ctl - modified
Control File /data/app/oracle/oradata/PRIM/control02.ctl - modified
Datafile /data/app/oracle/oradata/PRIM/system01.db - dbid changed, wrote new name
Datafile /data/app/oracle/oradata/PRIM/sysaux01.db - dbid changed, wrote new name
Datafile /data/app/oracle/oradata/PRIM/undotbs01.db - dbid changed, wrote new name
Datafile /data/app/oracle/oradata/PRIM/pdbseed/system01.db - dbid changed, wrote new name
Datafile /data/app/oracle/oradata/PRIM/pdbseed/sysaux01.db - dbid changed, wrote new name
Datafile /data/app/oracle/oradata/PRIM/users01.db - dbid changed, wrote new name
Datafile /data/app/oracle/oradata/PRIM/pdbseed/undotbs01.db - dbid changed, wrote new name
Datafile /data/app/oracle/oradata/PRIM/pdbprim/system01.db - dbid changed, wrote new name
Datafile /data/app/oracle/oradata/PRIM/pdbprim/sysaux01.db - dbid changed, wrote new name
Datafile /data/app/oracle/oradata/PRIM/pdbprim/undotbs01.db - dbid changed, wrote new name
Datafile /data/app/oracle/oradata/PRIM/pdbprim/users01.db - dbid changed, wrote new name
Datafile /data/app/oracle/oradata/PRIM/temp01.db - dbid changed, wrote new name
Datafile /data/app/oracle/oradata/PRIM/pdbseed/temp012022-10-14_17-55-45-849-PM.db - dbid changed, wrote new name
Datafile /data/app/oracle/oradata/PRIM/pdbprim/temp01.db - dbid changed, wrote new name
Control File /data/app/oracle/oradata/PRIM/control01.ctl - dbid changed, wrote new name
Control File /data/app/oracle/oradata/PRIM/control02.ctl - dbid changed, wrote new name
Instance shut down
Database name changed to DEV.
Modify parameter file and generate a new password file before restarting.
Database ID for database DEV changed to 4196487951.
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.
3. change the db_name parameter in the parameter file.
SQL> shutdown immediate; ORA-01507: database not mounted ORACLE instance shut down. SQL> startup nomount ORACLE instance started. Total System Global Area 1426059984 bytes Fixed Size 9134800 bytes Variable Size 352321536 bytes Database Buffers 1056964608 bytes Redo Buffers 7639040 bytes SQL> SQL> SQL> alter system set db_name=DEV scope=spfile; System altered. SQL> shu immediate; ORA-01507: database not mounted ORACLE instance shut down. SQL>SQL> SQL> startup nomount ORACLE instance started. Total System Global Area 1426059984 bytes Fixed Size 9134800 bytes Variable Size 352321536 bytes Database Buffers 1056964608 bytes Redo Buffers 7639040 bytes SQL> SQL> SQL> show parameter db_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_name string DEV SQL> SQL> SQL> shu immediate ORA-01507: database not mounted ORACLE instance shut down.
4. Rename the spfile to new db name
[oracle@test dbs]$ cp spfileprim.ora spfileDEV.ora [oracle@test dbs]$ export ORACLE_SID=DEV [oracle@test dbs]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Sat Oct 15 13:29:27 2022 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to an idle instance. SQL> startup nomount ORACLE instance started. Total System Global Area 1426059984 bytes Fixed Size 9134800 bytes Variable Size 352321536 bytes Database Buffers 1056964608 bytes Redo Buffers 7639040 bytes SQL> SQL> SQL> alter database mount; Database altered. SQL> SQL> SQL> alter database open resetlogs; Database altered. SQL> show parameter local NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ local_listener string LISTENER_PRIM parallel_force_local boolean FALSE SQL> SQL> SQL> alter system register; System altered. SQL> select name, open_mode from v$database; NAME OPEN_MODE --------- -------------------- DEV READ WRITE