Skip to content
pvmehta.com

pvmehta.com

  • Home
  • About Me
  • Toggle search form
  • sql_plan9i.sql Oracle
  • default permission on ~/.ssh/authorized_keys2 or authorized_keys Linux/Unix
  • Privileges Required to Create Procedures and Functions that uses objects from other schema. Oracle
  • Oracle 10g Installation/Applying Patches Tips Oracle
  • Some OS level threshold for performance. Linux/Unix
  • How to hide author name in WordPress BLOG PHP/MYSQL/Wordpress
  • Roles and Stored Procs II Oracle
  • Export Oracle data and Compress at same time Oracle
  • how to find VIP from ifconfig Linux/Unix
  • crtgr.sql /* For creating trigger from data dictionary */ Oracle
  • How does one SELECT a value from a table into a Unix variable? From SQL to Shell Linux/Unix
  • compile_inv.sql Oracle
  • oracle 10g on linux Linux/Unix
  • Settting up get_vmstat.sh for colletinf CPU Usage. Oracle
  • move_arch_files.ksh Linux/Unix

Rename Oracle Instance Name

Posted on 02-Aug-2005 By Admin No Comments on Rename 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

Oracle, SQL scripts

Post navigation

Previous Post: PLSQL Table Syntax 2
Next Post: eplan9i.sql

Related Posts

  • How to find password change date for user Oracle
  • get_aix_vmstat.ksh Oracle
  • find_string_in_database.sql Oracle
  • Metalink Note: Note:250655.1 : ADDM Basics USING THE AUTOMATIC DATABASE DIAGNOSTIC MONITOR Oracle
  • Finding locked objects Oracle
  • findx.sql /* Find Indexes on specified USER.TABLE_NAME */ 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
  • currwaitobj.sql SQl_ID and SQL statement you can get from currwaitobj.sql Oracle
  • sid_wise_sql.sql Further explaination Oracle
  • before_trunc.sql Before Truncate table needs to execute following: Oracle
  • My Test Case On 21-OCT-2005 Oracle
  • Temporary Tablespsace Temp tablespace behaviour Oracle
  • oracle_env_10g_CADEV Linux/Unix
  • copying/removing directory with all its subdirectory Linux/Unix
  • All About Trace Fils Oracle

Copyright © 2025 pvmehta.com.

Powered by PressBook News WordPress theme