topic Re: Lost all datafiles in Database Server! in Operating System - HP-UX

Lost all datafiles in Database Server!

Tung Dang — Fri, 07 Dec 2007 04:32:22 GMT

Dear All,
I have a big problem that all of our datafiles, archive files and backup directory was lost. The struture of our database directories is as following:
1)/PROD/oradata: store all datafiles
2)/PROD/oraindex: store all indexfiles
3)/PROD/system: store system tablespace datafiles
4)/PROD/archive: store archive log
5)/PROD/backup: store backup data
The problem is all files of oraindex, system, archive, backup directories were lost. The most of datafiles inside oradata still remain, lost some files and directory inside this directory.
When I checked the Oracle Alert logfile, the duration for the error is very short, it just happened from 15:13:24 to 15:13:27 (just 3 seconds) while all of losted file is around 1TB.
I worry that error is caused by losting matadata because nobody can remove 1TB with just 3 seconds.
Could you please help us to find out the reason
of the error and recomend us how to prevent that problem from happening again.
Thank you very much.
Tung

Re: Lost all datafiles in Database Server!

Yogeeraj_1 — Fri, 07 Dec 2007 04:36:05 GMT

hi,

Can you confirm that the database is down?

Are there any errors at the OS level?
Are there any errors in your alert_.log?

revert

kind regards
yogeeraj

Re: Lost all datafiles in Database Server!

Kapil Jha — Fri, 07 Dec 2007 04:52:13 GMT

>>>it just happened from 15:13:24 to 15:13:27 (just 3 seconds)

Can you please tell me what is there is alert.log file in these 3 seconds.

And i suppose removing files does not take much time (well i have not removed such a huge files).
BR,
Kapil

Re: Lost all datafiles in Database Server!

Anshumali — Fri, 07 Dec 2007 05:20:48 GMT

Did someone try doing lvreduce on these LV's... it may be outcome of that as well.... if thats the case, you may still loose 3 TB in 3 Seconds.....
...Blind shot....

Re: Lost all datafiles in Database Server!

Tung Dang — Fri, 07 Dec 2007 09:03:24 GMT

Dear Sirs,
Thank you very much for your help.
The database down right after files were lost.
There is no errors in syslog.log, so that we can say nobody use lvreduce command.
Please check the attachment for the syslog.log and alert log file for more details.
Thanks and Regards,
Tung

Re: Lost all datafiles in Database Server!

Kapil Jha — Fri, 07 Dec 2007 09:26:10 GMT

Can you please send it again as text file or zip file....rar is not working :(
BR,
Kapil

Re: Lost all datafiles in Database Server!

Tung Dang — Fri, 07 Dec 2007 09:38:11 GMT

Dear Kapil,
Sorry. I attach again in .zip file. check it please.

rgds,
Tung

Re: Lost all datafiles in Database Server!

Kapil Jha — Fri, 07 Dec 2007 09:48:12 GMT

m sorry sir....its not coming.Can you mail me on kapiljha@gmail.com

BR,
Kapil

Re: Lost all datafiles in Database Server!

Tung Dang — Fri, 07 Dec 2007 11:04:30 GMT

Dear Mr.Kapil,
I try to attach this file again and send you also. Pls check it.

Tks,
Tung

Re: Lost all datafiles in Database Server!

F Verschuren — Fri, 07 Dec 2007 11:16:29 GMT

hi, there are no vmunix messages that normaly will pick out hartware messages, can you please post you syslog.conf to see it they are nomaly written to the syslog.log,
and the output of dmesg?

Re: Lost all datafiles in Database Server!

Tung Dang — Fri, 07 Dec 2007 11:28:07 GMT

Dear Mr.Verschuren,
Thanks for your help.
The attachment is the syslog.conf and dmesg.log. Pls check it.

Thanks and Best Regards,
Tung

Re: Lost all datafiles in Database Server!

Tung Dang — Fri, 07 Dec 2007 11:29:49 GMT

Dear Sirs,
This is the syslog.conf.
check the attachment pls.

Tung

Re: Lost all datafiles in Database Server!

F Verschuren — Fri, 07 Dec 2007 11:54:45 GMT

it looks like a lost of luns, please post a :
ioscan -fnC disk
ioscan -fnC fc
and vgdisplay -v

to check all the disks that are configured are still availeble.

Re: Lost all datafiles in Database Server!

Yogeeraj_1 — Fri, 07 Dec 2007 12:11:35 GMT

hi Tung,

based on the messages in your alert.log, it seems that the /CCBS2DB/ mount point is missing.

where is this mounted?

do a cat /etc/fstab to get more information.

revert
kind regards
yogeeraj

Re: Lost all datafiles in Database Server!

Tung Dang — Mon, 10 Dec 2007 02:50:45 GMT

Dear Sirs,
Thank you for your help.
We are currently using SAN environment with EVA8000, and configuring these volumn group in cluster. The briefly configuration as following:
/dev/vd09/lvol1 /CCBS2DB/backup
/dev/vd01/lvol1 /CCBS2DB/oradata1
/dev/vd01/lvol2 /CCBS2DB/oraindex1
/dev/vd01/lvol3 /CCBS2DB/system
/dev/vd03/lvol1 /CCBS2DB/archive
At the time the problem happened, I used bdf I saw all of these mountpoint still remain, but the "used" of /CCBS2DB/backup, /CCBS2DB/oraindex1, /CCBS2DB/system and /CCBS2DB/archive were 0%; and the "used" of /CCBS2DB/oradata1 was seemly normal. It means that all the directories were still remain, just lost the files and directories inside it.
Now I recovered the database from tape already, and the system run normally. So I think that ioscan and vgdisplay this time do not help us to find out the cause of the problem. If you still want to look it, let me know.
I want to know the root cause of the problem in order to prevent it happen again.
Thank you very much.
Tung

Re: Lost all datafiles in Database Server!

Eric Antunes — Mon, 10 Dec 2007 09:10:15 GMT

Hi,

Just one though about discovering what happened:

Login as root and do some "Esc+K" to check the root shell history.

Best Regards,

Eric Antunes

Re: Lost all datafiles in Database Server!

Anshumali — Mon, 10 Dec 2007 09:13:32 GMT

I doubt it will work. I hope root command's history isn't logged by default.

Re: Lost all datafiles in Database Server!

Eric Antunes — Mon, 10 Dec 2007 09:40:06 GMT

If you can't "Esc+k", you can always do as root:

vi $HOME/.sh_history

vi

Re: Lost all datafiles in Database Server!

Tung Dang — Tue, 11 Dec 2007 02:09:17 GMT

Dear Eric,
I checked .sh_history already, and there was no any "rm" or "lvreduce" command related with this problem.

Rgds,
Tung

Re: Lost all datafiles in Database Server!

Kapil Jha — Tue, 11 Dec 2007 05:40:18 GMT

Dear Tung,
sorry for late reply....
There is nothing in alert files they direct up to some unix error.

Error: ORA 1171
Text: datafile is going offline due to error advancing checkpoint
-------------------------------------------------------------------------------
Cause: The checkpoint in the file header could not be advanced.
See accompanying messages for the reason.
The datafile will be taken offline the same as for a write error of a
data block.
Action: See accompanying messages for details.
Restore access to the file, do media recovery, and bring it back
online.

ORA-01115: IO error reading block from file string (block # string)
Cause: Device on which the file resides is probably offline
Action: Restore access to the device

in syslog file some FTP activity was there at the time of crash
Dec 5 15:13:24 COVIS2 ftpd[27915]: USER oss
Dec 5 15:13:24 COVIS2 ftpd[27915]: PASS password
Dec 5 15:13:23 COVIS2 ftpd[29657]: PWD
Dec 5 15:13:24 COVIS2 above message repeats 29 times
Dec 5 15:13:24 COVIS2 ftpd[27915]: FTP LOGIN FROM 10.1.103.31 [10.1.103.31], oss
Dec 5 15:13:24 COVIS2 ftpd[27915]: TYPE ASCII
Dec 5 15:13:24 COVIS2 ftpd[27915]: CWD /ccbs2/stats_raw/
Dec 5 15:13:24 COVIS2 ftpd[27915]: PORT
Dec 5 15:13:24 COVIS2 ftpd[27915]: NLST
Dec 5 15:13:24 COVIS2 ftpd[27915]: QUIT
Dec 5 15:13:24 COVIS2 ftpd[27915]: FTP session closed
Dec 5 15:13:24 COVIS2 ftpd[29657]: PWD
Dec 5 15:13:54 COVIS2 ftpd[28809]: cannot stat private access file /etc/ftpd/ftpgroups: No such file or directory

From oracle error I suppose there was some hardware error but it is strange that only files were deleted not directories.
I would suggest to open a case with HP that would be good because its not a small issue.

BR,
Kapil