Wednesday, 9 August 2017

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.

Tuesday, 8 August 2017

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.

Tuesday, 1 August 2017

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.

Wednesday, 12 July 2017

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).

Monday, 30 January 2017

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.

Monday, 2 January 2017

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.

Tuesday, 24 May 2016

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/.