the previous post just mentioned that catupgrd.sql is deprecated as of 12.1.0.1. the question is why? this can easily be answered and the answer is the same as with why the catcon.pl appears in 12.1.0.1: oracle tries to do as much work as possible in parallel and therefore minimizes setup time for database setups and downtime for upgrades. so, for the same reason the catcon.pl wrapper exists for parallizing the setups, the catctl.pl exists for parallizing the upgrades.
one piece of information which is interesting is that development of this parallel stuff probably started back in 2005:
head -n 41 catctl.pl # # $Header: rdbms/admin/catctl.pl /st_rdbms_12.1.0.1/3 2013/03/05 10:22:25 krajaman Exp $ # # catctl.pl # # Copyright (c) 2005, 2013, Oracle and/or its affiliates. All rights reserved. # # NAME # catctl.pl - CATalog ConTroL PerL program # # DESCRIPTION # This perl program processes sqlplus files and organizes # them for parallel processing based on annotations within # the files. # # NOTES # Used by catupgrd shell/bat scripts to run parallel upgrades # Connects to database specified by ORACLE_SID environment variable # # MODIFIED (MM/DD/YY) # jerrede 02/11/13 - Fix Password containing special characters bug # 16177906 # jerrede 01/10/13 - Ignore sqlsessstart and sqlsessend in driver files # jerrede 12/11/12 - xbranchmerge of jerrede_lrg-7343558 # jerrede 11/06/12 - Add Display option for patch group # bmccarth 10/30/12 - call utlucdir # jerrede 10/16/12 - Fix lrg 7284666 # jerrede 10/11/12 - Fix Security bug 14750812 # jerrede 10/03/12 - Fix lrg 7291461 # jerrede 08/28/12 - Mandatory Post upgrade. # jerrede 07/19/12 - Remove Passing Password at Command Line # Use /n\/n as the SQL Terminator for all # Sql Statements # jerrede 05/24/12 - Add Display of SQL File Executing # jerrede 10/18/11 - Parallel Upgrade ntt Changes # jerrede 09/12/11 - Fix Bug 12959399 # jerrede 09/01/11 - Parallel Upgrade Project no 23496 # rburns 10/23/07 - remove multiple processes; fix password mgmt # rburns 10/20/06 - add session script # rburns 12/16/05 - perl script for parallel sqlplus processing # rburns 12/16/05 - Creation
this means: development on 12c started even before 11.1 was released (which was back in 2007). or at least this does mean thinking about and experimenting with the parallel stuff started before 11.1 was released. of course I am not sure about all this, but it seems to be reasonable.
back to the script: the first interesting bit of information you can gather is to use the “-y” switch. this will list all the various phases the script will go through (I did this on a 12.1.0.1 instance which was not in “upgrade” mode. that’s why the error occurs at the end):
$ORACLE_HOME/perl/bin/perl catctl.pl catupgrd.sql -y Analyzing file catupgrd.sql 14 scripts found in file catupgrd.sql Next path: catalog.sql 32 scripts found in file catalog.sql Next path: catproc.sql 37 scripts found in file catproc.sql Next path: catptabs.sql 61 scripts found in file catptabs.sql Next path: catpdbms.sql 205 scripts found in file catpdbms.sql Next path: catpdeps.sql 77 scripts found in file catpdeps.sql Next path: catpprvt.sql 260 scripts found in file catpprvt.sql Next path: catpexec.sql 26 scripts found in file catpexec.sql Next path: cmpupgrd.sql 16 scripts found in file cmpupgrd.sql [Phase 0] type is 1 with 1 Files catupstr.sql [Phase 1] type is 1 with 3 Files cdstrt.sql cdfixed.sql cdcore.sql [Phase 2] type is 1 with 1 Files ora_restart.sql [Phase 3] type is 2 with 18 Files cdplsql.sql cdsqlddl.sql cdmanage.sql cdtxnspc.sql cdenv.sql cdrac.sql cdsec.sql cdobj.sql cdjava.sql cdpart.sql cdrep.sql cdaw.sql cdsummgt.sql cdtools.sql cdexttab.sql cddm.sql catldr.sql cdclst.sql [Phase 4] type is 1 with 1 Files ora_restart.sql [Phase 5] type is 1 with 5 Files cdoptim.sql catsum.sql catexp.sql cddst.sql cdend.sql [Phase 6] type is 1 with 1 Files catpstrt.sql [Phase 7] type is 1 with 3 Files catptyps.sql catpgrants.sql catgwm.sql [Phase 8] type is 1 with 1 Files ora_restart.sql [Phase 9] type is 2 with 60 Files catnodpt.sql catbac.sql prvtrctv.plb catactx.sql prvtuttv.plb catsvrmg.sql prvtlsis.plb prvtlsss.plb cattrans.sql catrule.sql catsnap.sql catpitr.sql catdip.sql catrls.sql catar.sql catfga.sql catamgt.sql catidxu.sql cattsm.sql catchnf.sql catodm.sql catkppls.sql catsscr.sql catqueue.sql cathae.sql catadvtb.sql catrm.sql catsch.sql catol.sql catdpb.sql catcrc.sql dbmscr.sql dbmsutil.sql catdbfus.sql catalrt.sql catatsk.sql catmntr.sql catsqlt.sql catawrtv.sql catsmbvw.sql catwrrtb.sql catsumat.sql catrep.sql catlmnr.sql catdef.sql catadrvw.sql catrepv.sql catpexe.sql cattlog.sql catcapi.sql catpspi.sql catts.sql catnacl.sql catredact.sql catproftab.sql catpstdy.sql catrupg.sql catratmask.sql catqitab.sql catappcont.sql [Phase 10] type is 1 with 1 Files ora_restart.sql [Phase 11] type is 1 with 1 Files catpspec.sql [Phase 12] type is 1 with 1 Files ora_restart.sql [Phase 13] type is 2 with 199 Files utlinad.sql utlsmtp.sql utlurl.sql utlenc.sql utlgdk.sql utlcstk.sql utlcomp.sql utli18n.sql utllms.sql dbmsplsw.sql utlnla.sql dbmspdb.sql dbmstrns.sql dbmsrwid.sql dbmspclx.sql dbmserlg.sql dbmsspu.sql dbmsapin.sql dbmssyer.sql dbmspipe.sql dbmsalrt.sql dbmsdesc.sql dbmspexp.sql dbmsjob.sql dbmsstat.sql dbmsstts.sql dbmsddl.sql dbmsedu.sql dbmspp.sql prvthddl.plb prvthjob.plb prvthsye.plb prvtzhlp.plb dbmsidxu.sql prvthidx.plb dbmspsp.sql dbmstran.sql dbmsxa.sql dbmstxfm.sql dbmsread.sql prvtreut.plb dbmspb.sql dbmspbt.sql dbmsplts.sql dbmspitr.sql utlrefld.sql utlcoll.plb dbmstrst.sql dbmsrlsa.sql dbmsrpr.sql dbmsobtk.sql dbmshtdb.sql dbmslm.sql dbmslmd.sql prvtlmes.plb utlcxml.sql dbmsfga.sql dbmsamgt.sql dbmstypu.sql dbmsres.sql dbmstxin.sql dbmsdrs.sql dbmsdg.sql dbmssum.sql dbmshord.sql dbmsxfr.sql dbmsmap.sql dbmsfi.sql dbmsdbv.sql dbmstcv.sql dbmscoll.sql dbmscdcu.sql dbmscdcp.sql dbmscdcs.sql dbmspbp.sql dbmshpro.sql dbmssrv.sql dbmschnf.sql dbmsxpln.sql utlmatch.sql dbmsdbvn.sql dbmspool.sql dbmsrcad.sql prvthcrc.plb prvtkpps.plb dbmsaq.plb dbmsaqad.sql dbmsaq8x.plb dbmsaqem.plb prvtaqxi.plb dbmsslrt.sql dbmsmntr.sql dbmshm.sql catsqltk.sql dbmsir.sql prvtsss.plb dbmsocm.sql dbmslobu.sql dbmsmp.sql dbmsaddm.sql prvttxfs.plb dbmsrmin.plb dbmsrmad.sql dbmsrmpr.sql dbmsrmpe.plb dbmsrmge.plb dbmsrmpa.plb prvtrmie.plb prvthjob.plb prvthesh.plb dbmsol.sql prvtdputh.plb dbmsmeta.sql dbmsmetb.sql dbmsmetd.sql dbmsmet2.sql dbmsdp.sql prvthpp.plb prvthpd.plb prvthpdi.plb prvthpvi.plb prvtdtde.plb prvtsum.plb prvtjdbs.plb dbmsslxp.sql prvssmgu.plb dbmsawr.sql prvsemxi.plb prvsemx_admin.plb prvsemx_dbhome.plb prvsemx_memory.plb prvsemx_perf.plb dbmsperf.sql prvsrept.plb prvsrepr.plb prvshdm.plb prvsrtaddm.plb prvs_awr_data_cp.plb prvscpaddm.plb prvsadv.plb prvsawr.plb prvsawri.plb prvsawrs.plb prvsash.plb prvsawrv.plb dbmssqlu.sql prvssqlf.plb dbmswrr.sql dbmsfus.sql prvsfus.plb dbmsuadv.sql dbmsrepl.sql dbmsspm.sql prvsspmi.plb prvssmb.plb prvssmbi.plb dbmsstr.sql dbmssqlt.sql dbmsspa.sql prvsautorepi.plb dbmsautorep.sql dbmsratmask.sql dbmsdiag.sql dbmsobj.sql dbmskzxp.sql dbmscu.sql dbmsdst.sql dbmscomp.sql dbmsilm.sql dbmspexe.sql prvthpexei.plb dbmscapi.sql dbmsfuse.sql dbmsfspi.sql dbmspspi.sql dbmsdnfs.sql dbmsadr.sql dbmsadra.sql prvsadri.plb xsrs.sql xssc.sql xsacl.sql xsds.sql xsns.sql xsdiag.sql xssess.sql dbmsredacta.sql dbmssqll.sql dbmsgwm.sql dbmsappcont.sql dbmsspd.sql prvsspdi.plb dbmsfs.sql dbmssqlm.sql catprofp.sql prvtsys.plb dbmspart.sql dbmsrupg.sql dbmstsdp.sql [Phase 14] type is 1 with 1 Files ora_restart.sql [Phase 15] type is 1 with 3 Files dbmsmeti.sql dbmsmetu.sql dbmsqopi.sql [Phase 16] type is 1 with 1 Files ora_restart.sql [Phase 17] type is 2 with 33 Files catmettypes.sql prvthdbu.plb catost.sql dbmshae.sql catxpend.sql prvtotpt.plb prvthlut.plb prvthlin.plb prvthsdp.plb dbmsrman.sql dbmsbkrs.sql dbmstrig.sql dbmsrand.sql dbmsjdwp.sql catxs.sql dbmssnap.sql prvtxrmv.plb depsaq.sql prvthlrt.plb catadv.sql dbmscred.sql catcredv.sql cataqsch.sql catrssch.sql catplug.sql prvtsql.plb prvtssql.plb prvtlmd.plb prvtlmcs.plb prvtlmrs.plb dbmslms.sql prvthpu.plb prvthpv.plb [Phase 18] type is 1 with 1 Files ora_restart.sql [Phase 19] type is 1 with 3 Files prvtkupc.plb prvtaqiu.plb catlsby.sql [Phase 20] type is 1 with 1 Files ora_restart.sql [Phase 21] type is 2 with 23 Files catmetviews.sql prvthpw.plb prvthpm.plb prvthpfi.plb prvthpf.plb dbmsodm.sql prvtitrg.plb prvtsms.plb depssvrm.sql deptxn.sql catstr.sql prvthsts.plb prvthfgr.plb prvthfie.plb prvthcmp.plb catpexev.sql depscapi.sql depspspi.sql catwrrvw.sql dbmsjdcu.sql dbmsjdmp.sql prvthpc.plb prvt_awr_data.plb [Phase 22] type is 1 with 1 Files ora_restart.sql [Phase 23] type is 2 with 11 Files catmetgrant1.sql catldap.sql prvtocm.sql prvtrepl.sql catpstr.sql prvthpci.plb catilm.sql catemxv.sql catnaclv.sql dbmsnacl.sql dbmswlm.sql [Phase 24] type is 1 with 1 Files ora_restart.sql [Phase 25] type is 1 with 1 Files catcdbviews.sql [Phase 26] type is 1 with 1 Files ora_restart.sql [Phase 27] type is 2 with 0 Files [Phase 28] type is 1 with 1 Files ora_load_without_comp.sql [Phase 29] type is 2 with 130 Files prvtfile.plb prvtrawb.plb prvttcp.plb prvtinad.plb prvtsmtp.plb prvthttp.plb prvturl.plb prvtenc.plb prvtgdk.plb prvtlob.plb prvtlobu.plb prvtcstk.plb prvtcomp.plb prvti18n.plb prvtlms2.plb prvtnla.plb prvttrns.plb prvtsess.plb prvtrwid.plb prvtpclx.plb prvterlg.plb prvtapin.plb prvtsyer.plb prvtlock.plb prvtpipe.plb prvtalrt.plb prvtdesc.plb prvtpexp.plb prvtzexp.plb prvtstts.plb prvtddl.plb prvtpp.plb prvtscrp.plb prvtkppb.plb prvtutil.plb prvtpsp.plb prvttran.plb prvtxa.plb prvtany.plb prvtread.plb prvtpb.plb prvtpbt.plb prvtxpsw.plb prvtcoll.plb prvttrst.plb prvtrlsa.plb prvtodci.plb prvtrpr.plb prvtobtk.plb prvthtdb.plb prvtxmlt.plb prvturi.plb prvtxml.plb prvtcxml.plb prvtemxi.plb prvtemx_admin.plb prvtemx_dbhome.plb prvtemx_memory.plb prvtemx_perf.plb prvtperf.plb prvtrep.plb prvtrept.plb prvtrepr.plb prvtfga.plb prvtamgt.plb prvttypu.plb prvtjdwp.plb prvtjdmp.plb prvtres.plb prvtcr.plb prvttxin.plb prvtdrs.plb prvtdg.plb prvtfi.plb prvtmap.plb prvthpui.plb prvtdbv.plb prvttcv.plb prvtpbp.plb prvthpro.plb prvtbdbu.plb prvtsrv.plb prvtpool.plb prvtkzxs.plb prvtkzxp.plb prvtcrc.plb prvtrc.plb prvtaq.plb prvtaqdi.plb prvtaqxe.plb prvtaqis.plb prvtaqim.plb prvtaqad.plb prvtaq8x.plb prvtaqin.plb prvtaqal.plb prvtaqjm.plb prvtaqmi.plb prvtaqme.plb prvtaqem.plb prvtaqip.plb prvtaqds.plb prvtsqdi.plb prvtsqds.plb prvtsqis.plb prvthm.plb prvtwlm.plb prvtsqtk.plb prvtkjhn.plb prvtir.plb prvtssb.plb prvttxfm.plb prvtrmin.plb prvtrmad.plb prvtrmpr.plb prvtrmpe.plb prvtrmge.plb prvtrmpa.plb prvtjob.plb prvtbsch.plb prvtesch.plb prvtcred.plb prvtol.plb prvtlm.plb prvtlmcb.plb prvtlmrb.plb prvtlms.plb prvtlmeb.plb prvtbpu.plb prvtwrr.plb [Phase 30] type is 1 with 1 Files ora_load_with_comp.sql [Phase 31] type is 1 with 1 Files ora_restart.sql [Phase 32] type is 1 with 1 Files ora_load_without_comp.sql [Phase 33] type is 2 with 122 Files prvtbpui.plb prvtdput.plb prvtmeta.plb prvtmeti.plb prvtmetu.plb prvtmetb.plb prvtmetd.plb prvtmet2.plb prvtdp.plb prvtbpc.plb prvtbpci.plb prvtbpw.plb prvtbpm.plb prvtbpfi.plb prvtbpf.plb prvtbpp.plb prvtbpd.plb prvtbpdi.plb prvtbpv.plb prvtbpvi.plb prvtdpcr.plb prvtplts.plb prvtpitr.plb prvtreie.plb prvtrwee.plb prvtidxu.plb prvtrcmp.plb prvtchnf.plb prvtedu.plb prvtlsby.plb prvtlsib.plb prvtlssb.plb prvtsmv.plb prvtsma.plb prvtbxfr.plb prvtbord.plb prvtjdbb.plb prvtslrt.plb prvtslxp.plb prvtatsk.plb prvtmntr.plb prvtsmgu.plb prvtdadv.plb prvtadv.plb prvtawr.plb prvtawrs.plb prvtawri.plb prvtash.plb prvtawrv.plb prvtsqlf.plb prvtsqli.plb prvtsqlt.plb prvtautorepi.plb prvtautorep.plb prvtfus.plb prvtmp.plb prvthdm.plb prvtaddm.plb prvtrtaddm.plb prvt_awr_data_cp.plb prvtcpaddm.plb prvtuadv.plb prvtsqlu.plb prvtspai.plb prvtspa.plb prvtratmask.plb prvtspmi.plb prvtspm.plb prvtsmbi.plb prvtsmb.plb prvtfus.plb catfusrg.sql prvtwrk.plb prvtsmaa.plb prvtxpln.plb prvtstat.plb prvtstai.plb prvtsqld.plb prvtspcu.plb prvtodm.plb prvtkcl.plb prvtdst.plb prvtcmpr.plb prvtilm.plb prvtpexei.plb prvtpexe.plb prvtcapi.plb prvtfuse.plb prvtfspi.plb prvtpspi.plb prvtdnfs.plb prvtfs.plb prvtadri.plb prvtadr.plb prvtadra.plb prvtadmi.plb prvtutils.plb prvtxsrs.plb prvtsc.plb prvtacl.plb prvtds.plb prvtns.plb prvtdiag.plb prvtkzrxu.plb prvtnacl.plb prvtredacta.plb prvtpdb.plb prvttlog.plb prvtsqll.plb prvtappcont.plb prvtspd.plb prvtspdi.plb prvtpprof.plb prvtsqlm.plb prvtpart.plb prvtrupg.plb prvtrupgis.plb prvtrupgib.plb prvtpstdy.plb prvttsdp.plb prvtqopi.plb prvtlog.plb [Phase 34] type is 1 with 1 Files ora_load_with_comp.sql [Phase 35] type is 1 with 1 Files ora_restart.sql [Phase 36] type is 1 with 4 Files catmetinsert.sql catpcnfg.sql utluppkg.sql catdph.sql [Phase 37] type is 1 with 1 Files ora_restart.sql [Phase 38] type is 2 with 13 Files catmetgrant2.sql execemx.sql execcr.sql caths.sql catemini.sql execaq.sql execsvrm.sql exechae.sql execsec.sql execbsln.sql dbmspump.sql olappl.sql execrep.sql [Phase 39] type is 1 with 1 Files ora_restart.sql [Phase 40] type is 2 with 10 Files execstr.sql execsvr.sql execstat.sql catsnmp.sql wpiutil.sql owainst.sql catilmini.sql execocm.sql exectsdp.sql execqopi.sql [Phase 41] type is 1 with 1 Files ora_restart.sql [Phase 42] type is 1 with 1 Files catpend.sql [Phase 43] type is 1 with 1 Files ora_restart.sql [Phase 44] type is 1 with 1 Files catupprc.sql [Phase 45] type is 1 with 1 Files cmpupstr.sql [Phase 46] type is 1 with 1 Files ora_restart.sql [Phase 47] type is 1 with 2 Files cmpupjav.sql cmpupnjv.sql [Phase 48] type is 1 with 1 Files ora_restart.sql [Phase 49] type is 1 with 2 Files cmpupxdb.sql cmpupnxb.sql [Phase 50] type is 1 with 1 Files ora_restart.sql [Phase 51] type is 1 with 2 Files cmpupord.sql cmpupmsc.sql [Phase 52] type is 1 with 1 Files ora_restart.sql [Phase 53] type is 1 with 1 Files cmpupend.sql [Phase 54] type is 1 with 1 Files catupend.sql [Phase 55] type is 1 with 1 Files catuppst.sql [Phase 56] type is 1 with 1 Files catshutdown.sql Using 4 processes. Serial Phase #: 0 Files: 1 A process terminated prior to catupgrd.sql completion. Review the catupgrd*.log files to identify the failure. Died at catctl.pl line 1751.
“type 1” equals to single threaded while “type 2” equals to multi threaded.
how does oracle calculate the number of sqlplus sessions to start? well, this is pretty hard-coded:
######################################################################
# Set Process Number
######################################################################
sub set_process_no {
$Process = 4; # use 4 processes as default if no value specified
if ($opt_n == 0 || $opt_n)
{
$Process = $opt_n;
# Serial Run
if ($Process == 0)
{
$Process = 1;
$bRunSerial = $true;
}
#
# Make sure process is valid
#
if ($Process > 8)
{
$Process = 8; # Max
}
if ($Process < 0)
{
$Process = 4; # Default
}
}
}
oracle uses 4 processes as a default unless you explicitly define the number of processes to use by using the “-n” switch. but you will never get more than 8. this is passed to the init_sql_process funtion which sets up the number of available sqlplus sessions:
######################################################################
# Initialization sql process
######################################################################
sub init_sql_process {
my $cpus = $_[0]; # Sql Process
my $ps;
for ($ps=0; $ps < $cpus; $ps++)
{
set_sql_process($false, $ps);
}
} # end of init_sql_process
...
######################################################################
# Start parallel sqlplus session, based on number of processes
######################################################################
start_sql_process(0, $Process, $SpoolLog, $Connect, $opt_e, $true);
pretty easy and efficient, but: as this is a lot more code around the sql scripts this opens room for bugs.
there is a lot more going on in the script ( sending commands to all sqlplus sessions, enabling/disabling compile for oracle components, organizing the files into the different phases … ) but this basic information should provide you with an idea of how upgrades will be handled as of 12.1.0.1.
Daniel Westermann's Blog 