TAKS

DESCRIPTION

Clean up the database

• As SYS User, Empty the recycle bin
• PURGE DBA_RECYCLEBIN;
• Check for INVALID objects in SYS and SYSTEM
• col owner format a15
• col object_name format a45
• col object_type format a15
• select owner, object_name, object_type from dba_objects where owner in ('SYS','SYSTEM') and status='INVALID' order by owner,object_type;
• Check for duplicate objects in SYS and SYSTEM
• SELECT object_name, object_type, subobject_name, object_id
  FROM dba_objects
  WHERE object_name||object_type in
     (SELECT object_name||object_type  
      FROM dba_objects
      WHERE owner = 'SYS')
  AND owner = 'SYSTEM'
  AND object_name NOT in ('AQ$_SCHEDULES','AQ$_SCHEDULES_PRIMARY','DBMS_REPCAT_AUTH','DBMS_REPCAT_AUTH') ;

Check materialized views

• Check the status of all materialized views (MV), and refresh any materialized views that are not fresh.
• SELECT o.name FROM sys.obj$ o, sys.user$ u, sys.sum$ s WHERE o.type# = 42 AND bitand(s.mflags, 8) =8; 

Check Time Zone setting

• Use the following query to detect the timezone of your source database:
• select version from v$timezone_file;
• Default time zone version for Oracle Database 12c release 2 (12.2) is V26
• If the source database Time Zone version is higher than the target database, then apply the Time Zone patch on the target database to match the source database version.
  Time Zone defaults are in located in the path $ORACLE_HOME/oracore/zoneinfo.

Removing EM Repository (if exists)

• Execute emremove.sql script. The script will be located in new target  ORACLE_HOME/rdbms/admin
• SET ECHO ON
  SET SERVEROUTPUT ON
  @emremove.sql
• After emremove.sql completes, you must manually remove ORACLE_HOME/HOSTNAME_SID and ORACLE_HOME/oc4j/j2ee/OC4J_DBConsole_HOSTNAME_SID directories from your file system.

Check accounts using ONLY 10g password version

• As SYS, enter the following SQL query
• select USERNAME from DBA_USERS where ( PASSWORD_VERSIONS = '10G ' or PASSWORD_VERSIONS = '10G HTTP ') and USERNAME <> 'ANONYMOUS';
• If there is any account, then:
• 1- Configure the system so that it is not running in Exclusive Mode by editing the setting of the SQLNET.ORA parameter SQLNET.ALLOWED_LOGON_VERSION_SERVER to a level appropriate for affected accounts. For example: SQLNET.ALLOWED_LOGON_VERSION_SERVER=11
• 2- After you make this change, proceed with the upgrade
• 3- After the upgrade completes, use the following command syntax to expire the accounts you found in step 1, where username is the name of a user returned from the query in step 1:
  ALTER USER username PASSWORD EXPIRE;
• 4- Ask the users for whom you have expired the passwords to log in.
• 5- When these users log in, they are prompted to reset their passwords. The system internally generates the missing 11G and 12C password versions for their account, in addition to the 10G password version. The 10G password version is still present, because the system is running in the permissive mode.

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

• As SYS execute:  show parameter SEC_CASE_SENSITIVE_LOGON. If is TRUE or FALSE, remove it from the init.ora File and restart the DB

Gather dictionary statistics to decrease Oracle Database downtime

EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

Back up the database (recommended)

• Back up the database, create a guaranteed flashback restore point, or both.
• Test your fallback strategies at least once before your upgrade window.
• Ensure that you have fallback strategies for issues both during upgrade, and after upgrade.

TASK

DESCRIPTION

Query for any INVALID Objects

SELECT substr(owner,1,12) owner, substr(object_name,1,12) object, substr(object_type,1,30) object, status from dba_objects WHERE status <> 'VALID';

Run postinstallation SQL scripts as SYS

• @C:\oracle\product\12.2.0\dbhome_1\rdbms\admin\utlrp.sql

Review upgrade logs and trace files

• $ORACLE_BASE/cfgtoollogs/DBUA/upgradeTimestamp
  (Look under $ORACLE_HOME if $ORACLE_BASE is not set). Also, folders with the system identifier (SID) of individual database are in this timestamp folder. The SID folders contain files for individual databases for the preupgrade and upgrade process.
  alert_SID.log

Verify upgrade status for CATALOG and CATPROC components

• select substr(comp_name, 1, 45) as comp_name, substr(version,1,8) as version , substr(status,1,8) as status from dba_registry;
  Look for version and status changes.

Run Oracle Database 12.2 Post-Upgrade Status Tool (utlu122s.sql)

• @C:\oracle\product\12.2.0\dbhome_1\rdbms\admin\utlu122s.sql

Update time zone settings

• If Time Zone version at source database is lower than the target database, then run Time Zone conversion after the upgrade.
  Time Zone defaults are in the path $ORACLE_HOME/oracore/zoneinfo

Create Oracle Database system files

• Create an SPFILE from the PFILE
• Gather new Oracle Cost-Based Optimizer (CBO) statistics
• Back up the database