Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Getachew.

Asked: October 08, 2005 - 9:24 pm UTC

Last updated: October 09, 2005 - 3:51 pm UTC

Version: 9.2.0.6

Viewed 1000+ times

You Asked

Tom,
Thank you for taking my question:

My question is regarding "alter system checkpoint" after removing a
data file for backup and recovery testing. The steps I did are:

I have an online backup of all files:
Init.ora paramater FAST_START_MTTR_TARGET is set to 20 minutes.

1- create table abc (f1 number(10))
tablespace my_test_ts;
2) insert some data into it:
begin
for i in 1..1000 loop
insert into abc values(i);
end loop
commit;
end;
/

3) I recycled through all my redo log groups:
alter system switch logfile;
4) From the operating system, I removed the data file for the table
5) I forced a check point, expecting oracle will tell me
that the data file doesn't exist:
alter system check point;
6) I inserted more data into the table (Same as in step 2)

I kept inserting data but, still without failure.

Why doesn't forcing a check point trigger that the data file doesn't exist?
How do I force it so that the error gets generated immediately?

The only way I forces it was during "alter tablespace .." or during a shutdown.

I would appreciate your help or input in this.

Thank you and, your new book is excellent!!

and Tom said...

because when you erase a file in unix - it simply "unlinks" it. Any process that has it open - STILL HAS IT OPEN.

DBWR had the datafile open. You "erased" the file (which simply removed the directory entry in effect, everyone that has it open, still does). You then wrote to that file by checkpointing. Anything that had the file open would still have it open.

Until that process that had it open closed it, and tried to open it again would it discover "oh, it isn't there anymore..."

the tool "lsof" (google for it) can be useful to see who still has files open.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Forcing a check point

Getachew Geressu, October 09, 2005 - 12:27 pm UTC

Thanks again Tom,
During a checkpoint, doesn't DBWR flush the dirty blocks to the data file? If the file is "erased", where
does it write the blocks to?
In this case, what is the use of "alter system checkpoint"?

I will check the tool "lsof".
Thanks.

Tom Kyte
October 09, 2005 - 3:51 pm UTC

in unix, if you have a file open, and someone else erases it (really - they just "unlink it", they do not "erase it"), you STILL have the file open.

The side effect - when the LAST PERSON having the file open closes it - it "really" goes away - until everyone closes it - the file still exists in its entirety - only no new processes can open it (because the direct entry is gone). Unless and until the last process closes it - it is still there, still consumes disk.



More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library