How to Change Oracle Database Name Using Control File

In this article, we will explore how to change the Oracle Database name using the control file.

1. Verify the current database name before proceeding with the name change.

SQL> select name, open_mode from v$database;

2. Create a control file trace backup and direct it to a specified location of your choice.

SQL> alter database backup controlfile to trace as 'C:\Ayaan\Oracle\controltrace.log';

3. Create a PFILE from the SPFILE, then edit the PFILE to change the parameter db_name to TESTCDB (the new name).

SQL> show parameter pfile

SQL> create pfile='C:\APP\PRODUCT\19C\DB_HOME\DATABASE\initTESTCDB.ora' from spfile;

4. Shut down the database, then start it in NOMOUNT mode using the new PFILE, and verify the DB_NAME.

SQL> shut immediate

SQL> startup nomount pfile='C:\APP\PRODUCT\19C\DB_HOME\DATABASE\initTESTCDB.ora';

SQL> show parameter db_name

5. Now open the trace backup of the control file in a text editor and modify the first line from <CREATE CONTROLFILE REUSE DATABASE "ORCLCDB" NORESETLOGS  NOARCHIVELOG> to <CREATE CONTROLFILE SET DATABASE "TESTCDB" RESETLOGS> and run the script.

Note: Rename the old control files prior to creating the new control files.

6. Now open the database using the RESETLOGS option and verify the database name.

SQL> alter database open resetlogs;

SQL> select name, open_mode from v$database;

7. Create an SPFILE from the PFILE, then bounce the database (shut it down and start it again).

SQL> create spfile from pfile='C:\APP\PRODUCT\19C\DB_HOME\DATABASE\INITTESTCDB.ORA';

SQL> shut immediate;

SQL> startup

SQL> select name,open_mode from v$database;