tom_colson
asked on
ORA 9.0.2: redo02.log and redo03.log "allegedly" deleted. Misc. other errors in log. Desparate!
Background: We're research shop (University) and we got the wild idea that Oracle would be great storage medium for spatial and hydrologic data. So in April, we did an out of box install of 9.0.2, and it's been running fine ever since. As we're a research shop, obviously, there was no provision in the "grant" for things not related to research such as "tape drives" and "Veritas". Hint hint wink wink. With that said, now we're screwed. As of Aug 18th, the research DB and the Management Repository no longer start. I'm discovering this today. There is a suspicious absence of the 02 and 03 redo logs. We think malicious deletion on an unsecured (by policy) network. And, as a research shop, we only staff researchers, not DB admins, a fact that our tech support dept loves to remind us of. (They'll only support us if we give them ownership of the server, but then they won't allow us the flexibility we need to do research). sooo.....
My goal here is to get 9.0.2 running to a point where I can connect to it using our spatial database front end (Arc SDE 9.0) and suck the data out of it. We've learned our lesson, and will give the #*(@(*@#(*(@* data to IT where THEY can be responsible for it. On a side note, I did have the forsite to turn on one of those archive buttons, and have all the archive logs from install date to aug 18th for the research db. I'm not really concerned about the management repository, as I'm hosing this machine once I get the data, but the knowledge will be helpful.
Here is the alert log:
http://www4.ncsu.edu/~tpcolson/alert_hydro.txt
http://www4.ncsu.edu/~tpcolson/oms_nohup.txt
I get a tnslistner error when attempting to connect via sql plus.
Fire Wall is turned off ( I tried turning off everything) and there is nothing else but Oracle and Arc SDE on the box. There is about 700 GB free disk space (RAID array). I can't find any spyware or trojan horses. In a previous life, I dealt with ITSEC for a banking software company, so I'm a little bit miffed that someone managed to slip past my steel curtain and trash this DB. The renaming of the guest account to "owned" is what tipped me off.....no one but me has had write access to the tables, and I haven't made an edit in some time. Basically this was just a repository of spatial data so others involved in the project could access it from their GIS workstations. There is a vehement opposition to this project, my "results" could cost the home building industry millions, but that's for another email.....
The author of a successfull solution will get a nice thank you letter/letter of rec on NCSU letterhead.
Guys, I only get one shot at this. A botched recovery will torpedoe 2 GB of field data.
My goal here is to get 9.0.2 running to a point where I can connect to it using our spatial database front end (Arc SDE 9.0) and suck the data out of it. We've learned our lesson, and will give the #*(@(*@#(*(@* data to IT where THEY can be responsible for it. On a side note, I did have the forsite to turn on one of those archive buttons, and have all the archive logs from install date to aug 18th for the research db. I'm not really concerned about the management repository, as I'm hosing this machine once I get the data, but the knowledge will be helpful.
Here is the alert log:
http://www4.ncsu.edu/~tpcolson/alert_hydro.txt
http://www4.ncsu.edu/~tpcolson/oms_nohup.txt
I get a tnslistner error when attempting to connect via sql plus.
Fire Wall is turned off ( I tried turning off everything) and there is nothing else but Oracle and Arc SDE on the box. There is about 700 GB free disk space (RAID array). I can't find any spyware or trojan horses. In a previous life, I dealt with ITSEC for a banking software company, so I'm a little bit miffed that someone managed to slip past my steel curtain and trash this DB. The renaming of the guest account to "owned" is what tipped me off.....no one but me has had write access to the tables, and I haven't made an edit in some time. Basically this was just a repository of spatial data so others involved in the project could access it from their GIS workstations. There is a vehement opposition to this project, my "results" could cost the home building industry millions, but that's for another email.....
The author of a successfull solution will get a nice thank you letter/letter of rec on NCSU letterhead.
Guys, I only get one shot at this. A botched recovery will torpedoe 2 GB of field data.
ASKER
Here is the contents of the C:\oracle\oradata\hydro folder. Can one conclude that I don't have the redo folder here?
And this looks like it could be a problem as well....
C:\oracle\oradata\hydro>sq
SQL*Plus: Release 9.2.0.1.0 - Production on Wed Aug 24 20:41:58 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> connect SYS as SYSDBA
Enter password:
ERROR:
ORA-12560: TNS:protocol adapter error
SQL>
Directory of C:\oracle\oradata\hydro
08/24/2005 08:40 PM <DIR> .
08/24/2005 08:40 PM <DIR> ..
08/23/2005 02:45 PM <DIR> archive
08/23/2005 12:32 PM 2,121,728 CONTROL01.CTL
08/23/2005 12:32 PM 2,121,728 CONTROL02.CTL
08/23/2005 12:32 PM 2,121,728 CONTROL03.CTL
08/23/2005 10:45 AM 20,979,712 CWMLITE01.DBF
08/24/2005 08:40 PM 0 dir.txt
08/23/2005 10:45 AM 262,152,192 DOT_ADTBL1.DBF
08/23/2005 10:45 AM 262,152,192 DOT_AINDX1.DBF
08/23/2005 10:45 AM 262,152,192 DOT_BINDX.DBF
08/23/2005 10:45 AM 262,152,192 DOT_BTBL1.DBF
08/23/2005 10:45 AM 262,152,192 DOT_DINDX1.DBF
08/23/2005 10:45 AM 262,152,192 DOT_FINDX1.DBF
08/23/2005 10:45 AM 262,152,192 DOT_FTBL1.TBL
08/23/2005 10:45 AM 262,152,192 DOT_SINDX1.DBF
08/23/2005 10:45 AM 262,152,192 DOT_STBL1.DBF
08/23/2005 10:45 AM 20,979,712 DRSYS01.DBF
08/23/2005 10:45 AM 155,983,872 EXAMPLE01.DBF
08/23/2005 10:45 AM 26,222,592 INDX01.DBF
08/23/2005 10:45 AM 262,152,192 NHD_ADTBL.DBF
08/23/2005 10:45 AM 262,152,192 NHD_AINDX.DBF
08/23/2005 10:45 AM 262,152,192 NHD_BINDX.DBF
08/23/2005 10:45 AM 545,267,712 NHD_BTBL.DBF
08/23/2005 10:45 AM 262,152,192 NHD_DINDX.DBF
08/23/2005 10:45 AM 262,152,192 NHD_FINDX.DBF
08/23/2005 10:45 AM 597,696,512 NHD_FTBL.DBF
08/23/2005 10:45 AM 262,152,192 NHD_SINDX.DBF
08/23/2005 10:45 AM 262,152,192 NHD_STBL.DBF
08/23/2005 10:45 AM 20,979,712 ODM01.DBF
08/23/2005 03:12 PM 104,858,112 REDO01.LOG
08/23/2005 10:45 AM 262,152,192 SOILS_ADTBL1.DBF
08/23/2005 10:45 AM 262,152,192 SOILS_AINDX.DBF
08/23/2005 10:45 AM 262,152,192 SOILS_BINDX.DBF
08/23/2005 10:45 AM 262,152,192 SOILS_BTBL.DBF
08/23/2005 10:45 AM 262,152,192 SOILS_DINDX.DBF
08/23/2005 10:45 AM 262,152,192 SOILS_FINDX.DBF
08/23/2005 10:45 AM 440,410,112 SOILS_FTBL.DBF
08/23/2005 10:45 AM 262,152,192 SOILS_SINDX.DBF
08/23/2005 10:45 AM 262,152,192 SOILS_STBL.DBF
08/23/2005 10:45 AM 429,924,352 SYSTEM01.DBF
07/13/2005 08:40 AM 126,885,888 TEMP01.DBF
08/23/2005 10:45 AM 3,774,881,792 TENFT_LIDAR_BLK1.DBF
08/23/2005 10:45 AM 524,296,192 TENFT_LIDAR_DATA.DBF
08/23/2005 10:45 AM 10,493,952 TOOLS01.DBF
08/23/2005 10:45 AM 1,279,270,912 UNDOTBS01.DBF
08/23/2005 10:45 AM 26,222,592 USERS01.DBF
08/23/2005 10:45 AM 39,985,152 XDB01.DBF
45 File(s) 14,443,356,672 bytes
3 Dir(s) 672,838,311,936 bytes free
And this looks like it could be a problem as well....
C:\oracle\oradata\hydro>sq
SQL*Plus: Release 9.2.0.1.0 - Production on Wed Aug 24 20:41:58 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> connect SYS as SYSDBA
Enter password:
ERROR:
ORA-12560: TNS:protocol adapter error
SQL>
Directory of C:\oracle\oradata\hydro
08/24/2005 08:40 PM <DIR> .
08/24/2005 08:40 PM <DIR> ..
08/23/2005 02:45 PM <DIR> archive
08/23/2005 12:32 PM 2,121,728 CONTROL01.CTL
08/23/2005 12:32 PM 2,121,728 CONTROL02.CTL
08/23/2005 12:32 PM 2,121,728 CONTROL03.CTL
08/23/2005 10:45 AM 20,979,712 CWMLITE01.DBF
08/24/2005 08:40 PM 0 dir.txt
08/23/2005 10:45 AM 262,152,192 DOT_ADTBL1.DBF
08/23/2005 10:45 AM 262,152,192 DOT_AINDX1.DBF
08/23/2005 10:45 AM 262,152,192 DOT_BINDX.DBF
08/23/2005 10:45 AM 262,152,192 DOT_BTBL1.DBF
08/23/2005 10:45 AM 262,152,192 DOT_DINDX1.DBF
08/23/2005 10:45 AM 262,152,192 DOT_FINDX1.DBF
08/23/2005 10:45 AM 262,152,192 DOT_FTBL1.TBL
08/23/2005 10:45 AM 262,152,192 DOT_SINDX1.DBF
08/23/2005 10:45 AM 262,152,192 DOT_STBL1.DBF
08/23/2005 10:45 AM 20,979,712 DRSYS01.DBF
08/23/2005 10:45 AM 155,983,872 EXAMPLE01.DBF
08/23/2005 10:45 AM 26,222,592 INDX01.DBF
08/23/2005 10:45 AM 262,152,192 NHD_ADTBL.DBF
08/23/2005 10:45 AM 262,152,192 NHD_AINDX.DBF
08/23/2005 10:45 AM 262,152,192 NHD_BINDX.DBF
08/23/2005 10:45 AM 545,267,712 NHD_BTBL.DBF
08/23/2005 10:45 AM 262,152,192 NHD_DINDX.DBF
08/23/2005 10:45 AM 262,152,192 NHD_FINDX.DBF
08/23/2005 10:45 AM 597,696,512 NHD_FTBL.DBF
08/23/2005 10:45 AM 262,152,192 NHD_SINDX.DBF
08/23/2005 10:45 AM 262,152,192 NHD_STBL.DBF
08/23/2005 10:45 AM 20,979,712 ODM01.DBF
08/23/2005 03:12 PM 104,858,112 REDO01.LOG
08/23/2005 10:45 AM 262,152,192 SOILS_ADTBL1.DBF
08/23/2005 10:45 AM 262,152,192 SOILS_AINDX.DBF
08/23/2005 10:45 AM 262,152,192 SOILS_BINDX.DBF
08/23/2005 10:45 AM 262,152,192 SOILS_BTBL.DBF
08/23/2005 10:45 AM 262,152,192 SOILS_DINDX.DBF
08/23/2005 10:45 AM 262,152,192 SOILS_FINDX.DBF
08/23/2005 10:45 AM 440,410,112 SOILS_FTBL.DBF
08/23/2005 10:45 AM 262,152,192 SOILS_SINDX.DBF
08/23/2005 10:45 AM 262,152,192 SOILS_STBL.DBF
08/23/2005 10:45 AM 429,924,352 SYSTEM01.DBF
07/13/2005 08:40 AM 126,885,888 TEMP01.DBF
08/23/2005 10:45 AM 3,774,881,792 TENFT_LIDAR_BLK1.DBF
08/23/2005 10:45 AM 524,296,192 TENFT_LIDAR_DATA.DBF
08/23/2005 10:45 AM 10,493,952 TOOLS01.DBF
08/23/2005 10:45 AM 1,279,270,912 UNDOTBS01.DBF
08/23/2005 10:45 AM 26,222,592 USERS01.DBF
08/23/2005 10:45 AM 39,985,152 XDB01.DBF
45 File(s) 14,443,356,672 bytes
3 Dir(s) 672,838,311,936 bytes free
ASKER
More logs:
http://www4.ncsu.edu/~tpcolson/OracleOraHome92Agent.nohup.txt
http://www4.ncsu.edu/~tpcolson/sqlnet.txt
http://www4.ncsu.edu/~tpcolson/onrsd.txt
http://www4.ncsu.edu/~tpcolson/nmiconf.txt
http://www4.ncsu.edu/~tpcolson/dbsnmp.txt
http://www4.ncsu.edu/~tpcolson/agntsrvc.txt
http://www4.ncsu.edu/~tpcolson/listener.txt
http://www4.ncsu.edu/~tpcolson/OracleOraHome92Agent.nohup.txt
http://www4.ncsu.edu/~tpcolson/sqlnet.txt
http://www4.ncsu.edu/~tpcolson/onrsd.txt
http://www4.ncsu.edu/~tpcolson/nmiconf.txt
http://www4.ncsu.edu/~tpcolson/dbsnmp.txt
http://www4.ncsu.edu/~tpcolson/agntsrvc.txt
http://www4.ncsu.edu/~tpcolson/listener.txt
ASKER
Ok I made it through the tnserror issue...and here is the output I get. I just wanted to confirm what this means before I proceede with the other steps.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 254 104857600 1 NO INVALIDATED
32176128 18-AUG-05
2 1 252 104857600 1 YES INACTIVE
31619872 15-AUG-05
3 1 253 104857600 1 YES INACTIVE
31897856 17-AUG-05
SQL>
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 254 104857600 1 NO INVALIDATED
32176128 18-AUG-05
2 1 252 104857600 1 YES INACTIVE
31619872 15-AUG-05
3 1 253 104857600 1 YES INACTIVE
31897856 17-AUG-05
SQL>
Hi Tom,
What status is the database now, is it in OPEN, MOUNT or NOMOUNT mode ?
Your V$LOG entries show that the Group#2 was already archived and is not currently active which is good. However you have 3 log groups but I could not see either the REDO02.LOG or REDO03.LOG in the directory listing you posted before.
With the above input I think that you do not need to recover the database just recreate the redo logs but we will find that out. Can you also post the output from :
select * from v#logfile;
What status is the database now, is it in OPEN, MOUNT or NOMOUNT mode ?
Your V$LOG entries show that the Group#2 was already archived and is not currently active which is good. However you have 3 log groups but I could not see either the REDO02.LOG or REDO03.LOG in the directory listing you posted before.
With the above input I think that you do not need to recover the database just recreate the redo logs but we will find that out. Can you also post the output from :
select * from v#logfile;
Sorry it is
select * from v$logfile;
select * from v$logfile;
Another query to check before you go ahead, it should return 0 rows.
select * from v$datafile where status not in ('ONLINE','SYSTEM');
select * from v$datafile where status not in ('ONLINE','SYSTEM');
ASKER
redo02.log and redo03.log are definitely long gone; we ever tried very expensive "undelete" software.
SQL> select * from v#logfile;
select * from v#logfile
*
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only
It's in mount mode.
SQL> select * from v#logfile;
select * from v#logfile
*
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only
It's in mount mode.
ASKER
SQL> select * from v$logfile;
GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------
3 ONLINE
C:\ORACLE\ORADATA\HYDRO\RE
2 ONLINE
C:\ORACLE\ORADATA\HYDRO\RE
1 STALE ONLINE
C:\ORACLE\ORADATA\HYDRO\RE
WTF? 03 and 02 aren't there...but this says they are?
SQL> select * from v$datafile where status not in ('ONLINE','SYSTEM');
no rows selected
SQL>
GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------
3 ONLINE
C:\ORACLE\ORADATA\HYDRO\RE
2 ONLINE
C:\ORACLE\ORADATA\HYDRO\RE
1 STALE ONLINE
C:\ORACLE\ORADATA\HYDRO\RE
WTF? 03 and 02 aren't there...but this says they are?
SQL> select * from v$datafile where status not in ('ONLINE','SYSTEM');
no rows selected
SQL>
Your database thinks the files are there but Windows doesnt have them hence the errors.
Also what is the status of the database ? OPEN/MOUNT/NOMOUNT
you can check with :
select * from v$instance;
Looks like you just have to recreate the redo logs. I will post you the commands once you let me know the status.
Also what is the status of the database ? OPEN/MOUNT/NOMOUNT
you can check with :
select * from v$instance;
Looks like you just have to recreate the redo logs. I will post you the commands once you let me know the status.
ASKER
SQL> select * from v$instance;
INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
--------------------------
VERSION STARTUP_T STATUS PAR THREAD# ARCHIVE LOG_SWITCH_
----------------- --------- ------------ --- ---------- ------- -----------
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST
---------- --- ----------------- ------------------ ---------
1 hydro
HYDRODB
9.2.0.1.0 25-AUG-05 MOUNTED NO 1 STARTED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL
SQL>
INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
--------------------------
VERSION STARTUP_T STATUS PAR THREAD# ARCHIVE LOG_SWITCH_
----------------- --------- ------------ --- ---------- ------- -----------
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST
---------- --- ----------------- ------------------ ---------
1 hydro
HYDRODB
9.2.0.1.0 25-AUG-05 MOUNTED NO 1 STARTED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL
SQL>
So the database is in MOUNT state, perfect. You have to remain connected as SYS
SQL> alter database drop logfile group 2;
SQL> alter database add logfile group 2 ('C:\ORACLE\ORADATA\HYDRO\
SQL> alter database drop logfile group 3;
SQL> alter database add logfile group 3 ('C:\ORACLE\ORADATA\HYDRO\
SQL> select * from v$log; -- check the status of new ones will be UNUSED
SQL> alter database open; -- let me know if you get any errors at this point, past this you are golden
SQL> alter system switch logfile;
SQL> select * from v$log; -- check that the CURRENT group is changed
SQL> alter database drop logfile group 2;
SQL> alter database add logfile group 2 ('C:\ORACLE\ORADATA\HYDRO\
SQL> alter database drop logfile group 3;
SQL> alter database add logfile group 3 ('C:\ORACLE\ORADATA\HYDRO\
SQL> select * from v$log; -- check the status of new ones will be UNUSED
SQL> alter database open; -- let me know if you get any errors at this point, past this you are golden
SQL> alter system switch logfile;
SQL> select * from v$log; -- check that the CURRENT group is changed
ASKER
uh oh....
SQL> alter database add logfile group 2 ('C:\ORACLE\ORADATA\HYDRO\
alter database add logfile group 2 ('C:\ORACLE\ORADATA\HYDRO\
*
ERROR at line 1:
ORA-00301: error in adding log file 'C:\ORACLE\ORADATA\HYDRO\R
cannot be created
ORA-17610: file 'C:\ORACLE\ORADATA\HYDRO\R
specified
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> alter database add logfile group 2 ('C:\ORACLE\ORADATA\HYDRO\
alter database add logfile group 2 ('C:\ORACLE\ORADATA\HYDRO\
*
ERROR at line 1:
ORA-00301: error in adding log file 'C:\ORACLE\ORADATA\HYDRO\R
cannot be created
ORA-17610: file 'C:\ORACLE\ORADATA\HYDRO\R
specified
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
SQL> alter database add logfile group 2 ('C:\ORACLE\ORADATA\HYDRO\
ize 100M;
Database altered.
SQL> alter database drop logfile group 3;
Database altered.
SQL> alter database add logfile group 3 ('C:\ORACLE\ORADATA\HYDRO\
ize 100M;
Database altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 254 104857600 1 NO INVALIDATED
32176128 18-AUG-05
2 1 0 104857600 1 YES UNUSED
0
3 1 0 104857600 1 YES UNUSED
0
SQL> alter database open;
Database altered.
SQL> alter system switch logfile;
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 254 104857600 1 YES INACTIVE
32176128 18-AUG-05
2 1 255 104857600 1 YES ACTIVE
32437552 25-AUG-05
3 1 256 104857600 1 NO CURRENT
32439930 25-AUG-05
SQL>
ize 100M;
Database altered.
SQL> alter database drop logfile group 3;
Database altered.
SQL> alter database add logfile group 3 ('C:\ORACLE\ORADATA\HYDRO\
ize 100M;
Database altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 254 104857600 1 NO INVALIDATED
32176128 18-AUG-05
2 1 0 104857600 1 YES UNUSED
0
3 1 0 104857600 1 YES UNUSED
0
SQL> alter database open;
Database altered.
SQL> alter system switch logfile;
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 254 104857600 1 YES INACTIVE
32176128 18-AUG-05
2 1 255 104857600 1 YES ACTIVE
32437552 25-AUG-05
3 1 256 104857600 1 NO CURRENT
32439930 25-AUG-05
SQL>
Congrats !
You should be all set now. Remember to backup the database after this.
SDutta
You should be all set now. Remember to backup the database after this.
SDutta
ASKER
sweet~ It all worked. I'm naming my kids after you. I'm now sucking out all my data and taking the server out to a field for target practice.
LOL, Hope you take the steel curtains off before target practice ;)
Are you still on for "The author of a successfull solution will get a nice thank you letter/letter of rec on NCSU letterhead" ? I can send you an email with the details.
Are you still on for "The author of a successfull solution will get a nice thank you letter/letter of rec on NCSU letterhead" ? I can send you an email with the details.
ASKER
tom_colson@ncsu.edu.
You got a company name?
You got a company name?
First open up a SQLPLUS session with
sqlplus /nolog
SQL> connect SYS as SYSDBA
SQL> shutdown immediate; -- just in case !
SQL> startup nomount pfile=pfile_name; -- use appropriate file name
SQL> alter database mount;
SQL> select * from v$log;
Check the STATUS for Group#2, Thread#1 from above output, it will either be CURRENT or INACTIVE.
If it is INACTIVE then :
Do you have only 2 Groups ? If Yes - Create a new log Group#3 (You must have atleast 2 groups at any time)
SQL> alter database drop logfile group 2;
Recreate log Group#2
SQL> alter database add logfile group 2 ('C:\ORACLE\ORADATA\HYDRO\
SQL> alter database open;
This should open up your database, skip the next steps.
If the Group#2 was CURRENT then you need to do fake recovery :
SQL> recover database until cancel;
(cancel immediately)
SQL> alter database open resetlogs;
This will create the missing file and open up the database.
Best of luck!