dbupgdiag.sql—Script to Collect DB Upgrade/Migrate Diagnostic Information

该脚本用来收集升级前及升级后数据库无效和对象等数据库信息，执行后需要指定dbupgdiag.sql 输出日志的路径

Oracle 解释如下：

适用范围：

Oracle Server - Enterprise Edition - Version: 9.2.0.1 to 11.2.0.1 - Release: 9.2 to 11.2
Information in this document applies to any platform.
Oracle Server Enterprise Edition - Version: 9.2.0.1 to 11.2.0.1

脚本的作用：

This script is intended to provide a user friendly output to diagnose the status of the database either before (or) after upgrade. The script will create a file called db_upg_diag_<sid>_<timestamp>.log.

如何/何时使用：

The script needs to be run in SQL*Plus both before the upgrade on the source database and after the upgrade on the upgraded database as SYS user.This will help to determine the status of the database before and after upgrade.

Script As below：

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&&timestamp&&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 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 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 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 not 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 not in ('SYS','SYSTEM') ORDER BY owner,object_type; set feedback off Prompt 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'; Prompt Prompt ========================================= Prompt Duplicate Objects Owned by SYS and SYSTEM Prompt ========================================= Prompt Prompt Querying duplicate objects .... Prompt SELECT object_name, object_type FROM dba_objects WHERE object_name||object_type in  (SELECT object_name||object_type    FROM dba_objects  WHERE owner = 'SYS') AND owner = 'SYSTEM'; Prompt DOC ################################################################################  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 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.  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 Prompt INFO: Below query should succeed with 'foo' as result. set heading on select dbms_java.longname('foo') "JAVAVM TESTING" from dual; set heading off Prompt 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&&timestamp&&suffix from "&log_path" directory' from dual; set heading on set feedback on Prompt

Example：

SQL> @/home/oracle/upgtools/dbupgdiag.sql Enter location for Spooled output: Enter value for 1: dbupgdiag 20-Mar-2014_1216          .log cucds01_ SP2-0606: Cannot create SPOOL file "dbupgdiag/db_upg_diag_cucds01_20-Mar-2014_1216.log"                           *** Start of LogFile *** Oracle Database Upgrade Diagnostic Utility       03-20-2014 12:16:21 =============== Database Uptime =============== 18:48 20-FEB-14 ================= Database Wordsize ================= This is a 64-bit database ================ Software Version ================ Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production PL/SQL Release 11.2.0.4.0 - Production CORE    11.2.0.4.0      Production TNS for Linux: Version 11.2.0.4.0 - Production NLSRTL Version 11.2.0.4.0 - Production ============= Compatibility ============= Compatibility is set as 11.2.0.0.0 ================ Component Status ================ Comp ID Component                                                Status    Version        Org_Version    Prv_Version ------- -------------------------------------------------------    ---------- -------------- -------------- -------------- APEX    Oracle Application Express                           VALID     3.2.1.00.12 CATALOG Oracle Database Catalog Views             VALID     11.2.0.4.0     11.2.0.3.0     11.2.0.3.0 CATJAVA Oracle Database Java Packages              VALID     11.2.0.4.0     11.2.0.3.0     11.2.0.3.0 CATPROC Oracle Database Packages and Types VALID     11.2.0.4.0     11.2.0.3.0     11.2.0.3.0 CONTEXT Oracle Text                                                   VALID     11.2.0.4.0     11.2.0.3.0     11.2.0.3.0 EM      Oracle Enterprise Manager                              VALID     11.2.0.4.0     11.2.0.3.0 EXF     Oracle Expression Filter                                   VALID     11.2.0.4.0     11.2.0.3.0     11.2.0.3.0 JAVAVM  JServer JAVA Virtual Machine                        VALID     11.2.0.4.0     11.2.0.3.0     11.2.0.3.0 ORDIM   Oracle Multimedia                                          VALID     11.2.0.4.0     11.2.0.3.0     11.2.0.3.0 OWB     OWB                                                                    VALID     11.2.0.3.0 OWM     Oracle Workspace Manager                          VALID     11.2.0.4.0     11.2.0.3.0 RUL     Oracle Rules Manager                                     VALID     11.2.0.4.0     11.2.0.3.0     11.2.0.3.0 XDB     Oracle XML Database                                       VALID     11.2.0.4.0     11.2.0.3.0     11.2.0.3.0 XML     Oracle XDK                                                          VALID     11.2.0.4.0     11.2.0.3.0     11.2.0.3.0 ====================================================== List of Invalid Database Objects Owned by SYS / SYSTEM ====================================================== Number of Invalid Objects ------------------------------------------------------------------ There are no Invalid Objects DOC>################################################################ DOC> If there are no Invalid objects below will result in zero rows. DOC>################################################################ DOC># no rows selected ================================ List of Invalid Database Objects ================================ Number of Invalid Objects ------------------------------------------------------------------ There are 3 Invalid objects DOC>################################################################ DOC> If there are no Invalid objects below will result in zero rows. DOC>################################################################ DOC># OWNER        OBJECT_NAME                              OBJECT_TYPE ------------ ---------------------------------------- ---------------------------------------- TCUAT        AIAB_BSED_PKG                            PACKAGE BODY TCUAT        AIA_EXPENSE_REPORT_PKG                   PACKAGE BODY TCUAT        AIAC_PAYROLL_PKG                         PACKAGE BODY 3 rows selected. ============================================================== Identifying whether a database was created as 32-bit or 64-bit ============================================================== 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># 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) ----------          4 ========================================= Duplicate Objects Owned by SYS and SYSTEM ========================================= Querying duplicate objects .... OBJECT_NAME                              OBJECT_TYPE ---------------------------------------- ---------------------------------------- AQ$_SCHEDULES                            TABLE AQ$_SCHEDULES_PRIMARY                    INDEX DBMS_REPCAT_AUTH                         PACKAGE DBMS_REPCAT_AUTH                         PACKAGE BODY DOC> 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># ================ JVM Verification ================ ================================================ Checking Existence of Java-Based Users and Roles ================================================ 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># User Existence --------------------------- No Java Based Users DOC> DOC>############################################################### DOC> Healthy JVM Should contain Six Roles. DOC> If there are more or less than six role, JVM is inconsistent. DOC>############################################################### DOC># Role ------------------------------ There are 6 JAVA related roles Roles ROLE ------------------------------ JAVA_DEPLOY JAVAUSERPRIV JAVAIDPRIV JAVASYSPRIV JAVADEBUGPRIV JAVA_ADMIN ========================================= 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> If there are no Invalid objects below will result in zero rows. DOC> DOC>################################################################# DOC># no rows selected INFO: Below query should succeed with 'foo' as result. JAVAVM TESTING -------------------------------------------------------- foo                             *** End of LogFile *** not spooling currently Upload db_upg_diag_cucds01_20-Mar-2014_1216.log from "/home/oracle/upgtools/" directory

Reference：

Script to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.sql) [ID 556610.1]