Mir Sayeed Hassan – Oracle Blog

Change the existing DB Name to New DB NAME (SID)

Before performing the below operation, safer side to take a full backup of the database using rman or dumpfile (expdp)

Check the existing database name

SQL> SELECT DBID,NAME,open_mode from v$database;

DBID NAME      OPEN_MODE
--------- --------- --------------------
4187434325 PRIM      READ WRITE

SQL> shu immediate

Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.
Total System Global Area 2455228416 bytes
Fixed Size                  2255712 bytes
Variable Size             754975904 bytes
Database Buffers         1677721600 bytes
Redo Buffers               20275200 bytes
Database mounted.

Now let’s run nid to change database name.

[oracle@oracledbtest ~]$ nid target=sys/xxx@prim dbname=testdb setname=YES

DBNEWID: Release 11.2.0.4.0 - Production on Sat Apr 22 16:46:21 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to database PRIM (DBID=4187434325)
Connected to server version 11.2.0
Control Files in database:
/u01/app/oracle/fast_recovery_area/prim/control02.ctl
/u01/app/oracle/fast_recovery_area/prim/control03.ctl
/u01/app/oracle/fast_recovery_area/control04.ctl
Change database name of database PRIM to TESTDB? (Y/[N]) => Y
Proceeding with operation
Changing database name from PRIM to TESTDB
Control File /u01/app/oracle/fast_recovery_area/prim/control02.ctl - modified
Control File /u01/app/oracle/fast_recovery_area/prim/control03.ctl - modified
Control File /u01/app/oracle/fast_recovery_area/control04.ctl - modified
Datafile /u01/app/oracle/oradata/prim/system01.db - wrote new name
Datafile /u01/app/oracle/oradata/prim/sysaux01.db - wrote new name
Datafile /u01/app/oracle/oradata/prim/undotbs01.db - wrote new name
Datafile /u01/app/oracle/oradata/prim/users01.db - wrote new name
Datafile /u01/app/oracle/oradata/prim/example01.db - wrote new name
Datafile /u01/app/oracle/oradata/prim/PAP_DATA.db - wrote new name
Datafile /u01/app/oracle/oradata/prim/ussd_rtl_data.db - wrote new name
Datafile /u01/app/oracle/oradata/prim/nrename.db - wrote new name
Datafile /u01/app/oracle/oradata/prim/system02.db - wrote new name
Datafile /u01/app/oracle/oradata/prim/temp01.db - wrote new name
Control File /u01/app/oracle/fast_recovery_area/prim/control02.ctl - wrote new name
Control File /u01/app/oracle/fast_recovery_area/prim/control03.ctl - wrote new name
Control File /u01/app/oracle/fast_recovery_area/control04.ctl - wrote new name
Instance shut down
Database name changed to TESTDB.
Modify parameter file and generate a new password file before restarting.
Succesfully changed database name.
DBNEWID - Completed succesfully.

Now operation completed successfully.

[oracle@oracledbtest ~]$ !sq

sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sat Apr 22 16:48:24 2017
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to an idle instance

SQL> create spfile from pfile='$ORACLE_HOME/dbs/initprim.ora';
File created.

ORA-00214: control file ‘/u01/app/oracle/fast_recovery_area/prim/control02.ctl’

version 434103 inconsistent with file

‘/u01/app/oracle/oradata/prim/control01.ctl’ version 409394

With the above error its understood that the control file of 2 is higher then the controlfile 1 as we can compare the size

Later we can 1^st make a copy of all the controlfile for the safer side, then try to copy the control file 2  controlfile 1 location as overwrite then open the database with mount then open

Hence solve the issues

[oracle@oracledbtest prim]$ cp control02.ctl /u01/app/oracle/oradata/prim/control01.ctl

[oracle@oracledbtest prim]$ !sq

sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sat Apr 22 17:25:02 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> shu immediate
ORA-01507: database not mounted
ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.
Total System Global Area 2455228416 bytes
Fixed Size                  2255712 bytes
Variable Size             754975904 bytes
Database Buffers         1677721600 bytes
Redo Buffers               20275200 bytes
Database mounted.

SQL> alter database open;
Database altered.

[oracle@oracledbtest dbs]$ sqlplus sys/systpsp@testdb as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sat Apr 22 17:44:46 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> SELECT DBID,NAME,open_mode from v$database;

DBID NAME      OPEN_MODE
---------- --------- --------------------
4187434325 TESTDB    READ WRITE

Note:

• Change the . bash_profile sid * uniqueid

• Change the listener & tnsnames with new sid

• To set up the pfile configuration

Once the db is configure,  try to create the pfile from the spfile & edit the pfile and change the pfile of sid & unique name

Then try to take the backup of the shutdown the db & take the backup of the spfile then delete it

After that create the spfile from pfile and then start the database with the default as spfile

Hence the database is started with the spfile with the new sid name

SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/11.2.0
/dbhome_1/dbs/spfiletestdb.ora

For the reference find the below listerner & tnsnames.ora

[oracle@oracledbtest admin]$ cat listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracledbtest.local)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
[oracle@oracledbtest admin]$ cat tnsnames.ora
TESTDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.20.0.123)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = testdb)
(UR = A)
)
)

Hence change the database NAME (SID) to New database name (SID)