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&×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 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&×tamp&&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!
|
|
|
|
| |
|
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.
Copyright © 1996 - 2020
All rights reserved by
Burleson
Oracle ®
is the registered trademark of Oracle Corporation.
|
|
