This discussion is archived
11 Replies Latest reply: Sep 27, 2013 10:12 PM by 885842 RSS

DBUA taking 12 hours

885842 Newbie
Currently Being Moderated

Hi, im upgrading test databases before we do the production ones, and i have 1 database that is taking over 12 hours to upgrade using DBUA.

 

Im using Aix and currently on 10.2.0.5 going to 11.2.0.3.

 

After running the pre-upgrade script and addressing any issues the only 2 warnings left were:

 

Review any events or trace events and another saying  EM Database Control Repository exists in the database.
.... Direct downgrade of EM Database Control is not supported.  

 

I dont know of any events running and do not use EM Database control so have not done anything regarding these.

 

During the upgrade i get 2 red crosses for upgrading olap catalog and olap analytic workspace

 

After the upgrade everything seems ok but the time will not be acceptable in production.

 

I restored back to 10.2.0.5 and turned off flashback and archiving just in case this made any difference. Ran upgrade again but took the same amount of time.

 

Its a very small database with no other additional features. The other databases upgrade in around 2 hours.

 

Ive had a look in the dbua logs but cant see anything. Any ideas what could be causing this to take so long?

 

Many thanks

 

 


  • 1. Re: DBUA taking 12 hours
    DK2010 Guru
    Currently Being Moderated

    Hi,

     

    You can check this Doc:Checklist to Upgrade the Database to 11gR2 using DBUA (Doc ID 870814.1)

    By performing these steps(checklist) manually, the down time can be minimized

     

    HTH

  • 2. Re: DBUA taking 12 hours
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated

    Where does DBUA take most of the time ? Pl post the contents of the DBUA log. The time taken by an upgrade depends on the number of objects and the features being used in the database.

     

    Pl also review MOS Doc 455744.1

     

    Srini

  • 3. Re: DBUA taking 12 hours
    885842 Newbie
    Currently Being Moderated

    Hi Srini,  here is the contents of the upgrade.xml log (hopefully this is the one you wanted). I didnt notice one specific place where its taking longer than others.

    <RDBMSUP version="11.2.0.3.0">
    <SupportedOracleVersions value="9.2.0, 10.1.0, 10.2.0, 11.1.0, 11.2.0"/>
    <OracleVersion value="10.2.0"/>
    <Database>
    <database Name="TST1A"/>
    <database Version="10.2.0.5.0"/>
    <database Compatibility="10.2.0.5.0"/>
    </Database>
    <FlashbackInfo name="" status="OFF" limit="0 MB" used="0 MB" size="0 MB" reclaim="0 MB" files="0" />
    <InitParams>
    <Update>
    </Update>
    <Migration>
    </Migration>
    <NonHandled>
    <Parameter name="remote_listener"/>
    </NonHandled>
    <Rename>
    </Rename>
    <Remove>
    </Remove>
    </InitParams>

    <Components>
    <Component id ="Oracle Server" type="SERVER" cid="RDBMS" status="VALID">
    <CEP value="{ORACLE_HOME}/rdbms/admin/rdbmsup.sql"/>
    <SupportedOracleVersions value="9.2.0,10.1.0, 10.2.0,11.1.0,11.2.0"/>
    <OracleVersion value ="10.2.0.5.0"/>
    </Component>
    <Component id="JServer JAVA Virtual Machine" cid="JAVAVM" script="?/javavm/install/jvmdbmig.sql" version="10.2.0.5.0" status="VALID">
    </Component>
    <Component id="Oracle XDK for Java" cid="XML" script="?/xdk/admin/xmldbmig.sql" version="10.2.0.5.0" status="VALID">
    </Component>
    <Component id="Oracle Workspace Manager" cid="OWM" script="?/rdbms/admin/owmdbmig.sql" version="10.2.0.5.0" status="VALID">
    </Component>
    <Component id="OLAP Analytic Workspace" cid="APS" script="?/olap/admin/apsdbmig.sql" version="10.2.0.5.0" status="VALID">
    </Component>
    <Component id="OLAP Catalog" cid="AMD" script="?/olap/admin/amddbmig.sql" version="10.2.0.5.0" status="VALID">
    </Component>
    <Component id="EM Repository" cid="EM" script="?/sysman/admin/emdrep/sql/empatch.sql" version="10.2.0.5.0" status="VALID">
    </Component>
    <Component id="Oracle Text" cid="CONTEXT" script="?/ctx/admin/ctxdbmig.sql" version="10.2.0.5.0" status="VALID">
    </Component>
    <Component id="Oracle XML Database" cid="XDB" script="?/rdbms/admin/xdbdbmig.sql" version="10.2.0.5.0" status="VALID">
    </Component>
    <Component id="Oracle Java Packages" cid="CATJAVA" script="" version="10.2.0.5.0" status="VALID">
    </Component>
    <Component id="Oracle interMedia" cid="ORDIM" script="?/ord/im/admin/imdbmig.sql" version="10.2.0.5.0" status="VALID">
    </Component>
    <Component id="Spatial" cid="SDO" script="?/md/admin/sdodbmig.sql" version="10.2.0.5.0" status="VALID">
    </Component>
    <Component id="Data Mining" cid="ODM" script="?/rdbms/admin/odmdbmig.sql" version="10.2.0.5.0" status="VALID">
    </Component>
    <Component id="Expression Filter" cid="EXF" script="?/rdbms/admin/exfdbmig.sql" version="10.2.0.5.0" status="VALID">
    </Component>
    <Component id="Rule Manager" cid="RUL" script="?/rdbms/admin/ruldbmig.sql" version="10.2.0.5.0" status="VALID">
    </Component>
    <Component id="Oracle OLAP API" cid="XOQ" script="?/olap/admin/xoqdbmig.sql" version="10.2.0.5.0" status="VALID">
    </Component>
    </Components>
    <SystemResource>
    <MinFreeSpace>
    <DefaultTablespace value="SYSTEM"> <Datafile name="/andytest/oradata/TST1A/datasystem01.dbf"/> <AdditionalAlloc size="244" unit="MB"/>
    </DefaultTablespace>
    <DefaultTablespace value="UNDOTBS1"> <Datafile name="/andytest/oradata/TST1A/dataundotbs01.dbf"/> <AdditionalAlloc size="375" unit="MB"/>
    </DefaultTablespace>
    <DefaultTablespace value="SYSAUX"> <Datafile name="/andytest/oradata/TST1A/datasysaux01.dbf"/> <AdditionalAlloc size="189" unit="MB"/>
    </DefaultTablespace>
    <RollbackSegment name="SYSTEM" size="90" unit="MB"/>
    </MinFreeSpace>

  • 4. Re: DBUA taking 12 hours
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated

    There should be a separate upgrade log file. Where does DBUA spend a lot of time ?


    Srini

  • 5. Re: DBUA taking 12 hours
    885842 Newbie
    Currently Being Moderated

    Hi Srini, Im afraid i cant recall one part taking a lot longer than the others.i have the following logs - the only upgrade one i can see is the upgrade.xml which i posted. Which other ones are of use?

     

    /u03/app/oracle/cfgtoollogs/dbua/TST1A/upgrade2

    -rw-r-----    1 oracle   dba            1127 Aug 30 00:23 Upgrade_Directive.log

    -rw-r-----    1 oracle   dba            1328 Aug 30 00:23 mapfile.txt

    -rw-r-----    1 oracle   dba             726 Aug 30 00:25 SpaceUsage.txt

    -rw-r-----    1 oracle   dba            7788 Aug 30 00:25 PreUpgradeResults.html

    -rw-r-----    1 oracle   dba             579 Aug 30 00:27 PreUpgrade.log

    -rw-r-----    1 oracle   dba            2850 Aug 30 00:27 Oracle_Text.log

    -rw-r-----    1 oracle   dba        78736032 Aug 30 12:01 Oracle_Server.log

    -rw-r-----    1 oracle   dba            4950 Aug 30 12:09 PostUpgrade.log

    -rw-r-----    1 oracle   dba          228141 Aug 30 12:09 trace.log

    -rw-r-----    1 oracle   dba           81066 Aug 30 12:09 sqls.log

    -rw-r-----    1 oracle   dba            3948 Aug 31 00:43 upgrade.xml

     

    /u03/app/oracle/cfgtoollogs/dbua/logs

    -rw-r-----    1 oracle   dba            2028 Aug 30 00:15 PreUpgradeResults.html

    -rw-r-----    1 oracle   dba           45987 Aug 30 00:16 trace.log

    -rw-r-----    1 oracle   dba          328655 Aug 30 00:16 sqls.log

    -rw-r-----    1 oracle   dba             102 Aug 30 00:29 Welcome_TST1A.txt

  • 6. Re: DBUA taking 12 hours
    885842 Newbie
    Currently Being Moderated

    Hi Srini, just to add, i went through and compared one of the upgrade.xml files for another database that was upgraded and took only 2 hours.

     

    The one that is taking 12 hours has the following additional components :

     

    <Components>

    <Component id ="Oracle Server" type="SERVER" cid="RDBMS" status="VALID">

    <CEP value="{ORACLE_HOME}/rdbms/admin/rdbmsup.sql"/>

    <SupportedOracleVersions value="9.2.0,10.1.0, 10.2.0,11.1.0,11.2.0"/>

    <OracleVersion value ="10.2.0.5.0"/>

    </Component>

    <Component id="JServer JAVA Virtual Machine" cid="JAVAVM" script="?/javavm/insta

    ll/jvmdbmig.sql" version="10.2.0.5.0" status="VALID">

    </Component>

    <Component id="Oracle XDK for Java" cid="XML" script="?/xdk/admin/xmldbmig.sql"

    version="10.2.0.5.0" status="VALID">

    </Component>

    <Component id="Oracle Workspace Manager" cid="OWM" script="?/rdbms/admin/owmdbmi

    g.sql" version="10.2.0.5.0" status="VALID">

    </Component>

    <Component id="OLAP Analytic Workspace" cid="APS" script="?/olap/admin/apsdbmig.

    sql" version="10.2.0.5.0" status="VALID">

    </Component>

    <Component id="OLAP Catalog" cid="AMD" script="?/olap/admin/amddbmig.sql" versio

    n="10.2.0.5.0" status="VALID">

    </Component>

    <Component id="EM Repository" cid="EM" script="?/sysman/admin/emdrep/sql/empatch

    .sql" version="10.2.0.5.0" status="VALID">

    </Component>

    <Component id="Oracle Text" cid="CONTEXT" script="?/ctx/admin/ctxdbmig.sql" vers

    ion="10.2.0.5.0" status="VALID">

    </Component>

    <Component id="Oracle XML Database" cid="XDB" script="?/rdbms/admin/xdbdbmig.sql

    " version="10.2.0.5.0" status="VALID">

    </Component>

    <Component id="Oracle Java Packages" cid="CATJAVA" script="" version="10.2.0.5.0

    " status="VALID">

    </Component>

    <Component id="Oracle interMedia" cid="ORDIM" script="?/ord/im/admin/imdbmig.sql

    " version="10.2.0.5.0" status="VALID">

    </Component>

    <Component id="Spatial" cid="SDO" script="?/md/admin/sdodbmig.sql" version="10.2

    .0.5.0" status="VALID">

    </Component>

    <Component id="Data Mining" cid="ODM" script="?/rdbms/admin/odmdbmig.sql" versio

    n="10.2.0.5.0" status="VALID">

    </Component>

    <Component id="Expression Filter" cid="EXF" script="?/rdbms/admin/exfdbmig.sql"

    version="10.2.0.5.0" status="VALID">

    </Component>

    <Component id="Rule Manager" cid="RUL" script="?/rdbms/admin/ruldbmig.sql" versi

    on="10.2.0.5.0" status="VALID">

    </Component>

    <Component id="Oracle OLAP API" cid="XOQ" script="?/olap/admin/xoqdbmig.sql" ver

    sion="10.2.0.5.0" status="VALID">

    </Component>

    </Components>

     

    The database was created with DBCA so am not sure if all these additional components are need or not. Could it be right that DBUA will take so long?

  • 7. Re: DBUA taking 12 hours
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated

    These components should be present in the other database too - the "<Component id ="Oracle Server" type="SERVER" cid="RDBMS" status="VALID">" is the actual database binaries, - this should be listed in the other database too. You stated that you know which part of the upgrade is taking the longest - how did you determine that ? Check Oracle_Server.log file for timestamps to determine where it is spending time. Is the server heavily loaded ? what are the SGA sizes of the two databases ? In most cases, DBUA should complete within 4 hours.

     

    HTH
    Srini

  • 8. Re: DBUA taking 12 hours
    885842 Newbie
    Currently Being Moderated

    Hi Srini, the SGA sizes are very similar (and small), see below:

     

    Fixed Size 2224048

    Variable Size 343933008

    Database Buffers 272629760

    Redo Buffers 3362816

     

    I had a look at the oracle_server_file which was massive and doesnt give any easy time stamps.

     

    So I restored the database back to 10.2.0.5 and ran the DBUA upgrade again keeping a close eye on it.

     

    UPGRADING ORACLE SERVER - this took just under 3 hours - at this point the the progress bar was at 77% which seems ok. After this it started going downhill:

     

    The upgrade of JSERVER JAVA virtual machine and Oracle XDK for Java took a combined 3 hours . Progress bar moved to 79%

     

    The upgrades of OLAP analytic workspace and olap catalog both had red crosses (as did datamining and OLAP API) - does this mean they failed or were skipped ?

     

    EM repository took an hour and a half, Oracle XML database took another hour and a half and oracle intermedia took 2 hours.

     

    Altogether the total time was 13 hours.

     

    There are no ORA- messages in the alert log. Can log into the database finand everthing looks ok (although there are some invalid objects in OLAPSYS)

     

    The database is a really simple test db with nothing special about it.

     

    Any thoughts?

     

    Many thanks

  • 9. Re: DBUA taking 12 hours
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated

    What other systems/databases are running on this server ? I would suggest you double or triple the SGA size and retry,

     

    HTH
    Srini

  • 10. Re: DBUA taking 12 hours
    Helios-GunesEROL Oracle ACE
    Currently Being Moderated

    Hi;

     

    Possible reason for slow dbua response

     

    1. Unsufficient hw source

    2. Possibly it compile invalid objects

     

    PS: Is there any error message at alert.log?

     

    For can be sure please check dbua log for define issue


    Regard

    Helios

  • 11. Re: DBUA taking 12 hours
    885842 Newbie
    Currently Being Moderated

    Hi Srini, the reason it took so long was actually due to a number of factors, all related to the fact that the machine has low memory.

     

    The reason it took so much longer than the other databases is because it has a lot more components than the others.

     

    I managed to halve the time of the upgrade to 6 hours by shutting all the other DB's down running on the server and the doubling the sga target.

     

    I also found that there was another 3rd party application running on the box which was taking up alot of the CPU when the java upgrade was running so i ended this.

     

    Happy with the the time it now takes.

     

    Many thanks

Legend

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