Saturday, October 20, 2012

Major Pain - How to convert from 6k blocksize to 8k blocksize

OK, if you've been reading a few of my previous posts, you'll see I have a major problem. I tried to get the cleanest upgrade of a live production database from Oracle 9.2.0.7 x86 to 10.2.0.5 x64. So I used the method of recovering a hot-copy backup. But the problem now is, the database has a DB_BLOCK_SIZE of 6K, which is causing problems, viz unable to use FLASHBACK DATABASE and unable to create compressed RMAN backups (bug id 9789190). I'm supposed to create a Data Guard Standby database from this db, but now I'm not even sure if it'll work, since Data Guard is so highly dependent on archive logs and flashback database is highly recommended.

Anyway, I would like to change to a 8k block size to reduce the hassle of dealing with problems and to actually use compressed backups and FLASHBACK DATABASE.

Here's how I intend on doing it:
  1. Check for Transportable Tablespace violations. Clean up any violations.
  2. Define a DB_8K_CACHE_SIZE.
  3. Create equivalent 8K tablespaces. Resize the new tablespaces to cope with the table transfers.
  4. LONG / LONG RAWS. Many aplication versions ago, some columns were supposed to be converted from LONG RAW to BLOB. But at this customer, they weren't, and now it's my headache. Oracle can convert LONG RAW to BLOB by using the ALTER TABLE MOVE clause, and I've tested that the application still works with BLOB (Powerbuilder handles it internally according to the devs).
  5. Convert some of the tables' columns to BLOB.
  6. For those columns where I can't convert to LOB and I'm forced to maintain as LONG or LONG RAW, export drop, re-create (new tablespace) and import.About 4 tables, which isn't too much work.
  7. Move the LOB data to 8k-tablespaces. I wrote a script for this.
  8. Move all tables to the 8k-equivalent tablespaces.
  9. Rebuild all indexes to a new 8k tablespace.
  10. Set all 8k tablespaces to read only.
  11. Export using expdp for transport.
  12. Export the software code by using exp rows=n.
  13. Create a shiny, new db with 8k blocksize.
  14. Create the application-specific db roles and schema users.
  15. Create the directories.
  16. Attach the transportable tablespaces (application-specific).
  17. Change the default tablespaces per schema user.
  18. Import the software code fromuser / touser on the 8k db.
  19. Recreate the application user Oracle accounts.
Now, I could do a full export and a fromuser/touser import to a new db with a 8k block size. It will probably take around 9 hours. But that wouldn't fix the problem of LONG columns where they shouldn't be.

I have checked that, a table can have columns which reside simultaneously on 6k and 8k blocksize tablespaces.

No comments:

Post a Comment