saturday, early in the morning morning, everybody else is still sleeping. a perfect time to do one of the oracle on demand sessions I wanted to complete:
grrr….
Archives For November 30, 1999
some time ago I blogged on how to list the installed patches for an oracle database. if you are on 12c there is another possibility:
SQL> select dbms_qopatch.get_opatch_lsinventory from dual;
OracleHome-94047000-c49c-4d73-b13c-d252248d3c09
oracle_home
/opt/oracle/oraInventory
false
oneoff
/opt/oracle/product/base/12.1.0.1
oracle_home
....
this returns a xml of the opatch inventory right out of the database. another nice little feature introduced with 12c.
if you check the documentation you’ll notice that there are some other functions you might use to query the optach inventory.
oracle 12c introduced pdb level parameters:
select name from v$parameter where ISPDB_MODIFIABLE='TRUE'; NAME -------------------------------------------------------------------------------- sessions timed_statistics timed_os_statistics resource_limit ....
all of these parameters can be set per pdb, so lets have a look at one of the parameters. I’ll choose optimizer_dynamic_sampling for this little demonstration.
at the cdb level my value of optimizer_dynamic_sampling is:
SQL> show con_name CON_NAME ------------------------------ CDB$ROOT SQL> show parameter optimizer_dynamic_sampling NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ optimizer_dynamic_sampling integer 2
this is reflected in the spfile, too:
[oracle@localhost dbs]$ strings spfileorcl.ora | grep optimizer_dynamic_sampling *.optimizer_dynamic_sampling=2
lets connect to the pdb and see what is the value there:
SQL> show con_name CON_NAME ------------------------------ PDB1 SQL> show parameter optimizer_dynamic_sampling NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ optimizer_dynamic_sampling integer 2
same as in the cdb. lets change it:
SQL> show con_name CON_NAME ------------------------------ PDB1 SQL> alter system set optimizer_dynamic_sampling=4 scope=both; System altered. SQL> show parameter optimizer_dynamic_sampling NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ optimizer_dynamic_sampling integer 4
going back to the cdb we can see that the cdb level parameter was not touched:
SQL> show con_name CON_NAME ------------------------------ CDB$ROOT SQL> show parameter optimizer_dynamic_sampling NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ optimizer_dynamic_sampling integer 2 SQL>
but where does oracle store the pdb level parameters? in the spfile?
[oracle@localhost dbs]$ strings spfileorcl.ora | grep optimizer_dynamic_sampling *.optimizer_dynamic_sampling=2
obviously not. so, lets trace:
SQL> alter session set tracefile_identifier=PARAMETER_CHECK; Session altered. alter session set events '10046 trace name context forever, level 12'; Session altered. SQL> show parameter optimizer_dynamic_sampling NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ optimizer_dynamic_sampling integer 4
looking at the trace file we can see that the following happens:
PARSING IN CURSOR #139664056367904 len=289 dep=0 uid=0 oct=3 lid=0 tim=14602003422 hv=2462394820 ad='75206100' sqlid='7cfz5wy9caaf4'
SELECT NAME NAME_COL_PLUS_SHOW_PARAM
, DECODE(TYPE,1,'boolean',2,'string',3,'integer'
, 4,'file',5,'number', 6,'big integer', 'unknown') TYPE
, DISPLAY_VALUE VALUE_COL_PLUS_SHOW_PARAM
FROM V$PARAMETER WHERE UPPER(NAME) LIKE UPPER(:NMBIND_SHOW_OBJ)
ORDER BY NAME_COL_PLUS_SHOW_PARAM,ROWNUM
END OF STMT
substituting the bind variable gives exactly the results we expect:
SELECT NAME NAME_COL_PLUS_SHOW_PARAM
, DECODE(TYPE,1,'boolean',2,'string',3,'integer'
, 4,'file',5,'number', 6,'big integer', 'unknown') TYPE
, DISPLAY_VALUE VALUE_COL_PLUS_SHOW_PARAM
FROM V$PARAMETER
WHERE UPPER(NAME) LIKE UPPER('OPTIMIZER_DYNAMIC_SAMPLING')
ORDER BY NAME_COL_PLUS_SHOW_PARAM,ROWNUM;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling integer 4
but this does not really help, does it? the magic must be hidden in the v$parameter view, so lets see what is being done there (v$parameter is based on gv$parameter):
select x.inst_id,x.indx+1,ksppinm,ksppity,ksppstvl, ksppstdvl , ksppstdf, decode(bitand(ksppiflg/256,1),1,'TRUE','FALSE') , decode(bitand(ksppiflg/65536,3),1,'IMMEDIATE',2,'DEFERRED' , 3,'IMMEDIATE','FALSE') , decode(bitand(ksppiflg/524288,1),1,'TRUE','FALSE') , decode(bitand(ksppiflg,4),4,'FALSE' , decode(bitand(ksppiflg/65536,3), 0, 'FALSE', 'TRUE')) , decode(bitand(ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE') , decode(bitand(ksppstvf,2),2,'TRUE','FALSE') , decode(bitand(ksppilrmflg/64, 1), 1, 'TRUE', 'FALSE') , decode(bitand(ksppilrmflg/268435456, 1), 1, 'TRUE', 'FALSE') , ksppdesc , ksppstcmnt , ksppihash , x.con_id from x$ksppi x, x$ksppcv y where (x.indx = y.indx) and bitand(ksppiflg,268435456) = 0 and ((translate(ksppinm,'_','#') not like '##%') and ((translate(ksppinm,'_','#') not like '#%') or (ksppstdf = 'FALSE') or (bitand(ksppstvf,5) > 0)))
ah, there is a con_id column in x$ksppi. but no where clause on this column, so:
SQL> select distinct con_id from x$ksppi;
CON_ID
----------
3
… only container 3 is reflected in the x$ table, which is my pdb:
SQL> select con_id, name from v$pdbs;
CON_ID NAME
---------- ------------------------------
2 PDB$SEED
3 PDB1
doing the same in the cdb lists all containers:
SQL> select distinct con_id from x$ksppi;
CON_ID
----------
1
2
3
the answer is: the pdb level parameters are stored in the data dictionary of the pdb and each pdb just sees the paramters which are valid for just the pdb and nothing else. makes sense, as you may unplug and plug pdbs and for sure are happy to have the same parameters at the pdb level once you plug into a different instance.
another little, but fine improvement in oracle 12c: Now you may issue sql statements directly in rman without the need to use the “SQL ‘…'” syntax:
rman target / Recovery Manager: Release 12.1.0.1.0 - Production on Sat Jun 29 08:40:34 2013 Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved. connected to target database: DBS300 (DBID=3276277814) RMAN> select instance_name from v$instance; using target database control file instead of recovery catalog INSTANCE_NAME ---------------- dbs300 RMAN> alter system switch logfile; Statement processed RMAN> shutdown; database closed database dismounted Oracle instance shut down RMAN> startup connected to target database (not started) Oracle instance started database mounted database opened Total System Global Area 334036992 bytes Fixed Size 2288104 bytes Variable Size 268437016 bytes Database Buffers 58720256 bytes Redo Buffers 4591616 bytes RMAN> select status from v$instance; STATUS ------------ OPEN RMAN>
a lot of people define varchar2 columns this way:
create table t1 ( a varchar2(1) );
… and expect that they will be able to insert one character into this column. well, this is not always true. the default for a varchar2 column is byte, so the above statement is equal to:
create table t1 ( a varchar2(1 byte) );
the data dictionary does reflect this if you know what the default is:
SQL> create table t1 ( a varchar2(1 char), b varchar2(1)); Table created. SQL> desc t1; Name Null? Type ----------------------------------------- -------- ---------------------------- A VARCHAR2(1 CHAR) B VARCHAR2(1) SQL>
so, if you work with multibyte character sets and do not take care of this, this may happen:
create table t1 ( a varchar2(1 char), b varchar2(1));
insert into t1 (a) values (chr(1000));
1 row created.
SQL> insert into t1 (b) values (chr(1000));
insert into t1 (b) values (chr(1000))
*
ERROR at line 1:
ORA-12899: value too large for column "SYS"."T1"."B" (actual: 2, maximum: 1)
postgresql provides it, msssql (I think) provides it, too. and finally, with release 12c, you may have auto incrementing columns in oracle:
SQL> create table t1 ( a number generated always as identity, b number ); SQL> insert into t1 (b) values (1); 1 row created. SQL> insert into t1 (b) values (2); 1 row created. SQL> select * from t1; A B ---------- ---------- 1 1 2 2
what happens in the background is quite simple: oracle creates a sequence:
SQL> select sequence_name from user_sequences; SEQUENCE_NAME -------------------------------------------------------------------------------- ISEQ$$_19540
user/dba/all_tables has a new column to refect this:
SQL> select HAS_IDENTITY from user_tables where table_name = 'T1'; HAS --- YES
but it is not possible to have more than one of these:
SQL> create table t2 ( a number generated always as identity, b number generated always as identity );
create table t2 ( a number generated always as identity, b number generated always as identity )
*
ERROR at line 1:
ORA-30669: table can have only one identity column
once the table is dropped, the sequence is gone, too:
SQL> drop table t1; Table dropped. SQL> select sequence_name from user_sequences; no rows selected SQL>
oracle 12c introduced a nice feature: move data files online:
SQL*Plus: Release 12.1.0.1.0 Production on Fri Jun 28 16:48:29 2013 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> create tablespace tbs1 datafile '/oradata/dbs300/dbf/tbs1_01.dbf' size 10m; Tablespace created. SQL> alter database move datafile '/oradata/dbs300/dbf/tbs1_01.dbf' to '/oradata/dbs300/dbf/tbs1_02.dbf'; Database altered. SQL>
the trace file generated gives some more details about the move operation:
Moving datafile /oradata/dbs300/dbf/tbs1_01.dbf (16) to /oradata/dbs300/dbf/tbs1_02.dbf The secondary file /oradata/dbs300/dbf/tbs1_02.dbf is created Blocks copied for file /oradata/dbs300/dbf/tbs1_02.dbf Move operation committed for file /oradata/dbs300/dbf/tbs1_02.dbf Move operation completed for file /oradata/dbs300/dbf/tbs1_02.dbf Moving datafile /oradata/dbs300/dbf/tbs1_01.dbf (16) to /oradata/dbs300/dbf/tbs1_02.dbf *** 2013-06-28 17:28:26.437 The secondary file /oradata/dbs300/dbf/tbs1_02.dbf is created
The number in brackets (16) is the file id:
SQL> select FILE_ID from dba_data_files where file_name = '/oradata/dbs300/dbf/tbs1_02.dbf'; FILE_ID ---------- 16 SQL>
em12 donwload pagein note 1169017.1 oracle announced that the setting of “similar” for the cursor_sharing parameter will be deprecated as of 12c: “The ability to set this will be removed in version 12 of the Oracle Database (the settings of EXACT and FORCE will remain available). Instead, we recommend the use of Adaptive Cursor Sharing in 11g.”
the current documentation is up to date and “similar” is not mentioned anymore, but:
SQL*Plus: Release 12.1.0.1.0 Production on Fri Jun 28 15:33:34 2013 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> show parameter sharing NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ cursor_sharing string EXACT SQL> alter system set cursor_sharing='similar'; System altered. SQL> show parameter sharing NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ cursor_sharing string similar SQL>
so, the ability to set this is still there …
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 