Change the DB name without nid .

Problem: To change DB name from db11g to db11gnew without using nid utility ,

SQl>   show parameter db_name ;

NAME     TYPE VALUE
------------------------------------ ----------- -----
db_name     string db11g


SQL> ARCHIVE LOG LIST ;
Database log mode       Archive Mode
Automatic archival       Enabled
Archive destination       USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     2
Next log sequence to archive   4
Current log sequence       4

SQL> alter system switch logfile;

System altered.

SQL> alter database backup controlfile to trace as '/home/oracle/control_trace.sql' ; 

SQL> SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "Init File Type" FROM sys.v_$parameter WHERE name = 'spfile';

Init File Type
----------------

SPFILE

SQL>create pfile=' /home/oracle/initdb11gnew.ora' from spfile ; 


Database altered.
[oracle@oracle11g dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Jan 29 18:04:37 2015

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> shu immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.


Edit to control file trace /home/oracle/control_trace.sql . Remove everything above Set#2 : Resetlog case and edit the create statement as : 

CREATE CREATE CONTROLFILE SET  DATABASE "db11gnew"

[oracle@oracle11g ~]$ cat control_trace.sql

--     Set #2. RESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- The contents of online logs will be lost and all backups will
-- be invalidated. Use this only if online logs are damaged.

-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
--  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE

STARTUP NOMOUNT
CREATE CONTROLFILE SET  DATABASE "db11gnew" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 (
    '+DATA2/db11g/onlinelog/group_1.261.868740309',
    '+RMAN_DISK/db11g/onlinelog/group_1.257.868740313'
  ) SIZE 50M BLOCKSIZE 512,
  GROUP 2 (
    '+DATA2/db11g/onlinelog/group_2.262.868740319',
    '+RMAN_DISK/db11g/onlinelog/group_2.258.868740323'
  ) SIZE 50M BLOCKSIZE 512,
  GROUP 3 (
    '+DATA2/db11g/onlinelog/group_3.263.868740329',
    '+RMAN_DISK/db11g/onlinelog/group_3.259.868740333'
  ) SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '+DATA2/db11g/datafile/system.256.868740081',
  '+DATA2/db11g/datafile/sysaux.257.868740081',
  '+DATA2/db11g/datafile/undotbs1.258.868740081',
  '+DATA2/db11g/datafile/users.259.868740081'
CHARACTER SET WE8MSWIN1252
;
ALTER DATABASE OPEN RESETLOGS;
ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA2'  SIZE 20971520  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;


Rename  the current init file and spfile as Bkp files . 

Copy the init file create above in $ORACLE_HOME/dbs and change the db_name to db11gnew .

[oracle@oracle11g dbs]$ cp /home/oracle/initdb11gnew.ora $ORACLE_HOME/dbs
[oracle@oracle11g dbs]$ ls


[oracle@oracle11g dbs]$ cat initdb11gnew.ora


*.audit_file_dest='/u01/app/oracle/admin/db11g/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
#*.control_files='+DATA2/db11g/controlfile/','+RMAN_DISK/db11g/controlfile/'
*.db_block_size=8192
*.db_create_file_dest='+DATA2'
*.db_domain=''
*.db_name='db11gnew'
*.db_recovery_file_dest='+RMAN_DISK'
*.db_recovery_file_dest_size=4322230272
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=db11gXDB)'
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=807403520
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'

[oracle@oracle11g ~]$ export ORACLE_SID=db11gnew
[oracle@oracle11g ~]$ ls
control_trace.sql  db_11g_single_with_asm.rsp  db_info.log  Desktop  initdb11gnew.ora
[oracle@oracle11g ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Jan 29 18:06:39 2015

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

Connected to an idle instance.

SQL> @control_trace.sql ; 
ORACLE instance started.

Total System Global Area  805875712 bytes
Fixed Size    2232680 bytes
Variable Size  478154392 bytes
Database Buffers  322961408 bytes
Redo Buffers    2527232 bytes

Control file created.


Database altered.

Tablespace altered.


SQL> select NAME,LOG_MODE,CONTROLFILE_TYPE,OPEN_MODE from V$DATABASE ;

NAME  LOG_MODE     CONTROL OPEN_MODE
--------- ------------ ------- -------------------------------------
DB11GNEW  ARCHIVELOG   CURRENT READ WRITE

SQL> Show parameter control_file ;

control_files      string  +DATA2/db11gnew/controlfile/current.266.870286263,                                                                        +RMAN_DISK/db11gnew/controlfile/current .262.870286265

Shu immediate and Edit the init file with the new control_file values . Again start the DB using pfile and create spfile .



[oracle@oracle11g ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 29-JAN-2015 18:18:07

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER2
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                29-JAN-2015 17:27:48
Uptime                    0 days 0 hr. 50 min. 19 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/11.2.0/grid_11203/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/oracle11g/listener2/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle11g.localdomain)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "db11gXDB" has 1 instance(s).
  Instance "db11gnew", status READY, has 1 handler(s) for this service...
Service "db11gnew" has 1 instance(s).
  Instance "db11gnew", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@oracle11g ~]$

Check for the new path of control files created in ASM using asmcmd you can check that .
Shutdown the DB and change the new path of control_file in initdb11gnew.ora .

Start the db using initdb11gnew.ora and recreate the spfile , shutdown again and startup DB using spfile .

SQL> create spfile='+DATA2' from pfile ;

File created.

SQL> shu immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.

After this modify the initdb11gnew.ora to point to new spfile created .

ASMCMD> ls -l +DATA2/DB11GNEW/PARAMETERFILE/

Type           Redund  Striped  Time             Sys  Name
PARAMETERFILE  UNPROT  COARSE   JAN 29 18:00:00  Y    spfile.268.870289035

ASMCMD> +DATA2/DB11GNEW/PARAMETERFILE/spfile.268.870289035
ASMCMD>

Edit the init file to point to the spfile :

[oracle@oracle11g dbs]$ cat initdb11gnew.ora

SPFILE='+DATA2/DB11GNEW/PARAMETERFILE/spfile.268.870289035'

Now start the Db using spfile :

SQL> startup ;
ORACLE instance started.

Total System Global Area  805875712 bytes
Fixed Size    2232680 bytes
Variable Size  478154392 bytes
Database Buffers  322961408 bytes
Redo Buffers    2527232 bytes
Database mounted.
Database opened.

SQL> show parameter pfile ;

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile     string +DATA2/db11gnew/parameterfile/
spfile.268.870289035

 SQL> show parameter control_file;

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time     integer 7
control_files     string +DATA2/db11gnew/controlfile/current.266.870286263,                                                                        +RMAN_DISK/db11gnew/controlfile/current .262.870286265

change the settings in oratab , tns file etc .