Skip to content
pvmehta.com

pvmehta.com

  • Home
  • About Me
  • Toggle search form
  • How To Resolve Stranded DBA_2PC_PENDING Entries ID 401302.1 (Very Good prooven) Oracle
  • OPENING A STANDBY DATABASE IN READ-ONLY MODE Oracle
  • Good RAC & Standby Notes Oracle
  • Drop specific SQL plan baseline – spm Oracle
  • How does one SELECT a value from a table into a Unix variable? From SQL to Shell Linux/Unix
  • To find explain plan for a statement that occurred in past. Oracle
  • Finding last recovered file on DR and remove all chanracters before any “/” Linux/Unix
  • How can I tell if ASO is installed ? Oracle
  • How to sort list of files on basis of their sizes. Linux/Unix
  • block_ident.sql Oracle
  • Oracle Connections expire_time and firewall Oracle
  • db_status.sql Oracle
  • Kill a session dynanically using execute immediate Oracle
  • Pending Distributed Transations Oracle
  • lck.sql Oracle

Renaming Oracle Instance Name

Posted on 30-Aug-2005 By Admin No Comments on Renaming Oracle Instance Name

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//pfile

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 *******************

Oracle, SQL scripts

Post navigation

Previous Post: Monitor and Trace Unix processes using truss
Next Post: Wait Based Tuning Step by step with SQL statement

Related Posts

  • Standby Database File Management in 10g with STANDBY_FILE_MANAGEMENT Oracle
  • Convert multiple rows to single column Oracle
  • All About oracle password and security from metalink Oracle
  • OEM-troubleshooting on 20-MAY-08 Oracle
  • Optimizer_Index init.ora parameter explaination. Oracle
  • How to set Processes Parameter Oracle

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Categories

  • AWS (2)
  • Azure (1)
  • Linux/Unix (149)
  • Oracle (392)
  • PHP/MYSQL/Wordpress (10)
  • Power-BI (0)
  • Python/PySpark (7)
  • RAC (17)
  • rman-dataguard (26)
  • shell (149)
  • SQL scripts (341)
  • Uncategorized (0)
  • Videos (0)

Recent Posts

  • load SPM baseline from cursor cache05-Jun-2025
  • Drop all SPM baselines for SQL handle05-Jun-2025
  • Load SPM baseline from AWR05-Jun-2025
  • Drop specific SQL plan baseline – spm05-Jun-2025
  • findinfo.sql (SQL for getting CPU and Active session info)27-May-2025
  • SQL Tracker by SID sqltrackerbysid.sql22-Apr-2025
  • How to connect to Oracle Database with Wallet with Python.21-Mar-2025
  • JSON/XML Types in Oracle18-Mar-2025
  • CPU Core related projections12-Mar-2025
  • Exadata Basics10-Dec-2024

Archives

  • 2025
  • 2024
  • 2023
  • 2010
  • 2009
  • 2008
  • 2007
  • 2006
  • 2005
  • scripts to take listener.log backup Linux/Unix
  • Jai Shree Ram Oracle
  • Kernel Parameters for Solaris Linux/Unix
  • Finding Oracle Patches with opatch Oracle
  • get_vmstat.ksh Linux/Unix
  • SQL_PLAN.sql for checking real execution plan Oracle
  • Specify the Rollback segment to use in Transaction Oracle
  • Paste command syntax Linux/Unix

Copyright © 2025 pvmehta.com.

Powered by PressBook News WordPress theme