WARNING: Heavy swapping observed on system in last 5 mins.

WARNING: Heavy swapping observed on system in last 5 mins

In alert log. getting the following WARNING: Heavy swapping observed on system in last 5 mins.

Error
WARNING: Heavy swapping observed on system in last 5 mins.
Heavy swapping can lead to timeouts, poor performance, and instance eviction.
Errors in file E:\ORACLE\diag\rdbms\ic\ic\trace\ic_dbrm_5436.trc (incident=24027):
ORA-00700: soft internal error, arguments: [kskvmstatact: excessive swapping observed], [], [], [], [], [], [], [], [], [], [], []
Incident details in: E:\ORACLE\diag\rdbms\ic\ic\incident\incdir_24027\ic_dbrm_5436_i24027.trc

Cause
Until Oracle 11.2.0.3 version doesn’t report any swap warning in the alert.log.
For 11.2.0.3 and 11.2.0.4, a warning for swapping is written to the alert.log by the fix of unpublished Bug 10220118 – NEED TO ALERT WHEN SYSTEM IS CLOSE TO PAGING.

With 12.1.0.X, Oracle writes the warning and adds more diagnostic information with the ORA-700 error
If the total swap in + swap out percentage is over a certain threshold (2%).
Warning is generated in alert log when Oracle sees that the amount of memory swapped is greater than 2% of the physical memory in the past 5 minutes.

Solution
1. Solutions are to reduce the amount of SGA memory by reducing the memory_target or sga_target and pga_aggregate_target parameters. If it exceed from Physical memory of your Server.

Oracle allocate Memory = SGA_TARGET + PGA_AGGREGATE_TARGET

2. In Oracle the alert log message warning and ORA-700 are expected behaviour for 12c, no action is required.
For remove the message from alert log. you can apply the fix 23104033:
18.1.0
12.2.0.1.190115 (Jan 2019) Database Jan 2019 Release Update (DB RU)
12.2.0.1.180531 (May 2018) Bundle Patch for Windows Platforms

Advertisements

ORA-04030: out of process memory

ORA-04030: out of process memory

Error in Alert Log
Sun Nov 25 02:14:17 2018
Unable to allocate memory for new incident error in file E:\ORACLE12C\diag\rdbms\ic\ic\trace\ic_m001_6448.trc:
ORA-04030: out of process memory when trying to allocate 20520 bytes (pga heap,KTI PGA static small pool)

Solution
Following are the steps to check for solution:

1. Check the size of Physical Memory in the System.

2. Check the following parameters allocated in the Oracle.

Show parameter MEMORY_TARGET

show parameter SGA_TARGET

show parameter PGA

Note: If Memory target value is set then sga_target & pga_aggregate_target is 0 if not then it is the minimum value of memory allocated at time of startup.

3. If you have enough physical memory then you can increase the MEMORY_TARGET parameter value to sufficient value.
Because memory target will manage the PGA and SGA both. If Memory target is not in use then increase the PGA value separately will help.

Estimate the PGA value with help of Maximum session and process running

1. Check the Maximum utilization column of table for getting maximum session made from last startup.

select resource_name, current_utilization, max_utilization, limit_value
from v$resource_limit
where resource_name in ('sessions', 'processes');

--Check history of maximum value for session utilized
select resource_name,max(max_utilization) from DBA_HIST_RESOURCE_LIMIT
where resource_name in ('sessions','processes') group by resource_name;

2. Use maximum utilization session value in below query to find the optimal value for PGA.

SELECT MAx_utilization_session*(2048576+P1.VALUE+P2.VALUE)/(1024*1024) YOU_NEED_PGA_MB
FROM V$PARAMETER P1, V$PARAMETER P2
WHERE P1.NAME = 'sort_area_size'
AND P2.NAME = 'hash_area_size';

3. Change the PGA Aggregate parameter meter according to that

ALTER SYSTEM SET pga_aggregate_target = 3500M SCOPE=BOTH;

Note: its good to take 3 value as set in pga_Aggregate_target
alter system set pga_aggregate_limit=pga_aggregate_target*3 Scope=both;

Check the current PGA memory allocation

For memory allocation total by process:
SELECT ROUND(SUM(pga_used_mem)/(1024*1024),2) max,
ROUND(SUM(pga_alloc_mem)/(1024*1024),2) alloc,
ROUND(SUM(pga_used_mem)/(1024*1024),2) used,
ROUND(SUM(pga_freeable_mem)/(1024*1024),2) free
FROM V$PROCESS;

Cluster Health Advisor(ochad) in Oracle RAC Environment

Cluster Health Advisor(ochad) in RAC Oracle Environment

Oracle Cluster Health Advisor runs as on available cluster resource, ochad, on each node in the cluster.
Each Oracle Cluster Health Advisor daemon (ochad) monitors the operating system on the cluster node and each database instance on the RAC Nodes.

Fetch report from Cluster health check advisory

-- TEXT file
chactl query diagnosis -db oltpacdb -start "2019-02-01 02:00:00" -end "2019-02-01 03:00:00"

-- HTML
chactl query diagnosis -start "2016-07-03 20:50:00" -end "2016-07-04 03:50:00" -htmlfile ~/chaprob.html

Monitor the database
Note: CHACTL run with user Grid to turn on monitoring of the database.

chactl monitor database –db db_unique_name

Stop the monitoring of database

chactl unmonitor database –db db_unique_name

Check monitoring status of all cluster nodes and databases

chactl status -verbose

Retrieve the repository details

chactl query repository

specified max retention time(hrs) : 72
available retention time(hrs) : 212
available number of entities : 2
allocated number of entities : 0
total repository size(gb) : 2.00
allocated repository size(gb) : 0.07

Change the retention time in hours

chactl set maxretention -time number_of_hours
Example
chactl set maxretention -time 80
max retention successfully set to 80 hours

View the Status of Cluster Health Advisory Service on all nodes

srvctl status cha
Cluster Health Advisor is running on nodes racNode1, racNode2.
Cluster Health Advisor is not running on nodes racNode3, racNode4.

ASM Configuration Assistant(ASMCA) commands in Oracle

ASM Configuration Assistant ASMCA commands in Oracle

Check version of ASMCMD

-- Check ASM version
$asmcmd -V
asmcmd version 18.0.0.0.0

-- Check patch version
ASMCMD [+] > showversion --active
Oracle ASM active version on the cluster is [18.0.0.0.0]. The cluster upgrade state
is [NORMAL]. The cluster active patch level is [0].

--Check the attribute of data diskgroup
ASMCMD [+] > lsattr -lm -G data
-l display name with value
-m add additiona value

General ASMCMD commands

--List the directory
ASMCMD> ls
data/
fra/

--Make the directory
ASMCMD> mkdir +data/orcl/mydir

--Change directory
ASMCMD [+] > cd data/orcl
ASMCMD [+data/orcl] >

--Lists mounted disk groups and their information
ASMCMD [+] > lsdg data

-- List oracle ASM disk
ASMCMD [+] > lsdsk -t -G data

Configure and Manage the ASM instance with ASMCA

-- Create the ASM instance
asmca -silent
-configureASM
-diskString '/devices/disk*'
–sysAsmPassword my_sysasm_password
-asmsnmpPassword my_asmsnmp_passwd

-- configure the parameter of ASM instance
asmca -silent
-configureParameter
–param
asm_power_limit=3
–sysAsmPassword my_sysasm_password

--Delete the ASM instance
asmca -silent
-deleteASM
–sysAsmPassword my_sysasm_password
-force

List the operation going on disk
displays information from the V$ASM_OPERATION view

ASMCMD [+] > lsop
Group_Name Dsk_Num State Power
DATA REBAL WAIT 2

Manage the Diskgroup and disk with ASMCA

-- Create the diskgroup
asmca -silent -createDiskGroup
-diskGroupName mynewdg
-disk '/devices/diske*'
-disk '/devices/diskk*'
-redundancy NORMAL
-au_size 64
-compatible.asm '18.0.0.0.0'
-compatible.rdbms '18.0.0.0.0'
-compatible.advm '18.0.0.0.0

-- Add disk to diskGroup
asmca -silent
-addDisk
-diskGroupName mynewdg
-disk '/devices/diskl1'
-disk '/devices/diskl2'

-- Edit the diskgroup attribute
asmca -silent
-editDiskGroupAttributes
(-diskGroupName disk_group)
(-attribute value)

--Check ASM Disk Attribute
ASMCMD +> lsattr

--Set the ASM Attribute
ASMCMD +>setattr

-- Create the diskgroup
Creates a disk group based on an XML configuration file.
ASMCMD +> MKDG

--Drop the diskGroup
ASMCMD [+] > dropdg -r -f dat
-f force option
-r recursive like linux

-- For altering the disk group
ASMCMD +> chdg

--Rename the disk-group
renamedg dgname=fra1 newdgname=fra2 asm_diskstring='/devices/disk*' verbose=true

Handle file group or Quota group
ASMCA also supports commands to manage and track file and quota groups. These commands include:

-- create the filegroup
asmca -silent
-createFileGroups
(-diskGroupName disk_group)
(-fileGroupNames file_group_name_list)
(-fileGroupUsages file_group_usages_list)
(-fileGroupUsageIDs file_group_usagesID_list)

--Drop fileGroup
asmca -silent
-dropFileGroups
(-diskGroupName disk_group)
(-fileGroupNames file_group_name_list)

--Create quota
asmca -silent
-createQuotaGroups
(-diskGroupName disk_group)
(-quotaGroupNames quota_group_name_list)
(-quotaGroupSizes quota_group_size_list)

--Drop quota
asmca -silent
-dropQuotaGroups
(-diskGroupName disk_group)
(-quotaGroupNames quota_group_names_list)

--modify a file group or quota group
chfg and chqg

--list file groups or quota groups
lsfg and lsqg

--create a file group or quota group
mkfg and mkqg

-- Delete a file group or quota group
rmfg and rmqg

--to move a file group
mvfg

Managing the ADVM Volume

-- Create volume in DATA diskgroup
ASMCMD [+] > volcreate -G data -s 10G --width 1M --column 8 volume1

-- Check the list of volume
ASMCMD [+] > volinfo -G data volume1

-- List all volume in specific disk -a option
ASMCMD [+] > volinfo -G data -a

-- Delete the Volume
ASMCMD [+] > voldelete -G diskgroup volume

-- Disable the volume
ASMCMD [+] > voldisable -G diskgroup volume

-- Enable the volume
ASMCMD [+] > volenable -G diskgroup volume

--Resize operation on volume
ASMCMD [+] > volresize -G data -s 20G volume1

General Query for find ASM disk and diskgroup details

General Query for find ASM disk and diskgroup details

Note: All disks in the disk group must be of equal size.

Check rolling patch mode and patch level

SELECT SYS_CONTEXT('SYS_CLUSTER_PROPERTIES', 'CLUSTER_STATE') FROM DUAL;
SELECT SYS_CONTEXT('SYS_CLUSTER_PROPERTIES', 'CURRENT_PATCHLVL') FROM DUAL;

Check the disk group attributes

SELECT SUBSTR(dg.name,1,12) AS diskgroup, SUBSTR(a.name,1,24) AS name,
SUBSTR(a.value,1,24) AS value, read_only FROM V$ASM_DISKGROUP dg,
V$ASM_ATTRIBUTE a WHERE dg.name = 'DATA' AND dg.group_number = a.group_number
AND a.name NOT LIKE '%template%';

Check size of disk group

SELECT name, type, total_mb, free_mb, required_mirror_free_mb, usable_file_mb FROM V$ASM_DISKGROUP;

Check candidate disk present for add disk

SELECT name, header_status, path FROM V$ASM_DISK WHERE path LIKE '/devices/disk0%';

Check the compatibility of a disk group

SELECT name AS diskgroup, substr(compatibility,1,12) AS asm_compat,
substr(database_compatibility,1,12) AS db_compat FROM V$ASM_DISKGROUP;

DISKGROUP ASM_COMPAT DB_COMPAT
--------- ---------- ----------
DATA      18.0.0.0.0 18.0.0.0.0
DATA1     18.0.0.0.0 18.0.0.0.0

Check disks present in disk groups

SELECT SUBSTR(dg.name,1,16) AS diskgroup, SUBSTR(d.name,1,16) AS asmdisk,
d.mount_status, d.state, SUBSTR(d.failgroup,1,16) AS failgroup
FROM V$ASM_DISKGROUP dg, V$ASM_DISK d WHERE dg.group_number = d.group_number;

DISKGROUP ASMDISK MOUNT_S STATE  FAILGROUP
--------- ------- ------- ------ ---------
DATA DATA_01 CACHED NORMAL DATA_0008
DATA DATA_02 CACHED NORMAL DATA_0000
DATA DATA_03 CACHED NORMAL DATA_0004

Check stats of disks in disk groups

SELECT SUBSTR(dgs.name,1,10) AS diskgroup, SUBSTR(ds.name,1,10) AS asmdisk,
ds.mount_status, ds.state, ds.reads, ds.writes, ds.read_time, ds.write_time,
bytes_read, bytes_written
FROM V$ASM_DISKGROUP_STAT dgs, V$ASM_DISK_STAT ds
WHERE dgs.group_number = ds.group_number AND dgs.name = 'DATA';

Check disk group clients with 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;

Check Oracle ASM File Access Control

Check the Access Control from SQL command in Oracle ASM

Check Oracle ASM File Access Control information with V$ASM_USER

SELECT dg.name AS diskgroup, u.group_number, u.user_number, u.os_id, u.os_name
FROM V$ASM_DISKGROUP dg, V$ASM_USER u
WHERE dg.group_number = u.group_number AND dg.name = 'DATA';

DISKGROUP GROUP_NUMBER USER_NUMBER OS_ID OS_NAME
--------- ------------ ----------- ----- -------
DATA      1            1           1001  oracle1
DATA      1            2           1002  oracle2
DATA      1            3           1003  grid

Check File Access Control information with V$ASM_USERGROUP

SELECT dg.name AS diskgroup, ug.group_number, ug.owner_number, u.os_name,
ug.usergroup_number, ug.name FROM V$ASM_DISKGROUP dg, V$ASM_USER u, V$ASM_USERGROUP ug
WHERE dg.group_number = ug.group_number AND dg.name = 'DATA'
AND ug.owner_number = u.user_number;

DISKGROUP GROUP_NUMBER OWNER_NUMBER OS_NAME USERGROUP_NUMBER NAME
--------- ------------ ------------ ------- ---------------- --------
DATA      1            3            grid    1                asm_data

Check File Access Control information with V$ASM_USERGROUP_MEMBER

SELECT dg.name AS diskgroup, um.group_number, um.member_number, u.os_name,
um.usergroup_number, ug.name FROM V$ASM_DISKGROUP dg, V$ASM_USER u, V$ASM_USERGROUP_MEMBER um,
V$ASM_USERGROUP ug WHERE dg.group_number = um.group_number AND
dg.group_number = ug.group_number AND dg.group_number = u.group_number AND dg.name = 'DATA'
AND um.member_number = u.user_number AND um.usergroup_number = ug.usergroup_number;

DISKGROUP GROUP_NUMBER MEMBER_NUMBER OS_NAME USERGROUP_NUMBER NAME
--------- ------------ ------------- ------- ---------------- --------
DATA      1            1             oracle1 1                asm_data
DATA      1            2             oracle2 1                asm_data

Check ASM File Access Control information with V$ASM_FILE

SELECT dg.name AS diskgroup, a.name, f.permissions, f.user_number, u.os_name,
f.usergroup_number, ug.name
FROM V$ASM_DISKGROUP dg, V$ASM_USER u, V$ASM_USERGROUP ug, V$ASM_FILE f, V$ASM_ALIAS a
WHERE dg.name = 'FRA' AND dg.group_number = u.group_number AND
u.group_number = ug.group_number AND ug.group_number = f.group_number AND
f.group_number = a.group_number AND
f.usergroup_number = ug.usergroup_number AND f.user_number = u.user_number AND
f.file_number = a.file_number;

DISKGROUP NAME                PERMISSIONS USER_NUMBER OS_NAME USERGROUP_NUMBER NAME
--------- ------------------- ----------- ----------- ------- ---------------- -------
DATA      USERS.259.685366091 rw-r-----   3           grid    1                asm_fra
DATA      TEMP.264.685366227  rw-r-----   3           grid    1                asm_fra

For more detail refer to the following link:
https://docs.oracle.com/en/database/oracle/oracle-database/18/ostmg/automatic-storage-management-administrators-guide.pdf

Manage size of disk required in Oracle ASM

Manage size of disk required in Oracle ASM

V$ASM_DISKGROUP View help to manage the size of diskgroup required.

Reduced redundancy in Oracle ASM
Reduced redundancy means that one or more extents in the file are not mirrored at the expected level.
Example: High redundancy disk need 3 copies of file extent in failover groups but you have less than 3, you have two copies or less copies of the extent is called Reduce redundancy in ASM. Other causes of reduced redundancy files are disks running out of space or an insufficient number of failure groups.

V$ASM_DISKGROUP View
REQUIRED_MIRROR_FREE_MB indicates the amount of space required in a disk group to restore full redundancy without adding more space.
USABLE_FILE_MB indicates the amount of free space available for new files to restore redundancy after a disk failure
TOTAL_MB is the total usable capacity of a disk group in megabytes.
FREE_MB is the unused capacity of the disk group in megabytes.


SELECT name, type, total_mb, free_mb, required_mirror_free_mb, usable_file_mb FROM V$ASM_DISKGROUP;

 
NAME TYPE   TOTAL_MB FREE_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB
---- ------ -------- ------- ----------------------- --------------
DATA NORMAL 6144     3768    1024                    1372


Note: Negative Values of USABLE_FILE_MB
Negative value means you need to add more space in diskgroup.

Relation between USABLE_FILE_MB & FREE_MB & REQUIRED_MIRROR_FREE_MB
In case of Normal redundancy:
(FREE_MB – REQUIRED_MIRROR_FREE_MB) / 2 = USABLE_FILE_MB

Note:
–Depending on the value of FREE_MB, you may not be able to create new files.
–The next failure might result in files with reduced redundancy