some time ago I blogged about the catctl.pl script that organizes oracle database upgrades starting in oracle 12. what I didn’t looked at was how the the different phases are build.
when you call the catctl.pl whith the “-y” flag to display the phases some lines at the beginning of the output are:
Analyzing file catupgrd.sql
Log files in /opt/oracle/product/base/12.1.0.2.0/rdbms/admin
23 scripts found in file catupgrd.sql
Next path: catalog.sql
34 scripts found in file catalog.sql
Next path: catproc.sql
38 scripts found in file catproc.sql
Next path: catptabs.sql
63 scripts found in file catptabs.sql
Next path: catpdbms.sql
210 scripts found in file catpdbms.sql
Next path: catpdeps.sql
76 scripts found in file catpdeps.sql
Next path: catpprvt.sql
265 scripts found in file catpprvt.sql
Next path: catpexec.sql
26 scripts found in file catpexec.sql
Next path: cmpupgrd.sql
28 scripts found in file cmpupgrd.sql
so, what is the procedure for determining the order of these scripts? the documentation/header of the catctl.pl script gives some hints:
# --CATCTL -S
# Run Sql in Serial Mode one Process
# --CATCTL -M
# Run Sql in Multiple Processes
# --CATFILE -X
# Sql contains multiprocessing
# --CATCTL -R
# Bounce the Sql Process
# --CATCTL -CS
# Turns Compile Off (Defaults to on)
# --CATCTL -CE
# Turns Compile On
# --CATCTL -SES
# Session files for phases
# --CATCTL -SE
# Run Sql in Serial Mode only executed in catctl.pl
# --CATCTL -ME
# Run Sql in Parallel Mode only executed in catctl.pl
the first step that happens is scanning the catupgrd.sql for the “–CATFILE -X” text. this would look like:
grep "\-\-CATFILE \-X" catupgrd.sql
@@catalog.sql --CATFILE -X
@@catproc.sql --CATFILE -X
@@cmpupgrd.sql --CATFILE -X
not exactly the same as catctl.pl tells us. the reason is that each of these scripts can contain the “–CATFILE -X” again. so we would need to grep all of these scripts again. a simple script to do this:
!/bin/bash
RDBMS_ADMIN=/opt/oracle/product/base/12.1.0.2.0/rdbms/admin
FILES=`grep "\-\-CATFILE \-X" ${RDBMS_ADMIN}/catupgrd.sql | awk -F " " '{print $1}' | awk -F "@" '{print $3}'`
for f in ${FILES}
do
echo ${f}
FILES2=`grep "\-\-CATFILE \-X" ${RDBMS_ADMIN}/${f} | awk -F " " '{print $1}' | awk -F "@" '{print $3}'`
for ff in ${FILES2}
do
echo ${ff}
done
done
exactly the same:
./a.sh
catalog.sql
catproc.sql
catptabs.sql
catpdbms.sql
catpdeps.sql
catpprvt.sql
catpexec.sql
cmpupgrd.sql
be aware that there might be even more recursion in the future and the above little script might not work anymore. the complete logic is in the catctlScanSqlFiles routine which is much more complex.
the rest is pretty straight forward.
the first phase is the first script called in catupgrd.sql (serial execution)
[phase 0] type is 1 with 1 Files
@catupstr.sql
the second phase:
[phase 1] type is 1 with 5 Files
@cdstrt.sql @cdfixed.sql @catcdbviews.sql @catblock.sql
@cdcore.sql
just look at the header of catalog.sql:
--CATCTL -S Initial scripts single process
@@cdstrt.sql
@@cdfixed.sql
@@catcdbviews.sql
@@catblock.sql
@@cdcore.sql
--CATCTL -R
--CATCTL -M
@@cdplsql.sql
@@cdsqlddl.sql
everything below “–CATCTL -S” is phase 1. phase 2 is “–CATCTL -R” (restart the sqlplus session(s)). phase 3 runs in parallel because of the “–CATCTL -M” flag.
and so on and so on.
btw: by the time I wrote the post about catctl.pl the catctl.pl script was version 12.1.0.1. in the 12.1.0.2 release the documentation inside the scripts is much better especially for the number of processes used for the upgrade:
# Below is the Basic Flow of upgrade.
#
# Traditional Database
#
# Run Database upgrade in parallel (-n option) passing in catupgrd.sql.
# Minimum SQL process count is 1.
# Maximum SQL process count is 8.
# Default SQL process count is 4.
# Run Post Upgrade Procedure.
# If there are any errors in the upgrade the post
# upgrade procedure will not run.
# If the -x option is specified the post upgrade procedure
# will not run.
#
# 1) The database must be first started in upgrade mode
# by the database adminstrator.
# 2) After the upgrade completes the database is shutdown.
# 3) The database is then restarted in restricted normal mode.
# 4) Run the post upgrade procedure (catuppst.sql).
# 5) Shutdown the database.
#
# Multitenant Database (CDB)
#
# Run Database upgrade in parallel (-n option) passing in catupgrd.sql.
# Minimum SQL process count is 1.
# Maximum SQL process count is 64.
# Default SQL process count is 0.
# This is calculated by Number of Cpu's on your system.
# Run Upgrade on CDB$ROOT.
# The Maximum SQL process count is 8.
# If there are any errors in the CDB$ROOT upgrade the
# entire upgrade process is stopped.
# Run Upgrades in all the PDB's.
# The number of PDB's that run together is calculated by
# dividing the SQL process count by 2.
# Each individual PDB will use 2 for its SQL process count.
# For example:
# If the value of -n is set to 32. 32/2 yields 16
# PDB's upgrading at the same time.
# Each of those 16 PDB's use 2 for thier SQL
# process count for a total of 32 SQL sessions that
# running concurrently.
# If the value of -N is set then the PDB's will use
# this value for thier SQL process count instead of
# the default 2.
Daniel Westermann's Blog 