Pardon me if I am butting in but I think because the information in the control file does not match up to sys.file$ which ties file numbers to tablespaces. There apparently is a file# in file$ that is not in the control file. Was an old or backup control file used to start the db by the other DBA/Developer? -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Orr, Steve Sent: Wednesday, May 26, 2004 11:58 AM To: oracle-l@xxxxxxxxxxxxx Subject: RE: Undocumented Instance/Media Recover Feature? The curiousity is that the file didn't exist in the O/S, Oracle created it without telling me, and it created and placed a database object into that file, again, without telling me. I just stumbled across this but I'd like to KNOW when this happens. V$DATAFILE and V$TABLESPACE get information from the control file so how could they be out of sync?=20 -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Tim Johnston Sent: Wednesday, May 26, 2004 9:46 AM To: oracle-l@xxxxxxxxxxxxx Subject: Re: Undocumented Instance/Media Recover Feature? Hi Steve... I believe you are seeing a mismatch between the controlfile and the=20 data dictionary... Basically, you started up your database and the data dictionary says you have tablespace "blah" associated with file id "x"=20 but the controlfile doesn't have a file for that tablespace... So, it=20 places a dummy placeholder file in it's place... You can simulate this=20 but doing the following: $ sqlplus "/ as sysdba" SQL*Plus: Release 9.2.0.3.0 - Production on Wed May 26 11:37:32 2004 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to an idle instance. SQL> Create Tablespace Tim Datafile '/export/home/oradata/PV429/tim.dbf' size 10M; Tablespace created. SQL> alter database backup controlfile to trace; Database altered. SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> Then edit the create control file command and removed the line for the=20 newly created datafile... $ sqlplus "/ as sysdba" SQL*Plus: Release 9.2.0.3.0 - Production on Wed May 26 11:40:21 2004 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to an idle instance. SQL> startup nomount ORACLE instance started. Total System Global Area 252793548 bytes Fixed Size 455372 bytes Variable Size 167772160 bytes Database Buffers 83886080 bytes Redo Buffers 679936 bytes SQL> @cre_control Control file created. SQL> alter database open; Database altered. SQL> set pagesize 1000 =20 SQL> select * from dba_data_files where tablespace_name =3D 'TIM'; FILE_NAME ------------------------------------------------------------------------ -------- FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS ---------- ------------------------------ ---------- ---------- --------- RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ------------ --- ---------- ---------- ------------ ---------- ----------- /opt/oracle/product/9.2.0/dbs/MISSING00035 35 TIM AVAILABLE 35 SQL> Tim Orr, Steve wrote: >We have this QA database which I can mostly ignore but Mr. QA dude found >a bug caused by the compatible init.ora parameter not being properly >set. Since they kind of admin their own database and know the timing of >their database availability needs for QA testing, I gave QA dude >instructions on recycling the database... >"SQL> shutdown abort" and "SQL> startup"=3D20 > >But the database didn't come back up due to a shared memory error so I >figured I'd have to fix things with ipcs/ipcrm. But before getting into >that I just tried "SQL> startup" myself and behold, everything started >up just fine with no warning messages or anything. Hmmm... That's >curious... I guess the oracle just likes me better. I was in >$ORACLE_HOME/dbs verifying the existence of the "lk$ORACLE_SID" file >when I saw something curious... A 100MB file named "MISSING00042" which >had the same database startup timestamp as the lk$ORACLE_SID file and >wondered what it was and where it came from. So I queried dba_data_files >and the data file with file_id 42 has the path of >$ORACLE_HOME/dbs/MISSING00042. Whoa!!! That directory path and 100MB are >the default values when using Oracle-managed files and 42 is the answer >to all things! But we don't use Oracle-managed files. Curious but how >could the oracle create this datafile automagically for me and not even >tell me? Where's it going to get the data? Then I look at dba_segments >and see that there's only one object in that tablespace/datafile, an >index which could be rebuilt from the data in another tablespace the >table is in. Does this mean that the oracle couldn't find the datafile >but created it for me automagically just because it could and because >the only object was an index?=3D20 > >Has anybody seen this behavior before? Is there any documentation on it? >Running Oracle 9.2.0.4 on Linux. > > >Steve Orr >Curious in Bozeman, Montana >---------------------------------------------------------------- >Please see the official ORACLE-L FAQ: http://www.orafaq.com >---------------------------------------------------------------- >To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx >put 'unsubscribe' in the subject line. >-- >Archives are at //www.freelists.org/archives/oracle-l/ >FAQ is at //www.freelists.org/help/fom-serve/cache/1.html >----------------------------------------------------------------- > =20 > --=20 Regards, Tim Johnston Tel: 978-322-4226 Fax: 978-322-4100 ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------