-
I have exported a table with a corrupted block and get the following:
EXP-00056: ORACLE error 1578 encountered
ORA-01578: ORACLE data block corrupted (file # 5, block # 112676)
ORA-01110: data file 5: '/u12/oradata/SPFHD1/fh_datal01_001.dbf'
Export terminated successfully with warnings.
If I drop the corrupted table, and try to import the table back from the exported dump file which gave the above error during export, would I get my rows back minus what was in the corrupted block??? What are the consequences??? Please - your input highly awaited!!
roukie-dba
-
Can someone help with my problem please???
Can a moderator help with my problem please???
roukie-dba
-
No, AFAIK you will not get any rows of this table from this export. Export performs full table scan on this table and like normal select it fails without returning any rows when hitting the first corrupted block.
You have sveral options of how to pull out the table data (other than rows in the corrupted blocks).
*** If you are on 8i you can simply isue the following:
execute DBMS_REPAIR.SKIP_CORRUPT_BLOCKS('','');
and then export the table. This will enable exp to skip over the corrupted blocks. After you are done you can reset the skip flag back to normal with:
execute DBMS_REPAIR.SKIP_CORRUPT_BLOCKS('', '', flags=>dbms_repair.noskip_flag);
*** If you are on pre-8i or if you can't use DBMS_REPAIR, you can set special event in your init.ora:
event="10231 trace name context forever, level 10"
This event like the procedure above enable the database to skip over the corrupted blocks when performing full table scan. Start the database with this modified init.ora, export the table, shutdown the database, delete the above event from your init.ora and start the database again.
***
If you can't or wouldn't use any of the above options (for example you don't have the appropriate privileges on the database), you could find out the ROWID "boundaries" of the corrupted block and use that in your WHERE clause of the export. You should find the LAST rowid that resides in the block prior the offended block and the FIRST one that resides in the block following the corrupted one. Then you can simply use something like QUERY="where ROWID <= rid_LAST or ROWID >= rid_FIRST" in your exp, replacing rid_LAST and rid_FIRST with your actual ROWIDs found in the previous step. Note that QUERY parameter of exp is available only in 8i.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
Dear Jmodic
A very big thank you for very detailed answer. I am running on 8.0.5.2. I do have the following questions:
1) The following recommendation to adjust in my init.ora
event="10231 trace name context forever, level 10"
Please explain what the number 10231 and level 10 mean, and is this what we have to put in the init.ora regardless of corrupted block?
roukie-dba
-
event="10231 trace name context forever, level 10"
What is event 10231 ?
~~~~~~~~~~~~~~~~~~~~~
This event allows Oracle to skip certain types of corrupted blocks
on full table scans ONLY hence allowing export or "create table as
select" type operations to retrieve rows from the table which are not
in the corrupt block. Data in the corrupt block is lost.
The scope of this event is limited for Oracle versions prior to
Oracle 7.2 as it only allows you to skip 'soft corrupt' blocks.
Most ORA 1578 errors are a result of media corruptions and in such
cases event 10231 is useless.
From Oracle 7.2 onwards the event allows you to skip many forms of
media corrupt blocks in addition to soft corrupt blocks and so is
far more useful. It is still *NOT* guaranteed to work.
Felix
DBA
-
Thanks very much for the answers.
Please explain the following to me:
I do a select * from schema.table and obtained the following:
SQL> select count(*) from sphdb.fwhdblot;
select count(*) from sphdb.fwhdblot
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 112676)
ORA-01110: data file 5: '/u12/oradata/SPFHD1/fh_datal01_001.dbf'
Then I exported the table from the schema and obtained the following:
About to export specified tables via Conventional Path ...
Current user changed to SPHDB
. . exporting table FWHDBLOT
EXP-00056: ORACLE error 1578 encountered
ORA-01578: ORACLE data block corrupted (file # 5, block # 112676)
ORA-01110: data file 5: '/u12/oradata/SPFHD1/fh_datal01_001.dbf'
Export terminated successfully with warnings.
$
Then imported the table into another schema and obtained the following:
Connected to: Oracle8 Enterprise Edition Release 8.0.5.2.1 - Production
With the Partitioning option
PL/SQL Release 8.0.5.2.0 - Production
Export file created by EXPORT:V08.00.05 via conventional path
Warning: the objects were exported by SYSTEM, not by you
. . importing table "FWHDBLOT" 14068 rows imported
Import terminated successfully without warnings.
Then I repeat a SELECT * from schema.table (in schema I just imported table into) and get the following:
SQL> select count(*) from martin.fwhdblot;
COUNT(*)
----------
14068
1) Why did the preceding query not work in the schema containing the corrupt table?
2) a SELECT * FROM table is a full table scan and works in both schemas...I am confused!!!
Please help me understand!
roukie-dba
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|