1 / 33

How an Oracle Database 12 c Upgrade Works in a Multitenant Environment Joe Errede

How an Oracle Database 12 c Upgrade Works in a Multitenant Environment Joe Errede. Introduction & Overview. What changed in upgrade. Working with Oracle Multitenant. Joe Errede from Boston Mass. Database Upgrade Group. Roy Swonger Senior Director ST Dev . & Product Management.

prescott-ellison
Download Presentation

How an Oracle Database 12 c Upgrade Works in a Multitenant Environment Joe Errede

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. How an Oracle Database 12c Upgrade Works in a Multitenant EnvironmentJoe Errede How Database Upgrade works in a Multitenant Environment

  2. Introduction & Overview What changed in upgrade Working with Oracle Multitenant How Database Upgrade works in a Multitenant Environment

  3. Joe Errede from Boston Mass. How Database Upgrade works in a Multitenant Environment

  4. 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 Eric Wittenberg Principal Software Engineer Mike Dietrich Senior Principal Technologist How Database Upgrade works in a Multitenant Environment

  5. Introduction & Overview What changed in upgrade Working with Oracle Multitenant How Database Upgrade works in a Multitenant Environment

  6. Upgrade SQL Automation • 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. • preupgrd.sql • Executes pre-upgrade checks • Runs in source environment • Generates fixup scripts • preupgrade_fixups.sql • postupgrade_fixups.sql • MOS Note:884522.1 • New Pre-Upgrade Script • All Output is written to $ORACLE_BASE/cfgtoollogs/sid/preupgrade • Generates preupgrade_fixups.sqlExecuted in source home before the upgrade • Generates postupgrade_fixups.sqlExecuted in target home after the upgrade

  7. preupgrd.sqlin a Multitenant Database IF (PDB1 )THEN . . . IF (PDB2) THEN . . . IF (PDB3 )THEN . . . • 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 • New Pre-Upgrade Script

  8. Faster Upgrade – Less Downtime catctl.pl script1.sql script2.sql script3.sql • catctl.pl • Runs database upgrade in parallel • Up to 40% faster upgrade • Used and proven by selected Oracle Database 11g global customers • Telco billing • >100 SAP systems • Large DWH • New Parallel Upgrade script4.sql script7.sql script9.sql script8.sql script10.sql script5.sql script11.sql script6.sql script6.sql

  9. 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

  10. Introduction & Overview What changed in upgrade Working with Oracle Multitenant How Database Upgrade works in a Multitenant Environment

  11. One Interface Used By All catctl.pl catcon.pl sqlpatch.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 calls packages within catcon.pm to perform the upgrade. • catctl.pl works with DBUA, MASS, RAC and STANDBY upgrades. • catctl.pl catcon.pm DATABASE

  12. How Upgrade Works in a Multitenant Database CATCTL.PL • Upgrade the CDB$ROOT first • EXIT if there is any error in the upgrade and abort the upgrade • Upgrade the PDB’s next • catctl.pl UPGRADE CDB$ROOT EXIT IF ERROR UPGRADE PDB1 UPGRADE PDB2

  13. How Upgrade Works in a Multitenant Database MAIN PROCESS CATCTL.PL • Each PDB upgrade is run as a child catctl.pl process • Each child process is started by the main catctl process • Each child process run independently from main process • The main process waits for child processes to complete • The main process dispatches more child processes if more PDB’s are in need of upgrading • Upgrade processing is ordered by con_id’s • catctl.pl CHILDPROCESS 1 CATCTL.PL CHILDPROCESS2 CATCTL.PL PDB1 PDB2

  14. Multiple PDB Upgrades $ORACLE_HOME/perl/bin/perlcatctl.pl -n catupgrd.sql PDB3 Upgrade PDB2 Upgrade PDB1 Upgrade PDB n Upgrade • 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: • A cpu_count equal to 24 equates to 12 pdb upgrades • -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 • catctl.pl -n TO

  15. Parallel Processing Within a PDB Upgrade $ORACLE_HOME/perl/bin/perlcatctl.pl -Ncatupgrd.sql PDB3 PDB2 PDB1 PDBn SQL PROC 1 SQL PROC 2 SQL PROC 3 SQL PROC 4 SQL PROC N SQL PROC N+1 SQL PROC 5 SQL PROC 6 • Parallel processing is controlled by -N parameter • -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. • Maximum value is 8 minimum value is 1 • catctl.pl TO -N

  16. Putting It All Together CATCTL.PL $ORACLE_HOME/perl/bin/perl catctl.pl –n 8 catupgrd.sql CATCON.PM CDB$ROOT UPGRADE FIRST EXIT IF ERROR • 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 • catctl.pl 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 SQL PROC 7&8 PDB2 PDB1 PDB3 PDB$SEED

  17. Upgrade Success or Failure SQL> describe registry$error; Name Null? Type ----------------------------------------- -------- ---------------------------- USERNAME VARCHAR2(256) TIMESTAMP TIMESTAMP(6) SCRIPT VARCHAR2(1024) IDENTIFIER VARCHAR2(256) MESSAGE CLOB STATEMENT CLOB • 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. • catctl.pl

  18. How to Evaluate Upgrade Logs in a Multitenant Database Find Errors in upg_summary.rpt and identify the PDB Name • 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) • catctl.pl Edit catupgrdpdb_name0..1.log Search for error noted in summary report

  19. Container Lists Inclusion and Exclusion • Container lists provide a way to prioritizing upgrades. • Controls which upgrades are done first. • Container lists support re-upgrades to specific PDB(s). • Used in conjunction with the –p parameter you can restart a single PDB upgrade from a specific phase. • Catctl.pl –p 74 –P 74 • Upgrade log file identified each phase. PHASE_TIME___START 74 PHASE_TIME___END 74 • Container lists are also used after we plug a PDB into a container, where the container is at a higher release than the PDB. • Create pluggable database PDB1 using (‘PDB1.XML’) nocopytempfile reuse; • $ORACLE_HOME/perl/bin/perlcatctl.pl –c ‘PDB1’ catupgrd.sql • Inclusion List • -c ‘CDB$ROOT PDB$SEED’ • Upgrades CDB$ROOT and PDB$SEED only • Exclusion List • -C ‘CDB$ROOT’ • Upgrades everything but CDB$ROOT • catctl.pl

  20. Debugging catctl.pl CATCON.PM 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: my @gArgs; 270: foreach $argnum (0 .. $#ARGV) 271: { 272: push (@gArgs, $ARGV[$argnum]); 272: push (@gArgs, $ARGV[$argnum]); 272: push (@gArgs, $ARGV[$argnum]); • 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. • catctl.pl

  21. Other Parameters of Interest • -l /tmp • All log files will be placed in the /tmp directory. • -I orcl • All log files will be created with a prefix of catupgrdorcl • -d $ORACLE_HOME/rdbms/admin • Tells catctl.pl where the upgrade files live. • -x • Not advised postpones post upgrade procedures (datapatch and catuppst.sql). • Must be done manually after upgrade the completes. • -l directory to use for spool log files • -i identifier to use when creating spool log files • -d directory containing the files to be run • -x Postpone running the post upgrade procedure • catctl.pl

  22. Normal Mode for Multitenant Databases CDB$ROOT RUN UPGRADE RESTART IN NORMAL MODE • The default behavior for Multitenant upgrades is the following • Upgrade CDB$ROOT in upgrade mode • 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 • catctl.pl RUN POST UPGRADE RUN UPGRADE PDB2 PDB1 RESTART IN NORMAL RESTRICTED MODE RUN POST UPGRADE SHUTDOWN

  23. Normal Mode for Multitenant databases CDB$ROOT RUN UPGRADE RESTART IN NORMAL MODE • Advantages to default behavior • As PDB’s become available they can be brought online while other PDB’s are still being upgraded • 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 • catctl.pl RUN POST UPGRADE RUN UPGRADE PDB2 PDB1 RESTART IN NORMAL RESTRICTED MODE RUN POST UPGRADE SHUTDOWN

  24. Upgrade Mode for Multitenant Databases CDB$ROOT RUN UPGRADE RESTART IN UPGRADE MODE • Catctl–M catupgrd.sql • Upgrade CDB$ROOT in upgrade mode • 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 • catctl.pl RUN POST UPGRADE RUN UPGRADE PDB2 PDB1 RESTART IN NORMAL RESTRICTED MODE RUN POST UPGRADE SHUTDOWN

  25. Upgrade Mode for Multitenant Databases CDB$ROOT RUN UPGRADE RESTART IN UPGRADE MODE • Advantages to –M behavior • 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 • catctl.pl RUN POST UPGRADE RUN UPGRADE PDB2 PDB1 RESTART IN NORMAL RESTRICTED MODE RUN POST UPGRADE SHUTDOWN

  26. 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

  27. 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

  28. In House Case Study 2 • 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 Sqlprocesses per PDB • Upgrade Mode Verses Normal Mode • Upgrade Mode 3-5% faster • catctl.pl

  29. In Conclusion… • 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 • catctl.pl

  30. Resources • Download all slides from: • blogs.oracle.com/UPGRADE How Database Upgrade works in a Multitenant Environment Upgrade, Migrate & Consolidate to Oracle Database 12c |

  31. Demo Booth Presence • Discuss your upgrade at our demo booth • Moscone South – SD-183 How Database Upgrade works in a Multitenant Environment Upgrade, Migrate & Consolidate to Oracle Database 12c |

  32. Recommended Upgrade Related Talks September 28 –October 2, 2014San 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 How Database Upgrade works in a Multitenant Environment Upgrade, Migrate & Consolidate to Oracle Database 12c |

  33. How Database Upgrade works in a Multitenant Environment

More Related