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!
