Month: April 2024

How to Drop an Orphan LOB in Oracle Database

In Oracle 11.2.0.3 database, after dropped some tables, there are still lob objects in dba_objects and dba_segments.

Find the table name the lob belongs to:

SQL> show parameter recyclebin

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------------------
recyclebin                           string      on
SQL> select u.name, o.name TABLENAME, decode(bitand(c.property, 1), 1, ac.name, c.name) as column_name
  from sys.obj$ o, sys.col$ c, sys.attrcol$ ac,sys.lob$ l,sys.obj$ lo,sys.obj$ io,
sys.user$ u,sys.ts$ ts
 where o.owner# = u.user#
and o.obj# = c.obj#
 and c.obj# = l.obj# and c.intcol# = l.intcol#
and l.lobj# = lo.obj# and l.ind# = io.obj# and l.ts# = ts.ts# and c.obj# =
ac.obj#(+)
 and c.intcol# = ac.intcol#(+) and lo.name ='SYS_LOB0000542255C00004$$';

NAME                           TABLENAME
------------------------------ ------------------------------
COLUMN_NAME
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
APP_USER                    BIN$FvVDUKyLUJLgYyshAgoDnQ==$0
DATATBS
SQL>  purge table APP_USER."BIN$FvVDUKwcUJLgYyshAgoDnQ==$0";

Table purged.

The the lob will dropped successfully.

In order to avoid this problem, for databases with recyclebin turned ON, we can:

SQL> drop table APP_USER.TABLENAME PURGE;

Can you delete a service still with connections in Oracle database

Service created from srvctl

SQL> select count(*) from gv$session where service_name='test';

  COUNT(*)
----------
         1

SQL> select inst_id,SERVICE_ID,NAME,NAME_HASH,NETWORK_NAME,CREATION_DATE from gv$services  where NAME='test';

 INST_ID SERVICE_ID NAME    NAME_HASH NETWORK_NA CREATION_DATE
---------- ---------- ------ ---------- ---------- --------------------
         1          4 test   3237892936 test       19-APR-2024 13:43:50
SQL> exec dbms_service.DELETE_SERVICE('test');
BEGIN dbms_service.DELETE_SERVICE('test'); END;

*
ERROR at line 1:
ORA-44305: service test is running
ORA-06512: at "SYS.DBMS_SERVICE", line 68
ORA-06512: at "SYS.DBMS_SERVICE", line 458
ORA-06512: at line 1
$ srvctl remove service -s test -d testdb
PRCR-1025 : Resource ora.testdb.test.svc is still running

$ srvctl stop service -s test -d testdb

SQL>  exec dbms_service.DELETE_SERVICE('test');

PL/SQL procedure successfully completed.


SQL> select count(*) from gv$session where service_name='test';

  COUNT(*)
----------
         1

So we can delete a service only when the service is down, but the current connections are still kept.

Service created from dbms_service

SQL>  exec  DBMS_SERVICE.CREATE_SERVICE('test','test');

PL/SQL procedure successfully completed.

SQL>  exec  DBMS_SERVICE.start_service('test');

PL/SQL procedure successfully completed.

SQL>select inst_id,SERVICE_ID,NAME,CREATION_DATE,CON_NAME from gv$active_services;
  INST_ID SERVICE_ID NAME                                                             CREATION_DATE        CON_NAME
---------- ---------- ---------------------------------------------------------------- -------------------- ----------
...
.
 2          4 test                                                             19-APR-2024 14:40:45 pdb1
SQL>  exec dbms_service.DELETE_SERVICE('test');
BEGIN dbms_service.DELETE_SERVICE('test'); END;

*
ERROR at line 1:
ORA-44305: service test is running
ORA-06512: at "SYS.DBMS_SERVICE", line 68
ORA-06512: at "SYS.DBMS_SERVICE", line 458
ORA-06512: at line 1


SQL>  exec dbms_service.STOP_Service('test');
PL/SQL procedure successfully completed.

SQL>  exec dbms_service.DELETE_SERVICE('test');
PL/SQL procedure successfully completed.

SQL> select count(*) from gv$session where service_name='test';

  COUNT(*)
----------
         2

So we can delete a service only when the service is down, but the current connections are still kept.

emctl status agent : Status agent Failure:unable to connect to http server at [handshake has no peer]

OEM agent is unhealthy with the following errors:

$ emctl status agent
Oracle Enterprise Manager Cloud Control 13c Release 4
Copyright (c) 1996, 2020 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
Status agent Failure:unable to connect to http server at https://racnode1:3872/emd/lifecycle/main/. [handshake has no peer]

$ emctl stop agent
Oracle Enterprise Manager Cloud Control 13c Release 4
Copyright (c) 1996, 2020 Oracle Corporation.  All rights reserved.

$ emctl pingOMS
Oracle Enterprise Manager Cloud Control 13c Release 4
Copyright (c) 1996, 2020 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
EMD pingOMS error: unable to connect to http server at https://racnode1:3872/emd/main/. [handshake has no peer]

Error stack observed from <Agent_Inst>/sysman/log/emagent.nohup file:

—– 2024-04-13 10:03:14,913::8313::Checking status of EMAgent : 10965 —–
—– 2024-04-13 10:03:14,913::8313::Hang detected for EMAgent : 10965 —–
—– 2024-04-13 10:03:14,913::8313::Debugging component EMAgent —–
—– 2024-04-13 10:03:14,913::generate first thread dump file for diagnosis —–
—– 2024-04-13 10:03:27,394::generate second thread dump file for diagnosis —–
—– 2024-04-13 10:03:27,592::generate Threads.10965lsof.1 for diagnosis —–
—– Attempting to kill EMAgent : 10965 —–
—– 2024-04-13 10:03:32,660::8313::EMAgent exited at 2024-04-13 10:03:32,660 with signal 9 —–
—– 2024-04-13 10:03:32,660::8313::EMAgent either hung or in abnormal state. —–
—– 2024-04-13 10:03:32,660::8313::EMAgent will be restarted/thrashed. —–
—– 2024-04-13 10:03:32,660::8313::writeAbnormalExitTimestampToAgntStmp: exitCause=ABNORMAL : restartRequired=1 —–
—– 2024-04-13 10:03:32,660::8313::Restarting EMAgent. —–
—– 2024-04-13 10:03:32,875::8313::Auto tuning the agent at time 2024-04-13 10:03:32,875 —–
—– 2024-04-13 10:03:40,237::8313::Finished auto tuning the agent at time 2024-04-13 10:03:40,237 —–
—– 2024-04-13 10:03:40,238::8313::Launching the JVM with following options: -Xmx128M -XX:MaxMetaspaceSize=224M -server -Djava.security.egd=file:///dev/./urandom -Dsun.lang.ClassLoader.allowArraySyntax=true -XX:-UseLargePages -XX:+UseLinuxPosixThreadCPUClocks -XX:+UseConcMarkSweepGC -XX:+CMSClassUnloadingEnabled -XX:+UseCompressedOops -DHTTPClient.dontSeekTerminatingChunk=true —–
—– 2024-04-13 10:03:40,239::8313::Agent Launched with PID 81582 at time 2024-04-13 10:03:40,239 —–
—– 2024-04-13 10:03:40,239::81582::Execing EMAgent process is taking longer than expected 120 secs —–
—– 2024-04-13 10:03:40,239::81582::Time elapsed between Launch of Watchdog process and execing EMAgent is 727958 secs —–

SOLUTION

1. Take the backup of the <Agent_Inst>/sysman/config/emd.properties file.

2. Update the following property as below:

agentJavaDefines=-Xmx128M -XX:MaxMetaspaceSize=224M

TO

agentJavaDefines=-Xmx512M -XX:MaxMetaspaceSize=224M

3. Save the file.

4. Try restarting the agent.
$ emctl stop agent
$ emctl start agent