Re: Undocumented Instance/Media Recover Feature?

From: Tim Johnston <tjohnston_at_quallaby.com>
Date: Wed, 26 May 2004 14:06:24 -0400
Message-ID: <40B4DCA0.8020604@quallaby.com>

Oracle didn't create that segment in the datafile... The segment that used to exist in the old datafile is still referenced in the data dictionary... However, the actually physical segment does not exist in the "MISSING" file... Here's another example...

First I create the tablespace and put a table in it...

$ sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.3.0 - Production on Wed May 26 13:50:34 2004

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production With the Partitioning option
JServer Release 9.2.0.3.0 - Production

SQL> create tablespace tim datafile '/export/home/oradata/PV429/tim.dbf' size 10M;

Tablespace created.

SQL>
SQL> create table tim_table ( col1 number ) tablespace tim;

Table created.

SQL> insert into tim_table values (1);

1 row created.

SQL> commit;

Commit complete.

SQL> alter database backup controlfile to trace;

Database altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production With the Partitioning option
JServer Release 9.2.0.3.0 - Production
$

Then I edit the control file create and recreate the controlfiles...

$ sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.3.0 - Production on Wed May 26 13:58:03 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

Now you notice that dba_segments shows this table exists in the tablespace with the "MISSING" datafile...

SQL> select * from dba_segments where tablespace_name = 'TIM';

OWNER

SEGMENT_NAME

PARTITION_NAME                 SEGMENT_TYPE       TABLESPACE_NAME


------------------------------ ------------------ 

------------------------------

HEADER_FILE HEADER_BLOCK      BYTES     BLOCKS    EXTENTS INITIAL_EXTENT

----------- ------------ ---------- ---------- ---------- -------------- NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE FREELISTS FREELIST_GROUPS ----------- ----------- ----------- ------------ ---------- --------------- RELATIVE_FNO BUFFER_
------------ -------
SYS
TIM_TABLE

                               TABLE              TIM
         35            5                                           65536
                      1  2147483645                       1               1
          35 DEFAULT

However, if you try to access the segment, you get an error...

SQL> select * from tim_table;
select * from tim_table

*
ERROR at line 1:

ORA-00376: file 35 cannot be read at this time
ORA-01111: name for data file 35 is unknown - rename to correct file
ORA-01110: data file 35: '/opt/oracle/product/9.2.0/dbs/MISSING00035'

And if you look in v$datafile you notice the status is "RECOVER"... The segment doesn't really exist in your database...

SQL> select * from v$datafile where file# = 35;

FILE# CREATION_CHANGE# CREATION_ TS# RFILE# STATUS ENABLED

---------- ---------------- --------- ---------- ---------- -------

CHECKPOINT_CHANGE# CHECKPOIN UNRECOVERABLE_CHANGE# UNRECOVER LAST_CHANGE#

------------------ --------- --------------------- --------- ------------
LAST_TIME OFFLINE_CHANGE# ONLINE_CHANGE# ONLINE_TI      BYTES     BLOCKS

--------- --------------- -------------- --------- ---------- ---------- CREATE_BYTES BLOCK_SIZE
------------ ----------
NAME

PLUGGED_IN BLOCK1_OFFSET
---------- -------------
AUX_NAME

        35          1421140                   45         35 *RECOVER* 
READ WRITE
                 0                               0                1421349
26-MAY-04               0              0                    0          0
           0      16384
/opt/oracle/product/9.2.0/dbs/MISSING00035
         0    4294967295

UNKNOWN SQL> As far as how it gets out of sync, I have seen this a couple times... In my case, it was when I encountered an ORA-600 during a DDL operation... i.e. During a drop tablespace...

Tim

Orr, Steve wrote:

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



----------------------------------------------------------------

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 - 13:03:15 CDT