Home
All Oracle Error Codes
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

RE: corrupt datafile

Graeme Farmer

2004-06-13

Replies:
Terry,

For point-in-time recovery you would also need the RBS/UNDO tablespace(s) to
allow incomplete transactions to be rolled back. Moot point considering the
spread of datafiles in backups and lack of archive logs.

Based on the premise that some data is better than no data (and the
assumption that the indexes are in a non-corrupted tablespace), you could
take the affected datafile offline, open the database and dump the data that
is in the indexes into copies of the tables then dump the data to an export
dump file. It's not ideal (unless you have indexes that cover all columns)
but it may help application analysts/management recover a large amount of
usable data. Or it may not, depends entirely on the coverage/availability of
indexes and the relative importance of non-indexed fields (probably
important or else they wouldn't be there!).

This would often be a reasonable approach for reconstructing small amounts
of data following block corruption (pre-RMAN blockrecover) but when you
don't have many options it may be that it saves "some" business data.

Here's a sample of a session which simulates this (please be very careful
with the C code; don't even compile, let alone run the code on a production
server. ).

sys@(protected)
sys@(protected)>
sys@(protected);

Table dropped.

sys@(protected)
2 ( a varchar2(30),
3   b number,
4   c number,
5   d number,
6   e number
7 ) TABLESPACE users;

Table created.

sys@(protected)>
sys@(protected)
2   v_rows NUMBER := 1000;
3 BEGIN
4   FOR i IN 1 .. v_rows LOOP
5    INSERT INTO corrupt
6       VALUES
('CORRUPT'||lpad(v_rows-i,23,'0'),v_rows,v_rows-i,100.00,200.00);
7   END LOOP;
8   COMMIT;
9 END;
10 /

PL/SQL procedure successfully completed.

sys@(protected)>
sys@(protected)
2   ADD CONSTRAINT corrupt_pk
3     PRIMARY KEY (a,b,c)
4     USING INDEX TABLESPACE users_idx;

Table altered.

sys@(protected)>
sys@(protected)
dbms_stats.gather_table_stats('SYS','CORRUPT',estimate_percent=>10,cascade=>
true); END;
2 /

PL/SQL procedure successfully completed.

sys@(protected)>
sys@(protected);
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@(protected)>
sys@(protected)
Changing to directory .
Backing up file rman_users_01.dbf to .rman_users_01.dbf
Creating file rman_users_01.dbf with size: 10493952 bytes

sys@(protected)*
-rw-r-----   1 oracle  dba    10493952 Jun 13 18:20 .rman_users_01.dbf
-rw-r-----   1 oracle  dba    10493952 Jun 13 18:20 rman_users_01.dbf
-rw-r-----   1 oracle  dba     5251072 Jun 13 18:20
rman_users_idx_01.dbf

sys@(protected)>
sys@(protected)
ORACLE instance started.

Total System Global Area  79691776 bytes
Fixed Size             777516 bytes
Variable Size         70263508 bytes
Database Buffers        8388608 bytes
Redo Buffers           262144 bytes
Database mounted.
ORA-01122: database file 4 failed verification check
ORA-01110: data file 4: '/dbprd/oracle/rman/rman_users_01.dbf'
ORA-01251: Unknown File Header Version read for file number 4


sys@(protected)>
sys@(protected)'
OFFLINE;

Database altered.

sys@(protected)>
sys@(protected);

Database altered.

sys@(protected)>
sys@(protected)
sys@(protected)>
sys@(protected)
2   FROM corrupt
3  WHERE a >= (SELECT min(a)
4           FROM corrupt);

1000 rows selected.


Execution Plan
----------------------------------------------------------
      0
SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=50 Bytes=1900)
      1            0
INDEX (RANGE SCAN) OF 'CORRUPT_PK' (INDEX (UNIQUE)) (Cost=2 Card=50
Bytes=1900)
      2            1
  SORT (AGGREGATE)
      3            2
   INDEX (FULL SCAN (MIN/MAX)) OF 'CORRUPT_PK' (INDEX (UNIQUE)) (Cost=2
Card=1000 Bytes=31000)




Statistics
----------------------------------------------------------
    882 recursive calls
      0 db block gets
    213 consistent gets
     27 physical reads
      0 redo size
   48545 bytes sent via SQL*Net to client
    1238 bytes received via SQL*Net from client
     68 SQL*Net roundtrips to/from client
     10 sorts (memory)
      0 sorts (disk)
    1000 rows processed

sys@(protected)>
sys@(protected)
sys@(protected)>
sys@(protected)

sys@(protected)>
sys@(protected);
Connected.
sys@(protected)>
sys@(protected);

Tablespace altered.

sys@(protected)>
sys@(protected)
sys@(protected)>    

# cat corrupt.c
// corrupt.c - make a copy of an uninitialised copy of a file
//
// Don't leave this lying around!!!! Muy peligroso!!!
//

#include<sys/types.h>
#include<sys/stat.h>
#include<unistd.h>
#include<stdio.h>
#include<fcntl.h>
#include<string.h>
#include<libgen.h>

int main(int argc, char** argv)
{

if (argc<2)
{
  (void)fprintf(stderr,"Usage: %s <file>\n",argv[0]);
  _exit(1);
}

char* dir   = strdup(argv[1]);
char* file   = strdup(argv[1]);
char* newfile = (char*) malloc(strlen(argv[1])+2);

dir=dirname(dir);
printf("Changing to directory %s\n",dir);
if (chdir(dir)<0)
{
  perror("chdir");
  _exit(1);
}

file=basename(file);

*newfile = '.';
memcpy(newfile+1,file,strlen(file)+1);

struct stat s;
stat(argv[1],&s);

printf("Backing up file %s to %s\n",file,newfile);
rename(file,newfile);

printf("Creating file %s with size: %d bytes\n",file,s.st_size);
int fd = open(file,O_RDWR|O_CREAT|O_TRUNC);
if (fd)
{
  ftruncate(fd,s.st_size);
  fchmod(fd,S_IRUSR|S_IWUSR|S_IRGRP);
  close(fd);
} else {
  perror("open");
}

return 0;
}

Good luck,
Graeme.


-----Original Message-----
From: DENNIS WILLIAMS [mailto:DWILLIAMS@(protected)]
Sent: Sunday, 13 June 2004 9:34 AM
To: 'oracle-l@(protected)'
Subject: RE: corrupt datafile

Terry - I think you are correct. I believe the minimum you need is the
system tablespace and the lost tablespace. I don't think you can ever open
the lost tablespace otherwise. Frustratingly close though. Anyway you've
received better advice from Tim. People issues are often key in these
situations, but you sound like you've some insights to that.

Dennis Williams
DBA
Lifetouch, Inc.
dwilliams@(protected)

-----Original Message-----
From: oracle-l-bounce@(protected)
[mailto:oracle-l-bounce@(protected)
Sent: Saturday, June 12, 2004 4:53 PM
To: oracle-l@(protected)
Subject: Re: corrupt datafile


Dennis,

I don't think a TSPITR will help, because we don't have continuity between
datafiles from different times. Most datafiles are from Thursday's backup
and one is from Wednesday's backup, and we don't have archivelogs in
between. For TSPITR we'd need backups from a point in time prior to the
desired recovery time. Unfortunately the ONLY backup file we have from
Wednesday is the one for the now-corrupted file.

We do have everything on another server, so we can experiment there, but I
can't see how it would work.

Cheers,

Terry


> Terry - You may want to consider a TSPITR. Tablespace Point-In-Time
> Recovery. Do you have another server that you can perform this on? Then
> export/import the data back to your production system.
>
>
>
> Dennis Williams
> DBA
> Lifetouch, Inc.
> dwilliams@(protected)
>
>
>
> -----Original Message-----
> From: oracle-l-bounce@(protected)
[mailto:oracle-l-bounce@(protected)
> Behalf Of Terry Sutton
> Sent: Saturday, June 12, 2004 3:22 PM
> To: oracle-l@(protected)
> Subject: corrupt datafile
>
>
> A new client has a serious corruption error. During a hot backup (not
RMAN)
> Thursday morning it was discovered that a datafile was corrupted on the
> disk. When the database is started up, the following error occurs:
>
> "ORA-01122: database file 5 failed verification check
> ORA-01110: data file 5:
> '/ora2/app/oracle/admin/dbn/data/dbn_data_01.dbf'
> ORA-01251: Unknown File Header Version read for file number 5"
>
> When dbverify is run against this file, every block is indicated as
corrupt,
> which isn't surprising.
>
> We have a copy of the datafile from Wednesday's hot backup, but they don't
> have the archivelogs necessary to sync this datafile with the rest of the
> database. And we don't have the other datafiles from Wednesday's backup,
so
> we can't just go back to the state the database was in then.
>
> Any ideas on what they can do? I can't think of anything (other than
going
> back to an export they have from 6 months ago, which seems to be the only
> historical backup they have).
>
> --Terry
>
> ----------------------------------------------------------------
> 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
-----------------------------------------------------------------


--
This transmission is for the intended addressee only and is confidential information. If you have received this transmission in error, please notify the sender and delete the transmission. The contents of this e-mail are the opinion of the writer only and are not endorsed by the Mincom Limited unless expressly stated otherwise.

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