How to Change Database Name

How to Change Database Name 

BY RE-CREATING CONTROLFILE

STEP1:- CREATE CONTROLFILE CREATATION SCRIPT.

SQL> alter database backup controlfile to trace as 'D:\q.sql';
Database altered.

STEP2:- EDIT/MODIFY CONTROLFILE CREATION SCRIPT.

CREATE CONTROLFILE SET DATABASE "YE" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 'C:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\ONLINELOG\O1_MF_1_G9Z48JWN_.LOG'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 'C:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\ONLINELOG\O1_MF_2_G9Z48LD1_.LOG'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSTEM.DBF',
  'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSAUX.DBF',
  'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\UNDOTBS1.DBF',
  'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\USERS.DBF',
  'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\MKTBS.DBF',
  'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\R.DBF'
CHARACTER SET AL32UTF8
;

STEP3: CHANGE DB_NAME PARAMETER IN SPFILE/PFILE.

SQL> alter system set db_name=YE scope=spfile;
System altered.

STEP4:- NOW REMOVE OR MOVE OLD CONTROL FILES.

Before removing old controlfile do take backup of all controlfiles.

SQL> show parameter control
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
control_files                        string      C:\ORACLEXE\APP\ORACLE\ORADATA\XE\CONTROL.DBF
control_management_pack_access       string      NONE

STEP5: SHUT DOWN THE DATABASE AND STARTUP IN NOMOUNT MODE TO REFLECT DB_NAME PARAMETER CHANGES.

SQL> startup nomount;

STEP6: NOW EXECUTE NEWLY CREATED CONTROLFILE SCRIPT.

SQL>@D:\control_db.sql
Control file created.

STEP7: OPEN DATABASE WITH RESETLOGS OPTION.

SQL> alter database open resetlogs;
Database altered.

SQL> select database_name from v$database;


USING NID(DBNEWID UTILITY)

Step-1. We will change both db_name to XE and dbid belongs to cloned database.

Check db_id and db_name for new environment before operation.

SQL> select dbid,name from v$database;

DBID NAME
---------- ---------
6753825411 YE

Step-2. Startup instance with mount state.

export ORACLE_SID=CLONEDB
sqlplus / as sysdba
shutdown immediate;
startup mount;

nid target=/ dbname=XE logfile=/D:/nid.log
Database name changed to XE.
Modify parameter file and generate a new password file before restarting.
Database ID for database XE changed to 2898066260.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.

After DBNEWID completed successful, instance has been also closed automatically.

Step-3. Startup instance with nomount option and change the db_name to XE.

Then shutdown and startup mount instance again for activate new db_name. At last, open database with resetlogs option.

Step-4. You should create new password file for the new environment if you need

cd $ORACLE_HOME/dbs
orapwd file=orapwXE password=clone entries=3