Share
Embed
Upgrade Your Database Like a Pro with AutoUpgrade
Seán Scott PRO
March 08, 2023
Transcript
78 slides
-
Upgrade Your Database Like a Pro with AutoUpgrade Utah Oracle
User Group Training Days 2023 March 8, 2023 Sean Scott Oracle ACE Director Managing Principal Consultant @oraclesean linkedin.com/in/soscott/ [email protected] @ViscosityNA www.viscosityna.com -
DATABASE RELIABILITY ENGINEERING ⁘ DEVOPS & AUTOMATION HIGH AVAILABILITY
⁘ BUSINESS CONTINUITY ⁘ DISASTER RECOVERY MODERNIZATION ⁘ OBSERVABILITY ⁘ ENGINEERED SYSTEMS AutoUpgrade ⁘ Zero Downtime Migrations ⁘ Patching Real Application Clusters ⁘ Data Guard ⁘ Sharding Docker/Containers ⁘ Terraform ⁘ Ansible Exadata ⁘ Oracle Database Appliance AHF ⁘ TFA ⁘ GIMR ⁘ CHA Sean Scott Oracle ACE Director Managing Principal Consultant @oraclesean linkedin.com/in/soscott/ [email protected] @ViscosityNA www.viscosityna.com -
@ViscosityNA www.viscosityna.com Oracle on Docker Running Oracle Databases in Linux
Containers Download a free sample chapter: https:/ /oraclesean.com -
@ViscosityNA www.viscosityna.com Viscosity’s Oracle ACEs The Oracle ACE Program The
Oracle ACE Program recognizes and rewards individuals for their contributions to the Oracle community. Charles Kim CEO | Co-Founder W @racdba W ACE Director Craig Shallahamer Applied AI Scientist W @orapub W ACE Director Rich Niemiec Chief Innovation Officer W @richniemiec W ACE Director Sean Scott Principal Consultant W @oraclesean W ACE Director Gary Gordhamer Principal Consultant W @ggordham W ACE Pro -
@ViscosityNA www.viscosityna.com Viscosity Pillars and Delivery Models Workshops Assessments Proof
of Concepts Training Turnkey Projects Managed Services Oracle • SQL Server • Postgres Performance Tuning Data Replication Data Warehousing Analytics Data Integration ERP Blue Prints Database Upgrades APEX EBS Web/Mobile Apps .Net and C# E-Business Suite SAAS/PAAS Azure Gold Partner Cloud Migrations Engineered Systems Oracle Cloud Partner Google Partner AWS Partner Hybrid Cloud -
@ViscosityNA www.viscosityna.com Oracle License Management Get the most out of
your Oracle investment ZERO DOWNTIME Migrations Performance Health Checks How’s it running? On-Call Support Managed Services Professional Services Where you need it most DBA Services Remote and On-site Apps Oracle APEX SaaS/Paas Custom Development Staff Aug Workforce Capacity on Demand -
Viscosity’s Oracle ACEs The Oracle ACE Program The Oracle ACE
Program recognizes and rewards individuals for their contributions to the Oracle community. Charles Kim CEO | Co-Founder W @racdba W ACE Director Craig Shallahamer Applied AI Scientist W @orapub W ACE Director Rich Niemiec Chief Innovation Officer W @richniemiec W ACE Director Sean Scott Principal Consultant W @oraclesean W ACE Director Gary Gordhamer Principal Consultant W @ggordham W ACE Pro @ViscosityNA www.viscosityna.com -
Viscosity Pillars and Delivery Models Workshops Assessments Proof of Concepts
Training Turnkey Projects Managed Services Oracle & SQL Server PostGres Performance Tuning Data Replication Data Warehousing Analytics Data Integration ERP Blue Prints Database Upgrades APEX EBS Web/Mobile Apps .Net and C# E-Business Suite SAAS/PAAS Azure Gold Partner Cloud Migrations Engineered Systems Oracle Cloud Partner Google Partner AWS Partner Hybrid Cloud @ViscosityNA www.viscosityna.com -
Oracle License Management Get the most out of your Oracle
investment ZERO DOWNTIME Migrations Performance Health Checks How’s it running? On-Call Support Managed Services Professional Services Where you need it most DBA Services Remote and On-site Apps Oracle APEX SaaS/Paas Custom Development Staff Aug Workforce Capacity on Demand @ViscosityNA www.viscosityna.com -
@ViscosityNA www.viscosityna.com Oracle on Docker Running Oracle Databases in Linux
Containers Download a free sample chapter: https:/ /oraclesean.com -
@ViscosityNA www.viscosityna.com @ViscosityNA www.viscosityna.com In the last: • 12 months
• 1-2 years • 2-4 years • 4+ years • Wait... Databases are upgradeable? When did you last upgrade a database? -
@ViscosityNA www.viscosityna.com www.viscosityna.com @ViscosityNA 11.2 12.1 12.2 18c 19c 12.2
11.2.0.3, 11.2.0.4 12.1.0.1, 12.1.0.2 18c 11.2.0.3, 11.2.0.4 12.1.0.1, 12.1.0.2 12.2.0.1 19c 11.2.0.4 12.1.0.2, 12.2.0.1 18c 21c 12.2.0.1 18c, 19c 19c, 21c Database Upgrade Compatibility Matrix (MOS Note 551141.1) 21c 23c -
@ViscosityNA www.viscosityna.com Self-Healing • Resumable • Restore • Automatic healing
• Library of conditions/responses • Transparent • Manual intervention • Check logs • Repair • Simple restart • Revert at any time using GRP (N/A in Standard Edition) -
@ViscosityNA www.viscosityna.com AutoUpgrade - Always Use the Latest Version •
Included with Oracle 19.3 and newer ($OH/rdbms/admin) Outdated! • Download: MOS Note 2485457.1 • No extra license • Requires Java 8+ • ~5MB jar file -
@ViscosityNA www.viscosityna.com AutoUpgrade Command Syntax java -jar autoupgrade.jar -config config.txt
-mode deploy Location of the Java binary. A compatible version is included in newer ORACLE_HOMEs Location of the AutoUpgrade jar Location of the configuration file AutoUpgrade mode (analyze, fixup, upgrade, deploy) -
@ViscosityNA www.viscosityna.com AutoUpgrade - Analyze Read-only analysis to identify issues
Can run any time Can run repeatedly Generates reports for each SID: • HTML format • Text (log) format -
@ViscosityNA www.viscosityna.com AutoUpgrade - Fixups Performs an implicit analyze, +
automatic fixups Does not create a restore point Some fixes are manual only: • Fixes requiring restarts • Changes impacting the host env • Component deinstall • Feature deactivation -
@ViscosityNA www.viscosityna.com AutoUpgrade - Deploy Runs setup, pre-upgrade steps/checks Creates
a Guaranteed Restore Point Runs pre-upgrade fixup scripts Drains load Upgrades database Completes post-upgrade checks & fixes Performs post-upgrade actions -
@ViscosityNA www.viscosityna.com AutoUpgrade - Upgrade Runs an upgrade on a
separate target Performs upgrade, post-upgrade checks/fixes only Does not create a Guaranteed Restore Point Does not perform post-upgrade actions -
@ViscosityNA www.viscosityna.com Configuration Files # Global parameters global.autoupg_log_dir=/opt/oracle/autoupgrade # Database
parameters upg1.sid=PROD upg1.run_utlrp=yes upg1.start_time=now upg1.timezone_upg=yes upg1.upgrade_node=localhost upg1.target_version=19.11 upg1.target_home=/opt/oracle/product/19c/dbhome_1 upg1.source_home=/opt/oracle/product/12.1.0.2/dbhome_1 -
@ViscosityNA www.viscosityna.com Configuration Files - Global Parameters # Global parameters
global.autoupg_log_dir=/opt/oracle/autoupgrade # Database parameters upg1.sid=PROD upg1.run_utlrp=yes upg1.start_time=now upg1.timezone_upg=yes upg1.upgrade_node=localhost upg1.target_version=19.11 upg1.target_home=/opt/oracle/product/19c/dbhome_1 upg1.source_home=/opt/oracle/product/12.1.0.2/dbhome_1 • Global • Prefix = global • Parameters used for all upgrades • e.g. AutoUpgrade log directory • Provides consistent values for all systems • Local parameters can override -
@ViscosityNA www.viscosityna.com Configuration Files - Local Parameters # Global parameters
global.autoupg_log_dir=/opt/oracle/autoupgrade # Database parameters upg1.sid=PROD upg1.run_utlrp=yes upg1.start_time=now upg1.timezone_upg=yes upg1.upgrade_node=localhost upg1.target_version=19.11 upg1.target_home=/opt/oracle/product/19c/dbhome_1 upg1.source_home=/opt/oracle/product/12.1.0.2/dbhome_1 • Local • User defined prefix • Apply to individual databases • Overrides global settings on a per-database basis -
@ViscosityNA www.viscosityna.com Configuration Files - Local Overrides # Global parameters
global.target_version=19.11 global.target_home=/opt/oracle/product/19c/dbhome_1 ... # Database X upgx.sid=APPDB upgx.target_version=19.8 upgx.target_home=/opt/oracle/product/19c/dbhome_2 All databases use these values -
@ViscosityNA www.viscosityna.com Configuration Files - Local Overrides # Global parameters
global.target_version=19.11 global.target_home=/opt/oracle/product/19c/dbhome_1 ... # Database X upgx.sid=APPDB upgx.target_version=19.8 upgx.target_home=/opt/oracle/product/19c/dbhome_2 ...Except database x -
@ViscosityNA www.viscosityna.com Configuration Files - Multiple Databases # Global parameters
global.target_home=/opt/oracle/product/19c/dbhome_1 global.target_version=19.10 # Database 1 upg1.sid=ORCL ... # Database 2 upg2.sid=ORCLCDB -
@ViscosityNA www.viscosityna.com Configuration Files - Multiple Databases # Global parameters
global.target_home=/opt/oracle/product/19c/dbhome_1 global.target_version=19.10 # Database 1 upg1.sid=ORCL ... # Database 2 upg2.sid=ORCLCDB Local configurations for first database -
@ViscosityNA www.viscosityna.com Configuration Files - Multiple Databases # Global parameters
global.target_home=/opt/oracle/product/19c/dbhome_1 global.target_version=19.10 # Database 1 upg1.sid=ORCL ... # Database 2 upg2.sid=ORCLCDB Local configurations for second database -
@ViscosityNA www.viscosityna.com Configuration Files - Add, Remove Parameters global.add_during_upgrade_pfile=/opt/oracle/init.add.during.ora #
Database 1 upg1.add_during_upgrade_pfile=/opt/oracle/initupg1.add.during.ora upg1.del_during_upgrade_pfile=/opt/oracle/initupg1.del.during.ora upg1.add_after_upgrade_pfile=/opt/oracle/initupg1.add.after.ora upg1.del_after_upgrade_pfile=/opt/oracle/initupg1.del.after.ora -
@ViscosityNA www.viscosityna.com Configuration Files - Add, Remove Parameters global.add_during_upgrade_pfile=/opt/oracle/init.add.during.ora #
Database 1 upg1.add_during_upgrade_pfile=/opt/oracle/initupg1.add.during.ora upg1.del_during_upgrade_pfile=/opt/oracle/initupg1.del.during.ora upg1.add_after_upgrade_pfile=/opt/oracle/initupg1.add.after.ora upg1.del_after_upgrade_pfile=/opt/oracle/initupg1.del.after.ora Add database parameters during the upgrade -
@ViscosityNA www.viscosityna.com Configuration Files - Add, Remove Parameters global.add_during_upgrade_pfile=/opt/oracle/init.add.during.ora #
Database 1 upg1.add_during_upgrade_pfile=/opt/oracle/initupg1.add.during.ora upg1.del_during_upgrade_pfile=/opt/oracle/initupg1.del.during.ora upg1.add_after_upgrade_pfile=/opt/oracle/initupg1.add.after.ora upg1.del_after_upgrade_pfile=/opt/oracle/initupg1.del.after.ora Remove database parameters during the upgrade -
@ViscosityNA www.viscosityna.com Configuration Files - Add, Remove Parameters global.add_during_upgrade_pfile=/opt/oracle/init.add.during.ora #
Database 1 upg1.add_during_upgrade_pfile=/opt/oracle/initupg1.add.during.ora upg1.del_during_upgrade_pfile=/opt/oracle/initupg1.del.during.ora upg1.add_after_upgrade_pfile=/opt/oracle/initupg1.add.after.ora upg1.del_after_upgrade_pfile=/opt/oracle/initupg1.del.after.ora Add database parameters after the upgrade -
@ViscosityNA www.viscosityna.com Configuration Files - Add, Remove Parameters global.add_during_upgrade_pfile=/opt/oracle/init.add.during.ora #
Database 1 upg1.add_during_upgrade_pfile=/opt/oracle/initupg1.add.during.ora upg1.del_during_upgrade_pfile=/opt/oracle/initupg1.del.during.ora upg1.add_after_upgrade_pfile=/opt/oracle/initupg1.add.after.ora upg1.del_after_upgrade_pfile=/opt/oracle/initupg1.del.after.ora Remove database parameters after the upgrade -
@ViscosityNA www.viscosityna.com Configuration Files - Remove Hidden Parameters # Global
parameters ... global.remove_underscore_parameters=yes # Database 1 ... upg1.remove_underscore_parameters=no -
@ViscosityNA www.viscosityna.com Configuration Files - Run Scripts # Global scripts
... global.before_action=/opt/oracle/pre_upgrade.ALL.sh # Database scripts ... upg1.before_action=/opt/oracle/pre_upgrade.PROD.sh upg1.after_action=/opt/oracle/post_upgrade.PROD.sh -
@ViscosityNA www.viscosityna.com AutoUpgrade Console # java -jar autoupgrade.jar -config config.txt
-mode analyze AutoUpgrade 23.1.230224 launched with default internal options Processing config file ... +--------------------------------+ | Starting AutoUpgrade execution | +--------------------------------+ 1 CDB(s) plus 2 PDB(s) will be analyzed Type 'help' to list console commands upg> -
@ViscosityNA www.viscosityna.com AutoUpgrade Console upg> Job 100 completed ------------------- Final
Summary -------------------- Number of databases [ 1 ] Jobs finished [1] Jobs failed [0] Please check the summary report at: /scripts/autoupgrade/UTCDB/cfgtoollogs/upgrade/auto/status/status.html /scripts/autoupgrade/UTCDB/cfgtoollogs/upgrade/auto/status/status.log [oracle - UTCDB] ~ -
@ViscosityNA www.viscosityna.com AutoUpgrade Console ========================================== Autoupgrade Summary Report ========================================== [Date]
Tue Mar 07 23:42:28 UTC 2023 [Number of Jobs] 1 ========================================== [Job ID] 101 ========================================== [DB Name] UTCDB [Version Before Upgrade] 19.17.0.0.0 [Version After Upgrade] 21.3.0.0.0 ------------------------------------------ [Stage Name] PRECHECKS [Status] SUCCESS [Start Time] 2023-03-07 23:42:01 [Duration] [Log Directory] /scripts/autoupgrade/UTCDB/UTCDB/101/prechecks [Detail] /scripts/autoupgrade/UTCDB/UTCDB/101/prechecks/utcdb_preupgrade.log Check passed and no manual intervention needed ------------------------------------------ -
@ViscosityNA www.viscosityna.com AutoUpgrade Commands exit // To close and exit
help // Displays help lsj [<option>] [-a <number>] // list jobs by status up to n elements. -f Filter by finished jobs. -r Filter by running jobs. -e Filter by jobs with errors. -p Filter by jobs being prepared. -n <number> Display up to n jobs. -a <number> Repeats the command (in <number> seconds). lsr // Displays the restoration queue lsa // Displays the abort queue tasks // Displays the tasks running clear // Clears the terminal -
@ViscosityNA www.viscosityna.com AutoUpgrade Commands resume -job <number> [-ignore_errors=<ORA-#####,ORA-#####>] // Restarts
a job with option to ignore errors status [<option>] [-a <number>] // Summary of current execution -config Show Config Information -job <number> Summary of a given job -job <number> -c <dbname> Show details of container -a [<number>] Repeats the command (in <number> seconds). restore -job <number> // Restores the database to its state prior to the upgrade restore all_failed // Restores all failed jobs to their previous states prior to the upgrade logs // Displays all the log locations abort -job <number> // Aborts the specified job h[ist] // Displays the command line history /[<number>] // Executes the command specified from the history. // The default is the last command meta // Displays Internal latch count hwinfo // Displays additional information fxlist -job <number> [<option>] // FixUps summary -c <dbname> Container specific FixUps -c <dbname> alter <check> run <yes|no|skip> Update Run Configuration -
@ViscosityNA www.viscosityna.com Monitoring Logging `-- ORCL |-- 100 | |--
autoupgrade_20200902.log | |-- autoupgrade_20200902_user.log | |-- autoupgrade_err.log | `-- prechecks | |-- prechecks_orcl.log | |-- orcl_checklist.cfg | |-- orcl_checklist.json | |-- orcl_checklist.xml | |-- orcl_preupgrade.html | `-- orcl_preupgrade.log `-- temp |-- after_upgrade_pfile_ORCL.ora |-- before_upgrade_pfile_ORCL.ora `-- during_upgrade_pfile_ORCL.ora -
@ViscosityNA www.viscosityna.com AutoUpgrade Logging `-- ORCL |-- 100 | |--
autoupgrade_20200902.log | |-- autoupgrade_20200902_user.log | |-- autoupgrade_err.log | `-- prechecks | |-- prechecks_orcl.log | |-- orcl_checklist.cfg | |-- orcl_checklist.json | |-- orcl_checklist.xml | |-- orcl_preupgrade.html | `-- orcl_preupgrade.log `-- temp |-- after_upgrade_pfile_ORCL.ora |-- before_upgrade_pfile_ORCL.ora `-- during_upgrade_pfile_ORCL.ora ORACLE_SID -
@ViscosityNA www.viscosityna.com Monitoring AutoUpgrade `-- ORCL |-- 100 | |--
autoupgrade_20200902.log | |-- autoupgrade_20200902_user.log | |-- autoupgrade_err.log | `-- prechecks | |-- prechecks_orcl.log | |-- orcl_checklist.cfg | |-- orcl_checklist.json | |-- orcl_checklist.xml | |-- orcl_preupgrade.html | `-- orcl_preupgrade.log `-- temp |-- after_upgrade_pfile_ORCL.ora |-- before_upgrade_pfile_ORCL.ora `-- during_upgrade_pfile_ORCL.ora Job Number -
@ViscosityNA www.viscosityna.com Monitor and publish status • Start a simple
web server on the upgrade host: cd <AUTOUPGRADE BASE DIR> cd autoupgrade/cfgtoollogs/upgrade/auto python -m SimpleHTTPServer 8080 • View the upgrade status page: http://upgradehost:8080/status.html -
@ViscosityNA www.viscosityna.com Tips and tricks Always download the latest version
from MOS Unset: • SQLPATH • ORACLE_PATH • TWO_TASK Remove login.sql Revert to the default glogin.sql -
@ViscosityNA www.viscosityna.com Tips and tricks Run analyze and review Reduce
upgrade durations—fix as much as possible ahead of time • Run ultrp.sql • Collect dictionary, fixed object status • Empty recycle bin Re-run analyze; repeat Practice, practice, practice! -
@ViscosityNA www.viscosityna.com Tips and tricks When migrating: • Run analyze
and fixups on source • Run upgrade on target -
@ViscosityNA www.viscosityna.com Tips and tricks Increase AWR retention Backup: •
TNS files • Parameter, password files, oratab • ORACLE_HOME DBA_DB_LINKS, DBA_DIRECTORIES, external tables Profile IO and CPU loads OraChk -
@ViscosityNA www.viscosityna.com Tips and tricks Check for obsolete features -
APEX, Streams Remove unused features, demo schemas Review SCHEDULER_JOBS Backup wallets • Convert ACL to ACE Confirm backups by restoring them Review optimizer changes PRACTICE -
@ViscosityNA www.viscosityna.com .profile, .bash_profile, .bashrc, etc. Batch scripts cron jobs
Shell aliases Remove aliases to ORACLE_HOME/bin Tips and tricks -
@ViscosityNA www.viscosityna.com Download & stage files • Latest version of
Opatch - Patch 6880880 • Latest version of AutoUpgrade - MOS 2485457.1 • Latest version of Pre-Upgrade JAR - MOS 884522.1 • Latest version of AHF/TFA (includes OraChk) - MOS 2550798.1 -
@ViscosityNA www.viscosityna.com Get familiar with AHF/TFA Did you know? •
OraChk/ExaChk includes a pre-install check: orachk -u -o pre • AHF includes SRDC for install issues: $TFA_HOME/bin/tfactl diagcollect -srdc dbinstall -
@ViscosityNA www.viscosityna.com Opportunities Review documentation Validate backup/recovery procedures • Backups
don’t guarantee recovery! Change credentials for service accounts Upgrade wallets & certificates Upgrade listener security, client/host allowed versions Address technical debt -
@ViscosityNA www.viscosityna.com • Introduced in 18c • Adds two new
variables/paths • ORACLE_BASE_CONFIG • ORACLE_BASE_HOME • New in $ORACLE_HOME/bin: orabaseconfig, orabasehome • New file: $ORACLE_HOME/install/orabasetab • Separates configuration from software • ROOH is the default for Oracle Database 21c and beyond Read-Only Oracle Home (ROOH) -
@ViscosityNA www.viscosityna.com ROOH: ORACLE_BASE_CONFIG > ls -l $(orabaseconfig) drwxr-x--- 3
oracle oinstall 4096 Aug 21 14:39 admin drwxr-x--- 2 oracle oinstall 4096 Aug 21 14:39 audit drwxr-x--- 5 oracle oinstall 4096 Aug 21 14:47 cfgtoollogs drwxr-xr-x 2 oracle dba 4096 Aug 21 14:12 checkpoints drwxr-x--- 2 oracle oinstall 4096 Aug 21 15:58 dbs drwxrwxr-x 1 oracle dba 4096 Aug 21 14:12 diag drwxr-x--- 3 oracle oinstall 4096 Aug 21 14:38 homes drwxr-xr-x 1 oracle dba 4096 Aug 21 13:59 product -
@ViscosityNA www.viscosityna.com ROOH: ORACLE_BASE_HOME > ls -l $(orabasehome) drwxr-x--- 3
oracle oinstall 4096 Aug 21 14:38 assistants drwxr-x--- 2 oracle oinstall 4096 Aug 21 14:38 dbs drwxr-x--- 2 oracle oinstall 4096 Aug 21 14:38 install drwxr-xr-t 4 oracle oinstall 4096 Aug 21 14:42 log drwxr-x--- 5 oracle oinstall 4096 Aug 21 14:38 network drwxr-x--- 4 oracle oinstall 4096 Aug 21 14:38 rdbms -
@ViscosityNA www.viscosityna.com ROOH: $ORACLE_HOME/install/orabasetab > cat $ORACLE_HOME/install/orabasetab #orabasetab file is
used to track Oracle Home associated with Oracle Base /opt/oracle/product/19c/dbhome_1:/opt/oracle:OraDB19Home1:Y: ORACLE_HOME ORACLE_BASE_HOME -
@ViscosityNA www.viscosityna.com A cognitive bias is a systematic pattern of
deviation from norm or rationality in judgment. Individuals create their own "subjective reality" from their perception of the input. An individual's construction of reality, not the objective input, may dictate their behavior in the world. -
@ViscosityNA www.viscosityna.com Cognitive bias blind spots We have backups If
it works in pre-prod it will work in production We have documentation Stress Urgency Confusion Multitasking Coordination Phone calls Messaging Alerts Research Management Doubt, Panic Conflict Documentation doesn't cover: