Sunday, April 22, 2012

Recoving Oracle..'Wind' database...

It may happen that we might have an Oracle database crash power  down situations and Oracle never comes up.  Oracle might never come up due to corrupt block or any other issues. There may be errors like ORA-01578 or corruption.
There are some simple steps we need to follow
SQL>sqlplus / as sysdba
SQL> shutdown immediate
SQL> startup nomount
SQL>alter database open;
If all these steps go through without any issues then you the lucky person and there are no issues.
In case there is a real corruption we may have to recover the database In this Situation we need to recover the database using REDO logs.
Follow below steps
Take a backup of the entire OCU folder
SQL>sqlplus / as sysdba
SQL> shutdown immediate
SQL> startup nomount

SQL> SELECT MEMBER FROM V$LOG G, V$LOGFILE F WHERE G.GROUP# = F.GROUP#  AND G.STATUS = 'CURRENT';
This query outputs the path of the redo log that we need to submit in the next query.
SQL>recover database until cancel
In this step you should the path the is obtained in the above step
SQL>alter database open resetlogs;
If none of the above work then we need to create a new set of control files in addition to the above steps,
Follow below steps
Take a backup of the entire OCU folder and delete the .CTL files from the OCU folder.
SQL>sqlplus / as sysdba
SQL> shutdown immediate
SQL> startup nomount
SQL>CREATE CONTROLFILE REUSE DATABASE "WIND" RESETLOGS NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 'D:\ptc\Windchill_9.1\ocu\oradata\wind\WINDREDO01.LOG'  SIZE 100M,
  GROUP 2 'D:\ptc\Windchill_9.1\ocu\oradata\wind\WINDREDO02.LOG'  SIZE 100M,
  GROUP 3 'D:\ptc\Windchill_9.1\ocu\oradata\wind\WINDREDO03.LOG'  SIZE 100M
DATAFILE
  'D:\ptc\Windchill_9.1\ocu\oradata\wind\WINDBLOBS01.DBF',
  'D:\ptc\Windchill_9.1\ocu\oradata\wind\WINDINDEX01.DBF',
  'D:\ptc\Windchill_9.1\ocu\oradata\wind\WINDSYSAUX01.DBF',
  'D:\ptc\Windchill_9.1\ocu\oraata\wind\WINDSYSTEM01.DBF',
  'D:\ptc\Windchill_9.1\ocu\oradata\wind\WINDUNDOTBS01.DBF',
  'D:\ptc\Windchill_9.1\ocu\oradata\wind\WINDUSERS01.DBF',
  'D:\ptc\Windchill_9.1\ocu\oradata\wind\WINDWCAUDIT01.DBF'
;
SQL> SELECT MEMBER FROM V$LOG G, V$LOGFILE F WHERE G.GROUP# = F.GROUP#  AND G.STATUS = 'CURRENT';
This query outputs the path of the redo log that we need to submit in the next query.
SQL>recover database until cancel
In this step you should the path the is obtained in the above step
SQL>alter database open resetlogs;
These all 3 methods usually suffice to recover a crashed database. But if even these 3 methods donot recover your database then you need to get in touch with a database expert.