Hinty (external links)

• MATERIALIZE and INLINE

Calibrating I/O

Today a colleague of mine started the short calibration code:

set serveroutput on
declare 
 l_latency number;
 l_iops    number;
 l_mbps    number;
begin
 dbms_resource_manager.calibrate_io(num_physical_disks=>6, max_latency=>12, max_iops=>l_iops, max_mbps=>l_mbps, actual_latency=>l_latency);
 dbms_output.put_line('max_iops='||l_iops||'   max_mbps='||l_mbps||'  actual_latency='||l_latency);
end;
/

... and after some 10 minutes got

Tue Oct 24 13:26:50 2017
Errors in file /opt/oracle/diag/rdbms/xxx/xxx/trace/xxx_cs01_10660.trc  (incident=60493):
ORA-04031: unable to allocate 1049112 bytes of shared memory ("shared pool","unknown object","sga heap(1,1)","ksfdcamem shared I/O read buffer")
Incident details in: /opt/oracle/diag/rdbms/xxx/xxx/incident/incdir_60493/xxx_cs01_10660_i60493.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /opt/oracle/diag/rdbms/xxx/xxx/trace/xxx_cs01_10660.trc:
ORA-04031: unable to allocate 1049112 bytes of shared memory ("shared pool","unknown object","sga heap(1,1)","ksfdcamem shared I/O read buffer")
Errors in file /opt/oracle/diag/rdbms/xxx/xxx/trace/xxx_cs01_10660.trc:
ORA-04031: unable to allocate 1049112 bytes of shared memory ("shared pool","unknown object","sga heap(1,1)","ksfdcamem shared I/O read buffer")
Tue Oct 24 13:26:51 2017
Dumping diagnostic data in directory=[cdmp_20171024132651], requested by (instance=1, osid=10660 (CS01)), summary=[incident=60493].
Tue Oct 24 13:26:51 2017
Sweep [inc][60493]: completed
Sweep [inc2][60493]: completed

While there is not much in the subject on Metalink, there exists an excellent blog article. I may add to it the following:

• 1st idea just to flush shared pool did not help - apparently as the system was in use initially and the load taken off just prior to calibration and many allocations already took place
• 2nd idea was to restart and that was enough - when run immediately after start, the process went smoothly.
• after success we 've got

  Tue Oct 24 13:42:51 2017
  Shared IO Pool defaulting to 512MB. Trying to get it from Buffer Cache for process 12832.
  

  Now it seems quite obvious that even with shared pool allocating 2G after a while there is no such amount of free memory in 1M chunks - even in reserved shared pool part (shared_pool_reserved_size defaults to 5% of the shared pool total)

Problem on a SELECT through a db link

Not my adventure entirely, yet interesting.
There exists a query in the shape INSERT ... SEELECT. SELECT is done on the view. The view returns huge set of data (if called without conditions). But there is a condition, thus the whole query in normal circumstances should be executed in a fraction of a second.
But yesterday out of nothing the plan changed. It changed several times in the past, but then it was enough to inject through a sql profile the hint optimizer_features_enable('10.2.0.4') to smooth things out. So developers injected it in a hard way - to the code itself. But yesterday the plan changed again despite the hint, and the change was based on the full view generation and remote site trying to push whole that mass of data back to the local site to filter through the condition - the local database waited on TCP Socket wait event, waiting for any data, while the remote database burnt CPU trying to produce full view set (which was so huge effort that it completely choked). And any action on the 'local' side did not fix the problem.
After some time spend on struggling with the issue the final solution was as follows:

• on the local site - optimizer_features_enable('10.2.0.4') and rule injected by sql profile
• on remote site - optimizer_features_enable('10.2.0.4') and rule injected by sql profile to the view

It is clear that rule and optimizer_features_enable are exclusive - on the Metalink though somewhere was a note that OPAQUE_TRANSFORM may be disabled by an event or optimizer_features_enable or rule hints. My guess is the rule hint now disabled OPAQUE_TRANSFORM (which is passed by default), which allowed for the plan control on the remote site and there optimizer_features_enable('10.2.0.4') was used.

RMAN-20033: control file SEQUENCE# too low

Most probably the true reason for that error to occur is a race condition between 2 concurrent backup processes - for example one full backup and one archivelog backup with autobackup enabled. Thus both would synchronize with RMAN catalog and one of them may try to register controlfile being older than the last one already registered by the 2nd process.

On Metalink they suggests a weird solution for this (Rman-20033 Control File Sequence# Too Low (Doc ID 342696.1)) to recreate the controlfile or RMAN catalog - it is possible it may be needed in some scenario, but I guess the best way first is simply rerun the backup ensuring there is only one backup process running.

Environment variable expansion in sqlnet.ora

By and large there are not many articles on the environment variables expansion in sqlnet.ora.
May be the main reason is that in majority of cases there is no such possibility. I run a bunch of tests and for all those few cases there was no such feature enabled - simply all the entries with $ sign as leading character were treated literally as text.

The only situation when it works is if used with a WALLET_LOCATION definition. The WALLET_LOCATION definition is more complex than all the rest sqlnet.ora parameters and is built with the help of usual Oracle syntax for .ora files with parentheses. Such definition could look as follows:

WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY=/opt/oracle/wallets/mywallet) ) )

But what I would emphasize here is the possibility to make a part of this definition to be build dynamically. I've tested only the file method. The only part which apparently allows for the environment variables expansion is the DIRECTORY value. Putting a variable to other attributes (like let's say METHOD) does not seem to work.
Thus one may put into the DIRECTORY path any variable defined in the environment. For example:

## as You see LOGNAME is a predefined variable, ORACLE_HOME a well known variable, 
## while $WALLET_METHOD is simply a custom one
WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY=$ORACLE_HOME/$WALLET_METHOD/wallets/walletOf$LOGNAME) ) )

Further this looks as follows in strace trail:

## ORACLE_HOME is /opt/oracle/app/oracle/product/12.1.0/client_1
## WALLET_METHOD was set to FILE
## and LOGNAME is seen as rems
## errors are due to the fact I had no wallet configured at all
## wanting only to see if the expansion would take place 
[..]
stat("/opt/oracle/app/oracle/product/12.1.0/client_1/FILE/home/rems/admin/network/admin/walletOfrems/ewallet.p12", 0x7ffebccd3430) = -1 ENOENT (No such file or directory)
stat("/opt/oracle/app/oracle/product/12.1.0/client_1/FILE/home/rems/admin/network/admin/walletOfrems/cwallet.sso", 0x7ffebccd3430) = -1 ENOENT (No such file or directory)
write(1, "ERROR:\n", 7)                 = 7
write(1, "ORA-12578: TNS:wallet open faile"..., 34) = 34
[..]

With such ability we can create several wallets being chosen based on the environment context - thus with one binaries installation (and one sqlnet.ora file) we may service many users with their own wallets thus separating their responsibilities.
One may also differentiate by $ORACLE_SID, which is also evaluated.

Blocking password change

Very interesting article on the subject http://www.petefinnigan.com/weblog/archives/00001198.htm by Pete Finnigan with even more insightful comment.
In short while the Oracle database allows implicitly to change user's own password without any additional grants one may block this with leverage of the before alter on database trigger. But the truly "professional" approach here would be to use the Oracle password verify function declared with profile. This one allows for fine-grained selction of password change commands without any conditions - here one may easily decide which user profiles are allowed to change own passwords and which are not.

My personal concern here was bound mainly with securing this ability from proxy users - without that function the proxy user is allowed to change the password of the schema to which one acquired proxy connection privilege, so not knowing the password is not that restrictive (one may change it).
With the properly constructed password verify function we have nice combo here.

Case-sensitive user names in Oracle ASM 12.2

Seems that with the upgrade to 12.2 version ASM recognizes the cases. We were able to create users asmsnmp and ASMSNMP. First we created user asmsnmp and expected this will solve the incident (lack of asmsnmp user, which was used by OEM to monitor ASM and gone). But the OEM still presented the red icons. We thought about a mistake in password spelling or so, but finally the friend of mine created the ASMSNMP user and the OEM cleared the alerts. Also there is no longer --password parameter - to change the password one simply adds --modify (ie. orapwusr --modify asmsnmp).

Multiple values for a parameter

By and large there are few Oracle RDBMS parameters, which allow for multiple values. The core example here is control_files. The syntax to set such multiple values is as follows:

alter system set [parameter name]='[value1]','[value2],'[value3]';

In the past this was not related to the local_listener parameter - it was one value parameter and to provide several host:port combinations one had to create multi value alias definition in tnsnames.ora.
Some time ago (few days may be) I realized that now the local_listener also allows for multiple values - in fact since 11.2.0.1.

Excessive CPU usage with 12c

After upgrade to 12c we experience the excessive CPU usage - during weekend it was not a problem, but on Monday after the upgrade the application based on that db was slow, very, very slow.
The workaround was switching the optimizer into the 11.2.0.4 mode with optimizer_feature_enable. The main suspect is Adaptive Optimizer features so possible one could disable it with optimizer_adaptive_feature set to false, but not tested yet.