{"id":3606,"date":"2024-03-01T07:00:00","date_gmt":"2024-03-01T04:00:00","guid":{"rendered":"https:\/\/www.dataplatform.gr\/?p=3606"},"modified":"2024-02-29T23:50:22","modified_gmt":"2024-02-29T20:50:22","slug":"pos-mporoyme-na-lamvanoyme-email-kathe-fora-5","status":"publish","type":"post","link":"https:\/\/www.dataplatform.gr\/en\/pos-mporoyme-na-lamvanoyme-email-kathe-fora-5-2\/","title":{"rendered":"How can we get email whenever PGA memory exceeds a limit in Oracle database"},"content":{"rendered":"

When an Oracle database is heavily used by many connections \/ sessions, one or some of them may consume a large percentage of memory (RAM). If there is a problem and it is not dealt with due to lack of memory, the operating system and Oracle may start to kill sessions by themselves indiscriminately even until the entire database instance goes down.<\/p>\n\n\n\n

So in this article we will see a way that as soon as the memory (RAM) and specifically the memory called PGA in Oracle exceeds a limit, it sends a warning email containing the size of the memory and the information of the session that occupies it more.<\/p>\n\n\n\n

What is PGA memory?<\/h2>\n\n\n\n

THE Program Global Area or otherwise PGA<\/strong> is the non-shared memory occupied by each individual connection \/ session in the database. The PGA is used to store session information and process SQL queries for operations such as sorting.<\/p>\n\n\n\n

The footsteps<\/h2>\n\n\n\n

In previous article<\/a> we had seen how we can set one up Linux servers<\/strong> (Oracle Linux based on RHEL specifically) so that we receive an email whenever an error occurs in the Alert log of the database instance. Accordingly now we will see the steps to receive email when PGA memory exceeds some limit.<\/p>\n\n\n\n

Create an SMTP relay with Google's gmail<\/h2>\n\n\n\n

If we have our own relay<\/strong> on the exchange server with configured smtp that will send the emails, we don't need this step.<\/p>\n\n\n\n

But now we will see what we do in case we want to send the emails through Gmail.<\/p>\n\n\n\n

First we need to connect to gmail account security<\/a>.<\/p>\n\n\n\n

There we should activate 2 factor authentication and then create an application password.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

After getting the code from gmail, go to our server and add to the mail configuration our email along with the code we got:<\/p>\n\n\n\n

[oracle@dp-gr ~]$ sudo vi \/etc\/mail.rc <\/code><\/pre>\n\n\n\n
account gmail {\n    set smtp-use-starttls\n    set ssl-verify=ignore\n    set smtp-auth=login\n    set smtp=smtp:\/\/smtp.gmail.com:587\n    set from=\"info@dataplatform.gr\"\n    set smtp-auth-user=dataplatform.gr@gmail.com\n    set smtp-auth-password=\"edw_vazoume_to_app_password\"\n    set ssl-verify=ignore\n    set nss-config-dir=\/etc\/pki\/nssdb\n}<\/pre>\n\n\n\n
We are ready to test that we can send email:<\/p>\n\n\n\n
[oracle@dp-gr ~]$ echo \"Test\" | \/usr\/bin\/mail -A gmail -s \"dokimastiko\" ToEmailMas@outlook.com<\/code><\/pre>\n\n\n\n
How to find the total PGA memory of the instance and the session that consumes the most<\/h2>\n\n\n\n
We create the following script that outputs a warning message with the total PGA memory occupied by all the sessions of the instance and the information of the session that currently occupies the most:<\/p>\n\n\n\n
[oracle@dp-gr ~]$ vi check_pga.sql<\/code><\/pre>\n\n\n\n
set echo off\nset pagesize 0\nset feedback off\nselect 'Warning: Session''s Program Page Area (PGA) Memory on '|| (select instance_name from v$instance) || '@' ||(select host_name from v$instance)||' is too high!!!       ---> '  ||to_char(pga_sum_gb, '999g999g990d00', 'NLS_NUMERIC_CHARACTERS=,.')|| ' GB' as PGA_Response \n FROM (select sum(pga_alloc_mem\/1024\/1024\/1024) as pga_sum_gb from v$process)\nwhere pga_sum_gb > 0.01; -- DONT FORGET TO SWITCH\nSELECT 'Highest PGA Session: ' \n   || ' PGA:' || to_char(p.pga_alloc_mem\/1024\/1024\/1024, '999g999g990d00', 'NLS_NUMERIC_CHARACTERS=,.')||' GB '\n   || ' ---- LOGON: ' || to_char(h.LOGON_TIME,'YYYY\/MM\/DD HH:MI:SS') \n   || ' ---- SID: ' || h.SID\n   || ' ---- SERIAL: ' || h.SERIAL#\n   || ' ---- PROCESS: ' || p.SPID\n   || ' ---- USERNAME: ' || u.username\n   || ' ---- OSUSER: ' || h.osuser\n   || ' ---- MACHINE: ' || h.machine\n   || ' ---- PROGRAM: ' || h.program\n   || ' ---- MODULE: ' || h.module\n   || ' ----               SQL TEXT: ' || s.sql_text\n   FROM\n   gv$session h\nLEFT JOIN gv$SQLAREA s ON h.sql_hash_value = s.hash_value and h.sql_ADDRESS = s.ADDRESS and h.inst_id = s.inst_id \nLEFT JOIN DBA_USERS u ON h.USER# = u.USER_ID\nLEFT JOIN gv$process  p ON p.ADDR = h.PADDR and p.inst_id = h.inst_id \nWHERE  1=1\nand (select sum(pga_alloc_mem\/1024\/1024\/1024) as pga_sum_gb from v$process) > 0.01 -- DONT FORGET TO SWITCH\nand rownum <=1\nORDER BY p.pga_alloc_mem DESC;\nquit;\n\/<\/pre>\n\n\n\n
*In the two fields in lines 6 and 25, we define the limit at which the query result will be returned from this total PGA memory and above. For the example I put 0.01 GB. If we wanted it to show us only whenever it exceeds 30 GB we would change it to 30.<\/em><\/p>\n\n\n\n
How do we send emails when PGA passes the threshold we set<\/h2>\n\n\n\n
Now we will create an executable that will run the above query, exporting the results to a text file. In case the file is more than 3 lines then it will send an email with the title \u201cOracle DB Alert ORCL\u201d<\/em> in the emails we have defined with a warning as text with the total PGA and the information of the session that occupies the most.<\/p>\n\n\n\n
The reason that the process only completes if the file contains more than 3 lines is so that it does not send an email if there is no alert and the text file is empty.<\/p>\n\n\n\n
So we create the executable with the following code:<\/p>\n\n\n\n
[oracle@dp-gr ~]$ vi check_pga_live.sh<\/code><\/pre>\n\n\n\n
#!\/bin\/bash\nsqlplus -S \"\/as sysdba\" @\/home\/oracle\/check_pga.sql > \/home\/oracle\/check_pga.log\ncnt=$(wc -l <\/home\/oracle\/check_pga.log)\necho $cnt\nif [ $cnt -ge \"4\" ]\nthen\n        \/usr\/bin\/mail -A gmail -s \"Oracle DB high PGA on ORCL\" \\\n                -r info@dataplatform.gr \\\n                patatakia@outlook.com, dokimi@outlook.com \\\n         < \/home\/oracle\/check_pga.log\nfi<\/pre>\n\n\n\n
We can alternatively run it remotely on another machine with the SYS user via ezconnect<\/a><\/strong>:<\/p>\n\n\n\n
#!\/bin\/bash\nsqlplus -S \"sys\/Kwdikos@hostname:1521\/ORCL as sysdba\" @\/home\/oracle\/check_pga.sql > \/home\/oracle\/check_pga.log\ncnt=$(wc -l <\/home\/oracle\/check_pga.log)\necho $cnt\nif [ $cnt -ge \"4\" ]\nthen\n        \/usr\/bin\/mail -A gmail -s \"Oracle DB Alert ORCL\" \\\n                -r info@dataplatform.gr \\\n                patatakia@outlook.com, dokimi@outlook.com \\\n         < \/home\/oracle\/check_pga.log\nfi<\/pre>\n\n\n\n
We don't forget to have given execute permission before executing it:<\/p>\n\n\n\n
[oracle@dp-gr ~]$ chmod +x check_pga_live.sh\n[oracle@dp-gr ~]$ .\/check_pga_live.sh<\/code><\/pre>\n\n\n\n
How do we schedule it to check every 5 minutes for Alerts<\/h2>\n\n\n\n
Since our executable is ready, we can add a line to the crontab in which we will set it to run every 5 minutes:<\/p>\n\n\n\n
[oracle@dp-gr ~]$ crontab -e<\/code><\/pre>\n\n\n\n
#crontab\n0,5,10,15,20,25,30,35,40,45,50,55 * * * * . ~\/.bash_profile && (\/home\/oracle\/check_pga_live.sh  >> \/dev\/null 2>&1)<\/pre>\n\n\n\n
The result<\/h2>\n\n\n\n
Now we will see that whenever the PGA exceeds the threshold we have set (for example I had set 0.01 GB) we will receive an email.<\/p>\n\n\n\n
This email will contain a message of the type:<\/p>\n\n\n\n
Warning: Session's Program Page Area (PGA) Memory on DBNAME@HOSTNAME is too high!!!\n     --->            0,09 GB<\/code><\/pre>\n\n\n\n
And the following information of the session consuming the most PGA memory at the moment:<\/p>\n\n\n\n