System Copy/ Oracle Backup/Restore as part of migrating from 32 to 64-bit

[former_member318733]

Source system (I386): R3E47 Ext Set 2.00 SAP_BASIS 620 Kernel 640 Windows 2000, Oracle 10.2.0.4, non-unicode

Target system (x86-64/AMD64): R3E47 Ext Set 2.00 SAP_BASIS 620 Kernel 640 Windows 2000 Oracle 10.2.0.4, non-unicode

Am using method System Copy/ Oracle Backup/Restore to migrate from 32 to 64 bit (HOM System Copy).

Actions on source system:

a) Upgrade from 9.2.0.7 to Oracle 10.2.0.4

b) Used ora_br_copy.bat to generate CONTROL.SQL and init<sid>.ora

c) Switched logfiles

d) Performed offline backup

Actions on target system:

a) Installed Oracle 10.2.0.1 and patched it to 10.2.0.4

b) Used CD 51033746 "6.20/6.40 based products Installation Master (Edition May 2008)" to install the CI

c) Started installation of the DB instance selecting System Copy/ Oracle Backup/Restore (same <SID> for source and target systems)

Relevant input parameters used:

Database schema: SAPR3

Database character set: US7ASCII

d) When SAPINST stopped to allow for restoring of datafiles, logs and init<sid.ora>:

- copied datafiles and logs to the usual place

- copied CONTROL.SQL (edited to include utilrp.sql and utlrp.sql for the migration from 32 to 64) to the DB-instance intallation . directory.

- copied generated init<SID>.ora to the <ORACLE_HOME>\database

e) Resumed SAPINST.

David Byrne in a previous post suggested: "check your oracle alert file in case there are errors there you may need to update the word size for the 64bit install. Errors like this in the oracle alert file would suggest that.,

ORA-12012

ORA-06544: PL/SQL: internal error, arguments: [ORA-06544: PL/SQL: internal error, arguments: 56319], [, ], [, ], [, ], [

ORA-06553"

Well, I am getting ORA-06553: PLS-801: internal error [56319] but then my edited CONTROL.SQL (which is mainly a CREATE CONTROLFILE as it would be obtained with a ...BACKUP CONTROLFILE TO TRACE) includes at the end this:

shutdown immediate

startup upgrade

spool utlirp.log

@?/rdbms/admin/utlirp.sql

spool off

Shutdown immediate

Startup

Spool utlrp.log

@?/rdbms/admin/utlrp.sql

Spool off

exit

which is supposed to do just that... (I think)

Help, anyone?

[markus_doehr2 · Accepted solution]

> Source system (I386): R3E47 Ext Set 2.00 SAP_BASIS 620 Kernel 640 Windows 2000, Oracle 10.2.0.4, non-unicode

> Target system (x86-64/AMD64): R3E47 Ext Set 2.00 SAP_BASIS 620 Kernel 640 Windows 2000 Oracle 10.2.0.4, non-unicode

I wonder where you got the 64bit Windows 2000 from

> Well, I am getting ORA-06553: PLS-801: internal error [56319] but then my edited CONTROL.SQL (which is mainly a CREATE CONTROLFILE as it would be obtained with a ...BACKUP CONTROLFILE TO TRACE) includes at the end this:

You may try to follow the suggestions in Oracle metalink 62290.1

Markus

[former_member318733 · Accepted solution]

Copy paste has its uses more often than not so I won't ban it just yet

[former_member318733 · Accepted solution]

Hi Markus,

I had a quick look at the metalink you suggested, but it looks not much different (or vice-versa) from SAP's "Upgrade to Oracle

Database 10g Release 2 (10.2): Windows". I will go back to it and check the small details if re-running things in my original database doesn't help. Am now copying the datafiles back to the 64-bit HW, so that gives me some 3 hours to get some sleep. Thanks for the suggestion.

Inê

[markus_doehr2 · Accepted solution]

Hi Ines,

I have pretty much the same thing to do in a few days (source is Windows 2003 32bit though) and I found this article as a "quick-shot" - it looked reasonable; I must admit, I haven't done it now. I just saw, that there are a few more things listed than in the upgrade guide (which may or may not help).

Good luck - and catch some sleep (I will do so too - 3:45 am here).

Markus

[former_member318733 · Accepted solution]

Copied back datafiles. Ran catupgrd. Ran into unexpected errors so, after some (unsuccessful) experiments, I'm going back to the 9.2.0.7 and re-upgrade it to 10.2.0.4 (will check on the details from that metalink 62290.1 tip of yours).

I do hope it works. Am leaving my thread "open" anyway, in case it doesn't...

[former_member318733]

Correction to original post: target system's OS is Windows 2003

Updated report on the situation:

Now... I'm almost ready to give up on the Oracle Backup/ Restore and use R3load instead. However, I'll keep the two options open (as long as I don't run out of disk space).

The potential (and serious) problem I can see with using Backup/ Restore is that I can't change SCHEMA-ID and the DB instance installation asks for the database schema. There's a note saying it is not recommended SAP be used and I have no wish to use it. Still... this is a much upgraded system and schema-id is still R3. I've been trying to use 'SAPR3' but that might be a "good" reason for it not to work since it's expecting SAP+3 characters. I wish I could use SAPSR3 but I can't change it (pre-condition for the method). So, after having - repeatedly and unsuccessfully - tried the much faster Backup/ Restore (this is not a joke! It would be if I got it to work and I have six systems in all to repeat the procedure in, that's why I'm being so obsessive about it) I went back to the original 32-bit Oracle 9.2.0.7 database and upgraded it again to 10.2.0.4 paying much more attention to parameters. Database is upgraded and working (tested the relevant SAP system) , so that shouldn't be the "root cause" for my problem.

I'm now going to try with R3load, but if anyone has some suggestion that would save the day for the Oracle Backup/ Restore I'd love to hear it

Inês Ferreira

[markus_doehr2]

Hi Ines,

there are also a number of advantages if you use R3load:

- the schmema-ID thing is out of scope them (you install with the correct one)

- you get a "free reorganization" (since everything is loaded into a new database)

- sapinst creates a "default" installation of the database so get all the 10.2 features activated by default (rollback vs. undo, LMTS and all that stuff) which you would need to do manually

If you plan to use that, I highly suggest using the latest version of R3load and lib_dbsl on source and target system and use the load option "-loadprocedure fast".

Markus

[former_member318733]

Hi Markus,

Trying to cheer me up, are you?

It is NOT free that reorganization! Quite expensive, actually, paid for in time! But you're right... the database needed a reorg anyway :-

And why would I have to reconfigure all those 10.2 features? Everything's either in the database or init.ora, ain't it? And my database is a pretty 10.2.0.4 with all those nice features already activated...

One sticky point... I haven't really read the r3load procedure yet and I will before I import, but... I would say I don't have to do anything about the 32->64 bit, right? I mean, it should behave properly while reloading on 64-bit...

Thanks for the tips,

Inê

[markus_doehr2]

> Trying to cheer me up, are you?

No

> It is NOT free that reorganization! Quite expensive, actually, paid for in time! But you're right... the database needed a reorg anyway 😕

True, not "free" - it takes time but the data will be nicely aligned then

> And why would I have to reconfigure all those 10.2 features? Everything's either in the database or init<sid>.ora, ain't it? And my database is a pretty 10.2.0.4 with all those nice features already activated...

No - not really auotmatically if you upgraded from 9.2 And "init<SID>.ora" is past, you nowadays use "spfile" If, of course, e. g. Automatic Undo was activated in 9.2 then it's there of course in 10.2 too.

> One sticky point... I haven't really read the r3load procedure yet and I will before I import, but... I would say I don't have to do anything about the 32->64 bit, right? I mean, it should behave properly while reloading on 64-bit...

The exported content is about 10 % of the size of the system, it's database and OS independent so yes, you can import it nicely on 32bit. Another tip: make sure you don't just use the default configurations for R3load, it will take a HUGE amount of time. Use the package splitter (builtin-in in sapinst) so you get a much higher parallelism when importing.

Markus

[markus_doehr2]

And something else about time:

You can even to export and import in parallel.

Markus

[stefan_koehler]

Hello Ines,

just as an additional information to the answers of Markus.

A reorganization with R3load can also have disadvantages .. especially in the case of performance issues. (changed statistics, changed object sizes, data order, etc.).

>And why would I have to reconfigure all those 10.2 features?

As you already mentioned that the system was upgraded often, you maybe still have DMTS (dictionary managed tablespaces) or maybe LMT (locally managed tablespaces) with or without ASSM (automatic segment space management), etc..

All these stuff is configured/installed with the newest supported options (for example LMT with ASSM), if you do a R3load migration.

No idea whats your issue with the backup/restore topic. I have done such migrations with RMAN and the two sql scripts (utlirp.sql, utlrp.sql) several times .. and it always worked fine (but on Linux/Unix).

Also no idea why you perform a "DB instance installation" with SAPinst, if you migrate your database with backup/restore. All that stuff that is needed to migrate the SAP part can mostly be done without SAPinst. (Installation central instance with SAPinst, copy the profiles, environment, etc.)

Regards

Stefan

[former_member318733]

"The exported content is about 10 % of the size of the system, it's database and OS independent so yes, you can import it nicely on 32bit". Hope I can import it just as nicely on 64 bit...

and how do I answer both you and Stefan at the same time??? Well, I suppose you'll both peek at the other's answers. I know I would...

1) I had most of the nice features already configured in 9.2.0.7... UNDO, LMT (without ASSM), etc., so they're still there.

2) I can still use init.ora, can't I? Faster than remembering the syntax for altering the parameter... (there are some things I love to hate and that is one of them... don't know why, might be Freudian or something)

3) (and this is for Stefan): It might work, without the DB "installation", but how do Oracle things get into the registry the first time? And SAPINST doesn't install a database from scratch, it just gets the required parameters, slips in the old datafiles, and carries on with some post processing. Rather like the old procedure with "alter database backup control file to trace", only more smooth (if I could get it to work flawlessly)

4) Now I just got me an error while exporting (!?!). Going to look at it (maybe it will go away if I frown)

Inês

Edited by: Ines Ferreira on Aug 23, 2009 4:52 PM

[stefan_koehler]

Hello Ines,

1) Fine .. well done )

2) Yes you can, if you don't use RMAN with AUTOBACKUP (with or without BR*Tools), otherwise your pfile will not be backuped.

3) The oracle registry keys get into the registry through the oracle installer .. not SAPinst. Of course you need to do some things manually, but if you want to save time (and maybe stress) i would prefer this solution. If you have documented the manual steps once, they are done really quick by the productive migration. I would also prefer the RMAN solution for the migration .. it's fast, reliable and smooth too )

But its your choice ... Good luck )

Regards

Stefan

[former_member318733]

Not all... some entries in HKLM -> SOFTWARE -> SAP -> <SID> -> Environment are placed there by the DB bit. However, I suppose I can put them there (don't like it, but...). Now, Stefan, the post processing from the very old R3COPY (or whatever it was called, it existed for Unix vefore it was adapted to Windows some centurie ago during 4.0B - 4.5B) procedure (PAHI, SDBAH, SDBAD, etc.) is still valid? I suppose the manual is not on the marketplace anymore but I think I have a copy at the office... 15 min drive, not too bad.

I will have to follow Markus advice, eventually (I want to change the SCHEMA-ID and the tablespace layout, these particular databases come from 3.1I or even older, and they need a reorg) but I can do it later, once this copy is settled.

[former_member318733]

Hi Markus,

Am trying R3load. Will go to the old R3COPY procedure (which is more or less what Stefan is suggesting) only if all else fails. I've "lost" so much time that I really want to get my procedure for system copies streamlined so as to get my time back on the next 5

I'm changing schema-id to SR3 but the owner will still be SAPR3 (they were born last century), I presume. So I'll be using schema-id SR3 when reloading but will keep dbs_ora_schema as SAPR3. Sounds ok?

Inês

Edited by: Ines Ferreira on Aug 24, 2009 2:30 AM

[former_member318733]

Hi Markus,

Got into various problems with the R3load (nothing serious, PSAPTEMP unable to extend) but it was really taking too long so on Monday I went back to the manual procedure I was trying to avoid. I used it often before (since 4.0B or 4.5B, I forgot which), so at least it was familiar. System is up and running since 8:00 AM. It has all the disadvantages you pointed out, but it is definitely quicker than load/ reload, so I'll do it on the 64-bit hardware later - the new HW has more of everything, not just double words, but double RAM, double processors...

However: load/ reload will solve the "outdated" schema-id thing but... the owner will still be the ancient SAPR3.

Have you started your own migrations?

In a few days I will have to do the same for the production server, and I will make some few experiments again. Will leave this open till then.

Inês

Edited by: Ines Ferreira on Aug 25, 2009 4:51 PM

[former_member318733]

Hi Stefan,

Ended up by doing the old manual procedure (which is really what sapinst is doing in my original choice of methos, but much more obscurely) for the reasons you pointed out (time and stress).

I still want to do a load/ reload but I'll do it later, when things are calmer and on this faster HW.

Thanks

Edited by: Ines Ferreira on Aug 25, 2009 4:47 PM