1. technology and computing
2. software
3. databases

How an Oracle Database 12c Upgrade
Works in a Multitenant Environment
Joe Errede
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. | How Database Upgrade works in a Multitenant Environment
Introduction & Overview
What changed in upgrade
Working with Oracle Multitenant
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. | How Database Upgrade works in a Multitenant Environment
2
Joe Errede from Boston Mass.
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. | How Database Upgrade works in a Multitenant Environment
3
Database Upgrade Group
Roy Swonger
Senior Director ST Dev.
&
Product Management
Carol Tagliaferri
Director Software Dev.
Cindy Lim
Principal Member Technical Staff
Joseph Errede
Principal Member Technical Staff
Mike Dietrich
Eric Wittenberg
Senior Principal Technologist
Principal Software Engineer
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | How Database Upgrade works in a Multitenant Environment
4
Introduction & Overview
What changed in upgrade
Working with Oracle Multitenant
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. | How Database Upgrade works in a Multitenant Environment
5
Upgrade SQL Automation
New Pre-Upgrade Script
• preupgrd.sql
• Executes pre-upgrade checks
• Runs in source environment
• Generates fixup scripts
– preupgrade_fixups.sql
– postupgrade_fixups.sql
• MOS Note:884522.1
PREUPGRD.SQL produces either TEXT or XML output.
upgrade.xml is used by (DBUA)
catcon.pl -n 1 -e -b preupgrade -d
'''.''' preupgrd.sql
upgrade.xml contains ID,
Status, Cause and Action to
perform
Creates preupgrade0.log
containing textual information
that is reviewed by DBA.
All Output is written to
$ORACLE_BASE/cfgtoollogs/sid/preupgrade
Generates preupgrade_fixups.sql
Executed in source home before the upgrade
Generates postupgrade_fixups.sql
Executed in target home after the upgrade
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
preupgrd.sql in a Multitenant Database
New Pre-Upgrade Script
• Pre-Upgrade tools runs in every container
using catcon.pl
• catcon.pl -n 1 -e -b preupgrade -d '''.'''
preupgrd.sql
• Results are logged to preupgrd0.log base
on the –b parameter
• The pre and post upgrade fix-up scripts run
changes specific to each PDB
• Intelligence has been built into the script
IF (PDB1 )THEN
.
.
.
IF (PDB2) THEN
.
.
.
IF (PDB3 )THEN
.
.
.
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
Faster Upgrade – Less Downtime
catctl.pl
New Parallel Upgrade
• catctl.pl
• Runs database upgrade in parallel
• Up to 40% faster upgrade
• Used and proven by selected Oracle
Database 11g global customers
script1.sql
script2.sql
script3.sql
script4.sql
script7.sql
script9.sql
script5.sql
script8.sql
script10.sql
– Telco billing
script6.sql
– >100 SAP systems
– Large DWH
script6.sql
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
script11.sql
Faster Upgrade – Less Downtime
New Parallel Upgrade
• Parallel upgrade is performed in phases.
These phases are determined at run time by
parsing CATCTL tags within the SQL files
• Each phase builds dependencies for next
phase
• Each phase must complete before moving
onto the next phase
• Phases can be run in serial or in parallel
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
Introduction & Overview
What changed in upgrade
Working with Oracle Multitenant
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. | How Database Upgrade works in a Multitenant Environment
10
One Interface Used By All
catctl.pl
• In 12.1.0.2.0 we broke out catctl.pl into a
common shared library called catcon.pm
• catcon.pm is used in upgrade, datapatch
and with catcon.pl
catctl.pl
catcon.pl
sqlpatch.pl
catcon.pm
• catctl.pl calls packages within
catcon.pm to perform the upgrade.
• catctl.pl works with DBUA, MASS, RAC
and STANDBY upgrades.
DATABASE
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
How Upgrade Works in a Multitenant Database
catctl.pl
CATCTL.PL
• Upgrade the CDB$ROOT first
• EXIT if there is any error in the upgrade and
abort the upgrade
EXIT IF
ERROR
UPGRADE
CDB$ROOT
• Upgrade the PDB’s next
UPGRADE
PDB1
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
UPGRADE
PDB2
How Upgrade Works in a Multitenant Database
MAIN PROCESS
CATCTL.PL
catctl.pl
• Each PDB upgrade is run as a child catctl.pl process
• Each child process is started by the main catctl
process
CHILD
PROCESS 1
CATCTL.PL
• Each child process run independently from main
process
• The main process waits for child processes to
complete
CHILD
PROCESS 2
CATCTL.PL
• The main process dispatches more child processes if
more PDB’s are in need of upgrading
• Upgrade processing is ordered by con_id’s
PDB1
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
PDB2
Multiple PDB Upgrades
catctl.pl
$ORACLE_HOME/perl/bin/perl catctl.pl -n catupgrd.sql
• Multiple PDB upgrades is controlled by -n
parameter
• -n tells catctl.pl how many PDB upgrades to run
together
• -n defaults to cpu_count/2 for example:
-n
– A cpu_count equal to 24 equates to 12 pdb
upgrades
PDB1
Upgrade
PDB2
Upgrade
• -n is divided by 2 and then rounded down. This
yields the number of PDB’s upgraded together
• Maximum concurrent upgrades is 32 minimum is 2
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
PDB3
Upgrade
TO
PDB n
Upgrade
Parallel Processing Within a PDB Upgrade
catctl.pl
$ORACLE_HOME/perl/bin/perl catctl.pl -N catupgrd.sql
• Parallel processing is controlled by -N
parameter
SQL PROC 1
SQL PROC 2
SQL PROC 3
SQL PROC 4
SQL PROC 5
SQL PROC 6
SQL PROC N
SQL PROC N+1
• -N tells catctl .pl how many SQL processes are
used to upgrade the PDB database dictionary
• -N defaults to 2 for example:
– cpu_count equal to 24 equates to 12
pdb upgrades. Each pdb upgrade runs
2 SQL processes.
-N
PDB1
PDB2
• Maximum value is 8 minimum value is 1
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
PDB3
TO
PDBn
Putting It All Together
CATCTL.PL
$ORACLE_HOME/perl/bin/perl catctl.pl –n 8 catupgrd.sql
catctl.pl
CATCON.PM
• Start catctl.pl with –n 8
• Upgrade Root first
• Startup child catctl processes to upgrade 4 pdb’s at
a time
• Startup 2 SQL processes per PDB because we took
the default
• Perform the upgrade
• As soon as one PDB is upgraded then the next PDB
can be processed until all the PDB’s have been
upgraded
CDB$ROOT UPGRADE
FIRST
EXIT IF
ERROR
START CHILD CATCTL.PL PROCESSES
CATCTL.PL
CATCTL.PL
CATCTL.PL
CATCTL.PL
CATCON.PM
CATCON.PM
CATCON.PM
CATCON.PM
SQL PROC 1&2
SQL PROC 3&4
SQL PROC 5&6
PDB$SEED
PDB1
PDB2
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
SQL PROC 7&8
PDB3
Upgrade Success or Failure
catctl.pl
• All errors are written to registry$error.
• Oracle Server component determines
upgrade success or failure by looking at the
registry$error table.
• Components have their own validation
routines to determine status.
SQL> describe registry$error;
Name
Null? Type
----------------------------------------- -------- ---------------------------USERNAME
VARCHAR2(256)
TIMESTAMP
TIMESTAMP(6)
SCRIPT
VARCHAR2(1024)
IDENTIFIER
VARCHAR2(256)
MESSAGE
CLOB
STATEMENT
CLOB
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
How to Evaluate Upgrade Logs in a Multitenant Database
Find Errors in
upg_summary.rpt
and identify the
PDB Name
catctl.pl
• View the upgrade summary report:
$ORACLE_BASE/cfgtoollogs/<sid>/upgrade/upg_summary.rpt
– Summary report is also included in
catupgrd0.log
• Report contains the status, errors and total
upgrade times for all the PDB’s that ran during
the upgrade
• PDB log files are named as follows
– catupgrdPDBNAME0…n.log (0..7 –N option)
• CDB$ROOT log files are named as follows
– catupgrd0…n.log (-n) (0…7 –n option)
Edit
catupgrdpdb_name
0..1.log
Search for error
noted in summary
report
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
Container Lists Inclusion and Exclusion
catctl.pl
• Inclusion List
– -c ‘CDB$ROOT PDB$SEED’
– Upgrades CDB$ROOT and PDB$SEED only
• Exclusion List
– -C ‘CDB$ROOT’
– Upgrades everything but CDB$ROOT
o
Container lists provide a way to prioritizing upgrades.
o Controls which upgrades are done first.
o
Container lists support re-upgrades to specific PDB(s).
o
Used in conjunction with the –p parameter you can restart a
single PDB upgrade from a specific phase.
o Catctl.pl –p 74 –P 74
o Upgrade log file identified each phase.
PHASE_TIME___START 74 PHASE_TIME___END 74
o
Container lists are also used after we plug a PDB into a
container, where the container is at a higher release
than the PDB.
o Create pluggable database PDB1 using
(‘PDB1.XML’) nocopy tempfile reuse;
o $ORACLE_HOME/perl/bin/perl catctl.pl –c ‘PDB1’
catupgrd.sql
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
Debugging catctl.pl
CATCON.PM
catctl.pl
• Catcon.pm debugging.
– Catctl.pl -z catupgrd.sql
• Catctl.pl tracing. This is done using perl to generate
trace files. Trace files are generated in the format
catctl_YYYYMMDDHHMNSC_pid_trace.log
– Catctl.pl –Z 1 catupgrd.sql
– All code is log along with entry and exit
function values.
• By default catctl.pl will generate a stack trace
should some catastrophic event occurs.
catconInit: base for log and spool file names
= catupgrd
running catconInit(User
= 0,
InternalUser = ,
SrcDir
= 0,
LogDir
= 0,
LogBase
= catupgrd,
CATCTL.PL
Package catctl.pl.
269:
270:
271:
272:
272:
272:
my @gArgs;
foreach $argnum
{
push (@gArgs,
push (@gArgs,
push (@gArgs,
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
(0 .. $#ARGV)
$ARGV[$argnum]);
$ARGV[$argnum]);
$ARGV[$argnum]);
Other Parameters of Interest
catctl.pl
o
-l /tmp
o All log files will be placed in the /tmp directory.
• -l directory to use for spool log files
o
-I orcl
o All log files will be created with a prefix of
catupgrdorcl
o
-d $ORACLE_HOME/rdbms/admin
o Tells catctl.pl where the upgrade files live.
o
-x
o Not advised postpones post upgrade
procedures (datapatch and catuppst.sql).
o Must be done manually after upgrade the
completes.
• -i identifier to use when creating
spool log files
• -d directory containing the files to
be run
• -x Postpone running the post
upgrade procedure
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
Normal Mode for Multitenant Databases
catctl.pl
RUN UPGRADE
• The default behavior for Multitenant upgrades is
RESTART IN NORMAL MODE
the following
• Upgrade CDB$ROOT in upgrade mode
CDB$ROOT
RUN POST UPGRADE
– Restart CDB$ROOT in normal mode
• Run post upgrade procedures
• Upgrade the PDB’s
– Restart PDB in normal restricted mode
• Run post upgrade procedures
• Shutdown PDB
RUN UPGRADE
RESTART IN NORMAL RESTRICTED MODE
PDB2
PDB1
RUN POST UPGRADE
SHUTDOWN
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
Normal Mode for Multitenant databases
catctl.pl
• Advantages to default behavior
RUN UPGRADE
RESTART IN NORMAL MODE
– As PDB’s become available they can be
brought online while other PDB’s are still
being upgraded
CDB$ROOT
RUN POST UPGRADE
• Disadvantages to default behavior
– More background processes are running
while upgrade is in progress
– Online PDB’s will experience slow down
while other PDB’s are being upgraded
– Upgrades of the PDB’s tend to be slower
RUN UPGRADE
RESTART IN NORMAL RESTRICTED MODE
PDB2
PDB1
RUN POST UPGRADE
SHUTDOWN
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
Upgrade Mode for Multitenant Databases
catctl.pl
• Catctl –M catupgrd.sql
• Upgrade CDB$ROOT in upgrade mode
RUN UPGRADE
RESTART IN UPGRADE MODE
CDB$ROOT
RUN POST UPGRADE
– Restart CDB$ROOT in upgrade mode
• Run post upgrade procedures
• Upgrade the PDB’s
– Restart PDB in normal restricted mode
• Run post upgrade procedures
• Shutdown PDB
RUN UPGRADE
RESTART IN NORMAL RESTRICTED MODE
PDB2
PDB1
RUN POST UPGRADE
SHUTDOWN
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
Upgrade Mode for Multitenant Databases
catctl.pl
• Advantages to –M behavior
RUN UPGRADE
RESTART IN UPGRADE MODE
– No background processes are running
while PDB upgrades are in progress
– Upgrades of the PDB’s are faster
• Disadvantages to -M behavior
– All PDB’s have to complete their
upgrades before they can become
available
CDB$ROOT
RUN POST UPGRADE
RUN UPGRADE
RESTART IN NORMAL RESTRICTED MODE
PDB2
PDB1
RUN POST UPGRADE
SHUTDOWN
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
Upgrade Mode Versus Normal Mode
catctl.pl
• Can I have a show of hands on which
method you prefer?
• Upgrade Mode (“Total Time”)
– Faster upgrades but waiting until all the
upgrades have completed before you
can bring online
• Normal Mode (“Fast First”)
– Slower upgrades but able to bring your
PDB online as soon as the PDB upgrade
completes
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
In House Case Study 1
catctl.pl
• Upgraded 252 Pdb’s + CDB$ROOT+ PDB$SEED
• Linux Machine with 12 cores (Hyperthreading turn
on for 24 CPUs)
• 20 Gig Redo Log size.
• 30 Gig sga_target.
• Defaults were used when running catctl.pl
– 24/2 = 12 PDBS upgraded at a time
– 2 Sql processes per PDB
– Upgrade Mode Verses Normal Mode
• Upgrade mode was twice as fast
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
In House Case Study 2
catctl.pl
• Upgraded 25 Pdb’s + CDB$ROOT+ PDB$SEED
• Linux Exadata machine V1 with 2 physical CPU’s
(8 Hyperthreading cores)
• 20 Gig Redo Log size
• catctl.pl –n 18
– 18/2 = 9 PDBS upgraded at a time gave best
performance
– 2 Sql processes per PDB
– Upgrade Mode Verses Normal Mode
• Upgrade Mode 3-5% faster
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
In Conclusion…
catctl.pl
• The more PDB’s you have the better performance
you will get from upgrade mode (catctl –M)
• Different machines perform better with different
parameters
• Before you upgrade see what works best for your
system
– catctl.pl –n –N –M
• In most cases just taking the defaults maybe good
enough
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
Resources
• Download all slides from:
– blogs.oracle.com/UPGRADE
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
How
Database
Upgrade
works intoa Oracle
Multitenant
Environment
Upgrade,
Migrate
& Consolidate
Database
12c |•
30
Demo Booth Presence
• Discuss your upgrade at our demo booth
–Moscone South – SD-183
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
How
Database
Upgrade
works intoa Oracle
Multitenant
Environment
Upgrade,
Migrate
& Consolidate
Database
12c |•
31
Recommended Upgrade Related Talks
September 28 –
October 2, 2014
San Francisco
• Oracle Database 12c Upgrade: Tools and Best Practices from Oracle Support
– Tuesday, Sep 30, 5:00 PM - Moscone South - 310
• How and Why to Migrate from Schema Consolidation to Pluggable Databases
– Wednesday, Oct 1, 11:30 AM - Moscone South – 306
• Trouble Free Upgrade to Oracle Database 12c with Oracle Real Application Testing
– Wednesday, Oct 1, 2:00 PM - Moscone South – 303
• Database Patching Best Practices II CON7748
– Wednesday, Oct 1, 5:00 PM - Moscone South 104
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
How
Database
Upgrade
works intoa Oracle
Multitenant
Environment
Upgrade,
Migrate
& Consolidate
Database
12c |•
32
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. | How Database Upgrade works in a Multitenant Environment
33