SET DEF ^
SET AUTOPRINT ON
SET ECHO OFF FEED OFF VER OFF SHOW OFF HEA OFF LIN 2000 NEWP NONE PAGES 0 SQLC MIX TAB ON TRIMS ON TI OFF TIMI OFF ARRAY 100 NUMF "" SQLP SQL> SUF sql BLO . RECSEP OFF APPI OFF;
SET LONG 20000 LONGCHUNK 20000
COL database_name_short NEW_V database_name_short FOR A10;
SELECT UPPER(SUBSTR(SYS_CONTEXT('USERENV', 'DB_NAME'), 1, 10)) database_name_short FROM DUAL;
SELECT SUBSTR('^^database_name_short.', 1, INSTR('^^database_name_short..', '.') - 1) database_name_short FROM DUAL;
SELECT TRANSLATE('^^database_name_short.',
'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789 ''`~!@#$%^*()-_=+[]{}\|;:",.<>/?'||CHR(0)||CHR(9)||CHR(10)||CHR(13)||CHR(38),
'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789') database_name_short FROM DUAL;
COL host_name_short NEW_V host_name_short FOR A30;
SELECT SUBSTR(SYS_CONTEXT('USERENV', 'SERVER_HOST'), 1, 30) host_name_short FROM DUAL;
SELECT SUBSTR('^^host_name_short.', 1, INSTR('^^host_name_short..', '.') - 1) host_name_short FROM DUAL;
SELECT TRANSLATE('^^host_name_short.',
'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789 ''`~!@#$%^*()-_=+[]{}\|;:",.<>/?'||CHR(0)||CHR(9)||CHR(10)||CHR(13)||CHR(38),
'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789') host_name_short FROM DUAL;
COL rdbms_version NEW_V rdbms_version FOR A17;
SELECT version rdbms_version FROM v$instance;
COL rdbms_role NEW_V rdbms_role FOR A7;
select substr(DATABASE_ROLE,-7,7) rdbms_role from v$database;
COL rdbms_type NEW_V rdbms_type FOR A7;
select decode(PARALLEL,'YES','RAC_'||INSTANCE_NUMBER,'NO','SINGLE') rdbms_type from v$instance;
COL time_stamp NEW_V time_stamp FOR A15;
SELECT TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') time_stamp FROM DUAL;
variable L_DBID number;
variable L_INST_NUM number;
variable beg_snap number;
variable end_snap number;
variable snap_sec number;
variable orabase varchar2(4000);
variable orahome varchar2(4000);
begin
select (extract(hour from snap_interval)*60+extract(minute from snap_interval))*60 into :snap_sec from dba_hist_wr_control;
select max(snap_id)-5 into :beg_snap from dba_hist_snapshot;
select max(snap_id) into :end_snap from dba_hist_snapshot;
select dbid into :L_DBID from v$database;
select instance_number into :L_INST_NUM from v$instance;
end;
/
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES',FALSE);
alter session set nls_timestamp_format='YYYY-MM-DD HH24:MI:SS';
alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';
SPO dbcheck_087_^^database_name_short._^^rdbms_role._^^rdbms_type._^^host_name_short._^^rdbms_version._^^time_stamp..html;
PRO <html>
PRO
PRO <head>
PRO <title>dbcheck_^^database_name_short._^^rdbms_role._^^rdbms_type._^^host_name_short._^^rdbms_version._^^time_stamp..html</title>
PRO
PRO <style type="text/css">
PRO body {font:8pt Arial,Helvetica,Verdana,Geneva,sans-serif; color:black; background:white;}
PRO pre {font:8pt Monaco,"Courier New",Courier,monospace;}
PRO h1 {font-size:15pt; font-weight:bold; color:#000000;}
PRO h2 {font-size:10pt; font-weight:bold; color:#000000;}
PRO li {font-size:8pt; font-weight:bold; color:#336699; padding:0.1em 0 0 0;}
PRO table {font-size:8pt; color:black; background:white;}
PRO th {font-weight:bold; background:#336699; color:#ffffff; vertical-align:bottom; padding-left:3pt; padding-right:3pt; padding-top:1pt; padding-bottom:1pt;}
PRO th.l {font-weight:bold; background:#986634; color:#ffffff; vertical-align:bottom; padding-left:3pt; padding-right:3pt; padding-top:1pt; padding-bottom:1pt;}
PRO a {font-weight:bold; text-decoration: none; color: white;}
PRO td {text-align:left; background:#ffffff; vertical-align:top; padding-left:3pt; padding-right:3pt; padding-top:1pt; padding-bottom:1pt; }
PRO td.w {font:8pt Arial,Helvetica,Geneva,sans-serif;color:black;background:White; vertical-align:top;}
PRO td.y {font:8pt Arial,Helvetica,Geneva,sans-serif;color:black;background:#FFFFCC; vertical-align:top;}
PRO td.x {font:8pt Arial,Helvetica,Geneva,sans-serif;color:black;background:#FF99CC; vertical-align:top;}
PRO td.c {text-align:center;}
PRO td.l {text-align:left;}
PRO td.r {text-align:right;}
PRO font.n {font-size:8pt; font-style:italic; color:#336699;}
PRO font.f {font-size:8pt; color:#999999;}
PRO </style>
PRO
PRO </head>
PRO <body>
PRO <h1>Oracle Database Health Check Report v0.8.7 - ^^database_name_short. </h1>
PRO <h2>Support Oracle Database Version 11gR2,12c,18c</h2>
PRO <h2>Oracle<h2>
PRO <h2>dbcheck<h2>
PRO <hr />
PRO <h2> </h2>
PRO
PRO <h1>Main Report</h1>
PRO <ul>
PRO <li><a style="color:#663300" href="#10">1. Database Information</a></li>
PRO <li><a style="color:#663300" href="#20">2. Schema Information</a></li>
PRO <li><a style="color:#663300" href="#30">3. ASM Configure</a></li>
PRO <li><a style="color:#663300" href="#40">4. Performance</a></li>
PRO <li><a style="color:#663300" href="#50">5. Backup and Recover</a></li>
PRO </ul>
PRO <hr />
PRO
PRO <a name="10"></a>
PRO <h1>1. Database Information</h1>
PRO <ul>
PRO <li><a style="color:#663300" href="#101">1.1 Database Info</a></li>
PRO <li><a style="color:#663300" href="#102">1.2 Database Size Detail</a></li>
PRO <li><a style="color:#663300" href="#103">1.3 Database Component and Patch</a></li>
PRO <li><a style="color:#663300" href="#104">1.4 Database Parameter</a></li>
PRO <li><a style="color:#663300" href="#105">1.5 Memory Info</a></li>
PRO <li><a style="color:#663300" href="#106">1.6 SGA Dynamic Components</a></li>
PRO <li><a style="color:#663300" href="#107">1.7 Control File</a></li>
PRO <li><a style="color:#663300" href="#108">1.8 Log File</a></li>
PRO <li><a style="color:#663300" href="#109">1.9 Tablespace Usage</a></li>
PRO <li><a style="color:#663300" href="#110">1.10 Datafile Information</a></li>
PRO <li><a style="color:#663300" href="#113">1.11 Resource Limit</a></li>
PRO <li><a style="color:#663300" href="#114">1.12 Serivce</a></li>
PRO <li><a style="color:#663300" href="#115">1.13 SYSDBA User</a></li>
PRO <li><a style="color:#663300" href="#117">1.14 Autotask</a></li>
PRO <li><a style="color:#663300" href="#118">1.15 DST Check</a></li>
PRO <li><a style="color:#663300" href="#119">1.16 Audit</a></li>
PRO </ul>
PRO <hr />
PRO <a name="101"></a>
PRO <h2>1.1 Database Info</h2>
PRO <table border="1px" bordercolor="#000000" cellspacing="0px" style="border-collapse:collapse">
PRO <tr>
PRO <th width="200">Option</th>
PRO <th width="600">Values</th>
PRO </tr>
SELECT '<tr><td class=y>'||'DB Name' ||'</td><td>'||value||'</td></tr>' from v$parameter where name='db_name';
SELECT '<tr><td class=y>'||'DB Unique name' ||'</td><td>'||value||'</td></tr>' from v$parameter where name='db_unique_name';
SELECT '<tr><td class=y>'||'Instance Name' ||'</td><td>'||listagg(instance_name,' & ') within group (order by instance_name)||'</td></tr>' from gv$instance;
SELECT '<tr><td class=y>'||'DB Startup Time' ||'</td><td>'||to_char(startup_time, 'YYYY-MM-DD HH24:MI:SS')||'</td></tr>' from v$instance;
SELECT '<tr><td class=y>'||'CPU Information' ||'</td><td>'||(select to_char(value)||' Sockets, ' T from v$osstat where stat_name='NUM_CPU_SOCKETS')||(select to_char(value)||' Cores, ' T from v$osstat where stat_name='NUM_CPU_CORES')||(select TO_CHAR(value)||' Threads. ' T FROM v$osstat WHERE stat_name='NUM_CPUS')||'</td></tr>' from dual;
SELECT '<tr><td class=y>'||'Physical Memory Size' ||'</td><td>'||min(to_char(round(value/1024/1024,0)))||' MB</td></tr>' from gv$osstat where stat_name='PHYSICAL_MEMORY_BYTES';
SELECT '<tr><td class=y>'||'OS Platform' ||'</td><td>'||dbms_utility.port_string||'</td></tr>' from dual;
SELECT '<tr><td class=y>'||'RDBMS Release' ||'</td><td>'||banner||'</td></tr>' from v$version where rownum < 2;
SELECT '<tr><td class=y>'||'NLS_TERRITORY' ||'</td><td>'||value||'</td></tr>' from v$nls_parameters where parameter='NLS_TERRITORY';
SELECT '<tr><td class=y>'||'NLS_LANGUAGE' ||'</td><td>'||value||'</td></tr>' from v$nls_parameters where parameter='NLS_LANGUAGE';
SELECT '<tr><td class=y>'||'NLS_CHARACTERSET' ||'</td><td>'||value||'</td></tr>' from v$nls_parameters where parameter='NLS_CHARACTERSET';
SELECT '<tr><td class=y>'||'NLS_NCHAR_CHARACTERSET' ||'</td><td>'||value||'</td></tr>' from v$nls_parameters where parameter='NLS_NCHAR_CHARACTERSET';
SELECT '<tr><td class=y>'||'DB_BLOCK_SIZE' ||'</td><td>'||value||'</td></tr>' from v$parameter where name='db_block_size';
SELECT '<tr><td class=y>'||'Number of Concurrent Users' ||'</td><td>'||count(*)||'</td></tr>' from gv$session where username is not null;
SELECT '<tr><td class=y>'||'Number of Active Concurrent Users'||'</td><td>'||count(*)||'</td></tr>' from gv$session where username is not null and sid not in (select sid from v$mystat) and status='ACTIVE';
SELECT '<tr><td class=y>'||'Redo Log Generation Rate (KB/H)' ||'</td><td>'||TO_CHAR(SUM(BLOCKS*BLOCK_SIZE)/1024/168,99999999.99)||'</td></tr>' from V$ARCHIVED_LOG where FIRST_TIME > sysdate -7;
SELECT '<tr><td class=y>'||'Max Object ID' ||'</td><td '||case when max(data_object_id)>100000000 then 'class=x' else null end||'>'||max(data_object_id)||'</td></tr>' from dba_objects;
SELECT '<tr><td class=y>'||'Current SCN' ||'</td><td '||case when current_scn>5000000000000 then 'class=x' else null end||'>'||current_scn||'</td></tr>' from v$database;
SELECT '<tr><td class=y>'||'Max SCN Increment per Sec' ||'</td><td '||case when round(max(scn_inc)/:snap_sec)>10000 then 'class=x' else null end||'>'||round(max(scn_inc)/:snap_sec) ||'</td></tr>' from (select snap_id,c1-lag(c1, 1) over(order by snap_id) scn_inc from(select SNAP_ID,sum(VALUE) c1 from DBA_HIST_SYSSTAT where STAT_NAME='calls to kcmgas' group by snap_id));
SELECT '<tr><td class=y>'||'SCN Health Check(Days)' ||'</td><td '||case when v.indicator < 10 then 'class=x' else null end||'>'||v.indicator||'</td></tr>' from (select current_scn,indicator from (select to_char(SYSDATE,'YYYY/MM/DD HH24:MI:SS') DATE_TIME, current_scn,trunc((((((to_number(to_char(sysdate,'YYYY'))-1988)*12*31*24*60*60)+((to_number(to_char(sysdate,'MM'))-1)*31*24*60*60)+(((to_number(to_char(sysdate,'DD'))-1))*24*60*60)+(to_number(to_char(sysdate,'HH24'))*60*60)+(to_number(to_char(sysdate,'MI'))*60)+(to_number(to_char(sysdate,'SS'))) ) * (16*1024)) - current_scn ) / (16*1024*60*60*24)) indicator from v$database)) v;
SELECT '<tr><td class=y>'||'Datafile File Number Useage(%)' ||'</td><td '||case when round(100*(select count(*) from dba_data_files)/(select value from v$parameter where name='db_files'),2)>80 then 'class=x' else null end||'>'||round(100*(select count(*) from dba_data_files)/(select value from v$parameter where name='db_files'),2)||'</td></tr>' from dual;
SELECT '<tr><td class=y>'||'Bitcoin Hacker Checking' ||'</td><td '||case when count(*)>0 then 'class=x' else null end||'>'||count(*)||'</td></tr>' from dba_objects where object_name like '%DBMS_CORE_INTERNAL%' or object_name like '%DBMS_SYSTEM_INTERNAL%' or object_name like '%DBMS_SUPPORT_INTERNAL%' or object_name like '%DBMS_STANDARD_FUN9%';
SELECT '<tr><td class=y>'||'Force Logging Enabled?' ||'</td><td>'||force_logging||'</td></tr>' from v$database;
SELECT '<tr><td class=y>'||'Supplemental Log Enabled?' ||'</td><td>'||supplemental_log_data_min||'</td></tr>' from v$database;
SELECT '<tr><td class=y>'||'Flashback Enabled?' ||'</td><td '||case when flashback_on = 'YES' then 'class=x' else null end||'>'||flashback_on||'</td></tr>' from v$database;
SELECT '<tr><td class=y>'||'Archiving Enabled?' ||'</td><td '||case when decode(log_mode,'ARCHIVELOG','YES','NO') = 'NO' then 'class=x' else null end||'>'||decode(log_mode,'ARCHIVELOG','YES','NO')||'</td></tr>' from v$database;
PRO </table>
PRO <a name="102"></a>
PRO <h2>1.2 Database Size Detail</h2>
PRO <table border="1px" bordercolor="#000000" cellspacing="0px" style="border-collapse:collapse">
PRO <tr>
PRO <th width="100">ID </th>
PRO <th width="100">Segment </th>
PRO <th width="100">Contorlfile </th>
PRO <th width="100">Tablespace </th>
PRO <th width="100">Datafile </th>
PRO <th width="100">Tempfile </th>
PRO <th width="100">Online Logfile </th>
PRO <th width="100">Standby Logfile</th>
PRO </tr>
SELECT CHR(10)||'<tr>'||CHR(10)||
'<td class=y>'||v.id ||'</td>'||CHR(10)||
'<td>'||v.segment ||'</td>'||CHR(10)||
'<td>'||v.contorlfile||'</td>'||CHR(10)||
'<td>'||v.tablespaces||'</td>'||CHR(10)||
'<td>'||v.datafile ||'</td>'||CHR(10)||
'<td>'||v.tempfile ||'</td>'||CHR(10)||
'<td>'||v.onlinelog ||'</td>'||CHR(10)||
'<td>'||v.standbylog ||'</td>'||CHR(10)||
'</tr>'
FROM (select '1.Object Count' id,
(select count(*) from dba_segments) segment,
(select count(*) from v$controlfile) contorlfile,
(select count(*) from v$tablespace) tablespaces,
(select count(*) from dba_data_files) datafile,
(select count(*) from dba_temp_files) tempfile,
(select count(*) from v$logfile where TYPE='ONLINE') onlinelog,
(select count(*) from v$logfile where TYPE='STANDBY') standbylog
from dual
union all
select '2.Size(GB)',
(select round(sum(bytes)/1024/1024/1024,2) from dba_segments),
(select round(sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024) from v$controlfile),
null,
(select round(sum(bytes)/1024/1024/1024,2) from dba_data_files),
(select round(sum(bytes)/1024/1024/1024,2) from dba_temp_files),
(select round(sum((bytes*cnt)/1024/1024/1024),2) from
(select l.group#,s.bytes,count(l.member) cnt from V$logfile l,v$log s
where l.group#=s.group# and l.TYPE='ONLINE' group by l.group#,s.bytes)),
(select round(sum((bytes*cnt)/1024/1024/1024),2) from
(select l.group#,s.bytes,count(l.member) cnt from V$logfile l,v$standby_log s
where l.group#=s.group# and l.TYPE='STANDBY' group by l.group#,s.bytes))
from dual order by 1) v;
PRO </table>
PRO <a name="1021"></a>
PRO <h2>1.21 Database Size Detail for PDB</h2>
PRO <table border="1px" bordercolor="#000000" cellspacing="0px" style="border-collapse:collapse">
PRO <tr>
PRO <th width="100">ID </th>
PRO <th width="100">Segment </th>
PRO <th width="100">Contorlfile </th>
PRO <th width="100">Tablespace </th>
PRO <th width="100">Datafile </th>
PRO <th width="100">Tempfile </th>
PRO <th width="100">Online Logfile </th>
PRO <th width="100">Standby Logfile</th>
PRO </tr>
SELECT CHR(10)||'<tr>'||CHR(10)||
'<td class=y>'||v.id ||'</td>'||CHR(10)||
'<td>'||v.segment ||'</td>'||CHR(10)||
'<td>'||v.contorlfile||'</td>'||CHR(10)||
'<td>'||v.tablespaces||'</td>'||CHR(10)||
'<td>'||v.datafile ||'</td>'||CHR(10)||
'<td>'||v.tempfile ||'</td>'||CHR(10)||
'<td>'||v.onlinelog ||'</td>'||CHR(10)||
'<td>'||v.standbylog ||'</td>'||CHR(10)||
'</tr>'
FROM (select '1.Object Count' id,
(select count(*) from cdb_segments) segment,
(select count(*) from v$controlfile) contorlfile,
(select count(*) from v$tablespace) tablespaces,
(select count(*) from cdb_data_files) datafile,
(select count(*) from cdb_temp_files) tempfile,
(select count(*) from v$logfile where TYPE='ONLINE') onlinelog,
(select count(*) from v$logfile where TYPE='STANDBY') standbylog
from dual
union all
select '2.Size(GB)',
(select round(sum(bytes)/1024/1024/1024,2) from cdb_segments),
(select round(sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024) from v$controlfile),
null,
(select round(sum(bytes)/1024/1024/1024,2) from cdb_data_files),
(select round(sum(bytes)/1024/1024/1024,2) from cdb_temp_files),
(select round(sum((bytes*cnt)/1024/1024/1024),2) from
(select l.group#,s.bytes,count(l.member) cnt from V$logfile l,v$log s
where l.group#=s.group# and l.TYPE='ONLINE' group by l.group#,s.bytes)),
(select round(sum((bytes*cnt)/1024/1024/1024),2) from
(select l.group#,s.bytes,count(l.member) cnt from V$logfile l,v$standby_log s
where l.group#=s.group# and l.TYPE='STANDBY' group by l.group#,s.bytes))
from dual order by 1) v;
PRO </table>
PRO <a name="103"></a>
PRO <h2>1.3 Database Component and Patch</h2>
PRO <table border="1px" bordercolor="#000000" cellspacing="0px" style="border-collapse:collapse">
PRO <tr>
PRO <th width="20%">Component ID </th>
PRO <th width="40%">Component Name</th>
PRO <th width="10%">Version </th>
PRO <th width="10%">Status </th>
PRO <th width="20%">Modify Time </th>
PRO </tr>
SELECT CHR(10)||'<tr>'||CHR(10)||
'<td class=y>'||v.comp_id ||'</td>'||CHR(10)||
'<td>'||v.comp_name||'</td>'||CHR(10)||
'<td>'||v.version ||'</td>'||CHR(10)||
'<td '||case when v.status != 'VALID' then 'class=x' else null end||'>'||v.status||'</td>'||CHR(10)||
'<td>'||to_char(to_date(v.modified,'DD-Mon-YYYY HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS')||'</td>'||CHR(10)||
'</tr>'
FROM (select comp_id,comp_name,version,status,modified from dba_registry order by 1) v;
PRO <tr>
PRO <th width="20%">Patch ID </th>
PRO <th width="40%">Comments </th>
PRO <th width="10%">Version </th>
PRO <th width="10%">Action </th>
PRO <th width="20%">Action Time</th>
PRO </tr>
SELECT CHR(10)||'<tr>'||CHR(10)||
'<td class=y>'||v.id ||'</td>'||CHR(10)||
'<td>'||v.comments ||'</td>'||CHR(10)||
'<td>'||v.version ||'</td>'||CHR(10)||
'<td>'||v.action ||'</td>'||CHR(10)||
'<td>'||v.action_time||'</td>'||CHR(10)||
'</tr>'
FROM (select id,comments,version,action,action_time from dba_registry_history order by action_time) v;
PRO </table>
PRO <a name="104"></a>
PRO <h2>1.4 Database Parameter</h2>
PRO <table border="1px" bordercolor="#000000" cellspacing="0px" style="border-collapse:collapse">
PRO <tr>
PRO <th width="15%">Name </th>
PRO <th width="10%">Sid </th>
PRO <th width="25%">Spfile Value </th>
PRO <th width="25%">Current Value</th>
PRO <th width="25%">Check Detail </th>
PRO </tr>
SELECT CHR(10)||'<tr>'||CHR(10)||
'<td class=y>'||v.name||'</td>'||CHR(10)||
'<td>'||v.sid ||'</td>'||CHR(10)||
'<td>'||v.spvalue||'</td>'||CHR(10)||
'<td '||case when v.name='processes' and v.value<500 then 'class=x'
when v.name='sessions' and v.value<500 then 'class=x'
when v.name='db_files' and v.value<500 then 'class=x'
when v.name='spfile' and v.value is null then 'class=x'
when v.name='optimizer_adaptive_features' and decode(value,'TRUE','NO','YES')='NO' then 'class=x'
when v.name='optimizer_adaptive_plans' and decode(value,'TRUE','NO','YES')='NO' then 'class=x'
when v.name='optimizer_adaptive_statistics' and decode(value,'TRUE','NO','YES')='NO' then 'class=x'
when v.name='_optimizer_use_feedback' and decode(value,'TRUE','NO','YES')='NO' then 'class=x'
when v.name='max_string_size' and decode(upper(value),'STANDARD','NO','YES')='NO' then 'class=x'
when v.name='statistics_level' and decode(upper(value),'TYPICAL','YES','NO')='NO' then 'class=x'
when v.name='control_file_record_keep_time' and v.value<10 then 'class=x'
when v.name='_use_adaptive_log_file_sync' and decode(value,'FALSE','YES','NO')='NO' then 'class=x'
when v.name='fast_start_parallel_rollback' and decode(value,'HIGH','NO','YES')='NO' then 'class=x'
when v.name='_datafile_write_errors_crash_instance' and decode(value,'FALSE','YES','NO')='NO' then 'class=x'
when v.name='max_dump_file_size' and decode(value,'unlimited','NO','YES')='NO' then 'class=x'
when v.name='parallel_max_servers' and v.value > (select value*4 from v$parameter where name='cpu_count') then 'class=x'
when v.name='deferred_segment_creation' and decode(upper(value),'TRUE','NO','YES')='NO' then 'class=x'
when v.name='open_cursors' and v.value*0.8 < (SELECT MAX(SUM(S.VALUE)) FROM V$STATNAME N, V$SESSTAT S WHERE N.NAME IN ('opened cursors current', 'session cursor cache count') AND S.STATISTIC# = N.STATISTIC# GROUP BY S.SID) then 'class=x'
when v.name='session_cached_cursors' and v.value*0.8 < (SELECT MAX(SUM(S.VALUE)) FROM V$STATNAME N, V$SESSTAT S WHERE N.NAME ='session cursor cache count' AND S.STATISTIC# = N.STATISTIC# GROUP BY S.SID) then 'class=x'
else null end||'>'||v.value||'</td>'||CHR(10)||
'<td>'||case when v.name='processes' and v.value<500 then 'Processes is small than 500.'
when v.name='sessions' and v.value<500 then 'Sessions is small than 500.'
when v.name='db_files' and v.value<500 then 'Db_files is small than 500.'
when v.name='spfile' and v.value is null then 'Instance Not Starup from SPFILE.'
when v.name='optimizer_adaptive_features' and decode(value,'TRUE','NO','YES')='NO' then 'Optimizer_adaptive is Enabled,Plan may be Change.'
when v.name='optimizer_adaptive_plans' and decode(value,'TRUE','NO','YES')='NO' then 'optimizer_adaptive_plans is Enabled,Plan may be Change.'
when v.name='optimizer_adaptive_statistics' and decode(value,'TRUE','NO','YES')='NO' then 'optimizer_adaptive_statistics is Enabled,Plan may be Change.'
when v.name='_optimizer_use_feedback' and decode(value,'TRUE','NO','YES')='NO' then 'Optimizer Feedback is Enabled,Plan may be Change.'
when v.name='max_string_size' and decode(upper(value),'STANDARD','NO','YES')='NO' then 'VARCHAR2 Maximum Not Support 32k.'
when v.name='statistics_level' and decode(upper(value),'TYPICAL','YES','NO')='NO' then 'Statistics Level is not Typical.'
when v.name='control_file_record_keep_time' and v.value<10 then 'Controlfile Record Maybe to Small.'
when v.name='_use_adaptive_log_file_sync' and decode(value,'FALSE','YES','NO')='NO' then 'OLTP Should not used Adaptive Log File Sync.'
when v.name='fast_start_parallel_rollback' and decode(value,'HIGH','NO','YES')='NO' then 'Parallel Rollback is HIGH.'
when v.name='_datafile_write_errors_crash_instance' and decode(value,'FALSE','YES','NO')='NO' then 'Instance May Crash when Datafile Write Failed.'
when v.name='max_dump_file_size' and decode(value,'unlimited','NO','YES')='NO' then 'Max Size of Tracefile is Unlimited.'
when v.name='parallel_max_servers' and v.value > (select value*4 from v$parameter where name='cpu_count') then 'Max Parallel Processed is seting too High.'
when v.name='deferred_segment_creation' and decode(upper(value),'TRUE','NO','YES')='NO' then 'Deferred Segment Creation Not Disable, EXPDP may report Error.'
when v.name='open_cursors' and v.value*0.8 < (SELECT MAX(SUM(S.VALUE)) FROM V$STATNAME N, V$SESSTAT S WHERE N.NAME IN ('opened cursors current', 'session cursor cache count') AND S.STATISTIC# = N.STATISTIC# GROUP BY S.SID) then 'Open Cursor is over 80% Full.'
when v.name='session_cached_cursors' and v.value*0.8 < (SELECT MAX(SUM(S.VALUE)) FROM V$STATNAME N, V$SESSTAT S WHERE N.NAME ='session cursor cache count' AND S.STATISTIC# = N.STATISTIC# GROUP BY S.SID) then 'session_cached_cursors Sholud Be inc.'
else null end||'</td>'||CHR(10)||
'</tr>'
FROM (select distinct s.name,s.sid,s.value spvalue,p.value value from v$spparameter s,gv$parameter p
where s.name=p.name
and (s.value is not null
or (p.name in ('statistics_level','processes','sessions','db_files','spfile','optimizer_adaptive_features','optimizer_adaptive_plans',
'optimizer_adaptive_statistics','max_string_size','control_file_record_keep_time','_use_adaptive_log_file_sync','fast_start_parallel_rollback',
'_datafile_write_errors_crash_instance','max_dump_file_size','parallel_max_servers','deferred_segment_creation','_optimizer_use_feedback',
'open_cursors','session_cached_cursors')))
and p.name not in ('thread','instance_name','instance_number','undo_tablespace','local_listener','remote_listener','lisneter_network','control_files')
order by s.name) v;
PRO </table>
PRO <a name="1041"></a>
PRO <h2>1.41 Database Parameter for PDB</h2>
PRO <table border="1px" bordercolor="#000000" cellspacing="0px" style="border-collapse:collapse">
PRO <tr>
PRO <th width="15%">Con Name </th>
PRO <th width="15%">Name </th>
PRO <th width="10%">Sid </th>
PRO <th width="25%">Spfile Value </th>
PRO <th width="25%">Current Value</th>
PRO <th width="25%">Check Detail </th>
PRO </tr>
SELECT CHR(10)||'<tr>'||CHR(10)||
'<td class=y>'||con_name||'</td>'||CHR(10)||
'<td>'||v.name ||'</td>'||CHR(10)||
'<td>'||v.sid ||'</td>'||CHR(10)||
'<td>'||v.spvalue||'</td>'||CHR(10)||
'<td '||case when v.name='processes' and v.value<500 then 'class=x'
when v.name='sessions' and v.value<500 then 'class=x'
when v.name='db_files' and v.value<500 then 'class=x'
when v.name='spfile' and v.value is null then 'class=x'
when v.name='optimizer_adaptive_features' and decode(value,'TRUE','NO','YES')='NO' then 'class=x'
when v.name='optimizer_adaptive_plans' and decode(value,'TRUE','NO','YES')='NO' then 'class=x'
when v.name='optimizer_adaptive_statistics' and decode(value,'TRUE','NO','YES')='NO' then 'class=x'
when v.name='_optimizer_use_feedback' and decode(value,'TRUE','NO','YES')='NO' then 'class=x'
when v.name='max_string_size' and decode(upper(value),'STANDARD','NO','YES')='NO' then 'class=x'
when v.name='statistics_level' and decode(upper(value),'TYPICAL','YES','NO')='NO' then 'class=x'
when v.name='control_file_record_keep_time' and v.value<10 then 'class=x'
when v.name='_use_adaptive_log_file_sync' and decode(value,'FALSE','YES','NO')='NO' then 'class=x'
when v.name='fast_start_parallel_rollback' and decode(value,'HIGH','NO','YES')='NO' then 'class=x'
when v.name='_datafile_write_errors_crash_instance' and decode(value,'FALSE','YES','NO')='NO' then 'class=x'
when v.name='max_dump_file_size' and decode(value,'unlimited','NO','YES')='NO' then 'class=x'
when v.name='parallel_max_servers' and v.value > (select value*4 from v$parameter where name='cpu_count') then 'class=x'
when v.name='deferred_segment_creation' and decode(upper(value),'TRUE','NO','YES')='NO' then 'class=x'
when v.name='open_cursors' and v.value*0.8 < (SELECT MAX(SUM(S.VALUE)) FROM V$STATNAME N, V$SESSTAT S WHERE N.NAME IN ('opened cursors current', 'session cursor cache count') AND S.STATISTIC# = N.STATISTIC# GROUP BY S.SID) then 'class=x'
when v.name='session_cached_cursors' and v.value*0.8 < (SELECT MAX(SUM(S.VALUE)) FROM V$STATNAME N, V$SESSTAT S WHERE N.NAME ='session cursor cache count' AND S.STATISTIC# = N.STATISTIC# GROUP BY S.SID) then 'class=x'
else null end||'>'||v.value||'</td>'||CHR(10)||
'<td>'||case when v.name='processes' and v.value<500 then 'Processes is small than 500.'
when v.name='sessions' and v.value<500 then 'Sessions is small than 500.'
when v.name='db_files' and v.value<500 then 'Db_files is small than 500.'
when v.name='spfile' and v.value is null then 'Instance Not Starup from SPFILE.'
when v.name='optimizer_adaptive_features' and decode(value,'TRUE','NO','YES')='NO' then 'Optimizer_adaptive is Enabled,Plan may be Change.'
when v.name='optimizer_adaptive_plans' and decode(value,'TRUE','NO','YES')='NO' then 'optimizer_adaptive_plans is Enabled,Plan may be Change.'
when v.name='optimizer_adaptive_statistics' and decode(value,'TRUE','NO','YES')='NO' then 'optimizer_adaptive_statistics is Enabled,Plan may be Change.'
when v.name='_optimizer_use_feedback' and decode(value,'TRUE','NO','YES')='NO' then 'Optimizer Feedback is Enabled,Plan may be Change.'
when v.name='max_string_size' and decode(upper(value),'STANDARD','NO','YES')='NO' then 'VARCHAR2 Maximum Not Support 32k.'
when v.name='statistics_level' and decode(upper(value),'TYPICAL','YES','NO')='NO' then 'Statistics Level is not Typical.'
when v.name='control_file_record_keep_time' and v.value<10 then 'Controlfile Record Maybe to Small.'
when v.name='_use_adaptive_log_file_sync' and decode(value,'FALSE','YES','NO')='NO' then 'OLTP Should not used