Bug 9789190 - RMAN RESTORE raises ORA-19660 from a compressed backup taken from an 6k DB_BLOCK_SIZE [ID 9789190.8]

I finally found the issue with my migrated database. It's a bug - it prevents RMAN compressed backups made with a 6k blocksize from being usable. Yup, my backups were completely unusable. Thank goodness I discovered that BEFORE needing a backup.

I'm not going to reproduce the text of the bug. Its Bug ID is 9789190 - you can look it up.

The bug describes it as compressed backups created using an 'unusual' block size. Well, OK, is 6k an unusual block size? I thought in 9i it was pretty common. It's been fixed in 11.2.0.1, it definitely affects 10.2.0.5 & 10.2.0.4.

I discovered it by accident. After converting the training db, I backed it up, and then messed around, then restored it. It didn't restore. I wasn't concerned about the restore per se, since I still had the offline backup from the 9i db. But, I was concerned that a freshly baked backup didn't work. That's how I found this bug. I was not a happy bunny when I found it, since that customer's live db has the same problem.

But, there are workarounds if you discover this issue the easy way. Don't use compressed backups or don't use an 'unusual' blocksize. Sigh.

I tested the use of uncompressed RMAN backups, and yes, that works. I would also say that the standard Export I would also highly recommend in such circumstances.

In a separate blog I'll detail how I'm going to deal with this permanently.

Oracle 7 Export Gotcha’s

At work, I had to migrate an Oracle 7 database on HP Unix to Oracle 10g 64-bit on Red Hat Linux. The easiest way, given the circumstances, was to stop updates to the db, and then export it consistently, import it into a new db, with the appropriate tablespaces, roles and schema users pre-created.

Sounds good in theory. Here are some gotchas to look out for when exporting an Oracle 7 database.

The export kept running out of space, that old chestnut “snapshot too old” error. I tried using DIRECT=Y (faster export requires less undo segments), CONSISTENT=N and INDEXES=N. I refused to stoop to doing table-by-table exports or partial exports since that approach can get messy quickly (CONSISTENT=N for testing purposes was acceptable).

Not, a problem, just add a large tablespace and create a large rollback segment (call it ‘BIGRBS’), disable the old, small rollback segments, and that should be it.

Hmm, no, still doesn’t work. The export still fails with that error. Well, not a problem, I’ll just add more space to the tablespace, and drop & re-create BIGRBS with a higher MAXEXTENTS.

NO!  Oracle 7 still thwarts the export. I wonder if the db is ‘possessed’ and doesn’t want to be migrated, lol.

But, by using the GUI Oracle Enterprise Manager (OEM), I found out why this kept happening.  When an export transaction requests rollback segments, Oracle 7 allocates up to MINEXTENTS, and denies any further extents while MAXEXTENTS IS STILL FAR AWAY!

The solution to this problem is elegant – create BIGRBS with the same value for MINEXTENTS and MAXETENTS.

Ah, here is the second gotcha: if you have 500 MB allocated to a tablespace, you can not use all 500 MB to create a rollback segment. Because of internal overheads, you MUST use LESS than 500 MB for you extents. But, you are not warned, dear reader, you are left to taste the bitterness of defeat yet again.

To add insult to injury, when you attempt to create a rollback segment on Oracle 7 and the statement fails, all the extents remain allocated!!! Then, the only way to clear the extents is to drop the tablespace. But, lo and behold, the database now decides to drop the unused (but allocated) extents! To add salt to a wound it takes longer to drop the unused extents than it took to allocate them in the first place!

I finally created BIGRBS on a new tablespace. But, the crucial point is this: create the rollback segment with a large INITIAL and NEXT (so extent allocation is rapid), ensure MINEXTENTS is the same as MAXEXTENTS and use at least 100 MB less than the full tablespace space available.

Finally, I got an export :) And the first import of the dmp file worked :) Hurrah!