Oracle Upgrade - Pre-upgrade scripts - Errors (invalid objects)

[joo_migueldimas]

Hello,

I´m doing an Oracle database upgrade (from 10.2.0.5 to 11.2.0.4) in a windows server 2003 x64.

Now I´m facing one problem, I have some doubts in how to solve/overcome some errors/warnings when I run the pre_upgrade scripts (1431793 - Oracle 11.2.0: Upgrade Scripts) before I run the dbua wizard to perform the real upgrade of oracle version!

The output of pre_upgrade_status and pre_upgrade_tasks shows some warnings and errors:

**********************************************************************                         
 


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:                              
 


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


.                                                                                              
 


                                                                                               
 


--> 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]  INVALID                                            
 


.                                                                                              
 


**********************************************************************                         
 


Miscellaneous Warnings                                                                         
 


**********************************************************************                         
 


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


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


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


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


WARNING: --> Database contains INVALID objects prior to upgrade.                               
 


.... The list of invalid SYS/SYSTEM objects was written to                                     
 


.... registry$sys_inv_objs.                                                                    
 


.... The list of non-SYS/SYSTEM objects was written to                                         
 


.... registry$nonsys_inv_objs.                                                                 
 


.... Use utluiobj.sql after the upgrade to identify any new invalid                            
 


.... objects due to the upgrade.                                                               
 


.... USER SYS has 19 INVALID objects.                                                          
 


WARNING: --> JOB_QUEUE_PROCESS value must be updated                                           
 


.... Your current setting of "1" is too low.                                                   
 


                                                                                               
 


.... Starting with Oracle Database 11g Release 2 (11.2), setting                               
 


.... JOB_QUEUE_PROCESSES to 0 causes both DBMS_SCHEDULER and                                   
 


.... DBMS_JOB jobs to not run. Previously, setting JOB_QUEUE_PROCESSES                         
 


.... to 0 caused DBMS_JOB jobs to not run, but DBMS_SCHEDULER jobs were                        
 


.... unaffected and would still run. This parameter must be updated to                         
 


.... a value greater than 4  (default value is 1000) prior to upgrade.                         
 


.... Not doing so will affect the running of utlrp.sql after the upgrade

I also run the following SQL command and it show me 19 invalid objects:

select object_name from dba_objects where status='INVALID';

My doubts... are how can I correct this invalid objects!? The upgrade guide and the sap notes doesn´t describe any details about it! Can you guide me please and help me to understand how to correct this invalid objects before I run the DBUA upgrade!?

Thank you,

João Dimas

[francois_keen]

Hello

it has been a while I havent done an oracle upgrade so I cannot say 100% sure if I also had invalid objects before an upgrade.

I remember that the invalid objects used to be recompiled in the pre_upgrade_tasks.sql.

Also invalid objects can be fixed after having applied an SBP thanks to @@rdbms/admin/utlrp.sql

I saw the objects names you listed and they are familiar and in my case I had them actually post upgrade (from 11.2.0.2 to 11.2.0.3) - the sequence I followed was  : install 11.2.0.x binaries, apply the SBP binary on 11.2.0.x, do the oracle upgrade steps ( i didnt have invalid objects pre-upgrade), after dbua was done, run the catsbp.sql , I had mostly the same invalid objects as you, ran utlrp.sql, and they were no more invalid objects

have you ever ran utlrp.sql as part of a post SBP step on your 10.2.0.x DB? Maybe this will help you address your objects pre-upgrade....

Cheers

Francois

[joo_migueldimas]

Hello Francois,

Hum... I applied all the patches (interim patches) before, the latest SBP for the new 11.2.0.4 version and also the generic patches (sap note 1949250 - Oracle 11.2.0: Patches / Patch collections for 11.2.0.4).

When I run the command opatch lsinventory -all I can see all the SBP´s and generic patches applied in the 11.2g version, as you can see next:

C:\oracle\SWO\11204\OPatch>opatch lsinventory -all
========================================================
GENERIC OPATCH VERSION - FOR USE IN SAP ENVIRONMENT ONLY
========================================================
Oracle Interim Patch Installer version 11.2.0.3.7
Copyright (c) 2013, Oracle Corporation.  All rights reserved.


Oracle Home       : C:\oracle\SWO\11204
Central Inventory : C:\Program Files\Oracle\Inventory
   from           : n/a
OPatch version    : 11.2.0.3.7
OUI version       : 11.2.0.4.0
Log file location : C:\oracle\SWO\11204\cfgtoollogs\opatch\opatch2015-12-23_16-20-56PM_1.log

Lsinventory Output file location : C:\oracle\SWO\11204\cfgtoollogs\opatch\lsinv\lsinventory2015-12-23_16-20-56PM.txt

--------------------------------------------------------------------------------
List of Oracle Homes:
  Name          Location
   SWO102         C:\oracle\SWO\102
   SWO11204         C:\oracle\SWO\11204

Installed Top-level Products (1):

Oracle Database 11g                                                  11.2.0.4.0
There are 1 product(s) installed in this Oracle Home.


Interim patches (1) :

Patch  21821802     : applied on Wed Dec 23 16:20:02 GMT 2015
Unique Patch ID:  19435369
Patch description:  "WINDOWS DB BUNDLE PATCH 11.2.0.4.20:(21821802)"
   Created on 7 Oct 2015, 06:35:47 hrs
   Bugs fixed:
     16809786, 17716305, 17348614, 16382883, 18125929, 18246045, 17835048
     17545847, 16042673, 18096714, 21691487, 16543190, 17258090, 17214497
     17618819, 17610798, 16954950, 20368850, 19358317, 14800956, 18098207
     16731148, 17006570, 14764840, 14458319, 18191542, 17973301, 19359219
     14543350, 16798862, 19168690, 16878635, 16571443, 17820195, 17215306
     17842825, 18441944, 13955826, 19188927, 19500064, 18084625, 17336871
     17201159, 17001914, 16685417, 17311728, 18262334, 18729166, 16194160
     20995001, 18182018, 16494615, 14046443, 16434236, 17927204, 14354737
     11883252, 18282562, 16903536, 18842982, 19126146, 19532017, 17238511
     17602269, 17936109, 20014326, 19181525, 17080436, 17265217, 17912217
     20127071, 17359610, 19455741, 18419770, 17925209, 17838476, 18331812
     16399083, 18966843, 17853355, 13498382, 16065166, 17493764, 18034737
     14146298, 19220183, 18091059, 18092127, 9670413, 17987366, 19393542
     16187992, 17156148, 20951527, 17336898, 18689530, 20110156, 17313525
     19558324, 18464784, 20245930, 16281493, 10231702, 20527770, 18882642
     14285317, 15832129, 18199537, 17401353, 17484712, 19949371, 20032370
     17344412, 13949321, 15986647, 21191575, 18681683, 16384983, 14034426
     10052729, 20771003, 20194181, 16870214, 18633374, 17332343, 16863422
     18134680, 16613232, 18673304, 20825533, 18111216, 16206997, 17394950
     17785632, 17186905, 17397545, 17325413, 16832076, 19479503, 17258083
     17764053, 17202639, 17089344, 13991403, 16542886, 18766012, 17159489
     20294666, 18893947, 16901385, 17564992, 18284763, 17754782, 18191164
     19606562, 14761365, 18160822, 18084873, 16545319, 16579084, 17174582
     18641461, 18306996, 16994839, 16043574, 14378120, 17752995, 12608451
     18199185, 17865671, 16594015, 18520351, 17587063, 20074391, 13829543
     18232462, 17570606, 17877323, 20613398, 17231779, 17086446, 17484731
     17019086, 17238586, 18261183, 16633319, 16956380, 17651484, 17769597
     16770590, 19690653, 16470836, 17824637, 20175174, 18328509, 12905058
     18767554, 17921190, 18331850, 16943198, 18663316, 17898325, 17640316
     12758971, 16198143, 18765602, 16343493, 18554871, 18189036, 14693336
     18143836, 17551261, 17501491, 17591148, 18277454, 19272663, 16875449
     16863642, 17614134, 17775506, 17296856, 14255128, 17155238, 16249829
     19211724, 17164243, 18244962, 17947785, 20400092, 18508710, 17082983
     18235390, 14320415, 18674465, 14602788, 20583364, 14657740, 18914624
     16657901, 15913355, 17430165, 17282229, 18428146, 17165204, 14044260
     16410570, 17622427, 17501296, 17172091, 16315398, 18409441, 18000422
     14678924, 18472224, 19896336, 18868646, 18399991, 20091753, 17896018
     19051460, 12897813, 16683112, 14133975, 17336889, 17721778, 17798953
     12646133, 17031322, 12928592, 18841764, 16538760, 18241194, 18325460
     19478594, 18024745, 17532816, 13609098, 15861775, 18456514, 16360112
     19784663, 18362222, 18709496, 17851160, 17046460, 16422541, 18384391
     20502905, 18272135, 12611721, 17242746, 14525998, 18999857, 20532070
     17781991, 17694209, 16833527, 18846036, 17227277, 18886413, 19201866
     17050798, 19540182, 20560611, 18964939, 8886629, 17510275, 20331945
     17068448, 17357979, 19245018, 18819257, 20730170, 7533808, 17783588
     16296213, 14275161, 12747740, 17393683, 16069901, 17405605, 19601228
     17449815, 17389192, 17786518, 19403858, 20105012, 18352846, 18316692
     18317531, 18673342, 17649265, 18334586, 19152705, 17232014, 16818892
     13823394, 19955755, 17889549, 19559495, 16988311, 17883081, 17843489
     20898010, 16721594, 17387214, 18088509, 17722664, 19703301, 17284817
     17040527, 17059927, 16989407, 18414137, 18353141, 17390160, 18296772
     21091431, 19234907, 16314254, 17572525, 19438874, 16668584, 20777150
     20949558, 16613964, 19012893, 16702217, 16989630, 17936349, 18231837
     18193833, 18348155, 18024089, 18430495, 19289642, 17785870, 20412068
     17811789, 14015842, 13744436, 20094984, 14692762, 17908541, 19879746
     17546973, 17452841, 18436647, 14503359, 13077335, 19241857, 14106803
     17889583, 20544696, 19006849, 17799716, 18685209, 17993995, 17363999
     17025461, 18691080, 18641451, 18273830, 16394467, 17767676, 20440643
     16524926, 18888066, 18708921, 14368995, 18405517, 15993436, 17465741
     20724902, 14280240, 17892268, 19885321, 17890099, 18051556, 18961101
     17391726, 19915271, 16285691, 17037130, 19269405, 16299727, 16837274
     18264060, 17489922, 16188701, 20393340, 16075609, 20043415, 18724012
     17446237, 18436307, 19720346, 18241710, 18946768, 17336884, 16450169
     17551674, 17726838, 16268425, 17518652, 17922254, 17441661, 17684143
     20317221, 17634921, 17546761, 20315294, 18053580, 17435488, 17852463
     9756271, 17040764, 14010183, 16837842, 17588480, 16228604, 17237521
     17042658, 17346671, 18202441, 15986311, 18247991, 20235486, 17805316
     15917869, 19791273, 17848897, 18641419, 18828868, 21245437, 19192715
     17577218, 18730542, 17208934, 16919631, 17570240, 17648596, 17352230
     17736165, 20114174, 18890385, 19441649, 18346135, 17299889, 17306264
     18554763, 18482502, 16698971, 17655634, 14764829, 17611362, 19488514
     19000463, 14851828, 16206882, 16934803, 20657441, 18704244, 18120545
     17551699, 17621643, 17279227, 19461687, 17483479, 20558005, 20246071
     17945983, 16199543, 21080143, 17402822, 18417036, 17308789, 19128723
     16344544, 18673325, 20925795, 19835650, 20844426, 16618694, 17741995
     17006183, 11733603, 18339044, 13651346, 14054676, 18952577, 17644091
     18665660, 21821802, 16491477, 17617815, 21059919, 18135678, 19267095
     17376318, 18061914, 17088068, 18280356, 20141091, 18779636, 16692232
     16850630, 19689979, 14580303, 18471685, 18223971, 20340620, 18068871
     18260550, 18508861, 16345970, 17443671, 19727057, 14392595, 18296644
     17957017, 15990359, 16403344, 19615136, 16759171, 14231204, 18006980
     16595641, 17816865, 13364795, 17365043, 18856999, 17617807, 18009564
     20067212, 19543439, 16472716, 18948524, 13944971, 13853126, 17291347
     18315328, 17531342, 16849620, 17205719, 19361710, 18180390, 20073979
     18440047, 17267114, 17951124, 13984192, 14176370, 17082359, 18700935
     18962892, 18154779, 19000876, 17019356, 14000767, 17787259, 13645875
     20218012, 21222147, 18146463, 18384537, 18304693, 20194732, 17184721
     16943711, 20448824, 17612828, 17375354, 19197175, 17393915, 16667538
     17955615, 16980342, 16825679, 17571306, 17477958, 19472320, 17374271
     19205169, 18230522, 14084247, 19601468, 19524384, 17019345, 12578873
     18674047, 18280813, 18614717, 18080511, 14565184, 17389314, 17279525
     18226143, 17614227, 18094246, 19873377, 18018515, 20398963, 17762296
     17405302, 18228645, 17583959, 20004087, 17270418, 16306373, 18493178
     18762750, 21091518, 18336452, 17288409, 18747196, 19049453, 21369858
     18155762, 17984331, 17571039, 14338435, 20887355, 16180763, 17011832
     20228093, 17597748, 17070158, 17346091, 19625472, 18317074, 16992075
     17927970, 13724904, 20936905, 18053631, 20121313, 19013183, 19291380
     19507904, 16392068, 17982555, 17761775, 18619917, 20334344, 12716670
     14460384, 15894143, 16305657, 20263424, 17050888, 13073613, 17481314
     19165892, 19576559, 14497275, 13866822, 19309466, 14671408, 17197081
     20140148, 17951233, 19680952, 17721717, 16810361, 17600719, 16777840
     17071721, 18416368, 16929165, 18326617, 14373152, 19550902, 20219458
     14777968, 16804061, 17036973, 17273020, 16619152, 4189542, 14852021
     21201481, 19713693, 18614015, 18265482, 18723434, 18620694, 16785708
     17209968, 19879144, 17343514, 16284825, 12982566, 17831758, 19152115
     17891943, 19442102, 17705023, 17255538, 19780701, 19827973, 17224314
     17341326, 18485835, 20539715, 21469106, 18075666, 19730508, 18996843
     20856290, 17598201, 18293054, 17827250, 16876500, 18783224, 20531190
     18904062, 17853456, 17332800, 13498243, 18674024, 17853498, 17292250
     17376015, 14245531, 14843189, 18069285, 19487147, 19827030, 19613828
     17897511, 17973658, 16867761, 12816846, 17274537, 17586955, 10136473
     16902031, 18069723, 14458214, 17691313, 21235427, 20004021, 17468141
     21067387, 17273003, 17250794, 18035463, 18590877, 19319357, 16855292
     17239687, 17079301, 18867023, 17217593, 16976121, 19888492, 17484762
     17235750, 16819962, 18418934, 16359751, 20355502, 18744838, 19146626
     17478145, 19651773, 16091637, 12364061, 17835627, 17596908, 18718696
     20365005, 18607546, 17500165, 18187697, 13960236, 13951456, 16454223
     17891946, 16247011, 17360606, 18356166, 17209410, 14500654, 20186278
     18973907, 19384686, 20017509, 18875012, 19835133, 14076173, 18073821
     13558557, 16596890, 12928658, 18759589, 18352845, 17672719, 18034871
     16912439, 17385178, 19699191, 16317771, 18940497, 16220077, 17016369
     17215560, 18115594, 17786278, 18876528, 17027426, 16346413, 17901041
     15979965, 17752121, 14829250, 17818075, 17590813, 19359787, 20833831
     17381384, 17390431, 13837378, 20513382, 17323222, 17297939



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

OPatch succeeded.


C:\oracle\SWO\11204\OPatch>

I searched for a solution and I found the following link where in chapter "6.4 Task 4: Check for Invalid Objects in the Upgraded Database" you can see some tips about the recompilation of invalid objects (using the utlrp.sql, the same that you mentioned):

http://docs.oracle.com/cd/E12839_01/upgrade.1111/e10125/db_upgrade.htm

I did exactly what is describe in the above link but unfortunately it still show me the invalid objects (19)!

Answer to you, to other question "have you ever ran utlrp.sql as part of a post SBP step on your 10.2.0.x DB? Maybe this will help you address your objects pre-upgrade...."

No... I didn´t! This was in 10.2.0.2 and then I done the oracle patchset to the 10.2.0.5 version (because it´s a requirement if you want to perform an upgrade to oracle 11.2g version (11.2.0.4)! Now everytime I type de command sqlplus /nolog it show me the 11.2.0.4 version.

I can´t understand what is wrong here! DAMN

I need to run the DBUA wizard but I don´t want to do that without first delete all the invalid objects!!

Can you help me please?

Thank you,

João Dimas

[francois_keen]

Hi Joao.

The output you showed about the inventory is your target release and the point of your SCN message was to address the invalid objects so I am not sure to understand the relevance of the target inventory.

I am confused by your statement "Now everytime I type de command sqlplus /nolog it show me the 11.2.0.4 version." As you havent yet run dbua, you shouldnt have yet pointed the env to the new binaries...

I see you had another problem in

I can only suggest you double check your environment variables ORACLE_HOME_SRC ORACLE_HOME_TGT

I will have to step out this SCN thread as I'm quite confused by your problem, (I am not a DBA by trade and I havent done an upgrade from 10.2.0.x to 11.2.0.x for many years), I hope some people will provide you help otherwise, I'd suggest browsing the articles in support.oracle.com

good luck to you!

best regards

[joo_migueldimas]

Hello Francois,

I believe that is correct... because in this stage I already have the 11.2.0.4 database, I already did several changes that is described in the "oracle upgrade guide 11.2.0.4"...

Maybe the problem was something that I done... or not! I didn´t run the pre-upgrade scripts in that "third phase"... I performed that check after I ran the DBUA warnings:

It was because of these warnings... therefore I run the pre-upgrade scripts, and maybe this pre-upgrade scripts must be running in 10.2.0.5 version and not the new/future version (11.2.0.4).

I don´t know how to manage this... what to do.

Maybe I will start all again... put all with 10.2g version run the pre-upgrade scripts and then restart all the actions/changes again to the 11.2g.

Thank you,

João Dimas