 | | | Undocumented Instance/Media Recover Feature? | Undocumented Instance/Media Recover Feature? 2004-05-26 - By Powell, Mark D
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@(protected)
[mailto:oracle-l-bounce@(protected)]On Behalf Of Orr, Steve
Sent: Wednesday, May 26, 2004 11:58 AM
To: oracle-l@(protected)
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@(protected)
[mailto:oracle-l-bounce@(protected)] On Behalf Of Tim Johnston
Sent: Wednesday, May 26, 2004 9:46 AM
To: oracle-l@(protected)
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 > @(protected)
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@(protected)
>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@(protected)
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@(protected)
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@(protected)
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
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
|
|
|