====== Autoupgrade to 19c ======

  * [[https://support.oracle.com/epmos/faces/DocumentDisplay?id=2118136.2|Assistant: Download Reference for Oracle Database/GI Update, Revision, PSU, SPU(CPU), Bundle Patches, Patchsets and Base Releases (Doc ID 2118136.2)]]
  * [[https://updates.oracle.com/download/6880880.html|Oracle Global Lifecycle Management - Latest version of OPatch]]

===== General autoupgrade help =====
  * [[https://mikedietrichde.com/database-upgrade-hands-on-lab-oracle-18c-and-19c/hol-19c-main-index-page-oracle-database-19c-hands-on-lab/|Mike Dietrich - Hands-on Lab]]
  * [[https://www.oracle.com/downloads/community/vts-hands-on-labs-downloads.html|Mike Dietrich - Hands-on Lab downloads]]
  * [[https://docs.oracle.com/en/database/oracle/oracle-database/19/upgrd/autoupgrade-command-line-parameters.html|Autoupgrade commmand line parameters]]
  * [[https://support.oracle.com/epmos/faces/DocumentDisplay?id=2485457.1|Download the latest version of the AutoUpgrade Tool (Doc ID 2485457.1) (every 4 to 6 weeks)]]


===== Preupgrade =====

  * Put database in maintenance mode
https://mon.cegeka.be

  * Handy to have this setup
<code>
export OH19="$ORACLE_BASE/product/19.9.0/dbhome_1"
</code>
    
  * Create a sample config file if not already prepared
<code>
ORACLE_HOME="${OH19}"
java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar -create_sample_file config
</code>


  * Protect listener.ora and tnsnames.ora (if updated already)
<code>
cd $OH19/network/admin
cp sqlnet.ora sqlnet.ora.preupgrade_${ORACLE_SID}.$(date '+%Y%m%d%H%M')
cp listener.ora listener.ora.preupgrade_${ORACLE_SID}.$(date '+%Y%m%d%H%M')
cp tnsnames.ora tnsnames.ora.preupgrade_${ORACLE_SID}.$(date '+%Y%m%d%H%M')
ls -altr *ora.preupgrade*
cd -
</code>


  * Ensure current sqlnet.ora contains (only) these (19c is already like this)
<code>
cp -p $TNS_ADMIN/sqlnet.ora $TNS_ADMIN/sqlnet.ora.preupgrade_${ORACLE_SID}.$(date '+%Y%m%d%H%M')
vi $TNS_ADMIN/sqlnet.ora
NAMES.DIRECTORY_PATH = (TNSNAMES, HOSTNAME)
SQLNET.ALLOWED_LOGON_VERSION_SERVER = 11
SQLNET.EXPIRE_TIME = 10
</code>


  * create a pfile and check for links parameters
<code>
echo "create pfile from spfile;"|sqlplus / as sysdba
grep links ${ORACLE_HOME}/dbs/init${ORACLE_SID}.ora
</code>
If links found, remove the parameters from the pfile
<code>
vi ${ORACLE_HOME}/dbs/init${ORACLE_SID}.ora
</code>


  * Enable FRA if not already done
<code>
sqlplus / as sysdba
show parameter recovery
</code>

  * If not set, set one up so we can create a restore point
Use this method if no common FRA is setup
<code>
col data_dir new_value ndata_dir
select min((substr(file_name,1,instr(file_name,'/',-1)-1))) data_dir from dba_data_files where tablespace_name = 'SYSTEM';

host mkdir -p &ndata_dir/fra
alter system set db_recovery_file_dest_size = 10g scope=both;
alter system set db_recovery_file_dest = '&ndata_dir/fra' scope=both;
show parameter recovery
</code>

  * For non-prod, a common NFS mounted filesystem could be used (eg. /fra)
Ensure this has the correct mount options though, otherwise it will not work
<code>
host mkdir -p /fra/&&HOST/&&SID
alter system set db_recovery_file_dest_size = 10g scope=both;
alter system set db_recovery_file_dest = '/fra/&HOST/&SID' scope=both;
show parameter recovery
</code>

  * Check that both of the following queries do something. If one says No rows selected, there’s something wrong with the recovery_file_dest
<code>
set lines 1000
col name for a45
col file_type for a28
select r.name
,      r.space_limit/1024/1024 megs_limit
,      r.space_used/1024/1024 megs_used
,      r.space_reclaimable/1024/1024 megs_reclaimable
,      r.number_of_files
from   sys.v$recovery_file_dest r
,      sys.v$parameter p
where  r.name = p.value
and    p.name = 'db_recovery_file_dest'
/

select * from v$recovery_area_usage
/
</code>

  * Test the fra
<code>
create restore point test19c guarantee flashback database;
drop restore point test19c;
</code>

  * Check for past bad DST upgrade (https://minimalistic-oracle.blogspot.com/2018/02/dstupgradestate-set-to-datapump1-when.html)
<code>
set lines 200
col property_name for a30
select property_name, substr(property_value, 1, 30) value
from database_properties
where property_name like 'DST_%'
order by property_name
/
</code>
If dst_upgrade_state = 'DATAPUMP (1)', run these:
<code>
alter session set events '30090 trace name context forever, level 32';
exec dbms_dst.unload_secondary;
select property_name, substr(property_value, 1, 30) value
from database_properties
where property_name like 'DST_%'
order by property_name
/
</code>
value should now say 'NONE'


  * Deprecated parameters
  * remote_os_authent and utl_file_dir are no longer used and event will cause the upgrade to fail
<code>
alter system reset remote_os_authent sid='*' scope=spfile;
alter system reset utl_file_dir sid='*' scope=spfile;
alter system reset event sid='*' scope=spfile;
</code>


  * Make sure we have enough sga. Ensure mem parameters are 0, sga is at least 3G and pga is at least 800M
<code>
sho parameter memory_max_target
sho parameter memory_target
sho parameter sga_max_size
sho parameter sga_target
sho parameter pga
alter system set sga_max_size = 3G scope=spfile;
alter system set sga_target = 3G scope=spfile;
alter system reset pga_aggregate_limit sid='*' scope=spfile;
alter system set pga_aggregate_target = 800M scope=both;
</code>


  * Check db_domain, service_names and local_listener parameters
  * If db_domain contains a value, reset it
<code>
show parameter db_domain
alter system reset db_domain sid='*' scope=spfile;
</code>


  * If db_domain was set, ensure the service_names have a non '.world' and a '.world' version
<code>
show parameter service_names
alter system set service_names = '&&SID, &SID..world';
</code>

show parameter local_listener
<code>
host tnsping listener_&SID
</code>

  * check audit files go to $ORACLE_BASE/admin/$ORACLE_SID/adump and not somewhere else
<code>
sho parameter audit_file_dest
</code>

  * Prevent AWR_DBIDS_PRESENT post-fixup error in case there are multiple DBIDs present
  * and allow capturing of AWR result set for future comparisons
  * If this query returns any rows
<code>
select dbid
from   wrm$_wr_control wwc
where  wwc.dbid != (select dbid from v$database)
/
</code>

  * then AWR database will need to be cleaned otherwise post-upgrade will fail
<code>
exec dbms_swrf_internal.cleanup_database;
</code>

  * Capture stats for possible future comparison. This will now work if AWR is clean.
<code>
@/oracle/scripts/.shb/capture_awr.sql
@/oracle/scripts/.shb/capture_cc.sql
</code>


-- Check dba_registry components are all valid
<code>
set lines 1000
col comp_name for a60
select comp_id, comp_name, version, status from dba_registry
/
</code>

-- Ensure there are no compilation errors for sys objects
<code>
@?/rdbms/admin/utlrp
</code>
  * or
<code>
@?/rdbms/admin/utlprp 1
</code>


  * Compile materialised views
<code>
set serveroutput on
declare
    l_retval number := 0;
begin
    dbms_output.enable(null);
    dbms_mview.refresh_all_mviews (l_retval,'C','', true, false);
    dbms_output.put_line ('returned: '||l_retval);
end;
/
</code>


-- Check for remaining invalid objects (make sure none are sys)
<code>
set lines 1000 pages 1000
column owner       for a30
column object_name for a30
select owner,
       object_type,
       object_name,
       status
from   dba_objects
where  status = 'INVALID'
order by owner, object_type, object_name
/
</code>


  * Log_archive_dest
  * Change to log_archive_dest_1 if not already done. Verify the path if it is already done.
<code>
show parameter log_archive_dest
alter system reset log_archive_dest sid='*' scope=both;
alter system set log_archive_dest_1='location=&log_arch_dir';
host ls -al &log_arch_dir
</code>


  * Purge DBA recyclebin
<code>
purge dba_recyclebin;
</code>

  * Make sure backups are not running
<code>
select * from v$backup WHERE status != 'NOT ACTIVE';
</code>

  * Remove pending (in doubt) distributed transactions
<code>
select 'exec dbms_transaction.purge_lost_db_entry('''||local_tran_id||''');commit;' from sys.dba_2pc_pending;
</code>


  * Remove all hidden parameters
  * Get a list of all _ parameters
<code>
set lines 1000 pages 100
select 'alter system reset "'||name||'" sid = ''*'' scope = spfile;' from v$parameter where  name like '\_%' escape '\';
</code>


  * 10g Passwords
There was/is a big change when upgrading from a version less than 12.2 to one greater, namely sqlnet.ora parameter SQLNET.ALLOWED_LOGON_VERSION_SERVER is set by default to 12. This meant that 10g passwords were no longer generated when doing ‘alter user’

If 10g passwords are not generated on the database server, clients attempting to connect with a client version less that 11 cannot connect to the database. This will not affect upgrade to 19c as the minimum client level is 11.2.0.4 anyway.
Where it will have an effect on the upgrade to 19c is where there are database users with ONLY 10g passwords. This should be a rare situation and can only happen where a user, originally setup in a 10g environment, has never had its password changed since the database was 10g.
This is the only case where passwords will have to be reset using 'alter user'. Doing a reset with 'identified by values' will not work.

sqlnet.ora parameter SQLNET.ALLOWED_LOGON_VERSION_SERVER can still be set to 11 but it is not advised.

SEC_CASE_SENSITIVE_LOGON. This parameter is now deprecated in favour of the sqlnet.ora parameter SQLNET.ALLOWED_LOGON_VERSION_SERVER and no longer seems to make any difference.


===== Autoupgrade =====

  * Check version
<code>
$OH19/jdk/bin/java -jar $OH19/rdbms/admin/autoupgrade.jar -version
</code>
If not latest
<code>
cp /oracle/Patches/autoupgrade.jar.19.10.0 $OH19/rdbms/admin/autoupgrade.jar
</code>

  * If db_domain was not empty
<code>
sqlplus / as sysdba<<EOSQL
shu immediate
create spfile from pfile;
startup
sho parameter db_domain
sho parameter links
EOSQL
</code>

  * Autoupgrade with analyse option
<code>
$OH19/jdk/bin/java -jar $OH19/rdbms/admin/autoupgrade.jar -config /oracle/scripts/.shb/autoupgrade/autoupgrade_${ORACLE_SID}.cfg -mode analyze
</code>

  * Pre-upgrade results
<code>
------------------- Final Summary --------------------
Number of databases            [ 1 ]

Jobs finished                  [1]
Jobs failed                    [0]
Jobs pending                   [0]

Please check the summary report at:
/home/oracle/logs/autoupgrade_${ORACLE_SID}/cfgtoollogs/upgrade/auto/status/status.html
/home/oracle/logs/autoupgrade_${ORACLE_SID}/cfgtoollogs/upgrade/auto/status/status.log
</code>


  * Autoupgrade with deploy option
<code>
$OH19/jdk/bin/java -jar $OH19/rdbms/admin/autoupgrade.jar -config /oracle/scripts/.shb/autoupgrade/autoupgrade_${ORACLE_SID}.cfg -mode deploy
</code>

  * Upgrade results
<code>
------------------- Final Summary --------------------
Number of databases            [ 1 ]

Jobs finished                  [1]
Jobs failed                    [0]
Jobs pending                   [0]

---- Drop GRP at your convenience once you consider it is no longer needed ----
Drop GRP from ${ORACLE_SID}: drop restore point AUTOUPGRADE_9212_CLND121020


Please check the summary report at:
/home/oracle/logs/autoupgrade_${ORACLE_SID}/cfgtoollogs/upgrade/auto/status/status.html
/home/oracle/logs/autoupgrade_${ORACLE_SID}/cfgtoollogs/upgrade/auto/status/status.log
</code>

  * Clear a properly failed upgrade
<code>
select * from v$restore_point;
shu immediate
startup mount;
flashback database to restore point &grp;
alter database open resetlogs;
exit
or
$OH19/jdk/bin/java -jar $OH19/rdbms/admin/autoupgrade.jar -config /oracle/scripts/.shb/autoupgrade/autoupgrade_${ORACLE_SID}.cfg -restore -all_failed

$OH19/jdk/bin/java -jar $OH19/rdbms/admin/autoupgrade.jar -config /oracle/scripts/.shb/autoupgrade/autoupgrade_${ORACLE_SID}.cfg -mode analyse -clear_recovery_data
</code>

  * If the issue was with the startup file...
<code>
vi /home/oracle/logs/autoupgrade/${ORACLE_SID}/${ORACLE_SID}/temp/*pfile_${ORACLE_SID}.ora
</code>
Note: changing the real one will have no effect!

  * If necessary, disable one or more fixups: https://docs.oracle.com/en/database/oracle/oracle-database/19/upgrd/how-to-override-default-fixups.html


===== Post-upgrade =====

  * Duplicate and comment out the old line in oratab
<code>
vi /etc/oratab
</code>
  * Check listener startup
<code>
vi /etc/lsnrtab
</code>
  * When listener and tnsnames are setup in 19c home, comment them out in the old home
<code>
vi $ORACLE_HOME/network/admin/listener.ora
vi $ORACLE_HOME/network/admin/tnsnames.ora
</code>
  * Reinitialise database environment in 19c
<code>
. oraenv
export TNS_ADMIN="${ORACLE_HOME}/network/admin"
</code>
  * Check the end time on this file for the end of the upgrade
<code>
ls -al /oracle/autoupgrade/logs/${ORACLE_SID}/${ORACLE_SID}/*/postupgrade/postupgrade.log
</code>

If POST-UPGRADE erors:
<code>
cat /home/oracle/autoupgrade/logs/${ORACLE_SID}/${ORACLE_SID}/*/postfixups/failed_postfixups.log
</code>


  * Change the password file format
  * The format of the password file has changed (new format is version 12.2). It is possible to run 19c database with a legacy password file but sysdba, sysbackup, etc… passwords cannot be changed unless the new format is used (by creating a new password file). This also introduces minimum password complexities that cannot be overruled.
<code>
orapwd describe file=${ORACLE_HOME}/dbs/orapw${ORACLE_SID}
orapwd file=${ORACLE_HOME}/dbs/orapw${ORACLE_SID} password=$password force=y
orapwd describe file=${ORACLE_HOME}/dbs/orapw${ORACLE_SID}
</code>

  * Check for symbolic links in directory names
<code>
@?/rdbms/admin/utldirsymlink.sql
</code>

  * Manage database users
<code>
alter profile default limit password_life_time unlimited;
alter user dbsnmp identified by &password account unlock;
alter user system account lock;
alter user sys account lock;
</code>

  * Remove just upgraded static connection from listener.ora.19c
<code>
vi $TNS_ADMIN/listener.ora.19c
</code>

  * Check 19c tnsnames and listener.ora are not corrupted
If you have static connections, you may find all the homes have changed!
<code>
cd $OH19/network/admin
diff sqlnet.ora.19c sqlnet.ora
diff listener.ora.19c listener.ora
diff tnsnames.ora.19c tnsnames.ora
cd -
</code>

  * Restart listeners
<code>
for i in $(ps -ef | grep [t]ns|grep "LSNR_HN"|grep '/19'|awk '{print $10}'); do
    echo $i
    lsnrctl stop $i
    lsnrctl start $i
done
</code>

  * Check appli connection is in tnsnames.ora
<code>
sqlplus cgkmon/$monpass@appli
</code>

  * Test connection via tns
<code>
sqlplus cgkmon/$monpass@"${ORACLE_SID}"
</code>

  * Change ORACLE_HOME in OEM and Commvault and run a full backup

  * Now do user testing



===== Time to set compatible to 19? =====
<code>
select * from v$restore_point;
drop restore point &restore_point;
</code>

<code>
alter system reset db_recovery_file_dest_size sid='*' scope=spfile;
alter system reset db_recovery_file_dest sid='*' scope=spfile;
create pfile from spfile;
</code>

  *  change compatible to '19.0.0'
<code>
vi $ORACLE_HOME/dbs/init${ORACLE_SID}.ora
</code>

<code>
sqlplus / as sysdba <<EOSQL
create spfile from pfile;
startup
EOSQL
</code>

  * Check the password file format is still correct
<code>
orapwd describe file=${ORACLE_HOME}/dbs/orapw${ORACLE_SID}
</code>


  * Check database setup in OEM
  * Oracle database : Target Setup : Monitoring Configuration


===== Errors during upgrade =====
<code>
$OH19/jdk/bin/java -jar $OH19/rdbms/admin/autoupgrade.jar -config /oracle/scripts/.shb/autoupgrade/autoupgrade_${ORACLE_SID}.cfg -mode analyze
</code>
AutoUpgrade tool launched with default options
Processing config file ...
Unable to determine DB unique name for entry upg1

No idea why it fixed the issue but... restrt the database and try again!


https://support.oracle.com/epmos/faces/DocumentDisplay?id=2616365.1
To avoid Java errors like this, XML component must be installed in database.
ERRORS FOUND: During Upgrade
------------------------------------------------------
Identifier CATJAVA 19-04-03 05:53:52
SCRIPT = [/u01/app/oracle/product/18.4.0/dbhome_1/rdbms/admin/initjms.sql]
ERROR = [ORA-29532: Java call terminated by uncaught Java exception:]
STATEMENT = [call sys.dbms_java.loadjava('-v -f -r -s -g PUBLIC rdbms/jlib/aqapi.jar')] 
Bug 30483521  is fixed in 20c, but until then:
<code>
sqlplus / as sysdba
@?/xdk/admin/initxml.sql
</code>

An upgrade failed due to:
<code>
BEGIN xdk_drop_package('org/w3c/dom/validation'); END;
  * 
ERROR at line 1: 
ORA-06550: line 1, column 7: 
PLS-00905: object SYS.XDK_DROP_PACKAGE is invalid 
ORA-06550: line 1, column 7: 
PL/SQL: Statement ignored 


DISK_SPACE_FOR_RECOVERY_AREA:
</code>
  * Problem
Database check with running exception” (conName="monproda",stage="PRECHECKS",checkName="DISK_SPACE_FOR_RECOVERY_AREA")
and in the log file I see:
<code>
2020-06-03 16:49:24.734 INFO
build.hash:255dd7d
build.version:19.9.0
build.date:2020/04/23 15:01:36
build.max_target_version:19
build.supported_target_versions:12.2,18,19
build.type:production
build.label:HEAD
</code>

  * Solution
<code>
create pfile from spfile;
vi init<sid>.ora
</code>
Check the db_recovery_dest parameter. It had a linefeed in it!


Database [historyt] on stage [POSTFIXUPS] has reported an ERROR status\\
Database [historyt] on stage [POSTFIXUPS] has reported an ERROR status but since -restore_on_fail is set\\
the database will be restored and left as was found.  For further details about what caused
the errors, review the log file /home/oracle/autoupgrade/logs/historyt/historyt/121/autoupgrade_20200621_user.log
<code>
Processing table WRH$_AWR_TEST_1
declare
  *
ERROR at line 1:
ORA-00904: "CON_DBID": invalid identifier
ORA-06512: at line 218
ORA-06512: at line 162
ORA-06512: at line 162
</code>

Bug.

19.x:Autoupgrade fails with ORA-00904: “CON_DBID”: Invalid Identifier Error (Doc ID 2602216.1)
  * Problem
Issue is identified and triaged under
Bug 29202461 – ORA-00904: “CON_DBID”: INVALID IDENTIFIER EXECUTING AWRUPD12.SQL
  * Solution
a) Apply patch 29202461 on 19.x database home ,patch can be downloaded from https://updates.oracle.com/download/29202461.html\\
b) Rerun upgrade\\
or if the prereq's were performed as specified, this will no longer happen.