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
=========================
