Lessons from migrating Oracle 9i to 10g

So I had to migrate a 32-bit Oracle 9.2.0.7 database on Windows 2000 to a new platform - 64-bit 10.2.0.5   on Windows 2008. Now, when doing this sort of thing, you generally have two options:

1. Database export & import. Generally, this tends to be messy, long and tedious. I dislike it, but in this case, I probably should've done it this way. But I'm getting ahead of myself

2. RMAN recovery. I wasn't sure about this method, since, generally the RMAN versions have to the same i.e.  an RMAN backup had to be restored with the same RMAN version. I thought around this, and decided to try something different.

I found that If I took a hot-copy backup, I could re-create the instance, and apply the archive logs! Voila! A nice, clean method of migrating using RMAN. I could then run catupgrd.sql at my leisure.

Here are some lessons from this experience:

1. Controflile restore - don't use RMAN.

I tried to restore the controlfile to an ASM volume, and here we go:

SQL> $rman target /

Recovery Manager: Release 10.2.0.5.0 - Production on Fri Oct 12 14:46:43 2012

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: live (not mounted)

RMAN> restore controlfile from 'E:\backup321\CONTROL.BAK';

Starting restore at 12-OCT-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=158 devtype=DISK

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 10/12/2012 14:46:58
ORA-00600: internal error code, arguments: [krbydd_lbszXinbuf], [1048576], [6144], [], [], [], [], []
ORA-19600: input file is control file  (+DG1/live/controlfile/current.270.796488417)
ORA-19601: output file is control file  (E:\LIVE\CONTROLFILE\O1_MF_%U_.CTL)

This error is not documented anywhere. I think it has to do with using RMAN 10g to restore a 9i controlfile.

Lesson: copy the controlfile into place without RMAN.

Update: it has to do with the DB_BLOCK_SIZE  - look at the second argument, it's the DB_BLOCK_SIZE.

2. Check your blocksize.

This 9i db has a block size of 6k, which is unusual in the 10g world. Turning on FLASHBACK DATABASE works, but then try to open the db! This is what you get:



SQL> alter database flashback on;

Database altered.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-38701: Flashback database log 1 seq 1 thread 1:
"E:\LIVE\FLASHBACK\O1_MF_87TK8985_.FLB"
ORA-27091: unable to queue I/O
ORA-27067: size of I/O buffer is invalid
OSD-04026: Invalid parameter passed. (OS 1048576)


SQL> show parameters db_rec

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------------
db_recovery_file_dest                string      E:\
db_recovery_file_dest_size           big integer 200G
db_recycle_cache_size                big integer 0
SQL> alter system set db_recovery_file_dest='+DG1' scope=both;

System altered.

SQL> alter database flashback off;

Database altered.

SQL> alter database flashback on;

Database altered.

SQL> alter database open;

Database altered.


Nothing about this in the Oracle Support site. I have been unable to resolve this, but the workaround (as seen above) is the store the flashback logs on an ASM volume.

This error has something to do with the way the flashback log writer (RVWR) is interacting with the OS. I think it has to do with the blocksize.

This is a bit of a kick in the teeth for me, since FLASHBACK DATABASE is one of the most useful features of 10g. If I had used export / import I'd by-pass this issue.

Anyway, hope this helps someone out there.