To rename a database, you need to change the header of the database file headers, the controlfiles and god knows what else :) Luckily for us, there is a tool by Oracle “NID” (New ID) which will do all that for us. Historically, I used to change the DB name manually, by: recreating the spfile, controlfile and opening with resetlogs.
So let's see how all that can be done
First, we need to shutdown the database and start it in a mount state:
Startup in mount
SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount; ORACLE instance started. Total System Global Area 3724541952 bytes Fixed Size 8627296 bytes Variable Size 872418208 bytes Database Buffers 2835349504 bytes Redo Buffers 8146944 bytes Database mounted.
Then we can use the NID tool to change the DB name as follows:
Change DB Name
[oracle@temporaback admin]$ nid target=sys/password DBNAME=newname
DBNEWID: Release 12.2.0.1.0 - Production on Wed May 11 11:44:59 2022
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Connected to database OLDNAME(DBID=************)
Connected to server version 12.2.0
Control Files in database:
/app/data/orcltest/control01.ctl
/app/data/fast_recovery_area/orcltest/control02.ctl
Change database ID and database name OLDNAME to NEWNAME? (Y/[N]) => Y
Proceeding with operation
Changing database ID from ************* to *********
Changing database name from OLDNAME to NEWNAME
Control File /app/data/orcltest/control01.ctl - modified
Control File /app/data/fast_recovery_area/orcltest/control02.ctl - modified
Datafile /app/data/orcltest/system01.db - dbid changed, wrote new name
Datafile /app/data/orcltest/tbs_data_02.db - dbid changed, wrote new name
Datafile /app/data/orcltest/sysaux01.db - dbid changed, wrote new name
Datafile /app/data/orcltest/undotbs01.db - dbid changed, wrote new name
Datafile /app/data/orcltest/tbs_data_01.db - dbid changed, wrote new name
Datafile /app/data/orcltest/users01.db - dbid changed, wrote new name
Datafile /app/data/orcltest/tbs_ind_01.db - dbid changed, wrote new name
Datafile /app/data/orcltest/tbs_inx_01.db - dbid changed, wrote new name
Datafile /app/data/orcltest/temp01.db - dbid changed, wrote new name
Control File /app/data/orcltest/control01.ctl - dbid changed, wrote new name
Control File /app/data/fast_recovery_area/orcltest/control02.ctl - dbid changed, wrote new name
Instance shut down
Database name changed to NEWNAME.
Modify parameter file and generate a new password file before restarting.
Database ID for database ORCLBACK changed to 3567561723.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.
[oracle@temporaback admin]$
Then we can change the pfile and startup the DB with reset logs:
Startup the DB (After changing the init file
[oracle@temporaback dbs]$ vi initorclback.ora [oracle@temporaback dbs]$ sqlplus SQL*Plus: Release 12.2.0.1.0 Production on Wed May 11 11:46:45 2022 Copyright (c) 1982, 2016, Oracle. All rights reserved. Enter user-name: / as sysdba Connected to an idle instance. SQL> startup mount; ORACLE instance started. Total System Global Area 3724541952 bytes Fixed Size 8627296 bytes Variable Size 872418208 bytes Database Buffers 2835349504 bytes Redo Buffers 8146944 bytes Database mounted. SQL> alter database open resetlogs; Database altered. SQL>