export OH19="$ORACLE_BASE/product/19.9.0/dbhome_1"
* Create a sample config file if not already prepared
ORACLE_HOME="${OH19}"
java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar -create_sample_file config
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 -
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
echo "create pfile from spfile;"|sqlplus / as sysdba
grep links ${ORACLE_HOME}/dbs/init${ORACLE_SID}.ora
If links found, remove the parameters from the pfile
vi ${ORACLE_HOME}/dbs/init${ORACLE_SID}.ora
sqlplus / as sysdba show parameter recovery
Use this method if no common FRA is setup
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
Ensure this has the correct mount options though, otherwise it will not work
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
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 /
create restore point test19c guarantee flashback database; drop restore point test19c;
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 /
If dst_upgrade_state = 'DATAPUMP (1)', run these:
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 /
value should now say 'NONE'
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;
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;
show parameter db_domain alter system reset db_domain sid='*' scope=spfile;
show parameter service_names alter system set service_names = '&&SID, &SID..world';
show parameter local_listener
host tnsping listener_&SID
sho parameter audit_file_dest
select dbid from wrm$_wr_control wwc where wwc.dbid != (select dbid from v$database) /
exec dbms_swrf_internal.cleanup_database;
@/oracle/scripts/.shb/capture_awr.sql @/oracle/scripts/.shb/capture_cc.sql
– Check dba_registry components are all valid
set lines 1000 col comp_name for a60 select comp_id, comp_name, version, status from dba_registry /
– Ensure there are no compilation errors for sys objects
@?/rdbms/admin/utlrp
@?/rdbms/admin/utlprp 1
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;
/
– Check for remaining invalid objects (make sure none are sys)
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
/
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
purge dba_recyclebin;
select * from v$backup WHERE status != 'NOT ACTIVE';
select 'exec dbms_transaction.purge_lost_db_entry('''||local_tran_id||''');commit;' from sys.dba_2pc_pending;
set lines 1000 pages 100 select 'alter system reset "'||name||'" sid = ''*'' scope = spfile;' from v$parameter where name like '\_%' escape '\';
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.
$OH19/jdk/bin/java -jar $OH19/rdbms/admin/autoupgrade.jar -version
If not latest
cp /oracle/Patches/autoupgrade.jar.19.10.0 $OH19/rdbms/admin/autoupgrade.jar
sqlplus / as sysdba<<EOSQL shu immediate create spfile from pfile; startup sho parameter db_domain sho parameter links EOSQL
$OH19/jdk/bin/java -jar $OH19/rdbms/admin/autoupgrade.jar -config /oracle/scripts/.shb/autoupgrade/autoupgrade_${ORACLE_SID}.cfg -mode analyze
------------------- 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
$OH19/jdk/bin/java -jar $OH19/rdbms/admin/autoupgrade.jar -config /oracle/scripts/.shb/autoupgrade/autoupgrade_${ORACLE_SID}.cfg -mode deploy
------------------- 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
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
vi /home/oracle/logs/autoupgrade/${ORACLE_SID}/${ORACLE_SID}/temp/*pfile_${ORACLE_SID}.ora
Note: changing the real one will have no effect!
vi /etc/oratab
vi /etc/lsnrtab
vi $ORACLE_HOME/network/admin/listener.ora vi $ORACLE_HOME/network/admin/tnsnames.ora
. oraenv
export TNS_ADMIN="${ORACLE_HOME}/network/admin"
ls -al /oracle/autoupgrade/logs/${ORACLE_SID}/${ORACLE_SID}/*/postupgrade/postupgrade.log
If POST-UPGRADE erors:
cat /home/oracle/autoupgrade/logs/${ORACLE_SID}/${ORACLE_SID}/*/postfixups/failed_postfixups.log
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}
@?/rdbms/admin/utldirsymlink.sql
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;
vi $TNS_ADMIN/listener.ora.19c
If you have static connections, you may find all the homes have changed!
cd $OH19/network/admin diff sqlnet.ora.19c sqlnet.ora diff listener.ora.19c listener.ora diff tnsnames.ora.19c tnsnames.ora cd -
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
sqlplus cgkmon/$monpass@appli
sqlplus cgkmon/$monpass@"${ORACLE_SID}"
select * from v$restore_point; drop restore point &restore_point;
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;
vi $ORACLE_HOME/dbs/init${ORACLE_SID}.ora
sqlplus / as sysdba <<EOSQL create spfile from pfile; startup EOSQL
orapwd describe file=${ORACLE_HOME}/dbs/orapw${ORACLE_SID}
$OH19/jdk/bin/java -jar $OH19/rdbms/admin/autoupgrade.jar -config /oracle/scripts/.shb/autoupgrade/autoupgrade_${ORACLE_SID}.cfg -mode analyze
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:
sqlplus / as sysdba @?/xdk/admin/initxml.sql
An upgrade failed due to:
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:
Database check with running exception” (conName=“monproda”,stage=“PRECHECKS”,checkName=“DISK_SPACE_FOR_RECOVERY_AREA”) and in the log file I see:
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
create pfile from spfile; vi init<sid>.ora
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
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
Bug.
19.x:Autoupgrade fails with ORA-00904: “CON_DBID”: Invalid Identifier Error (Doc ID 2602216.1)
Issue is identified and triaged under Bug 29202461 – ORA-00904: “CON_DBID”: INVALID IDENTIFIER EXECUTING AWRUPD12.SQL
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.