diag

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__.log.

-- - - - - - - - - - - - - - Script begins here - - - - - - - - - - - - - - 

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
-- - - - - - - - - - - - - - - - Script ends here - - - - - - - - - - - - - -

Found that background process of diag is occupied high pga memory usage in RAC of node 1.
Value of PGA memory usage is captured by “select sid, name, value from v$statname n, v$sesstat s where n.statistic# = s.statistic# and n.name like ‘%memory%’ and s.sid=481order by sid;”

Why occuried high pga memory usage of background process of diag in node 1??

====================================================================================================
SID/Serial : 481,1
Foreground : PID: 14326 – oracle@askmac.cn (DIAG)
Shadow : PID: 14326 – oracle@askmac.cn (DIAG)
Terminal : UNKNOWN/ UNKNOWN
OS User : oracle on askmac.cn
Ora User :
Status Flags: ACTIVE DEDICATED BACKGROUND
Tran Active : NONE
Login Time : Fri 17:10:26
Last Call : Fri 17:10:27 – 8,251.4 min
Lock/ Latch : NONE/ NONE
Latch Spin : NONE
Current SQL statement:
Previous SQL statement:
Session Waits:
EVENT P2TEXT P2 seconds_in_w
—————————– ———— ———— ————
DIAG idle wait where 1 0
====================================================================================================

RAC-node 1
===========

SID NAME VALUE
———- —————————————————————- ———-
481 session uga memory 180984
481 session uga memory max 180984
481 session pga memory 1647496248
481 session pga memory max 1647496248
481 redo k-bytes read (memory) 0
481 redo k-bytes read (memory) by LNS 0
481 workarea memory allocated 0
481 sorts (memory) 0

RAC-node 2
===========

SID NAME VALUE
———- —————————————————————- ———-
481 session uga memory 180984
481 session uga memory max 180984
481 session pga memory 5950520
481 session pga memory max 5950520
481 redo k-bytes read (memory) 0
481 redo k-bytes read (memory) by LNS 0
481 workarea memory allocated 0
481 sorts (memory) 0

Bug 5092124 : PGA MEMORY FOR DIAG PROCESS LEAKS WHEN DUMPING KST TRACE

1. Please provide the output of the following query:
sql> select a.sid,a.program,b.name,c.value from v$session a,v$sysstat b,v$sesstat c where a.program like ‘%DIAG%’ and a.sid = c.sid and b.name like ‘%pga%’ and b.statistic# = c.statistic#;

2. Provide the output of the following command:
ps -ef | grep diag

3. Perform following test case:

1. Confirm the size of DIAG’s PGA.
.
select a.sid,a.program,b.name,c.value from v$session a,v$sysstat b,v$sesstat c where a.program like ‘%DIAG%’ and a.sid = c.sid and b.name like ‘%pga%’
and b.statistic# = c.statistic#;
.
SID PROGRAM NAME VALUE
—– ———————– ———————- ———-
169 oracle@jpdel1380 (DIAG) session pga memory 798524
169 oracle@jpdel1380 (DIAG) session pga memory max 798524
.
2. Connect 50 sessions via sqlplus.
.
3. Kill one of shadow process.
.
Eg.
% ps -ef | grep rac1022
rac1022 15626 15618 0 20:31 ? 00:00:00 oraclerac10221
(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
.
% kill -11 15626
.
4. DIAG dump KST traces under cdmp_xxxxx directory.
.
5. Confirm the size of DIAG’s PGA.
.
SID PROGRAM NAME VALUE
—– ———————– ———————- ———-
169 oracle@jpdel1380 (DIAG) session pga memory 2699068
169 oracle@jpdel1380 (DIAG) session pga memory max 2699068
.
6. Perform the same steps as 2-5.
Confirm the size of DIAG’s PGA.
SID PROGRAM NAME VALUE
—– ———————– ———————- ———-
169 oracle@jpdel1380 (DIAG) session pga memory 3944252
169 oracle@jpdel1380 (DIAG) session pga memory max 3944252

==> PGA for DIAG process increases.

2. Provide the output of the following command:
ps -ef | grep diag

3. Perform following test case:

==> PGA for DIAG process increases.

1. AWR report of one hour from all the instances when the pga usage is high by diag.
2. Database alert.log file from all the instances.
3. init.ora or spfile used in the db.
4. output of the following :
show parameter “_trace_buffer”

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

DIAG Background process may consume Large PGA Size