Rename a Single Instance Oracle Database

Renaming a single instance db that uses a pfile
———————————————–
———————————————–

-Shutdown db (MOHSINDB)
————————-
export ORACLE_SID=MOHSINDB
sqlplus / as sysdba
SELECT dbid, name FROM v$database;
shutdown immediate

-Backup
——
stop the backup cron job on abc.def.company.net for MOHSINDB (there isnt one. so dont bother)

################################################################
Make sure the db is backed up using NetApps Snaps, ask networks to create a pre-rename snap
################################################################

–rename db using nid (nid updates db file headers. but does not touch the physical location of db files)
———————
#### In a xterm window:
export ORACLE_SID=MOHSINDB
sqlplus / as sysdba
startup mount;
exit

nid TARGET=SYS/password@MOHSINDB DBNAME=NEWNAME
#### nid automatically shutdown the database once completed

–create the directory structure required for the new dbname
————————————————————
################################################################
Ask SysAdmin to:
umount /oradata/MOHSINDB, /oraarch/MOHSINDB
change /oradata/MOHSINDB and /oraarch/MOHSINDB to /oradata/NEWNAME and /oraarch/NEWNAME in /etc/fstab
mount /oradata/NEWNAME, /oraarch/NEWNAME
##########################################################################################################

cd /oradata/NEWNAME
mv MOHSINDB NEWNAME

mkdir /u01/app/oracle/admin/NEWNAME
mkdir /u01/app/oracle/admin/NEWNAME/bdump
mkdir /u01/app/oracle/admin/NEWNAME/cdump
mkdir /u01/app/oracle/admin/NEWNAME/udump
mkdir /u01/app/oracle/admin/NEWNAME/adump

copy and modify pfile
———————
cd $ORACLE_HOME/dbs
cp $ORACLE_HOME/dbs/initMOHSINDB.ora $ORACLE_HOME/dbs/initNEWNAME.ora
vi initNEWNAME.ora
%s/MOHSINDB/NEWNAME/g
%s/SRCH2/NEWNAME/g

–change tns entry
——————-
vi $ORACLE_HOME/network/admin/tnsnames.ora
%s/MOHSINDB/NEWNAME/g

–create the password file
————————–
export ORACLE_SID=NEWNAME
orapwd file=’$ORACLE_HOME/dbs/orapwNEWNAME’ password=<pwd>

— relocate data files
———————–
sqlplus / as sysdba
startup mount;

–rename the files using the script generated by sql
select ‘alter database rename file ‘ || chr(39) || name || chr(39) || ‘ to ‘
|| chr(39) || regexp_replace(name, ‘MOHSINDB’ , ‘NEWNAME’) || chr(39) || ‘;’
as datafile_path from v$datafile;

–Rename log files:
select ‘alter database rename file ‘ || chr(39) || member || chr(39) || ‘ to ‘
|| chr(39) || regexp_replace(member, ‘MOHSINDB’ , ‘NEWNAME’) || chr(39) || ‘;’
as datafile_path from v$logfile;

alter database open resetlogs;
ALTER DATABASE RENAME GLOBAL_NAME TO NEWNAME;

create temporary tablespace TEMP3
tempfile ‘/oradata/NEWNAME/NEWNAME/temp03.dbf’
size 100M AUTOEXTEND ON NEXT 100M MAXSIZE 10G;

alter database default temporary tablespace TEMP3;

drop tablespace TEMP1 including contents and datafile;
drop tablespace TEMP2 including contents and datafile;

exit

–start the db with new pfile and create the spfile
——————————————————
sqlplus / as sysdba
create spfile = ‘$ORACLE_HOME/dbs/spfileNEWNAME.ora’  from pfile = ‘$ORACLE_HOME/dbs/initNEWNAME.ora’;
shutdown immediate
startup
exit;

-Backup
——
#########################################################################################################
Make sure the db is backed up using NetApps Snaps, ask networks to create a post-rename snap
Keep snaps for few days then ask Networks to delete
#########################################################################################################

#########################################################################################################
Unregister mviews and re register them and do a complete refresh wherever required
#########################################################################################################

remove any old spfile, pfile and pwdfile
——————————————-
rm /oradata/MOHSINDB/MOHSINDB/orapwMOHSINDB
rm initMOHSINDB.ora

rm -r /u01/app/oracle/admin/MOHSINDB/bdump
rm -r /u01/app/oracle/admin/MOHSINDB/cdump
rm -r /u01/app/oracle/admin/MOHSINDB/udump
rm -r /u01/app/oracle/admin/MOHSINDB/adump
rm -r /u01/app/oracle/admin/MOHSINDB

–tns entries on other dbs
change tns entry on Other database to NEWNAME

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: