PRE-UPGRADE

1. PRE-UPGRADE

Run this pre-upgrade tool on 11g database

export ORACLE_SID=db11g_1

 /opt/oracle/ora11204/jdk/bin/java -jar /opt/oracle/ora12201/rdbms/admin/preupgrade.jar

Preupgrade generated files:

/opt/oracle/ora11204/cfgtoollogs/DB11G/preupgrade/preupgrade.log

/opt/oracle/ora11204/cfgtoollogs/DB11G/preupgrade/preupgrade_fixups.sql

/opt/oracle/ora11204/cfgtoollogs/DB11G/preupgrade/postupgrade_fixups.sql

Check invalid objects

set pagesize500
set linesize 100

select substr(comp_name,1,40) comp_name, status, substr(version,1,10) version from dba_registry order by comp_name;

select substr(object_name,1,40) object_name,substr(owner,1,15) owner,object_type from dba_objects where status=’INVALID’ order by owner,object_type;

select owner,object_type,count(*) from dba_objects where status=’INVALID’ group by owner,object_type order by owner,object_type ;

Run this if there is invalid

@$ORACLE_HOME/rdbms/admin/utlrp.sql

Gathering Optimizer Statistics to Decrease Oracle Database Downtime

EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

Verifying Materialized View Refreshes are Complete Before Upgrade

Use this procedure to query the system to determine if there are any materialized view refreshes still in progress. Before upgrading Oracle Database, you must wait until all materialized views have
completed refreshing.

SELECT o.name FROM sys.obj$ o, sys.user$ u, sys.sum$ s WHERE o.type# = 42 AND bitand(s.mflags, 8) =8;

Check of TIMESTAMP WITH TIMEZONE Datatype

Ensuring That No Files Are in Backup Mode and no files need media recovery Before Upgrading

Execute below query to check for the status of the backup:

SQL> SELECT * FROM v$backup WHERE status != ‘NOT ACTIVE’;

Ensure that no files require media recovery:

SQL> SELECT * FROM v$recover_file;

Purging Recycle Bin before upgrade

PURGE DBA_RECYCLEBIN;

HIDDEN PARAMETER

Remove hidden parameter or underscore parameter from pfile or spfile before upgrade

grant this to user if required 

GRANT ADMINISTER DATABASE TRIGGER TO DBADMIN;

mv refresh

refresh Materialized view before upgrade

Check the accounts use Case-Insensitive password version

Log in to SQL*Plus as an administrative user, and enter the following SQL query
SELECT USERNAME,PASSWORD_VERSIONS FROM DBA_USERS;

If there are any 10g versions, suggest you to refer Oracle documentation to fix 10g versions, failing to do so, user accounts with LOCKED after upgrade is completed.

About Password Case Sensitivity

Ensure that you do not have the deprecated parameter SEC_CASE_SENSITIVE_LOGON set to FALSE.

In DB11G –

12.2 Upgrade : Pre-Upgrade message “exclusive_mode_auth Failed Manual fixup recommended” (Doc ID 2310808.1)

Before upgrade these are the settings

sec_case_sensitive_logon – TRUE  – It require to keep this for upgrade – check after upgrade

password version – 10g

select USERNAME  from DBA_USERS where ( PASSWORD_VERSIONS = ’10G ‘  or PASSWORD_VERSIONS = ’10G HTTP ‘) and USERNAME <> ‘ANONYMOUS’;

Starting with Oracle Database release 12.2, Exclusive Mode is the default password-based authentication mode. In Exclusive Mode, accounts which only have the 10G password version (see DBA_USERS.PASSWORD_VERSIONS) are no longer accessible. If your system has any account with only the 10G password version, Exclusive Mode must be switched off prior to the upgrade. See the Network Reference Manual chapter about the SQLNET.ORA parameter SQLNET.ALLOWED_LOGON_VERSION_SERVER for more details on Exclusive Mode. Refer to the Oracle Database Readme 12c Release 2 (12.2) for further information regarding Bug 22031049. (more details)

12.2 Upgrade : Pre-Upgrade message “exclusive_mode_auth Failed Manual fixup recommended” (Doc ID 2310808.1)