| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: corrupt datafile
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_at_rman> @corrupt sys_at_rman> sys_at_rman> DROP TABLE corrupt;
Table dropped.
sys_at_rman> CREATE TABLE corrupt
2 ( a varchar2(30),
3 b number, 4 c number, 5 d number,
Table created.
sys_at_rman>
sys_at_rman> DECLARE
2 v_rows NUMBER := 1000;
3 BEGIN
4 FOR i IN 1 .. v_rows LOOP
5 INSERT INTO corrupt 6 VALUES
PL/SQL procedure successfully completed.
sys_at_rman>
sys_at_rman> ALTER TABLE corrupt
2 ADD CONSTRAINT corrupt_pk
3 PRIMARY KEY (a,b,c) 4 USING INDEX TABLESPACE users_idx;
Table altered.
sys_at_rman>
sys_at_rman> BEGIN
dbms_stats.gather_table_stats('SYS','CORRUPT',estimate_percent=>10,cascade=>
true); END;
2 /
PL/SQL procedure successfully completed.
sys_at_rman>
sys_at_rman> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
sys_at_rman>
sys_at_rman> !corrupt rman_users_01.dbf
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_at_rman> !ls -al *users* .*users*
-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:20rman_users_idx_01.dbf
sys_at_rman>
sys_at_rman> STARTUP
ORACLE instance started.
Total System Global Area 79691776 bytes
Fixed Size 777516 bytes Variable Size 70263508 bytes Database Buffers 8388608 bytes Redo Buffers 262144 bytesDatabase 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_at_rman>
sys_at_rman> ALTER DATABASE DATAFILE '/dbprd/oracle/rman/rman_users_01.dbf'
OFFLINE;
Database altered.
sys_at_rman>
sys_at_rman> ALTER DATABASE OPEN;
Database altered.
sys_at_rman> sys_at_rman> SET AUTOTRACE TRACE sys_at_rman> sys_at_rman> SELECT a,b,c
4 FROM corrupt);
1000 rows selected.
Execution Plan
0
SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=50 Bytes=1900)
1 0
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_at_rman>
sys_at_rman> SET AUTOTRACE OFF
sys_at_rman>
sys_at_rman> CONN / AS SYSDBA;
Connected.
sys_at_rman>
sys_at_rman> ALTER TABLESPACE users ONLINE;
Tablespace altered.
sys_at_rman> sys_at_rman> SET ECHO OFF sys_at_rman>
# cat corrupt.c
// corrupt.c - make a copy of an uninitialised copy of a file // // Don't leave this lying around!!!! Muy peligroso!!! //#include<string.h>
#include<sys/types.h>
#include<sys/stat.h>
#include<unistd.h>
#include<stdio.h>
#include<fcntl.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_at_LIFETOUCH.COM]
Sent: Sunday, 13 June 2004 9:34 AM
To: 'oracle-l_at_freelists.org'
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_at_lifetouch.com
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Terry Sutton
Sent: Saturday, June 12, 2004 4:53 PM
To: oracle-l_at_freelists.org
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_at_lifetouch.com > > > > -----Original Message----- > From: oracle-l-bounce_at_freelists.org
> Behalf Of Terry Sutton > Sent: Saturday, June 12, 2004 3:22 PM > To: oracle-l_at_freelists.org > Subject: corrupt datafile > > > A new client has a serious corruption error. During a hot backup (notRMAN)
> 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 ascorrupt,
> 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 thangoing
> 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_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 > ----------------------------------------------------------------- > > ----------------------------------------------------------------Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 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_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 ----------------------------------------------------------------- -- 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_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 Sun Jun 13 2004 - 03:58:34 CDT
 |
 |