Renaming oracle instance (from UOCTEST to UAS)
Note : Remember to rename datafile, logfile, controlfile, parameterfile and passwordfile. Aslo need to change listener.
******** Parameter File ***************************
(1) check whether instance is using SPFIlE. using “show parameter spfile”
(2) SQL> connect / as sysdba
SQL> “create pfile from spfile”
in 10g by default it will create pfile on $ORACLE_BASE/admin/
i executed following following
create pfile=’$ORACLE_HOME/dbs/init_UOCTEST.ora’ from spfile;
(3) “cp init_UOCTEST.ora initUAS.ora”
(4) modify initUAS.ora and change UOCTEST to UAS. So now Pfile is ready.
********** Password File *********************
(5) Now for create a new password file as in previous instance passwordfile authentication was enabled (orapwUOCTEST exist).
orapwd file=orapwUAS password=flowers123 entries=10
**********Control file*************************
(6) alter database backup controlfile to trace;
Above statement will generate tracefile that contain ASCII definitions for controlfile in $UDUMP folder.
(7) get copy of “create controlfile” statement. I got following copy after deleting unnecessary comments.
***ORIGINAL***
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE “UOCTEST” RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 454
LOGFILE
GROUP 1 ‘/uocdb1/oradata/UOCTEST/redo01.log’ SIZE 10M,
GROUP 2 ‘/uocdb1/oradata/UOCTEST/redo02.log’ SIZE 10M,
GROUP 3 ‘/uocdb1/oradata/UOCTEST/redo03.log’ SIZE 10M
— STANDBY LOGFILE
DATAFILE
‘/uocdb1/oradata/UOCTEST/system01.dbf’,
‘/uocdb1/oradata/UOCTEST/undotbs01.dbf’,
‘/uocdb1/oradata/UOCTEST/sysaux01.dbf’,
‘/uocdb1/oradata/UOCTEST/users01.dbf’,
‘/uocdb1/oradata/UOCTEST/uas_data_01.dbf’,
‘/uocdb1/oradata/UOCTEST/uas_data_02.dbf’,
‘/uocdb1/oradata/UOCTEST/uas_data_03.dbf’,
‘/uocdb1/oradata/UOCTEST/uas_data_04.dbf’,
‘/uocdb2/oradata/UOCTEST/uas_index_01.dbf’,
‘/uocdb2/oradata/UOCTEST/uas_index_02.dbf’,
‘/uocdb2/oradata/UOCTEST/uas_index_03.dbf’,
‘/uocdb2/oradata/UOCTEST/uas_index_04.dbf’,
‘/uocdb1/oradata/UOCTEST/system02.dbf’,
‘/uocdb1/oradata/UOCTEST/neulion.dbf’,
‘/uocdb1/oradata/UOCTEST/neulion02.dbf’
CHARACTER SET UTF8
;
RECOVER DATABASE USING BACKUP CONTROLFILE
ALTER DATABASE OPEN RESETLOGS;
ALTER TABLESPACE TEMP ADD TEMPFILE ‘/uocdb1/oradata/UOCTEST/temp01.dbf’
SIZE 60817408 REUSE AUTOEXTEND ON NEXT 1048576 MAXSIZE 32767M;
***UPDATED***
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE SET DATABASE “UAS” RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 454
LOGFILE
GROUP 1 ‘/uocdb1/oradata/UAS/redo01.log’ SIZE 10M,
GROUP 2 ‘/uocdb1/oradata/UAS/redo02.log’ SIZE 10M,
GROUP 3 ‘/uocdb1/oradata/UAS/redo03.log’ SIZE 10M
— STANDBY LOGFILE
DATAFILE
‘/uocdb1/oradata/UAS/system01.dbf’,
‘/uocdb1/oradata/UAS/undotbs01.dbf’,
‘/uocdb1/oradata/UAS/sysaux01.dbf’,
‘/uocdb1/oradata/UAS/users01.dbf’,
‘/uocdb1/oradata/UAS/uas_data_01.dbf’,
‘/uocdb1/oradata/UAS/uas_data_02.dbf’,
‘/uocdb1/oradata/UAS/uas_data_03.dbf’,
‘/uocdb1/oradata/UAS/uas_data_04.dbf’,
‘/uocdb2/oradata/UAS/uas_index_01.dbf’,
‘/uocdb2/oradata/UAS/uas_index_02.dbf’,
‘/uocdb2/oradata/UAS/uas_index_03.dbf’,
‘/uocdb2/oradata/UAS/uas_index_04.dbf’,
‘/uocdb1/oradata/UAS/system02.dbf’,
‘/uocdb1/oradata/UAS/neulion.dbf’,
‘/uocdb1/oradata/UAS/neulion02.dbf’
CHARACTER SET UTF8;
ALTER DATABASE OPEN RESETLOGS;
ALTER TABLESPACE TEMP ADD TEMPFILE ‘/uocdb1/oradata/UAS/temp01.dbf’ SIZE 1024M MAXSIZE 2048M;
(8) ****** Created Updated OS environment file *****
modify new envionment variable configuraiton file and .profile for new instance changes.
(9) ****** Shurtting Down running instance *******
shutdown normal or immediate running UOCTEST instance.
*** renaming OS folders ***
(10)
By looking as ascii control file i come to know the distribution of physical files are on /uocdb1 and /uocdb2 mountpoints. So renamed /uocdb1/oradata/UOCTEST to /uocdb1/oradata/UAS and /uocdb2/oradata/UOCTEST to /uocdb2/oradata/UAS on file system.
Also re-checked control_files, background_dump_dest, user_dump_dest & core_dump_dest parameter from initUAS.ora and made appropriate folder name changes at file system level.
(11)
Logout from server eaappprod21 and login in new env. variable settings.
(12) ***** Operational Statement *****
cd paresh
eaappprod21->UAS@(/oracle/paresh)sqlplus / as sysdba
SQL> startup pfile=$ORACLE_HOME/dbs/initUAS.ora nomount
ORACLE instance started.
Total System Global Area 1509949440 bytes
Fixed Size 1302480 bytes
Variable Size 282861616 bytes
Database Buffers 1224736768 bytes
Redo Buffers 1048576 bytes
SQL>
/* Executed following statement */
CREATE CONTROLFILE REUSE SET DATABASE “UAS” RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 454
LOGFILE
GROUP 1 ‘/uocdb1/oradata/UAS/redo01.log’ SIZE 10M,
GROUP 2 ‘/uocdb1/oradata/UAS/redo02.log’ SIZE 10M,
GROUP 3 ‘/uocdb1/oradata/UAS/redo03.log’ SIZE 10M
— STANDBY LOGFILE
DATAFILE
‘/uocdb1/oradata/UAS/system01.dbf’,
‘/uocdb1/oradata/UAS/undotbs01.dbf’,
‘/uocdb1/oradata/UAS/sysaux01.dbf’,
‘/uocdb1/oradata/UAS/users01.dbf’,
‘/uocdb1/oradata/UAS/uas_data_01.dbf’,
‘/uocdb1/oradata/UAS/uas_data_02.dbf’,
‘/uocdb1/oradata/UAS/uas_data_03.dbf’,
‘/uocdb1/oradata/UAS/uas_data_04.dbf’,
‘/uocdb2/oradata/UAS/uas_index_01.dbf’,
‘/uocdb2/oradata/UAS/uas_index_02.dbf’,
‘/uocdb2/oradata/UAS/uas_index_03.dbf’,
‘/uocdb2/oradata/UAS/uas_index_04.dbf’,
‘/uocdb1/oradata/UAS/system02.dbf’,
‘/uocdb1/oradata/UAS/neulion.dbf’,
‘/uocdb1/oradata/UAS/neulion02.dbf’
CHARACTER SET UTF8;
Control file created successfully.
SQL> ALTER DATABASE OPEN RESETLOGS;
Database is open successfully with new instance name.
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE ‘/uocdb1/oradata/UAS/temp01.dbf’ SIZE 1024M reuse;
Tablespace altered.
***** Listener rename *****
(13) Stop running listener
$ lsnrctl stop LISTENER_UOCTEST
(14) modify listener.ora. rename UOCTEST to UAS.
(15) start listener
$ lsnrctl start LISTENER_UAS
***************************** Instance Renamed *******************