Migration procedure wanted: Oracle 32 bit to Oracle 64 bit

[Steven E. Protter]

We have an oracle db running on HP-UX 11.00 32 bit.

Its oracle 8i release 3 8.1.7.0.0 with full JVM installation.

We want to migrate this database to HP-UX 11.11 64 bit. Oracle's procedures thus far have been full of holes. Big holes, that you can drive a semi truck through.

Our current migration procedure generates this error:

PLS-213, ORA-6553: PACKAGE STANDARD NOT ACCESSIBLE

Thats because it deletes a package called dbms_standard

Then when it tries to create it brand new, it can't access the package.

We are wondering if any real world users have encountered this problem and what they did to solve it.

Oracle's been workin on this about 3 weeks and they seem somewhat clueless(a charitable description). You guys do real work on HP-UX and points will be liberally awarded for help.

Steve

[Dave Chamberlin]

You can't install a 64 bit APP on a 32 bit OS. If you had a 64 bit OS, you could do a full export of the 32 bit database into a 64 bit database.

[John Poff]

Hi Steve,

The 64 bit version of Oracle will choke like a big dog on a 32-bit HP-UX installation. I experienced it first hand last summer by accident. One of our guys loaded the OS and missed that tiny little part about the 32/64 bitness in HP-UX. We wound up reloading the OS and starting from scratch.

I'm kind of surprised that Oracle didn't ask you if the OS was 64 bit.

JP

[Steven E. Protter]

Allow me to clarify.

The target environment is a different machine.

Its an 11.11 machine, 64 bit. Oracle 8.1.7 is already installed and working, also 64 bit.

We are need a data migration plan.

How to get the data off the 32 bit platform and into an instance on the 64 bit platform.

Steve

[John Poff]

Hi,

We've been doing some 32 bit Oracle 7.X migrations to 64 bit Oracle 8.1.x recently. Our DBAs have been exporting the database on the old system, copying it over to the new system, and importing the data into the Oracle 8 database.

JP

[Steven E. Protter]

thanks, one of our issues has been the presence or absence of vital java objects. It also seems that some objects are not compiled properly.

There are also issues with 32 bit word size to 64 bit word size.

Exports have not been importing really well into the target db.

I'm hoping to get lucky and run into a sysadmin who has done exactly what we are trying to do.

You'd think it would take five minutes since the version number is the same on all platforms, but we're dealing with Oracle support here.

Steve

[Brian Crabtree]

Hmmm. It sounds like you are hitting more than one issue. For the Java problem, I would suggest having them reload all of the java from scratch (there are procedures for this on metalink). For the invalid procedures, you can run $OH/rdbms/admin/utlrp.sql or utlirp.sql. The utlrp.sql script just rebuilds the packages that are invalid, while the utlirp.sql script invalidates all the packages, and then rebuilds them (requirement for a change in word size).

Can you run the following sql statement and paste the results here or in an attachment?

select object_name,object_type,status from dba_objects where owner = 'SYS' order by object_name,object_type;

Thanks,

Brian

[Indira Aramandla]

Hi with the erro message that you are experiencing, "ORA-00604 ORA-06553 PLS-213 Standard-package not accessible", you can try to execute the ORACLE_HOME/rdbms/admin/catalog.sql and catproc.sql as the SYS user ans see if this fixes.
But then again I have seen that you have Java issues. With Java issues, the only way is to reconfigure your database for Java. You have to remove the java (follow the instructions in hava enabling the database in Metalink) and re-configure the java. After the java configuration there should be roughly 1,040 java objects and class all with status ='VALID" and the package DBMS_JAVA should exist.

[Alexander M. Ermes]

Hi there.
The idea with export / import is ok.
You have to recompile your objects after the import. We found this the hard way.
It may be possible, that oyu compile your views every minute during the import, otherwise you might get stuck.
Oracle has sent me some tools.
----------------------------
compile.sql

@viewcomp
@package
@packbody
@procedure
@trigcomp

@badobjects
-------------------------------

viewcomp.sql

@terse
spool vslave.sql

select 'alter view '||owner||'.'||object_name||' compile ; '
from dba_objects
where object_type='VIEW'
and status = 'INVALID'
/

spool off
@login
spool viewcomp
@verbose
@vslave
spool off
@login

prompt viewcomp.sql completed
-------------------------------------
terse.sql

set term off
set show off
set scan on
set space 1
set time off
set timing off
set sqlprompt ''
set sqlcontinue ''
set pages 50000
set lines 255
set head off
set feed off
set pause off
set wrap off
~
--------------------------------

package.sql

@terse
spool pslave.sql

select 'alter package '||owner||'.'||object_name||' compile package ; '
from dba_objects
where object_type = 'PACKAGE'
and status = 'INVALID'
/

spool off
@login
spool package
@verbose
@pslave
spool off
@login

prompt package.sql completed
~
--------------------------------------

packbody.sql

@terse
spool slave.sql


select 'alter package '||owner||'.'||object_name||' compile body ; '
from dba_objects
where object_type = 'PACKAGE BODY'
and status = 'INVALID'
/
spool off
@login
spool packbody
@verbose
@slave
spool off
@login

prompt packbody.sql completed
-----------------------------

procedure.sql

@terse
spool slave.sql

select 'alter procedure '||owner||'.'||object_name||' compile ; '
from dba_objects
where object_type = 'PROCEDURE'
and status = 'INVALID'
/

spool off
@login
spool procedure
@verbose
@slave
spool off
@login

prompt procedure.sql completed
~
------------------------------------

trigcomp.sql

@terse
spool tslave.sql

select 'alter trigger '||owner||'.'||trigger_name||' compile ; '
from dba_triggers
/

spool off
@login
spool trigcomp
@verbose
@tslave
spool off
@login

prompt trigcomp.sql completed
--------------------------------------

badobjects.sql

@login
spool badobjects

select owner, object_name, object_type
from dba_objects
where status = 'INVALID'
/

spool off

prompt end of badobjects.sql

--------------------------------------------

Hope i could help.
Rgds
Alexander M. Ermes

[H.Merijn Brand (procura]

Don't forget that you will need a completely rebuilt perl for 64bit if you are using DBD::Oracle. Note that if you are using a lot of CPAN modules, some might not work in 64bit mode.

Just a reminder.

Enjoy, have FUN! H.Merijn