Trivia question - file date.
Joel Garry
If you don't use read-only tablespaces, and you bounce your database
weekly, can you have a legitimate Oracle database file dated 6 months
old?
(assuming 8.1.7 on unix, normal operations with no backup/restore in
progress, no funny business with "touch," Oracle handling the files,
not including stuff like installed files and logs.)
.
.
.
.
.
.
.
.
Nearly shot myself in the foot with this one. About six months ago,
someone added a second temporary sort file. Every once in a while,
especially on a system I haven't been on in a while, I "clean out the
garage," including looking for unused large files with a command like
(depending on your ls and your data file locations):
ls -Rl /oracle |sort -nrk5|more
which normally pops up the data files first, then forgotten exports
and load files and undropped data files and such. Since Oracle often
touches the data files (except read-only), thereby updating the file
system date, old dates stand out like a sore thumb.
So I see a file among the data files with a 6 month old date. select
* from v$dbfile; not there. Oh wait, select tablespace_name,
contents from dba_tablespaces; almost missed the locally managed temp
file. Well, the temp file has a recent date, so the old one isn't
used, right? Oh wait, select * from dba_temp_files;
Yow! Almost missed that! Seems having _2_ 2G locally managed temp
files has allowed the second one never to be used, Oracle never
updating the date. But just waiting for someone to make
assumptions...
Other answers welcomed.
jg
--
@home is bogus
http://www.garry.to
Daniel Morgan
If you are talking about the operating system date ... you can have one
ten years old. The file's date-time is only touched once at the moment of
its initial creation.
Daniel Morgan
Roman Mirzaitov
"Joel Garry" <joel-...@home.com> wrote in message
news:91884734.02112...@posting.google.com...
>
> If you don't use read-only tablespaces, and you bounce your database
> weekly, can you have a legitimate Oracle database file dated 6 months
> old?
my guess is no. Probably this is the old unused file.
> (assuming 8.1.7 on unix, normal operations with no backup/restore in
> progress, no funny business with "touch," Oracle handling the files,
> not including stuff like installed files and logs.)
>
>
> So I see a file among the data files with a 6 month old date. select
> * from v$dbfile; not there. Oh wait, select tablespace_name,
> contents from dba_tablespaces; almost missed the locally managed temp
> file. Well, the temp file has a recent date, so the old one isn't
> used, right? Oh wait, select * from dba_temp_files;
>
did you try
select file_name from dba_data_files (describes database files)
and
select * from v$datafile? (datafile information from the control file)
What do they say? I think if there are no records about your file you can rm
it easily.
Regards,
--
Roman Mirzaitov
Brainbench MVP for Oracle Administration
www.brainbench.com
Norman Dunbar
creation date/time.
As I don't use read only tablespaces, I always check I've got the right
file to delete when I drop a tablespace by forcing a checkpoint, and
checking the datafile dates - the one that I thought it was shouldn't
have changed but the others should have. I then know that it is safe to
remove the data file.
Measure twice, cut once - as Nuno would no doubt agree.
Why you have a file with an old date, and you don't have readonly
tablespaces - I would suggest that the file is no longer part of the
database.
Cheers,
Norman.
-------------------------------------
Norman Dunbar
Database/Unix administrator
Lynx Financial Systems Ltd.
mailto:Norman...@LFS.co.uk
Tel: 0113 289 6265
Fax: 0113 289 3146
URL: http://www.Lynx-FS.com
-------------------------------------
Howard J. Rogers
Regards
HJR
"Joel Garry" <joel-...@home.com> wrote in message
news:91884734.02112...@posting.google.com...
Joel Garry
> Yes. You put it into hot backup mode and leave it there.
I guess I wasn't clear enough that was what I meant by no
backup/restore in progress".
jg
--
@home is bogus.
Joel Garry
> Assuming Unix, then ls -l shows the last modification date/time, not the
> creation date/time.
>
> As I don't use read only tablespaces, I always check I've got the right
> file to delete when I drop a tablespace by forcing a checkpoint, and
> checking the datafile dates - the one that I thought it was shouldn't
> have changed but the others should have. I then know that it is safe to
> remove the data file.
So you are saying, a checkpoint has never happened with weekly
bouncing?
WOW!
>
> Measure twice, cut once - as Nuno would no doubt agree.
That was why I posted this trivia question! Instance name obscured:
SQL> select * from dba_temp_files;
FILE_NAME
--------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
---------- ------------------------------ ---------- ----------
---------
RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES
USER_BLOCKS
------------ --- ---------- ---------- ------------ ----------
-----------
/oracle/oradata/****/temp_local01.dbf
1 TEMP_LOCAL 2097152000 256000
AVAILABLE
1 NO 0 0 0 2086666240
254720
/oracle/oradata/****/temp_local02.dbf
2 TEMP_LOCAL 2097152000 256000
AVAILABLE
2 NO 0 0 0 2086666240
254720
FILE_NAME
--------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
---------- ------------------------------ ---------- ----------
---------
RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES
USER_BLOCKS
------------ --- ---------- ---------- ------------ ----------
-----------
SQL> !ll /oracle/oradata/****/temp_local02.dbf
-rw-rw---- 1 oracle sys 2097160192 May 28 2002
/oracle/oradata/****/temp_local02.dbf
SQL> alter system checkpoint;
System altered.
SQL> !ll /oracle/oradata/****/temp_local*.dbf
-rw-rw---- 1 oracle sys 2097160192 Nov 26 14:02
/oracle/oradata/****/temp_local01.dbf
-rw-rw---- 1 oracle sys 2097160192 May 28 2002
/oracle/oradata/****/temp_local02.dbf
>
>
> Why you have a file with an old date, and you don't have readonly
> tablespaces - I would suggest that the file is no longer part of the
> database.
I would suggest you are making the same mistake I almost made. :-O
Joel Garry
> Hi Joel,
>
>
> "Joel Garry" <joel-...@home.com> wrote in message
> news:91884734.02112...@posting.google.com...
> >
> > If you don't use read-only tablespaces, and you bounce your database
> > weekly, can you have a legitimate Oracle database file dated 6 months
> > old?
>
> my guess is no. Probably this is the old unused file.
>
> > (assuming 8.1.7 on unix, normal operations with no backup/restore in
> > progress, no funny business with "touch," Oracle handling the files,
> > not including stuff like installed files and logs.)
> >
> >
> > So I see a file among the data files with a 6 month old date. select
> > * from v$dbfile; not there. Oh wait, select tablespace_name,
> > contents from dba_tablespaces; almost missed the locally managed temp
> > file. Well, the temp file has a recent date, so the old one isn't
> > used, right? Oh wait, select * from dba_temp_files;
> >
>
> did you try
> select file_name from dba_data_files (describes database files)
> and
> select * from v$datafile? (datafile information from the control file)
>
Well, yes I did, until I realized v$dbfile was just as reasonable.
They both don't deal with temp files. That's what dba_temp_files is
for.
> What do they say? I think if there are no records about your file you can rm
> it easily.
>
> Regards,
Well, I'm not about to test that on my production database! :-)
jg
--
@home is bogus
First SBC/Pac Bell digs up a bunch of people's yards to test fiber.
Then they cancel test and dig it back up because they want to push
copper DSL. Now they complain that they can't compete with cable
companies' fiber. Sheesh.
Norman Dunbar
It does indeed look like I made the same mistake you almost made :o)
I wasn't aware that temp files were involved - I keep my temp files
separate from the data files, so when I checkpoint after dropping a
tablespace, I only look at the data files. I'd never noticed that the
temp files don't change their dates on a checkpoint - I'll have to make
a note of that.
Richard Foote
Remember that the purpose of a checkpoint is to have a point from which to
commence instance recoveries. However, if you had an instance failure, what
precisely within a temp file would be worth recovering. Not a lot. Therefore
if Oracle is not going to concern itself with recovering a temp file, then
there's really no point in checkpointing it either.
Now if only all files in an Oracle database were temp files, the Backup and
Recovery manual would be so much easier to follow ;)
Cheers
Richard
"Norman Dunbar" <Norman...@lfs.co.uk> wrote in message
news:E2F6A70FE45242488C86...@lnewton.leeds.lfs.co.uk...
Norman Dunbar
you had to get one in about the cricket didn't you :o)
Niall will have something to say on the matter - and he'll probably be
very scathing towards a certain Mr Bowie as well.
Yep the checkpointing of temp files isn't much good - I eventually
figured that one out. I must be getting old.
Cheers,
Norman.
-------------------------------------
Norman Dunbar
Database/Unix administrator
Lynx Financial Systems Ltd.
mailto:Norman...@LFS.co.uk
Tel: 0113 289 6265
Fax: 0113 289 3146
URL: http://www.Lynx-FS.com
-------------------------------------
-----Original Message-----
From: Richard Foote [mailto:richar...@bigpond.com]
Posted At: Wednesday, November 27, 2002 11:48 AM
Posted To: server
Conversation: Trivia question - file date.
Subject: Re: Trivia question - file date.
Joel Garry
>
> you had to get one in about the cricket didn't you :o)
> Niall will have something to say on the matter - and he'll probably be
> very scathing towards a certain Mr Bowie as well.
>
> Yep the checkpointing of temp files isn't much good - I eventually
> figured that one out. I must be getting old.
The real issue here is explained in metalink NOTE: 160426.1. When
sorting, Oracle only actually goes to the sort file if it needs to.
So in the situation of 2 large locally managed temp files, the second
file may never be used. So the checkpointing is irrelevant, and while
it is true you may not care about recovering this second file, Oracle
may have something to say about it after you rm it without dropping
it, and then happen to have a very large sort like the one that
originally prompted you to add it (mayhaps, a year-end thingee...).
So now I'm wondering if Oracle checks for this situation on startup,
and I'm guessing it doesn't, since all the transportable ts and such
rely on plugins... ORA-25153 doesn't quite seem to cover it... sounds
like an easy bomb to overlook.
I noticed also in that note they seem to be very clear about the
difference between an tempfile and a datafile, until the example about
dropping a temporary datafile in 9i... :-)
SQL> alter database tempfile '/oradata/V901/temp_temp01.dbf'
drop including datafiles;
Database altered.
jg
--
@home is bogus.
I hate it when crickets make all that noise on a hot night.
Niall Litchfield
news:E2F6A70FE45242488C86...@lnewton.leeds.lfs.co.uk...
>
> you had to get one in about the cricket didn't you :o)
> Niall will have something to say on the matter - and he'll probably be
> very scathing towards a certain Mr Bowie as well.
lil ol me. as if I would. picture of innocence me.
--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
******************************************
Paul Brewer
news:E2F6A70FE45242488C86...@lnewton.leeds.lfs.co.uk...
>
> tablespaces - I would suggest that the file is no longer part of the
> database.
>
> Cheers,
> Norman.
Concur.
Regards,
Paul
Besides, it's easy to check with dba_data_files, v$logfile and so forth.
Paul Brewer
news:Sa1F9.84803$g9.2...@newsfeeds.bigpond.com...
> Hi Norman, Joel and all cricket loving DBAs ;)
>
important thing now?
My (then, not now, thankfully) boss's secretary put it rather well in 1990,
I thought: "Maybe all the Husseins (Hussains?) are crazy".
Regards,
Paul
Richard Foote
"Paul Brewer" <pa...@paul.brewers.org.uk> wrote in message
news:3de55...@mk-nntp-1.news.uk.worldonline.com...
Hi Paul,
Can you imagine how annoying it must be to have a packaged set of tickets
that gave you entry to the 5th day of each Test Match.
Hee Hee
Richard
Paul Brewer
But I'm not rising to this baiting any more.
I give in.
Regards,
Demoralised DBA