Archives For August 2013

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.

Advertisements

while reading the 12c upgrade guide I noticed that the catupgrd.sql is deprecated:

“Oracle Database 12c introduces the new Parallel Upgrade Utility, catctl.pl. This utility replaces the catupgrd.sql script that was used in earlier releases. Although you can still use the catupgrd.sql script, it is deprecated starting with Oracle Database 12c and will be removed in future releases. Oracle recommends database upgrades be performed with the new Parallel Upgrade Utility, catctl.pl.”

so prepare for changing your upgrade process …

a question which pops up regulary is how one can find out which psu is installed on an oracle database. there are at least two possibilities.

if you have access to the server use opatch:

$ORACLE_HOME/OPatch/opatch lsinventory | grep "Database Patch Set Update"
Patch description:  "Database Patch Set Update : 11.2.0.2.8 (14275621)"
Sub-patch  13923804; "Database Patch Set Update : 11.2.0.2.7 (13923804)"
Sub-patch  13696224; "Database Patch Set Update : 11.2.0.2.6 (13696224)"

if you don’t have access to the server but can connect as sys:

col comments for a60
col version for a30
set lines 264
select version,comments from registry$history where comments like '%PSU%';

VERSION 		       COMMENTS
------------------------------ ------------------
11.2.0.2		       PSU 11.2.0.2.3
11.2.0.2		       PSU 11.2.0.2.4
11.2.0.2		       PSU 11.2.0.2.8

you may even check if the database was upgraded from a previous release:

select version,comments from registry$history where comments like '%Upgraded%';

VERSION 		       COMMENTS
------------------------------ ------------------------
11.2.0.3.0		       Upgraded from 11.2.0.2.0

nothing special but maybe this might help someone. this is a simple bash script which automates the installation of the oracle 12c binaries and creates an initial oracle database. all you have to do is to download the sources, adjust the parameters in the configuration section and execute the script. if you keep the script as it is (except for SOURCEPATH) you will get the following:

ORACLE_SID=orcl
ORACLE_BASE=/opt/oracle/product/base
ORACLE_HOME=/opt/oracle/product/base/12.1.0.1
oraInventory=/opt/oracle/oraInventory
/oradata/orcl/...    for the database files
standard oracle user and oracle groups

this is for redhat6 based distributions only (rhel6, ol6, centos6 (not supported, but works)).

as the script installs the required linux software you should have the yum repositories available somehow ( either you are connected to the internet or you have a local copy of the repositories ).

btw: the logs can be found in the home of the oracle user.
btw2: the script does not create the limits (ulimit) recommended by oracle as the defaults should be fine for testing

#!/bin/bash

##################################################
#         CONFIGURATION SECTION                  #
##################################################

# ** location of the database source files
SOURCEPATH=/home/daniel/Downloads
# ** name of the first source file
SOURCE1=linuxamd64_12c_database_1of2.zip
# ** name of the second source file
SOURCE2=linuxamd64_12c_database_2of2.zip
# ** working directory for extracting the source
WORKDIR=/opt/oracle/stage
# ** the oracle top directory
ORATOPDIR=/opt/oracle
# ** the oracle inventory
ORAINVDIR=${ORATOPDIR}/oraInventory
# ** the ORACLE_BASE to use
ORACLE_BASE=${ORATOPDIR}/product/base
# ** the ORACLE_HOME to use
ORACLE_HOME=${ORACLE_BASE}/12.1.0.1
# ** base directory for the oracle database files
ORABASEDIR=/oradata
# the ORACLE_SID to use
ORACLE_SID=orcl
# ** the owner of the oracle software
ORAOWNER=oracle
# ** the primary installation group
ORAINSTGROUP=oinstall
# ** the dba group
ORADBAGROUP=dba
# ** the oper group
ORAOPERGROUP=oper
# ** the backup dba group
ORABACKUPDBA=backupdba
# ** the dataguard dba group
ORADGBAGROUP=dgdba
# ** the transparent data encryption group
ORAKMBAGROUP=kmdba


##################################################
#        MAIN SECTION                            # 
##################################################

PFILE=${ORACLE_HOME}/dbs/init${ORACLE_SID}.ora

# print the header
_header() {
   echo "*** ---------------------------- ***"
   echo "*** -- starting oracle 12c setup ***"
   echo "*** ---------------------------- ***"
}

# print simple log messages to screen
_log() {
   echo "****** $1 "
}

# check for the current os user
_check_user() {
    if [ $(id -un) != "${1}" ]; then
        _log "you must run this as ${1}"
        exit 0
    fi

}

# create the user and the groups
_create_user_and_groups() {
    _log "*** checking for group: ${ORAINSTGROUP} "
    getent group ${ORAINSTGROUP}
    if [ "$?" -ne "0" ]; then
        /usr/sbin/groupadd ${ORAINSTGROUP} 2> /dev/null || :
    fi
    _log "*** checking for group: ${ORADBAGROUP} "
    getent group ${ORADBAGROUP}
    if [ "$?" -ne "0" ]; then
        /usr/sbin/groupadd ${ORADBAGROUP} 2> /dev/null || :
    fi
    _log "*** checking for group: ${ORAOPERGROUP} "
    getent group ${ORAOPERGROUP}
    if [ "$?" -ne "0" ]; then
        /usr/sbin/groupadd ${ORAOPERGROUP} 2> /dev/null || :
    fi
    _log "*** checking for group: ${ORABACKUPDBA} "
    getent group ${ORABACKUPDBA}
    if [ "$?" -ne "0" ]; then
        /usr/sbin/groupadd ${ORABACKUPDBA} 2> /dev/null || :
    fi
    _log "*** checking for group: ${ORADGBAGROUP} "
    getent group ${ORADGBAGROUP}
    if [ "$?" -ne "0" ]; then
        /usr/sbin/groupadd ${ORADGBAGROUP} 2> /dev/null || :
    fi
    _log "*** checking for group: ${ORAKMBAGROUP} "
    getent group ${ORAKMBAGROUP}
    if [ "$?" -ne "0" ]; then
        /usr/sbin/groupadd ${ORAKMBAGROUP} 2> /dev/null || :
    fi
    _log "*** checking for user: ${ORAOWNER} "
    getent passwd ${ORAOWNER}
    if [ "$?" -ne "0" ]; then
        /usr/sbin/useradd -g ${ORAINSTGROUP} -G ${ORADBAGROUP},${ORAOPERGROUP},${ORABACKUPDBA},${ORADGBAGROUP},${ORAKMBAGROUP} \
                          -c "oracle software owner" -m -d /home/${ORAOWNER} -s /bin/bash ${ORAOWNER}
    fi
}

# create the directories
_create_dirs() {
    _log "*** creating: ${WORKDIR} "
    mkdir -p ${WORKDIR}
    chown ${ORAOWNER}:${ORAINSTGROUP} ${WORKDIR}
    _log "*** creating: ${ORATOPDIR} "
    mkdir -p ${ORATOPDIR}
    chown ${ORAOWNER}:${ORAINSTGROUP} ${ORATOPDIR}
    _log "*** creating: ${ORACLE_BASE} "
    mkdir -p ${ORACLE_BASE}
    chown ${ORAOWNER}:${ORAINSTGROUP} ${ORACLE_BASE}
    _log "*** creating: ${ORACLE_HOME} "
    mkdir -p ${ORACLE_HOME}
    chown ${ORAOWNER}:${ORAINSTGROUP} ${ORACLE_HOME}
    _log "*** creating: ${ORABASEDIR} "
    mkdir -p ${ORABASEDIR}
    chown ${ORAOWNER}:${ORAINSTGROUP} ${ORABASEDIR}
    _log "*** creating: ${ORABASEDIR}/${ORACLE_SID} "
    mkdir -p ${ORABASEDIR}/${ORACLE_SID}
    chown ${ORAOWNER}:${ORAINSTGROUP} ${ORABASEDIR}/${ORACLE_SID}
    _log "*** creating: ${ORABASEDIR}/${ORACLE_SID}/rdo1 "
    mkdir -p ${ORABASEDIR}/${ORACLE_SID}/rdo1
    _log "*** creating: ${ORABASEDIR}/${ORACLE_SID}/rdo2 "
    mkdir -p ${ORABASEDIR}/${ORACLE_SID}/rdo2
    _log "*** creating: ${ORABASEDIR}/${ORACLE_SID}/dbf "
    mkdir -p ${ORABASEDIR}/${ORACLE_SID}/dbf
    _log "*** creating: ${ORABASEDIR}/${ORACLE_SID}/arch "
    mkdir -p ${ORABASEDIR}/${ORACLE_SID}/arch
    _log "*** creating: ${ORABASEDIR}/${ORACLE_SID}/admin "
    mkdir -p ${ORABASEDIR}/${ORACLE_SID}/admin
    _log "*** creating: ${ORABASEDIR}/${ORACLE_SID}/admin/adump "
    mkdir -p ${ORABASEDIR}/${ORACLE_SID}/admin/adump
    _log "*** creating: ${ORABASEDIR}/${ORACLE_SID}/pdbseed "
    mkdir -p ${ORABASEDIR}/${ORACLE_SID}/pdbseed
    chown -R ${ORAOWNER}:${ORADBAGROUP} ${ORABASEDIR}/${ORACLE_SID}
}

# extract the source files
_extract_sources() {
    cp ${SOURCEPATH}/${SOURCE1} ${WORKDIR}
    cp ${SOURCEPATH}/${SOURCE2} ${WORKDIR}
    chown ${ORAOWNER}:${ORAINSTGROUP} ${WORKDIR}/*
    _log "*** extracting: ${SOURCE1} "
    su - ${ORAOWNER} -c "unzip -d ${WORKDIR} ${WORKDIR}/${SOURCE1}"
    _log "*** extracting: ${SOURCE2} "
    su - ${ORAOWNER} -c "unzip -d ${WORKDIR} ${WORKDIR}/${SOURCE2}"
}

# install required software
_install_required_software() {
    _log "*** installing required software "
    yum install -y binutils compat-libcap1 compat-libstdc++-33 gcc gcc-c++ glibc glibc-devel ksh \
                   libgcc libstdc++ libstdc++-devel libaio libaio-devel libXext libXtst libX11 libXau libxcb libXi make sysstat
}

# install oracle software
_install_oracle_software() {
    _log "*** installing oracle software"
    su -  ${ORAOWNER} -c "cd ${WORKDIR}/database; ./runInstaller oracle.install.option=INSTALL_DB_SWONLY \
    ORACLE_BASE=${ORACLE_BASE} \
    ORACLE_HOME=${ORACLE_HOME} \
    UNIX_GROUP_NAME=${ORAINSTGROUP}  \
    oracle.install.db.DBA_GROUP=${ORADBAGROUP} \
    oracle.install.db.OPER_GROUP=${ORAOPERGROUP} \
    oracle.install.db.BACKUPDBA_GROUP=${ORABACKUPDBA}  \
    oracle.install.db.DGDBA_GROUP=${ORADGBAGROUP}  \
    oracle.install.db.KMDBA_GROUP=${ORAKMBAGROUP}  \
    FROM_LOCATION=../stage/products.xml \
    INVENTORY_LOCATION=${ORAINVDIR} \
    SELECTED_LANGUAGES=en \
    oracle.install.db.InstallEdition=EE \
    DECLINE_SECURITY_UPDATES=true  -silent -ignoreSysPrereqs -ignorePrereq -waitForCompletion"
    ${ORAINVDIR}/orainstRoot.sh
    ${ORACLE_HOME}/root.sh
}

# create a very minimal pfile
_create_pfile() {
    _log "*** creating pfile "
    echo "instance_name=${ORACLE_SID}" > ${PFILE}
    echo "db_name=${ORACLE_SID}" >> ${PFILE}
    echo "db_block_size=8192" >> ${PFILE}
    echo "control_files=${ORABASEDIR}/${ORACLE_SID}/rdo1/control01.ctl,${ORABASEDIR}/${ORACLE_SID}/rdo2/control02.ctl" >> ${PFILE}
    echo "sga_max_size=512m" >> ${PFILE}
    echo "sga_target=512m" >> ${PFILE}
    echo "diagnostic_dest=${ORABASEDIR}/${ORACLE_SID}/admin" >> ${PFILE}
    echo "audit_file_dest=${ORABASEDIR}/${ORACLE_SID}/admin/adump" >> ${PFILE}
    echo "enable_pluggable_database=true" >> ${PFILE}
}

# create the database
_create_database() {
    _log "*** creating database "
    # escaping the dollar seems not to work in EOF
    echo "alter pluggable database pdb\$seed close;" > ${ORABASEDIR}/${ORACLE_SID}/admin/seedhack.sql
    echo "alter pluggable database pdb\$seed open;" >> ${ORABASEDIR}/${ORACLE_SID}/admin/seedhack.sql
    su - ${ORAOWNER} -c "export ORACLE_HOME=${ORACLE_HOME};export LD_LIBRARY_PATH=${LD_LIBRARY_PATH};export PATH=${ORACLE_HOME}/bin:${PATH};export ORACLE_SID=${ORACLE_SID};export PERL5LIB=${ORACLE_HOME}/rdbms/admin; sqlplus / as sysdba <<EOF 
shutdown abort
startup force nomount pfile=${PFILE} 
create spfile from pfile='${PFILE}';
startup force nomount
CREATE DATABASE \"${ORACLE_SID}\"
MAXINSTANCES 8
MAXLOGHISTORY 5
MAXLOGFILES 16
MAXLOGMEMBERS 5
MAXDATAFILES 1024
DATAFILE '${ORABASEDIR}/${ORACLE_SID}/dbf/system01.dbf' SIZE 1024m REUSE AUTOEXTEND ON NEXT 8m MAXSIZE 2g EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '${ORABASEDIR}/${ORACLE_SID}/dbf/sysaux01.dbf' SIZE 1024m REUSE AUTOEXTEND ON NEXT 8m MAXSIZE 2g
DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '${ORABASEDIR}/${ORACLE_SID}/dbf/temp01.dbf' SIZE 1024m REUSE AUTOEXTEND ON NEXT 8m MAXSIZE 2g
UNDO TABLESPACE \"UNDOTBS1\" DATAFILE  '${ORABASEDIR}/${ORACLE_SID}/undotbs01.dbf' SIZE 1024m REUSE AUTOEXTEND ON NEXT 8m MAXSIZE 2g
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
LOGFILE GROUP 1 ('${ORABASEDIR}/${ORACLE_SID}/rdo1/redo01_1.log', '${ORABASEDIR}/${ORACLE_SID}/rdo2/redo01_2.log') SIZE 64m,
        GROUP 2 ('${ORABASEDIR}/${ORACLE_SID}/rdo1/redo02_1.log', '${ORABASEDIR}/${ORACLE_SID}/rdo2/redo02_2.log') SIZE 64m,
        GROUP 3 ('${ORABASEDIR}/${ORACLE_SID}/rdo1/redo03_1.log', '${ORABASEDIR}/${ORACLE_SID}/rdo2/redo03_2.log') SIZE 64m
USER SYS IDENTIFIED BY \"sys\" USER SYSTEM IDENTIFIED BY \"system\"
enable pluggable database
seed file_name_convert=('${ORABASEDIR}/${ORACLE_SID}/dbf/system01.dbf', '${ORABASEDIR}/${ORACLE_SID}/pdbseed/system01.dbf'
                       ,'${ORABASEDIR}/${ORACLE_SID}/dbf/sysaux01.dbf', '${ORABASEDIR}/${ORACLE_SID}/pdbseed/sysaux01.dbf'
                       ,'${ORABASEDIR}/${ORACLE_SID}/dbf/temp01.dbf', '${ORABASEDIR}/${ORACLE_SID}/pdbseed/temp01.dbf'
                       ,'${ORABASEDIR}/${ORACLE_SID}/dbf/undotbs01.dbf', '${ORABASEDIR}/${ORACLE_SID}/pdbseed/undotbs01.dbf');
startup force
alter session set \"_oracle_script\"=true;
start ${ORABASEDIR}/${ORACLE_SID}/admin/seedhack.sql
host perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -l /home/${ORAOWNER} -b catalog $ORACLE_HOME/rdbms/admin/catalog.sql;
host perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -l /home/${ORAOWNER} -b catblock $ORACLE_HOME/rdbms/admin/catblock.sql;
host perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -l /home/${ORAOWNER} -b catproc $ORACLE_HOME/rdbms/admin/catproc.sql;
host perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -l /home/${ORAOWNER} -b catoctk $ORACLE_HOME/rdbms/admin/catoctk.sql;
host perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -l /home/${ORAOWNER} -b pupbld -u SYSTEM/system $ORACLE_HOME/sqlplus/admin/pupbld.sql;
connect "SYSTEM"/"system"
host perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -l /home/${ORAOWNER} -b hlpbld -u SYSTEM/system -a 1  $ORACLE_HOME/sqlplus/admin/help/hlpbld.sql 1helpus.sql;
connect / as sysdba
start $ORACLE_HOME/rdbms/admin/utlrp.sql
set lines 264 pages 9999
col owner for a30
col status for a10
col object_name for a30
col object_type for a30
col comp_name for a80
col PDB_NAME for a30
col PDB_ID for 999
select owner,object_name,object_type,status from dba_objects where status  'VALID';
select comp_name,status from dba_registry;
select pdb_id,pdb_name from dba_pdbs;
exit;
EOF"
}

# add oracle environment to .bash_profile
_create_env() {
    _log "*** adding environment to .bash_profile "
    echo "ORACLE_BASE=${ORACLE_BASE}" >> /home/${ORAOWNER}/.bash_profile
    echo "ORACLE_HOME=${ORACLE_HOME}" >> /home/${ORAOWNER}/.bash_profile
    echo "ORACLE_SID=${ORACLE_SID}" >> /home/${ORAOWNER}/.bash_profile
    echo "LD_LIBRARY_PATH=${ORACLE_HOME}/lib:${LD_LIBRARY_PATH}" >> /home/${ORAOWNER}/.bash_profile
    echo "PATH=${ORACLE_HOME}/bin:${PATH}" >> /home/${ORAOWNER}/.bash_profile
    echo "export ORACLE_BASE ORACLE_HOME ORACLE_SID LD_LIBRARY_PATH PATH" >> /home/${ORAOWNER}/.bash_profile
}

_header
_check_user "root"
_create_user_and_groups
_create_dirs
_install_required_software
_extract_sources
_install_oracle_software
_create_pfile
_create_database
_create_env

once the script finished simply do:

su - oracle
sqlplus / as sysdba

… and start playing around.

if you are on a distribution based on redhat 6 there are some interesting tools which can help in fine tuning the system for different workloads. e.g. if you’d like to put a database on
your server there a various settings you might want to adjust ( kernel, disks, network … ). if you use the system as a workstation other settings might make more sense ( power saving settings, for example ).

as this blog mainly is about databases i’ll focus there, obviously. first of all you’ll need the software:

yum install tuned

as tuned is a service you’ll need to enable and start it.

service tuned start
chkconfig tuned on
chkconfig --list | grep tuned

let’s see what happend. the main configuration file for tuned is located in /etc:

/etc/tuned.conf

if you take a look at the file you will find a main section and various plugins sections (e.g. DiskTuning or CPUTuning).

next several default tuning profiles have been created:

ls -l /etc/tune-profiles/
active-profile
default
desktop-powersave
enterprise-storage
functions
laptop-ac-powersave
laptop-battery-powersave
latency-performance
server-powersave
spindown-disk
throughput-performance
virtual-guest
virtual-host

each of these directories contains the same configuration files ( ktune.sh, ktune.sysconfig, sysctl.ktune, tuned.conf ) which specify the various settings which will be set once the profile becomes active.

you can list the available profiles with the tune-adm command, too:

tuned-adm list
Available profiles:
- server-powersave
- laptop-ac-powersave
- latency-performance
- default
- desktop-powersave
- enterprise-storage
- virtual-guest
- virtual-host
- spindown-disk
- laptop-battery-powersave
- throughput-performance
Current active profile: default

… which additionally tells us that the default profile is the one which is active at the moment.

another way to check the active profile is:

tuned-adm active

if you want to create a new profile just copy an existing one and adjust the settings you want to:

cp -pr /etc/tune-profiles/enterprise-storage/ /etc/tune-profiles/my_profile
tuned-adm list | grep my_profile
- my_profile

for databases you’ll probably need maximum throughput, so let’s activate the throughput-performance profile:

tuned-adm profile throughput-performance
Stopping tuned:                                            [  OK  ]
Switching to profile 'throughput-performance'
Applying ktune sysctl settings:
/etc/ktune.d/tunedadm.conf:                                [  OK  ]
Calling '/etc/ktune.d/tunedadm.sh start':                  [  OK  ]
Applying sysctl settings from /etc/sysctl.conf
Applying deadline elevator: sda                            [  OK  ]
Starting tuned:                                            [  OK  ]

according to the documentation and the output from above this should change the io scheduler to deadline ( which is recommended for databases ). is this true ?

cat /sys/block/sda/queue/scheduler 
noop anticipatory [deadline] cfq

seems to work. does this survive a reboot?

reboot
tuned-adm active
cat /sys/block/sda/queue/scheduler
noop anticipatory [deadline] cfq

very good. no need to adjust this in the bootloader anymore.

if you want to check which kernel settings have been adjusted by activating this profile just have a look at the configuration files:

cat /etc/tune-profiles/throughput-performance/sysctl.ktune

include any kernel setting you need in there and you’re fine.

as profiles may be switched on the fly several profiles activated at different times of the day might make sense, too.