Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

 dupgdiag.sql Tips

Oracle Database Tips by Donald BurlesonMarch 14, 2015

Question:  How do I use dupgdiag.sql script when upgrading Oracle?

Answer:  The dupgdiag.sql script is run to check integrity of your database prior to upgrading to Oracle 11g:

NAME: DBUPGDIAG.SQL

- 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.
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 Verison
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

 

   
Oracle Training from Don Burleson 

The best on site "Oracle training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!

Oracle training
 
 


 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational

 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster