ORACLE 9i 9.1.0.1 DATABASE RECOVERY

ORACLE 9i 9.1.0.1 DATABASE RECOVERY scenario [ COLD BACKUP ]

OS :windows 2003 server

All database files [pfile,controlfile,redolog,datafile] are copied to new machine as the DB Server OS was corrupted.

Finding :
1. unable to login
   sqlplus /nolog
   SQL> conn / as sysdba              // shows error

2. Unable to start database using pfile.
3. Listner Satatus is Unknown
3. Sys password not provided
4. All database files are in respective directories.
5. Database software was installed.
6. used sys and the default password is change_on_install
7. checked services.msc

Solution:

1. we have created database using dbca with dbname found in init.ora(backup) i.e. XYZ
2. After successfull creation of database XYZ, we create a tracefile using following command.
   SQL> alter database backup controlfile to trace;
3. trace file can be found inside "udump" directory
4. To created new controlfile by following :

CREATE CONTROLFILE REUSE DATABASE "XYZ" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 5
    MAXLOGMEMBERS 3
    MAXDATAFILES 14
    MAXINSTANCES 1
    MAXLOGHISTORY 226
LOGFILE
  GROUP 1 'E:\ORACLE\ORADATA\ORCL\REDO01.LOG'  SIZE 100M,
  GROUP 2 'E:\ORACLE\ORADATA\ORCL\REDO02.LOG'  SIZE 100M,
  GROUP 3 'E:\ORACLE\ORADATA\ORCL\REDO03.LOG'  SIZE 100M
DATAFILE
  'E:\ORACLE\ORADATA\ORCL\SYSTEM01.DBF',
  'E:\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF',
  'E:\ORACLE\ORADATA\ORCL\EXAMPLE01.DBF',
  'E:\ORACLE\ORADATA\ORCL\INDX01.DBF',
  'E:\ORACLE\ORADATA\ORCL\TOOLS01.DBF',
  'E:\ORACLE\ORADATA\ORCL\USERS01.DBF'
.
.
.     // other 3 datafiles

CHARACTER SET WE8MSWIN1252
;

5. shutdown the database using following command:

   SQL> shutdown immediate;

6. Edited the pfile, changed the location of controlfile:
7. Then started the database using pfile :-> SQL> startup nomount pfile=\init.ora;
8. Created controlfile using above script in step no 4
   (we have chage the location of logfile and datafile to the backup location i.e. F:\DB drive)

9. After creation of controlfile the database is in mount mode.
10. when we tried the following :
    SQL> alter database open;

    alter database open
     *
    ERROR at line 1:
    ORA-01113: file 1 needs media recovery
    ORA-01110: data file 1: 'F:\DB\SYSTEM01.DBF'

11. To recover the SYSTEM01.DBF file we issued following command:
    SQL> recover datafile 'F:\DB\SYSTEM01.DBF';
    Media recovery complete.
12. all the datafile are recoverd using above command and we have successfully opened the database.
   
    SQL> alter database open;
    Database altered







------------------------------------------------ :) :) ----