This discussion is archived
11 Replies Latest reply: Jan 31, 2013 10:18 AM by 652072 RSS

Out-of-place upgrade going wrong

Catfive Lander Explorer
Currently Being Moderated
Trying to work through an upgrade from 11.2.0.1 to 11.2.0.3 on a dev box, prefereably without major downtime, 64-bit Linux. Since I cannot shutdown existing listeners or database, the approach is perhaps a bit non-standard

<li>I've got the 11.2.0.3 software unzipped</li>
<li>I've created directories for a new ORACLE_BASE</li>
<li>I've exported ORACLE_BASE=/new/directory/structure</li>
<li>I've then launched database/runInstaller from the unzipped location and performed a painless software-only installation</li>

I'm now slightly confused about the next bit, bearing in mind I'm trying to do this without any downtime on the existing 11.2.0.1 database.

What I'd like to do is to get a copy of the existing database opened and running from the 11.2.0.3 home, whilst the existing 11.2.0.1 db continues on as it is.

<li>So I've set a new ORACLE_SID</li>
<li>I've copied across the orapw and init.ora from the old O_H to the new, renaming them appropriately</li>
<li>I've edited the init.ora so that path-specific parameters all now take the new O_H directory structure</li>
<li>I've then put the entire existing database into hot backup mode and copied it to the new O_H structure</li>

Because so little is being done on the original database I appear to have got away with copying online redo logs, because I was able to startup mount; issue lots of 'alter database rename file...' commands so the new file locations are picked up; recover database; alter database open upgrade.

What I am now struggling with is how to actually upgrade the freshly-opened new database. If I run dbua, it says 'database isn't mounted', whether the new db copy is in mount or open upgrade mode. If I run @catupgrd.sql, it seems to complete without a problem, leaving STATUS of V$INSTANCE at OPEN MIGRATE. But when I run the @utlu112s.sql command, I get this:
Oracle Database 11.2 Post-Upgrade Status Tool           01-23-2013 6:45:00
.
Component                               Current      Version     Elapsed Time
Name                                    Status       Number      HH:MM:SS
.
Oracle Server
.
Upgrade Incomplete

PL/SQL procedure successfully completed.
If I push on and run @catuppst.sql, I get this (I've abbreviated as much as I can):

SQL> @catuppst.sql

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP POSTUP_BGN 2013-01-23 6:48:41


PL/SQL procedure successfully completed.


This script will migrate the Baseline data on a pre-11g database
to the 11g database.

begin
*
ERROR at line 1:
ORA-04063: package body "SYS.DBMS_SWRF_INTERNAL" has errors
ORA-06508: PL/SQL: could not find program unit being called:
"SYS.DBMS_SWRF_INTERNAL"
ORA-06512: at line 2

(null) rows created.

[...]

DECLARE
*
ERROR at line 1:
ORA-04063: package body "SYS.DBMS_REGISTRY" has errors
ORA-06508: PL/SQL: could not find program unit being called:
"SYS.DBMS_REGISTRY"
ORA-06512: at line 4

SP2-1506: START, @ or @@ command has no arguments

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP POSTUP_END 2013-01-23 6:48:46


PL/SQL procedure successfully completed.

BEGIN
*
ERROR at line 1:
ORA-04063: package body "SYS.DBMS_REGISTRY" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_REGISTRY"
ORA-06512: at line 2


          IF CatbundleCreateDir(:catbundleLogDir) = 0 THEN
             *
ERROR at line 71:
ORA-06550: line 71, column 14:
PLS-00201: identifier 'CATBUNDLECREATEDIR' must be declared
ORA-06550: line 71, column 11:
PL/SQL: Statement ignored 
Not only can't I query dba_registry, opatch lsinventory shows nothing (because it's a fresh install of 11.2.0.3).

Any suggestions of where I am going wrong, please?

(I am following http://docs.oracle.com/cd/E11882_01/server.112/e23633/upgrade.htm#i1011372 and have checked Upgrade from 10203 to 10204 among others)
  • 1. Re: Out-of-place upgrade going wrong
    Catfive Lander Explorer
    Currently Being Moderated
    PS. If it helps any, I have re-run catalog.sql and catproc.sql and utlrp.sql to no effect.

    If it additionally helps, I was very careful in the new installation only to install those components which we are actually licensed to use (eg, OLAP was turned off). I think the original installation consisted of someone just clicking next all the way through OUI, so a lot of things were installed which shouldn't have been. I thought I had been quite careful in removing those extra components in the original database before attempting this procedure, though).
  • 2. Re: Out-of-place upgrade going wrong
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    If I run dbua, it says 'database isn't mounted'
    Possibly, the environment is pointing to the old O_H and/or SID ?

    Did you run the preupgrade script utlu112i.sql ?

    You need to do another SHUTDOWN and STARTUP after the catupgrd.sql (i.e. before utlu112s.sql)

    Hemant K Chitale
  • 3. Re: Out-of-place upgrade going wrong
    Catfive Lander Explorer
    Currently Being Moderated
    <i>Possibly, the environment is pointing to the old O_H and/or SID ?</i>

    No. Checked and triple-checked, largely because I was determined that I wouldn't accidentally do anything to the running (original) instance.

    <i>Did you run the preupgrade script utlu112i.sql ?</i>

    Yeah, but there was nothing there that was a shop-stopper. Some warnings about existing invalid objects (dba_registry isn't one of them); a warning to purge the recycle bin (which I did); a recommendation to gather dictionary statistics (which I didn't).

    <i>You need to do another SHUTDOWN and STARTUP after the catupgrd.sql (i.e. before utlu112s.sql)</i>

    Makes no difference to my results, I'm afraid.
  • 4. Re: Out-of-place upgrade going wrong
    baskar.l Pro
    Currently Being Moderated
    Hi,
    You need to do another SHUTDOWN and STARTUP after the catupgrd.sql (i.e. before utlu112s.sql)
    
    Makes no difference to my results, I'm afraid. 
    Is that you did

    sqlplus "/as sysdba"
    SQL> STARTUP
    SQL> @utlu112s.sql

    and ran

    SQL> @catuppst.sql

    Baskar.l
  • 5. Re: Out-of-place upgrade going wrong
    baskar.l Pro
    Currently Being Moderated
    Hi,

    Can you also check if this package SYS.DBMS_SWRF_INTERNAL, SYS.DBMS_REGISTRY are in valid state.

    baskar.l
  • 6. Re: Out-of-place upgrade going wrong
    Catfive Lander Explorer
    Currently Being Moderated
    They are clearly not in a valid state. But they are in the original database.
    Sorry, I thought I'd made that clear.
  • 7. Re: Out-of-place upgrade going wrong
    Catfive Lander Explorer
    Currently Being Moderated
    No, the database won't open unless you say startup upgrade.
    So, I originally got the new database into mount state, recover database, alter database open upgrade, then shutdown immediate and startup upgrade. Then I ran the two SQL scripts mentioned in my original post.
  • 8. Re: Out-of-place upgrade going wrong
    baskar.l Pro
    Currently Being Moderated
    Hi,
    Thanks. I was going through the doc Complete Checklist for Manual Upgrades to 11gR2 [ID 837570.1] where after issuing startup we have to run the scripts utlu112s.sql and catuppst.sql.

    i will check again .

    Baskar.l
  • 9. Re: Out-of-place upgrade going wrong
    Catfive Lander Explorer
    Currently Being Moderated
    Well, I'm not sure what point you're making or what you have to check.

    I am running utlu112s and catuppst.sql, though obviously only after first running catupgrd.sql.

    The thing you might possibly be missing is that to run catupgrd, you first have to open your database with STARTUP UPGRADE.

    I was following http://docs.oracle.com/cd/E11882_01/server.112/e23633/upgrade.htm#i1011372 down at the "Manually Upgrading Oracle Database" section, step 9, then step 12, 13 and 15, which produces the 'upgrade incomplete' message; then step 16, which produces the 'dba_registry invalid' errors I posted. Step 17 doesn't change anything.
  • 10. Re: Out-of-place upgrade going wrong
    Catfive Lander Explorer
    Currently Being Moderated
    I think that (partially, as it turns out) de-installing unlicensed components on the original database may have caused this problem.

    I tried doing a data pump export of the original database, and it failed with an error I tracked down in Metalink (I'm sorry, I didn't record the specifics). Turned out there were four tables left behind from OWB and a broken package related to Spatial. I dropped those, and the export worked.

    But instead of using that export, I tried my hot copy technique again.. and this time it worked.

    So I guess the moral of the story is to ensure your source database is 100% functional before you try messing around with any of this stuff.

    Edited by: Catfive Lander on Jan 24, 2013 10:01 PM

    Have remembered one of the specifics: Metalink 1328829.1 (OLAP removal leaves behind DBMS_CUBE_EXP in an invalid state). There was one other similar issue, but I still don't remember the details on that.
  • 11. Re: Out-of-place upgrade going wrong
    652072 Newbie
    Currently Being Moderated
    DBUA is the best approach for upgrade, it does a fantastic job...

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points