Orphan ASM File Cleanup Script

Hi all,

So I got asked by a client to perform a checking on ASM for orphan files, as they have some frequent create/drop database on this environment, as being a development env.

Also, lots of databases shared the same data diskgroup, so I had to work this out for all databases and also for possible inexistent databases.

Some basic approaches I raised:

1) Locating uncatalloged files in ASM per database.
– Source: https://oraganism.wordpress.com/2012/09/09/orphaned-files-in-asm/
– This approach assumes the files on ASM uncatalogged to any database are the Orphaned ones. Which is a fair assumption.
– But I understand that files can be catalloged and unmonted, which would brake this approach.

2) Listing files in ASM but not in database (v$datafile, v$datafile_copy, v$controlfile, v$tempfile, v$logfile) by database.
– Source: https://oracledba.blogspot.com/2018/11/orphaned-files-in-asm.html
– This seems a fair assumption. Would need to be ran from each database.
– There is not guarantee if this is working properly or not.
– Not clear also if PDB files are included.
– There is another similar one: https://dbaliveblog.wordpress.com/asm-orphaned-file-identification-script/
– Also this one: https://anjo.pt/wp/keyword-oracle/2013/02/26/find-orphan-asm-files/

3) MOS: Query That Can Be Used to Find Orphaned Datafiles on a 12c ASM Instance (Doc ID 2228573.1)
– From MOS, seems the most recommended approach.
– Attention point: PDB$SEED may not be shown as per: PDB$SEED Datafiles Not Appear In CDB_DATA_FILES (Doc ID 1940806.1)
— On 12.1.0.2, recommended to use “EXCLUDE_SEED_CDB_VIEW”. To check if it can be done on session level.

I downloaded and ran referred script on MOS Script to report the list of files stored in ASM and CURRENTLY NOT OPENED (Doc ID 552082.1) on the environment.
But the results didn’t sound correct.

After a while, I ended up building my own script based on all mentioned approaches and it worked very fine.

After approved I dropped all the listed files, freed a several TBs of space and no database affected. So I’d assume it as correct and would really recommend it for you.

So what did I used:

SQL to Check ASM Space per Database:

set pages 350 timing on
col gname form a10
col dbname form a10
col file_type form a16
break on gname skip 2 on dbname skip 1
compute sum label total_db of gb on dbname
compute sum label total_diskg of gb on gname  
SELECT
    gname,
    dbname,
    file_type,
    round(SUM(space)/1024/1024) mb,
    round(SUM(space)/1024/1024/1024) gb,
    COUNT(*) "#FILES"
FROM
    (
        SELECT
            gname,
            regexp_substr(full_alias_path, '[[:alnum:]_]*',1,4) dbname,
            file_type,
            space,
            aname,
            system_created,
            alias_directory
        FROM
            (
                SELECT
                    concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path,
                    system_created,
                    alias_directory,
                    file_type,
                    space,
                    level,
                    gname,
                    aname
                FROM
                    (
                        SELECT
                            b.name            gname,
                            a.parent_index    pindex,
                            a.name            aname,
                            a.reference_index rindex ,
                            a.system_created,
                            a.alias_directory,
                            c.type file_type,
                            c.space
                        FROM
                            v$asm_alias a,
                            v$asm_diskgroup b,
                            v$asm_file c
                        WHERE
                            a.group_number = b.group_number
                        AND a.group_number = c.group_number(+)
                        AND a.file_number = c.file_number(+)
                        AND a.file_incarnation = c.incarnation(+) ) START WITH (mod(pindex, power(2, 24))) = 0
                AND rindex IN
                    (
                        SELECT
                            a.reference_index
                        FROM
                            v$asm_alias a,
                            v$asm_diskgroup b
                        WHERE
                            a.group_number = b.group_number
                        AND (
                                mod(a.parent_index, power(2, 24))) = 0
                    ) CONNECT BY prior rindex = pindex )
        WHERE
            NOT file_type IS NULL
            and system_created = 'Y' )
GROUP BY
    gname,
    dbname,
    file_type
ORDER BY
    gname,
    dbname,
    file_type
/

Expected Output:

SQL> @asm_sizebydb

GNAME	   DBNAME     FILE_TYPE 	       MB	  GB	 #FILES
---------- ---------- ---------------- ---------- ---------- ----------
DATAC1	   DATABSE1   CONTROLFILE	     2316	   2	      1
		      DATAFILE		  7620756	7442	     49
		      DATAGUARDCONFIG	       16	   0	      2
		      ONLINELOG 	    82536	  81	     14
		      PARAMETERFILE		8	   0	      1
	   **********				  ----------
	   total_db					7525

	   DATABSE2   CONTROLFILE	      492	   0	      1
		      DATAFILE		  3081604	3009	     47
		      ONLINELOG 	      416	   0	      4
		      PARAMETERFILE	       16	   0	      2
		      PASSWORD			0	   0	      2
		      TEMPFILE		    83372	  81	      3
	   **********				  ----------
	   total_db					3090

	   DATABSE3   CONTROLFILE	      588	   1	      1
		      DATAFILE		  1430712	1397	      8
		      DATAGUARDCONFIG	       16	   0	      2
		      ONLINELOG 	   147816	 144	     18
		      PARAMETERFILE		8	   0	      1
	   **********				  ----------
	   total_db					1542
[...]

**********					  ----------
total_disk					       76868

SQL To list Orphan files per Database:

SET VERIFY OFF
SET LINESIZE 200
SET SERVEROUTPUT ON
SET PAGESIZE 50000

DECLARE
   cmd   CLOB;
BEGIN
   FOR c IN (SELECT name Diskgroup
               FROM V$ASM_DISKGROUP)
   LOOP
      FOR l
         IN (SELECT 'rm ' || files files
               FROM
                    (SELECT '+' || c.Diskgroup || files files, TYPE
                       FROM (    SELECT UPPER
                                        (
                                           SYS_CONNECT_BY_PATH (aa.name, '/')
                                        )
                                           files
                                      , aa.reference_index
                                      , b.TYPE
                                   FROM (SELECT file_number
                                              , alias_directory
                                              , name
                                              , reference_index
                                              , parent_index
                                           FROM v$asm_alias) aa
                                      , (SELECT parent_index
                                           FROM (SELECT parent_index
                                                   FROM v$asm_alias
                                                  WHERE     group_number =
                                                               (SELECT group_number
                                                                  FROM v$asm_diskgroup
                                                                 WHERE name =
                                                                          c.Diskgroup)
                                                        AND alias_index = 0)) a
                                      , (SELECT file_number, TYPE
                                           FROM (SELECT file_number, TYPE
                                                   FROM v$asm_file
                                                  WHERE group_number =
                                                           (SELECT group_number
                                                              FROM v$asm_diskgroup
                                                             WHERE name =
                                                                      c.Diskgroup)))
                                        b
                                  WHERE     aa.file_number = b.file_number(+)
                                        AND aa.alias_directory = 'N'
                                        AND b.TYPE IN
                                               ('DATAFILE'
                                              , 'ONLINELOG'
                                              , 'CONTROLFILE'
                                              , 'TEMPFILE')
                             START WITH aa.PARENT_INDEX = a.parent_index
                             CONNECT BY PRIOR aa.reference_index =
                                           aa.parent_index)
                      WHERE SUBSTR
                            (
                               files
                             , INSTR (files, '/', 1, 1)
                             ,   INSTR (files, '/', 1, 2)
                               - INSTR (files, '/', 1, 1)
                               + 1
                            ) =
                               (SELECT dbname
                                  FROM (SELECT    '/'
                                               || UPPER (db_unique_name)
                                               || '/'
                                                  dbname
                                          FROM v$database))
                     MINUS
                     (SELECT UPPER (name) files, 'DATAFILE' TYPE
                        FROM v$datafile
                      UNION ALL
                      SELECT UPPER (name) files, 'TEMPFILE' TYPE
                        FROM v$tempfile
                      UNION ALL
                      SELECT UPPER (name) files, 'CONTROLFILE' TYPE
                        FROM v$controlfile
                       WHERE name LIKE '+' || c.Diskgroup || '%'
                      UNION ALL
                      SELECT UPPER (name), 'CONTROLFILE' TYPE
                        FROM v$datafile_copy
                       WHERE deleted = 'NO'
                      UNION ALL
                      SELECT UPPER (MEMBER) files, 'ONLINELOG' TYPE
                        FROM v$logfile
                       WHERE MEMBER LIKE '+' || c.Diskgroup || '%')))
      LOOP
         DBMS_OUTPUT.put_line (l.files);
      END LOOP;
   END LOOP;
END;
/

Expected Output:

rm +DATA/XPTODB/CONTROLFILE/CURRENT.4928.955985765
rm +DATA/XPTODB/CONTROLFILE/CURRENT.4934.955986589
rm +DATA/XPTODB/CONTROLFILE/CURRENT.4962.955998825
rm +DATA/XPTODB/CONTROLFILE/CURRENT.5063.956480113
rm +DATA/XPTODB/CONTROLFILE/CURRENT.6374.955984145
rm +DATA/XPTODB/CONTROLFILE/CURRENT.7547.955968953
rm +DATA/XPTODB/DATAFILE/TBSEXEMPLE.4936.955985803
rm +DATA/XPTODB/DATAFILE/TBSEXEMPLE.4966.955998847
rm +DATA/XPTODB/DATAFILE/TBSEXEMPLE.7540.955968995
rm +DATA/XPTODB/DATAFILE/TBSEXEMPLE.7574.955984177
rm +DATA/XPTODB/DATAFILE/TBSEXEMPLE.4937.955985803
rm +DATA/XPTODB/DATAFILE/TBSEXEMPLE.4967.955998847
rm +DATA/XPTODB/DATAFILE/TBSEXEMPLE.7542.955968995
rm +DATA/XPTODB/DATAFILE/TBSEXEMPLE.7558.955984177
rm +DATA/XPTODB/DATAFILE/SYSAUX.4935.955986599
rm +DATA/XPTODB/DATAFILE/SYSAUX.4963.955998847
rm +DATA/XPTODB/DATAFILE/SYSAUX.6286.955984161
rm +DATA/XPTODB/DATAFILE/SYSAUX.7544.955968963
rm +DATA/XPTODB/DATAFILE/SYSTEM.4930.955986599
rm +DATA/XPTODB/DATAFILE/SYSTEM.4964.955998847
rm +DATA/XPTODB/DATAFILE/SYSTEM.7536.955968965

To run this for all databases on server (RAC Databases):

export ORAENV_ASK=NO
for DBSID in `ps -ef | grep ora_pmon | grep -v grep | awk -F_ '{ print $3}'` 
do
echo "######" ${DBSID}
export ORACLE_SID=${DBSID}
. oraenv
sqlplus / as sysdba
@script.sql
exit
done

Hope it helps you!

Issues with Email attachments for Crontab Jobs

Hello ALL,

Last days I faced a strange issue with a shell script running as crontab job.

I had a Shell Script with working properly when executing manually, it functions simply sent an email with embedded attachments.  However, when it scheduled in Crontab, it does not send attachments ( I received the email but with BLANK attachments – even if code running fine and sending attachments when executed manually).

It made my brain burn for some time, then I found a lot of people facing the same issue when scripts in crontab.

It looks for some crontab relation with SH/Bash scripts.

Here are the workarounds I found and deployed in my script. Now, the email attachments are being included in emails when running as crontab jobs.

  • Include: ‘#!/bin/sh’ or ‘#!/bin/bash’ in the first line of your shell script.
  • Insert full path of commands you using in statements. E.g: Are you using ‘mailx’? so, execute ‘whereis mailx’, then, all statements where mailx is running will be like ‘/usb/bin/mailx -s “SUBJECT” myemail@domain.com’.
  • Source your sh/bash profile before the email statement. This statement solved my error. I found some posts explanations, but not sure why
    • ‘. ~/.bash_profile’.

Here is my code:

#!/bin/sh
################################################################################
# Email alerting to
################################################################################
EMAILTO="youremail@company.com"      # The email to send the alert to
SUBJECT="My shell script in crontab" # email subject

. ~/.bash_profile

sqlplus /nolog < /dev/null 2>&1
connect / as sysdba
spool /tmp/temp_out.txt
select * from dual
spool off
EOF

cat /tmp/temp_out.txt |grep "no rows selected"
if [ $? == 0 ]; then
echo "Nothing to do"
/usr/bin/echo "Nothing to do"| /usr/bin/mail -s "${SUBJECT}" ${EMAILTO}
else
/usr/bin/cat /tmp/temp_out.txt | /usr/bin/mail -s "${SUBJECT}" ${EMAILTO}
fi
rm /tmp/temp_out.txt

 

Extracting DML Stats With GGate

Hi all,

After having GoldenGate setup in your environment, on of the most frequent questions you most likely face is how much work is being done by your GGate environment. Or even you may want to set up some dummy extraction just to measure it before start your GGate implementation project.

GoldenGate provides stats command to report the work which is done. It’s recommended to reset the counters/stats before any testing you want or before start gathering, for better outputs.

The retrieving can be done for the total (since last reset), daily or even on table level, as per below:

1. Reset counters/stats:

stats extract ext_test, reset

2. Retrieve stats for a Table since Reset:

stats extract ext_test, table owner1.test, latest

3. Retrieve stats Total since last start of extract:

stats extract ext_int, totalsonly owner1.test

4. Retrieve stats from the day of a table:

stats extract ext_int, daily, table owner1.test

5. Retrieve stats from the day of all schema tables configured on extract:

stats extract ext_int, daily, table owner1.*

 

Example of Output:

GGSCI (myserver.local) 1> stats ext_test total daily

Sending STATS request to EXTRACT EXT_TEST ...

Start of Statistics at 2019-12-16 15:21:56.

Output by User Exit:

Extracting from OWNER1.TABLE1 to OWNER1.TABLE1:

*** Total statistics since 2019-12-16 07:18:14 ***
        Total inserts                                   2744.00
        Total updates                                      0.00
        Total deletes                                    300.00
        Total discards                                     0.00
        Total operations                                3044.00

*** Daily statistics since 2019-12-16 07:18:14 ***
        Total inserts                                   2744.00
        Total updates                                      0.00
        Total deletes                                    300.00
        Total discards                                     0.00
        Total operations                                3044.00

More about it?
Here: https://docs.oracle.com/goldengate/1212/gg-winux/GWURF/ggsci_commands030.htm#GWURF216

Hope it Helps!

ORA-02056: 2PC: k2lcom: bad two-phase command number rdonly from coord

Hi all,

So, you found this error?

Well, I received this in a client environment. After checking MOS ORA-02056: 2PC: K2gCheckGlobalCommit: Bad Two-phase Command Number 1 From Coord (Doc ID 2498134.1), which points to a known issue when dealing with committing transactions as per Bug 22016049 I noticed my problem could be different…

My real root cause is my transaction being done on a remote object and when checking on remote database I found pending distributed transactions.

For more informations about distributed transactions, please check: https://docs.oracle.com/database/121/ADMIN/ds_txns.htm#ADMIN12211

GUID-D521B4E9-E916-4B02-8B0E-4FAF4DC61423-default

So, if you getting this situation, before planning and applying the patch I suggest you:

  • Check if your process is not working on remote databases.
  • Check for any possible distributed remote transactions.

How? Easy check (on remote db):

select * from dba_2pc_pending;

I hope it helps!

PS NOTE: I just found this very good and complete post by Rodrigo Mufalani on the same topic: ORA-01591: lock held by in-doubt distributed transaction

Have a look for an Action Plan!
Cheers!

OEM 12c Agent: java.lang.OutOfMemoryError: Java heap space

Hi all,

So I got this error from OEM in a client. When connecting noticed OEM agent was down.

From logs, it failed to restart because of error “java.lang.OutOfMemoryError: Java heap space“.

I tried to run clearstate but got same error.

Searched on MOS – found MOS Duplicate 1952593.1: EM12c: emctl start agent Fails With Target Interaction Manager failed at Startup java.lang.OutOfMemoryError: Java heap space reported in gcagent_errors.log (Doc ID 1902124.1)

Solution seems to be moving /agent_inst/sysman/emd/state/ content to another location before running clearstate.

After doing so, all worked fine! So if you are facing the same, try this out!

oracle@dbserver:/db/oracle/product/agent12c/12.1.0.4/agent_inst/bin$ ./emctl status agent
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
Agent is Not Running
oracle@dbserver:/db/oracle/product/agent12c/12.1.0.4/agent_inst/bin$ cd ..
oracle@dbserver:/db/oracle/product/agent12c/12.1.0.4/agent_inst$ cd sysman
oracle@dbserver:/db/oracle/product/agent12c/12.1.0.4/agent_inst/sysman$ cd log/
oracle@dbserver:/db/oracle/product/agent12c/12.1.0.4/agent_inst/sysman/log$ ls -lrt | tail
-rw-r----- 1 oracle dba 4096 Jan 9 09:47 emagent.nohup.lr
-rw-r----- 1 oracle dba 11165 Jan 9 09:48 startup.info
-rw-r----- 1 oracle dba 153432 Jan 9 09:48 gcagent_errors.log
-rw------- 1 oracle dba 540010788 Jan 9 09:48 heapDump_7.hprof
-rw-r----- 1 oracle dba 4687452 Jan 9 09:48 gcagent.log
-rw-r----- 1 oracle dba 1746 Jan 9 09:48 agabend.log
-rw-r----- 1 oracle dba 2863 Jan 9 09:48 gcagent_pfu.log
-rw-r----- 1 oracle dba 84308 Jan 9 09:48 emagent.nohup
-rw-r----- 1 oracle dba 762142 Jan 9 10:06 emdctlj.log
-rw-r----- 1 oracle dba 121782 Jan 9 10:06 emctl.log
oracle@dbserver:/db/oracle/product/agent12c/12.1.0.4/agent_inst/sysman/log$ date
Monday, January 9, 2020 10:06:24 AM PST
oracle@dbserver:/db/oracle/product/agent12c/12.1.0.4/agent_inst/sysman/log$ tail gcagent.log
2020-01-09 09:48:50,875 [32:F9C26A76] INFO - *jetty*: Graceful shutdown SslSelectChannelConnector@0.0.0.0:3872
2020-01-09 09:48:50,877 [32:F9C26A76] INFO - *jetty*: Graceful shutdown ContextHandler@63b5a40a@63b5a40a/emd/lifecycle/main,null
2020-01-09 09:48:50,877 [32:F9C26A76] INFO - *jetty*: Graceful shutdown HTTPLifecycleHandler@7284aa02
2020-01-09 09:48:50,877 [32:F9C26A76] INFO - *jetty*: Graceful shutdown ContextHandler@5dac13d7@5dac13d7/emd/main,null
2020-01-09 09:48:50,877 [32:F9C26A76] INFO - *jetty*: Graceful shutdown HTTPRequestHandler@52a34783
2020-01-09 09:48:50,877 [32:F9C26A76] INFO - *jetty*: Graceful shutdown ServletContextHandler@201d592a@201d592a/emd/browser,null
2020-01-09 09:48:50,878 [32:F9C26A76] INFO - *jetty*: Graceful shutdown ContextHandler@19a9bea3@19a9bea3/emd/persistence/main,null
2020-01-09 09:48:50,878 [32:F9C26A76] INFO - *jetty*: Graceful shutdown HTTPAgentPersistenceHandler@3d89acb5
2020-01-09 09:48:50,878 [32:F9C26A76] INFO - *jetty*: Graceful shutdown ContextHandler@5722cc7e@5722cc7e/,null
2020-01-09 09:48:53,932 [32:F9C26A76] INFO - *jetty*: Shutdown hook complete
oracle@dbserver:/db/oracle/product/agent12c/12.1.0.4/agent_inst/sysman/log$ tail gcagent_errors.log
at oracle.sysman.gcagent.target.interaction.execution.Threshold.initThreshold(Threshold.java:4132)
at oracle.sysman.gcagent.target.interaction.execution.TargetInteractionMgr.init(TargetInteractionMgr.java:225)
at oracle.sysman.gcagent.target.interaction.execution.TargetInteractionMgr.tmNotifier(TargetInteractionMgr.java:171)
at oracle.sysman.gcagent.tmmain.lifecycle.TMComponentSvc.invokeNotifier(TMComponentSvc.java:998)
at oracle.sysman.gcagent.tmmain.lifecycle.TMComponentSvc.invokeInitializationStep(TMComponentSvc.java:1083)
at oracle.sysman.gcagent.tmmain.lifecycle.TMComponentSvc.doInitializationStep(TMComponentSvc.java:916)
at oracle.sysman.gcagent.tmmain.lifecycle.TMComponentSvc.notifierDriver(TMComponentSvc.java:812)
at oracle.sysman.gcagent.tmmain.TMMain.startup(TMMain.java:256)
at oracle.sysman.gcagent.tmmain.TMMain.agentMain(TMMain.java:557)
at oracle.sysman.gcagent.tmmain.TMMain.main(TMMain.java:546)
oracle@dbserver:/db/oracle/product/agent12c/12.1.0.4/agent_inst/sysman/log$ less gcagent_errors.log
oracle@dbserver:/db/oracle/product/agent12c/12.1.0.4/agent_inst/sysman/log$ cd ..
oracle@dbserver:/db/oracle/product/agent12c/12.1.0.4/agent_inst/sysman$ cd config/
oracle@dbserver:/db/oracle/product/agent12c/12.1.0.4/agent_inst/sysman/config$ ls -lrt | tail
-rw-r----- 1 oracle dba 8001 May 13 2016 b64LocalCertificate.txt
-rw-r----- 1 oracle dba 17893 May 13 2016 b64InternetCertificate.txt
-rw-r----- 1 oracle dba 7833 May 13 2016 emd.properties.2016_05_13_10_05_31
-rw------- 1 oracle dba 499 May 20 2016 s_jvm_options.opt.save
-rw-r----- 1 oracle dba 8202 Sep 26 01:20 emd.properties.bkp
-rw-r----- 1 oracle dba 8204 Jan 9 09:46 emd.properties.bak
-rw-r----- 1 oracle dba 156 Jan 9 09:46 private.properties.bak
-rw-r----- 1 oracle dba 8204 Jan 9 09:48 emd.properties
-rw-r----- 1 oracle dba 266 Jan 9 09:48 autotune.properties
-rw-r----- 1 oracle dba 156 Jan 9 09:48 private.properties
oracle@dbserver:/db/oracle/product/agent12c/12.1.0.4/agent_inst/sysman/config$ date
Monday, January 9, 2020 10:07:56 AM PST
oracle@dbserver:/db/oracle/product/agent12c/12.1.0.4/agent_inst/sysman/config$ n/emctl status agent
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
Agent is Not Running
oracle@dbserver:/db/oracle/product/agent12c/12.1.0.4/agent_inst/sysman/config$ which emctl
/db/oracle/product/agent12c/12.1.0.4/agent_inst/bin/emctl
oracle@dbserver:/db/oracle/product/agent12c/12.1.0.4/agent_inst/sysman/config$ e agent; emctl start agent

Oracle Enterprise Manager Cloud Control 12c Release 4 Copyright (c) 1996, 2014 Oracle Corporation. All rights reserved. EMD clearstate failed: Offline clearstate failed : java.lang.OutOfMemoryError: Java heap space Oracle Enterprise Manager Cloud Control 12c Release 4 Copyright (c) 1996, 2014 Oracle Corporation. All rights reserved. Starting agent ...........................................................................................................................failed. Consult emctl.log and emagent.nohup in: /db/oracle/product/agent12c/12.1.0.4/agent_inst/sysman/log oracle@dbserver:/db/oracle/product/agent12c/12.1.0.4/agent_inst/sysman/config$ oracle@dbserver:/db/oracle/product/agent12c/12.1.0.4/agent_inst/sysman/config$ cd .. oracle@dbserver:/db/oracle/product/agent12c/12.1.0.4/agent_inst/sysman$ cd emd oracle@dbserver:/db/oracle/product/agent12c/12.1.0.4/agent_inst/sysman/emd$ mkdir state-20200109 oracle@dbserver:/db/oracle/product/agent12c/12.1.0.4/agent_inst/sysman/emd$ mv state/* state\-20200109/ oracle@dbserver:/db/oracle/product/agent12c/12.1.0.4/agent_inst/sysman/emd$ du -hs state* 1K state 166M state-20200109 oracle@dbserver:/db/oracle/product/agent12c/12.1.0.4/agent_inst/sysman/emd$ emctl clearstate agent; emctl sta
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation. All rights reserved.
EMD clearstate completed successfully
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation. All rights reserved.
Starting agent ......................... started.
oracle@dbserver:/db/oracle/product/agent12c/12.1.0.4/agent_inst/sysman/emd$
oracle@dbserver:/db/oracle/product/agent12c/12.1.0.4/agent_inst/sysman/emd$ emctl status agent
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
Agent Version : 12.1.0.4.0
OMS Version : 12.1.0.4.0
Protocol Version : 12.1.0.1.0
Agent Home : /db/oracle/product/agent12c/12.1.0.4/agent_inst
Agent Log Directory : /db/oracle/product/agent12c/12.1.0.4/agent_inst/sysman/log
Agent Binaries : /db/oracle/product/agent12c/12.1.0.4/core/12.1.0.4.0
Agent Process ID : 27885
Parent Process ID : 27848
Agent URL : https://x0319vp114.nordstrom.net:3872/emd/main/
Local Agent URL in NAT : https://x0319vp114.nordstrom.net:3872/emd/main/
Repository URL : https://oemcloud.nordstrom.net:4900/empbs/upload
Started at : 2020-01-09 10:15:39
Started by user : oracle
Operating System : SunOS version 5.11 (sparcv9)
Last Reload : (none)
Last successful upload : 2020-01-09 10:17:35
Last attempted upload : 2020-01-09 10:17:35
Total Megabytes of XML files uploaded so far : 0.59
Number of XML files pending upload : 1
Size of XML files pending upload(MB) : 0
Available disk space on upload filesystem : 50.38%
Collection Status : Collections enabled
Heartbeat Status : Ok
Last attempted heartbeat to OMS : 2020-01-09 10:16:54
Last successful heartbeat to OMS : 2020-01-09 10:16:54
Next scheduled heartbeat to OMS : 2020-01-09 10:17:54

---------------------------------------------------------------
Agent is Running and Ready
oracle@dbserver:/db/oracle/product/agent12c/12.1.0.4/agent_inst/sysman/emd$

 

ORA-04030: out of process memory when trying to allocate % bytes (kkoutlCreatePh,ub1 : kkoabr)

Hi all,

So, I started seen this on a client environment. Researching on the  case after no crear reference on MOS, I noticed some high PGA allocation as per below.

SYS@proddb AS SYSDBA PROD> select pid, serial#,category, allocated/1024/1024 MB, used/1024/1024 MB_used, max_allocated/1024/1024 MB_MAX_ALLOCATED_ON_PGA
2 from v$process_memory where pid=852;

PID SERIAL# CATEGORY MB MB_USED MB_MAX_ALLOCATED_ON_PGA
---------- ---------- --------------- ---------- ---------- -----------------------
852 91 SQL .086807251 .00806427 .672416687
852 91 PL/SQL .087730408 .078926086 .126182556
852 91 Freeable .5625 0
852 91 Other 2.25187302 2.25187302

Seems a match MOS ORA-04030 Error With High “kkoutlCreatePh” (Doc ID 1618444.1).

The solution? Simply disabled the following parameter:

"_b_tree_bitmap_plans"=false

 

Trace for additional info:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_1
System name: Linux
Node name: proddb.local
Release: 4.1.12-94.8.3.el7uek.x86_64
Version: #2 SMP Wed Apr 25 19:57:32 PDT 2018
Machine: x86_64
Instance name: proddb
Redo thread mounted by this instance: 1
Oracle process number: 854
Unix process pid: 28899, image: oracle@proddb.local

*** 2019-01-09 11:20:09.130
*** SESSION ID:(5429.55092) 2019-01-09 11:20:09.130
*** CLIENT ID:() 2019-01-09 11:20:09.130
*** SERVICE NAME:(XXXXX) 2019-01-09 11:20:09.130
*** MODULE NAME:(JDBC Thin Client) 2019-01-09 11:20:09.130
*** CLIENT DRIVER:(jdbcthin) 2019-01-09 11:20:09.130
*** ACTION NAME:() 2019-01-09 11:20:09.130

[TOC00000]
Jump to table of contents
Dump continued from file: /u01/app/oracle/diag/rdbms/proddb/proddb/trace/proddb_ora_28899.trc
[TOC00001]
ORA-04030: out of process memory when trying to allocate 34392040 bytes (kkoutlCreatePh,ub1 : kkoabr)

[TOC00001-END]
[TOC00002]
========= Dump for incident 2710965 (ORA 4030) ========
[TOC00003]
----- Beginning of Customized Incident Dump(s) -----
=======================================
TOP 10 MEMORY USES FOR THIS PROCESS
---------------------------------------
72% 18 GB, 1356 chunks: "free memory " SQL
kxs-heap-c ds=0x7fa8df330220 dsprt=0x7fa8df49dbe0
27% 6809 MB, 3366 chunks: "permanent memory " SQL
kxs-heap-c ds=0x7fa8df330220 dsprt=0x7fa8df49dbe0
0% 100 MB, 894 chunks: "permanent memory " SQL
kkoutlCreatePh ds=0x7fa8cce16708 dsprt=0x7fa8df330220
0% 23 MB, 589515 chunks: "chedef : qcuatc "
TCHK^2a9688d9 ds=0x7fa8df33feb8 dsprt=0x7fa8df49c9e0
0% 18 MB, 150124 chunks: "opndef: qcopCreateOpnViaM "
TCHK^2a9688d9 ds=0x7fa8df33feb8 dsprt=0x7fa8df49c9e0
0% 16 MB, 214829 chunks: "logdef: qcopCreateLog "
TCHK^2a9688d9 ds=0x7fa8df33feb8 dsprt=0x7fa8df49c9e0
0% 11 MB, 241 chunks: "free memory "
top call heap ds=0x7fa8df49dbe0 dsprt=(nil)
0% 9643 KB, 4623 chunks: "qkkele " SQL
kxs-heap-c ds=0x7fa8df330220 dsprt=0x7fa8df49dbe0
0% 6534 KB, 58578 chunks: "optdef: qcopCreateOptInte "
TCHK^2a9688d9 ds=0x7fa8df33feb8 dsprt=0x7fa8df49c9e0
0% 4134 KB, 15399 chunks: "kccdef : qcsvwsci "
TCHK^2a9688d9 ds=0x7fa8df33feb8 dsprt=0x7fa8df49c9e0

=======================================
PRIVATE MEMORY SUMMARY FOR THIS PROCESS
---------------------------------------
******************************************************
PRIVATE HEAP SUMMARY DUMP
25 GB total:
25 GB commented, 794 KB permanent
12 MB free (0 KB in empty extents),
24 GB, 1 heap: "kxs-heap-c " 67 KB free held
------------------------------------------------------
Summary of subheaps at depth 1
25 GB total:
203 MB commented, 6809 MB permanent
18 GB free (0 KB in empty extents),
6398 MB, 9243 chunks: "allocator state " 6398 MB free held
3758 MB, 4623 chunks: "qkkele " 3749 MB free held
3650 MB, 4623 chunks: "qkkkey " 3650 MB free held

=========================================
REAL-FREE ALLOCATOR DUMP FOR THIS PROCESS
-----------------------------------------

Dump of Real-Free Memory Allocator Heap [0x7fa8df317000]
mag=0xfefe0001 flg=0x5000003 fds=0x0 blksz=65536
blkdstbl=0x7fa8df317010, iniblk=524288 maxblk=524288 numsegs=318
In-use num=2965 siz=641597440, Freeable num=0 siz=0, Free num=254 siz=3586195456

==========================================
INSTANCE-WIDE PRIVATE MEMORY USAGE SUMMARY
------------------------------------------

Dumping Work Area Table (level=1)
=====================================

Global SGA Info
---------------

global target: 102400 MB
auto target: 62376 MB
max pga: 2048 MB
pga limit: 4096 MB
pga limit known: 0
pga limit errors: 0

pga inuse: 33104 MB
pga alloc: 35238 MB
pga freeable: 1225 MB
pga freed: 433664026 MB
pga to free: 0 %
broker request: 0

pga auto: 12 MB
pga manual: 0 MB

pga alloc (max): 35238 MB
pga auto (max): 12284 MB
pga manual (max): 2 MB

# workareas : 0
# workareas(max): 551