Monday , September 25 2017
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

Comments

comments

Check Also

How to switch on primary database to physical standby database

After configuration data guard then data is switching  into primary database  to standby database : …

Leave a Reply

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