Re: REDO LOG Concepts

  • From: "Stefan Knecht" <knecht.stefan@xxxxxxxxx>
  • To: "Frits Hoogland" <frits.hoogland@xxxxxxxxx>
  • Date: Thu, 17 May 2007 12:43:50 +0200

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

=========================

Other related posts: