Author: dbalifeeasy

Check Last Refresh Date and Time of Oracle Materialized Views

SQL>SELECT
           OWNER,
           MVIEW_NAME,
           REFRESH_MODE,
           REFRESH_METHOD,
           TO_CHAR(LAST_REFRESH_END_TIME,'YYYYMMDD-HH24:MI:SS'),
           STALENESS 
    FROM
           DBA_MVIEWS;

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.

Subscribe

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.

Subscribe