Home / Oracle DBA / How to Change database name in Oracle

How to Change database name in Oracle

How to change database name

HOW TO CHANGE DATABASE NAME ??

Method 1 – by recreating controlfile

SQL> alter database backup controlfile to trace;
This will generate script in udump location
[oracle@server1 udump]$cp prod_ora_7784.trc control.sql
[oracle@server1 ~ ]$vicontrol.sql
Here change the database name and replace word REUSE with SET and make sure it is having RESETLOGS
SQL> show parameter control_files
SQL> alter system set db_name=prod123 scope=spfile;
SQL> shutdown immediate
SQL> !rm /datafiles/prod/*.ctl
SQL> startup nomount
SQL> @control.sql
SQL> alter database open resetlogs;

Method 2 – using nid (DBNEWID utility)

SQL> shutdown immediate
SQL> startup mount
[oracle@server1 ~ ]$nid target=/ dbname=prod123
SQL> alter system set db_name=prod123 scope=spfile;
SQL> shut immediate
SQL> startup mount
SQL> alter database open resetlogs;
The above steps will change database id also
# To change only dbname
[oracle@server1 ~ ]$nid target=/ dbname=prod123 setname=yes

How to change instance name

SQL> alter system set instance_name=prod123 scope=spfile;
SQL> shutdown immediate
SQL> !mv $ORACLE_HOME/dbs/spfileprod.ora $ORACLE_HOME/dbs/spfileprod123.ora
SQL> startup
View More:
ORACLE DATABASE TUNING WITH EXAMPLES
PERFORMANCE TUNING IN ORACLE
ORACLE INCREMENTAL BACKUP WITH EXAMPLE

more details, you can look on oracle.docs

DON’T MISS OUT!
Subscribe To Newsletter
Be the first to get latest updates and exclusive content straight to your email inbox.
Stay Updated
Give it a try, you can unsubscribe anytime.

Check Also

Oracle 10g to 11g Enhancements (Difference between Oracle 10g and 11g)

Difference between Oracle 10g and 11g 1- Enhanced Automatic Memory Management System Oracle 9i automated …

Leave a Reply

Your email address will not be published. Required fields are marked *

DON’T MISS OUT!
Subscribe To Newsletter
Be the first to get latest updates and exclusive content straight to your email inbox.
Stay Updated
Give it a try, you can unsubscribe anytime.
close-link
GET OUR LATEST CONTENT IN YOUR INBOX

SUBSCRIBE 
Your information will never be shared
close-link
Enquiry
Submit
close-link
Sign-up for exclusive content. Be the first to hear about DBA Centre Blogs.
Subscribe