Tales From A Lazy Fat DBA

Its all about Databases & their performance, troubleshooting & much more …. ¯\_(ツ)_/¯

An Oracle Deadlock scenario and the importance of Event 10027 trace …

Posted by FatDBA on February 16, 2023

Hi Guys,

Recently someone asked me about Oracle debug 10027 trace event which we use in case of a deadlock scenario i.e. ORA-0060. Though the deadlock itself creates a trace file in DIAG directory, but 10027 trace event gives you a better control over the amount and type of DIAG information generated in response to the deadlock case. I mean the default trace file for deadlock (ora-60) contains cached cursors, a deadlock graph, process state info, current SQL Statements of the session involved and session wait history.
Event 10027 may be used to augment the trace information with a system state dump or a call stack in an attempt to find the root cause of the deadlocks. The minimum amount of trace information is written at level 1, at this level it will hardly contain deadlock graph and current AQL statements of the sessions involved.

In todays post I will try to simulate a deadlock scenario in one of my test box and will generate the 10027 trace with level 2 to get more information. Level 2 will give you cached cursors, process state info, session wait history for all sessions and the system state which is not possible in case of level 1. I am going to try with the Level 4 here as I want to get system state dump and don’t want to complicate this scenario.

I am going to create two tables – TableB is child table of TableA and some supporting objects to simulate the deadlock case.
Will try to simulate a deadlock scenario when insert/update/delete happens on TableB we need to sum the amount(amt) and then update it in TableA.total_amt column.

[oracle@oracleontario ~]$ sqlplus dixdroid/oracle90

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Feb 16 03:58:12 2023
Version 19.15.0.0.0

Copyright (c) 1982, 2022, Oracle.  All rights reserved.

Last Successful login time: Thu Feb 16 2023 03:04:03 -05:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.15.0.0.0

SQL> create table tableA (pk_id number primary key, total_amt number);

Table created.

SQL> create table tableB (pk_id number primary key, fk_id number references tableA(pk_id) not null, amt number);

Table created.

SQL>
SQL> CREATE OR REPLACE PACKAGE global_pkg
IS
fk_id tableA.pk_id%TYPE;
END global_pkg;  
/

Package created.

SQL>
SQL> CREATE OR REPLACE TRIGGER tableB_ROW_TRG
BEFORE INSERT OR UPDATE OR DELETE
ON tableB
FOR EACH ROW
BEGIN
IF INSERTING OR UPDATING
THEN
global_pkg.fk_id := :new.fk_id;
ELSE
global_pkg.fk_id := :old.fk_id;
END IF;
END tableB_ROW_TRG;
/

Trigger created.

SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGER tableB_ST_trg
AFTER INSERT OR UPDATE OR DELETE
ON tableB
BEGIN
IF UPDATING OR INSERTING
THEN
UPDATE tableA
SET total_amt =
(SELECT SUM (amt)
FROM tableB
WHERE fk_id = global_pkg.fk_id)
WHERE pk_id = global_pkg.fk_id;
ELSE
UPDATE tableA
SET total_amt =
(SELECT SUM (amt)
FROM tableB
WHERE fk_id = global_pkg.fk_id)
WHERE pk_id = global_pkg.fk_id;
END IF;
END tableB_ST_trg;

Trigger created.

SQL>
SQL>
SQL> insert into tableA values (1, 0);

1 row created.

SQL> insert into tableA values (2, 0);

1 row created.

SQL> insert into tableB values (123, 1, 100);

1 row created.

SQL> insert into tableB values (456,1, 200);

1 row created.

SQL> insert into tableB values (789, 1, 100);

1 row created.

SQL> insert into tableB values (1011, 2, 50);

1 row created.

SQL> insert into tableB values (1213,2, 150);

1 row created.

SQL> insert into tableB values (1415, 2, 50);

1 row created.

SQL> commit;

Commit complete.

SQL>

Lets query the table and see the record count and next will delete an entry from tableB and won’t commit. At the same time will check the locks — TM lock on tableA.

SQL>
SQL> select * from tableA;

     PK_ID  TOTAL_AMT
---------- ----------
         1        400
         2        250

SQL> delete tableB where pk_id = 1415;

1 row deleted.

SQL> SELECT sid,
(SELECT username
FROM v$session s
WHERE s.sid = v$lock.sid)
uname,
TYPE,
id1,
id2,
(SELECT object_name
FROM user_objects
WHERE object_id = v$lock.id1)
nm
FROM v$lock
WHERE sid IN (SELECT sid
FROM v$session
WHERE username IN (USER)); 


       SID UNAME                          TY        ID1        ID2 NM
---------- ------------------------------ -- ---------- ---------- ------------------------------
       440 DIXDROID                       AE        134          0
       440 DIXDROID                       TX     589853       3078
       440 DIXDROID                       TM      82284          0 TABLEB
       440 DIXDROID                       TM      82282          0 TABLEA


I will now connect to another session (sesion 2) and delete from tableB and that will induce a locking scenario in the database and session 2 will go into hung/waiting state.

---- from session 2:
SQL>
SQL>
SQL> delete tableB where pk_id = 1213;
.....
.........
............. <HUNG > <HUNG > <HUNG > <HUNG > <HUNG > 

Lets see more stats on the blocking session.

SQL>
SQL> SELECT (SELECT username
FROM v$session
WHERE sid = a.sid)
blocker,
a.sid,
' is blocking ',
(SELECT username
FROM v$session
WHERE sid = b.sid)
blockee,
b.sid
FROM v$lock a, v$lock b
WHERE a.block = 1 AND b.request > 0 AND a.id1 = b.id1 AND a.id2 = b.id2;  


BLOCKER                     SID 'ISBLOCKING'  BLOCKEE                               SID
-------------------- ---------- ------------- ------------------------------ ----------
DIXDROID                    440  is blocking  DIXDROID                              427

And as expected 440 SID is now blocking 427. Now lets go back to the original (session 1) and will try to delete from tableB again and this will snipe the session 2 which is still in hung/wait state and will throw a deadlock error (ORA-00060). Though we have already collected blocking information from v$lock + v$session but to get more clarify about blocking sessions, I will set the 10027 trace event with level 2.

This will increase our chance of finding the root cause of the deadlocks. If the setting shall persist across instance startups, you need to use the initialization parameter EVENT.

i.e. EVENT=”10027 trace name contex forever, level 2″ otherwise use ALTER SYSTEM version of it.

------------------------------------------------------------
-- Execute below statement in session1
------------------------------------------------------------
delete tableB where pk_id = 1213;





---------------------------------------------------------------------------------------
-- Oracle is throwing deadlock error as below in session2
---------------------------------------------------------------------------------------
SQL>
SQL> ALTER SYSTEM SET EVENTS '10027 trace name context forever, level 2';

System altered.

SQL> delete tableB where pk_id = 1213;
delete tableB where pk_id = 1213
       *
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at "DIXDROID.TABLEB_ST_TRG", line 11
ORA-04088: error during execution of trigger 'DIXDROID.TABLEB_ST_TRG'

-- same captured in alert log too.
2023-02-16T04:02:57.648156-05:00
Errors in file /u01/app/oracle/diag/rdbms/dixitdb/dixitdb/trace/dixitdb_ora_29389.trc:
2023-02-16T04:03:00.019794-05:00
ORA-00060: Deadlock detected. See Note 60.1 at My Oracle Support for Troubleshooting ORA-60 Errors. More info in file /u01/app/oracle/diag/rdbms/dixitdb/dixitdb/trace/dixitdb_ora_29389.trc.

And we done, we were able to simulate the deadlock case in the database. Now lets dig into the trace file generated by the deadlock along with information flushed by 10027 trace event. It has all crucial information associated with the deadlock.

2023-02-16 04:13:34.115*:ksq.c@13192:ksqdld_hdr_dump():
DEADLOCK DETECTED ( ORA-00060 )
See Note 60.1 at My Oracle Support for Troubleshooting ORA-60 Errors

[Transaction Deadlock]


Deadlock graph:
                                          ------------Blocker(s)-----------  ------------Waiter(s)------------
Resource Name                             process session holds waits serial  process session holds waits serial
TX-00080009-00000C62-00000000-00000000         45     427     X        13193      24     440           X  22765
TX-0004001A-00000C1E-00000000-00000000         24     440     X        22765      45     427           X  13193




----- Information for waiting sessions -----
Session 427:
  sid: 427 ser: 13193 audsid: 3390369 user: 115/DIXDROID
    flags: (0x41) USR/- flags2: (0x40009) -/-/INC
    flags_idl: (0x1) status: BSY/-/-/- kill: -/-/-/-
  pid: 45 O/S info: user: oracle, term: UNKNOWN, ospid: 30174
    image: oracle@oracleontario.ontadomain (TNS V1-V3)
  client details:
    O/S info: user: oracle, term: pts/1, ospid: 30173
    machine: oracleontario.ontadomain program: sqlplus@oracleontario.ontadomain (TNS V1-V3)
    application name: SQL*Plus, hash value=3669949024
  current SQL:
  UPDATE TABLEA SET TOTAL_AMT = (SELECT SUM (AMT) FROM TABLEB WHERE FK_ID = :B1 ) WHERE PK_ID = :B1

Session 440:
  sid: 440 ser: 22765 audsid: 3380369 user: 115/DIXDROID
    flags: (0x41) USR/- flags2: (0x40009) -/-/INC
    flags_idl: (0x1) status: BSY/-/-/- kill: -/-/-/-
  pid: 24 O/S info: user: oracle, term: UNKNOWN, ospid: 29847
    image: oracle@oracleontario.ontadomain (TNS V1-V3)
  client details:
    O/S info: user: oracle, term: pts/2, ospid: 29846
    machine: oracleontario.ontadomain program: sqlplus@oracleontario.ontadomain (TNS V1-V3)
    application name: SQL*Plus, hash value=3669949024
  current SQL:
  delete tableB where pk_id = 1213

.....
.......


----- Current SQL Statement for this session (sql_id=duw5q5rpd5xvs) -----
UPDATE TABLEA SET TOTAL_AMT = (SELECT SUM (AMT) FROM TABLEB WHERE FK_ID = :B1 ) WHERE PK_ID = :B1
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0x8ed34c98        11  DIXDROID.TABLEB_ST_TRG
.......
.........


----- VKTM Time Drifts Circular Buffer -----
session 427: DID 0001-002D-000000D9     session 440: DID 0001-0018-0000001C
session 440: DID 0001-0018-0000001C     session 427: DID 0001-002D-000000D9

Rows waited on:
  Session 427: obj - rowid = 0001416A - AAAUFqAAHAAAMgvAAB
  (dictionary objn - 82282, file - 7, block - 51247, slot - 1)
  Session 440: obj - rowid = 0001416C - AAAUFsAAHAAAMg/AAE
  (dictionary objn - 82284, file - 7, block - 51263, slot - 4)
.....
.......


Current Wait Stack:
     0: waiting for 'enq: TX - row lock contention'
        name|mode=0x54580006, usn<<16 | slot=0x4001a, sequence=0xc1e
        wait_id=69 seq_num=70 snap_id=1
        wait times: snap=18.173838 sec, exc=18.173838 sec, total=18.173838 sec
        wait times: max=infinite, heur=18.173838 sec
        wait counts: calls=6 os=6
        in_wait=1 iflags=0x15a0
    There is at least one session blocking this session.
      Dumping 1 direct blocker(s):
        inst: 1, sid: 440, ser: 22765
      Dumping final blocker:
        inst: 1, sid: 440, ser: 22765
    There are 1 sessions blocked by this session.
    Dumping one waiter:
      inst: 1, sid: 440, ser: 22765
      wait event: 'enq: TX - row lock contention'
        p1: 'name|mode'=0x54580006
        p2: 'usn<<16 | slot'=0x80009
        p3: 'sequence'=0xc62
      row_wait_obj#: 82284, block#: 51263, row#: 4, file# 7
      min_blocked_time: 0 secs, waiter_cache_ver: 33942
    Wait State:
      fixed_waits=0 flags=0x22 boundary=(nil)/-1
    Session Wait History:
        elapsed time of 0.004677 sec since current wait
........
............


24: USER ospid 29847 sid 440 ser 22765, waiting for 'enq: TX - row lock contention'
          Cmd: DELETE
          Blocked by inst: 1, sid: 427, ser: 13193
          Final Blocker inst: 1, sid: 427, ser: 13193
.....
.............


45: USER ospid 30174 sid 427 ser 13193, waiting for 'enq: TX - row lock contention'
          Cmd: UPDATE
          Blocked by inst: 1, sid: 440, ser: 22765
          Final Blocker inst: 1, sid: 440, ser: 22765
.........
.............


 The history is displayed in reverse chronological order.

    sample interval: 1 sec, max history 120 sec
    ---------------------------------------------------
      [19 samples,                                         04:13:16 - 04:13:34]
        waited for 'enq: TX - row lock contention', seq_num: 70
          p1: 'name|mode'=0x54580006
          p2: 'usn<<16 | slot'=0x4001a
          p3: 'sequence'=0xc1e
      [14 samples,                                         04:13:01 - 04:13:15]
        idle wait at each sample
      [session created at: 04:13:01]
    ---------------------------------------------------
    Sampled Session History Summary:
      longest_non_idle_wait: 'enq: TX - row lock contention'
      [19 samples, 04:13:16 - 04:13:34]
    ---------------------------------------------------
.........
..........


      Virtual Thread:
      kgskvt: 0x9b0e7f10, sess: 0x9ccdd4b8, pdb: 0, sid: 427 ser: 13193
      vc: (nil), proc: 0x9db42028, idx: 427
      consumer group cur: OTHER_GROUPS (pdb 0) (upd? 0)
mapped: DEFAULT_CONSUMER_GROUP, orig:  (pdb 0)
      vt_state: 0x2, vt_flags: 0xE030, blkrun: 0, numa: 1
      inwait: 1, wait event: 307, posted_run: 0
      lastmodrngcnt: 0, lastmodrngcnt_loc: '(null)'
      lastmodrblcnt: 0, lastmodrblcnt_loc: '(null)'
      location where insched last set: kgskbwt
      location where insched last cleared: kgskbwt
      location where inwait last set: kgskbwt
      location where inwait last cleared: NULL
      is_assigned: 1, in_scheduler: 0, insched: 0
      vt_active: 0 (pending: 1)
      vt_pq_active: 0, dop: 0, pq_servers (cur: 0 cg: 0)
      ps_allocs: 0, pxstmts (act: 0, done: 0 cg: 0)
      used quanta (usecs):
      stmt: 57272, accum: 0, mapped: 0, tot: 57272
      exec start consumed time lapse: 164377 usec
      exec start elapsed time lapse: 18179246 usec
      idle time: 0 ms, active time: 57272 (cg: 57272) usec
      last updnumps: 0 usec, active time (pq: 0 ps: 0) ms
      cpu yields:       stmt: 0, accum: 0, mapped: 0, tot: 0
      cpu waits:       stmt: 0, accum: 0, mapped: 0, tot: 0
      cpu wait time (usec):       stmt: 0, accum: 0, mapped: 0, tot: 0
      ASL queued time outs: 0, time: 0 (cur 0, cg 0)
      PQQ queued time outs: 0, time: 0 (cur 0, cg 0)
      Queue timeout violation: 0
      calls aborted: 0, num est exec limit hit: 0
      KTU Session Commit Cache Dump for IDLs:
      KTU Session Commit Cache Dump for Non-IDLs:
      ----------------------------------------
........
.............

Hope It Helped!
Prashant Dixit

Advertisement

Posted in Uncategorized | Tagged: , , , | Leave a Comment »

Here goes my first blog post on Real Application Testing on Oracle Blogging platform …

Posted by FatDBA on January 30, 2023

I’m excited to announce that I’ve authored my first blog post on Oracle’s multi-cloud observability and management platform. 
My post “Real Application Testing for Capture and Replay in a PDB, a great addition in 19c.” recently got published by Oracle Corporation on their blogging platform 🙂 🙂 🙂 🙂

https://blogs.oracle.com/observability/post/real-application-testing-for-capture-and-replay-in-a-pdb-a-great-addition-was-made-in-19c

Hope It Helped!
Prashant Dixit

Posted in Advanced | Tagged: , , | Leave a Comment »

What is Cluster Health Advisor GUI or CHAG ?

Posted by FatDBA on January 4, 2023

CHA GUI (CHAG) is a graphical user interface for Cluster Health Advisor (CHA) which was earlier internal to Oracle teams but its now available to the customers. It is a standalone, interactive, real-time capable front-end/GUI to the classic CHA utility. Oracle 12.2 is the first version that is supported by CHA GUI (CHAG). You only require RAC license and there is no need for any additional license to use CHAG tool.

CHAG communicates directly with the Grid Infrastructure Management Repository (GIMR) using a JDBC connection.. GIMR is mandatory for CHAG to work as it fetches the data out of the GIMR repository. In case you don’t have the GIMR repo installed, for example on 19c databases as GIMR is optional there, you can use the local mode for CHAG to work, but in absence of GIMR mgmt repo you will not get the historical abilities to go back in time.

Installation is quite simple, you have to download and unzip the software on one of your cluster machines and I recommend not to dump it inside your ORACLE HOME, but in a separate place. CHAG requires X11 or XHost and Java as it uses Java Swing to open the GUI. CHAG can operate in several modes:

  • With a default connection to GIMR Database. This option initiate a live session and provide real-time monitoring.
  • With a user specified location of a GIMR Database (option -P). This option initiate a live session and provide real-time monitoring.
  • Read in a text file with monitoring data (option -f). This initiates a passive session, which allow to analyze textual data extracted from a GIMR, or data collected during a live session. This data is held in a “*.mdb” file.
  • Parse text file with data and generate JSON object with information similar to query “diagnosis” (option -C). This mode of operation ‘chag -C -f ‘ directs CHAG to parse a *.mdb file and to generate a summary of its content in JSON format.

For the offline mode you can get the “mdb” file to analyze using below command. Depending on the time model you will get n number of mdb files for the period.

chactl export repository -format mdb -start <timestamp> -end <timestamp>

About the usage, CHAG is invoked using the ‘chag’ script available in the bin directory of the CHA Home. CHAG is designed primarily for Cluster or Database experts. Usage is quite simple and straight forward, you can move the pointer/slider to choose any particular timeframe to catch problems, their cause and the corrective actions. You can use it both in real time and offline version, its just that for real time you have to be on any of the cluster node, for offline you can generate the MDB file (cha datafile) and can run it anywhere on the client machine with no need of oracle home and only Java will be needed.

Below is the CHAG look and feel, its running on a 2 node RAC cluster where we have two databases installed. Here you see few color codes, where red colors means there were few problems during that interval.

Next is how it explains more about any particular problem caught for the timeslot. Gives you cause and the corrective action, for example in below screenshot it has detected that the ASM IO service time is higher than the expected which points to the underlying IO Subsystem used by ASM disks.

You can use SHIFT keys combinations to get wait event specific details for the selected time period.

You can use few other SHIFT key combinations to present the same data in the form of line graphs.

Few more examples or problems detected by the CHAG. This time it was reporting for redo log writes which are slower and that is something expected as ASM IO is slow too means the entire IO subsystem is impacted.

I highly recommend all readers to go through Doc ID 2340062.1 on metalink for more details on Cluster Health Advisor Graphical User Interface – CHAG.

Hope It Helped!
Prashant Dixit

Posted in Advanced, troubleshooting | Tagged: , , , | Leave a Comment »

Finally, I am an Oracle ACE :)

Posted by FatDBA on December 21, 2022

Christmas came a little early!

Finally, after associated with Oracle Databases and technologies for more than 15 years, and contributing towards the database community for more than 12 years, The FatDBA has finally got the golden email from Oracle Ace Program – “I am officially an Oracle Ace now ♠ ♠ ♠ “ 🙏 🤘 😊

Link to my profile: https://apexapps.oracle.com/pls/apex/r/ace_program/oracle-aces/ace?ace_id=2364&clear=2

Posted in Uncategorized | Leave a Comment »

No Hint, No Degree, No Auto-DOP, Why my query is going for the parallelism ?

Posted by FatDBA on December 16, 2022

Recently I was working on a performance problem where customer reported few of their SQL statements going for parallelism even when they are not forcing DOP via any HINT, and all of the referenced table and underlying Indexes were with degree=1

I was asked to take a look, and I immediately checked if Auto DOP was the reason forcing unwanted parallelism, but parallel_degree_policy was set to MANUAL which means the auto DOP, statement queuing and in-memory parallel execution all were disabled.

SQL> show parameter parallel_degree_policy

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
parallel_degree_policy               string      MANUAL

Next, I thought to verify Table and Indexes general stats or details and I queried DBA_TABLES & DBA_INDEXES for Instances column, and found one of the table was set to value ‘DEFAULT’. If we have a value of DEFAULT set for INSTANCES, it will always force the query use DEFAULT degree of parallelism.

Let me explain the impact of having DEFAULT value for Instances, and how it forces SQL to spawn parallelism. For demo purpose, I am going to create a test table and an index with INSTANCES value set to DEFAULT.

[oracle@oracleontario ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Dec 19 08:23:12 2022
Version 19.15.0.0.0

Copyright (c) 1982, 2022, Oracle.  All rights reserved.


SQL> create table fatdba_table as select * from dba_objects;

Table created.

SQL>
SQL> select count(*) from fatdba_table;

  COUNT(*)
----------
     74932

SQL>

SQL> create index fatdba_table_idx on fatdba_table(OBJECT_TYPE,object_name) parallel(DEGREE 1 INSTANCES DEFAULT);

Index created.

SQL>
SQL> select index_name,degree,instances from dba_indexes where index_name='FATDBA_TABLE_IDX';

INDEX_NAME                     DEGREE                                   INSTANCES
------------------------------ ---------------------------------------- ----------------------------------------
FATDBA_TABLE_IDX               1                                        DEFAULT

SQL>

Alright the stage is set, lets run a SQL statement and force it to use that Index and see its impact on the execution.

SQL> explain plan for select /*+ index_ffs(fatdba_table,fatdba_table_idx) */ count(distinct object_name) from fatdba_table 
where OBJECT_TYPE='TABLE';

Explained.

SQL> set linesize 400 pagesize 400
SQL> select * from table(dbms_xplan.display) ;

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1154043599

-------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name             | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                  |     1 |    66 |   157   (1)| 00:00:01 |        |      |            |
|   1 |  SORT AGGREGATE               |                  |     1 |    66 |            |          |        |      |            |
|   2 |   PX COORDINATOR              |                  |       |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)        | :TQ10001         |     1 |    66 |            |          |  Q1,01 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE            |                  |     1 |    66 |            |          |  Q1,01 | PCWP |            |
|   5 |      VIEW                     | VW_DAG_0         |  1558 |   100K|   157   (1)| 00:00:01 |  Q1,01 | PCWP |            |
|   6 |       HASH GROUP BY           |                  |  1558 | 68552 |   157   (1)| 00:00:01 |  Q1,01 | PCWP |            |
|   7 |        PX RECEIVE             |                  |  1561 | 68684 |   156   (0)| 00:00:01 |  Q1,01 | PCWP |            |
|   8 |         PX SEND HASH          | :TQ10000         |  1561 | 68684 |   156   (0)| 00:00:01 |  Q1,00 | P->P | HASH       |
|   9 |          PX BLOCK ITERATOR    |                  |  1561 | 68684 |   156   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|* 10 |           INDEX FAST FULL SCAN| FATDBA_TABLE_IDX |  1561 | 68684 |   156   (0)| 00:00:01 |  Q1,00 | PCWP |            |
-------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  10 - filter("OBJECT_TYPE"='TABLE')

22 rows selected.

SQL>

As expected, it forced SQL to go with parallelism. Let me set INSTANCE value of the Index to 1 and see what happens next.

SQL> alter index FATDBA_TABLE_IDX noparallel;

Index altered.

SQL> select index_name,degree,instances from dba_indexes where index_name='FATDBA_TABLE_IDX';


INDEX_NAME                     DEGREE                                   INSTANCES
------------------------------ ---------------------------------------- ----------------------------------------
FATDBA_TABLE_IDX               1                                        1

SQL> SQL>


SQL> select index_name,degree,instances from dba_indexes where index_name='FATDBA_TABLE_IDX';


INDEX_NAME                     DEGREE                                   INSTANCES
------------------------------ ---------------------------------------- ----------------------------------------
FATDBA_TABLE_IDX               1                                        1

SQL> SQL> explain plan for select /*+ index_ffs(fatdba_table,fatdba_table_idx) */ count(distinct object_name) 
from fatdba_table where OBJECT_TYPE='TABLE';

Explained.

SQL> select * from table(dbms_xplan.display) ;

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
Plan hash value: 3184007477

--------------------------------------------------------------------------------------------
| Id  | Operation               | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                  |     1 |    66 |   157   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE         |                  |     1 |    66 |            |          |
|   2 |   VIEW                  | VW_DAG_0         |  1558 |   100K|   157   (1)| 00:00:01 |
|   3 |    HASH GROUP BY        |                  |  1558 | 68552 |   157   (1)| 00:00:01 |
|*  4 |     INDEX FAST FULL SCAN| FATDBA_TABLE_IDX |  1561 | 68684 |   156   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("OBJECT_TYPE"='TABLE')

16 rows selected.

And no parallelism was used after set the value of INSTANCES to 1.

Hope It Helped!
Prashant Dixit

Posted in Advanced, troubleshooting | Tagged: , , , | Leave a Comment »

Why I am not able to locate 21c for my Always Free Autonomous Databases ?

Posted by FatDBA on December 4, 2022

Recently one of my friend who started learning OCI told me he is not able to locate Oracle 21c Autonomous database in the list, but can only see 19c. He was using ‘Always Free Autonomous Databases’ to test few things on Oracle Database version 21c.

I was sure it was happening due to region he selected, I mean you can provision free Autonomous Databases only in your home region, and not all regions support the same database versions. The supported version may be 19c-only or 21c-only, depending on the region you have selected.

When checked, found he was trying from the ap-tokyo-1 (Tokyo region – key NRT) where only 19c version is available in ‘Always Free Autonomous Databases’. So, it always depends the region where you are trying creating your Always Free Autonomous Database Oracle Database weather you see 19c or 21c.

Always Free databases can be created in Oracle Cloud Infrastructure accounts that are in a trial period, have paying status, or are always free. More details : https://docs.oracle.com/en-us/iaas/Content/Database/Concepts/adbfreeoverview.htm

Try and sign up for Oracle Cloud Free Tier, trust me its awesome 🙂 🙂 : https://www.oracle.com/cloud/free/

Hope It Helped
Prashant Dixit

Posted in Basics | Tagged: , , | Leave a Comment »

Some of the new exotic wait events in Oracle 21c …

Posted by FatDBA on November 12, 2022

Hi Guys,


Lately I was doing tests on Oracle 21c and came across few of the exotic new wait events added to Oracle 21c. I was using 21.3.0.0.0 and have noticed few new Exadata, ASM, Dataguard, AI/ML, Exascale, Cluster, exa persistent memory, CTWR etc. specific waits were added to the database. I have also noticed some strange looking waits as well i.e. ‘Spare1′,’Spare2′,’Spare3’ ….

Being an innovation release, and since this is a beta version, there is no document available or published for the customer for any of these new waits on Metalink

Couple of new ‘Bloom Filter‘ related waits added to 21c
Bloom Filter EOF
Bloom Filter Fetch

Bunch of new Exadata ‘smart Index|Table scan’ related, User I/O classed wait events added to 21c are
cell smart index scan request
cell smart index scan: db timezone upgrade
cell smart index scan: disabled by user
cell smart index scan: pass through
cell smart table scan request
cell smart table scan: db timezone upgrade
cell smart table scan: disabled by user
cell smart table scan: pass through
cell worker online completion
cell worker retry

Few of the new machine learning & Artificial Intellegence related wait events added in 21c are
enq: AI - Seek operation
enq: AI - dequeue operation
enq: AI - lwm update operation
enq: AI - remove subscriber
enq: AI - start cross operation
enq: AI - stop cross operation
enq: AI - truncate operation

Some of the new RAC (cluster class), global cache (cache fusion) block related waits added to 21c are
gc cr block direct read
gc current block direct read

Some of the new EDSLib/EBSLib latch waits, which are used to gum the code between RDBMS and Exascale storage added in 21c are given below.
latch: EDSLib File
latch: EDSLib Message
latch: EDSLib Root
latch: EGSLib Root
latch: EGSLibDS Root
latch: EGSLibDataStore Object
latch: EGSLibDataStoreShard Object
latch: EGSLibForum Object
latch: EGSLibNet Root
latch: EGSLibSP Root
latch: EGSLibStoragePool Object
latch: EGSLibStoragePoolRing Object
latch: FOB s.o list parent latch

This is by far the most interesting one, with no name and they seem some sort of free or extra wait events names. I am familiar with few of the other ‘spare’ i.e. “enq: BS – krb spare”, “enq: SP..xx”, “enq: SX.xx.xxx“, but these are different
spare 10
spare 2
spare 3
spare 4
spare 5
spare 6
spare 7
spare 8
spare 9

Couple of new ASM related wait events were added too
enq: AM - ASM Scrubbing
enq: AM - ASM used quota calculation

Some of the new enqueue waits on Change Tracking file are
enq: CT - CTWR DBA buffer resize
enq: CT - CTWR datafile sync
enq: CT - CTWR thread checkpoint

Some of the new enqueue related waits on Exadata Persistent Memory (PMEM)
enq: FF - PMEMfs - ODM
enq: FF - PMEMfs - Param
enq: FF - PMEMfs - Resize
enq: FF - PMEMfs - aXtnd pool
enq: FF - PMEMfs - reSze pool

Hope It Helped!
Prashant Dixit

Posted in Uncategorized | Tagged: , , , | Leave a Comment »

Kafka Java Client for Transactional Event Queues (TEQ), Another great feature added to Oracle 21c …

Posted by FatDBA on November 6, 2022

Hi Guys,

Recently I was testing some advance queuing functions to setup an asynchronous communication (using event queues) between few applications and users on Oracle 21c database, to build a messaging platform. I was looking for a way to connect my client side libraries to allow Kafka APIs to connect to Oracle DB.

I had no clue how to do this integration, and then I came across this official documentation which talks about a new functionality in Oracle Database Version 21c – Kafka Java Client for Transactional Event Queues (TEQ) that enables Kafka application compatibility with Oracle Database. This provides easy migration of Kafka applications to TEQ.

Oracle Transactional Event Queue (TEQ) makes it easy to implement event-based applications. It is also highly integrated with Apache Kafka. Apart from enabling apps that use Kafka APIs to transparently operate on Oracle TEQ, Oracle TEQ also supports bi-directional information flow between TEQ and Kafka, so that changes are available in TEQ or Kafka as soon as possible in near-real-time.

Apache Kafka Connect is a framework included in Apache Kafka that integrates Kafka with other systems. Oracle TEQ will provide standard JMS package and related JDBC, Transaction packages to establish the connection and complete the transactional data flow. Oracle TEQ configures standard Kafka JMS connectors to establish interoperability and complete the data flow between the two messaging systems.

Another great blog post on the topic : https://docs.oracle.com/en/database/oracle/oracle-database/21/adque/Kafka_cient_interface_TEQ.html#GUID-94589C97-F323-4607-8C3A-10A0EDF9DA0D

Hope It Helps!
Prashant Dixit

Posted in Uncategorized | Tagged: , , , | Leave a Comment »

TRANLOGOPTIONS, a crucial performance parameter, Golden Gate 21c, a BUG and a quick workaround …

Posted by FatDBA on October 23, 2022

One of the crucial performance parameter for Golden gate extract process is TRANLOGOPTIONS which controls the way that it interacts with the transaction log. You can use multiple TRANLOGOPTIONS statements in the same parameter file, or you can specify multiple options within the same TRANLOGOPTIONS statement.

There are lot of performance related options i.e. INCLUDEAUX (AUX trails when reading audit trails), DBLOGREADERBUFSIZE etc. that you can use with TRANLOGOPTIONS parameter, but recently I’d tried one of the tuning parameter PERFORMANCEPROFILE with our medium intensity workload. It can be set to HIGH and MEDIUM (default). It helps achieve better performance by grouping the parameters that affect performance. Once the performance profile is set up, this option automatically configures the applicable parameters, to achieve the desired throughput and latency.

We’d used this parameter in one of our 21c (21.7.0) GG installation with TRANLOGOPTIONS PERFORMANCEPROFILE HIGH, but immediately we’d started seeing spikes in extract’s latency. This was might be because it increases the Extract’s read buffer size to 8MB and the rule to purge the extract read buffer is either when the buffer is full or there is no ingress records for 0.2 seconds. Therefore, any uninterrupted workload with Extract consumption rate below 8MB will result in integrated Extract latency to exceed 1 second.

We’d checked with Oracle support and as a quick temporary solution they’d suggested to not use PERFORMANCEPROFILE parameter with HIGH flag, as the Extract consumption/intake rates are below specific value, such as ~15 MB/sec to get ~0.5 second extract response times. Hence we’d set the buffer size to one-third of the redo generation rate in MB/sec to get ~0.3 second maximum Extract latency. They also marked this as Bug 33772499 for GG 21c for July 2022 release.

Hope It Helped!
Prashant Dixit

Posted in Advanced, troubleshooting | Tagged: , , , | Leave a Comment »

SUMMARY, my favorite Trace File Analyzer (TFA) command …

Posted by FatDBA on October 16, 2022

Hi Guys,

Trace File Analyzer (TFA) is one of the popular Oracle provided toolkit that is very useful when you are dealing with any ORA error and want to pack incident files or when you want to monitor your database using oratop (now part of tfa), or want to run orachk (part of tfa) for a health check of your entire Oracle software stack for issue detection. It also comes with support tools like OSWatcher, process watcher and darda which we all are very much familiar. Today’s post is about one of my favorite TFA utilities option/flag ‘Summary’ which is not that commonly used, but is one of the cool things that we you can do with the TFA.

For this post I have used TFA version 21.2.0.0.0, I suggest to use latest version as it comes with bug fixes and lot of new features.

Feature : ‘summary
This flag/function of TFA provides you a detailed (high level) real time status summary of your full DB system. It collects stats of your CRS, ASM, ACFS, Database, Patching Info, Network, Listeners and Operating System. Once you execute ‘summary’ mode within TFA command line (TFACTL), it will take you to tfactl_summary mode or SUMMARY Command-Line Interface (CLI) from where you can select from multiple options to view component wise statistics. The best part is you can even save the report in the form of HTML page (using option q) which is more fancy and easy to read version, otherwise text based is the default version.

Alright lets get started, I am going to call the ‘summary’ option and will see what is there for us.

tfactl>
tfactl>
tfactl> summary

  Executing Summary in Parallel on Following Nodes:
    Node : nodeabc
    Node : nodexyz

LOGFILE LOCATION : /u01/app/grid/oracle.ahf/data/repository/suptools/nodeabc/summary/root/20221015052849/log/summary_command_20221015052849_nodeabc_32378.log

  Component Specific Summary collection :
    - Collecting CRS details ... Done.
    - Collecting ASM details ... Done.
    - Collecting ACFS details ... Done.
    - Collecting DATABASE details ... Done.
    - Collecting PATCH details ... Done.
    - Collecting LISTENER details ... Done.
    - Collecting NETWORK details ... Done.
    - Collecting OS details ... Done.
    - Collecting TFA details ... Done.
    - Collecting SUMMARY details ... Done.

  Remote Summary Data Collection : In-Progress - Please wait ...
  - Data Collection From Node - nodexyz .. Done.

  Prepare Clusterwide Summary Overview ... Done
      cluster_status_summary

  COMPONENT   STATUS    DETAILS
+-----------+---------+---------------------------------------------------------------------------------------------------+
  CRS         PROBLEM   .-----------------------------------------------.
                        | CRS_SERVER_STATUS   : ONLINE                  |
                        | CRS_STATE           : ONLINE                  |
                        | CRS_INTEGRITY_CHECK : FAIL                    |
                        | CRS_RESOURCE_STATUS : OFFLINE Resources Found |
                        '-----------------------------------------------'
  ASM         PROBLEM   .-------------------------------------------------------.
                        | ASM_DISK_SIZE_STATUS : WARNING - Available Size < 20% |
                        | ASM_BLOCK_STATUS     : PASS                           |
                        | ASM_CHAIN_STATUS     : PASS                           |
                        | ASM_INCIDENTS        : PASS                           |
                        | ASM_PROBLEMS         : PASS                           |
                        '-------------------------------------------------------'
  ACFS        OFFLINE   .-----------------------.
                        | ACFS_STATUS : OFFLINE |
                        '-----------------------'
  DATABASE    PROBLEM   .-----------------------------------------------------------------------------------------------.
                        | ORACLE_HOME_DETAILS                                                        | ORACLE_HOME_NAME |
                        +----------------------------------------------------------------------------+------------------+
                        | .------------------------------------------------------------------------. | OraDB19000_home1 |
                        | | PROBLEMS | INCIDENTS | DB_BLOCKS | DATABASE_NAME | STATUS  | DB_CHAINS | |                  |
                        | +----------+-----------+-----------+---------------+---------+-----------+ |                  |
                        | | PROBLEM  | PROBLEM   | PASS      | FATDBA1       | PROBLEM | PROBLEM   | |                  |
                        | '----------+-----------+-----------+---------------+---------+-----------' |                  |
                        '----------------------------------------------------------------------------+------------------'
  PATCH       OK        .----------------------------------------------.
                        | CRS_PATCH_CONSISTENCY_ACROSS_NODES      : OK |
                        | DATABASE_PATCH_CONSISTENCY_ACROSS_NODES : OK |
                        '----------------------------------------------'
  LISTENER    OK        .-----------------------.
                        | LISTNER_STATUS   : OK |
                        '-----------------------'
  NETWORK     OK        .---------------------------.
                        | CLUSTER_NETWORK_STATUS :  |
                        '---------------------------'
  OS          OK        .-----------------------.
                        | MEM_USAGE_STATUS : OK |
                        '-----------------------'
  TFA         OK        .----------------------.
                        | TFA_STATUS : RUNNING |
                        '----------------------'
  SUMMARY     OK        .------------------------------------.
                        | SUMMARY_EXECUTION_TIME : 0H:1M:41S |
                        '------------------------------------'
+-----------+---------+---------------------------------------------------------------------------------------------------+


        ### Entering in to SUMMARY Command-Line Interface ###

tfactl_summary>list

  Components : Select Component - select [component_number|component_name]
        1 => overview
        2 => crs_overview
        3 => asm_overview
        4 => acfs_overview
        5 => database_overview
        6 => patch_overview
        7 => listener_overview
        8 => network_overview
        9 => os_overview
        10 => tfa_overview
        11 => summary_overview

tfactl_summary>
tfactl_summary>
tfactl_summary>

Now when all statistics summaries are collected, this will automatically take us to the SUMMARY Command-Line Interface (tfactl_summary>) where if we use ‘list’ option will bring the entire chart of options to choose from (see above all 11 different areas). Next I am going to select option 3 to get ASM_OVERVIEW from tfactl_summary CLI which when selected will further take you to another linked CLI that is tfactl_summary_asmoverview> which will further gives you details of your entire cluster, like in my case I have node machines as a part of this cluster.

So basically the flow will be something like this
tfactl —> Use summary command and enter into tfactl_summary —> Choose option from the list using ‘list’ command and enter into option related CLI (i.e. tfactl_summary_asmoverview>) and choose option or node to view stats.

Please take a look below for better understanding.

tfactl_summary>3

  ASM_INSTANCE   HOSTNAME   ASM_DISK_SIZE_STATUS             ASM_BLOCK_STATUS   ASM_CHAIN_STATUS   ASM_INCIDENTS   ASM_PROBLEMS
+--------------+----------+--------------------------------+------------------+------------------+---------------+--------------+
  +ASM1          nodeabc    WARNING - Available Size < 20%   PASS               PASS               PASS            PASS
+--------------+----------+--------------------------------+------------------+------------------+---------------+--------------+

tfactl_summary_asmoverview>list

  Status Type: Select Status Type - select [status_type_number|status_type_name]
        1 => asm_clusterwide_status
        2 => asm_nodeabc
        3 => asm_nodexyz

tfactl_summary_asmoverview>2


     =====> asm_problems

  ADR_EVENTS
+---------------------------------------------------------------------------------------+
  .-----------------------------------------------------------------------------------.
  | STATUS_TYPE          | DETAILS                                                    |
  +----------------------+------------------------------------------------------------+
  | ADR_HOME             | /u01/app/grid/diag/clients/user_oracle/host_2241630980_110 |
  | ORACLE_HOME          | /u01/app/19.0.0.0/grid                                     |
  | PROBLEM_STATUS_CHECK | host_2241630980_110:PASS                                   |
  '----------------------+------------------------------------------------------------'
..
....
......

     =====> asm_instancefiles

  file_number   compound_index   bytes         block_size   name               modification_date   creation_date   striped   incarnation   group_number
+-------------+----------------+-------------+------------+------------------+-------------------+---------------+---------+-------------+--------------+
          253         16777469          1536          512   ASMPARAMETERFILE   31-AUG-20           31-AUG-20       COARSE     1049911287              1
          255         16777471     165974016         4096   OCRFILE            18-APR-21           31-AUG-20       COARSE     1049911291              1
          256         16777472         21504          512   PASSWORD           31-AUG-20           31-AUG-20       COARSE     1049911289              1
          257         16777473         11264          512   PASSWORD           31-AUG-20           31-AUG-20       COARSE     1049911469              1
          258         16777474      20971520        32768   ASMVDRL            18-APR-21           31-AUG-20       CUSTOM     1049912853              1
          259         16777475    5368709120      1048576   ASMVOL             18-APR-21           31-AUG-20       CUSTOM     1049912853              1
          260         16777476    5368717312         8192   DATAFILE           14-OCT-22           31-AUG-20       COARSE     1049923157          
...
....
     =====> asm_status_summary

  STATUS_TYPE                    STATUS
+------------------------------+-----------------------------------------------------------------+
  SYSTEM_DATE                    Sat Oct 15 05:29:06 UTC 2022
  ASM_HOME                       /u01/app/19.0.0.0/grid
  ASM_VERSION
  ASM_INSTANCE                   +ASM1
  ASM_DIAGNOSTICS_TRACE_FOLDER   /u01/app/grid/diag/asm/+asm/+ASM1/trace
  ASM_CHAIN_STATUS               PASS
  ASM_BLOCK_STATUS               PASS
  ASM_DISK_SIZE_STATUS           WARNING - Available Size < 20%
  ASM_DISK_GROUP_SUMMARY         .------------------------.
                                 | name | disk_size_alert |
                                 +------+-----------------+
                                 | DATA | Red : 14%       |
                                 | RECO | Red : 8%        |
                                 '------+-----------------'
  ADR_EVENTS                     .-------------------------------------------------------------.
                                 | HOSTNAME | INCIDENT_STATUS | INSTANCE_NAME | PROBLEM_STATUS |
                                 +----------+-----------------+---------------+----------------+
                                 | nodeabc  | PASS            | ASM1          | PASS           |
                                 '----------+-----------------+---------------+----------------'
+------------------------------+-----------------------------------------------------------------+

    =====> asm_diskgroup_details

  allocation_unit_size   name   disk_size_alert   state     usable_file_mb   type   total_mb   group_number
+----------------------+------+-----------------+---------+----------------+------+----------+--------------+
               4194304   DATA   Red : 14%         MOUNTED          5141366   HIGH   35163648              1
               4194304   RECO   Red : 8%          MOUNTED           731597   HIGH    8788992              2
+----------------------+------+-----------------+---------+----------------+------+----------+--------------+


  Status Type: Select Status Type - select [status_type_number|status_type_name]
        1 => asm_clusterwide_status
        2 => asm_nodeabc
        3 => asm_nodexyz








--
--
--
--
--
--
--
-- Next I am going to select option 9 to view OS_OVERVIEW
--
--
tfactl_summary>9

  IDLE_TIME   #CORES   HOSTNAME   SWAP_USED   MEM_USED   TASKS   LOAD   #PROCESSORS
+-----------+--------+----------+-----------+----------+-------+------+-------------+
       94.4       12   nodeabc     0.00 %      59.94 %    1971   2.44            48
+-----------+--------+----------+-----------+----------+-------+------+-------------+

tfactl_summary_osoverview>list

  Status Type: Select Status Type - select [status_type_number|status_type_name]
        1 => os_clusterwide_status
        2 => os_nodeabc
        3 => os_nodexyz

tfactl_summary_osoverview>2


     =====> os_disk_details

  PATH
+------------------------------------+
  /dev/asm/commonstore-332
  /dev/mapper/IO1_P1S00_2758906044
  /dev/mapper/IO1_P1S01_2758880300
  /dev/mapper/IO1_P1S02_2758924712
  /dev/mapper/IO1_P1S03_2758924872
  /dev/mapper/IO1_P1S04_2758924616
  /dev/mapper/IO1_P1S05_2758906308
  /dev/mapper/VolGroupSys-LogVolOpt
  /dev/mapper/VolGroupSys-LogVolRoot
  /dev/mapper/VolGroupSys-LogVolSwap
  /dev/mapper/VolGroupSys-LogVolU01
  /dev/md126
  /dev/sda
  /dev/sdb
  /dev/sdc
  /dev/sdd
  /dev/sde
  /dev/sdf
  /dev/sdg
  /dev/sdh
  /dev/sdi
  /dev/sdj
  /dev/sdk
  /dev/sdl
  /dev/sdm
  /dev/sdn
+------------------------------------+



     =====> os_status_summary

  STATUS TYPE     DETAILS
+---------------+--------------------------------------------------------------------------+
  OS and SYSTEM   .----------------------------------------------------------------------.
                  | #CORES | CACHE    | KERNEL_RELEASE                     | #PROCESSORS |
                  +--------+----------+------------------------------------+-------------+
                  |     12 | 22528 KB | 4.14.35-1902.303.4.1.el7uek.x86_64 |          48 |
                  '--------+----------+------------------------------------+-------------'
  SLEEPING TASK   .----------------------------------.
                  | PID | TASK              | USER   |
                  +-----+-------------------+--------+
                  | 531 | wait_woken        | oracle |
                  | 301 | KsWaitEvent_OSD   | root   |
                  | 267 | worker_thread     | root   |
                  | 217 | do_semtimedop     | oracle |
                  | 192 | smpboot_thread_fn | root   |
                  '-----+-------------------+--------'
  CPU DETAILS     .-------------------------------------------------.
                  | IDLE_TIME | SWAP_USED | LOAD | TASKS | MEM_USED |
                  +-----------+-----------+------+-------+----------+
                  |      94.4 |  0.00 %   | 2.44 |  1971 |  59.94 % |
                  '-----------+-----------+------+-------+----------'
+---------------+--------------------------------------------------------------------------+



     =====> os_disk_location

  ID   BLOCKS       BOOT   START   SYSTEM   END         PATH
+----+------------+------+-------+--------+-----------+-----------+
  ee   468845567+   NO         1   GPT      937691135   /dev/sda1
  ee   468845567+   NO         1   GPT      937691135   /dev/sdb1
+----+------------+------+-------+--------+-----------+-----------+



     =====> os_sleeping_tasks

  PID   TASK                    USER
+-----+-----------------------+--------+
  531   wait_woken              oracle
  301   KsWaitEvent_OSD         root
  267   worker_thread           root
  217   do_semtimedop           oracle
  192   smpboot_thread_fn       root
  112   rescuer_thread          root
   64   kthread_parkme          root
   38   do_semtimedop           grid
   22   pipe_wait               grid
   20   poll_schedule_timeout   root
+-----+-----------------------+--------+



     =====> system_configuration

|                                               System configuration                                              |
+-----------+-------------+----------+------------------------------------------+-------+----------+--------------+
  Frequency   #Processors   Hostname   Model_name                                 Cores   Cache      Vendor
+-----------+-------------+----------+------------------------------------------+-------+----------+--------------+
   3100.005            48   nodeabc    Intel(R) Xeon(R) Gold 5218 CPU @ 2.30GHz      12   22528 KB   GenuineIntel
+-----------+-------------+----------+------------------------------------------+-------+----------+--------------+



     =====> os_cpu_details

  TYPE           DETAILS
+--------------+-----------------------------------------------------------------------------+
  AVERAGE_LOAD   .------------------------------------------------.
                 | LAST_5_MINUTE | LAST_1_MINUTE | LAST_15_MINUTE |
                 +---------------+---------------+----------------+
                 |          2.95 |          3.55 |           2.44 |
                 '---------------+---------------+----------------'
  TASKS          .------------------------------------------------.
                 | Total | Stopped | Running | Sleeping | Zombies |
                 +-------+---------+---------+----------+---------+
                 |  1971 |      17 |       1 |     1508 |       0 |
                 '-------+---------+---------+----------+---------'
  MEMORY         .-------------------------------------------------------------------------.
                 | Total        | Used         | Percent_Used | Buffers      | Free        |
                 +--------------+--------------+--------------+--------------+-------------+
                 | 39466067+ KB | 23657516+ KB |  59.94 %     | 12640815+ KB | 31677368 KB |
                 '--------------+--------------+--------------+--------------+-------------'
  SWAP           .-------------------------------------------------------------------.
                 | Total       |  | Used | Cached       | Percent_Used | Free        |
                 +-------------+--+------+--------------+--------------+-------------+
                 | 25165820 KB |  | 0 KB | 14715107+ KB |  0.00 %      | 25165820 KB |
                 '-------------+--+------+--------------+--------------+-------------'
  CPU            .----------------------------.
                 | VALUE | TYPE               |
                 +-------+--------------------+
                 |   1.9 | USER_CPU_TIME      |
                 |   1.7 | SYSTEM_CPU_TIME    |
                 |   0.0 | USER_NICE_CPU_TIME |
                 |  94.4 | IDLE_CPU_TIME      |
                 |   2.0 | IO_WAIT_CPU_TIME   |
                 |   0.0 | HARDWARE_IRQ       |
                 |   0.0 | SOFTWARE_IRQ       |
                 |   0.0 | STEAL_TIME         |
                 '-------+--------------------'
+--------------+-----------------------------------------------------------------------------+


  Status Type: Select Status Type - select [status_type_number|status_type_name]
        1 => os_clusterwide_status
        2 => os_nodeabc
        3 => os_nodexyz

tfactl_summary_osoverview>

You can even save your output into an HTML file which is great for a quick view and very easy to present and transfer too. The option to generate report into HTML format is by calling “summary -overview -html” command within TFACTL and use option ‘q’ to save reports from tfactl_summary> CLI.
Let’s see how to do that.

[root@nodeabc ~]#
[root@nodeabc ~]# tfactl
WARNING - AHF Software is older than 180 days. Please consider upgrading AHF to the latest version using ahfctl upgrade.
tfactl> summary -overview -html

  Executing Summary in Parallel on Following Nodes:
    Node : nodeabc
    Node : nodexyz

LOGFILE LOCATION : /u01/app/grid/oracle.ahf/data/repository/suptools/nodeabc/summary/root/20221012121321/log/summary_command_20221012121321_nodeabc_82905.log

  Component Specific Summary collection :
    - Collecting CRS details ... Done.
    - Collecting ASM details ... Done.
    - Collecting ACFS details ... Done.
    - Collecting DATABASE details ... Done.
    - Collecting PATCH details ... Done.
    - Collecting LISTENER details ... Done.
    - Collecting NETWORK details ... Done.
    - Collecting OS details ... Done.
    - Collecting TFA details ... Done.
    - Collecting SUMMARY details ... Done.

  Remote Summary Data Collection : In-Progress - Please wait ...
  - Data Collection From Node - nodexyz .. Done.

  Prepare Clusterwide Summary Overview ... Done
      cluster_status_summary

  COMPONENT   STATUS    DETAILS
+-----------+---------+---------------------------------------------------------------------------------------------------+
  CRS         PROBLEM   .-----------------------------------------------.
                        | CRS_SERVER_STATUS   : ONLINE                  |
                        | CRS_STATE           : ONLINE                  |
                        | CRS_INTEGRITY_CHECK : FAIL                    |
                        | CRS_RESOURCE_STATUS : OFFLINE Resources Found |
                        '-----------------------------------------------'
  ASM         PROBLEM   .-------------------------------------------------------.
                        | ASM_DISK_SIZE_STATUS : WARNING - Available Size < 20% |
                        | ASM_BLOCK_STATUS     : PASS                           |
                        | ASM_CHAIN_STATUS     : PASS                           |
                        | ASM_INCIDENTS        : PASS                           |
                        | ASM_PROBLEMS         : PASS                           |
                        '-------------------------------------------------------'
  ACFS        OFFLINE   .-----------------------.
                        | ACFS_STATUS : OFFLINE |
                        '-----------------------'
  DATABASE    PROBLEM   .-----------------------------------------------------------------------------------------------.
                        | ORACLE_HOME_DETAILS                                                        | ORACLE_HOME_NAME |
                        +----------------------------------------------------------------------------+------------------+
                        | .------------------------------------------------------------------------. | OraDB19000_home1 |
                        | | PROBLEMS | INCIDENTS | DB_BLOCKS | DATABASE_NAME | STATUS  | DB_CHAINS | |                  |
                        | +----------+-----------+-----------+---------------+---------+-----------+ |                  |
                        | | PROBLEM  | PROBLEM   | PASS      | FATDBA1       | PROBLEM | PROBLEM   | |                  |
                        | '----------+-----------+-----------+---------------+---------+-----------' |                  |
                        '----------------------------------------------------------------------------+------------------'
  PATCH       OK        .----------------------------------------------.
                        | CRS_PATCH_CONSISTENCY_ACROSS_NODES      : OK |
                        | DATABASE_PATCH_CONSISTENCY_ACROSS_NODES : OK |
                        '----------------------------------------------'
  LISTENER    OK        .-----------------------.
                        | LISTNER_STATUS   : OK |
                        '-----------------------'
  NETWORK     OK        .---------------------------.
                        | CLUSTER_NETWORK_STATUS :  |
                        '---------------------------'
  OS          OK        .-----------------------.
                        | MEM_USAGE_STATUS : OK |
                        '-----------------------'
  TFA         OK        .----------------------.
                        | TFA_STATUS : RUNNING |
                        '----------------------'
  SUMMARY     OK        .------------------------------------.
                        | SUMMARY_EXECUTION_TIME : 0H:1M:44S |
                        '------------------------------------'
+-----------+---------+---------------------------------------------------------------------------------------------------+


        ### Entering in to SUMMARY Command-Line Interface ###

tfactl_summary>list

  Components : Select Component - select [component_number|component_name]
        1 => overview
        2 => crs_overview
        3 => asm_overview
        4 => acfs_overview
        5 => database_overview
        6 => patch_overview
        7 => listener_overview
        8 => network_overview
        9 => os_overview
        10 => tfa_overview
        11 => summary_overview

tfactl_summary>q

        ### Exited From SUMMARY Command-Line Interface ###

--------------------------------------------------------------------
REPOSITORY  : /u01/app/grid/oracle.ahf/data/repository/suptools/nodeabc/summary/root/20221012121321/nodeabc
HTML REPORT : <REPOSITORY>/report/Consolidated_Summary_Report_20221012121321.html
--------------------------------------------------------------------

tfactl> 

This is how the HTML report looks like, the summary TFA report.

Hope It Helped!
Prashant Dixit

Posted in Advanced, troubleshooting | Tagged: | Leave a Comment »

 
%d bloggers like this: