--- @dbupgdiag.sql -- - - - - - - - - - - - - - Script begins here - - - - - - - - - - - - - - -- NAME: DBUPGDIAG.SQL -- Version: 1.2 -- Executed as SYS as sysdba -- ------------------------------------------------------------------------ -- AUTHOR: -- Raja Ganesh and Agrim Pandit - Oracle Support Services - DataServer Group -- Copyright 2008, Oracle Corporation -- ------------------------------------------------------------------------ -- PURPOSE: -- This script is intended to provide a user friendly output to diagonise -- the status of the database before (or) after upgrade. The script will -- create a file called db_upg_diag__ .log in your local -- working directory. This does not make any DDL / DML modifications. -- -- This script will work in both Windows and Unix platforms from database -- version 9.2 or higher. -- ------------------------------------------------------------------------ -- DISCLAIMER: -- This script is provided for educational purposes only. It is NOT -- supported by Oracle World Wide Technical Support. -- The script has been tested and appears to work as intended. -- You should always run new scripts on a test instance initially. -- ------------------------------------------------------------------------ -- -- col TODAY NEW_VALUE _DATE col VERSION NEW_VALUE _VERSION set termout off select to_char(SYSDATE,'fmMonth DD, YYYY') TODAY from DUAL; select version from v$instance; set termout on set echo off set feedback off set head off set verify off Prompt PROMPT Enter location for Spooled output: Prompt DEFINE log_path = &1 column timecol new_value timestamp column spool_extension new_value suffix SELECT to_char(sysdate,'dd_Mon_yyyy_hhmi') timecol,'.log' spool_extension FROM sys.dual; column output new_value dbname SELECT value || '_' output FROM v$parameter WHERE name = 'db_name'; spool &log_path/db_upg_diag_&&dbname&×tamp&&suffix set linesize 150 set pages 100 set trim on set trims on col Compatible for a35 col comp_id for a12 col comp_name for a40 col org_version for a11 col prv_version for a11 col owner for a12 col object_name for a40 col object_type for a40 col Wordsize for a25 col Metadata for a8 col 'Initial DB Creation Info' for a35 col 'Total Invalid JAVA objects' for a45 col 'Role' for a30 col 'User Existence' for a27 col "JAVAVM TESTING" for a15 Prompt Prompt set feedback off head off select LPAD('*** Start of LogFile ***',50) from dual; select LPAD('Oracle Database Upgrade Diagnostic Utility',44)|| LPAD(TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS'),26) from dual; Prompt Prompt =============== Prompt Hostname Prompt =============== select host_name from v$instance; Prompt Prompt =============== Prompt Database Name Prompt =============== select name from v$database; Prompt Prompt =============== Prompt Database Uptime Prompt =============== SELECT to_char(startup_time, 'HH24:MI DD-MON-YY') "Startup Time" FROM v$instance; Prompt Prompt ================= Prompt Database Wordsize Prompt ================= SELECT distinct('This is a ' || (length(addr)*4) || '-bit database') "WordSize" FROM v$process; Prompt Prompt ================ Prompt Software Version Prompt ================ SELECT * FROM v$version; Prompt Prompt ============= Prompt Compatibility Prompt ============= SELECT 'Compatibility is set as '||value Compatible FROM v$parameter WHERE name ='compatible'; Prompt Prompt ================ Prompt Archive Log Mode Prompt ================ Prompt archive log list Prompt Prompt ================ Prompt Auditing Check Prompt ================ Prompt set head on show parameter audit Prompt Prompt ================ Prompt Cluster Check Prompt ================ show parameter cluster_database Prompt DOC ################################################################ If CLUSTER_DATABASE is set to TRUE, change it to FALSE before upgrading the database ################################################################ # Prompt Prompt =========================================== Prompt Tablespace and the owner of the aud$ table ( IF Oracle Label Security and Oracle Database Vault are installed then aud$ will be in SYSTEM.AUD$) Prompt =========================================== select owner,tablespace_name from dba_extents where segment_name='AUD$' group by owner,tablespace_name; Prompt Prompt ============================================================================ Prompt count of records in the sys.aud$ table where dbid is null- Standard Auditing Prompt ============================================================================ Prompt set head off select count(*) as Records from sys.aud$ where dbid is null; Prompt Prompt Prompt ============================================================================================ Prompt count of records in the system.aud$ when dbid is null, Std Auditing with OLS or DV installed Prompt ============================================================================================ set head off select count(*) from system.aud$ where dbid is null; Prompt Prompt Prompt ============================================================================= Prompt count of records in the sys.fga_log$ when dbid is null, Fine Grained Auditing Prompt ============================================================================= set head off select count(*) from sys.fga_log$ where dbid is null; Prompt Prompt prompt Prompt ========================================== Prompt Oracle Label Security is installed or not Prompt ========================================== set head off SELECT case count(schema) WHEN 0 THEN 'Oracle Label Security is NOT installed at database level' ELSE 'Oracle Label Security is installed ' END "Oracle Label Security Check" FROM dba_registry WHERE schema='LBACSYS'; Prompt Prompt ================ Prompt Number of AQ Records in Message Queue Tables Prompt ================ Prompt SET SERVEROUTPUT ON SIZE 100000 declare V_COUNT NUMBER; cursor c1 is select owner,queue_table from dba_queue_tables where owner in ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP','WMSYS'); begin for c in c1 loop execute immediate 'select count(1) from ' || c.owner || '.' || c.queue_table into v_count; dbms_output.put_line(c.owner || ' - ' || c.queue_table || ' - ' || v_count); end loop; END; / Prompt Prompt ================ Prompt Time Zone version Prompt ================ Prompt SELECT version from v$timezone_file; Prompt Prompt ================ Prompt Local Listener Prompt ================ Prompt select substr(value,1,50) "Local Listener" from v$parameter where name='local_listener'; Prompt Prompt ================ Prompt Default and Temporary Tablespaces By User Prompt ================ Prompt set head on COLUMN USERNAME FORMAT A28 COLUMN TEMPORARY_TABLESPACE FORMAT A22 COLUMN DEFAULT_TABLESPACE FORMAT A22 SELECT username, temporary_tablespace,default_tablespace FROM DBA_USERS; Prompt Prompt Prompt ================ Prompt Component Status Prompt ================ Prompt SET SERVEROUTPUT ON; DECLARE ORG_VERSION varchar2(12); PRV_VERSION varchar2(12); P_VERSION VARCHAR2(10); BEGIN SELECT version INTO p_version FROM registry$ WHERE cid='CATPROC' ; IF SUBSTR(p_version,1,5) = '9.2.0' THEN DBMS_OUTPUT.PUT_LINE(RPAD('Comp ID', 8) ||RPAD('Component',35)|| RPAD('Status',10) ||RPAD('Version', 15)); DBMS_OUTPUT.PUT_LINE(RPAD(' ',8,'-') ||RPAD(' ',35,'-')|| RPAD(' ',10,'-') ||RPAD(' ',15,'-')); FOR x in (SELECT SUBSTR(dr.comp_id,1,8) comp_id, SUBSTR(dr.comp_name,1,35) comp_name, dr.status Status,SUBSTR(dr.version,1,15) version FROM dba_registry dr,registry$ r WHERE dr.comp_id=r.cid and dr.comp_name=r.cname ORDER BY 1) LOOP DBMS_OUTPUT.PUT_LINE(RPAD(SUBSTR(x.comp_id,1,8),8) || RPAD(SUBSTR(x.comp_name,1,35),35)|| RPAD(x.status,10) || RPAD(x.version, 15)); END LOOP; ELSIF SUBSTR(p_version,1,5) != '9.2.0' THEN DBMS_OUTPUT.PUT_LINE(RPAD('Comp ID', 8) ||RPAD('Component',35)|| RPAD('Status',10) ||RPAD('Version', 15)|| RPAD('Org_Version',15)||RPAD('Prv_Version',15)); DBMS_OUTPUT.PUT_LINE(RPAD(' ',8,'-') ||RPAD(' ',35,'-')|| RPAD(' ',10,'-')||RPAD(' ',15,'-')||RPAD(' ',15,'-')|| RPAD(' ',15,'-')); FOR y in (SELECT SUBSTR(dr.comp_id,1,8) comp_id, SUBSTR(dr.comp_name,1,35) comp_name, dr.status Status, SUBSTR(dr.version,1,11) version,org_version,prv_version FROM dba_registry dr,registry$ r WHERE dr.comp_id=r.cid and dr.comp_name=r.cname ORDER BY 1) LOOP DBMS_OUTPUT.PUT_LINE(RPAD(substr(y.comp_id,1,8), 8) || RPAD(substr(y.comp_name,1,35),35)||RPAD(y.status,10) || RPAD(y.version, 15)||RPAD(y.org_version,15)||RPAD(y.prv_version,15)); END LOOP; END IF; END; / SET SERVEROUTPUT OFF Prompt Prompt Prompt Prompt ====================================================== Prompt List of Invalid Database Objects Owned by SYS / SYSTEM Prompt ====================================================== Prompt set head on SELECT case count(object_name) WHEN 0 THEN 'There are no Invalid Objects' ELSE 'There are '||count(object_name)||' Invalid objects' END "Number of Invalid Objects" FROM dba_objects WHERE status='INVALID' AND owner in ('SYS','SYSTEM'); Prompt DOC ################################################################ If there are no Invalid objects below will result in zero rows. ################################################################ # Prompt set feedback on SELECT owner,object_name,object_type FROM dba_objects WHERE status='INVALID' AND owner in ('SYS','SYSTEM') ORDER BY owner,object_type; set feedback off Prompt Prompt ================================ Prompt List of Invalid Database Objects Prompt ================================ Prompt set head on SELECT case count(object_name) WHEN 0 THEN 'There are no Invalid Objects' ELSE 'There are '||count(object_name)||' Invalid objects' END "Number of Invalid Objects" FROM dba_objects WHERE status='INVALID' AND owner in ('SYSMAN','CTXSYS','ORDSYS','MDSYS','EXFSYS','WKSYS','WKPROXY','WK_TEST','OLAPSYS','OUTLIN','TSMSYS', 'FLOWS_FILES','SI_INFORMTN_SCHEMA','ORACLE_OCM','ORDPLUGINS','ORDDATA','DBSNMP'); Prompt DOC ################################################################ If there are no Invalid objects below will result in zero rows. ################################################################ # Prompt set feedback on SELECT owner,object_name,object_type FROM dba_objects WHERE status='INVALID' AND owner in ('SYSMAN','CTXSYS','ORDSYS','MDSYS','EXFSYS','WKSYS','WKPROXY','WK_TEST','OLAPSYS','OUTLIN','TSMSYS', 'FLOWS_FILES','SI_INFORMTN_SCHEMA','ORACLE_OCM','ORDPLUGINS','ORDDATA','DBSNMP') ORDER BY owner,object_type; set feedback off Prompt Prompt ====================================================== Prompt Count of Invalids by Schema Prompt ====================================================== Prompt select owner,object_type,count(*) from dba_objects where status='INVALID' group by owner,object_type order by owner,object_type ; Prompt ============================================================== Prompt Identifying whether a database was created as 32-bit or 64-bit Prompt ============================================================== Prompt DOC ########################################################################### Result referencing the string 'B023' ==> Database was created as 32-bit Result referencing the string 'B047' ==> Database was created as 64-bit When String results in 'B023' and when upgrading database to 10.2.0.3.0 (64-bit) , For known issue refer below articles Note 412271.1 ORA-600 [22635] and ORA-600 [KOKEIIX1] Reported While Upgrading Or Patching Databases To 10.2.0.3 Note 579523.1 ORA-600 [22635], ORA-600 [KOKEIIX1], ORA-7445 [KOPESIZ] and OCI-21500 [KOXSIHREAD1] Reported While Upgrading To 11.1.0.6 ########################################################################### # Prompt SELECT SUBSTR(metadata,109,4) "Metadata", CASE SUBSTR(metadata,109,4) WHEN 'B023' THEN 'Database was created as 32-bit' WHEN 'B047' THEN 'Database was created as 64-bit' ELSE 'Metadata not Matching' END "Initial DB Creation Info" FROM sys.kopm$; Prompt Prompt =================================================== Prompt Number of Duplicate Objects Owned by SYS and SYSTEM Prompt =================================================== Prompt Prompt Counting duplicate objects .... Prompt SELECT count(1) 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') ; Prompt Prompt ========================================= Prompt Duplicate Objects Owned by SYS and SYSTEM Prompt ========================================= Prompt Prompt Querying duplicate objects .... Prompt 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') ; Prompt DOC ################################################################################ Below are expected and required duplicates objects and OMITTED in the report . Without replication installed: INDEX AQ$_SCHEDULES_PRIMARY TABLE AQ$_SCHEDULES If replication is installed by running catrep.sql: INDEX AQ$_SCHEDULES_PRIMARY PACKAGE DBMS_REPCAT_AUTH PACKAGE BODY DBMS_REPCAT_AUTH TABLE AQ$_SCHEDULES If any objects found please follow below article. Note 1030426.6 How to Clean Up Duplicate Objects Owned by SYS and SYSTEM schema Read the Exceptions carefully before taking actions. ################################################################################ # Prompt Prompt ======================== Prompt Password protected roles Prompt ======================== Prompt DOC ################################################################################ In version 11.2 password protected roles are no longer enabled by default so if an application relies on such roles being enabled by default and no action is performed to allow the user to enter the password with the set role command, it is recommended to remove the password from those roles (to allow for existing privileges to remain available). For more information see: Note 745407.1 : What Roles Can Be Set as Default for a User? ################################################################################ # Prompt Prompt Querying for password protected roles .... Prompt break on "Password protected Role" select r.ROLE "Password protected Role", p.grantee "Assigned by default to user" from dba_roles r, dba_role_privs p where r.PASSWORD_REQUIRED = 'YES' and p.GRANTED_ROLE = r.role and p.default_role = 'YES' and p.grantee <> 'SYS' and r.role not in (select role from dba_application_roles); Prompt Prompt ================ Prompt JVM Verification Prompt ================ Prompt SET SERVEROUTPUT ON DECLARE V_CT NUMBER; P_VERSION VARCHAR2(10); BEGIN -- If so, get the version of the JAVAM component EXECUTE IMMEDIATE 'SELECT version FROM registry$ WHERE cid=''JAVAVM'' AND status <> 99' INTO p_version; SELECT count(*) INTO v_ct FROM dba_objects WHERE object_type LIKE '%JAVA%' AND owner='SYS'; IF SUBSTR(p_version,1,5) = '8.1.7' THEN IF v_ct>=6787 THEN DBMS_OUTPUT.PUT_LINE('JAVAVM - Installed properly'); ELSE DBMS_OUTPUT.PUT_LINE('JAVAVM - Not Installed properly'); END IF; ELSIF SUBSTR(p_version,1,5) = '9.0.1' THEN IF v_ct>=8585 THEN DBMS_OUTPUT.PUT_LINE('JAVAVM - Installed properly'); ELSE DBMS_OUTPUT.PUT_LINE('JAVAVM - Not Installed properly'); END IF; ELSIF SUBSTR(p_version,1,5) = '9.2.0' THEN IF v_ct>=8585 THEN DBMS_OUTPUT.PUT_LINE('JAVAVM - Installed properly'); ELSE DBMS_OUTPUT.PUT_LINE('JAVAVM - Not Installed properly'); END IF; ELSIF SUBSTR(p_version,1,6) = '10.1.0' THEN IF v_ct>=13866 THEN DBMS_OUTPUT.PUT_LINE('JAVAVM - Installed properly'); ELSE DBMS_OUTPUT.PUT_LINE('JAVAVM - Not Installed properly'); END IF; ELSIF SUBSTR(p_version,1,6) = '10.2.0' THEN IF v_ct>=14113 THEN DBMS_OUTPUT.PUT_LINE('JAVAVM - Installed properly'); ELSE DBMS_OUTPUT.PUT_LINE('JAVAVM - Not Installed properly'); END IF; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('JAVAVM - NOT Installed. Below results can be ignored'); END; / SET SERVEROUTPUT OFF Prompt Prompt ================================================ Prompt Checking Existence of Java-Based Users and Roles Prompt ================================================ Prompt DOC ################################################################################ There should not be any Java Based users for database version 9.0.1 and above. If any users found, it is faulty JVM. ################################################################################ # Prompt SELECT CASE count(username) WHEN 0 THEN 'No Java Based Users' ELSE 'There are '||count(*)||' JAVA based users' END "User Existence" FROM dba_users WHERE username LIKE '%AURORA%' AND username LIKE '%OSE%'; Prompt DOC ############################################################### Healthy JVM Should contain Six Roles. For 12.2 Seven Roles If there are more or less than six role, JVM is inconsistent. ############################################################### # Prompt SELECT CASE count(role) WHEN 0 THEN 'No JAVA related Roles' ELSE 'There are '||count(role)||' JAVA related roles' END "Role" FROM dba_roles WHERE role LIKE '%JAVA%'; Prompt Prompt Roles Prompt SELECT role FROM dba_roles WHERE role LIKE '%JAVA%'; set head off Prompt Prompt ========================================= Prompt List of Invalid Java Objects owned by SYS Prompt ========================================= SELECT CASE count(*) WHEN 0 THEN 'There are no SYS owned invalid JAVA objects' ELSE 'There are '||count(*)||' SYS owned invalid JAVA objects' END "Total Invalid JAVA objects" FROM dba_objects WHERE object_type LIKE '%JAVA%' AND status='INVALID' AND owner='SYS'; Prompt DOC ################################################################# Check the status of the main JVM interface packages DBMS_JAVA and INITJVMAUX and make sure it is VALID. If there are no Invalid objects below will result in zero rows. ################################################################# # Prompt set feedback on SELECT owner,object_name,object_type FROM dba_objects WHERE object_type LIKE '%JAVA%' AND status='INVALID' AND owner='SYS'; set feedback off Prompt DOC ################################################################# If the JAVAVM component is not installed in the database (for example, after creating the database with custom scripts), the next query will report the following error: select dbms_java.longname('foo') "JAVAVM TESTING" from dual * ERROR at line 1: ORA-00904: "DBMS_JAVA"."LONGNAME": invalid identifier If the JAVAVM component is installed, the query should succeed with 'foo' as result. ################################################################# # Prompt set heading on select dbms_java.longname('foo') "JAVAVM TESTING" from dual; set heading off Prompt SET FEEDBACK ON HEAD ON serveroutput on Prompt =================================== Prompt Oracle Multimedia/InterMedia status Prompt =================================== Prompt DECLARE v_count NUMBER; v_version varchar2(200); v_user_count number; v_status VARCHAR2(200); v_xdb_installed NUMBER; v_xdk_installed NUMBER; v_javavm_installed NUMBER; TYPE string_tt IS TABLE OF VARCHAR2 (100) INDEX BY BINARY_INTEGER; v_user string_tt; BEGIN v_count := 0; v_version := ''; v_user_count := 0; v_status := ''; v_xdb_installed := 0; v_xdk_installed := 0; v_javavm_installed := 0; SELECT 1,version,status INTO v_count, v_version, v_status FROM dba_registry WHERE comp_id='ORDIM'; IF v_count > 0 then DBMS_OUTPUT.PUT_LINE ('.'); DBMS_OUTPUT.PUT_LINE ('Oracle Multimedia/interMedia is installed and listed with the following version: '||v_version||' and status: '||v_status); DBMS_OUTPUT.PUT_LINE ('.'); /* check if all users are installed.*/ v_user(1) := 'ORDSYS'; v_user(2) := 'ORDPLUGINS'; v_user(3) := 'MDSYS'; v_user(4) := 'SI_INFORMTN_SCHEMA'; v_user(5) := 'ORDDATA'; DBMS_OUTPUT.PUT_LINE('Checking for installed Database Schemas...'); FOR i IN v_user.first .. v_user.last LOOP SELECT COUNT(username) INTO v_user_count FROM dba_users WHERE username = v_user(I); /* ORDDATA user only exists starting 11.2 so no test if v_version is different */ IF v_user(i) = 'ORDDATA' AND SUBSTR(V_VERSION,1,6) NOT IN ('11.2.0','12.1.0') THEN v_user_count :=2; END IF; /* SI_INFORMTN_SCHEMA user only exists starting 11.2 so no test if v_version is different */ IF v_user(i) = 'SI_INFORMTN_SCHEMA' AND SUBSTR(V_VERSION,1,2) NOT IN ('10','11','12') THEN v_user_count :=2; END IF; CASE v_user_count WHEN 0 THEN DBMS_OUTPUT.PUT_LINE (v_user(I)||' user does not exist.'); WHEN 2 THEN NULL; -- user does not exist in that version ELSE DBMS_OUTPUT.PUT_LINE (v_user(I)||' user exists.'); END CASE; END LOOP; DBMS_OUTPUT.PUT_LINE('.'); /* Prerequisites Check*/ DBMS_OUTPUT.PUT_LINE ('Checking for Prerequisite Components...'); /* for versions >= 10.2 we will verify, if XDB and XDK are installed and valid */ SELECT COUNT(1) INTO v_javavm_installed FROM dba_registry WHERE comp_id='JAVAVM'; IF v_javavm_installed <> 1 THEN DBMS_OUTPUT.PUT_LINE ('JAVAVM is not installed or not valid'); ELSE DBMS_OUTPUT.PUT_LINE('JAVAVM installed and listed as valid'); END IF; IF SUBSTR(V_VERSION,1,2) IN ('11','12') OR SUBSTR(V_VERSION,1,6) = ('10.2.0') THEN SELECT COUNT(1) INTO v_xdk_installed FROM dba_registry WHERE comp_id='XML'; IF v_xdk_installed <> 1 THEN DBMS_OUTPUT.PUT_LINE ('XDK is not installed or not valid'); ELSE DBMS_OUTPUT.PUT_LINE('XDK installed and listed as valid'); END IF; SELECT COUNT(1) INTO v_xdb_installed FROM dba_registry WHERE comp_id='XDB'; IF v_xdb_installed <> 1 THEN DBMS_OUTPUT.PUT_LINE ('XDB is not installed or not valid'); ELSE DBMS_OUTPUT.PUT_LINE ('XDB installed and listed as valid'); END IF; END IF; /* for versions >= 11 we run validate_ordim */ DBMS_OUTPUT.PUT_LINE ('Validating Oracle Multimedia/interMedia...(no output if component status is valid)'); IF SUBSTR(V_VERSION,1,2) IN ('11','12') THEN EXECUTE IMMEDIATE 'begin validate_ordim; end;'; ELSIF SUBSTR(V_VERSION,1,2) IN ('8.','9.','10') AND v_status <> 'VALID' THEN DBMS_OUTPUT.PUT_LINE('Please run $ORACLE_HOME/ord/im/admin/imchk.sql to display details about invalid interMedia installation'); END IF; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE ('Oracle Multimedia/interMedia is NOT installed at database level'); END; / set feedback off head off select LPAD('*** End of LogFile ***',50) from dual; set feedback on head on Prompt spool off Prompt set heading off set heading off set feedback off select 'Upload db_upg_diag_&&dbname&×tamp&&suffix from "&log_path" directory' from dual; set heading on set feedback on Prompt -- - - - - - - - - - - - - - - - Script ends here - - - - - - - - - - - - - - =================================================================================================================================================================================================== SQL> @dbupgdiag.sql *** Start of LogFile *** Oracle Database Upgrade Diagnostic Utility 01-08-2025 06:26:44 =============== Hostname =============== rac01.int.XXXXXXXX =============== Database Name =============== UGARYD =============== Database Uptime =============== 02:36 07-JAN-25 ================= Database Wordsize ================= This is a 64-bit database ================ Software Version ================ Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production 0 PL/SQL Release 12.1.0.2.0 - Production 0 CORE 12.1.0.2.0 Production 0 TNS for Linux: Version 12.1.0.2.0 - Production 0 NLSRTL Version 12.1.0.2.0 - Production 0 ============= Compatibility ============= Compatibility is set as 12.1.0.2.0 ================ Archive Log Mode ================ Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 219 Next log sequence to archive 221 Current log sequence 221 ================ Auditing Check ================ NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ audit_file_dest string /u01/app/oracle/admin/ugaryd/a dump audit_sys_operations boolean TRUE audit_syslog_level string audit_trail string DB unified_audit_sga_queue_size integer 1048576 ================ Cluster Check ================ NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ cluster_database boolean TRUE cluster_database_instances integer 2 DOC>################################################################ DOC> DOC> If CLUSTER_DATABASE is set to TRUE, change it to FALSE before DOC> upgrading the database DOC> DOC>################################################################ DOC># =========================================== Tablespace and the owner of the aud$ table ( IF Oracle Label Security and Oracle Database Vault are installed then aud$ will be in SYSTEM.AUD$) =========================================== OWNER TABLESPACE_NAME ------------ ------------------------------ SYS SYSTEM ============================================================================ count of records in the sys.aud$ table where dbid is null- Standard Auditing ============================================================================ 0 ============================================================================================ count of records in the system.aud$ when dbid is null, Std Auditing with OLS or DV installed ============================================================================================ select count(*) from system.aud$ where dbid is null * ERROR at line 1: ORA-00942: table or view does not exist ============================================================================= count of records in the sys.fga_log$ when dbid is null, Fine Grained Auditing ============================================================================= 0 ========================================== Oracle Label Security is installed or not ========================================== Oracle Label Security is NOT installed at database level ================ Number of AQ Records in Message Queue Tables ================ SYS - ALERT_QT - 0 SYS - AQ$_MEM_MC - 0 SYS - AQ_EVENT_TABLE - 0 SYS - AQ_PROP_TABLE - 0 SYS - KUPC$DATAPUMP_QUETAB - 0 SYS - SCHEDULER$_EVENT_QTAB - 0 SYS - SCHEDULER$_REMDB_JOBQTAB - 0 SYS - SCHEDULER_FILEWATCHER_QT - 0 SYS - SYS$SERVICE_METRICS_TAB - 0 SYSTEM - DEF$_AQCALL - 0 SYSTEM - DEF$_AQERROR - 0 ================ Time Zone version ================ 18 ================ Local Listener ================ (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP) ================ Default and Temporary Tablespaces By User ================ USERNAME TEMPORARY_TABLESPACE DEFAULT_TABLESPACE ---------------------------- ---------------------- ---------------------- ANONYMOUS TEMP SYSAUX DBSNMP TEMP SYSAUX XDB TEMP SYSAUX APPQOSSYS TEMP SYSAUX GSMADMIN_INTERNAL TEMP SYSAUX GSMCATUSER TEMP SYSTEM SYSBACKUP TEMP SYSTEM OUTLN TEMP SYSTEM DIP TEMP SYSTEM SYSDG TEMP SYSTEM ORACLE_OCM TEMP SYSTEM SYSKM TEMP SYSTEM XS$NULL TEMP SYSTEM GSMUSER TEMP SYSTEM AUDSYS TEMP SYSTEM SYSTEM TEMP SYSTEM SYS TEMP SYSTEM ================ Component Status ================ Comp ID Component Status Version Org_Version Prv_Version ------- ---------------------------------- --------- -------------- -------------- -------------- CATALOG Oracle Database Catalog Views VALID 12.1.0.2.0 CATPROC Oracle Database Packages and Types VALID 12.1.0.2.0 RAC Oracle Real Application Clusters VALID 12.1.0.2.0 XDB Oracle XML Database VALID 12.1.0.2.0 ====================================================== List of Invalid Database Objects Owned by SYS / SYSTEM ====================================================== Number of Invalid Objects ------------------------------------------------------------------ There are no Invalid Objects DOC>################################################################ DOC> DOC> If there are no Invalid objects below will result in zero rows. DOC> DOC>################################################################ DOC># no rows selected ================================ List of Invalid Database Objects ================================ Number of Invalid Objects ------------------------------------------------------------------ There are no Invalid Objects DOC>################################################################ DOC> DOC> If there are no Invalid objects below will result in zero rows. DOC> DOC>################################################################ DOC># no rows selected ====================================================== Count of Invalids by Schema ====================================================== ============================================================== Identifying whether a database was created as 32-bit or 64-bit ============================================================== DOC>########################################################################### DOC> DOC> Result referencing the string 'B023' ==> Database was created as 32-bit DOC> Result referencing the string 'B047' ==> Database was created as 64-bit DOC> When String results in 'B023' and when upgrading database to 10.2.0.3.0 DOC> (64-bit) , For known issue refer below articles DOC> DOC> Note 412271.1 ORA-600 [22635] and ORA-600 [KOKEIIX1] Reported While DOC> Upgrading Or Patching Databases To 10.2.0.3 DOC> Note 579523.1 ORA-600 [22635], ORA-600 [KOKEIIX1], ORA-7445 [KOPESIZ] and DOC> OCI-21500 [KOXSIHREAD1] Reported While Upgrading To 11.1.0.6 DOC> DOC>########################################################################### DOC># Metadata Initial DB Creation Info -------- ----------------------------------- B047 Database was created as 64-bit =================================================== Number of Duplicate Objects Owned by SYS and SYSTEM =================================================== Counting duplicate objects .... COUNT(1) ---------- 0 ========================================= Duplicate Objects Owned by SYS and SYSTEM ========================================= Querying duplicate objects .... DOC> DOC>################################################################################ DOC>Below are expected and required duplicates objects and OMITTED in the report . DOC> DOC>Without replication installed: DOC>INDEX AQ$_SCHEDULES_PRIMARY DOC>TABLE AQ$_SCHEDULES DOC> DOC>If replication is installed by running catrep.sql: DOC>INDEX AQ$_SCHEDULES_PRIMARY DOC>PACKAGE DBMS_REPCAT_AUTH DOC>PACKAGE BODY DBMS_REPCAT_AUTH DOC>TABLE AQ$_SCHEDULES DOC> DOC>If any objects found please follow below article. DOC>Note 1030426.6 How to Clean Up Duplicate Objects Owned by SYS and SYSTEM schema DOC>Read the Exceptions carefully before taking actions. DOC> DOC>################################################################################ DOC># ======================== Password protected roles ======================== DOC> DOC>################################################################################ DOC> DOC> In version 11.2 password protected roles are no longer enabled by default so if DOC> an application relies on such roles being enabled by default and no action is DOC> performed to allow the user to enter the password with the set role command, it DOC> is recommended to remove the password from those roles (to allow for existing DOC> privileges to remain available). For more information see: DOC> DOC> Note 745407.1 : What Roles Can Be Set as Default for a User? DOC> DOC>################################################################################ DOC># Querying for password protected roles .... ================ JVM Verification ================ JAVAVM - NOT Installed. Below results can be ignored ================================================ Checking Existence of Java-Based Users and Roles ================================================ DOC> DOC>################################################################################ DOC> DOC> There should not be any Java Based users for database version 9.0.1 and above. DOC> If any users found, it is faulty JVM. DOC> DOC>################################################################################ DOC># User Existence --------------------------- No Java Based Users DOC> DOC>############################################################### DOC> DOC> Healthy JVM Should contain Six Roles. For 12.2 Seven Roles DOC> If there are more or less than six role, JVM is inconsistent. DOC> DOC>############################################################### DOC># Role ------------------------------ No JAVA related Roles Roles ========================================= List of Invalid Java Objects owned by SYS ========================================= There are no SYS owned invalid JAVA objects DOC> DOC>################################################################# DOC> DOC> Check the status of the main JVM interface packages DBMS_JAVA DOC> and INITJVMAUX and make sure it is VALID. DOC> DOC> If there are no Invalid objects below will result in zero rows. DOC> DOC>################################################################# DOC># no rows selected DOC> DOC>################################################################# DOC> DOC> If the JAVAVM component is not installed in the database (for DOC> example, after creating the database with custom scripts), the DOC> next query will report the following error: DOC> DOC> select dbms_java.longname('foo') "JAVAVM TESTING" from dual DOC> * DOC> ERROR at line 1: DOC> ORA-00904: "DBMS_JAVA"."LONGNAME": invalid identifier DOC> DOC> If the JAVAVM component is installed, the query should succeed DOC> with 'foo' as result. DOC> DOC>################################################################# DOC># select dbms_java.longname('foo') "JAVAVM TESTING" from dual * ERROR at line 1: ORA-00904: "DBMS_JAVA"."LONGNAME": invalid identifier =================================== Oracle Multimedia/InterMedia status =================================== Oracle Multimedia/interMedia is NOT installed at database level PL/SQL procedure successfully completed. *** End of LogFile *** not spooling currently Enter value for log_path: Upload db_upg_diag_UGARYD_08_Jan_2025_0626.log from "" directory
Wednesday, 8 January 2025
dbupgdiag script ---- @dbupgdiag.sql
ANALYZE PARTITION TABLE VALIDATE STRUCTURE CASCADE
ANALYZE PARTITION TABLE VALIDATE STRUCTURE CASCADE Script
ORA-14508: ANALYZE PARTITION TABLE VALIDATE STRUCTURE CASCADE (Doc ID 111990.1)
set linesize 300 pagesize 300
col xx for a100
select 'Prompt '||TABLE_OWNER||'.'||TABLE_NAME||'.'||PARTITION_NAME dba_TAB_PARTITIONS FROM dba_TAB_PARTITIONS WHERE TABLE_NAME ='XXXX';
set pagesize 0
-- spool p.sql
select 'Prompt '||TABLE_OWNER||'.'||TABLE_NAME||'.'||PARTITION_NAME || chr(10) ||'analyze table ' ||TABLE_OWNER||'.'||TABLE_NAME || ' partition (' ||PARTITION_NAME ||') validate structure cascade online into invalid_rows;' || chr(10) || ' select count(*) from invalid_rows ;'
FROM dba_TAB_PARTITIONS
WHERE TABLE_NAME ='XXX';
-- spool off
Subscribe to:
Posts (Atom)
