I will explain Oracle Rename Database using NID Utility in this post.
Oracle Rename Database with DBNEWID utility (nid)
Sometimes you need renaming Oracle database name or Oracle SID.
Database name is very important parameter in Oracle especially for backup operations and Preprod, UAT databases.
You need to rename 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 as follows.
SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL>
SQL> startup mount ORACLE instance started. Total System Global Area 2.6867E+11 bytes Fixed Size 30150224 bytes Variable Size 2.5770E+10 bytes Database Buffers 2.4267E+11 bytes Redo Buffers 207720448 bytes Database mounted. SQL>
You can rename 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>
Do you want to learn Oracle Database for Beginners, then read the following articles.
Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )