Oracle Change Database Name

In this Oracle tutorial, we will learn how to change the name of the database in Oracle Database 21c, 19c, and 12c.

Oracle change database name

Oracle Database name can be changed using the NID tool, which is installed by default when installing Oracle Database 21c on the Windows operating system.

Please note that the database name cannot exceed 8 characters; otherwise, Oracle will throw an error message: ORA-01127.

Below, we have discussed the steps to change the database name in Oracle. We changed the database name from salesDB to sales2022DB. The new name exceeds 8 characters, and we got an error at step 8.

We repeated the steps and renamed the database from salesDB to sales22. Follow these steps to change the database name in Oracle:

1. Ensure you have created a backup of the database to tackle any uncertainty.
2. Set the oracle sid to the database name of choice on the command prompt.
3. Log in as a sys or any other user with the privilege to perform actions on the database.
4. Create a pfile from spfile that can be used later to start a new database.

SET ORACLE_SID=SALESDB

-- login as sysdba
SQLPLUS / AS SYSDBA

-- view spfile
SHOW PAPRAMETER SPFILE;

-- create spfile
CREATE PFILE FROM SPFILE;

-- show database name
SHOW PARAMETER DB_NAME;

The image below shows an implementation of the above scripts on the command prompt. Yellow texts are the script, and green texts are the output.

In the image below, the yellow text is the script, and the blue is the result or output of that script.

4. The database name can be changed only when it is in the mount state. Shut down the database and then start it in the mount state.

-- shutdown the current database
SHUTDOWN IMMEDIATE

-- start the database in mount state
STARTUP MOUNT

5. Exit from the SQL PLUS and on the command prompt type nid. If you see options as mentioned in the image below, you can follow the next step.

7. Type the below command to change the database name in Oracle:

NID TARGET=sys/toor@salesdb dbname=sales2022db SETNAME=YES LOGFILE=changed_db_name.log

Description of parameters:

• NID is a tool name used to change the database name in Oracle
• sys is the sysdba user
• toor is the password for the sys user
• salesdb is the current database name
• sales2022db is the new name
• SETNAME=YES will not change the database id; set it to NO to change it as well.

8. Next step is to create a duplicate file of initsalesDB inside the database folder with the name initsales2022DB. Open the file and change the database name to sales2022DB.

8. Startup the pfile that we created earlier. Use the below command to startup pfile:

STARTUP PFILE = C:\Oracle_21c\database\initsales2022db.ora

The error below appeared because the database name is longer than 8 characters. If your database name is shorter than 8 characters, then the database should have been changed.

10. The database name has successfully changed, you can view the changed database name using the below command.

SELECT NAME, OPEN_MODE, DBID
FROM DATABASE;

Also, check: Oracle Drop Database

Oracle change database name 21c, 19c, 12c

The database name can be changed using the NID tool in Oracle, and so far, this method has not been changed in any version of the Oracle database. We have checked with Oracle 12c, 19c, and 21c.

All of them are following the method discussed in the above section – Oracle change database name

Oracle rename database name is the synonym for Oracle change database name. So you can follow the steps for the Oracle change in the database name.

Also, read: Database Size in Oracle 21c

Oracle change db_unique_name 19c

In oracle db_unique_name can be changed using the following steps:

Step 1. Create a new PFILE using SPfile.

Step 2. Open the newly created PFILE and add the line below at the bottom of the page. Change sales2022 to the name of your database.

*.db_unique_name='sales2022'

Step 3. Replace all the names with the new names.

Step 4. Create a new spfile using the modified spfile in the running instance.

Step 5. Point to the location of Pfile and SPfile inside both nodes.

Read: Oracle Create User

How to change database name in oracle 19c RAC

In this section, we will learn how to change the database name in Oracle 19c RAC. Follow the below steps:

Step 1. Create a new pfile using the Oracle spfile.
Step 2. Shut down the database and mount it using the newly created pfile.
Step 3. Using nid toolset, the target is to provide the new database name

NID TARGET SYS/toor dbname=newdb

Step 4. Type Y for ‘yes‘ to change database name and ID.
Step 5. Mount the database with the newly created pfile.

SET ORACLE_SID=newdb

Step 6. Startup mount pfile='C:\Oracle_21c\database\initnewdb.ora';
Step 7. You view the changed name using the below command:

SELECT NAME, DBHI
FROM V$DATABASE;

Step 8. Open the database and reset the logs.

ALTER DATABASE OPEN RESETLOGS;

Step 9. Create spfile using modified pfile

CREATE spfile='C:\Oracle_21c\database\newdb.ora' from pfile='C:\Users\Oracle\Desktop\newdb

Step 10. Set the new database.

SET ORACLE_SID=NEWDB

Read: Oracle check database status

Oracle change database name control file

In this section, we will learn how to change the database name using the control file in Oracle 21c. We recommend backing up your database before following the steps in this tutorial.

With this method, you can change the database global name in Oracle 21c. We have a database with the name sales, and we will rename it to salesdb in the Oracle Database 21c. Follow the below steps to change the database name:

Step 1: Start the command prompt as an administrator to avoid the error below later when we start and stop Oracle services on the Windows operating system.

Unable to stop service, OS Error = 5
DIM-00014: Cannot open the Windows NT Service Control Manager.
O/S-Error: (OS 5) Access is denied.
Error creating or opening key, OS Error = 1

Step 2: Set the oracle_sid to the database name you want to change. In our case, we want to change the database name – sales.

SET ORACLE_SID=SALES

Step 3: Log in to SQLplus with the sysdba role and check the database and datafile names. The second part is optional, but it is good practice to know what you are going to do.

/ as sysdba

select name from v$database;
select name from v$datafile;

Step 4: Check the archive log list and change the database archive log. To do that, we need to shut down the database and start it in the mount state.

archive log list
shutdown immediate
startup mount
alter database archivelog;
alter database open;

Step 5: Take a backup of the control file. We have named the file as ctrlfile and saved it on the desktop. Please open the file and clean it by removing all the commented lines. Change the reuse with set in the second line and replace the previous database name with a new one.

In our case, the previous database name is sales, and the new database name is sales. Set the noresetlogs to resetlogs. Remove everything after the datafile information.

Alter database backup 
controlfile to trace as 'c:\users\oracle\ctrlfile.sql' 
reuse;

shutdown immediate

quit 

Step 6: Go to pfile in C:\Oracle_21c\admin\sales\pfile a directory. Remove the numbers in the name of init.ora file. Open file and change database name db_name=saledb

Step 7: Delete the PWDsales file from the C:\Oracle_21c\database directory. Please note that since our current database name is sales, the password file has a name as PWDsales.

Once removed, use the below command to create a new PWD file with saledDB. The new password must have at least 8 characters, 1 number, and a special character.

orapwd file=PWDsalesdb.ora password=oracle123

Step 8: Delete the current sid and create a new one for saledb also start the services. type the below script on the command prompt. Make sure you are using cmd as admin.

oradim -delete -sid sales
oradim -new -sid salesdb
net start OracleServicedev

Step 9: Login as a sys user and start the pfile in nomount state.

startup nomount pfile='C:\Oracle_21c\admin\sales\pfile\init.ora'

Step 10: Run the below script to use the previously created control file.

@c:\Users\Oracle\Desktop\ctrlfile.sql

Step 11: Create a new spfile using the pfile

create spfile from pfile='C:\Oracle_21c\admin\webcomp\pfile\init.ora'

Step 12: The Final step is to open the database and reset the logs. This will erase the previous information and open the database.

shutdown immediate
startup mount
alter database open resetlogs;
select name from v$database;

The database name must be changed now. If you encounter any issues, please write to us with the error message.

Read: Connect to oracle database

Oracle change database name to uppercase

The database name is created according to how it is mentioned by the user or developer. Oracle scripts are not case-sensitive, which means you can run the query in upper or lower case; it will work both ways.

In the first section of this tutorial, we explain how to create an Oracle database. Follow the steps in that section and write the database name in upper case when asked.

In Oracle database 21c, the SPfile, file, control file, etc., will be changed to upper case.

Also, take a look at some more Oracle tutorials.

• Oracle get database name
• Oracle Stored Procedure
• Oracle Add Row Number
• Oracle to_char Date Format
• Oracle add primary key
• How to backup table in Oracle

In this tutorial, we have learned how to change the database names in Oracle databases 21c, 19c, and 12c.