Links
• Home
• Oracle DBA Forum
Frequent Oracle Errors
•TNS:could not resolve the connect identifier specified
•Backtrace message unwound by exceptions
•invalid identifier
•PL/SQL compilation error
•internal error
•missing expression
•table or view does not exist
•end-of-file on communication channel
•TNS:listener unknown in connect descriptor
•insufficient privileges
•PL/SQL: numeric or value error string
•TNS:protocol adapter error
•ORACLE not available
•target host or object does not exist
•invalid number
•unable to allocate string bytes of shared memory
•resource busy and acquire with NOWAIT specified
•error occurred at recursive SQL level string
•ORACLE initialization or shutdown in progress
•archiver error. Connect internal only, until freed
•snapshot too old
•unable to extend temp segment by string in tablespace
•Credential retrieval failed
•missing or invalid option
•invalid username/password; logon denied
•unable to create INITIAL extent for segment
•out of process memory when trying to allocate string bytes
•shared memory realm does not exist
•cannot insert NULL
•TNS:unable to connect to destination
•remote database not found'>ora-02019
•exception encountered: core dump
•inconsistent datatypes
•no data found
•TNS:operation timed out
•PL/SQL: could not find program
•existing state of packages has been discarded
•maximum number of processes exceeded
•error signaled in parallel query server
•ORACLE instance terminated. Disconnection forced
•TNS:packet writer failure
•see ORA-12699
•missing right parenthesis
•name is already used by an existing object
•cannot identify/lock data file
•invalid file operation
•quoted string not properly terminated
Undocumented Instance/Media Recover Feature?

Undocumented Instance/Media Recover Feature?

2004-05-26       - By Orr, Steve

Reply:     1     2     3     4     5     6     7     8     9     10     >>  

Sorry for the long silence... I was doing something more important...
Playing racquetball.

Thanks for the control file demo thingy as it does mimic the symptoms
but no create controlfile stuff was done here so it must have been
something else.=20

> 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 (See ORA-600.ora-code.com) during a DDL=20
> operation... i.e. During a drop tablespace...
Well it 's related since it all started with a shared memory error.
Here 's another curiosity... The alert log history shows the create
tablespace but no other tablespace or datafile operations. Other curious
things in the alert log: Crash recovery completes successfully then
there 's a message saying it was switching the redo format from 8.1 to
9.2... That 's because that compatible parameter had been changed just
prior to the shutdown abort. Then there 's a lgwr error trace file saying
it can 't verify the archivelog destination even though the database is
not in archivelog mode. I fixed it anyway as it looks like there were a
lot of those over time. Then shortly after that is a Linux file read
error. Then after that I come along and startup the database
successfully. So how could the tablespace/datafile go away when an
object was still in it? Or how could the database come up if a datafile
was missing and the controlfile wasn 't changed? It 's a mystery and now,
since I apparently fixed everything I 'm going back to neglecting this
database. ;-)

One more thing... I once told QA dude that a routine shutdown abort was
OK but now I 'm thinking I 'll do a shutdown immediate IF... The
compatible parameter was changed and Oracle needs to switch its redo
format. (Something to test would be to create a 9.2 database with
compatible set to 8.1.0.0.0, change it to 9.2.0.0.0, shutdown abort, and
restart. But I 'm too lazy to do that and am not THAT curious. :-)


-- --Original Message-- --
From: oracle-l-bounce@(protected)
[mailto:oracle-l-bounce@(protected)] On Behalf Of Tim Johnston
Sent: Wednesday, May 26, 2004 12:06 PM
To: oracle-l@(protected)
Subject: Re: Undocumented Instance/Media Recover Feature?


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


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


$ sqlplus "/ as sysdba " =20

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

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


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

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



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


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

OWNER
-- ---- ---- ---- ---- ---- --
SEGMENT_NAME
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
-- -----
PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME
-- ---- ---- ---- ---- ---- -- -- ---- ---- -----=20
-- ---- ---- ---- ---- ---- --
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 (See ORA-00376.ora-code.com): file 35 cannot be read at this time
ORA-01111 (See ORA-01111.ora-code.com): name for data file 35 is unknown - rename to correct file
ORA-01110 (See ORA-01110.ora-code.com): 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# =3D 35; =20

FILE# CREATION_CHANGE# CREATION_ TS# RFILE# STATUS
ENABLED
-- ---- -- -- ---- ---- --- -- ------ -- ---- -- -- ---- -- -- ----=20
-- ---- --
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*=20
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... =20
In my case, it was when I encountered an ORA-600 (See ORA-600.ora-code.com) during a DDL=20
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?=3D20
>
>
> =20
>

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