Renaming an Oracle instance on Windows



I don't remember doing it before, as almost every database is on UNIX and on Windows one tends to use graphical tools, but a couple of days ago I decided to rename TTAU2 to TTAU1, which contains our Apex development environment.

The steps are very much the same as on UNIX, the main different being that a Windows service must be created with oradim before re-creating the controlfile.

I took a cold backup of the TTAU2 before starting

SQL> select name from v$database;

NAME
---------
TTAU2

SQL> select name from v$datafile;

NAME
---------------------------------------------------------------
M:\ORADATA\TTAU2\SYSTEM01.DBF
M:\ORADATA\TTAU2\UNDOTBS01.DBF
M:\ORADATA\TTAU2\SYSAUX01.DBF
M:\ORADATA\TTAU2\USERS01.DBF
M:\ORADATA\TTAU2\APEX_BRASPROD_DATA_01.DBF
M:\ORADATA\TTAU2\RIMINI_01.DBF
M:\ORADATA\TTAU2\PISA_01.DBF
M:\ORADATA\TTAU2\FLOW_1.DBF
M:\ORADATA\TTAU2\FLOW_2.DBF
M:\ORADATA\TTAU2\APEX_BRASPROD_DATA_01_01.DBF
M:\ORADATA\TTAU2\APEX_BRASPROD_INDX_01_01.DBF
M:\ORADATA\TTAU2\CONFIG_DATA_01_01.DBF
M:\ORADATA\TTAU2\RENZO_DEV_01.DBF

13 rows selected.

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER 
---------- ------- ------- ----------------------------
         3         ONLINE  M:\ORADATA\TTAU2\REDO03.LOG
         2         ONLINE  M:\ORADATA\TTAU2\REDO02.LOG
         1         ONLINE  M:\ORADATA\TTAU2\REDO01.LOG
Everything is contained on M:\ORADATA\TTAU2, which makes the backup and the renaming easier
M:\oradata>mkdir TTAU1

M:\oradata>copy TTAU2\* TTAU1
TTAU2\APEX_BRASPROD_DATA_01.DBF
TTAU2\APEX_BRASPROD_DATA_01_01.DBF
TTAU2\APEX_BRASPROD_INDX_01_01.DBF
TTAU2\CONTROL01.CTL
TTAU2\CONTROL02.CTL
TTAU2\CONTROL03.CTL
TTAU2\CONFIG_DATA_01_01.DBF
TTAU2\RIMINI_01.DBF
TTAU2\FLOW_1.DBF
TTAU2\FLOW_2.DBF
TTAU2\PISA_01.DBF

SQL> show parameter dump

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
background_core_dump                 string      partial
background_dump_dest                 string      D:\ORACLE\PRODUCT\10.2.0\ADMIN
                                                 \TTAU2\BDUMP
core_dump_dest                       string      D:\ORACLE\PRODUCT\10.2.0\ADMIN
                                                 \TTAU2\CDUMP
max_dump_file_size                   string      UNLIMITED
shadow_core_dump                     string      partial
user_dump_dest                       string      D:\ORACLE\PRODUCT\10.2.0\ADMIN
                                                 \TTAU2\UDUMP
SQL>

Create a new initTTAU1.ora parameter file



Create a new directory structure ..admin/TTAU1 with the dump destinations



Execute ALTER DATABASE BACKUP CONTROLFILE TO TRACE

Edit the trace file generated on udump using the clause "SET DATABASE" and specifying the new datafiles
STARTUP NOMOUNT pfile=D:\oracle\product\10.2.0\db_1\database\initTTAU1.ora
CREATE CONTROLFILE SET DATABASE "TTAU1" RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 'M:\ORADATA\TTAU1\REDO01.LOG'  SIZE 50M,
  GROUP 2 'M:\ORADATA\TTAU1\REDO02.LOG'  SIZE 50M,
  GROUP 3 'M:\ORADATA\TTAU1\REDO03.LOG'  SIZE 50M
-- STANDBY LOGFILE
DATAFILE
  'M:\ORADATA\TTAU1\SYSTEM01.DBF',
  'M:\ORADATA\TTAU1\UNDOTBS01.DBF',
  'M:\ORADATA\TTAU1\SYSAUX01.DBF',
  'M:\ORADATA\TTAU1\USERS01.DBF',
  'M:\ORADATA\TTAU1\APEX_BRASPROD_DATA_01.DBF',
  'M:\ORADATA\TTAU1\RIMINI_01.DBF',
  'M:\ORADATA\TTAU1\PISA_01.DBF',
  'M:\ORADATA\TTAU1\FLOW_1.DBF',
  'M:\ORADATA\TTAU1\FLOW_2.DBF',
  'M:\ORADATA\TTAU1\APEX_BRASPROD_DATA_01_01.DBF',
  'M:\ORADATA\TTAU1\APEX_BRASPROD_INDX_01_01.DBF',
  'M:\ORADATA\TTAU1\CONFIG_DATA_01_01.DBF',
  'M:\ORADATA\TTAU1\RENZO_DEV_01.DBF'
CHARACTER SET WE8MSWIN1252
;

Create a service TTAU1



D:\oracle\product\10.2.0\db_1\database>oradim -new -sid TTAU1 -intpwd password -startmode auto -pfile D:\oracle\product\10.2. 1\database\initTTAU1.ora

Instance created.

SQL> @trace.sql
CREATE CONTROLFILE SET DATABASE "TTAU1" RESETLOGS  ARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-00200: control file could not be created
ORA-00202: control file: 'M:\ORADATA\TTAU1\CONTROL01.CTL'
ORA-27038: created file already exists
OSD-04010:  option specified, file already exists


SQL> shutdown immediate
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> exit
Let's delete the copy of the control file

D:\oracle\product\10.2.0\admin\TTAU1\udump>del M:\ORADATA\TTAU1\CONTROL*.*

sqlplus / as sysdba

SQL> @trace.sql
ORACLE instance started.

Total System Global Area  612368384 bytes
Fixed Size                  1298168 bytes
Variable Size             167772424 bytes
Database Buffers          436207616 bytes
Redo Buffers                7090176 bytes

Control file created.

SQL> alter database open resetlogs;

Database altered.

SQL> exit


Define a listener and put an entry in tnsnames.ora

D:\oracle\product\10.2.0\admin\TTAU1\udump>lsnrctl reload

LSNRCTL for 32-bit Windows: Version 10.2.0.4.0 - Production on 26-AUG-2009 13:35:12

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=london27.se.yus)(PORT=1521)))
The command completed successfully

D:\oracle\product\10.2.0\admin\TTAU1\udump>tnsping TTAU1

TNS Ping Utility for 32-bit Windows: Version 10.2.0.4.0 - Production on 26-AUG-2009 13:35:19

Modify the dads.conf

<Location /pls/apex32>
 Order deny,allow
 PlsqlDocumentPath docs
 AllowOverride None
 PlsqlDocumentProcedure      wwv_flow_file_mgr.process_download
 PlsqlDatabaseConnectString  london27.se.yus:1521:TTAU1 ServiceNameFormat
 PlsqlNLSLanguage            AMERICAN_AMERICA.AL32UTF8
 PlsqlAuthenticationMode     Basic
 SetHandler                  pls_handler
 PlsqlDocumentTablename      wwv_flow_file_objects$
 PlsqlDatabaseUsername       APEX_PUBLIC_USER
 PlsqlDefaultPage            apex
 PlsqlDatabasePassword       yusadmin
 Allow from all
</Location>

No modification in the SSO partner application is needed (the instance name is contained in dads.conf)



The Apex URL is available, but nothing is displayed. Checking the Apache logs the reason becomes evident: TEMP is empty
[Wed Aug 26 09:35:07 2009] [error] [client 10.41.51.204] [ecid: 1251272107:192.44.6.221:276:3408:802,0]
File does not exist: d:/oracle/product/10.2.0/http/apache/apache/htdocs/favicon.ico
[Wed Aug 26 13:19:51 2009] [error] [client 10.41.51.204] [ecid: 1251285591:192.44.6.221:276:3612:860,0] mod_plsql: /pls/apex32/f HTTP-503 ORA-1034 
[Wed Aug 26 13:25:11 2009] [error] [client 10.41.51.204] [ecid: 1251285910:192.44.6.221:276:2176:843,0] mod_plsql: /pls/apex32/f HTTP-503 ORA-1034 
[Wed Aug 26 13:39:48 2009] [notice] FastCGI: process manager initialized
[Wed Aug 26 13:40:24 2009] [error] [client 10.41.51.90] [ecid: 1251286824:192.44.6.221:2856:1608:3,0] File does not exist:
d:/oracle/product/10.2.0/http/apache/apache/htdocs/favicon.ico
[Wed Aug 26 13:43:33 2009] [notice] FastCGI: process manager initialized
[Wed Aug 26 13:43:53 2009] [error] [client 10.41.51.90] [ecid: 1251287033:192.44.6.221:1008:2568:5,0] mod_plsql: /pls/apex32/apex_admin HTTP-404
ORA-25153: Temporary Tablespace is Empty\nORA-06512: at "APEX_030200.F", line 279\nORA-06512: at "APEX_030200.APEX_ADMIN", line 5\nORA-06512: at line 22\n
M:\oradata\TTAU1>sqlplus / as sysdba
SQL> alter tablespace temp add tempfile 'M:\oradata\TTAU1\TEMP01.DBF' reuse;

Tablespace altered.


Rate this note ...
Useless Poor Average Good Very helpful