ORA-01578: ORACLE data block corrupted (file # 7, block # 42061)

I've been loading the raw data into my sandbox for my little side project (Florida Medicaid reporting database).

Since much of this is throw away, I am not terribly concerned with repeatable processes, just get the data in there.

I have about 100 files to load, ~250 GBs worth. I've created a spreadsheet with the file names, the associated external table, whether it has been loaded or not and the data loaded. This is just for my own sanity, as I do this when time permits and I don't want to lose track of my progress.

Using the external table name and file name, I've created a formula to build my ALTER statements (ALTER TABLE <table_name> LOCATION ( '<file_name>' );). Each file takes 3 to 5 minutes to load.

The other morning, around 3 AM, I got to the 10th file and received a communication error with the server. Being so late, I didn't bother with researching the issue, I just retired.

The next day, I attempted to look into the issue, first I wanted to see which files were loaded.

SELECT COUNT(*)
FROM orig_claim;

SELECT COUNT(*) FROM orig_claim
                     *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 7, block # 42061)
ORA-01110: data file 7: '/u01/app/oracle/oradata/TESTING/medicaid_02.dbf'

Uh oh.

As I usually do, I posted that error message to Twitter.

Twitter = Awesomesauce Part 983



Here are some answers to the questions above:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>SELECT 
  instance_name, 
  host_name, 
  version, 
  status, 
  archiver 
FROM v$instance;

INSTANCE_NAME    HOST_NAME  VERSION           STATUS       ARCHIVE
---------------- ---------- ----------------- ------------ -------
TESTING          oracle     11.2.0.1.0        OPEN         STOPPED

SQL> SELECT 
  dbid, 
  name, 
  log_mode 
FROM v$database;

      DBID NAME      LOG_MODE
---------- --------- ------------
  86530622 TESTING   NOARCHIVELOG

Also, since I just started loading data, I had not performed a backup yet.

Other possibly pertinent information:
- Using COMPRESS BASIC
- Just before I had the communication error, I had enabled PARALLEL DML for the session and set it to 8 as a hint in the SQL statement
- INSERT /*+ APPEND */ from an external table into a staging table
- Oracle Enterprise Linux 5.5 (Carthage)
- 6 GB of RAM allocated to Oracle
- 4 CPUs allocated to this VirtualBox virtual machine

SQL> SELECT * FROM V$DATABASE_BLOCK_CORRUPTION;

     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
         4     100939          1                  0 CHECKSUM
         7      42061          1                  0 CHECKSUM

I tried using RMAN (BLOCKRECOVER DATAFILE 7 BLOCK 42061;), but naturally it had nothing to recover from. I had pondered using DBMS_REPAIR, but I was exactly sure of the implications, i.e. would I have to reload specific data files.

---

From Martin Berger:
An answer to his question below, what is the corrupted object?

SELECT 
  tablespace_name, 
  segment_type,
  owner, 
  segment_name 
FROM dba_extents 
WHERE file_id = 7 
  AND 42061 BETWEEN block_id AND block_id + blocks - 1;

It's a table. The ORIG_CLAIM table to be exact. That's the only core table I have begun to load.

Dropping and recreating it won't be terribly difficult, way better than rebuilding the entire VM/DB.

---

After identifying that a specific table had a corrupt block, I dropped it per Martin's guidance.

Since I wanted to keep the original (raw) data along with the, dimensional model, I decided to create a separate tablespace to house the raw data. I did that and began to load the data.

That's when my new joy happened:

ORA-01578: ORACLE data block corrupted (file # 8, block # 26031)

At first, at I was just going to drop and recreate the database. Issued the SHUTDOWN command then started it back up in RESTRICTED mode, STARTUP NOMOUNT EXCLUSIVE RESTRICTED, then dropped the thing. After a bit more thinking, I just decided to rebuild the entire VM.

The good news there, I get to mess around with Oracle Enterprise Linux 6. The bad news, more time.