March 2016 – Page 2 – Make DBA Life Easy

EMAGENT Stopped With “OutOfMemoryError”

Java paramemetes like ” -Xmx” and “-XX:MaxPermSize” might cause OEM agent up/down periodically.

SYMPTOMS

The agent fails to start, or keeps restarting itself, or becomes unresponsive and the following messages are seen in logs.

$AGENT_INST/bin/emctl start agent reports following error :

......
......
......
Starting agent .........................................
Consult emctl.log and emagent.nohup in: /db/oracle/product/agent12c/12.1.0.4/agent_inst/sysman/log

$AGENT_INST/sysman/log/emagent.nohup reports following error :

......
......
2016-03-06 20:03:08,691 [1:main] WARN - Missing filename for log handler 'opsscfg
Agent is going down due to an OutOfMemoryError
----- Sun Mar 6 20:03:28 2016::23451::Checking status of EMAgent : 30249 -----
----- Sun Mar 6 20:03:28 2016::23451::EMAgent exited at Sun Mar 6 20:03:28 2016 with return value 57. -----
----- Sun Mar 6 20:03:28 2016::23451::EMAgent will be restarted because of an Out of Memory Exception. 
......
......

$AGENT_INST/sysman/log/gcagent.log reports following error :

......
......
2016-03-06 20:02:52,549 [1:main] FATAL - Fatal error: Target Interaction Manager failed at Startup
java.lang.OutOfMemoryError: Java heap space
......
......
......
2016-03-06 20:02:52,552 [1:main] ERROR - agent main threw an error
java.lang.OutOfMemoryError: Java heap space

$GENT_INST/bin/emctl start agent reports following error :

Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation. All rights reserved.
Starting agent ............................................failed.
Fatal agent error: Target Interaction Manager failed at Startup
Fatal agent error: Target Interaction Manager failed at Startup
Fatal agent error: Target Interaction Manager failed at Startup
EMAgent is Thrashing. Exiting watchdog

$AGENT_INST/sysman/log/emagent.nohup reports following error :

......
......
2016-03-06 20:03:08,691 [1:main] WARN - Missing filename for log handler 'opsscfg
Agent is going down due to an OutOfMemoryError

$AGENT_INST/sysman/log/gcagent.log reports following error :

2016-03-06 20:02:52,549[1:3305B9] INFO - Invoking STARTUP_P1 (1) on Target Interaction Manager
2016-03-06 20:02:52,549[1:main] FATAL - Fatal error: Target Interaction Manager failed at Startup
java.lang.OutOfMemoryError: Java heap space
at java.util.Arrays.copyOf(Arrays.java:2882)
at java.lang.AbstractStringBuilder.expandCapacity(AbstractStringBuilder.java:100)

$AGENT_INST/sysman/log/gcagent.log reports following errors:

......
......
......
java.lang.OutOfMemoryError: PermGen space
......
......
......
java.lang.OutOfMemoryError: PermGen space
2016-03-06 20:02:52,660 [64:GC.Executor.8 (oracle_camm_manager:ADPManager_EMGC_ADPMANAGER2:Response) (oracle_camm_manager:ADPManager_EMGC_ADPMANAGER2:Response:Response)] ERROR - oracle_camm_manager:ADPManager_EMGC_ADPMANAGER2:Response:Response
java.lang.OutOfMemoryError: PermGen space

SOLUTION

1. Stop the agent first.

$ $AGENT_INST/bin/emctl stop agent

If agent does not shutdown gracefully, then kill all agent background processes by first grepping for agent perl and java processes only.

$ps -eaf | grep java | grep '/db/oracle/product/agent12c/12.1.0.4/agent_inst'

$ps -eaf | grep perl
$kill -9 <Process id>

2. Take backup and edit $AGENT_INST/sysman/config/emd.properties file.

Note : Tune -Xmx and -XX:MaxPermSize parameters , in the below example heap value is increased to 512M from 128M .

The default current value:

agentJavaDefines=-Xmx128M -XX:MaxPermSize=96M

The new value:

agentJavaDefines=-Xmx512M -XX:MaxPermSize=128M

For 13c, please refer to “EM 13c: Agent Tuning Parameters when the Agent is Monitoring Several Targets (Doc ID 2449197.1)“

3. Restart agent successfully.

$ $AGENT_INST/bin/emctl start agent
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation. All rights reserved.
Starting agent ............................................... started.

$AGENT_INST/bin/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 : 6532
Parent Process ID : 6208
......
......
......
Operating System : Linux version 2.6.32-358.6.1.el6.x86_64 (amd64)
Last Reload : (none)
Last successful upload : 2016-03-06 20:19:15
Last attempted upload : 2016-03-06 20:19:15
Total Megabytes of XML files uploaded so far : 0.03
Number of XML files pending upload : 0
Size of XML files pending upload(MB) : 0
Available disk space on upload filesystem : 41.27%
Collection Status : Collections enabled
Heartbeat Status : Ok
Last attempted heartbeat to OMS : 2016-03-06 20:19:14
Last successful heartbeat to OMS : 2016-03-06 20:19:14
Next scheduled heartbeat to OMS : 2016-03-06 20:20:14

---------------------------------------------------------------
Agent is Running and Ready

How to Understand and Use ESTIMATE and ESTIMATE_ONLY Parameters in DataPump

Be aware LOB segment size is not included in the estimation size of datapump.

ESTIMATE Parameter

The value for parameter ESTIMATE is either BLOCKS (default) or STATISTICS.

BLOCKS: The estimated space used is calculated by multiplying the number of database blocks used by the target objects with the appropriate block sizes. It is the least accurate because of:

The table was created with a much bigger initial extent size than was needed for the actual table data
Many rows have been deleted from the table, or a very small percentage of each block is used.

STATISTICS: The estimated space used is calculated by using statistics for each table. If the table has been recently analyzed, the “estimate=statistics” would be the most accurate.

ESTIMATE_ONLY Parameter

Using estimate_only parameter will not generate the dump file other than a logfile .

The value for this parameter is either Y (yes) or N (no = default).

Y: Export estimates the space that would be consumed, but quits without actually performing the export operation.
N: Export does not only estimate, it performs an actual export of data, too.

BLOB EXCLUDED FROM ESTIMATION

If a table has LOBs, ESTIMATE dump file size does NOT take LOB size into consideration.

The table segment size is 34.43GB, and the blog segment size is 38.3GB. It seems the dadapump estimation size excludes blob segment size.

SQL> select bytes/1024/1024/1024 
       from dba_segments 
       where owner='TESTUSER' 
         and segment_name='TEST' 
         and segment_type='TABLE';

BYTES/1024/1024/1024
--------------------
 34.4365234

SQL> desc TESTUSER.TEST
 Name    Null?     Type
 ------- -------  ----------
 NAME    NOT NULL VARCHAR2(30)
 CREATED NOT NULL DATE
 ......
 ......
 DOC              BLOB
 ......
 ......


SQL> select owner,table_name,column_name,segment_name 
       from dba_lobs 
      where owner='TESTUSER' and table_name='TEST';

OWNER      TABLE_NAME  COLUMN_NAME       SEGMENT_NAME 
---------- ----------  ----------------  ------------------------
TESTUSER   TEST        SESSIONITEMLONG   SYS_LOB0000062845C00010$

SQL> select owner, segment_Name,bytes/1024/1024/1024 
       from dba_segments 
      where segment_name='SYS_LOB0000062845C00010$' 
        and owner='TESTUSER';

OWNER       SEGMENT_NAME             BYTES/1024/1024/1024
----------- ------------------------ ------------------
TESTUSER    SYS_LOB0000062845C00010$  38.3


$ expdp \"/ as sysdba\" directory=DATAPUMP_DIR ESTIMATE_ONLY=y ESTIMATE=BLOCKS tables=TESTUSER.TEST;

....
....
....
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. estimated "TESTUSER"."TEST" 35.50 GB
Total estimation using BLOCKS method: 35.50 GB
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 14:50:11

$expdp \"/ as sysdba\" directory=DATAPUMP_DIR ESTIMATE_ONLY=y ESTIMATE=STATISTICS tables=TESTUSER.TEST;
......
......
......
Estimate in progress using STATISTICS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. estimated "TESTUSER"."TEST" 28.96 GB
Total estimation using STATISTICS method: 28.96 GB
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 14:51:31

From the above test, we can see LOB segment size is excluded from estimation size.

How to Tell If Oracle Database is Standard Edition or Enterprise Edition

Standard Edition and Enterprise Edition License.

Standard Edition

$ sqlplus / as sysdba 

SQL*Plus: Release 11.2.0.4.0 Production on Fri Mar 4 14:13:15 2016

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

Enterprise Edition

$sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 3 20:16:33 2016

Copyright (c) 1982, 2011, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

ORA-02475: maximum cluster chain block count of 65534 has been exceeded

For 8k block size tablespace, maximum cluster chain block count is 65534. Otherwise bigger block size tablespace is recommended.

This article demonstrates how to reproduce ORA-02475 error , and explains why it happens and how to resolve this issue.

Create cluster tables and indexes.

SQL> CREATE CLUSTER emp_dept (deptno NUMBER(3)) TABLESPACE users;

Cluster created.

SQL> CREATE TABLE dept 
     (
        deptno NUMBER(3) PRIMARY KEY,
        deptname VARCHAR2(15) NOT NULL
     )
     CLUSTER emp_dept (deptno);

Table created.

SQL> CREATE TABLE emp 
     (
        empno NUMBER(10) PRIMARY KEY,
        ename VARCHAR2(15) NOT NULL,
       deptno NUMBER(3) REFERENCES dept
     )
     CLUSTER emp_dept (deptno);

Table created.

SQL> CREATE INDEX emp_dept_index
         ON CLUSTER emp_dept
         TABLESPACE users ;

Index created.

SQL> insert into dept values ( 1,'DEPT1 1');

1 row created.

SQL> insert into dept values (2,'DEPT1 2');

1 row created.

SQL> insert into dept values (3, 'DEPT1 3');

1 row created.

SQL> commit;

Commit complete.

2. Insert records into cluster table EMP until gets ORA-02475 error.

SQL> set serveroutput on;
begin
    for i in 1 .. 1000000000
    loop
       begin
           insert into emp values (i,'emp '||i, 3);
           commit;
       exception
       when others
       then
           dbms_output.put_line( SQLERRM );
          exit;
       end;
   end loop;
end;
/

ORA-02475: maximum cluster chain block count of 65534 has been exceeded

PL/SQL procedure successfully completed.

Subscribe to get access

Read more of this content when you subscribe today.