Autonomous Health Framework (AHF ) Inventory Status STOPPED

This post demonstrates how to resolve AHF Inventory Status is “STOPPED” without uninstalling and installing AHF.

# ahfctl statusahf

.-------------------------------------------------------------------------------------------------.
| Host     | Status of TFA | PID    | Port | Version    | Build ID             | Inventory Status |
+----------+---------------+--------+------+------------+----------------------+------------------+
| racnode1 | RUNNING       |  10052 | 5000 | 21.4.0.0.0 | 21400020211220074549 | STOPPED          |
| racnode2 | RUNNING       | 384602 | 5000 | 21.4.0.0.0 | 21400020211220074549 | COMPLETE         |
| racnode3 | RUNNING       |  20041 | 5000 | 21.4.0.0.0 | 21400020211220074549 | COMPLETE         |
| racnode4 | RUNNING       | 228081 | 5000 | 21.4.0.0.0 | 21400020211220074549 | COMPLETE         |
'----------+---------------+--------+------+------------+----------------------+------------------'

Subscribe to get access

Read more of this content when you subscribe today.

How to Move LOBSEGMENT LOBINDEX to Another Tablespace

Check LOB current tablespace

SQL> select owner, segment_name, segment_type, tablespace_name , bytes from dba_segments where owner='USER1';

OWNER    SEGMENT_NAME                SEGMENT_TYPE       TABLESPACE_NAME    BYTES
-------- --------------------------- ------------------ ----------------- ----------
USER1    SYS_IL0000057639C00010$$    LOBINDEX           TEMPDATA	       65536
USER1    SYS_LOB0000057639C00010$$   LOBSEGMENT         TEMPDATA	    12713984
USER1    SYS_IL0000057685C00008$$    LOBINDEX           TEMPDATA	       65536
USER1    SYS_LOB0000057685C00008$$   LOBSEGMENT         TEMPDATA	     2228224
USER1    SYS_IL0000057693C00008$$    LOBINDEX           TEMPDATA	       65536
USER1    SYS_LOB0000057693C00008$$   LOBSEGMENT         TEMPDATA	      131072

6 rows selected.

Generate moving LObs sql

SQL> select 'alter table  '||owner||'.'||table_name||' move lob ('||column_name||') store as (tablespace USER1) ;' from dba_lobs where  owner='USER1';

'ALTERTABLE'||OWNER||'.'||TABLE_NAME||'MOVELOB('||COLUMN_NAME||')STOREAS(TABLESPACE USER1;'
---------------------------------------------------------------------------------------------
alter table  USER1.TABLE1 move lob (DATA) store as (tablespace USER1);
alter table  USER1.TABLE2 move lob (DATA) store as (tablespace USER1);
alter table  USER1.TABLE3 move lob (DATA) store as (tablespace USER1);

move lobs into new tablespace

SQL>alter table  USER1.TABLE1 move lob (DATA) store as (tablespace USER1);
Table altered.

SQL>alter table  USER1.TABLE2 move lob (DATA) store as (tablespace USER1);
Table altered.

SQL>alter table  USER1.TABLE3 move lob (DATA) store as (tablespace USER1);
Table altered.

check lobs moved into new tablespace

SQL> select owner, segment_name, segment_type, tablespace_name , bytes from dba_segments where owner='USER1';

OWNER    SEGMENT_NAME                SEGMENT_TYPE       TABLESPACE_NAME    BYTES
-------- --------------------------- ------------------ ----------------- ----------
USER1    SYS_IL0000057639C00010$$    LOBINDEX           USER1	       65536
USER1    SYS_LOB0000057639C00010$$   LOBSEGMENT         USER1	    12713984
USER1    SYS_IL0000057685C00008$$    LOBINDEX           USER1	       65536
USER1    SYS_LOB0000057685C00008$$   LOBSEGMENT         USER1	     2228224
USER1    SYS_IL0000057693C00008$$    LOBINDEX           USER1	       65536
USER1    SYS_LOB0000057693C00008$$   LOBSEGMENT         USER1	      131072

6 rows selected.

rebuild UNUSABLE indxes

SQL>  select 'alter index '||owner||'.'||INDEX_NAME||' rebuild;' from dba_indexes where owner='USER1' and STATUS='UNUSABLE';

'ALTERINDEX'||OWNER||'.'||INDEX_NAME||'REBUILD;'
------------------------------------------------------
alter index USER1.idx_customers rebuild;
alter index USER1.idx_prods rebuild;
alter index USER1.idx_orders rebuild;
....
..
.

SQL>alter index USER1.idx_customers rebuild;
Index altered.

SQL>alter index USER1.idx_prods rebuild;
Index altered.

SQL>alter index USER1.idx_orders rebuild;
Index altered.

Please note LOBINDEX will be automatically relocated onto the same new tablespace as LOBSEGMENT, so there is no need to run SQL to relocate LOBINDEX . Otherwise the error will occur.

Autonomous Health Framework (AHF ) 20.4.4.0.0 and vulnerability CVE-2021-45105/CVE-2021-44228/CVE-2021-45046  

A client’s ExaCC platform installed with Autonomous Health Framework (AHF ) 20.4.4.0.0, which uses /opt/oracle.ahf/common/jlib/log4j-core-2.13.3.jar.

So to address vulnerability CVE-2021-45105/CVE-2021-44228/CVE-2021-45046. AHF should be upgraded to 21.4 or later versions.

To download the latest AHF here Autonomous Health Framework (AHF) – Including TFA and ORAchk/EXAchk (Doc ID 2550798.1)

This version 21.4 of AHF uses Log4J 2.17, fixing the following Log4J CVEs:

  1. CVE-2021-44228
  2. CVE-2021-45046
  3. CVE-2021-45105

High CPU and Memory Usage by Autonomous Health Framework (AHF)

A client ExaCC platform experiences both CPU and memory resource issues. After detailed investigations, we found the Autonomous Health Framework (AHF) processes consume huge CPU and memory resources. The followings are the diagnoses and solutions.

INVESTIGATIONS and SOLUTIONS

Subscribe to get access

Read more of this content when you subscribe today.

The Cluster Health Advisor (CHA) detected an unexpected increase in CPU utilization by databases or applications on this node. Identify CPU intensive processes and databases by reviewing Cluster Health Monitoring (CHM) data. Relocate databases to less busy machines, or limit the number of connections to databases on this node. Add nodes if more resources are required

A client keeps receiving the following alerts from OEM:

Host=racnode1.ractest
Target type=Cluster 
Target name=RACTEST-CLUSTER
Incident creation time=15/12/2021 2:11:20 PM AEDT 
Last updated time=15/12/2021 2:11:20 PM AEDT 
Message=Host CPU Utilization on Host racnode1 Database/Cluster RACTEST-CLUSTER Instance . The Cluster Health Advisor (CHA) detected an unexpected increase in CPU utilization by databases or applications on this node. Identify CPU intensive processes and databases by reviewing Cluster Health Monitoring (CHM) data. Relocate databases to less busy machines, or limit the number of connections to databases on this node. Add nodes if more resources are required. 
Severity=Warning 
Incident ID=501920 
Event count=1 
Incident Status=New 
Escalated=No 
Priority=High 
Incident owner=SYSMAN
Incident Acknowledged By Owner=No 
Categories= 
Rule Name=RuleSet - SYSMAN,Email SYSMAN About the Incidents 
Rule Owner=SYSMAN
...
..
.

Now let’s look into further where the alerts come from, the logs, GI processes and related metrics configurations on OEM.

Subscribe to get access

Read more of this content when you subscribe today.