Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Undocumented Instance/Media Recover Feature?

RE: Undocumented Instance/Media Recover Feature?

From: Powell, Mark D <mark.powell_at_eds.com>
Date: Wed, 26 May 2004 12:17:11 -0400
Message-ID: <564DE4477544D411AD2C00508BDF0B6A1CE0AB5A@usahm018.exmi01.exch.eds.com> 





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_at_freelists.org


[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Orr, Steve

Sent: Wednesday, May 26, 2004 11:58 AM


To: oracle-l_at_freelists.org


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_at_freelists.org


[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Tim Johnston

Sent: Wednesday, May 26, 2004 9:46 AM


To: oracle-l_at_freelists.org


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_at_freelists.org
>put 'unsubscribe' in the subject line.
>--
>Archives are at http://www.freelists.org/archives/oracle-l/
>FAQ is at http://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_at_freelists.org
put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://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_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://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_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------


Received on Wed May 26 2004 - 11:15:04 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US