Oracle Upgrade -- Pre-Upgrade 10.2.0.4 to 11.2.0.1

[joo_migueldimas]

Hello,

I want to do an Oracle Upgrade to version 11.2g!

At this point I did some things (in Windows Server 2003 X64):

1-) Install Oracle 10.2.0.1 (DVD Media);

2-) Installation of Oracle patchset 10.2.0.4 (note 871735);

3-) Installation of interim / last generics patch (note 1137346);

4-) Installation of SAP ECC 6.0 SR3 by SAPInst

5-) After SAP installation I did all the corrections in Oracle parameters as it mentioned in note 830576.

As you see above, all the SAP system was well installed!

But now I want to do the Oracle Upgrade process to the last version, Oracle DB 11.2g (11.2.0.1)... and so I´m following the Upgrade guide "Upgrade to Oracle Database 11g Release 2 (11.2) Windows.pdf" with some SAP notes for Upgrade process.

So I´m at this point in planning chapter where is recommended to check so things in actual database, one of this checks are running some SQL-scripts to perform an Oracle database upgrade with DBUA to release 11.2 (as mentioned in note 1431793 - Oracle 11.2.0: Upgrade Scripts)

I ran two Pre-Scripts, the @pre_upgrade_status.sql and @pre_upgrade_tasks.sql but both outputs showed some warnings and they are worrying me... so what I ask to you is some kind of help in analyse of them and tell me if this issues are relevant for the upgrading process and if so, what you recommend me to do to correct their!

Next I will post here this two log scripts... they are long!!!

(...)

[stefan_koehler · Accepted solution]

Hello João,

what I ask to you is some kind of help in analyse of them and tell me if this issues are relevant for the upgrading process

i have done several upgrade of an 10.2.0.4 database to 11.2.0.1 and these warnings are completely normal (as far as it is possible to read that unformatted text). The only "WARNING", that i would consider is the following:

Parameters: WARNING: --> "shared_pool_size" needs to be increased to at least 472 MB

So extend the shared pool memory area and start the upgrade. Good luck!

Regards

Stefan

[joo_migueldimas · Accepted solution]

Hello Stefan,

First at all, thank you for your help...

It´s weird that warning in that parameter of shared_pool_size because the actually value of it is 483026534

I checked that at Brtools option - Show database parameters. You can see it below:

List of database parameters

Parameter                              Modif.    Spfile    Inst.    Deft.     Value
shared_pool_size                  both       yes        *         no       486539264 -> 483026534

What do you have to say about this? Could you answer please...

Kind regards,

João Dimas - Portugal

[volker_borowski2 · Accepted solution]

>the actually value of it is 483026534

So what's wrong ?

483026534 / 1024 (K) / 1024 (M) = 460,65 MB < 472 MB

Set the correct value and go ahead.

Volker

[former_member204746 · Accepted solution]

refer to SAP note 690241, look near the end:

CPUs Shared_Pool_Size New Shared Pool Size

4 500M 650M

6 1G 1G

10 1G 1.175G

32 2G 2.75G

64 2G 2.75G

with 4 CPU, set shared_pool_size to 650M

[joo_migueldimas · Accepted solution]

Hello Volker Borowski

Sorry Volker... can you tell what´s wrong!?... because I can´t change the value, it show me an error...:

BR0280I BRSPACE time stamp: 2010-08-06 18.33.53
BR0663I Your choice: 'c'
BR0259I Program execution will be continued...
BR0280I BRSPACE time stamp: 2010-08-06 18.33.54
BR0301E SQL error -2097 at location BrSqlExecute-1, SQL statement:
'/* BRSPACE */ alter system set shared_pool_size = 500000000 scope = both'
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-04033: Insufficient memory to grow pool
BR1017E Execution of SQL statement 'alter system set shared_pool_size = 500000000 scope = both' failed

BR0669I Cannot continue due to previous warnings or errors - you can go back to repeat the last action

BR0280I BRSPACE time stamp: 2010-08-06 18.33.54
BR0671I Enter 'b[ack]' to go back, 's[top]' to abort:

I changed the value of sga_max_size 1002438656 -> 1110000000 because I guess this was a requirement to get change the shared_pool_size parameter, but even so I can´t change it!

Can you help me to know why...?!

Thank you...

Best regards,

João Dimas - Portugal

[joo_migueldimas · Accepted solution]

Hello Eric Brunelle,

Thank you for the information... but in my case the server it only have 2 CPU´s with 6GB of RAM!

Best regards,

João Dimas - Portugal

[former_member204746 · Accepted solution]

search te web with keywords:

ORA-04033: Insufficient memory to grow pool

[volker_borowski2 · Accepted solution]

Hi,

you do not have enough free memory to apply the increase of shared_pool.

When you do it with "scope=both" it means, "do it now (for the runnning DB) and store it for later restart".

But you do not have reserved enough memory to fullfill the request.

If you would have selected "scope=spfile", it would have made the parameter active with the next restart.

The increase of sga_max_size requires a restart as far as I remember.

If you can not take the db offline now, reduce db_cache_size first to get the 20 MB you need from there.

Hope this helps

Volker

[joo_migueldimas · Accepted solution]

Hello Eric Brunelle,

It has been solved! What I did was increase the value of parameter sga_max_size 1002438656 to 1110000000 and after I restarted the SAP system and Oracle Database and after that I've managed to increase the parameter value of shared_pool_size to 500000000 with command SQL Plus:

alter system set shared_pool_size=500000000 scope=spfile

So thank you for your help Eric

Best regards

João Dimas - Portugal

[joo_migueldimas · Accepted solution]

Hello Volker Borowski,

Ohh allrigh!! I didn´t know this... Thank you very much for that usefull information, maybe I will use that information in future!!

Best regards,

João Dimas - Portugal

[joo_migueldimas · Accepted solution]

Hello again...

Stefan can you help me again now with other problem... Yesterday I opened a post but I put a wrong subject to that message/post... so can you take a look?... once you've did several Oracle Upgrades:

Thank you

Kind regards,

João Dimas - Portugal

[former_member204746 · Accepted solution]

Joao,

please start a new topic for that new problem.

I see that Volker Borowski gave you the final solution, so, I have changed his point status to "Solved problem (10)".

please read http://www.sdn.sap.com/irj/sdn/crphelp to learn how "Contribution Recognition" works.

[Former Member]

I am not sure, that its right(and help to TC), but in "Upgrade guide" for

Oracle 10.2 I saw recomendation "Before upgrade to Oracle11, you must upgrade from Oracle 10 first version to Oracle 10.2.0.5)

[joo_migueldimas]

Hello Dmitriy,

As you may have noticed, this thread is already old (2010), but I'll answer you anyway... I believe you want to upgrade your oracle database from 10.2 to 11.2, am I right? Because that was my scenario here...!

What I read in upgrade guide was not what you mentioned here! You can read in Prerequisites chapter the following recommendation:

Prerequisites

In the following special cases you need to obtain more information:

If the source database runs Oracle 10.2.0.2, you need to first update the source database to at least patch set 10.2.0.5. You can then perform the upgrade from 10.2.0.4 or 10.2.0.5 to 11.2.0.2 or 11.2.0.3, as described in this guide.

So... as you can read above is not an obligation to do the patchset update to 10.2.0.5 before doing the upgrade to oracle 11.2... you can do this from the version 10.2.0.4! But, if otherwise, your database is an oracle 10.2.0.2 you must perform the patchset update to 10.2.0.4 or 10.2.0.5!

I think I made myself clear...

Thank you,

JDimas

[joo_migueldimas]

PRE_UPGRADE_STATUS.log:

                              
**********************************************************************                              
Database:                                                                                
**********************************************************************                              
--> name          : PRD                                                                             
--> version       : 10.2.0.4.0                                                                      
--> compatible    : 10.2.0                                                                          
--> blocksize     : 8192                                                                            
--> platform      : Microsoft Windows x86 64-bit(12)                                                
--> timezone file : V4                                                                              
--> Log mode      : NOARCHIVELOG                                                                    
--> Flashback mode: NO                                                                                
--> Database Vault: FALSE                                                                           
--> RAC system    : FALSE                                                                           
--> SAP BW system : FALSE                                                                                
--> SAP Unicode system       : TRUE                                                                 
--> DB Character Set         : UTF8                                                                 
--> National DB Character Set: UTF8                                                                 
.                                                                                
**********************************************************************                              
Instance:                                                                                
**********************************************************************                              
--> Instance Number : 1                                                                             
--> Instance Name   : prd                                                                           
--> Version         : 10.2.0.4.0                                                                    
--> Host Name       : SAPORA                                                                        
--> Database Status : OPEN                                                                          
--> Instance Role   : PRIMARY_INSTANCE                                                              
.                                                                                
**********************************************************************                              
Database Components:                                                                                
**********************************************************************                              
.... Component ID                   Version     Status                                              
.... CATALOG                        10.2.0.4.0  VALID                                               
.... CATPROC                        10.2.0.4.0  VALID                                               
.                                                                                
**********************************************************************                              
Database Flashback Status / Restore Points:                                                         
**********************************************************************                              
--> Flashback mode            : NO                                                                  
--> db_recovery_file_dest     :                                                                     
--> db_recovery_file_dest_size: 0 M                                                                 
Guaranteed  Time                  Name                                                              
----------  ----                  ----                                                              
.                                                                                
**********************************************************************                              
Database Maintenance Jobs:                                                                          
**********************************************************************                              
JOB_NAME                       Owner                          Status                                
--------                       -----                          ------                                
FGR$AUTOPURGE_JOB              SYS                            FALSE                                 
GATHER_STATS_JOB               SYS                            FALSE                                 
MGMT_CONFIG_JOB                ORACLE_OCM                     TRUE                                  
MGMT_STATS_CONFIG_JOB          ORACLE_OCM                     TRUE                                  
AUTO_SPACE_ADVISOR_JOB         SYS                            TRUE                                  
PURGE_LOG                      SYS                            TRUE                                  
.                                                                                
**********************************************************************                              
Invalid Objects:                                                                                
**********************************************************************                              
WARNING: ----> Invalid Objects in SYS schema                                                        
.... USER SYS has 1 INVALID object(s).                                                              
WARNING: ----> Invalid Objects in any other schema                                                  
.... USER PUBLIC has 1 INVALID object(s).                                                                                
List of invalid objects:                                                                            
PUBLIC.DBA_REGISTRY_DATABASE (SYNONYM)                                                              
SYS.DBA_REGISTRY_DATABASE (VIEW)                                                                    
.                                                                                
**********************************************************************                              
SAP specific Warnings                                                                                
**********************************************************************                              
WARNING: ----> Invalid objects in SYS schema                                                        
               Check why these objects are invalid before database upgrade.                         
               Invalid SYS objects can cause the database upgrade to fail.                          
WARNING: ----> Invalid objects in other schema                                                      
               Check why these objects are invalid before database upgrade.                         
WARNING: ----> Table(s) PLAN_TABLE exist(s) in SAP schema.                                          
               The table(s) will get dropped after the upgrade and                                  
               recreated with new structure.                                                        
               SAPSR3.PLAN_TABLE                                                                    
WARNING: ----> AWR retention is too small.                                                          
               Current AWR retention: 7 days. Recommended: 42 or higher                             
               See SAP note 1326067.                                                                
.                                                                                
**********************************************************************                              
FINISHED - Oracle Database 11.2 Pre-Upgrade Information Tool for SAP                                
**********************************************************************

[joo_migueldimas]

PRE_UPGRADE_TASKS.log:

SQL> @pre_upgrade_tasks.sql

PRE-UPGRADE Tasks: === START ===

2010-08-05 18:24:13

Recompiling invalid objects

This reduces the number of invalid objects as much as possible.

COMP_TIMESTAMP UTLRP_BGN 2010-08-05 18:24:13

DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid

DOC> objects in the database. Recompilation time is proportional to the

DOC> number of invalid objects in the database, so this command may take

DOC> a long time to execute on a database with a large number of invalid

DOC> objects.

DOC>

DOC> Use the following queries to track recompilation progress:

DOC>

DOC> 1. Query returning the number of invalid objects remaining. This

DOC> number should decrease with time.

DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);

DOC>

DOC> 2. Query returning the number of objects compiled so far. This number

DOC> should increase with time.

DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;

DOC>

DOC> This script automatically chooses serial or parallel recompilation

DOC> based on the number of CPUs available (parameter cpu_count) multiplied

DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).

DOC> On RAC, this number is added across all RAC nodes.

DOC>

DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel

DOC> recompilation. Jobs are created without instance affinity so that they

DOC> can migrate across RAC nodes. Use the following queries to verify

DOC> whether UTL_RECOMP jobs are being created and run correctly:

DOC>

DOC> 1. Query showing jobs created by UTL_RECOMP

DOC> SELECT job_name FROM dba_scheduler_jobs

DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';

DOC>

DOC> 2. Query showing UTL_RECOMP jobs that are running

DOC> SELECT job_name FROM dba_scheduler_running_jobs

DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';

DOC>#

COMP_TIMESTAMP UTLRP_END 2010-08-05 18:24:15

DOC> The following query reports the number of objects that have compiled

DOC> with errors (objects that compile with errors have status set to 3 in

DOC> obj$). If the number is higher than expected, please examine the error

DOC> messages reported with each object (using SHOW ERRORS) to see if they

DOC> point to system misconfiguration or resource constraints that must be

DOC> fixed before attempting to recompile these objects.

DOC>#

0

DOC> The following query reports the number of errors caught during

DOC> recompilation. If this number is non-zero, please query the error

DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors

DOC> are due to misconfiguration or resource constraints that must be

DOC> fixed before objects can compile successfully.

DOC>#

0

Purging Recyclebin

This reduces the time needed for upgrading the database.

Truncating SYS.AUD$

This reduces the time needed for upgrading the database.

Gathering Oracle Dictionary Statistics

This reduces the time needed for upgrading the database.

Running Pre-Upgrade-Information Tool utlu112i.sql

This is a mandatory task for manual database upgrades.

This tool is also run in pre_upgrade_status.sql.

Oracle Database 11.2 Pre-Upgrade Information Tool 08-05-2010 18:24:45

Script Version: 11.2.0.1.0 Build: 003

.

Database:

--> name: PRD

--> version: 10.2.0.4.0

--> compatible: 10.2.0

--> blocksize: 8192

--> platform: Microsoft Windows x86 64-bit

--> timezone file: V4

.

Tablespaces: [make adjustments in the current environment]

--> SYSTEM tablespace is adequate for the upgrade.

.... minimum required size: 905 MB

--> PSAPUNDO tablespace is adequate for the upgrade.

.... minimum required size: 84 MB

--> SYSAUX tablespace is adequate for the upgrade.

.... minimum required size: 210 MB

--> PSAPTEMP tablespace is adequate for the upgrade.

.... minimum required size: 61 MB

.

Flashback: OFF

Update Parameters: [Update Oracle Database 11.2 init.ora or spfile]

Note: Pre-upgrade tool was run on a lower version 64-bit database.

--> If Target Oracle is 32-Bit, refer here for Update Parameters:

-- No update parameter changes are required.

.

--> If Target Oracle is 64-Bit, refer here for Update Parameters:

WARNING: --> "shared_pool_size" needs to be increased to at least 472 MB

.

Renamed Parameters: [Update Oracle Database 11.2 init.ora or spfile]

-- No renamed parameters found. No changes are required.

.

Obsolete/Deprecated Parameters: [Update Oracle Database 11.2 init.ora or spfile]

--> remote_os_authent 11.1 DEPRECATED

--> background_dump_dest 11.1 DEPRECATED replaced by "diagnostic_dest"

--> user_dump_dest 11.1 DEPRECATED replaced by "diagnostic_dest"

.

Components: [The following database components will be upgraded or installed]

--> Oracle Catalog Views [upgrade] VALID

--> Oracle Packages and Types [upgrade] VALID

.

Miscellaneous Warnings

WARNING: --> Database is using a timezone file older than version 11.

.... After the release migration, it is recommended that DBMS_DST package

.... be used to upgrade the 10.2.0.4.0 database timezone version

.... to the latest version which comes with the new release.

.

Recommendations

Oracle recommends gathering dictionary statistics prior to

upgrading the database.

To gather dictionary statistics execute the following command

while connected as SYSDBA:

EXECUTE dbms_stats.gather_dictionary_stats;

Oracle recommends removing all hidden parameters prior to upgrading.

To view existing hidden parameters execute the following command

while connected AS SYSDBA:

SELECT name,description from SYS.V$PARAMETER WHERE name

LIKE '\_%' ESCAPE '\'

Changes will need to be made in the init.ora or spfile.

Oracle recommends reviewing any defined events prior to upgrading.

To view existing non-default events execute the following commands

while connected AS SYSDBA:

Events:

SELECT (translate(value,chr(13)||chr(10),' ')) FROM sys.v$parameter2

WHERE UPPER(name) ='EVENT' AND isdefault='FALSE'

Trace Events:

SELECT (translate(value,chr(13)||chr(10),' ')) from sys.v$parameter2

WHERE UPPER(name) = '_TRACE_EVENTS' AND isdefault='FALSE'

Changes will need to be made in the init.ora or spfile.

PRE-UPGRADE Tasks: === FINISHED ===

SQL> spool off