Interesting technique Frits :) This could technically even work for Oracle files. I'll have to test this :-) Stefan On 5/17/07, Frits Hoogland <frits.hoogland@xxxxxxxxx> wrote:
A little test on linux reveals a method to save deleted files with open file descriptors on it: frits@bedrock:~$ mkdir t frits@bedrock:~$ cd t frits@bedrock:~/t$ echo "hi" > tt frits@bedrock:~/t$ tail -f tt & [1] 703 frits@bedrock:~/t$ hi frits@bedrock:~/t$ ls -ls /proc/703/fd/ total 4 1 lrwx------ 1 frits frits 64 2007-05-17 12:30 0 -> /dev/pts/1 1 lrwx------ 1 frits frits 64 2007-05-17 12:30 1 -> /dev/pts/1 1 lrwx------ 1 frits frits 64 2007-05-17 12:30 2 -> /dev/pts/1 1 lr-x------ 1 frits frits 64 2007-05-17 12:30 3 -> /home/frits/t/tt frits@bedrock:~/t$ cat /proc/703/fd/3 hi frits@bedrock:~/t$ rm tt frits@bedrock:~/t$ ls -ls /proc/703/fd/ total 4 1 lrwx------ 1 frits frits 64 2007-05-17 12:30 0 -> /dev/pts/1 1 lrwx------ 1 frits frits 64 2007-05-17 12:30 1 -> /dev/pts/1 1 lrwx------ 1 frits frits 64 2007-05-17 12:30 2 -> /dev/pts/1 1 lr-x------ 1 frits frits 64 2007-05-17 12:30 3 -> /home/frits/t/tt (deleted) frits@bedrock:~/t$ cat /proc/703/fd/3 hi Not sure if this behaves the same on solaris. frits On 5/17/07, DBA Deepak <oracle.tutorials@xxxxxxxxx> wrote: > > Can we use UTL_FILE to copy the file as seen by oracle itself to a > separate location? Am suggesting the use of something like UTL_FILE because > UTL_FILE will be running from within Oracle's environment and Oracle can > still see the file. > > Deepak > > > On 5/17/07, Stefan Knecht <knecht.stefan@xxxxxxxxx > wrote: > > > > I've run into this situation before as well. It happens, people > > accidentally delete files. > > > > What would be interesting to research, is if you still can somehow > > recover those files. As they're still there, and locked by the process > > accessing them (in your case LGWR), there might be a way to "steal" the > > filehandle from that process and just write the file to another location > > before it's being closed. Though you would have to intercept the syscall > > closing the file. > > > > Anyone ever heard of any research on this ? > > > > Stefan > > > > On 5/17/07, DBA Deepak <oracle.tutorials@xxxxxxxxx > wrote: > > > > > > Hi Alexander, > > > > > > I do not think UNIX removes the directory entry immediately. > > > Otherwise how would it access the REDO logs by their paths. > > > > > > What might be happening is UNIX is marking it as deleted (may be > > > using a flag or so). Please do correct me if am wrong. > > > > > > Deepak > > > > > > > > > On 5/17/07, Alexander Fatkulin <afatkulin@xxxxxxxxx > wrote: > > > > > > > > Deepak, > > > > > > > > As others have already pointed out - you actually didn't delete > > > > anything. You just unlinked a directory entry in a file system. > > > > File > > > > is still "there" and is open by the LGWR. > > > > > > > > Shutdown immediate is a clean shutdown (no instance recovery is > > > > required after it). > > > > > > > > On 5/17/07, DBA Deepak < oracle.tutorials@xxxxxxxxx> wrote: > > > > > Hi All, > > > > > > > > > > As we know when we issue a COMMIT the log buffer content is > > > > flushed to the > > > > > online redo log file before the "Commit complete" status is > > > > returned to the > > > > > user. Please correct me if I am wrong. > > > > > > > > > > I have the following scenario... > > > > > > > > > > SQL> select member from v$logfile; > > > > > > > > > > MEMBER > > > > > > > > > -------------------------------------------------------------------------------- > > > > > > > > > /u04/oradata/BIDWDB01/redo03.log > > > > > /u04/oradata/BIDWDB01/redo02.log > > > > > /u04/oradata/BIDWDB01/redo01.log > > > > > > > > > > SQL> !rm /u04/oradata/BIDWDB01/redo01.log > > > > > > > > > > SQL> !rm /u04/oradata/BIDWDB01/redo02.log > > > > > > > > > > SQL> !rm /u04/oradata/BIDWDB01/redo03.log > > > > > > > > > > > > > > > SQL> connect scott/oracle > > > > > Connected. > > > > > SQL> update emp set ename=ename; > > > > > > > > > > 14 rows updated. > > > > > > > > > > SQL> commit; > > > > > > > > > > Commit complete. > > > > > > > > > > SQL> commit; > > > > > > > > > > Commit complete. > > > > > > > > > > SQL> conn / as sysdba > > > > > Connected. > > > > > SQL> shutdown immediate > > > > > Database closed. > > > > > Database dismounted. > > > > > ORACLE instance shut down. > > > > > > > > > > > > > > > SQL> startup > > > > > ORACLE instance started. > > > > > > > > > > Total System Global Area 4479516672 bytes > > > > > Fixed Size 1984504 bytes > > > > > Variable Size 905975816 bytes > > > > > Database Buffers 3556769792 bytes > > > > > Redo Buffers 14786560 bytes > > > > > Database mounted. > > > > > ORA-00313: open failed for members of log group 1 of thread 1 > > > > > ORA-00312: online log 1 thread 1: > > > > > '/u04/oradata/BIDWDB01/redo01.log' > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > ----------------------- > > > > > > > > > > My question is even after deleting all the online redo log files > > > > how did > > > > > Oracle returned the Commit complete status to the user? What > > > > happened to the > > > > > log buffer data flush? > > > > > > > > > > I am using 10.2.0.2 on Solaris 9 (64-bit) > > > > > > > > > > > > > > > Regards, > > > > > > > > > > Deepak > > > > > > > > > > > > -- > > > > Alexander Fatkulin > > > > > > > > > > > > > > > > -- > > > Regards, > > > > > > Deepak > > > Oracle DBA > > > > > > > > > > -- > > ========================= > > > > Stefan P Knecht > > Consultant > > Infrastructure Managed Services > > > > Trivadis AG > > Europa-Strasse 5 > > CH-8152 Glattbrugg > > > > Phone +41-44-808 70 20 > > Fax +41-808 70 12 > > Mobile +41-79-571 36 27 > > stefan.knecht@xxxxxxxxxxxx > > http://www.trivadis.com > > > > ========================= > > > > > -- > Regards, > > Deepak > Oracle DBA
-- ========================= Stefan P Knecht Consultant Infrastructure Managed Services Trivadis AG Europa-Strasse 5 CH-8152 Glattbrugg Phone +41-44-808 70 20 Fax +41-808 70 12 Mobile +41-79-571 36 27 stefan.knecht@xxxxxxxxxxxx http://www.trivadis.com =========================