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.

ORA-06502: PL/SQL: numeric or value error on auto execute of job "SYS"."BSLN_MAINTAIN_STATS_JOB" (external link)

The background of this issue is frequently bound to some cloning or copying some template database to a new instance. The Oracle Support (former Metalink) for version 11.2.0.3 suggests to install patch "Patch 10110625: DBSNMP.BSLN_INTERNAL RECEIVES ORA-6502" or to recreate a DBSNMP user. However by googling I 've found more finesse solution - https://oracledbazone.com/2012/04/17/ora-12012-error-on-auto-execute-of-job-sys-bsln_maintain_stats_job-2/.

A data block was corrupted in alert log on Exadata

The following excerpt in the alert log on one of instances on Exadata:

Mon May 16 08:22:08 2016
Errors in file /u01/app/oracle/diag/rdbms//4/trace/4_dbw0_103431.trc:
ORA-01186: file 4097 failed verification tests
ORA-01157: cannot identify/lock data file 4097 - see DBWR trace file
ORA-01110: data file 4097: '+DATA1'
Mon May 16 08:22:08 2016
File 4097 not verified due to error ORA-01157
Mon May 16 08:22:08 2016
Dictionary check complete
Mon May 16 08:22:08 2016
Errors in file /u01/app/oracle/diag/rdbms//4/trace/4_dbw0_103431.trc:
ORA-01186: file 4097 failed verification tests
ORA-01157: cannot identify/lock data file 4097 - see DBWR trace file
ORA-01110: data file 4097: '+DATA1'
Mon May 16 08:22:08 2016
File 4097 not verified due to error ORA-01157
Mon May 16 08:22:09 2016
Re-creating tempfile +DATA1 as +DATA1//TEMPFILE/temp.5375.911982129

While at first it looks somewhat dangerous, it is a minor issue and self-healing. First of all one have to know the database in question is a standby, and it is in open mode with log apply (so Active Data Guard option in use). The file 4097 has id just above the db_files parameter - which immediately indicates this is a temporary file and such files do not exist by default on standbys - hence the inability to identify the file.
Now look at the last line in the excerpt - recreation of the temporary file, which heals the problem.

Thus if one looks at the v$database_block_corruptions, there is nothing there. In dba_temp_files also one does not meet the re-created tempfile - while this is standby, it does not change sources for this view apparently, while the file is listed in the v$tempfile.