OS upgradation and Production DB Recovered (size 320 Gigs)

Implemented steps
Scenario is
The server OS has been upgraded from Windows 2000 standard edition to Windows 2003 standard edition, Oracle 10.2.0.3 is installed in C: drive where the OS has been installed.
Apart from this we have additional D: drive and E: drive mounted in the server.
Note: All the database files are present in the D: drive and E: drive.
Client wants to upgrade the OS in this server, So what he did is he removed the mounted drives (D: and F:) and upgraded the OS to Windows 2003 standard edition.
And after installing the OS Ms 2003 he again remounted the drive, since both the OS versions has the same filesystem no need of formating the remounted drives.

So all the Datafiles were present in the drives as it was before.
Before this, Additionally to be in safer side :

1. We had the rman backup of Database and Spfile and controlfile.
2. We also had a backup of control file by trace(ie: in text format).
3. We also had a backup of updated spfile and pfile in text format.

So in this case what we did is,
Step 1 : We installed the Oracle 10.2.0.1 version in C: drive.
Step 2 : Apply patch 10.2.0.3 to the 10.2.0.1 software home in C: drive.
While patching we got an error as below

Workaround is to go to services.msc and stop the Distributed Transaction Coordinator services and retry.
After that it worked like charm. !
Step 3: After Applying the patch, Manually create the Oracle ADUMP,BDUMP,CDUMP,PFILE,DPDUMP folders in the “C:\oracle\admin\shat8\ “ location and copy the pfile (which we have a backup already) to the pfile directory in Oracle location “ c:\oracle\admin\shat8\pfile\ “.

Note:before going to step 4 update the pfile according to the changes in the new installation if any.

Step 4: Go to command prompt and create the service using ORADIM utility by using the upated backup pfile which we already taken.
C:\> Oradim –new –sid shat8 –pfile ‘c:\oracle\admin\shat8\pfile\init.ora’
Step 5: Create password file using orapwd like ORADIM.
C:\> set ORACLE_SID=shat8
C:\> orapwd file= password= entries=
Step 6: Since we have all the data files in the mounted drive itself don’t need to restore the datafiles from Rman Backup.

But Client has mistakenly remounted drives with different Drive letters (D: drive as E: drive <> E: drive as F: drive). So as per the Database controlfile records most of the datafiles are in E: drive and some of the datafiles are present in F: drive.
So what we did is we recreated the controlfile with the trace (text ) backup which we have already taken by updating the script with the changes (ie D: drive as E: drive and E: drive as F: drive) as below

C:\>Set Oracle_sid=shat8
C:\>Set Oracle_home=c:\oracle\product\10.2.0\db_1\
C:\>Cd %ORACLE_HOME%
C:\oracle\product\10.2.0\db_1\>sqlplus sys/****** as sysdba

///
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "SHAT8" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 10278
LOGFILE
GROUP 1 'E:\ORACLE10G\SHAT8\ADMIN\REDO01A.LOG' SIZE 100M,
GROUP 2 'E:\ORACLE10G\SHAT8\ADMIN\REDO02A.LOG' SIZE 100M,
GROUP 3 'E:\ORACLE10G\SHAT8\ADMIN\REDO03A.LOG' SIZE 100M,
GROUP 4 'E:\ORACLE10G\SHAT8\ADMIN\REDO04A.LOG' SIZE 100M
-- STANDBY LOGFILE
DATAFILE
'F:\ORACLE10G\SHAT8\ORADATA\SYSTEM01.DBF',
'F:\ORACLE10G\SHAT8\ORADATA\UNDOTBS01.DBF',
'F:\ORACLE10G\SHAT8\ORADATA\SYSAUX01.DBF',
'F:\ORACLE10G\SHAT8\ORADATA\USERS01.DBF',
'F:\ORACLE10G\SHAT8\ORADATA\BACH_A\TS_BACH_A_01.DBF',
'E:\ORACLE10G\SHAT8\INDEX\BACH_A\INDX_BACH_A_01.DBF',
'F:\ORACLE10G\SHAT8\ORADATA\BACH_A\TS_A_DATA_FY2005.DBF',
'F:\ORACLE10G\SHAT8\ORADATA\BACH_A\TS_A_DATA_FY2006.DBF',
'F:\ORACLE10G\SHAT8\ORADATA\BACH_A\TS_A_DATA_FY2007_Q1.DBF',
'F:\ORACLE10G\SHAT8\ORADATA\BACH_A\TS_A_DATA_FY2007_Q2.DBF',
'F:\ORACLE10G\SHAT8\ORADATA\BACH_A\TS_A_DATA_FY2007_Q3.DBF',
'F:\ORACLE10G\SHAT8\ORADATA\BACH_A\TS_A_DATA_FY2007_Q4.DBF',
'F:\ORACLE10G\SHAT8\ORADATA\BACH_A\TS_A_DATA_FY2008_Q1.DBF',
'F:\ORACLE10G\SHAT8\ORADATA\BACH_A\TS_A_DATA_FY2008_Q2.DBF',
'F:\ORACLE10G\SHAT8\ORADATA\BACH_A\TS_A_DATA_FY2008_Q3.DBF',
'F:\ORACLE10G\SHAT8\ORADATA\BACH_A\TS_A_DATA_FY2008_Q4.DBF',
'E:\ORACLE10G\SHAT8\INDEX\BACH_A\INDX_A_DATA_FY2005.DBF',
'E:\ORACLE10G\SHAT8\INDEX\BACH_A\INDX_A_DATA_FY2006.DBF',
'E:\ORACLE10G\SHAT8\INDEX\BACH_A\INDX_A_DATA_FY2007_Q1.DBF',
'E:\ORACLE10G\SHAT8\INDEX\BACH_A\INDX_A_DATA_FY2007_Q2.DBF',
'E:\ORACLE10G\SHAT8\INDEX\BACH_A\INDX_A_DATA_FY2007_Q3.DBF',
'E:\ORACLE10G\SHAT8\INDEX\BACH_A\INDX_A_DATA_FY2007_Q4.DBF',
'E:\ORACLE10G\SHAT8\INDEX\BACH_A\INDX_A_DATA_FY2008_Q1.DBF',
'E:\ORACLE10G\SHAT8\INDEX\BACH_A\INDX_A_DATA_FY2008_Q2.DBF',
'E:\ORACLE10G\SHAT8\INDEX\BACH_A\INDX_A_DATA_FY2008_Q3.DBF',
'E:\ORACLE10G\SHAT8\INDEX\BACH_A\INDX_A_DATA_FY2008_Q4.DBF',
'F:\ORACLE10G\SHAT8\ORADATA\BACH_DE\TS_DE_DATA_FY2005.DBF',
'F:\ORACLE10G\SHAT8\ORADATA\BACH_DE\TS_BACH_DE_01.DBF',
'F:\ORACLE10G\SHAT8\ORADATA\BACH_DE\TS_DE_DATA_FY2006.DBF',
'F:\ORACLE10G\SHAT8\ORADATA\BACH_DE\TS_DE_DATA_FY2007_Q1.DBF',
'F:\ORACLE10G\SHAT8\ORADATA\BACH_DE\TS_DE_DATA_FY2007_Q2.DBF',
'E:\ORACLE10G\SHAT8\INDEX\BACH_DE\INDX_BACH_DE_01.DBF',
'F:\ORACLE10G\SHAT8\ORADATA\BACH_DE\TS_DE_DATA_FY2007_Q3.DBF',
'F:\ORACLE10G\SHAT8\ORADATA\BACH_DE\TS_DE_DATA_FY2008_Q1.DBF',
'F:\ORACLE10G\SHAT8\ORADATA\BACH_DE\TS_DE_DATA_FY2007_Q4.DBF',
'F:\ORACLE10G\SHAT8\ORADATA\BACH_DE\TS_DE_DATA_FY2008_Q2.DBF',
'E:\ORACLE10G\SHAT8\INDEX\BACH_DE\INDX_DE_DATA_FY2005.DBF',
'F:\ORACLE10G\SHAT8\ORADATA\BACH_DE\TS_DE_DATA_FY2008_Q3.DBF',
'F:\ORACLE10G\SHAT8\ORADATA\BACH_DE\TS_DE_DATA_FY2008_Q4.DBF',
'E:\ORACLE10G\SHAT8\INDEX\BACH_DE\INDX_DE_DATA_FY2006.DBF',
'E:\ORACLE10G\SHAT8\INDEX\BACH_DE\INDX_DE_DATA_FY2007_Q1.DBF',
'E:\ORACLE10G\SHAT8\INDEX\BACH_DE\INDX_DE_DATA_FY2008_Q1.DBF',
'E:\ORACLE10G\SHAT8\INDEX\BACH_DE\INDX_DE_DATA_FY2007_Q2.DBF',
'E:\ORACLE10G\SHAT8\INDEX\BACH_DE\INDX_DE_DATA_FY2008_Q2.DBF',
'E:\ORACLE10G\SHAT8\INDEX\BACH_DE\INDX_DE_DATA_FY2007_Q3.DBF',
'E:\ORACLE10G\SHAT8\INDEX\BACH_DE\INDX_DE_DATA_FY2008_Q3.DBF',
'E:\ORACLE10G\SHAT8\INDEX\BACH_DE\INDX_DE_DATA_FY2007_Q4.DBF',
'E:\ORACLE10G\SHAT8\INDEX\BACH_DE\INDX_DE_DATA_FY2008_Q4.DBF',
'F:\ORACLE10G\SHAT8\ORADATA\UNDOTBS02.DBF',
'E:\ORACLE10G\SHAT8\INDEX\BACH_DE\INDX_BACH_DE_02.DBF',
'F:\ORACLE\ORADATA\STAT\TS_DE_DATA_FY2009_Q1.ORA',
'F:\ORACLE\ORADATA\STAT\TS_DE_DATA_FY2009_Q2.ORA',
'F:\ORACLE\ORADATA\STAT\TS_DE_DATA_FY2009_Q3.ORA',
'F:\ORACLE\ORADATA\STAT\TS_DE_DATA_FY2009_Q4.ORA',
'F:\ORACLE\ORADATA\STAT\INDX_DE_DATA_FY2009_Q1.ORA',
'F:\ORACLE\ORADATA\STAT\INDX_DE_DATA_FY2009_Q2.ORA',
'F:\ORACLE\ORADATA\STAT\INDX_DE_DATA_FY2009_Q3.ORA',
'F:\ORACLE\ORADATA\STAT\INDX_DE_DATA_FY2009_Q4.ORA',
'F:\ORACLE10G\SHAT8\ORADATA\BACH_A\TS_A_DATA_FY2009_Q1.DBF',
'F:\ORACLE10G\SHAT8\ORADATA\BACH_A\TS_A_DATA_FY2009_Q2.DBF',
'F:\ORACLE10G\SHAT8\ORADATA\BACH_A\TS_A_DATA_FY2009_Q3.DBF',
'F:\ORACLE10G\SHAT8\ORADATA\BACH_A\TS_A_DATA_FY2009_Q4.DBF',
'F:\ORACLE10G\SHAT8\ORADATA\BACH_A\TS_A_DATA_FY2010_Q1.DBF',
'F:\ORACLE10G\SHAT8\ORADATA\BACH_A\TS_A_DATA_FY2010_Q2.DBF',
'F:\ORACLE10G\SHAT8\ORADATA\BACH_A\TS_A_DATA_FY2010_Q3.DBF',
'F:\ORACLE10G\SHAT8\ORADATA\BACH_A\TS_A_DATA_FY2010_Q4.DBF',
'E:\ORACLE10G\SHAT8\INDEX\BACH_A\INDX_A_DATA_FY2009_Q1.DBF',
'E:\ORACLE10G\SHAT8\INDEX\BACH_A\INDX_A_DATA_FY2009_Q2.DBF',
'E:\ORACLE10G\SHAT8\INDEX\BACH_A\INDX_A_DATA_FY2009_Q3.DBF',
'E:\ORACLE10G\SHAT8\INDEX\BACH_A\INDX_A_DATA_FY2009_Q4.DBF',
'E:\ORACLE10G\SHAT8\INDEX\BACH_A\INDX_A_DATA_FY2010_Q1.DBF',
'E:\ORACLE10G\SHAT8\INDEX\BACH_A\INDX_A_DATA_FY2010_Q2.DBF',
'E:\ORACLE10G\SHAT8\INDEX\BACH_A\INDX_A_DATA_FY2010_Q3.DBF',
'E:\ORACLE10G\SHAT8\INDEX\BACH_A\INDX_A_DATA_FY2010_Q4.DBF',
'F:\ORACLE10G\SHAT8\ORADATA\BACH_DE\TS_DE_DATA_FY2010_Q1.DBF',
'F:\ORACLE10G\SHAT8\ORADATA\BACH_DE\TS_DE_DATA_FY2010_Q2.DBF',
'F:\ORACLE10G\SHAT8\ORADATA\BACH_DE\TS_DE_DATA_FY2010_Q3.DBF',
'F:\ORACLE10G\SHAT8\ORADATA\BACH_DE\TS_DE_DATA_FY2010_Q4.DBF',
'E:\ORACLE10G\SHAT8\INDEX\BACH_DE\INDX_BACH_DE_03.DBF',
'F:\ORACLE10G\SHAT8\ORADATA\BACH_DE\BACH_DE04.DBF',
'F:\ORACLE10G\SHAT8\ORADATA\BACH_A\INDX_BACH_A02.DBF',
'F:\ORACLE10G\SHAT8\ORADATA\BACH_A\INDX_BACH_A03.DBF',
'F:\ORACLE10G\SHAT8\ORADATA\BACH_DE\INDX_DE_FY2008_Q3.DBF',
'F:\ORACLE10G\SHAT8\ORADATA\BACH_DE\INDX_DE_DATA_FY2008_Q2.DBF'
CHARACTER SET JA16SJIS
;

////
Step 7: Recover the database as mentioned in the controlfile trace
File. This step is followed as per the comment in the controlfile trace as below.

-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.

RECOVER DATABASE
-- All logs need archiving and a log switch is needed.

ALTER SYSTEM ARCHIVE LOG ALL;

Step 8: Open the database and add the temp file as below.
ALTER DATABASE OPEN;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.

ALTER TABLESPACE TEMP ADD TEMPFILE 'E:\ORACLE10G\SHAT8\ORADATA\TEMP01.ORA'
SIZE 10000M REUSE AUTOEXTEND OFF;
ALTER TABLESPACE TEMP ADD TEMPFILE 'E:\ORACLE10G\SHAT8\ORADATA\TEMP027.DBF'
SIZE 4000M REUSE AUTOEXTEND OFF;
ALTER TABLESPACE TEMP ADD TEMPFILE 'E:\ORACLE10G\SHAT8\ORADATA\TEMP026.DBF'
SIZE 4000M REUSE AUTOEXTEND OFF;
ALTER TABLESPACE TEMP ADD TEMPFILE 'E:\ORACLE10G\SHAT8\ORADATA\TEMP025.DBF'
SIZE 4000M REUSE AUTOEXTEND OFF;
ALTER TABLESPACE TEMP ADD TEMPFILE 'E:\ORACLE10G\SHAT8\ORADATA\TEMP024.DBF'
SIZE 2000M REUSE AUTOEXTEND OFF;
ALTER TABLESPACE TEMP ADD TEMPFILE 'E:\ORACLE10G\SHAT8\ORADATA\TEMP023.DBF'
SIZE 2000M REUSE AUTOEXTEND OFF;
ALTER TABLESPACE TEMP ADD TEMPFILE 'E:\ORACLE10G\SHAT8\ORADATA\TEMP022.DBF'
SIZE 2000M REUSE AUTOEXTEND OFF;
ALTER TABLESPACE TEMP ADD TEMPFILE 'E:\ORACLE10G\SHAT8\ORADATA\TEMP021.DBF'
SIZE 2000M REUSE AUTOEXTEND OFF;
-- End of tempfile additions.

Step 9: create spfile from pfile=’c:\oracle\admin\shat8\pfile\init.ora’;
Step 10: Shutdown immediate
Step 11: Startup
Step 12: Now configure the listener using NETCA and NETMGR utility and configure the database with the listener and test from a client machine.

Finally check the components, number of datafiles,status of datafiles and check the archive log by switching the redo logs and do other basic checks.