RE: Undocumented Instance/Media Recover Feature?

From: Orr, Steve <sorr_at_rightnow.com>
Date: Wed, 26 May 2004 09:57:47 -0600
Message-ID: <D30BE1A2F9109A43BA989E2F51684056062CA335@pobox.corp.rightnow.com>

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

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

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
-----------------------------------------------------------------

Received on Wed May 26 2004 - 10:54:49 CDT