Blog Feed

OEM 13c and Amazon’s RDS

Amazon released a note late last year about Amazon’s RDS is now supporting Oracle Enterprise Manager Agents.
Amazon RDS supports agent version 12 (12.1.0.5), 13R1 and 13R2 (13.2.0.0).
You can use this to monitor your DB instances running on Amazon’s RDB but there are some limitations, like the fact you cannot execute jobs against those targets.
See below for more information about this.
Thanks,
Alfredo

OEM 13C Useful EMCTL Agent Commands

Here’s a list of some useful emctl commands to manage your OEM 13c agent.
Get the status of the agent:
$ emctl status agent
Oracle Enterprise Manager Cloud Control 13c Release 2
Copyright (c) 1996, 2016 Oracle Corporation.  All rights reserved.
—————————————————————
Agent Version          : 13.2.0.0.0
OMS Version            : 13.2.0.0.0
Protocol Version       : 12.1.0.1.0
Last Reload            : (none)
Last successful upload                       : 2018-05-15 09:04:32
Last attempted upload                        : 2018-05-18 13:12:29
Total Megabytes of XML files uploaded so far : 13.6
Number of XML files pending upload           : 1,956
Size of XML files pending upload(MB)         : 3.64
Available disk space on upload filesystem    : 81.90%
Collection Status                            : Collections enabled
Heartbeat Status                             : OMS is unreachable [not running]
Last attempted heartbeat to OMS              : 2018-05-18 13:12:02
Last successful heartbeat to OMS             : 2018-05-15 09:10:00
Next scheduled heartbeat to OMS              : 2018-05-18 13:12:44
—————————————————————
Agent is Running and Ready
Get the list of targets currently monitored by the agent:
$ emctl config agent listtargets
Oracle Enterprise Manager Cloud Control 13c Release 2
Copyright (c) 1996, 2016 Oracle Corporation.  All rights reserved.
[localhost.localdomain.com, host]
[localhost.localdomain.com:3872, oracle_emd]
[Management Services and Repository, oracle_emrep]
[/EMGC_GCDomain/GCDomain/EMGC_OMS1, weblogic_j2eeserver]
[NodeManager_localhost.localdomain.com_1, weblogic_nodemanager]
[oms13c1_1_localhost.localdomain.com_4754, oracle_home]
Get the status of a particular target:
$ emctl status agent target /EMGC_GCDomain/GCDomain/EMGC_OMS1,weblogic_j2eeserver
Oracle Enterprise Manager Cloud Control 13c Release 2
Copyright (c) 1996, 2016 Oracle Corporation.  All rights reserved.
—————————————————————
Target Name : /EMGC_GCDomain/GCDomain/EMGC_OMS1
Target Type : weblogic_j2eeserver
Current severity state
———————-
Metric        Column name             Key             State           Timestamp
——————————————————————————–
Response      Status                  n/a             CRITICAL        Fri May 18 13:13:25 EDT 2018
alertLogAdrIncident adr_problemKey          Fri Jun 23 16:07:21 2017/254 CRITICAL        Fri Jun 23 16:07:58 EDT 2017
alertLogAdrIncident adr_problemKey          Fri Jun 23 16:08:10 2017/263 CRITICAL        Fri Jun 23 16:12:58 EDT 2017
alertLogAdrIncident adr_problemKey          Mon Jul 10 06:06:34 2017/290 CRITICAL        Mon Jul 10 06:08:52 EDT 2017
alertLogAdrIncident adr_problemKey          Mon Jun 26 13:04:51 2017/272 CRITICAL        Mon Jun 26 13:08:48 EDT 2017
alertLogAdrIncident adr_problemKey          Tue Jun 27 17:50:54 2017/281 CRITICAL        Tue Jun 27 17:53:48 EDT 2017
jvm           heapUsedPercentage.value n/a             CLEAR           Tue May 15 09:07:27 EDT 2018
jvm_threads   deadlockedThreadCount.value n/a             CLEAR           Tue May 15 09:06:15 EDT 2018
—————————————————————
Agent is Running and Ready
Clear the current status of a target’s metrics:
$ emctl clearstate agent /EMGC_GCDomain/GCDomain/EMGC_OMS1,weblogic_j2eeserver
Oracle Enterprise Manager Cloud Control 13c Release 2
Copyright (c) 1996, 2016 Oracle Corporation.  All rights reserved.
Clear target severity state
Now they show as undefined until the next collection happens:
$ emctl status agent target /EMGC_GCDomain/GCDomain/EMGC_OMS1,weblogic_j2eeserver
Oracle Enterprise Manager Cloud Control 13c Release 2
Copyright (c) 1996, 2016 Oracle Corporation.  All rights reserved.
—————————————————————
Target Name : /EMGC_GCDomain/GCDomain/EMGC_OMS1
Target Type : weblogic_j2eeserver
Current severity state
———————-
Metric        Column name             Key             State           Timestamp
——————————————————————————–
Response      Status                  n/a             UNDEFINED       Fri May 18 13:14:25 EDT 2018
alertLogAdrIncident adr_problemKey          Fri Jun 23 16:07:21 2017/254 UNDEFINED       Fri Jun 23 16:07:58 EDT 2017
alertLogAdrIncident adr_problemKey          Fri Jun 23 16:08:10 2017/263 UNDEFINED       Fri Jun 23 16:12:58 EDT 2017
alertLogAdrIncident adr_problemKey          Mon Jul 10 06:06:34 2017/290 UNDEFINED       Mon Jul 10 06:08:52 EDT 2017
alertLogAdrIncident adr_problemKey          Mon Jun 26 13:04:51 2017/272 UNDEFINED       Mon Jun 26 13:08:48 EDT 2017
alertLogAdrIncident adr_problemKey          Tue Jun 27 17:50:54 2017/281 UNDEFINED       Tue Jun 27 17:53:48 EDT 2017
jvm           heapUsedPercentage.value n/a             UNDEFINED       Tue May 15 09:07:27 EDT 2018
jvm_threads   deadlockedThreadCount.value n/a             UNDEFINED       Tue May 15 09:06:15 EDT 2018
—————————————————————
Agent is Running and Ready
Force the agent to collect dynamic properties:
$ emctl reload agent dynamicproperties /EMGC_GCDomain/GCDomain/EMGC_OMS1:weblogic_j2eeserver
Oracle Enterprise Manager Cloud Control 13c Release 2
Copyright (c) 1996, 2016 Oracle Corporation.  All rights reserved.
—————————————————————
EMD recompute dynprops completed successfully
Thanks,
Alfredo

Oracle EBS 12.2 EBS Technology Codelevel Checker ETCC

Looks like this is my first post about Oracle EBS. Yeah, looks like now I’m more on the dark side of the force.
This is the time for ETCC. ETCC is a set of 2 scripts. One is going to check the recommended patches for the DB stack and the other one is going to check for the recommended patches for the Middleware stack.
It does not connect to the internet to verify the latest patches. So you must verify and download the latest version of this (patch 17537119).
The scripts are:
– checkDBpatch.sh
– checkMTpatch.sh
You just execute these scripts and provide the information that is being asked. They are going to spool a report like list with the recommended patches for your stack.
Keep in mind that they make use of utilities like OPatch, so is recommended you have the latest version in your DB Oracle Home.
Identifying database release.
  Database release set to 12.1.0.2.
  Connecting to database.
  Database connection successful.
  Checking for DB-ETCC results table.
  Table to store DB-ETCC results already exists in the database.
  Checking if InMemory option is enabled.
  Obtained list of bugfixes to be applied and the list to be rolled back.
  Now checking Database ORACLE_HOME.
  The opatch utility is at the required version.
  Found patch records in the inventory.
    Missing Bugfix: 11111111  ->  Patch 12345678
    Missing Bugfix: 22222222  ->  Patch 45678912
    Missing Bugfix: 33333333  ->  Patch 98765432
    Missing Bugfix: 44444444  ->  Patch 98765432
  Generating Patch Recommendation Summary.
  ================================================================================
  PATCH RECOMMENDATION SUMMARY
  ================================================================================
  The default patch recommendations to install these missing bugfixes are:
  ——————————————————————————–
  Oracle Database Release 12.1.0.2  (No PSU applied)
  ——————————————————————————–
    Patch 12345678
      – Filename: p12345678_121020_Linux-x86-64.zip
    Patch 45678912
      – Filename: p45678912_121020_Generic.zip
    Patch 98765432
      – Filename: p98765432_121020_Linux-x86-64.zip
  Apply the required patches and rerun this script.
As a side note, I noticed that with TXK Delta 10 if you try to start an ADOP cycle it is going to check if ETCC was recently executed and all the recommended patches are in place.
Looks like is some way to enforce that all the tech stack patches are current.
Thanks,
Alfredo

Enable NFS mount point monitoring in OEM 13c

OEM doesn’t monitor NFS mount points by default. In case you need to monitor NFS mount points you have 2 options.
Either create a metric extension using an OS script or to enable a property called EM_MONITOR_ALL_DISKS in the agent’s configuration file.
There are two ways to accomplish the second option. You can go and modify the emd.properties file or you can use the OEM console to modify it.
The OEM console can submit a job called “Agents Configuration Operation”. This job is going to prompt you for a name and the agents you want to modify the property. The Parameters tab has all the available parameters to modify. Just set the EM_MONITOR_ALL_DISKS to true and submit the job.

If you want to modify this manually. Just follow the instruction in the MOS note 1513537.1.
Thanks,
Alfredo

Exclusion not working in OEM 13c Rule Sets

While setting up Rules and RuleSets you may want to exclude either target types or alert categories from your notifications. Well, there’s an issue if you try to do this in OEM 13c.
If you configure a Rule that has the exclusion and contains more than one category, then you will keep receiving alerts from it. So the exclusion won’t work.
In order to fix it you need to apply a patch or the January 2018 BP for OEM.
MOS note (Doc ID 2347238.1) shall be used as a reference.
Thanks,
Alfredo

Install OMC Data Collector To Harvest Oracle Enterprise Manager Target’s Metric Data

In order to make use of the data you already have in your Oracle Enterprise Manager (OEM) in Oracle’s Management Cloud (OMC) you need to install and configure the OMC’s Data Collector agent.
Here’s how you do it.
Login to OMC and navigate to Administration -> Agents -> Download
In the Agent Type box, select Data Collector.

Select the desired Operating System version. In my case is Linux 64 bit.

Once the agent is downloaded, take a note in the same OMC’s web page of the TENANT_ID and the UPLOAD_ROOT values.
Next login to the server hosting your OEM Repository database.
Create a directory in a place where you have enough space (3GB).
$ cd /u01/agent
$ mkdir omc
$ cd omc
Transfer the downloaded file to this new directory and unzip it.
$ ls
datacollector_linux.x64_1.23.0.zip
$ unzip datacollector_linux.x64_1.23.0.zip
Archive:  datacollector_linux.x64_1.23.0.zip
  inflating: unzip
  inflating: AgentDeployment.sh
  inflating: agentimage.properties
  inflating: agent.rsp
  inflating: agent_software_build.xml
 extracting: agentcoreimage.zip
  inflating: AgentInstall.sh
Modify the response file adding the required values. This step depends in whether you’re using a Gateway or a Proxy to connect to the OMC.
$ vi agent.rsp
All required values can be found here.
Now execute the installation.
$ ./AgentInstall.sh
Unzipping agent software, this may take some time…
Installing Data Collector…
Data Collector parameter validation started…
Skipping Data Collector pre-requisite checks as IGNORE_VALIDATIONS is set to true…
Data Collector base directory creation started…
Security artifacts download started…
Data Collector setup started…
Registering Data Collector…
Starting Data Collector…
Data Collector started.
Data Collector installation completed.
The following configuration scripts need to be executed as the root user:
/bin/sh /u01/agent/omc/datacollector/core/1.23.0/root.sh
You now need to execute the configuration script as root.
Once this is executed, go and verify the Data Collector agent is up and running.
$ cd /u01/agent/omc/datacollector/agent_inst/bin
./omcli status agent
Oracle Management Cloud Data Collector
Copyright (c) 1996, 2017 Oracle Corporation.  All rights reserved.
—————————————————————
Version                : 1.23.0
Started at             : 2017-04-07 11:45:04
Started by user        : oracle
Operating System       : Linux version 4.1.17-12.3.5.el6uek.x86_64 (amd64)
Data Collector enabled : true
Sender Status          : FUNCTIONAL
Gateway Upload Status  : FUNCTIONAL
Last successful upload : 2017-04-07 11:46:28
Last attempted upload  : 2017-04-07 11:46:27
Pending Files (MB)     : 2.26
Pending Files          : 80
Backoff Expiration     : (none)
—————————————————————
Agent is Running and Ready
Verify the OMC’s Data Collector in the OMC console.
Navigate to Agents -> Administration -> Data Collectors
In this page you should see your newly installed Data Collector Agent.
Thanks,
Alfredo

_optimizer_distinct_placement

I remember seeing this hidden parameter in the past (11.2.0.3) and that had an issue when querying v$temp_extent_map view. 
This view returned wrong results if this parameter was set to true.
Now is a different story in 12.1.0.2. 
We started to see many ORA-600 [kkqcscpopnWithMap: 0] errors. 
Turns out is because of the same parameter.
MOS note “Query Errors With ORA-600 [kkqcscpopnWithMap: 0] (Doc ID 2260457.1)" gives a workaround and a patch to be applied.
After applying the patch no more errors in the alert log.

This patch is non-Data Guard Standby-First Installable

During a DB patching I found this note in the patch README file.
This patch is non-Data Guard Standby-First Installable - 
Please read My Oracle Support Note 1265700.1 
https://support.us.oracle.com/oip/faces/secure/km/DocumentDisplay.jspx?id=1265700.1
This is the first time I see this, so I was curious about this as Oracle recommends to apply the patches first to the standby database and then patch the primary database.
After reading the note id 1265700.1, found that we must shutdown both the primary and the standby and apply the patches at the same time.
This worked well and both databases were patched.
Thanks,
Alfredo

Oracle Database 12.2 New Features – SQL* Plus Enhancements

I want to start a series of posts about Oracle Database 12.2 new features this new year 2018.
There’s no better start of this series than to start with SQL*Plus.
SQL*Plus is probably one of the most utilized tools by DBA’s (sqlcl is gaining steam) and here I show some really cool new features.

SQL*Plus History

With this command HIST[ORY] and if turned on; you can run, edit, delete or list previously used SQL or PL/SQL commands for the current session.
  
 $ sqlplus sys as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Fri Jan 5 12:28:58 2018
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
Enter password:
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
SQL> show history
history is OFF
SQL> set history on
SQL> show history
history is ON and set to “100”
SQL> set history 1000
SQL> show history
history is ON and set to “1000”
SQL> show user
USER is “SYS”
SQL> desc dual
 Name                                                      Null?    Type
 —————————————– ——– —————————-
 DUMMY                                                                 VARCHAR2(1)
SQL> select * from dual;
D
X
SQL> hist
  1  show history
  2  show user
  3  desc dual
  4  select * from dual;
SQL> hist 4 run
D
X
SQL>

SQL*Plus SET MARKUP CSV

This setting is going to present the output is CVS format. 
SQL> set markup csv on
SQL> select * from emp;
“EMPNO”,”ENAME”,”JOB”,”MGR”,”HIREDATE”,”SAL”,”COMM”,”DEPTNO”
7839,”KING”,”PRESIDENT”,,”17-NOV-81″,5000,,10
7698,”BLAKE”,”MANAGER”,7839,”01-MAY-81″,2850,,30
7782,”CLARK”,”MANAGER”,7839,”09-JUN-81″,2450,,10
7566,”JONES”,”MANAGER”,7839,”02-APR-81″,2975,,20
7788,”SCOTT”,”ANALYST”,7566,”19-APR-87″,3000,,20
7902,”FORD”,”ANALYST”,7566,”03-DEC-81″,3000,,20
7369,”SMITH”,”CLERK”,7902,”17-DEC-80″,800,,20
7499,”ALLEN”,”SALESMAN”,7698,”20-FEB-81″,1600,300,30
7521,”WARD”,”SALESMAN”,7698,”22-FEB-81″,1250,500,30
7654,”MARTIN”,”SALESMAN”,7698,”28-SEP-81″,1250,1400,30
7844,”TURNER”,”SALESMAN”,7698,”08-SEP-81″,1500,0,30
7876,”ADAMS”,”CLERK”,7788,”23-MAY-87″,1100,,20
7900,”JAMES”,”CLERK”,7698,”03-DEC-81″,950,,30
7934,”MILLER”,”CLERK”,7782,”23-JAN-82″,1300,,10
14 rows selected.

SQL*Plus SET FEEDBACK ONLY

This option will display the number of rows selected without displaying the data. Useful to measure fetch time.
SQL> set feedback only
SQL> set timing on
SQL> select * from emp;
14 rows selected.
Elapsed: 00:00:00.01

SQL*Plus Performance Settings


SET ROWPREFECTH (default 1 | 2G max)
This setting pre-fetches rows in a result set. It can reduce the number of round trips between OCI execute calls.
I had an interesting question from a colleague about this setting. What is the difference between this ROWPREFECTH and ARRAYSIZE?
I’m still struggling to find the differences. Here are both definitions extracted from Oracle’s documentation:
SET ARRAYSIZE
Sets the number of rows that SQL*Plus will fetch from the database at one time. Valid values are 1 to 5000.
The effectiveness of setting ARRAYSIZE depends on how well Oracle Database fills network packets and your network latency and throughput. In recent versions of SQL*Plus and Oracle Database, ARRAYSIZE may have little effect. Overlarge sizes can easily take more SQL*Plus memory which may decrease overall performance.

SET ROWPREFETCH {1 | n}

Sets the number of rows that SQL*Plus will prefetch from the database at one time.
The default value is 1.
What I can see from here is that ARRAYSIZE takes place during the fetch step and probably ROWPREFECTH is just before the fetch step?
I even tried to identify this by using the autotrace option and by tracing a test session, but unfortunately I was not able to find any differences. Maybe the amount of data queried wasn’t large enough.
$ sqlplus scott/****
SQL*Plus: Release 12.2.0.1.0 Production on Fri Jan 5 13:18:41 2018
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
Last Successful login time: Fri Jan 05 2018 13:15:53 -05:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
SQL> set autotrace on
SQL> select * from emp;
14 rows selected.
Execution Plan
———————————————————-
Plan hash value: 3956160932
————————————————————————–
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time    |
————————————————————————–
|   0 | SELECT STATEMENT  |         |    14 |  1218 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP  |    14 |  1218 |     3   (0)| 00:00:01 |
————————————————————————–
Note
—–
   – dynamic statistics used: dynamic sampling (level=2)
Statistics
———————————————————-
            0  recursive calls
            0  db block gets
            8  consistent gets
            0  physical reads
            0  redo size
       1537  bytes sent via SQL*Net to client
          608  bytes received via SQL*Net from client
            2  SQL*Net roundtrips to/from client
            0  sorts (memory)
            0  sorts (disk)
           14  rows processed
SQL> set rowprefetch 10
SQL> select * from emp;
14 rows selected.
Execution Plan
———————————————————-
Plan hash value: 3956160932
————————————————————————–
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time    |
————————————————————————–
|   0 | SELECT STATEMENT  |         |    14 |  1218 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP  |    14 |  1218 |     3   (0)| 00:00:01 |
————————————————————————–
Note
—–
   – dynamic statistics used: dynamic sampling (level=2)
Statistics
———————————————————-
            0  recursive calls
            0  db block gets
            8  consistent gets
            0  physical reads
            0  redo size
       1534  bytes sent via SQL*Net to client
          608  bytes received via SQL*Net from client
            2  SQL*Net roundtrips to/from client
            0  sorts (memory)
            0  sorts (disk)
           14  rows processed
SQL> set rowprefetch 5
SQL> select * from emp;
14 rows selected.
Execution Plan
———————————————————-
Plan hash value: 3956160932
————————————————————————–
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time    |
————————————————————————–
|   0 | SELECT STATEMENT  |         |    14 |  1218 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP  |    14 |  1218 |     3   (0)| 00:00:01 |
————————————————————————–
Note
—–
   – dynamic statistics used: dynamic sampling (level=2)
Statistics
———————————————————-
            0  recursive calls
            0  db block gets
            8  consistent gets
            0  physical reads
            0  redo size
       1534  bytes sent via SQL*Net to client
          608  bytes received via SQL*Net from client
            2  SQL*Net roundtrips to/from client
            0  sorts (memory)
            0  sorts (disk)
           14  rows processed
SQL> set arraysize 5
SQL> select * from emp;
14 rows selected.
Execution Plan
———————————————————-
Plan hash value: 3956160932
————————————————————————–
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time    |
————————————————————————–
|   0 | SELECT STATEMENT  |         |    14 |  1218 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP  |    14 |  1218 |     3   (0)| 00:00:01 |
————————————————————————–
Note
—–
   – dynamic statistics used: dynamic sampling (level=2)
Statistics
———————————————————-
            0  recursive calls
            0  db block gets
            8  consistent gets
            0  physical reads
            0  redo size
       1534  bytes sent via SQL*Net to client
          608  bytes received via SQL*Net from client
            2  SQL*Net roundtrips to/from client
            0  sorts (memory)
            0  sorts (disk)
           14  rows processed
SQL> set rowprefetch 1
SQL> select * from emp;
14 rows selected.
Execution Plan
———————————————————-
Plan hash value: 3956160932
————————————————————————–
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time    |
————————————————————————–
|   0 | SELECT STATEMENT  |         |    14 |  1218 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP  |    14 |  1218 |     3   (0)| 00:00:01 |
————————————————————————–
Note
—–
   – dynamic statistics used: dynamic sampling (level=2)
Statistics
———————————————————-
            0  recursive calls
            0  db block gets
           10  consistent gets
            0  physical reads
            0  redo size
       1919  bytes sent via SQL*Net to client
          630  bytes received via SQL*Net from client
            4  SQL*Net roundtrips to/from client
            0  sorts (memory)
            0  sorts (disk)
           14  rows processed
As you can see the only visible changes are the SQL*Net roundtrips to/from client of the ARRAYSIZE setting.
SET LOBPREFETCH (default 0 bytes | 2G max)
Similar to ROWPREFECTH but for LOB data.

SET STATEMENTCACHE (default 0 | 32767 max)
This setting will cache similar SQL statements for the current session reducing the amount of necessary parses.
Thanks,

Alfredo

Upload the OPatch that is of version “13.8.0.0.0” and platform “226” to the Software Library

I was having an issue trying to deploy a patch to an agent due to the version of OPatch available in the software library.
The error I got was “Upload the OPatch that is of version “13.8.0.0.0” and platform “226” to the Software Library”

The solution was to run the “OPatch Update” job available from the job console in OEM.

Choose “OPatch Update” as job type:

Type a name for the job:

And click submit:

After this the agent patching was successful.
Thanks,
Alfredo