Differences

This shows you the differences between two versions of the page.

--- upgrade_to_oracle_19c [2020/03/27 13:55] – stuart
+++ upgrade_to_oracle_19c [2026/03/17 09:56] (current) – external edit 127.0.0.1
@@ Line 1: / Line 1: @@
-==== Oracle database upgrade to 19c ====
+==== Mike Dietrich Classroom sessions 2020 ====
+  * [[https://mikedietrichde.com/2020/11/10/upgrade-essentials-videos-for-the-virtual-classroom-series-2020/|Upgrade Essentials – Videos for the Virtual Classroom Series 2020]]
+  * [[https://mikedietrichde.com/2026/03/17/does-autoupgrade-support-the-move-option/|Does AutoUpgrade support the MOVE option? - also non-OMF to OMF during migration]]
+Active Data Guard is more than just the Read-Only Standby database. Together with your Active Data Guard license comes the “Rolling Upgrade using Active Data Guard” Feature, better known as DBMS_ROLLING.
+  * [[https://vanpupi.stepi.net/2020/02/dbms_rolling-explained/|DBMS_ROLLING EXPLAINED]]
+==== A worked example of a manual upgrade (including timezone data) from 12.2 to 19c ====
+  *  [[http://www.br8dba.com/upgrade-oracle-database-manually-from-12-2-0-1-to-19c/|Upgrade Oracle Database Manually from 12.2.0.1 to 19c]]
+==== Oracle 19c changes and desuppoerted features ====
+  * [[https://docs.oracle.com/en/database/oracle/oracle-database/19/upgrd/behavior-changes-deprecated-desupport-oracle-database.html#GUID-3BAFD95E-4D00-4F0F-BC80-6064F497F878]]
 === Using Mike Dietrich's AutoUpgrade ===
+This is really the only way to go from now on. It makes life so much easier for the dba. See my [[AutoUpgrade]] page for my real life working document.
   *  [[https://mikedietrichde.com/2019/06/13/create-and-adjust-the-config-file-for-autoupgrade-19c/|Create and adjust the config file for AutoUpgrade 19c]]
   *  [[https://support.oracle.com/epmos/faces/DocumentDisplay?id=2485457.1|AutoUpgrade Tool (Doc ID 2485457.1)]]
   *  [[https://docs.oracle.com/en/database/oracle/oracle-database/19/upgrd/autoupgrade-log-file-structure.html|Database Upgrade Guide]]
+  *  [[http://www.ludovicocaldara.net/dba/getting-oracle-homes-from-orainventory/|Getting the Oracle Homes in a server from the oraInventory]] - Central Inventory needs to be available/correct before starting upgrade
+==== Upgrade to 19c and also apply the latest RU patch at the same time ====
+  *  [[https://mikedietrichde.com/2020/07/28/install-and-patch-in-one-single-action-with-oui/|Install and Patch in one single action with OUI - Mike Dietrich]]
+==== A word on 10g passwords and compatibility ====
+  *  [[https://mikedietrichde.com/2017/04/24/having-some-fun-with-sec_case_sensitive_logon-and-ora-1017/|Having some fun with SEC_CASE_SENSITIVE_LOGON and ORA-1017 - Mike Dietrich]]
+  *  [[https://askdba.org/weblog/2008/11/how-to-changerestore-user-password-in-11g/|How To Change/Restore User Password in 11G]]
+  *  [[http://www.dba-oracle.com/t_save_reset_oracle_user_password.htm|Grab user password using dbms_metadata.get_xml or dbms_metadata.get_ddl]]
+There are two things that influence access to the database with 10g passwords.
+  *  SQLNET.ALLOWED_LOGON_VERSION_SERVER. If this is set to a value lower than 12, the database engine will (also) **generate** a 10g type password when <tt>alter user <user> identified by <password>;</tt> is executed.
+  *  If there are users with ONLY a 10g password, they will not be able to login unless SQLNET.ALLOWED_LOGON_VERSION_SERVER is set to 11 (and listener restarted).
+  *  Parameter SEC_CASE_SENSITIVE_LOGON. If set to false, 10g password is used for authentication (password column in user$). If it is set to true, the 11g, 12c etc. password is used for authentication (spare4 in user$)
 === Download the Hands-On Lab ===
@@ Line 12: / Line 42: @@
 [[https://mikedietrichde.com/hol-19c-setup-setup-for-the-oracle-19c-upgrade-hands-on-lab/]]
+==== Pre-requisites ====
+Autoupgrade sets a guaranteed restore point before it does the upgrade in case anything goes wrong.\\
+I have done an upgrade that went wrong so to make life a lot easier, use a FRA!
+=== Ensure a FRA exists to hold the flashback logs ===
+Note: Flashback does not need to be enabled for this to work.
+<code>
+alter system set db_recovery_file_dest_size = 10g scope=both;
+alter system set db_recovery_file_dest = ‘/cln/acc/ora_data2/archivelog/aarct' scope=both;
+SQL> sho parameter recovery
+NAME                                 TYPE        VALUE
+------------------------------------ ----------- ------------------------------
+db_recovery_file_dest                string      /cln/acc/ora_data2/archivelog/aarct
+db_recovery_file_dest_size           big integer 10G
+recovery_parallelism                 integer     0
+</code>
+==== An example of an autoupgrade config file that has worked successfully ====
+The recompilation and timezone options are commented as 'yes' is the default anyway.
+<code>
+# --------------------
+# Global configuration
+# --------------------
+global.autoupg_log_dir=/home/oracle/autoupgrade/logs
+global.target_version=19
+# -----------------
+# Database number 1
+# -----------------
+upg1.dbname=aarct
+upg1.start_time=now
+upg1.source_home=/cln/tst/ora_bin1/app/oracle/product/11.2.0.4/dbhome_1
+upg1.target_home=/cln/tst/ora_bin1/app/oracle/product/19/dbhome_1
+upg1.sid=aarct
+upg1.log_dir=/home/oracle/autoupgrade/logs/aarct
+upg1.upgrade_node=localhost
+#upg1.run_utlrp=yes
+#upg1.timezone_upg=yes
+</code>
+=== Run the autoupgrade in analyse mode to check that the upgrade will likely succeed ===
+<code>
+export CFGS="/oracle/scripts"
+export OH19="$ORACLE_BASE/product/19/dbhome_1"
+echo $OH19
+/cln/tst/ora_bin1/app/oracle/product/19/dbhome_1
+"${OH19}/jdk/bin/java" -jar "${OH19}/rdbms/admin/autoupgrade.jar" -config "${CFGS}/autoupgrade_aarct.cfg" -mode analyze
+</code>
+=== Run the autoupgrade in deploy mode requesting it to undo what it did should it fail ===
+<code>
+"${OH19}/jdk/bin/java" -jar "${OH19}/rdbms/admin/autoupgrade.jar" -config "${CFGS}/autoupgrade_aarct.cfg" -mode deploy -restore_on_fail
+</code>
+==== Downgrade in case of failed application testing ====
+If the application testing shows that this database version is not going to work there are options.
+=== Compatible parameter was not updated ===
+  *  If the compatible parameter is still the same as what it was before the upgrade and the restore point is still intact, we can restore the database back to the restore point in a few seconds.\\
+NOTE: The whole database will be rewound - dictionary and data!
+<code>
+select * from v$restore_point;
+shu immediate
+startup mount;
+flashback database to restore point &grp;
+alter database open resetlogs;
+</code>
+  *  If the compatible parameter is still the same as what it was before the upgrade and irrespective of whether the restore point is still intact, we can restore the database by downgrading it. This may take 20-30 minutes but works.\\
+The advantage of this method is that the dictionary is downgraded but the data remains as it is.
+<code>
+shutdown immediate
+startup downgrade
+set echo on termout on serveroutput on timing on
+spool /home/oracle/autoupgrade/logs/aarct/catdwgrd.log
+@?/rdbms/admin/catdwgrd.sql
+shutdown immediate
+exit
+</code>
+Switch back to the old ORACLE_HOME. A duplicated entry in /etc/oratab helps here.
+<code>
+export ORACLE_SID=aarct_11
+. oraenv
+</code>
+Start the instance up in upgrade mode to to complete the downgrade!
+<code>
+startup upgrade
+set echo on termout on timing on
+spool /home/oracle/autoupgrade/logs/aarct/catrelod.log
+@?/rdbms/admin/catrelod.sql
+shutdown immediate
+</code>
+Startup and recompile any uncompiled objects
+<code>
+startup
+@?/rdbms/admin/utlrp.sql
+</code>
+=== Compatible parameter was updated ===
+  *  If the compatible parameter was set to the upgraded verson, the only option to return to the old database version is to export the database and import it into ia database in the original home. This obviously takes a lot longer depending on the size of the database.