Oracle Change Database Name

In this Oracle tutorial, we will learn how to change the database names in oracle database 21c, 19c, and 12c. Also, we will cover these topics.

• Oracle change database name
• Oracle change database name 21c, 19c, 12c
• Oracle change db_unique_name 19c
• How to check database name in oracle 19c RAC
• Oracle change database name control file
• Oracle change database name to uppercase

Oracle change database name

Oracle Database name can be changed using the NID tool that is installed by default while 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 change the database name from salesDB to sales2022DB. The new name is exceeding 8 characters and we got the error at step 8.

We have repeated the steps and this time renamed the database from salesDB to sales22. Follow these steps to change the database name in oracle:

1. Make sure that you have created the backup of database to tackle any uncertainty.
2. Set the oracle sid to the database name of choice on the command prompt.
3. Login as a sys or any other user with privileges to perform actions on the database.
4. Create a pfile from spfile that can be used later for starting 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 below image shows an implementation of the above scripts on the command prompt. Yellow texts are the script and green texts are the output.

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

4. Database name can be changed only when the database is in mount state. Shutdown the database and then start in 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 below image that means 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 database name in oracle
• sys is the the sysdba user
• toor is the password for 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 the database ID 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

Since the database name has more than 8 characters that is why the below error appeared. If your database name is smaller than 8 characters then the database should have been changed.

10. The database name has successfully changed you 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 is not 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 shown 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 newly created PFILE and add the below line at the bottom of the page. change sales2022 with 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 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 oracle spfile.
Step 2. Shut down the database and mount it using the newly created pfile.
Step 3. Using nid toolset the target 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 backup 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 oracle database 21c. Follow the below steps to change the database name:

Step 1: Start command prompt as an administrator to avoid the below error later when we will 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 sysdba role and check the database name and datafile name. The second part is optional, but it is good practice to see 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 archivelog. 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. 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 previous database name is sales and the new database name is salesdb. Set the noresetlogs to resetlogs. Remove everything after 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 that is why the password file has a name as PWDsales.

Once removed now use the below command to create a new PWDfile with saledDB. The new password must have at least 8 characters and 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: 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, in case you faced any issue please write us with the error message.

Read: Connect to oracle database

Oracle change database name to uppercase

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

In the first section of this tutorial, we have explained how to create a database in oracle. Follow the steps in that section and write the database name in upper case when asked.

The SPfile, pfile, control file, etc, of them, will be changed to the upper case in oracle database 21c.

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. Also, we have covered these topics.

• Oracle change database name
• Oracle change database name 21c, 19c, 12c
• Oracle change db_unique_name 19c
• How to check database name in oracle 19c RAC
• Oracle change database name control file
• Oracle change database name to uppercase