Change Database Name Using NID Utility

• Check database status.

select name,open_mode,database_role from v$database;

• Disable the monitoring for the database if any.

• Backup the database.

• Gather datafiles, control files and redo file count and location.

select file_name from dba_data_files;

show parameter control

set lines 1000
SELECT a.GROUP#, a.THREAD#, a.SEQUENCE#,
a.ARCHIVED, a.STATUS, b.MEMBER AS REDOLOG_FILE_NAME,
a.BYTES/1024/1024) AS SIZE_MB FROM v$log a
JOIN v$logfile b ON a.Group#=b.Group#
ORDER BY a.GROUP#;

•  Backup parameter file.
  

create pfile='/tmp/initorcl.ora' from spfile

• Bring the database to mount state.
  

shut immediate
startup mount

• Run the NID utility to change the DB name.

nid sys/password@CURRENT_DBNAME DBNAME=NEW_DBNAME

• Change db_name parameter in parameter file.

shut immediate
startup nomount
alter system set db_name=<NEW_DBNAME> scope=spfile;
shut immediate
startup nomount

• Validate the DB name and bring it down.

show parameter db_name
shut immediate

• Rename the spfile and password file to to new DB.

cd $ORACLE_HOME/dbs
cp spfileold_dbname.ora spfile_new_dbname.ora
cp orapwold_dbname orapwnew_dbname

• Set the environment and bring up the database.

export ORACLE_SID=<NEW DB_NAME>
echo $ORACLE_SID
env | grep oracle
sqlplus / as sysdba
startup nomount;
create pfile from spfile;
shut immediate;

• If any datafiles, redo files, control files has DB name rename them to the new one. Update the pfile accordingly and start the DB.

startup nomount pfile='$ORACLE_HOME/dbs/init<NEW DBNAME>.ora';
show parameter service;
alter database mount;
alter database open resetlogs;
create spfile from pfile 
shut immediate;
startup;
show parameter listener
alter system register;
select name,open_mode,database_role from v$database; 

• Recreate temp tablespace. 

SELECT v.file#, t.file_name, v.status
FROM dba_temp_files t, v$tempfile v
WHERE t.file_id = v.file#;
CREATE TEMPORARY TABLESPACE "TEMP1" TEMPFILE '<path>/<DB NAME>_temp02.dbf' SIZE 230686720;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP1;
DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;
CREATE TEMPORARY TABLESPACE "TEMP" TEMPFILE '<path>/<DB NAME>_temp01.dbf' SIZE 230686720;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;
DROP TABLESPACE temp1 INCLUDING CONTENTS AND DATAFILES;

• Recreate redo logs

## Check the status of redo group 
set lines 1000
SELECT a.GROUP#, a.THREAD#, a.SEQUENCE#,
 a.ARCHIVED, a.STATUS, b.MEMBER AS REDOLOG_FILE_NAME,
 (a.BYTES/1024/1024) AS SIZE_MB FROM v$log a
JOIN v$logfile b ON a.Group#=b.Group#
ORDER BY a.GROUP#;
Alter database drop logfile group 3;
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 3  ('<path>/<DB NAME>_log3a.rdo', '<path>/<DB NAME>_log3b.rdo') SIZE 500M;
Alter database drop logfile group 4;
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 4  ('<path>/<DB NAME>_log4a.rdo', '<path>/<DB NAME>_log4b.rdo') SIZE 500M;
Alter database drop logfile group 4;
alter system switch logfile;