SQL*Plus: Release 10.2.0.1.0 - Production on Pet Jun 22 11:04:36 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> connect
Enter user-name: sys as sysdba
Enter password:
Connected.
SQL> shutdown normal
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  251658240 bytes
Fixed Size                  1286820 bytes
Variable Size              83889500 bytes
Database Buffers          163577856 bytes
Redo Buffers                2904064 bytes
Database mounted.
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1:
'C:\ORACLEXE\APP\ORACLE\FLASH_RECOVERY_AREA\XE\ONLINELOG\O1_MF_2_36ZOHKTC_.LOG'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.


SQL>

SQL>
SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
E:\ORACLE\ORADATA\WIN9I\REDO03.LOG
E:\ORACLE\ORADATA\WIN9I\REDO02.LOG
E:\ORACLE\ORADATA\WIN9I\REDO01.LOG

SQL> select * from v$controlfile;

STATUS  NAME
------- ----------------------------------------
        E:\ORACLE\ORADATA\WIN9I\CONTROL01.CTL
        E:\ORACLE\ORADATA\WIN9I\CONTROL02.CTL
        E:\ORACLE\ORADATA\WIN9I\CONTROL03.CTL

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

E:\REM Remove all redologs and controlfiles associated with the database

E:\>del E:\ORACLE\ORADATA\WIN9I\redo*.log

E:\>del E:\ORACLE\ORADATA\WIN9I\control*.ctl

E:\>sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.1.0 - Production on Sun Jul 8 00:10:27 2007

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area  135338868 bytes
Fixed Size                   453492 bytes
Variable Size             109051904 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "WIN9I" RESETLOGS  ARCHIVELOG
  2  --  SET STANDBY TO MAXIMIZE PERFORMANCE
  3      MAXLOGFILES 5
  4      MAXLOGMEMBERS 3
  5      MAXDATAFILES 100
  6      MAXINSTANCES 1
  7      MAXLOGHISTORY 226
  8  LOGFILE
  9    GROUP 1 'E:\ORACLE\ORADATA\WIN9I\REDO01.LOG'  SIZE 20M,
 10    GROUP 2 'E:\ORACLE\ORADATA\WIN9I\REDO02.LOG'  SIZE 20M,
 11    GROUP 3 'E:\ORACLE\ORADATA\WIN9I\REDO03.LOG'  SIZE 20M
 12  -- STANDBY LOGFILE
 13  DATAFILE
 14    'E:\ORACLE\ORADATA\WIN9I\SYSTEM01.DBF',
 15    'E:\ORACLE\ORADATA\WIN9I\UNDOTBS01.DBF',
 16    'E:\ORACLE\ORADATA\WIN9I\CWMLITE01.DBF',
 17    'E:\ORACLE\ORADATA\WIN9I\DRSYS01.DBF',
 18    'E:\ORACLE\ORADATA\WIN9I\EXAMPLE01.DBF',
 19    'E:\ORACLE\ORADATA\WIN9I\INDX01.DBF',
 20    'E:\ORACLE\ORADATA\WIN9I\ODM01.DBF',
 21    'E:\ORACLE\ORADATA\WIN9I\TOOLS01.DBF',
 22    'E:\ORACLE\ORADATA\WIN9I\USERS01.DBF',
 23    'E:\ORACLE\ORADATA\WIN9I\XDB01.DBF',
 24  CHARACTER SET WE8MSWIN1252
 25  ;

Control file created.

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 1143873 generated at 07/08/2007 00:08:10 needed for thread 1
ORA-00289: suggestion : E:\DB_BACKUPS\ARCHIVE\ARC00001.001
ORA-00280: change 1143873 for thread 1 is in sequence #1


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> alter database open resetlogs;

Database altered.

SQL> select status from v$instance;

STATUS
------------
OPEN

SQL>