RE: Undocumented Instance/Media Recover Feature?
- From: "Powell, Mark D" <mark.powell@xxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 26 May 2004 13:02:00 -0400
I have seen problems related to the control file before but I cannot
remember the exact details since it was a VMS specific bug for version
7.0.15 (if my memory is correct) that required us to recreate the control
file by editing the file information in the script created by a backup
control file to trace command.
I have also seen system tables out of sync with each other. Specifically,
uet$ and fet$ where Oracle manages free and used extents. There was a
little problem related to truncate where if you cancelled the truncate the
entire instance might crash and if did these two base tables may not contain
information about an extent (or maybe it was that an extent would show as
both used and free). It was a generic UNIX version bug with what was
probably version 7.1. That was a fun tar! Funny things like this can and
do sometimes happen.
In this case dropping the "missing" tablespace and recreateing the index in
the tablespace you want it in will fix everything so that may be the best
course followed by wait and see.
Good luck.
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Orr, Steve
Sent: Wednesday, May 26, 2004 12:28 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Undocumented Instance/Media Recover Feature?
> Was an old or backup control file used to start the db=20
Nope. And I never witnessed any datadictionary views that weren't in
sync with the control files.
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Powell, Mark D
Sent: Wednesday, May 26, 2004 10:17 AM
To: 'oracle-l@xxxxxxxxxxxxx'
Subject: RE: Undocumented Instance/Media Recover Feature?
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?=3D20
-----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=3D20
data dictionary... Basically, you started up your database and the data
dictionary says you have tablespace "blah" associated with file id
"x"=3D20 but the controlfile doesn't have a file for that tablespace...
So, it=3D20 places a dummy placeholder file in it's place... You can
simulate this=3D20 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=3D20 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 =3D20
SQL> select * from dba_data_files where tablespace_name =3D3D '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=20
>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=20
>instructions on recycling the database... "SQL> shutdown abort" and=20
>"SQL> startup"=3D3D20
>
>But the database didn't come back up due to a shared memory error so I=20
>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=20
>up just fine with no warning messages or anything. Hmmm... That's=20
>curious... I guess the oracle just likes me better. I was in=20
>$ORACLE_HOME/dbs verifying the existence of the "lk$ORACLE_SID" file=20
>when I saw something curious... A 100MB file named "MISSING00042" which
>had the same database startup timestamp as the lk$ORACLE_SID file and=20
>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=20
>$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=20
>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=20
>and see that there's only one object in that tablespace/datafile, an=20
>index which could be rebuilt from the data in another tablespace the=20
>table is in. Does this mean that the oracle couldn't find the datafile=20
>but created it for me automagically just because it could and because=20
>the only object was an index?=3D3D20
>
>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=20
>'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
>-----------------------------------------------------------------
> =3D20
>
--=3D20
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
-----------------------------------------------------------------
----------------------------------------------------------------
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
-----------------------------------------------------------------
Other related posts:
