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.

man target / 

run {
allocate channel test_backup_disk1 type disk format '/u01/backup/%d_%U';
backup incremental level 0 tag 'ORCL_BEFORE_UPG'  database;
backup format  tag 'ORCL_CONTROL_FILE' current controlfile;
release channel test_backup_disk1;
}

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';

Verify DBA_REGISTRY

col COMP_ID for a10
col COMP_NAME for a40
col VERSION for a15
set lines 180
set pages 999
select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry;

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';

Verify DBA_REGISTRY

col COMP_ID for a10
col COMP_NAME for a40
col VERSION for a15
set lines 180
set pages 999
select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry;