How to change database name in Oracle
We can change the database name using the “DBNEWID“. Before changing the db name we will know the basics about the DBNEWID tool. We can change the database name and DBID using the DBNEWID utility.
Changing the DBID of a database is a serious procedure. When the DBID of a database is changed, all previous backups and archived logs of the database become unusable. This is similar to creating a database except that the data is already in the data files. You must open the database with the RESETLOGS option, which re-creates the online redo logs and resets their sequence to 1. Consequently, you should make a backup of the whole database immediately after changing the DBID.
Changing the DBNAME without changing the DBID does not require you to open with the RESETLOGS option,You need to change the initialization parameter DB_NAME in the pfile and create a new password file.
NOTE : If we restore the old control file with old database name ,we need to use the old password file and old parameter file.
Changing the DB name :-
1) Ensure you have full database backup before changing the database name.
2) Mount the database
[oracle@node2 u01]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Thu Mar 10 20:45:19 2016
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
SQL> select name , open_mode from v$database;
NAME OPEN_MODE
——— ——————–
MYDB READ WRITE
SQL>
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup mount
ORACLE instance started.
Total System Global Area 521936896 bytes
Fixed Size 2214936 bytes
Variable Size 360711144 bytes
Database Buffers 155189248 bytes
Redo Buffers 3821568 bytes
Database mounted.
3) Invoke the utility on the command line, specifying a valid user with the SYSDBA privilege (you will be prompted for a password). You must specify both the DBNAME and SETNAME parameters.
[oracle@node2 u01]$ nid TARGET=SYS DBNAME=PROD SETNAME=YES
DBNEWID: Release 11.2.0.1.0 – Production on Thu Mar 10 21:07:31 2016
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to database MYDB (DBID=2820641880)
Connected to server version 11.2.0
Control Files in database:
/u01/app/oracle/oradata/mydb/control01.ctl
/u01/app/oracle/flash_recovery_area/mydb/control02.ctl
Change database name of database MYDB to PROD? (Y/[N]) => Y
Proceeding with operation
Changing database name from MYDB to PROD
Control File /u01/app/oracle/oradata/mydb/control01.ctl – modified
Control File /u01/app/oracle/flash_recovery_area/mydb/control02.ctl – modified
Datafile /u01/app/oracle/oradata/mydb/system01.db – wrote new name
Datafile /u01/app/oracle/oradata/mydb/sysaux01.db – wrote new name
Datafile /u01/app/oracle/oradata/mydb/undotbs01.db – wrote new name
Datafile /u01/app/oracle/oradata/mydb/users01.db – wrote new name
Datafile /u01/app/oracle/oradata/mydb/MYDB/datafile/o1_mf_prod_cg0gnsjn_.db – wrote new name
Datafile /u01/app/oracle/oradata/mydb/temp01.db – wrote new name
Control File /u01/app/oracle/oradata/mydb/control01.ctl – wrote new name
Control File /u01/app/oracle/flash_recovery_area/mydb/control02.ctl – wrote new name
Instance shut down
Database name changed to PROD.
Modify parameter file and generate a new password file before restarting.
Succesfully changed database name.
DBNEWID – Completed succesfully.
4) Change the “DB_NAME” parameter in the spfile.
startup nomount
SQL> alter system set db_name=’PROD’ scope=spfile;
System altered.
5) Open the database
[oracle@node2 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Thu Mar 10 21:20:07 2016
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 521936896 bytes
Fixed Size 2214936 bytes
Variable Size 360711144 bytes
Database Buffers 155189248 bytes
Redo Buffers 3821568 bytes
Database mounted.
Database opened.
SQL>
SQL> select name , open_mode from v$database;
NAME OPEN_MODE
——— ——————–
PROD READ WRITE
Viswamitra's blog 