Oracle Database Name Change Using NID

Hi,

Database name is very important parameter in Oracle especially for backup operations.

 

We need to change database name of Oracle when we prepare testing or preprod database to seperate from Production database.

Database should be in mount mode for this operation. So shutdown database and startup mount mode.

 

You can change database name ( db_name parameter) parameter with nid tool like following.

oracle:deveci01:/home/oracle:> nid TARGET=SYS/oracle DBNAME=CLONE

DBNEWID: Release 18.3.0.0.0 - Production on Mon Sep 8 14:18:07 2018

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

Connected to database DEVECI18C (DBID=2402765690)

Connected to server version 18.3.0

Control Files in database:
+DATA/DEVECI18C/controlfile/control02
+RECO/DEVECI18C/controlfile/control02

Change database ID and database name DEVECI18C to CLONE? (Y/[N]) => y

Proceeding with operation
Changing database ID from 2402765690 to 1056563552
Changing database name from DEVECI18C to CLONE
Control File +DATA/DEVECI18C/controlfile/control02 - modified
Control File +RECO/DEVECI18C/controlfile/control02 - modified
Datafile +DATA/DEVECI18C/datafile/system01.db - dbid changed, wrote new name
Datafile +DATA/DEVECI18C/datafile/sysaux01.db - dbid changed, wrote new name
Datafile +DATA/DEVECI18C/datafile/undotbs01.db - dbid changed, wrote new name
Datafile +DATA/DEVECI18C/datafile01.db - dbid changed, wrote new namew
Datafile +DATA/DEVECI18C/datafile/temp01.db - dbid changed, wrote new name
Control File +DATA/DEVECI18C/controlfile/control02 - dbid changed, wrote new name
Control File +RECO/DEVECI18C/controlfile/control02 - dbid changed, wrote new name
Instance shut down

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

 

Database name and ID has changed like above.

 

If database name has changed then you should change db_name parameter in pfile or spfile otherwise you can get following error.

oracle:deveci01:/home/oracle:>export ORACLE_SID=CLONE
oracle:deveci01:/home/oracle:>sqlplus / as sysdba

SQL*Plus: Release 18.3.0.0.0 Production on Mon Sep 8 14:18:54 2018

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

Connected to an idle instance.

SQL> startup mount;
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/oracle/product/18.3.0.0/db/dbs/initCLONE.ora'
SQL> startup mount;
ORACLE instance started.

Total System Global Area 2.1379E+10 bytes
Fixed Size 2230912 bytes
Variable Size 1.0469E+10 bytes
Database Buffers 1.0872E+10 bytes
Redo Buffers 36106240 bytes
ORA-01103: database name 'CLONE' in control file is not 'DEVECI18C'

Shutdown database and change pfile with new db_name. like following.

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

ORACLE instance shut down.

 

I have changed pfile name with initCLONE.ora and db_name is changed from DEVECI18C to CLONE.

SQL> 
SQL> startup mount;
ORACLE instance started.

Total System Global Area 2.1379E+10 bytes
Fixed Size 2230912 bytes
Variable Size 1.0469E+10 bytes
Database Buffers 1.0872E+10 bytes
Redo Buffers 36106240 bytes
Database mounted.

 

Now we need to open database with resetlogs like following. And we will check database open mode and name.

SQL> alter database open resetlogs;

Database altered.



SQL> select open_mode, name from v$database;

OPEN_MODE NAME
-------------------- ---------
READ WRITE   CLONE

SQL>

 

Mehmet Salih Deveci

I am Founder of IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress mehmetsalih.deveci@outlook.com.-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  mehmetsalih.deveci@outlook.com a mail atabilirsiniz.

Leave a Reply

Your email address will not be published. Required fields are marked *