Skip to content
pvmehta.com

pvmehta.com

  • Home
  • About Me
  • Toggle search form
  • pvmehta.com SQL scripts
    Find which sessions is accessing object that prevent your session to have exclusive locks in Oracle Oracle
  • Implementing Listener Security Oracle
  • Giving Grant on v$DATABASE Oracle
  • Explain Plan Output 2 Oracle
  • TRUNCATE Privs Oracle
  • CTAS with LONG Column for 7.x and 8 and 8i Oracle
  • create PLAN_TABLE command. Oracle
  • Directory wise folder wise space usage Linux/Unix
  • To find explain plan for a statement that occurred in past. Oracle
  • Goldengate document from Porus Oracle
  • load SPM baseline from cursor cache Oracle
  • Insert cause enqueue locks Oracle
  • Identical Dblink Issue… Oracle
  • 284785.1 How to check RAC Option is currently linked into the Oracle Binary Oracle
  • online_ts_bkup.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

  • To find explain plan for a statement that occurred in past. Oracle
  • Oracle Connections expire_time and firewall Oracle
  • Parallel DML Oracle
  • Oracle 10g Wait Model Oracle
  • Find sort details from Db find_sort.sql Oracle
  • SQL_PROFILE – I explaination 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
  • Search and Replace vi editor command. Linux/Unix
  • How to collect CPU usage on Linux using Shell script Linux/Unix
  • AWR settings- MMON is not taking snapshot. Oracle
  • PHP code to add WordPress posts in bulk programmatically PHP/MYSQL/Wordpress
  • CPU speed on Linux Linux/Unix
  • FGA Part-I Oracle
  • currwaitobj.sql SQl_ID and SQL statement you can get from currwaitobj.sql Oracle
  • usnsql.sql Displays information about UNDO segments with sql statements Oracle

Copyright © 2025 pvmehta.com.

Powered by PressBook News WordPress theme