Current Environment: Operating System: Oracle Linux 6.3RDBMS Version : 11.2.0.4 |
Step 1: Perform below prerequisites before proceeding. 1) Your database must be mounted to do this.(It will not allow you to change the database name in open mode. 2)To change or rename the database name, you need to change below locations of database:
Step 2: Take backup of pfile, spfile,tnsnames.ora, listener.ora and sqlnet.ora file Step 3: Set the environment variables for current database and run the below command. [oracle@dc admin]$ env | grep ORA ORACLE_SID=DC ORACLE_BASE=/home/oracle/u01 ORACLE_HOME=/home/oracle/u01/product/11.2.0/dbhome_1 You will get below error message if you run below command in open mode. SQL> select name,instance_name,open_mode from v$database,v$instance; NAME INSTANCE_NAME OPEN_MODE --------- ---------------- -------------------- DC DC READ WRITE [oracle@dc admin]$ nid target=sys/oracle@DC dbname=GTS setname=YES DBNEWID: Release 11.2.0.4.0 - Production on Thu Aug 10 12:57:35 2017 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to database DC (DBID=1238895280) NID-00121: Database should not be open Change of database name failed during validation - database is intact. DBNEWID - Completed with validation errors. SQL> alter database close; Database altered. [oracle@dc admin]$ nid target=sys/oracle@DC dbname=GTS setname=YES DBNEWID: Release 11.2.0.4.0 - Production on Thu Aug 10 12:58:49 2017 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to database DC (DBID=1238895280) Connected to server version 11.2.0 Control Files in database: /oradisk/DC/control01.ctl /home/oracle/u01/fast_recovery_area/DC/control02.ctl Change database name of database DC to GTS? (Y/[N]) => Y Proceeding with operation Changing database name from DC to GTS Control File /oradisk/DC/control01.ctl - modified Control File /home/oracle/u01/fast_recovery_area/DC/control02.ctl - modified Datafile /oradisk/DC/system01.db - wrote new name Datafile /oradisk/DC/sysaux01.db - wrote new name Datafile /oradisk/DC/undotbs01.db - wrote new name Datafile /oradisk/DC/users01.db - wrote new name Datafile /oradisk/DC/temp01.db - wrote new name Control File /oradisk/DC/control01.ctl - wrote new name Control File /home/oracle/u01/fast_recovery_area/DC/control02.ctl - wrote new name Instance shut down Database name changed to GTS. Modify parameter file and generate a new password file before restarting. Succesfully changed database name. DBNEWID - Completed succesfully. In the below command, nid target=sys/oracle@DC dbname=GTS setname=YES DC => is the tns service entry in tnsnames.ora file. GTS => It is the new name for the current database. Step 4: Now start the database but you will get below error message, because you have not changed db_name and db_unique_name entries in the pfile. [oracle@dc admin]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Thu Aug 10 12:59:06 2017 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to an idle instance. SQL> startup; ORACLE instance started. Total System Global Area 417546240 bytes Fixed Size 2253824 bytes Variable Size 180358144 bytes Database Buffers 230686720 bytes Redo Buffers 4247552 bytes ORA-01103: database name 'GTS' in control file is not 'DC' SQL> show parameter instance_name NAME TYPE VALUE --------------- ----------- ------------ instance_name string DC SQL> show parameter db_name NAME TYPE VALUE ----------------- ------ -------- db_name string DC SQL> show parameter db_unique NAME TYPE VALUE ----------------- ------ -------- db_unique_name string DC SQL> shut immediate; ORA-01507: database not mounted ORACLE instance shut down. SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@dc admin]$ pwd /home/oracle/u01/product/11.2.0/dbhome_1/dbs [oracle@dc dbs]$ ls -ltr -rw-r-----. 1 oracle oinstall 1536 Jun 21 17:17 orapwDC -rw-r-----. 1 oracle oinstall 2560 Aug 9 16:37 bkp_spfileDC.ora -rw-r--r--. 1 oracle oinstall 1319 Aug 9 16:38 initDC.ora -rw-r-----. 1 oracle oinstall 3584 Aug 10 11:15 spfileDC.ora [oracle@dc dbs]$ cp initDC.ora bkp_initDC.ora Step 5: Modify below two parameters in pfile and start the database in nomount and create spfile from pfile: db_name='GTS' db_unique_name='GTS' [oracle@dc dbs]$ vi initDC.ora [oracle@dc dbs]$ mv spfileDC.ora bkp_spfileDC.ora [oracle@dc dbs]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Thu Aug 10 13:02:28 2017 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to an idle instance. SQL> startup nomount; ORACLE instance started. Total System Global Area 221331456 bytes Fixed Size 2251856 bytes Variable Size 163578800 bytes Database Buffers 50331648 bytes Redo Buffers 5169152 bytes SQL> show parameter instance_name NAME TYPE VALUE -------------- ----------- --------- instance_name string DC SQL> show parameter db_name NAME TYPE VALUE --------- ----------- --------- db_name string GTS SQL> show parameter db_uni NAME TYPE VALUE --------------- ----------- --------- db_unique_name string GTS Step 6: Now mount the database and open it. SQL> alter database mount; Database altered. SQL> alter database open; Database altered. Step 7: Verify below things wherther everthing is working fine.
[oracle@dc admin]$ sqlplus sys/oracle@GTS as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Thu Aug 10 13:07:22 2017 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> @/home/oracle/rd.sql NAME INSTANCE_NAME OPEN_MODE --------- ---------------- -------------------- GTS DC READ WRITE |
📚 Refer More Popular Posts:
Thanks for reading this post ! Please comment if you like this post ! Click on FOLLOW to get future blog updates !
