Re: Undocumented Instance/Media Recover Feature?

From: Tim Johnston <tjohnston_at_quallaby.com>
Date: Wed, 26 May 2004 11:46:03 -0400
Message-ID: <40B4BBBB.2050300@quallaby.com>

Hi Steve...

I believe you are seeing a mismatch between the controlfile and the data dictionary... Basically, you started up your database and the data dictionary says you have tablespace "blah" associated with file id "x" but the controlfile doesn't have a file for that tablespace... So, it places a dummy placeholder file in it's place... You can simulate this 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 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 SQL> select * from dba_data_files where tablespace_name = '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"=20
>
>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?=20
>
>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
>-----------------------------------------------------------------
>
>

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

Received on Wed May 26 2004 - 10:42:42 CDT