Thursday, October 13, 2016

Manually deinstall EM12C agent

The procedure below will use the Installer to remove the agent software from a host.
Before this step you have to use the OMS 12c console to remove the monitored targets and the Agent from OMS. Alternatively this can be done with the emcli tool , but this is not the scope of the post.

ACTIONS

Login the configured oracle user on the host and check if EM agent is running:

output may be different depending your setup !!

$ ps -ef | grep emagent.nohup
oracle    4006     1  0 Aug30 ?        00:00:01 /u01/app/oracle/product/agent12c/core/12.1.0.2.0/perl/bin/perl /u01/app/oracle/product/agent12c/core/12.1.0.2.0/bin/emwd.pl agent /u01/app/oracle/product/agent12c/agent_inst/sysman/log/emagent.nohup
oracle   16962 10385  0 12:30 pts/0    00:00:00 grep emagent.nohup

Agent is running so you need to stop it.

The highlighted directory is the AGENT_HOME !!

$ /u01/app/oracle/product/agent12c/core/12.1.0.2.0/bin/emctl stop agent
......

Then you have to invoke the installer to remove the software.

$ /u01/app/oracle/product/agent12c/core/12.1.0.2.0/oui/bin/runInstaller -deinstall ORACLE_HOME=/u01/app/oracle/product/agent12c -removeallfiles

After the installer starts you have to follow a specific order to safely remove the software:

1. First remove the plugin homes
They are usually named OraHome[X] where X=1,2... , by selecting the in the installer and press remove

2. Then remove the oracle_sysman_db_....agent_Home0 (if exists)

3. Then remove the sbin12C1 home

4. Finally remove the agent12c1 home

ATTENTION : The names for the previous homes may be different in your setup

Close the installer and then remove the agent base directory to cleanup the leftovers

$ cd /u01/app/oracle/product
$ rm -fr agent12c


Wednesday, August 31, 2016

ORA-20011 & KUP-11024 during DBMS_STATS: GATHER_STATS_JOB

ORA-20011: Approximate NDV failed: ORA-29913: error in executing ODCIEXTTABLEOPEN callout
KUP-11024: This external table can only be accessed from within a Data Pump job.

If you encounter these errors in the alert.log of an instance , usually the cause is that an OS file for an external table existed at some point in time but does not now, but the database still believes the OS file for the table exists.

When DBMS_STATS is run against the table in question, it makes a call out to the external table which fails. 

Error KUP-11024 is for temporary Datapump external tables that have not been cleaned up properly. 

Solution:

Ensure that there are no DataPump jobs running at the same time as the DBMS_STATS job run.

The run as sysdba:

1. SQL to identify the external table name and the owner:

select
    owner,
    object_name,
    object_type,
    status,
    to_char(created,'dd-mon-yyyy hh24:mi:ss') created ,
    to_char(last_ddl_time , 'dd-mon-yyyy hh24:mi:ss') last_ddl_time
from
    dba_objects
where
    object_name like 'ET$%' ;

"OWNER" "OBJECT_NAME" "OBJECT_TYPE" "STATUS" "CREATED" "LAST_DDL_TIME"
"SYSTEM" "ET$00F9E3ED0001" "TABLE" "VALID" "11-dec-2012 10:59:30" "11-dec-2012 10:59:30"

2. SQL to check the dirctory the DB thinks the external table is

select owner, TABLE_NAME, DEFAULT_DIRECTORY_NAME, ACCESS_TYPE
from dba_external_tables
ORDER BY 1,2
;

3. Drop the table 

drop table SYSTEM.ET$00F9E3ED0001 purge;