Deinstalling Oracle Enterprise Manager 13c

Fortunatelly, deinstalling OEM 13c can be done in one command.

1. The middleware home will be removed after successful deinstall. So copy the deinstall script to a temporary location:

$ cp /u01/app/oracle/middleware/sysman/install/EMDeinstall.pl /home/oracle/

2.  Run deinstall script.

Please note that I am using -force option because, I have played with repository database before deinstalling EM, so my SYSMAN schema is broken. In case your EM is up and running, then you don’t have to indicate -force option.

$ cd /home/oracle

$ perl EMDeinstall.pl -mwHome /u01/app/oracle/middleware -stageLoc /home/oracle/ -force

Refer to /home/oracle/deinstall_2018-12-25_12-02-37.log for deinstall log
Substring valuye is /u01/app/oracle/gc_inst
This is a First OMS install. So, this deinstalls the OMS , Repository and Agent. Confirm (y/n)y
User confirmed for deinstallation.

Enter the SYS Password :
Enter the sysman Password :
Enter the Admin Server password :
dbConnectStr – (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=stbyracq.example.com)(PORT=1521)))(CONNECT_DATA=(SID=emrep)))

The command is /u01/app/oracle/middleware/bin/emctl stop oms

Stopping oms………… Wait for the completion of the execution.
Oracle Enterprise Manager Cloud Control 13c Release 3
Copyright (c) 1996, 2018 Oracle Corporation. All rights reserved.
Stopping Oracle Management Server…
Oracle Management Server Already Stopped
Oracle Management Server is Down
JVMD Engine is Down
return value is : 0

The command is /u01/app/oracle/middleware/bin/omsca delete -full -OMSNAME EMGC_OMS1 -AS_USERNAME weblogic -REP_CONN_STR “(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=stbyracq.example.com)(PORT=1521)))(CONNECT_DATA=(SID=emrep)))” -force

The oms delete will take sometime. Wait for the completion of the execution. Don’t abort the execution.

log4j:WARN No appenders could be found for logger (emctl.secure.oms.SecureOMSCmds).
log4j:WARN Please initialize the log4j system properly.
Oracle Enterprise Manager Cloud Control 13c Release 13.3.0.0.0
Copyright (c) 1996, 2018, Oracle. All rights reserved.
Cannot determine if BI Publisher is configured with Enterprise Manager: JPS-01030: Cannot get credential. Reason oracle.security.jps.service.policystore.PolicyStoreException: javax.persistence.PersistenceException: Exception [EclipseLink-4002] (Eclipse Persistence Services – 2.5.2.v20140319-9ad6abd): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: weblogic.jdbc.extensions.PoolDisabledSQLException: weblogic.common.resourcepool.ResourceDisabledException: Pool opss-data-source is Suspended, cannot allocate resources to applications..
Error Code: 0
Call: SELECT t1.JPS_ATTRS_ID, t1.ATTRNAME, t1.ATTRVAL, t1.JPS_DN_ENTRYID, t1.RFU1, t1.RFU2 FROM JPS_DN t0, JPS_ATTRS t1 WHERE (((t1.JPS_DN_ENTRYID = t0.ENTRYID) AND (t0.RDN = ?)) AND (t0.PARENTDN = ?))
bind => [2 parameters bound]
Query: ReadAllQuery(referenceClass=JpsStore sql=”SELECT t1.JPS_ATTRS_ID, t1.ATTRNAME, t1.ATTRVAL, t1.JPS_DN_ENTRYID, t1.RFU1, t1.RFU2 FROM JPS_DN t0, JPS_ATTRS t1 WHERE (((t1.JPS_DN_ENTRYID = t0.ENTRYID) AND (t0.RDN = ?)) AND (t0.PARENTDN = ?))”).
OMSCA-WRN:Enterprise Manager repository database is down. The system cannot clean up the OMS from the repository
OMS Deleted successfully

return value is : 0
Running the command /u01/app/oracle/middleware/sysman/admin/emdrep/bin/RepManager -action drop -dbUser sys -dbRole sysdba -connectString “(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=stbyracq.example.com)(PORT=1521)))(CONNECT_DATA=(SID=emrep)))” -mwHome /u01/app/oracle/middleware -mwOraHome /u01/app/oracle/middleware -oracleHome /u01/app/oracle/middleware

Repository drop will take sometime. Wait for the completion of the execution. Don’t abort the execution.

stty: standard input: Inappropriate ioctl for device
stty: standard input: Inappropriate ioctl for device
stty: standard input: Inappropriate ioctl for device
stty: standard input: Inappropriate ioctl for device
processing arguments
compiling arguments for validation
Enter sys user password :
Verify :
Performing PreDropAll action…
Enter password for: sys process_id:1eqaqmmifkgo4
null
Done PreDropAll action…
Dropping BIP schema…
Enter sys user password :
Verify :
Action on BIP schema succeed.
Dropping APM schema… Component is already dropped Enter sysdba password:
Dropping OPSS schema… Component is already dropped Enter sysdba password:
Dropping SYSMANUPGR_OPSS schema… Component is already dropped Enter sysdba password:
Dropping STB schema… Processing command line …. Enter sysdba password:
Repository Creation Utility – Checking Prerequisites
Checking Global Prerequisites
Repository Creation Utility – Checking Prerequisites
Checking Component Prerequisites
Repository Creation Utility – Drop
Repository Drop in progress.
Percent Complete: 9
Percent Complete: 9
Percent Complete: 9
Percent Complete: 18
Percent Complete: 47
Percent Complete: 100
Repository Creation Utility: Drop – Completion Summary
Database details:
Connect Descriptor : (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=stbyracq.example.com)(PORT=1521)))(CONNECT_DATA=(SID=emrep)))
Connected As : sys
Prefix for (prefixable) Schema Owners : SYSMAN
RCU Logfile : /u01/app/oracle/middleware/cfgtoollogs/cfgfw/emsecrepmgr.log
Component schemas dropped:
Component Status Logfile
Common Infrastructure Services Success /u01/app/oracle/middleware/cfgtoollogs/cfgfw/stb.log

Repository Creation Utility – Drop : Operation Completed
Successfully dropped schema
Dropping MDS schema…
Enter sys user password :
Verify :
Action on MDS schema succeed.
Enter password for: sys process_id:1plaza1hs1ba
Processing command line ….
Repository Creation Utility – Checking Prerequisites
Checking Global Prerequisites
Repository Creation Utility – Checking Prerequisites
Checking Component Prerequisites
Repository Creation Utility – Drop
Repository Drop in progress.
Repository Creation Utility: Drop – Completion Summary
Database details:
Connect Descriptor : (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=stbyracq.example.com)(PORT=1521)))(CONNECT_DATA=(SID=emrep)))
Connected As : sys
RCU Logfile : /u01/app/oracle/middleware/sysman/log/schemamanager/m_122518_1204_PM/m_122518_1204_PM.DROP/rcu.log
Component schemas dropped:
Component Status Logfile
EM Repository Drop Success /u01/app/oracle/middleware/sysman/log/schemamanager/m_122518_1204_PM/m_122518_1204_PM.DROP/em_repos_drop.log

Repository Creation Utility – Drop : Operation Completed
Performing PostDropAll action…
Enter password for: sys process_id:10wli8gmmbnqm
drop completed successfully
return value is : 0
The command executed is /u01/app/oracle/middleware/oui/bin/detachHome.sh
Launcher log file is /tmp/OraInstall2018-12-25_12-05-42PM/launcher2018-12-25_12-05-42PM.log.
Starting Oracle Universal Installer

Checking swap space: must be greater than 500 MB. Actual 0 MB Failed <<<<
Checking if this platform requires a 64-bit JVM. Actual 64 Passed (64-bit not required)

Some system prerequisite checks failed.
You must fulfill these requirements before continuing.
The log is located here: /tmp/OraInstall2018-12-25_12-05-42PM/launcher2018-12-25_12-05-42PM.log.
return value is : 256
The detachHome of /u01/app/oracle/middleware allDepHomes has failed. Rectify the error and try again

Deleting the instance home
Deleting the em home
The deinstallation of OMS is successful.

The location of the file is : /etc/oragchomelist

Configure BI publisher in Enterprise Manager 13c

You can configure BI publisher during the EM13c installation phase. If you have choosen not to configure BI publisher, then you can enable/configure it by the following way.

Check the status of BI publisher:

$ emctl status oms -bip_only
Oracle Enterprise Manager Cloud Control 13c Release 3
Copyright (c) 1996, 2018 Oracle Corporation. All rights reserved.
BI Publisher Server is Down
BI Publisher is disabled, to enable BI Publisher on this host, use the ’emctl config oms -enable_bip’ command

Enable BI Publisher:

$ emctl config oms -enable_bip
Oracle Enterprise Manager Cloud Control 13c Release 3
Copyright (c) 1996, 2018 Oracle Corporation. All rights reserved.
Enter Enterprise Manager Root (SYSMAN) Password :
Starting BI Publisher Server …
WebTier Successfully Started
BI Publisher Server Could Not Be Started. Check BIP Server log file for details.
BI Publisher Server Logs: /u01/app/oracle/gc_inst/user_projects/domains/GCDomain/servers/BIP/logs/
BI Publisher Server is Down
BI Publisher is enabled for startup on this host with the ’emctl start oms’ and ’emctl start oms -bip_only’ commands.
Overall result of operations: SUCCESS

Start the publisher:

$ emctl start oms -bip_only
Oracle Enterprise Manager Cloud Control 13c Release 3
Copyright (c) 1996, 2018 Oracle Corporation. All rights reserved.
Starting BI Publisher Server only.
Starting BI Publisher Server …
WebTier Successfully Started
BI Publisher Server Successfully Started
BI Publisher Server is Up

Check the status again:

$ emctl status oms -bip_only
Oracle Enterprise Manager Cloud Control 13c Release 3
Copyright (c) 1996, 2018 Oracle Corporation. All rights reserved.
BI Publisher Server is Up

The “visiblepw” is not set the sudoers file and as a result, the user will not be able to run sudo over ssh

Problem:

During Agent Deployment from EM, we get the following warning:

Solution:

Change the entry “Defaults !visiblepw” to “Defaults visiblepw” in the /etc/sudoers file and re-try Agent Deployment.

SYS_OP_C2C internal function, implicit data type converstion

Our database is heavily loaded. In addition, developers are writing codes that make even Exadata to hang 🙂  They are the best hackers ever :):)

Investigating database performance using 13c Cloud Control found one very interesting SQL.
At glance everything is fine, but there is one thing that is important and makes SQL  heavy.

Top SQL:

SELECT nvl(max(bl.id), 0) 
FROM schemaname.eb_restriction_balance bl 
WHERE bl.restrictcode = :b1

SCHEMANAME.EB_RESTRICTION_BALANCE table structure:

SQL> DESC schemaname.eb_restriction_balance

Name Type Nullable Default Comments 
-------------- ------------ -------- ------- -------- 
ID NUMBER 
RESTRICTCODE VARCHAR2(30) 
CURRENCY VARCHAR2(3) 
RESTRICTAMOUNT NUMBER(14) 0 
BALANCE NUMBER(14) 0 
STATE NUMBER(5) 5 
INSERTDATE DATE sysdate 
UPDATEDATE DATE Y 
INN VARCHAR2(30) Y

RESTRICTCODE column is indexed.

So in ideal way the above select should use the index on RESTRICTCODE.

Cloud Control shows that CBO chooses TABLE ACCESS STORAGE FULL.

You can run SQL Tuning Advisor from Cloud Control easily. Advisor generated the following recommendation:

The execution plan of this statement can be improved by creating one or more indices. Consider running the Access Advisor to improve the physical schema design or creating the recommended index.schemaname.EB_RESTRICTION_BALANCE(SYS_OP_C2C(“RESTRICTCODE”))

SYS_OP_C2C means that there happened implicit data type conversion.
So we must find the exact bind value that was used at the time sql was run.

SQL> SELECT name, datatype_string, value_string
 2 FROM v$sql_bind_capture
 3 WHERE sql_id='dnb1771sbm98x';

NAME DATATYPE_STRING VALUE_STRING
------- ------------------ -------------
:B1 NVARCHAR2(128) BR16215493
:B1 NVARCHAR2(128) BR16213680

As you see bind value type was NVARCHAR, that is why oracle converted varchar2 to nvarchar2 and did not use index on RESTRICTCODE.

Solution is to make developer change code and pass the parameter with the type varchar2.

 

Oracle Management Server is Down – Failed to push ldap config data to libOvd for service instance “idstore.ldap”

I have setup Oracle Cloud Control 13c on virtual machine.

Yesterday machine was shut downed forcibly and Cloud Control was unable to start up after that.

[oracle@oragc ~]$ /u01/app/oracle/middleware/bin/emctl start oms
Oracle Enterprise Manager Cloud Control 13c Release 1
Copyright (c) 1996, 2015 Oracle Corporation. All rights reserved.
Starting Oracle Management Server...
WebTier Successfully Started
Oracle Management Server Could Not Be Started
Check EM Server log file for details: /u01/app/oracle/gc_inst/user_projects/domains/GCDomain/servers/EMGC_OMS1/logs/EMGC_OMS1.out
AdminServer Could Not Be Started
Check Admin Server log file for details: /u01/app/oracle/gc_inst/user_projects/domains/GCDomain/servers/EMGC_ADMINSERVER/logs/EMGC_ADMINSERVER.out
Oracle Management Server is Down
JVMD Engine is Down
Starting BI Publisher Server ...
BI Publisher Server Could Not Be Started. Check BIP Server log file for details.
BI Publisher Server Logs: /u01/app/oracle/gc_inst/user_projects/domains/GCDomain/servers/BIP/logs/
AdminServer Could Not Be Started
Check Admin Server log file for details: /u01/app/oracle/gc_inst/user_projects/domains/GCDomain/servers/EMGC_ADMINSERVER/logs/EMGC_ADMINSERVER.out
BI Publisher Server is Down

Let’s check the following log:

[oracle@oragc log]$ tailf /u01/app/oracle/gc_inst/user_projects/domains/GCDomain/servers/EMGC_ADMINSERVER/logs/EMGC_ADMINSERVER.out

SEVERE: Failed to push ldap config data to libOvd for service instance "idstore.ldap" in JPS context "default", 
cause: org.xml.sax.SAXException: Error Parsing at line #210: 38.org.xml.sax.SAXParseException; lineNumber: 210; 
columnNumber: 38; <Line 210, Column 38>: XML-20210: (Fatal Error) Unexpected EOF.
Jan 03, 2017 2:02:17 PM oracle.security.opss.internal.runtime.ServiceContextManagerImpl createContextInternal
WARNING: Service instance "idstore.ldap" cannot be instantiated. 
Reason: oracle.security.jps.service.idstore.IdentityStoreException: 
JPS-02592: Failed to push ldap config data to libOvd for service instance "idstore.ldap" in JPS context "default",
cause: org.xml.sax.SAXException: Error Parsing at line #210: 38.org.xml.sax.SAXParseException; 
lineNumber: 210; columnNumber: 38; <Line 210, Column 38>: XML-20210: (Fatal Error) Unexpected EOF..
Jan 03, 2017 2:02:17 PM oracle.security.jps.internal.config.OpssCommonStartup start
INFO: Jps startup failed.
<Jan 3, 2017 2:02:17 PM GET> <Error> <Security> 
<BEA-090892> <The loading of an OPSS java security policy provider failed due to an exception. 
See the exception stack trace or the server log file for the root cause. 
If there is no obvious cause, enable the debug flag -Djava.security.debug=jpspolicy to get more information. 
Error message: null>

I have found the solution on Oracle Community site: https://community.oracle.com/thread/2375509

“Please follow below steps to get you domain up and running
1) Take a backup of $DOMAIN_HOME/config/fmwconfig/ovd/default/adapters.os_xml

2) Delete $DOMAIN_HOME/config/fmwconfig/ovd/default/adapters.os_xml

3) copy adapters.os_xml from $MW_HOME/oracle_common/modules/oracle.ovd_11.1.1/templates/ to $DOMAIN_HOME/config/fmwconfig/ovd/default/

4) Restart the server.”

After performing the above steps:

[oracle@oragc ~]$ /u01/app/oracle/middleware/bin/emctl start oms
Oracle Enterprise Manager Cloud Control 13c Release 1
Copyright (c) 1996, 2015 Oracle Corporation. All rights reserved.
Starting Oracle Management Server...
WebTier Successfully Started
Oracle Management Server Successfully Started
Oracle Management Server is Up
JVMD Engine is Up
Starting BI Publisher Server ...
BI Publisher Server Successfully Started
BI Publisher Server is Up

CHANGE SYSMAN PASSWORD ON EM13C

You will need sys password to reset sysman password.

If you don’t remember sys password you should already know how to change it from repository server… If you still thinking , ok then let’s write this step also:

  1. Connect to the database(repository) server via ssh.
  2. Describe ORACLE_SID and ORACLE_HOME variables.
  3. Connect to the database without password and with sysdba privilege:

    sqlplys / as sysdba

  4. Change sys user password:

    alter user sys identified by <sys password>;


1. Stop all OMS processes

cd <oms home>/bin
emctl stop oms

2. Change the SYSMAN password:

cd <oms home>/bin
emctl config oms -change_repos_pwd -use_sys_pwd -sys_pwd <sys password> -new_pwd <new sysman password>

3. Stop the Admin Server on the primary OMS and restart OMS:

cd <oms home>/bin
emctl stop oms -all
emctl start oms

4. Check that oms is up and running:

cd <oms home>/bin
emctl status oms -details

That’s it.