Table of Contents
Mike Dietrich Classroom sessions 2020
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.
A worked example of a manual upgrade (including timezone data) from 12.2 to 19c
Oracle 19c changes and desuppoerted features
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.
- 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
A word on 10g passwords and compatibility
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
This is the main setup page for the HOL
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.
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
An example of an autoupgrade config file that has worked successfully
The recompilation and timezone options are commented as 'yes' is the default anyway.
# -------------------- # 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
Run the autoupgrade in analyse mode to check that the upgrade will likely succeed
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
Run the autoupgrade in deploy mode requesting it to undo what it did should it fail
"${OH19}/jdk/bin/java" -jar "${OH19}/rdbms/admin/autoupgrade.jar" -config "${CFGS}/autoupgrade_aarct.cfg" -mode deploy -restore_on_fail
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!
select * from v$restore_point; shu immediate startup mount; flashback database to restore point &grp; alter database open resetlogs;
- 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.
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
Switch back to the old ORACLE_HOME. A duplicated entry in /etc/oratab helps here.
export ORACLE_SID=aarct_11 . oraenv
Start the instance up in upgrade mode to to complete the downgrade!
startup upgrade set echo on termout on timing on spool /home/oracle/autoupgrade/logs/aarct/catrelod.log @?/rdbms/admin/catrelod.sql shutdown immediate
Startup and recompile any uncompiled objects
startup @?/rdbms/admin/utlrp.sql
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.