Changing Database Name in ORACLE

Changing Database Name in ORACLE

by

Changing Database Name in ORACLE: During this article we’ll discuss about a way to Changing Database Name in ORACLE Database.

Question: I have already changed the instance_name parameter in the parameter file mean init.ora file, but its still now at database said showing same after we select instance_name from v$instance? what’s the right methods to re-name an Oracle instance?

You may Follow the below steps to change the oracle database name using nid utility. click here

Here, we will change the database name from PROD to ORCL by Recreating controlfile .

To change Database name, we’ve two ways :

  • By recreating controlfile.
  • Using nid (DBNEWID utility).

Let’s see how to change DB name using manual method (By recreating control file) :

Firstly, CREATE CONTROLFILE CREATATION SCRIPT :

[oracle@orakldba ~]$
[oracle@orakldba ~]$ export ORACLE_SID=PROD
[oracle@orakldba ~]$
[oracle@orakldba ~]$ echo $ORACLE_SID
PROD
[oracle@orakldba ~]$
[oracle@orakldba ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Aug 19 02:02:29 2020

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL>
SQL> select name, database_role, open_mode from v$database;

NAME      DATABASE_ROLE    OPEN_MODE
--------- ---------------- --------------------
PROD      PRIMARY          READ WRITE

SQL>
SQL> alter database backup controlfile to trace;

Database altered.

SQL>

Secondly, EDIT/MODIFY CONTROLFILE CREATION SCRIPT :

Here in this control_ORCL.sql file you need to change the database name and replace the word REUSE with SET and make sure it is having RESETLOGS option.

[oracle@orakldba trace]$ ls -ltr

-rw-r----- 1 oracle oracle   1189 Aug 19 02:00 PROD_vkrm_11522.trc
-rw-r----- 1 oracle oracle    919 Aug 19 02:03 PROD_ora_15687.trm
-rw-r----- 1 oracle oracle   7082 Aug 19 02:03 PROD_ora_15687.trc
-rw-r----- 1 oracle oracle  36621 Aug 19 02:03 alert_PROD.log
-rw-r----- 1 oracle oracle  20340 Aug 19 02:06 PROD_gen0_11504.trm
-rw-r----- 1 oracle oracle 122210 Aug 19 02:06 PROD_gen0_11504.trc
-rw-r----- 1 oracle oracle  20460 Aug 19 02:06 PROD_mmon_11552.trm
-rw-r----- 1 oracle oracle 122551 Aug 19 02:06 PROD_mmon_11552.trc
[oracle@orakldba trace]$
[oracle@orakldba trace]$ vi PROD_ora_15687.trc
[oracle@orakldba trace]$
[oracle@orakldba trace]$ cp PROD_ora_15687.trc /home/oracle/control_ORCL.sql
[oracle@orakldba trace]$ 
[oracle@orakldba ~]$
[oracle@orakldba ~]$ vi control_ORCL.sql
[oracle@orakldba ~]$
[oracle@orakldba ~]$
[oracle@orakldba ~]$ cat control_ORCL.sql
CREATE CONTROLFILE SET DATABASE "ORCL" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/ORCL/redo01.log'  SIZE 200M BLOCKSIZE 512,
  GROUP 2 '/u01/app/oracle/oradata/ORCL/redo02.log'  SIZE 200M BLOCKSIZE 512,
  GROUP 3 '/u01/app/oracle/oradata/ORCL/redo03.log'  SIZE 200M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/oradata/ORCL/system01.dbf',
  '/u01/app/oracle/oradata/ORCL/sysaux01.dbf',
  '/u01/app/oracle/oradata/ORCL/undotbs01.dbf',
  '/u01/app/oracle/oradata/ORCL/users01.dbf'
CHARACTER SET AL32UTF8
;

Thirdly, CHANGE DB_NAME PARAMETER IN SPFILE/PFILE :

SQL>
SQL> show parameter control

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
control_files                        string      /u01/app/oracle/oradata/ORCL/c
                                                 ontrol01.ctl, /u01/app/oracle/
                                                 fast_recovery_area/ORCL/contro
                                                 l02.ctl
control_management_pack_access       string      DIAGNOSTIC+TUNING
SQL>
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL> startup nomount;
ORACLE instance started.

Total System Global Area  536870912 bytes
Fixed Size                  8622776 bytes
Variable Size             448793928 bytes
Database Buffers           75497472 bytes
Redo Buffers                3956736 bytes
SQL>
SQL>
SQL>
SQL> alter system set db_name=ORCL scope=spfile;

System altered.

SQL>

After that, REMOVE OR MOVE OLD CONTROL FILES :

[oracle@orakldba ~]$
[oracle@orakldba ~]$ cd /u01/app/oracle/oradata/ORCL/
[oracle@orakldba ORCL]$ 
[oracle@orakldba ORCL]$ ls -ltr
total 2034928
-rw-r----- 1 oracle oracle    10829824 Aug 19 02:29 control01.ctl
drwxr-xr-x 2 oracle oinstall         6 Aug 18 02:03 redo
-rw-r----- 1 oracle oracle   209715712 Aug 19 01:05 redo01.log
-rw-r----- 1 oracle oracle   209715712 Aug 19 01:07 redo02.log
-rw-r----- 1 oracle oracle   209715712 Aug 19 02:17 redo03.log
-rw-r----- 1 oracle oracle   503324672 Aug 19 02:17 sysaux01.dbf
-rw-r----- 1 oracle oracle   849354752 Aug 19 02:17 system01.dbf
-rw-r----- 1 oracle oracle    33562624 Aug 19 00:05 temp01.dbf
-rw-r----- 1 oracle oracle    73408512 Aug 19 02:17 undotbs01.dbf
-rw-r----- 1 oracle oracle     5251072 Aug 19 02:17 users01.dbf
[oracle@orakldba ORCL]$
[oracle@orakldba ORCL]$ mv control01.ctl control01.ctl_bkp
[oracle@orakldba ORCL]$ 
[oracle@orakldba ORCL]$ cd /u01/app/oracle/fast_recovery_area/ORCL/
[oracle@orakldba ORCL]$ 
[oracle@orakldba ORCL]$ ls -ltr
total 10352
-rw-r----- 1 oracle oracle 10600448 Aug 19 02:18 control02.ctl
drwxr-x--- 5 oracle oracle       59 Aug 18 01:22 ORCL
drwxr-x--- 5 oracle oracle       59 Aug 19 00:57 PROD
[oracle@orakldba ORCL]$
[oracle@orakldba ORCL]$ mv control02.ctl control02.ctl_bkp

Then, SHUT DOWN THE DATABASE AND STARTUP IN NOMOUNT MODE TO REFLECT DB_NAME PARAMETER CHANGES :

SQL> shut immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL>
SQL>
SQL> startup nomount;
ORACLE instance started.

Total System Global Area  536870912 bytes
Fixed Size                  8622776 bytes
Variable Size             448793928 bytes
Database Buffers           75497472 bytes
Redo Buffers                3956736 bytes
SQL>

Lastly, EXECUTE NEWLY CREATED CONTROLFILE SCRIPT :

SQL>
SQL> @/home/oracle/control_ORCL.sql

Control file created.

SQL>
STEP7: OPEN DATABASE WITH RESETLOGS OPTION :
SQL>
SQL> alter database open resetlogs;

Database altered.

SQL>
SQL>
SQL> select name, database_role, open_mode from v$database;

NAME      DATABASE_ROLE    OPEN_MODE
--------- ---------------- --------------------
ORCL      PRIMARY          READ WRITE

SQL>
SQL> alter system register;

System altered.

SQL>

I hope my written content will helped you. Your suggestions/feedback are most welcome from orakldba.com.
Keep learning… Have a great day!!!
Please comment here for your any query which is related to above content. You can write to mail me through : orakldba@gmail.com.

1 thought on “Changing Database Name in ORACLE”

  1. Pingback: How to create a password file using ORAPWD Utility in oracle -

Leave a Comment