Oracle asm information


ASM information :


 kfod disks=all


-- space  used by each database in asm 

set lines 200
column DATABASE format a25
col "GB" format 99,999.9
set pagesize 90

SELECT disk_group_name,
       SUBSTR(alias_path, 2, INSTR(alias_path, '/', 1, 2) - 2) DATABASE,
       ROUND(SUM(alloc_bytes)/1024/1024/1024, 1) "GB",
       ROUND(SUM(alloc_bytes)/1024/1024, 1) "MB"
FROM (
  SELECT SYS_CONNECT_BY_PATH(alias_name, '/') alias_path,
         alloc_bytes,
         disk_group_name
  FROM (
    SELECT g.name disk_group_name,
           a.parent_index pindex,
           a.name alias_name,
           a.reference_index rindex,
           f.space alloc_bytes,
           f.type type
    FROM v$asm_file f
    RIGHT OUTER JOIN v$asm_alias a USING (group_number, file_number)
    JOIN v$asm_diskgroup g USING (group_number)
  )
  WHERE type IS NOT NULL
  START WITH (MOD(pindex, POWER(2, 24)) = 0)
  CONNECT BY PRIOR rindex = pindex
)
WHERE disk_group_name = 'RECOC2'
GROUP BY disk_group_name, SUBSTR(alias_path, 2, INSTR(alias_path, '/', 1, 2) - 2)
ORDER BY 3;




 SPOOL ASM_FIRST.HTML
 SET MARKUP HTML ON
 set echo on
 set pagesize 200
 alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
 select 'THIS ASM REPORT WAS GENERATED AT: ==)>; ' , sysdate " " from dual;
 select 'HOSTNAME ASSOCIATED WITH THIS ASM INSTANCE: ==)> ' , MACHINE " " from v$session where program like '%SMON%';
 select * from v$asm_diskgroup;
 SELECT INST_ID||' '||OPERATION||' '||STATE||' '||POWER||' '||ACTUAL||' '||SOFAR FROM GV$ASM_OPERATION;
 select name, state, type, total_mb,   required_mirror_free_mb req_free, usable_file_mb use_mb from v$asm_diskgroup  ;
 SELECT * FROM V$ASM_DISK ORDER BY GROUP_NUMBER,DISK_NUMBER;
 SELECT * FROM V$ASM_CLIENT;
 select * from V$ASM_ATTRIBUTE;
  SELECT * FROM V$ASM_DISKGROUP_STAT ORDER BY GROUP_NUMBER;
  SELECT * FROM V$ASM_FILE ORDER BY GROUP_NUMBER,FILE_NUMBER;
 SELECT * FROM V$ASM_ALIAS ORDER BY GROUP_NUMBER,FILE_NUMBER;
 select * from v$asm_operation;select * from gv$asm_operation;
  SELECT * FROM V$ASM_TEMPLATE ORDER BY GROUP_NUMBER,ENTRY_NUMBER;
 select * from v$version;
 show parameter asm
 show parameter cluster
 show parameter instance_type
 show parameter instance_name
 show parameter spfile
 show parameter  sga
 spool off
 exit


 
 

ASMCMD> lsdg
-- Include dismounted diskgroups:
ASMCMD> lsdg --discovery

-- List diskgroups across all nodes of cluster:
ASMCMD> lsdg -g --discover


-- List all asm disks
ASMCMD> lsdsk -k 

-- List disks of a diskgroup(CDATA) with free and total MB
ASMCMD> lsdsk -k -G CDATA

-- List disks of a diskgroup(CDATA) with group and disk number
ASMCMD> lsdsk -p -G CDATA

-- List disks with disk creation date 
ASMCMD> lsdsk -t -G CDATA

-- List candidiate disks only
ASMCMD>  lsdsk --candidate -k

-- List member disks only
ASMCMD>  lsdsk --candidate -p


-- List attribute of all diskgroups:
ASMCMD> lsattr -lm 

-- List attribute of specific diskgroup(DMARCH)
ASMCMD> lsattr -lm -G  DMARCH


-- List attributes with specific pattern
ASMCMD> lsattr -lm %au_size%


-- here asm_power_limit is 8 and diskgroup is ARCH
ASMCMD> rebal --power 8 ARCH

-- Monitor progress
ASMCMD> lsop




Cell failure checks :

SQL>SET SERVEROUTPUT ON
SQL>SET LINES 155
SQL>SET PAGES 0
SQL>SET TRIMSPOOL ON

ASM capacity script:

********************************************************************************************************************

DECLARE
   v_num_disks    NUMBER;
   v_group_number   NUMBER;
   v_max_total_mb   NUMBER;

   v_required_free_mb   NUMBER;
   v_usable_mb      NUMBER;
   v_cell_usable_mb   NUMBER;
   v_one_cell_usable_mb   NUMBER;
   v_enuf_free      BOOLEAN := FALSE;
   v_enuf_free_cell   BOOLEAN := FALSE;

   v_req_mirror_free_adj_factor   NUMBER := 1.10;
   v_req_mirror_free_adj         NUMBER := 0;
   v_one_cell_req_mir_free_mb     NUMBER  := 0;

   v_disk_desc      VARCHAR(10) := 'SINGLE';
   v_offset      NUMBER := 50;

   v_db_version   VARCHAR2(8);
   v_inst_name    VARCHAR2(1);

   v_cfc_fail_msg VARCHAR2(500);

BEGIN

   SELECT substr(version,1,8), substr(instance_name,1,1)    INTO v_db_version, v_inst_name    FROM v$instance;

   IF v_inst_name <> '+' THEN
      DBMS_OUTPUT.PUT_LINE('ERROR: THIS IS NOT AN ASM INSTANCE!  PLEASE LOG ON TO AN ASM INSTANCE AND RE-RUN THIS SCRIPT.');
      GOTO the_end;
   END IF;

    DBMS_OUTPUT.PUT_LINE('------ DISK and CELL Failure Diskgroup Space Reserve Requirements  ------');
    DBMS_OUTPUT.PUT_LINE(' This procedure determines how much space you need to survive a DISK or CELL failure. It also shows the usable space ');
    DBMS_OUTPUT.PUT_LINE(' available when reserving space for disk or cell failure.  ');
   DBMS_OUTPUT.PUT_LINE(' Please see MOS note 1551288.1 for more information.  ');
   DBMS_OUTPUT.PUT_LINE('.  .  .');
    DBMS_OUTPUT.PUT_LINE(' Description of Derived Values:');
    DBMS_OUTPUT.PUT_LINE(' One Cell Required Mirror Free MB : Required Mirror Free MB to permit successful rebalance after losing largest CELL regardless of redundancy type');
    DBMS_OUTPUT.PUT_LINE(' Disk Required Mirror Free MB     : Space needed to rebalance after loss of single or double disk failure (for normal or high redundancy)');
    DBMS_OUTPUT.PUT_LINE(' Disk Usable File MB              : Usable space available after reserving space for disk failure and accounting for mirroring');
    DBMS_OUTPUT.PUT_LINE(' Cell Usable File MB              : Usable space available after reserving space for SINGLE cell failure and accounting for mirroring');
   DBMS_OUTPUT.PUT_LINE('.  .  .');

   IF (v_db_version = '11.2.0.3') OR (v_db_version = '11.2.0.4') OR (v_db_version = '12.1.0.1') OR (v_db_version = '12.1.0.2') THEN
      v_req_mirror_free_adj_factor := 1.10;
      DBMS_OUTPUT.PUT_LINE('ASM Version: '||v_db_version);
   ELSE
      v_req_mirror_free_adj_factor := 1.5;
      DBMS_OUTPUT.PUT_LINE('ASM Version: '||v_db_version||'  - WARNING DISK FAILURE COVERAGE ESTIMATES HAVE NOT BEEN VERIFIED ON THIS VERSION!');
   END IF;

   DBMS_OUTPUT.PUT_LINE('.  .  .');
-- Set up headings
     DBMS_OUTPUT.PUT_LINE('----------------------------------------------------------------------------------------------------------------------------------------------------');
      DBMS_OUTPUT.PUT('|          ');
      DBMS_OUTPUT.PUT('|         ');
      DBMS_OUTPUT.PUT('|     ');
      DBMS_OUTPUT.PUT('|          ');
      DBMS_OUTPUT.PUT('|            ');
      DBMS_OUTPUT.PUT('|            ');
      DBMS_OUTPUT.PUT('|            ');
      DBMS_OUTPUT.PUT('|Cell Req''d  ');
      DBMS_OUTPUT.PUT('|Disk Req''d  ');
      DBMS_OUTPUT.PUT('|            ');
      DBMS_OUTPUT.PUT('|            ');
      DBMS_OUTPUT.PUT('|    ');
      DBMS_OUTPUT.PUT('|    ');
      DBMS_OUTPUT.PUT('|       ');
      DBMS_OUTPUT.PUT_Line('|');
      DBMS_OUTPUT.PUT('|          ');
      DBMS_OUTPUT.PUT('|DG       ');
      DBMS_OUTPUT.PUT('|Num  ');
      DBMS_OUTPUT.PUT('|Disk Size ');
      DBMS_OUTPUT.PUT('|DG Total    ');
      DBMS_OUTPUT.PUT('|DG Used     ');
      DBMS_OUTPUT.PUT('|DG Free     ');
      DBMS_OUTPUT.PUT('|Mirror Free ');
      DBMS_OUTPUT.PUT('|Mirror Free ');
      DBMS_OUTPUT.PUT('|Disk Usable ');
      DBMS_OUTPUT.PUT('|Cell Usable ');
      DBMS_OUTPUT.PUT('|    ');
      DBMS_OUTPUT.PUT('|    ');
      DBMS_OUTPUT.PUT_LINE('|PCT    |');
      DBMS_OUTPUT.PUT('|DG Name   ');
      DBMS_OUTPUT.PUT('|Type     ');
      DBMS_OUTPUT.PUT('|Disks');
      DBMS_OUTPUT.PUT('|MB        ');
      DBMS_OUTPUT.PUT('|MB          ');
      DBMS_OUTPUT.PUT('|MB          ');
      DBMS_OUTPUT.PUT('|MB          ');
      DBMS_OUTPUT.PUT('|MB          ');
      DBMS_OUTPUT.PUT('|MB          ');
      DBMS_OUTPUT.PUT('|File MB     ');
      DBMS_OUTPUT.PUT('|File MB     ');
      DBMS_OUTPUT.PUT('|DFC ');
      DBMS_OUTPUT.PUT('|CFC ');
      DBMS_OUTPUT.PUT_LINE('|Util   |');
     DBMS_OUTPUT.PUT_LINE('----------------------------------------------------------------------------------------------------------------------------------------------------');

   FOR dg IN (SELECT name, type, group_number, total_mb, free_mb, required_mirror_free_mb FROM v$asm_diskgroup ORDER BY name) LOOP

      v_enuf_free := FALSE;

     v_req_mirror_free_adj := dg.required_mirror_free_mb * v_req_mirror_free_adj_factor;

      -- Find largest amount of space allocated to a cell
      SELECT sum(disk_cnt), max(max_total_mb), max(sum_total_mb)*v_req_mirror_free_adj_factor
     INTO v_num_disks, v_max_total_mb, v_one_cell_req_mir_free_mb
      FROM (SELECT count(1) disk_cnt, max(total_mb) max_total_mb, sum(total_mb) sum_total_mb
      FROM v$asm_disk
     WHERE group_number = dg.group_number
     GROUP BY failgroup);

      -- Eighth Rack
      IF dg.type = 'NORMAL' THEN

         -- Eighth Rack
         IF (v_num_disks < 36) THEN
            -- Use eqn: y = 1.21344 x+ 17429.8
            v_required_free_mb :=  1.21344 * v_max_total_mb + 17429.8;
            IF dg.free_mb > v_required_free_mb THEN v_enuf_free := TRUE; END IF;
         -- Quarter Rack
         ELSIF (v_num_disks >= 36 AND v_num_disks < 84) THEN
            -- Use eqn: y = 1.07687 x+ 19699.3
                        -- Revised 2/21/14 for 11.2.0.4 to use eqn: y=0.803199x + 156867, more space but safer
            v_required_free_mb := 0.803199 * v_max_total_mb + 156867;
            IF dg.free_mb > v_required_free_mb THEN v_enuf_free := TRUE; END IF;
         -- Half Rack
         ELSIF (v_num_disks >= 84 AND v_num_disks < 168) THEN
            -- Use eqn: y = 1.02475 x+53731.3
            v_required_free_mb := 1.02475 * v_max_total_mb + 53731.3;
            IF dg.free_mb > v_required_free_mb THEN v_enuf_free := TRUE; END IF;
         -- Full rack is most conservative, it will be default
         ELSE
            -- Use eqn: y = 1.33333 x+83220.
            v_required_free_mb := 1.33333 * v_max_total_mb + 83220;
            IF dg.free_mb > v_required_free_mb THEN v_enuf_free := TRUE; END IF;

         END IF;

         -- DISK usable file MB
         v_usable_mb := ROUND((dg.free_mb - v_required_free_mb)/2);
         v_disk_desc := 'ONE disk';

         -- CELL usable file MB
         v_cell_usable_mb := ROUND( (dg.free_mb - v_one_cell_req_mir_free_mb)/2 );
         v_one_cell_usable_mb := v_cell_usable_mb;

      ELSE
         -- HIGH redundancy

         -- Eighth Rack
         IF (v_num_disks <= 18) THEN
            -- Use eqn: y = 4x + 0
                        -- Updated for 11.2.0.4 to higher value:  y = 3.84213x + 84466.4
            v_required_free_mb :=  3.84213 * v_max_total_mb + 84466.4;
            IF dg.free_mb > v_required_free_mb THEN v_enuf_free := TRUE; END IF;
         -- Quarter Rack
         ELSIF (v_num_disks > 18 AND v_num_disks <= 36) THEN
            -- Use eqn: y = 3.87356 x+417692.
            v_required_free_mb := 3.87356 * v_max_total_mb + 417692;
            IF dg.free_mb > v_required_free_mb THEN v_enuf_free := TRUE; END IF;
         -- Half Rack
         ELSIF (v_num_disks > 36 AND v_num_disks <= 84) THEN
            -- Use eqn: y = 2.02222 x+56441.6
            v_required_free_mb := 2.02222 * v_max_total_mb + 56441.6;
            IF dg.free_mb > v_required_free_mb THEN v_enuf_free := TRUE; END IF;
         -- Full rack is most conservative, it will be default
         ELSE
            -- Use eqn: y = 2.14077 x+54276.4
            v_required_free_mb := 2.14077 * v_max_total_mb + 54276.4;
            IF dg.free_mb > v_required_free_mb THEN v_enuf_free := TRUE; END IF;

         END IF;

         -- DISK usable file MB
         v_usable_mb := ROUND((dg.free_mb - v_required_free_mb)/3);
         v_disk_desc := 'TWO disks';

         -- CELL usable file MB
         v_one_cell_usable_mb := ROUND( (dg.free_mb - v_one_cell_req_mir_free_mb)/3 );

      END IF;
      DBMS_OUTPUT.PUT('|'||RPAD(dg.name,v_offset-40));
      DBMS_OUTPUT.PUT('|'||RPAD(nvl(dg.type,'  '),v_offset-41));
      DBMS_OUTPUT.PUT('|'||LPAD(TO_CHAR(v_num_disks),v_offset-45));
      DBMS_OUTPUT.PUT('|'||TO_CHAR(v_max_total_mb,'9,999,999'));
      DBMS_OUTPUT.PUT('|'||TO_CHAR(dg.total_mb,'999,999,999'));
      DBMS_OUTPUT.PUT('|'||TO_CHAR(dg.total_mb - dg.free_mb,'999,999,999'));
      DBMS_OUTPUT.PUT('|'||TO_CHAR(dg.free_mb,'999,999,999'));
      DBMS_OUTPUT.PUT('|'||TO_CHAR(ROUND(v_one_cell_req_mir_free_mb),'999,999,999'));
      DBMS_OUTPUT.PUT('|'||TO_CHAR(ROUND(v_required_free_mb),'999,999,999'));
      DBMS_OUTPUT.PUT('|'||TO_CHAR(ROUND(v_usable_mb),'999,999,999'));
      DBMS_OUTPUT.PUT('|'||TO_CHAR(ROUND(v_one_cell_usable_mb),'999,999,999')); 

      IF v_enuf_free THEN
         DBMS_OUTPUT.PUT('|'||'PASS');
      ELSE
         DBMS_OUTPUT.PUT('|'||'FAIL');
      END IF;

     IF dg.type = 'NORMAL' THEN
        -- Calc Free Space for Rebalance Due to Cell Failure
        IF v_one_cell_req_mir_free_mb < dg.free_mb THEN
           DBMS_OUTPUT.PUT('|'||'PASS');
        ELSE
            DBMS_OUTPUT.PUT('|'||'FAIL');
            v_cfc_fail_msg := 'WARNING: Not enough free space to rebalance after loss of ONE cell (however, cell failure is very rare)';
        END IF;
     ELSE
        -- Calc Free Space for Rebalance Due to Single Cell Failure
        IF v_one_cell_req_mir_free_mb < dg.free_mb THEN
           DBMS_OUTPUT.PUT('|'||'PASS');
        ELSE
           DBMS_OUTPUT.PUT('|'||'FAIL');
           v_cfc_fail_msg := 'WARNING: Not enough free space to rebalance after loss of ONE cell(However, cell failure is very rare and high redundancy offers ample protection already)';
        END IF;

     END IF;

     -- Calc Disk Utilization Percentage
        IF dg.total_mb > 0 THEN
           DBMS_OUTPUT.PUT_LINE('|'||TO_CHAR((((dg.total_mb - dg.free_mb)/dg.total_mb)*100),'999.9')||CHR(37)||'|');
        ELSE
           DBMS_OUTPUT.PUT_LINE('|       |');
        END IF;

   END LOOP;

     DBMS_OUTPUT.PUT_LINE('----------------------------------------------------------------------------------------------------------------------------------------------------');
<<the_end>>

   IF v_cfc_fail_msg is not null THEN
      DBMS_OUTPUT.PUT_LINE('Cell Failure Coverage Freespace Failures Detected. Warning Message Follows.');
      DBMS_OUTPUT.PUT_LINE(v_cfc_fail_msg);
   END IF;

   DBMS_OUTPUT.PUT_LINE('.  .  .');
   DBMS_OUTPUT.PUT_LINE('Script completed.');

END

********************************************************************************************************************************************















How To Gather & Backup ASM/ACFS Metadata In A Formatted Manner version 10.1, 10.2, 11.1, 11.2, 12.1, 12.2, 18.x and 19.x (Doc ID 470211.1)


-- ASM VERSIONS 10.1, 10.2, 11.1, 11.2, 12.1 & 12.2
SET MARKUP HTML ON
SET ECHO ON
SET PAGESIZE 200

SPOOL ASM<#>_GENERIC_ASM_METADATA.html

ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';

SELECT 'THIS ASM REPORT WAS GENERATED AT: ==)> ' , SYSDATE " " FROM DUAL;
SELECT 'INSTANCE NAME: ==)> ' , INSTANCE_NAME " " FROM V$INSTANCE;

SELECT 'HOSTNAME ASSOCIATED WITH THIS ASM INSTANCE: ==)> ' , MACHINE " " FROM V$SESSION WHERE PROGRAM LIKE '%SMON%';

SELECT * FROM V$INSTANCE;

SELECT * FROM GV$INSTANCE;

SELECT * FROM V$ASM_DISKGROUP;

SELECT GROUP_NUMBER, DISK_NUMBER, MOUNT_STATUS, HEADER_STATUS, MODE_STATUS, STATE, OS_MB, TOTAL_MB, FREE_MB, NAME, FAILGROUP, PATH
FROM V$ASM_DISK ORDER BY GROUP_NUMBER, FAILGROUP, DISK_NUMBER;

SELECT * FROM V$ASM_DISK ORDER BY GROUP_NUMBER,DISK_NUMBER;

SELECT SUBSTR(D.NAME,1,16) AS ASMDISK, D.MOUNT_STATUS, D.STATE,
DG.NAME AS DISKGROUP FROM V$ASM_DISKGROUP DG, V$ASM_DISK D
WHERE DG.GROUP_NUMBER = D.GROUP_NUMBER;

SELECT * FROM V$ASM_CLIENT;

SELECT DG.NAME AS DISKGROUP, SUBSTR(C.INSTANCE_NAME,1,12) AS INSTANCE,
SUBSTR(C.DB_NAME,1,12) AS DBNAME, SUBSTR(C.SOFTWARE_VERSION,1,12) AS SOFTWARE,
SUBSTR(C.COMPATIBLE_VERSION,1,12) AS COMPATIBLE
FROM V$ASM_DISKGROUP DG, V$ASM_CLIENT C
WHERE DG.GROUP_NUMBER = C.GROUP_NUMBER;

SELECT * FROM V$ASM_ATTRIBUTE;

SELECT * FROM V$ASM_OPERATION;
SELECT * FROM GV$ASM_OPERATION;

SELECT * FROM V$VERSION;

SELECT * FROM V$ASM_ACFSSNAPSHOTS;
SELECT * FROM V$ASM_ACFSVOLUMES;
SELECT * FROM V$ASM_FILESYSTEM;
SELECT * FROM V$ASM_VOLUME;
SELECT * FROM V$ASM_VOLUME_STAT;

SELECT * FROM V$ASM_USER;
SELECT * FROM V$ASM_USERGROUP;
SELECT * FROM V$ASM_USERGROUP_MEMBER;

SELECT * FROM V$ASM_DISK_IOSTAT;
SELECT * FROM V$ASM_DISK_STAT;
SELECT * FROM V$ASM_DISKGROUP_STAT;

SELECT * FROM V$ASM_TEMPLATE;

SHOW PARAMETER

SHOW SGA

!echo "SELECT '" > /tmp/GPNPTOOL.SQL 2> /dev/null
! $ORACLE_HOME/bin/gpnptool get >> /tmp/GPNPTOOL.SQL 2>> /dev/null
!echo "' FROM DUAL;" >> /tmp/GPNPTOOL.SQL 2>> /dev/null
! cat /tmp/GPNPTOOL.SQL
SET ECHO OFF

--DISPLAYS INFORMATION ABOUT THE CONTENTS OF THE SPFILE.
SELECT * FROM V$SPPARAMETER ORDER BY 2;
SELECT * FROM GV$SPPARAMETER ORDER BY 3;

--DISPLAYS INFORMATION ABOUT THE INITIALIZATION PARAMETERS THAT ARE CURRENTLY IN EFFECT IN THE INSTANCE.
SELECT * FROM V$SYSTEM_PARAMETER ORDER BY 2;
SELECT * FROM GV$SYSTEM_PARAMETER ORDER BY 3;

-- 12C ACFS VIEWS

SELECT * FROM V$ASM_ACFS_ENCRYPTION_INFO;
SELECT * FROM V$ASM_ACFSREPL;
SELECT * FROM V$ASM_ACFSREPLTAG;
SELECT * FROM V$ASM_ACFS_SEC_ADMIN;
SELECT * FROM V$ASM_ACFS_SEC_CMDRULE;
SELECT * FROM V$ASM_ACFS_SEC_REALM;
SELECT * FROM V$ASM_ACFS_SEC_REALM_FILTER;
SELECT * FROM V$ASM_ACFS_SEC_REALM_GROUP;
SELECT * FROM V$ASM_ACFS_SEC_REALM_USER;
SELECT * FROM V$ASM_ACFS_SEC_RULE;
SELECT * FROM V$ASM_ACFS_SEC_RULESET;
SELECT * FROM V$ASM_ACFS_SEC_RULESET_RULE;
SELECT * FROM V$ASM_ACFS_SECURITY_INFO;
SELECT * FROM V$ASM_ACFSTAG;

-- 12C ASM AUDIT VIEWS

SELECT * FROM V$ASM_AUDIT_CLEAN_EVENTS;
SELECT * FROM V$ASM_AUDIT_CLEANUP_JOBS;
SELECT * FROM V$ASM_AUDIT_CONFIG_PARAMS;
SELECT * FROM V$ASM_AUDIT_LAST_ARCH_TS;

-- 12C ASM ESTIMATE VIEW

SELECT * FROM V$ASM_ESTIMATE;
SELECT * FROM GV$ASM_ESTIMATE;

-- SPARSE Diskgroups VIEW

SELECT * FROM V$ASM_DISK_SPARSE;
SELECT * FROM V$ASM_DISKGROUP_SPARSE;

-- FLEX Diskgroup Info.

set lines 300
SELECT * FROM V$ASM_FILEGROUP;
SELECT FILEGROUP_NUMBER, file_type, NAME, VALUE FROM V$ASM_FILEGROUP_PROPERTY;

SPOOL OFF

EXIT








Comments

Post a Comment

Popular posts from this blog

How To Purge Optimizer Statistics Advisor Old Records From 12.2 Onwards (Doc ID 2660128.1)

  In this Document Goal Solution References APPLIES TO: Oracle Database - Enterprise Edition - Version 12.2.0.1 and later Oracle Cloud Infrastructure - Database Service - Version N/A and later Information in this document applies to any platform. GOAL Goal of this document is to provide the method to purge the old records of Optimizer Statistics Advisor Task namely AUTO_STATS_ADVISOR_TASK that consumes huge SYSAUX space. Huge no.of old records retained in WRI$_ADV_OBJECTS for AUTO_STATS_ADVISOR_TASK or INDIVIDUAL_STATS_ADVISOR_TASK incurs heavy SYSAUX space. AUTO_STATS_ADVISOR_TASK is meant for Automatic Statistics Advisor task while INDIVIDUAL_STATS_ADVISOR_TASK is for Manual Statistics Advisor task.  DBA_ADVISOR_PARAMETERS displays all advisor task parameters and their current values in the database. There is a parameter name called EXECUTION_DAYS_TO_EXPIRE. This parameter is set in no.of days. Executions older than the value(no.of days) set would be purged automaticall...
Read more

Oracle session snapper

-- use dbms_session.sleep instead of dbms_lock on oracle 18c or higher -- last column  is sid  -- scipt_name   stat_to_collect   secods_of_snap  how_many_snap   sessions_to_snap -- @snapper all 100 1 lgdr@* -- @snapper all 100 1 sqlid@* -- @snapper all 190 1 all -- @snapper all 190 1 584 -- @snapper all 30 3 584 -- @snapper all 5 1 869 -- @snapper all 30 1 584 -- @snapper ash 5 1 144 -- @snapper ash=sql_id+event+wait_class+blocking_session+p2+p3 5 1 144 -- @snapper ash 5 1 156 -- @snapper ash 190  1 all -- @snapper ash 5 1 all -- @snapper ash=sid+event+wait_class 5 1 all -- @snapper ash=sid+event+wait_class,ash1=plsql_object_id+plsql_subprogram_id+sql_id 5 1 all -- @snapper ash=sid+event+wait_class,ash1=plsql_object_id+plsql_subprogram_id+sql_id,ash2=program+module+action 5 1 all -- @snapper ash=event+wait_class,stats,gather=ts,tinclude=CPU,sinclude=parse 5 1 all -- @snapper ash=sid+event+wait_class,ash1=sid+sqlid+module,stats,gather...
Read more

Oracle Materialized View In-Depth and Materialized View refresh issues in 19c

   We    had  situation demanding  tuning of  MV  refresh and  options  like  TUNE_MVIEW  and Atomic_Refresh was explored . Hence  Documenting materialized view sample commands  and views in this  blog .     After migration if we are  facing slowness  in  Materialized View refresh , we can explore to re-create  Materialized View   Since  main topic for this blog   was MV issues  in 19c ,  posting that topic   first in line below  We also   have Real-Time Materialized Views  to explore .  To Note :  1) We can  create Mv  on ON PREBUILT TABLE  2) We can create Index on Mv  3) We can  gather stats on mv  just like other table .  4) Mv can be REFRESH_MODE ON COMMIT / REFRESH ON DEMAND. 5) Mv  REFRESH_METHOD can be  Complete / FAST or FORCE . A FORCE attempts a FAST...
Read more