Wednesday, 24 May 2017

Failed login attempts



Number of failed login attempts exceeds threshold value message comes from OEM.
How to check what user is causing this? DBA_AUDIT_SESSION can help to identifiy the username,userhost, etc:
SQL> desc DBA_AUDIT_SESSION
 Name              
 -------------------
 OS_USERNAME       
 USERNAME          
 USERHOST          
 TERMINAL          
 TIMESTAMP         
 ACTION_NAME       
 LOGOFF_TIME       
 LOGOFF_LREAD      
 LOGOFF_PREAD      
 LOGOFF_LWRITE     
 LOGOFF_DLOCK      
 SESSIONID         
 RETURNCODE        
 CLIENT_ID         
 SESSION_CPU       
 EXTENDED_TIMESTAMP
 PROXY_SESSIONID   
 GLOBAL_UID        
 INSTANCE_NUMBER   
 OS_PROCESS 


Lets check the errors within last day:


SELECT
  TO_CHAR(TIMESTAMP,'YYYY/MM/DD HH24:MI') TIMESTAMP,
  SUBSTR(OS_USERNAME,1,20) OS_USERNAME,
  SUBSTR(USERNAME,1,20) USERNAME,
  SUBSTR(TERMINAL,1,20) TERMINAL,
  ACTION_NAME,
  RETURNCODE
FROM
SYS.DBA_AUDIT_SESSION
WHERE TIMESTAMP BETWEEN SYSDATE-1 AND SYSDATE
and RETURNCODE>0
ORDER BY TIMESTAMP;
TIMESTAMP        OS_USERNAM USERNAME              TERMINAL          ACTION_NAME   RETURNCODE
---------------- ---------- --------------------- ----------------- ------------- ----------
2017/05/24 12:56 SYSTEM     DBP_007_GENEOS_USER   LONGCASU0014      LOGON               1017
2017/05/24 12:56 SYSTEM     DBP_007_GENEOS_USER   LONGCASU0014      LOGON               1017
2017/05/24 12:56 SYSTEM     DBP_007_GENEOS_USER   LONGCASU0014      LOGON               1017
2017/05/24 12:56 SYSTEM     DBP_007_GENEOS_USER   LONGCASU0014      LOGON               1017
2017/05/24 12:56 SYSTEM     DBP_007_GENEOS_USER   LONGCASU0014      LOGON               1017
2017/05/24 12:56 SYSTEM     DBP_007_GENEOS_USER   LONGCASU0014      LOGON               1017
2017/05/24 12:57 SYSTEM     DBP_007_GENEOS_USER   LONGCASU0014      LOGON               1017
2017/05/24 12:57 SYSTEM     DBP_007_GENEOS_USER   LONGCASU0014      LOGON               1017

SELECT  SUBSTR(OS_USERNAME,1,20) OS_USERNAME,
SUBSTR(USERNAME,1,20) USERNAME,
SUBSTR(TERMINAL,1,20) TERMINAL,
ACTION_NAME,
RETURNCODE,count(*)
FROM
SYS.DBA_AUDIT_SESSION
WHERE TIMESTAMP BETWEEN SYSDATE-1 AND SYSDATE
and RETURNCODE>0
group by SUBSTR(OS_USERNAME,1,20),
SUBSTR(USERNAME,1,20),
SUBSTR(TERMINAL,1,20),
ACTION_NAME,RETURNCODE
order by count(*);
OS_USERNAM USERNAME                 TERMINAL         ACTION_NAME  RETURNCODE   COUNT(*)
---------- ------------------------ ---------------- ------------ ---------- ----------
umesnag    DBP_107_ETL_USER         unknown          LOGON              1017          1
singtaja   DBP_TOOLS_USER           unknown          LOGON              1017          1
singtaja   DBP_007_ACCESS_OWNER     unknown          LOGON             28000          1
rohivai    DBP_TOOLS_USER           unknown          LOGON              1017          1
dubeamib   DUBEAMIB                 unknown          LOGON             28001          1
kumshar    PRASAVV                  unknown          LOGON             28001          1
nunepab    DBP_011_STAGING_USER     unknown          LOGON              1017          1
singhash   DBP_TOOLS_USER           unknown          LOGON              1017          1
umalaks    URJA_DBP_107_RAP_OWN     unknown          LOGON              1017          1
umalaks    DBP_107_RAP_OWNER        unknown          LOGON              1017          1
kumakun    DBP_TOOLS_USER           unknown          LOGON              1017          5
panitan    DBP_014_APP_USER         unknown          LOGON              1017         10
dubeamib   DBP_107_ETL_USER         unknown          LOGON              1017         12
infarun    DBP_007_ETL_USER                          LOGON             28000         36
SYSTEM     DBP_007_GENEOS_USER      LONGCASU0014     LOGON              1017        121
SYSTEM     DBP_007_GENEOS_USER      LONGCASU0014     LOGON             28000       6920


RETURNCODE column is simply Oracle Error Code


SQL> !oerr ora 28000
28000, 00000, "the account is locked"
// *Cause:   The user has entered wrong password consequently for maximum
//           number of times specified by the user's profile parameter
//           FAILED_LOGIN_ATTEMPTS, or the DBA has locked the account
// *Action:  Wait for PASSWORD_LOCK_TIME or contact DBA
SQL> !oerr ora 1017
01017, 00000, "invalid username/password; logon denied"
// *Cause:
// *Action:
Posted by at No comments:
Labels: , ,

Monday, 28 September 2015

How to list all databases with old COMPATIBLE or OPTIMIZER_FEATURES_ENABLE according to OH software release version

With end of 2015 will Oracle Database 11g out of normal support so lot of customers make an upgrade campaign in data centers. Every DBA go(should go at least) through a list of manual step to upgrade database (Complete Checklist for Manual Upgrades to Oracle Database 12c Release 1 (12.1) (Doc ID 1503653.1)).

This note state that after upgrade we have to set new value for COMPATIBLE parameter. I will recommend as well to look on another instance parameter related with database release software version - OPTIMIZER_FEATURES_ENABLE. This parameter is responsible for enabling a series of optimizer features based on an Oracle release number.

Sometimes after few months or years:) after upgrade you realize that some of your 1000 databases have wrong parameters which you had simply forgot to change...

Bearing that in my mind I use Configuration Search feature of Enterprise Manager to report all database configuration issue due differences between oracle home release version and instance compatible and optimizer_features_enable parameters. (More about Configuration Search you can find in nice post from Dave Wolf https://blogs.oracle.com/oem/entry/harness_the_power_of_configuration).

So here is the script:

set lines 300
set pages 100
col hostname format a30
col instance format a20
col parameter format a20
col value format a20
col dbversion format a20
  SELECT s5.target_name hostname,
         s2.target_name instance,
         s4.NAME parameter,
         s4.VALUE,
         s3.DBVERSION
    FROM CM$MGMT_DB_INIT_PARAMS_ECM s4,
         CM$MGMT_DB_DBNINSTANCEINFO_ECM s3,
         MGMT$TARGET s5,
         GC$ASSOC_INST_WITH_INVERSE s5a1,
         MGMT$ECM_CURRENT_SNAPSHOTS s2gen1,
         MGMT$TARGET s2
   WHERE     s2gen1.TARGET_GUID = s2.TARGET_GUID
         AND s2gen1.ECM_SNAPSHOT_ID = s3.ECM_SNAPSHOT_ID(+)
         AND s3.ECM_SNAPSHOT_ID = s4.ECM_SNAPSHOT_ID
         AND (NLS_UPPER (s4.NAME) LIKE NLS_UPPER ('compatible'))
         AND s5a1.SOURCE_ME_GUID = s2.TARGET_GUID
         AND s5a1.DEST_ME_GUID = s5.TARGET_GUID
         AND s5.TARGET_TYPE = 'host'
         AND s5a1.ASSOC_TYPE = 'hosted_by'
         AND s2.TARGET_TYPE = 'oracle_database'
         AND s2gen1.SNAPSHOT_TYPE = 'oracle_dbconfig'
         AND SUBSTR (s3.DBVERSION, 1, 4) != SUBSTR (s4.VALUE, 1, 4)
ORDER BY 2, 3

and output:

HOSTNAME                            INSTANCE             PARAMETER            VALUE                DBVERSION          
----------------------------------  -------------------- -------------------- -------------------- --------------------
adux0444.us01.ny02.example.com      ADB03XXI             compatible           11.1.0.7.0           11.2.0.4.0         
adux0165.us01.ny02.example.com      ADUXAB1E             compatible           10.2.0.4             11.2.0.4.0         
adux0165.us01.ny02.example.com      ADUXAB1K             compatible           10.2.0.4             11.2.0.4.0         
adux0302.us01.ny02.example.com      ADUXATDE             compatible           11.1.0.7             11.2.0.4.0         
adux0350.us01.ny02.example.com      ADUXAX2K             compatible           11.1.0.0.0           11.2.0.4.0         
adux0318.us01.ny02.example.com      ADUXAX2P             compatible           11.1.0.0.0           11.2.0.3.0         
adux0037.us01.ny02.example.com      ADUXBO2P             compatible           11.1.0.7             11.2.0.4.0         
adux0096.us01.ny02.example.com      ADUXCL1E             compatible           10.2.0.3             11.2.0.4.0         
adux0444.us01.ny02.example.com      ADUXCL1I             compatible           10.2.0.4             11.2.0.4.0         
adux0096.us01.ny02.example.com      ADUXCL1K             compatible           10.2.0.4             11.2.0.4.0         
adux0065.us01.ny02.example.com      ADUXCL1P             compatible           10.2.0.4.0           11.2.0.4.0         
adux0066.us01.ny02.example.com      ADUXCL1Q             compatible           10.2.0.4.0           11.2.0.3.0         
adux0179.us01.ny02.example.com      ADUXCL2Q             compatible           10.2.0.4             11.2.0.3.0         
aduc0219.us01.ny02.example.com      ADUXCR6K_CR6K1       compatible           10.2.0.4             11.2.0.3.0         
aduc0220.us01.ny02.example.com      ADUXCR6K_CR6K2       compatible           10.2.0.4             11.2.0.3.0         
aduc0546.us01.ny02.example.com      ADUXCR6K_CR6K3       compatible           10.2.0.4             11.2.0.3.0         
adux0249.us01.ny02.example.com      ADUXCT1E             compatible           10.2.0.4.0           11.2.0.4.0         
adux0249.us01.ny02.example.com      ADUXCT1K             compatible           10.2.0.2.0           11.2.0.4.0         
adux0044.us01.ny02.example.com      ADUXCT1P             compatible           10.2.0.2.0           11.2.0.4.0         
adux0249.us01.ny02.example.com      ADUXCT1T             compatible           10.2.0.2.0           11.2.0.4.0         
adux0248.us01.ny02.example.com      ADUXCZ2E             compatible           10.2.0.3             11.2.0.4.0         
adux0248.us01.ny02.example.com      ADUXCZ2K             compatible           10.2.0.4             11.2.0.4.0         
adux0065.us01.ny02.example.com      ADUXCZ2P             compatible           10.2.0.4             11.2.0.4.0         
adux0066.us01.ny02.example.com      ADUXCZ2Q             compatible           10.2.0.4             11.2.0.4.0         
aduc0233.us01.ny02.example.com      ADUXEH3E             compatible           10.2.0.4.0           11.2.0.2.0         
adux0044.us01.ny02.example.com      ADUXEH3P             compatible           11.1.0.7.0           11.2.0.3.0         
adux0249.us01.ny02.example.com      ADUXEV3K             compatible           11.1.0.7.0           11.2.0.4.0         
adux0037.us01.ny02.example.com      ADUXEV3P             compatible           10.2.0.4.0           11.2.0.4.0         
adux0157.us01.ny02.example.com      ADUXEV4P             compatible           10.2.0.4.0           11.2.0.4.0         
adux0445.us01.ny02.example.com      ADUXFI1I             compatible           10.2.0.3             11.2.0.4.0         
adux0094.us01.ny02.example.com      ADUXFI1K             compatible           10.2.0.3             11.2.0.4.0         
adux0037.us01.ny02.example.com      ADUXFI1P             compatible           10.2.0.3             11.2.0.4.0         
adux0094.us01.ny02.example.com      ADUXFI2E             compatible           10.2.0.3             11.2.0.4.0         
adux0248.us01.ny02.example.com      ADUXFK3E             compatible           11.1.0.0             11.2.0.4.0         
adux0248.us01.ny02.example.com      ADUXFK3K             compatible           11.1.0.0             11.2.0.4.0         
adux0065.us01.ny02.example.com      ADUXFK3P             compatible           10.2.0.4             11.2.0.4.0         
adux0096.us01.ny02.example.com      ADUXFR4E             compatible           10.2.0.3             11.2.0.1.0         
adux0280.us01.ny02.example.com      ADUXGV8K             compatible           10.2.0.4             11.2.0.2.0         
adux0350.us01.ny02.example.com      ADUXIB3I             compatible           11.1.0.7.0           11.2.0.4.0         
adux0350.us01.ny02.example.com      ADUXIB3K             compatible           11.1.0.7.0           11.2.0.4.0         
adux0065.us01.ny02.example.com      ADUXIB3P             compatible           11.1.0.7.0           11.2.0.4.0         
adux0350.us01.ny02.example.com      ADUXIB3S             compatible           11.1.0.7.0           11.2.0.4.0         
adux0350.us01.ny02.example.com      ADUXIB4I             compatible           11.1.0.7.0           11.2.0.4.0         
adux0350.us01.ny02.example.com      ADUXIB7K             compatible           11.1.0.7.0           11.2.0.4.0         
adux0248.us01.ny02.example.com      ADUXIC1E             compatible           10.2.0.4             11.2.0.4.0         
adux0248.us01.ny02.example.com      ADUXIC2E             compatible           10.2.0.4             11.2.0.4.0         
adux0248.us01.ny02.example.com      ADUXIC2I             compatible           10.2.0.4             11.2.0.4.0         
adux0248.us01.ny02.example.com      ADUXIC2K             compatible           10.2.0.4             11.2.0.4.0         
adux0065.us01.ny02.example.com      ADUXIC2P             compatible           10.2.0.4             11.2.0.4.0         
adux0094.us01.ny02.example.com      ADUXKM1K             compatible           10.2.0.2.0           11.2.0.2.0         
adux0037.us01.ny02.example.com      ADUXKM1P             compatible           10.2.0.2.0           11.2.0.2.0         
adux0249.us01.ny02.example.com      ADUXMM1K             compatible           10.2.0.4             11.2.0.4.0         
adux0037.us01.ny02.example.com      ADUXMM1P             compatible           10.2.0.4             11.2.0.4.0         
adux0319.us01.ny02.example.com      ADUXOA1K             compatible           11.1.0.7.0           11.2.0.4.0         
adux0318.us01.ny02.example.com      ADUXOA1P             compatible           11.1.0.7.0           11.2.0.4.0         
adux0096.us01.ny02.example.com      ADUXOC1E             compatible           10.2.0.4             11.2.0.4.0         
adux0096.us01.ny02.example.com      ADUXOC1I             compatible           10.2.0.4             11.2.0.4.0         
adux0096.us01.ny02.example.com      ADUXOC1K             compatible           10.2.0.4             11.2.0.4.0         
adux0065.us01.ny02.example.com      ADUXOC1P             compatible           10.2.0.4             11.2.0.4.0         
adux0094.us01.ny02.example.com      ADUXPC1K             compatible           10.2.0.4             11.2.0.4.0         
adux0037.us01.ny02.example.com      ADUXPC1P             compatible           10.2.0.4             11.2.0.4.0         
adux0249.us01.ny02.example.com      ADUXSB5K             compatible           11.1.0.7.0           11.2.0.4.0         
adux0044.us01.ny02.example.com      ADUXSB5P             compatible           11.1.0.7.0           11.2.0.4.0         
adux0043.us01.ny02.example.com      ADUXSB5Q             compatible           11.1.0.7.0           11.2.0.4.0         
adux0065.us01.ny02.example.com      ADUXSM5P             compatible           11.1.0.7.0           11.2.0.4.0         
adux0066.us01.ny02.example.com      ADUXSM5Q             compatible           11.1.0.7.0           11.2.0.2.0         
adux0065.us01.ny02.example.com      ADUXSM6P             compatible           11.1.0.7.0           11.2.0.3.0         
adux0445.us01.ny02.example.com      ADUXSO1I             compatible           10.2.0.4.0           11.2.0.4.0         
adux0249.us01.ny02.example.com      ADUXSO1K             compatible           10.2.0.4.0           11.2.0.4.0         
adux0094.us01.ny02.example.com      ADUXSP1K             compatible           10.2.0.2.0           11.2.0.4.0         
adux0445.us01.ny02.example.com      ADUXTC1I             compatible           10.2.0.2             11.2.0.4.0         
adux0094.us01.ny02.example.com      ADUXTM1K             compatible           11.1.0.7.0           11.2.0.4.0         
adux0094.us01.ny02.example.com      ADUXTM2K             compatible           11.1.0.7.0           11.2.0.4.0         
adux0094.us01.ny02.example.com      ADUXUA6E             compatible           10.1.0.0.0           10.2.0.5.0         
adux0165.us01.ny02.example.com      ADUXUK8E             compatible           10.2.0.3.0           11.2.0.4.0         
adux0165.us01.ny02.example.com      ADUXUK8K             compatible           10.2.0.3.0           11.2.0.4.0         
adux0112.us01.ny02.example.com      ADUXUK8P             compatible           11.1.0.7.0           11.2.0.4.0         
adux0249.us01.ny02.example.com      ADUXVA1K             compatible           10.2.0.3.0           11.2.0.2.0         
adux0094.us01.ny02.example.com      ADUXVT2K             compatible           11.1.0.7             11.2.0.4.0         
adux0037.us01.ny02.example.com      ADUXVT2P             compatible           11.1.0.7.2           11.2.0.3.0         
adux0096.us01.ny02.example.com      ADUXWQ1E             compatible           11.1.0.0             11.2.0.4.0         
adux0445.us01.ny02.example.com      ADUXWQ1I             compatible           10.2.0.4             11.2.0.4.0         
adux0096.us01.ny02.example.com      ADUXWQ1K             compatible           10.2.0.4             11.2.0.4.0         
adux0065.us01.ny02.example.com      ADUXWQ1P             compatible           10.2.0.4             11.2.0.4.0         

84 rows selected.

Change this line to get list of optimizer parameters across your enviroment 


from
AND (NLS_UPPER (s4.NAME) LIKE NLS_UPPER ('compatible'))
to 
AND (NLS_UPPER (s4.NAME) LIKE NLS_UPPER ('optimizer_features_enable'))
Output:

HOSTNAME                       INSTANCE             PARAMETER                    VALUE                DBVERSION          
------------------------------ -------------------- ---------------------------- -------------------- --------------------
adux0065.us01.ny02.example.com      ADUXBB1P             optimizer_features_enable    10.2.0.4             11.2.0.4.0         
adux0094.us01.ny02.example.com      ADUXBO2E             optimizer_features_enable    11.1.0.7             11.2.0.4.0         
adux0094.us01.ny02.example.com      ADUXBO2K             optimizer_features_enable    11.1.0.7             11.2.0.4.0         
adux0037.us01.ny02.example.com      ADUXBO2P             optimizer_features_enable    11.1.0.7             11.2.0.4.0         
adux0094.us01.ny02.example.com      ADUXBO2T             optimizer_features_enable    11.1.0.7             11.2.0.4.0         
aduc0219.us01.ny02.example.com      ADUXCR6K_CR6K1       optimizer_features_enable    10.2.0.4             11.2.0.3.0         
aduc0220.us01.ny02.example.com      ADUXCR6K_CR6K2       optimizer_features_enable    10.2.0.4             11.2.0.3.0         
aduc0546.us01.ny02.example.com      ADUXCR6K_CR6K3       optimizer_features_enable    10.2.0.4             11.2.0.3.0         
adux0044.us01.ny02.example.com      ADUXEH3P             optimizer_features_enable    10.2.0.4             11.2.0.3.0         
adux0249.us01.ny02.example.com      ADUXIR1K             optimizer_features_enable    11.1.0.7             11.2.0.4.0         
adux0157.us01.ny02.example.com      ADUXIR1P             optimizer_features_enable    11.1.0.7             11.2.0.4.0         
adux0037.us01.ny02.example.com      ADUXKM1P             optimizer_features_enable    10.2.0.5             11.2.0.2.0         
adux0113.us01.ny02.example.com      ADUXUK3P             optimizer_features_enable    10.2.0.4             11.2.0.4.0         
adux0113.us01.ny02.example.com      ADUXUK4P             optimizer_features_enable    10.2.0.4             11.2.0.4.0         
adux0165.us01.ny02.example.com      ADUXUK5K             optimizer_features_enable    10.2.0.4             11.2.0.4.0         
adux0112.us01.ny02.example.com      ADUXUK5P             optimizer_features_enable    10.2.0.4             11.2.0.4.0         
adux0112.us01.ny02.example.com      ADUXUK6P             optimizer_features_enable    10.2.0.4             11.2.0.4.0         
adux0165.us01.ny02.example.com      ADUXUK7K             optimizer_features_enable    10.2.0.4             11.2.0.4.0         
adux0165.us01.ny02.example.com      ADUXUK9K             optimizer_features_enable    10.2.0.4             11.2.0.4.0         

19 rows selected.



PS. Using emcli you can easily export your reports from dev environment to production

 emcli export_report -title="Report_Diff_Ora" -owner="SYSMAN" -output_file=/l/ora/reports/Report_Diff_Ora.xml
Posted by at 3 comments:
Labels: , , , ,

Thursday, 4 June 2015

CloudControl: Report for sizing memory/sga across enterprise

In my previous post I showed how to create tnsnames file from grid/cloud repository. I think OMS repository is very useful tool but at the same time too oft underestimated.
Here is my story: My client is running numbers of databases on AIX. I was asked to show distribution of memory across LPARs and Oracle Instances. Yes, sure, you can take calculator and go through your environment. But I am too lazy... Motivation for this task came after watching a seminar with Christo Kutrovsky from Pythian(https://www.youtube.com/watch?v=5HyjTxD4soA) about benefits of RAM memory.
Based on SYSMAN.MGMT_ECM_HW you can build simple query to get hardware configuration:

COL HOSTNAME FORMAT A20
select HOSTNAME,CPU_COUNT,MEMORY_SIZE_IN_MB/1024 MEM_GB from SYSMAN.MGMT_ECM_HW order by hostname;
HOSTNAME              CPU_COUNT     MEM_GB
-------------------- ---------- ----------
utest32                       3         64
integ22                       2         16
integ32                       3         16
unprod32                      3         48
...
unprod62                      8         64
unprod72                      2         24
ucont32                       3         32
ucont32                       3         24
ucont42                       2         16
ucont52                       2         16

But challenge is to get for example SGA size for each instance on every host. You can choose between SYSMAN.MGMT_DB_INIT_PARAMS_ECM and SYSMAN.MGMT_DB_SGA_ECM. Here is my script:
SET pages 100
SET lines 300
col hostname format a20
col db_name format a10
col "Total SGA" format a20
break ON hostname SKIP page ON hostname
COMPUTE sum OF "MB" ON hostname
 SELECT
    c.host_name hostname,
    upper(c.entity_name) db_name,
 sga.sganame AS "Total SGA",
 sga.sgasize AS "MB"
 FROM
    sysman.em_manageable_entities c,
    sysman.mgmt_db_sga_ecm sga,
    sysman.mgmt_ecm_gen_snapshot s
  WHERE
  s.snapshot_guid = sga.ecm_snapshot_id
  AND s.target_guid = c.entity_guid
  AND s.is_current  ='Y'
  AND c.entity_type ='oracle_database'
  AND sga.sganame='Total SGA (MB)'
  ORDER BY
    c.host_name,c.entity_name;
Output:
HOSTNAME                       DB_NAME    Total SGA                    MB
------------------------------ ---------- -------------------- ----------
utest32                        BBAIS      Total SGA (MB)              864
                               BBAIS12    Total SGA (MB)              864
                               BUSYI      Total SGA (MB)             3472
                               BUSYT      Total SGA (MB)             3136
                               DWRT       Total SGA (MB)             3610
                               DWRT12     Total SGA (MB)             3504
                               FEINP8I    Total SGA (MB)              696
                               FEINP8T    Total SGA (MB)              955
                               HRDWRT     Total SGA (MB)             2819
                               HRDWRT12   Total SGA (MB)             2880
                               OPSWT      Total SGA (MB)              696
                               PAIST      Total SGA (MB)             3098
                               OPTRALT    Total SGA (MB)              788
                               PRFT6T     Total SGA (MB)              496
                               RMAN1T     Total SGA (MB)             1559
                               RMANBK22   Total SGA (MB)             1376
******************************                                 ----------
sum                                                                 30813
HOSTNAME                       DB_NAME    Total SGA                    MB
------------------------------ ---------- -------------------- ----------
utest35                        FEINT      Total SGA (MB)              562
******************************                                 ----------
sum                                                                   562
...
HOSTNAME                       DB_NAME    Total SGA                    MB
------------------------------ ---------- -------------------- ----------
unprod62                       DWRP       Total SGA (MB)            33137
                               RMAN2P     Total SGA (MB)             1543
******************************                                 ----------
sum                                                                 34680
HOSTNAME                       DB_NAME    Total SGA                    MB
------------------------------ ---------- -------------------- ----------
unprod72                       HRDWRP     Total SGA (MB)             4970
                               RMAN3P     Total SGA (MB)             1911
******************************                                 ----------
sum                                                                  6881
I made as well a percentage distribution within SGA:
col hostname format a20
col db_name format a10
SET pages 100
SET lines 300
break ON hostname SKIP page ON hostname
COMPUTE sum OF "Total SGA(MB)" ON hostname
SELECT
    c.host_name hostname,
    upper(c.entity_name) db_name,
    round(sga.sgasize) AS "Total SGA(MB)",
    round(sp.sgasize/sga.sgasize*100) AS "SharedPool(%)",
    round(bc.sgasize/sga.sgasize*100) AS "BufferedCache(%)",
    round(rb.sgasize/sga.sgasize*100/1024) AS "RedoBuffers(%)",
    round(lp.sgasize/sga.sgasize*100/1024) AS "LargePool(%)",
    round(jp.sgasize/sga.sgasize*100) AS "JavaPool(%)"
 FROM
    sysman.em_manageable_entities c,
    sysman.mgmt_db_sga_ecm sga,
    sysman.mgmt_db_sga_ecm sp,
    sysman.mgmt_db_sga_ecm bc,
    sysman.mgmt_db_sga_ecm rb,
    sysman.mgmt_db_sga_ecm lp,
    sysman.mgmt_db_sga_ecm jp,
    sysman.mgmt_ecm_gen_snapshot s
  WHERE
  s.snapshot_guid = sga.ecm_snapshot_id
  AND s.snapshot_guid = sp.ecm_snapshot_id
  AND s.snapshot_guid = bc.ecm_snapshot_id
  AND s.snapshot_guid = rb.ecm_snapshot_id
  AND s.snapshot_guid = lp.ecm_snapshot_id
  AND s.snapshot_guid = jp.ecm_snapshot_id
  AND s.target_guid = c.entity_guid
  AND s.is_current  ='Y'
  AND c.entity_type ='oracle_database'
  AND sga.sganame='Total SGA (MB)'
  AND sp.sganame='Shared Pool (MB)'
  AND bc.sganame='Buffered Cache (MB)'
  AND rb.sganame='Redo Buffers (KB)'
  AND lp.sganame='Large Pool (KB)'
  AND jp.sganame='Java Pool (MB)'
  ORDER BY
    c.host_name,c.entity_name;
Output:
HOSTNAME             DB_NAME    Total SGA(MB) SharedPool(%) BufferedCache(%) RedoBuffers(%) LargePool(%) JavaPool(%)
-------------------- ---------- ------------- ------------- ---------------- -------------- ------------ -----------
utest32              BBAIS                864            69                8              1            2          13
                     BBAIS12              864            69               19              1            2           1
                     BUSYI               3472            60               30              6            2           0
                     BUSYT               3136            62               28              7            2           1
                     DWRT                3610            24               43             29            2           0
                     DWRT12              3504            44               47              0            1           1
                     FEINP8I              696            35               30             30            3           1
                     FEINP8T              955            42               27             23            5           2
                     HRDWRT              2819            39               19             37            2           1
                     HRDWRT12            2880            18               74              0            1           1
                     OPSWT                696            35               32             30            2           1
                     PAIST               3098            60               36              0            1           1
                     OPTRALT              788            58               27              3            2           1
                     PRFT6T               496            79               11              0            2           2
                     RMAN1T              1559            45               36             14            3           1
                     RMANBK22            1376            33               55              1            6           1
********************            -------------
sum                                     30813
HOSTNAME             DB_NAME    Total SGA(MB) SharedPool(%) BufferedCache(%) RedoBuffers(%) LargePool(%) JavaPool(%)
-------------------- ---------- ------------- ------------- ---------------- -------------- ------------ -----------
utest35              FEINT                562            89                9              1            0           1
********************            -------------
sum                                       562
...
HOSTNAME             DB_NAME    Total SGA(MB) SharedPool(%) BufferedCache(%) RedoBuffers(%) LargePool(%) JavaPool(%)
-------------------- ---------- ------------- ------------- ---------------- -------------- ------------ -----------
unprod82n0           OPTRALP             1449            49               30             15            0           1
                     PRFT6P              1509            47               34              2            0           1
                     RMAN9P              1575            58               40              0            1           1
********************            -------------
sum                                      4533
HOSTNAME             DB_NAME    Total SGA(MB) SharedPool(%) BufferedCache(%) RedoBuffers(%) LargePool(%) JavaPool(%)
-------------------- ---------- ------------- ------------- ---------------- -------------- ------------ -----------
ucont25              FEINQ                562            89                9              1            0           1
********************            -------------



Posted by at No comments:
Labels: , , ,
Subscribe to: Comments (Atom)