Author: alfredokrieg

_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

“Failed to find the OPatch patch directory” while applying patch 25105555 ( 13.2.0.0.161231) from OEM console

While applying patch 25105555 ( 13.2.0.0.161231) to an agent using the “provisioning and Patching” console from OEM 13.2 and choosing the “Upgrade OPatch” option, got this error:
———- Error Message ———-
Error: 010
Failed to find the OPatch patch directory “/tmp/p6880880_600000000057471_2000_0/oracle/OPatch”.
If you chose “No” for the “StagePatches” option, ensure that you manually place the patch at the required location and retry the operation.
———– End Message ———–
Tue Aug 27 15:41:48 2017 – Patching aborted.
You have to follow the solution section from below MOS note:
EM 13c: Applying EM-AGENT Bundle Patch 25105555 ( 13.2.0.0.161231) from Enterprise
Manager 13.2 Cloud Control Fails with Message: Error: 010 Failed to find the OPatch patch directory (Doc ID 2229452.1)
It depends on whether you have an online or offline OMS configuration.
After doing this I was able to apply patch 25105555 to the agent.
Hope this helps.

Alfredo

RMAN restore slow due to ASMB process from ASM to filesystem

I was restoring a database running on ASM to an instance running on filesystem.
The step for cataloging files was extremely slow and after taking a look at the alertlog file, I noticed tons of errors related to ASMB process.
Well, turns out this is an unpublished bug explained in more detail on the below MOS note.
12c RMAN Operations from ASM To Non-ASM Slow Performance (Doc ID 2081537.1)
After applying the suggested patch (19503821) the performance went back to normal and the restore progressed as expected.
Thanks,

Alfredo

2017 April PSU – OEM sending tablespace related alerts every collection schedule

I noticed that several alerts related to tablespace full were sent from OEM every collection schedule. At first sight I thought this may be related to OEM itself but after spending some significant time in MOS found a bug note stating that the 2017 April PSU patch was responsible.



The issue is that dba_tablespace_usage_metrics is not accounting for autoextend datafiles. So even if you add a new datafile, your used space will continue be reported as it was before.
You need to apply patch 26198757 on top of April PSU.
Hope this help you to avoid all this noise and happy patching.
Thanks,

Alfredo

Oracle Management Cloud – Setup Entities in Log Analytics

In order to gather Weblogic’s Admin Server and Managed Server log files, you need first to install and configure the OMC Cloud Agent.  
Oracle Management Cloud – Cloud Agent Install
Then you need to verify that the Cloud Agent is up and running.  Refer to my previous post on how to manage the Cloud Agent.
Oracle Management Cloud – Managing the Oracle Cloud Agent
The next step is to setup the Log Analytics entities. In order to do this you have to make use of a JSON template. Oracle provides examples of these templates in the OMC documentation:
http://www.oracle.com/webfolder/technetwork/tutorials/sample_JSONs/omc_entity_json_samples.zip
In this example we are going to add a Weblogic Admin Server and Managed server as Cloud Agent entities.
Unzip the example zip file and modify the omc_weblogic_domain_credless.json file.
The data added to this file really depends on your environment. This is how it looks for my environment:
{
   “entities”:[
      {
         “name”:”base_domain”,
         “type”:”omc_weblogic_domain”,
         “displayName”:”Weblogic_on_”,
         “timezoneRegion”:”PST”,
         “properties”:{
            “port”:{
               “displayName”:”Port”,
               “value”:”7001″
            },
            “domain_home”:{
               “displayName”:”Domain Home”,
               “value”:”/u01/app/oracle/product/ofm12.1.2/user_projects/domains/base_domain”
            },
            “admin_server_host”:{
               “displayName”:”AdminServer”,
               “value”:”.”
            },
            “webLogic_home”:{
               “displayName”:”WebLogic Home”,
               “value”:”/u01/app/oracle/product/ofm12.1.2/wlserver”
            }
         },
         “associations”:[
            {
               “assocType”:”omc_contains”,
               “sourceEntityName”:”base_domain”,
               “sourceEntityType”:”omc_weblogic_domain”,
               “destEntityName”:” __AdminServer”,
               “destEntityType”:”omc_weblogic_j2eeserver”
            }
         ]
      },
      {
         “name”:”_AdminServer”,
         “type”:”omc_weblogic_j2eeserver”,
         “displayName”:”AdminServer_”,
         “timezoneRegion”:”PST”,
         “properties”:{
            “host_name”:{
               “displayName”:”Weblogic Host”,
               “value”:”.”
            },
            “domain_home”:{
               “displayName”:”Domain Home”,
               “value”:”/u01/app/oracle/product/ofm12.1.2/user_projects/domains/base_domain”
            },
            “listen_port”:{
               “displayName”:”Listen Port”,
               “value”:”7001″
            },
            “listen_port_enabled”:{
               “displayName”:”Listen Port Enabled”,
               “value”:”true”
            },
            “ssl_listen_port”:{
               “displayName”:”SSL Listen Port”,
               “value”:”7002″
            },
            “server_names”:{
               “displayName”:”Server Names”,
               “value”:”AdminServer”
            }
         },
         “associations”:[
            {
               “assocType”:”omc_monitored_by”,
               “sourceEntityName”:”_AdminServer”,
               “sourceEntityType”:”omc_weblogic_j2eeserver”,
               “destEntityName”:”base_domain”,
               “destEntityType”:”omc_weblogic_domain”
            }
         ]
      },
      {
         “name”:”_ManagedServer_1″,
         “type”:”omc_weblogic_j2eeserver”,
         “displayName”:”ManagedServer_1_”,
         “timezoneRegion”:”PST”,
         “properties”:{
            “host_name”:{
               “displayName”:”Weblogic Host”,
               “value”:”.”
            },
            “domain_home”:{
               “displayName”:”Domain Home”,
               “value”:”/u01/app/oracle/product/ofm12.1.2/user_projects/domains/base_domain”
            },
            “listen_port”:{
               “displayName”:”Listen Port”,
               “value”:”7003″
            },
            “listen_port_enabled”:{
               “displayName”:”Listen Port Enabled”,
               “value”:”true”
            },
            “ssl_listen_port”:{
               “displayName”:”SSL Listen Port”,
               “value”:”7005″
            },
            “server_names”:{
               “displayName”:”Server Names”,
               “value”:”ManagedServer_1″
            }
         },
         “associations”:[
            {
               “assocType”:”omc_monitored_by”,
               “sourceEntityName”:”_ManagedServer_1″,
               “sourceEntityType”:”omc_weblogic_j2eeserver”,
               “destEntityName”:”base_domain”,
               “destEntityType”:”omc_weblogic_domain”
            }
         ]
      }
   ]
}
Save this file and load the entities to the Cloud Agent as follows:
$ ./omcli add_entity agent /home/oracle/omc_weblogic.json
Oracle Management Cloud Agent 
Copyright (c) 1996, 2016 Oracle Corporation.  All rights reserved.
Operation Succeeded: Accepted 3 of 3 entities for processing.
As you can see, 3 entities were loaded to the agent; the base_domain, Admin Server and Managed Server.
Now verify the new entities in the Log Analytics configuration page.
–        Click on the “Navigation” icon.
–        Under “Cloud Services” click on “Log Analytics”
Click on “Configuration” located in the right side of the screen



In the “Entities” section. Click on the icon with the number (ie. 4)

You will see a list of the new entities just added.
Go back to “Log Explorer” by clicking on





In the search box type ‘Log Source’ = ‘FMW WLS Server Logs’


Click on “Run”


Check all the log information displayed for the WLS Server

This blog post is part of a series of blog posts related to the Oracle Management Cloud:
·                     How do you manage your cloud? Strategies for multi-vendor public, private, and hybrid cloud management
·                     What is the Oracle Management Cloud?
·                     Types of Oracle APM Agents
·                     Oracle Management Cloud – Cloud Agent Install
·                     Logging into Oracle APM Cloud Service for the first time
·                     Overview of Oracle APM Cloud Service features


Thanks,
Alfredo

Oracle Management Cloud – Managing the Oracle Cloud Agent

This post is about some useful commands to administer the OMC’s Cloud agent. The very first step is to install the OMC Cloud agent. You may refer to my previous post on how to install it here.
The omcli command is located at /agent_inst/bin.
I’m skipping these 3 basic commands start, stop and status.
Here we go!
–      List the targets currently managed by the Cloud agent
$ ./omcli config agent listtargets
Oracle Management Cloud Agent 
Copyright (c) 1996, 2016 Oracle Corporation.  All rights reserved.
[hostname.localdomain:1302, Lama]
[hostname.localdomain, omc_host_linux]
–      Get the TimeZone of the server where the Cloud agent is running
$ ./omcli config agent getTZ
Oracle Management Cloud Agent 
Copyright (c) 1996, 2016 Oracle Corporation.  All rights reserved.
US/Eastern
–      Get a detailed output of the Cloud agent scheduler
$ ./omcli status agent scheduler
Oracle Management Cloud Agent 
Copyright (c) 1996, 2016 Oracle Corporation.  All rights reserved.
—————————————————————
Number of currently running scheduled items :: 0
Number of currently ready scheduled items :: 0
Number of scheduled items :: 57
Schedule Attempts Delay Percentage :: 0.000%
Running entries::
Ready entries::
Scheduled entries::
2017-03-31 14:32:58.968 : HeapMonitorTask
…
2017-03-31 22:34:14.667 : PurgeTargetEvents
2017-03-31 22:35:47.885 : PurgeADR
2017-04-01 13:34:20.810 : EventStatusPurge
—————————————————————
Agent is Running and Ready
–      Get a summary of the Cloud agent’s scheduler
$ ./omcli status agent scheduler -summary
Oracle Management Cloud Agent 
Copyright (c) 1996, 2016 Oracle Corporation.  All rights reserved.
—————————————————————
Number of currently running scheduled items :: 0
Number of currently ready scheduled items :: 0
Number of scheduled items :: 57
Schedule Attempts Delay Percentage :: 0.000%
—————————————————————
Agent is Running and Ready
–      Dump the internal state of the agent [option component name]
$ ./omcli dumpstate agent
Oracle Management Cloud Agent 
Copyright (c) 1996, 2016 Oracle Corporation.  All rights reserved.
Generated file: /u01/omc/infra/agent_inst/sysman/emd/dumps/SystemDump_1490985620656.xml
–      Dump internal information of all components of the Cloud agent
$ ./omcli dumpstate agent -dump_full_state
Oracle Management Cloud Agent 
Copyright (c) 1996, 2016 Oracle Corporation.  All rights reserved.
Generated file: /u01/omc/infra/agent_inst/sysman/emd/dumps/SystemDump_1490985775823.xml
–      Add entities to the Cloud agent using JSON template
$ ./omcli add_entity agent /home/oracle/omc_weblogic.json
Oracle Management Cloud Agent 
Copyright (c) 1996, 2016 Oracle Corporation.  All rights reserved.
Operation Succeeded: Accepted 2 of 2 entities for processing.
–      Delete entities from the Cloud agent using JSON template
$ ./omcli delete_entity agent /home/oracle/omc_weblogic.json
Oracle Management Cloud Agent 
Copyright (c) 1996, 2016 Oracle Corporation.  All rights reserved.
Request for removing the entities was accepted for processing.
This is just a small taste of all the options built into the Oracle’s Cloud agent.  For more information about all the options refer to Oracle’s documentation.
https://docs.oracle.com/en/cloud/paas/management-cloud/gfadg/omcli-command-options.html
Thanks,

Alfredo