Login issues

The 11g version brought some changes to the DEFAULT profile. Previously the FAILED_LOGIN_ATTEMPTS parameter was always set to UNLIMITED and now it is set to some value, which means a schema lock after this value of failed logins is crossed over.

I must say I am puzzled about this. In general I understand the reason behind the FAILED_LOGIN_ATTEMPTS - it is against password breaking brute force attacks. On the other hand it means that some 'lost' application host with a wrong password becomes the point of a DoS attack. Which is better (or worse), hard to tell.
Usually a database is located after some firewall (or two) as this is quite a deep layer in the application stack. I usually meet with databases, where the schemas are application schemas, so there exists a client application interface between a human and a database - passwords are encoded in the application configuration and a direct access to a database itself is strictly limited. On the other hand there are users (though not numerous), who are allowed to make a direct connection and among them a 'malicious' one may be hidden .

So, how do I imagine dealing with the configuration?
I believe for application it is better to create another profile, which keeps the FAILED_LOGIN_ATTEMPTS parameter to the UNLIMITED, because it is not so rare that some forgotten application or script exists, which would block the schema and thus practically disable the application. Of course there is a monitoring system, but usually the delay in the information feedback to a human is ~5 minutes. There other issues come up (multiple application hosts, and only one of them with a wrong password; few applications sharing the same schema; scripts run from the cron on different shell accounts; etc.) and we get a noticeable delay in the application work, which was meant possibly to work in the 24x7 regime. And this may happen quite frequently and there is no need for malice.
Further, it would be reasonable to move direct users to other databases and possibly connect them through mix of additional schemas and/or database links, so that they would not be able to connect directly to the database with application schemas.

The drawbacks?

• the human users have got performance penalty, if connected through another database or may try to break passwords if there will be no such prevention measures - so if it is a database with plenty of directly connected human users then this would not be such a great idea.
• 11g: Multiple failed login attempt can block new application connections - shortly the 11g version has additional security feature against brute force attacks - if set to the UNLIMITED there is a delay enabled when returning an error message due to the failed login attempt after the first few attempts. Due to the bug 7715339 such delayed session keeps library cache lock for prolonged period (due to enabled delay) and new sessions wait on this lock till the number hits the sessions/processes ceiling. It is possible to disable the delay feature with event='28401 trace name context forever, level 1'

An issue with the UTL_FILE and file permissions

Configuration in which only local user is able to write to indicated directory through UTL_FILE running sqlplus, and for any remote access or access by other users, the code will not work properly Some time ago we met with a little weird behavior of a test database. The code in a package did some processing and wrote to specified directory. However it worked only if the user was logged on the database host as him. Calling the code remotely or locally from a different user (also the database owner) ended always with error about wrong operation on file (the writes were issued through UTL_FILE).
The os directory, which was aliased in the database, had mask 0777, so though not owned by oracle, should be accessible by the database.

The explanation is the following:
While the aliased directory was accessible to anybody, the parent directory to it (ie. home directory of the user) was accessible only to him and his group. Thus if any user tried to use the code, the database can not have written to the destination as it requires at least rX permissions on every directory level. To make the code run was to add the oracle user to the local user group or change permissions on the local user home or anything like that.
But why the local user was able to perform the code successfully? This is bound with the way the connection to the database is handed to a user client. When it is done through listener simplifying it prepares a process/dispatcher, then forwards the connection information to the client. If dealing with connection locally it is done differently - the database process becomes a child of the local user session and inherits user' permissions and thus is able to write to the otherwise inaccessible file.

Conditions in UPDATE statements

This is kind of a fix to common developer misuse. I noticed that sometimes see the solution for searching more complex constructs as this:

[..] where f1||f2||f3 in (select f1||f2||f3 from t1)

While in SELECT this is simply weird, because one may simply join 2 tables and specify conditions with f1=f1 etc., it may be considered when we play with DML. Of course this means the optimizer can not use indexes (unless there is some rather complex index on expression), but developers forget about very easy construct:

[..] where (f1, f2, f3) in (select f1, f2, f3 from t1)

Now plans look much better and we still may create condition on several fields at once.

Confusion at partition maintenance

Here an anecdote rather than a hint, but sometimes helps ;-). Once I have read about our "built-in" inability to find own errors and this is exactly about such case.

Lately I tried to add a new partition for 2013 year to some table. It was subpartitioned, so the syntax little more complex (below - please note, this is with error).

alter table schema1.tab1
add partition p1301 VALUES less than (201302) TABLESPACE tbs1 (
SUBPARTITION p1301_1 VALUES(1), 
SUBPARTITION p1301_2 VALUES(2),
SUBPARTITION p1301_3 VALUES(3), 
SUBPARTITION p1301_4 VALUES(4)));

I constructed the statement and tried to execute. But on and on I have got the same error:

ORA-14048: a partition maintenance operation may not be combined with other operations

. And it was not as complex as I may miss an additional operation. So I stuck. After several minutes I ask a colleague of mine for help. He came to me and I have explained him the issue. At the same moment I realize what was wrong - additional parenthesis.
If it would be missing, the error message would be correct

ORA-00907: missing right parenthesis

but with additional parenthesis the statement syntax suggests to the RDBMS something completely different, thus returning more enigmatic error, which in turn puzzled the RDBMS user - in this case me.

Some notes on materialized views

Few loosely notes on materialized views.

It is possible to build a mview on a pre-built table. I like this feature mainly because one is able to drop such view and recreate it with slightly changed definition, while the data is preserved, which is very valuable especially when dealing with large source tables.

Another observation is that sometimes the casting to right types is necessary. We met several times the situation when on Linux some mview definition worked properly while on AIX it failed due to not strong type casting, when the solution was to use CAST with precision to type and length (usually the problem core was longer VARCHAR2 than expected).

NoCOUG internal magazine

NoCOUG internal magazine at http://www.nocoug.org/Journal/NoCOUG_Journal_YYYYMM.pdf, where YYYY - year, MM - one of 02,05,08,11

Duplicating for standby

On the start I have a target database configured with OMF and specified unique db name. On the standby side I created an instance, started it and that's it. Oh, yeah - I copied password file from primary side to be able to connect to standby and configured listener with GLOBAL_DBNAME, which allows for registering a service for use in tnsnames.ora on both hosts. Of course I specified proper entries in tnsnames.ora as well.
There are possible many configurations for this duplication - combinations of a target database, a standby database and a catalog - of course a standby one is a must, but the rest is optional and may be avoided, although it complicates the matter a little. I choose to use additionally to standby database also active target database. This avoids the trouble of applying all the redo logs from last backup.

RMAN> DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE;

First failure: the command failed with ORA-17628: Oracle error 19505 returned by remote Oracle server on primary and ORA-19505: failed to identify file "/oracle/oradata/ZABBIX/controlfile/o1_mf_855ys7j8_.ctl"
ORA-27040: file create error, unable to create file as I did not create path for controlfile (so now mkdir -p /oracle/oradata/ZABBIX/controlfile/o1_mf_855ys7j8_.ctl).

Second start and failure:

RMAN-03002: failure of Duplicate Db command at 11/13/2012 10:14:19
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
ORA-06550: line 1, column 17:
PLS-00201: identifier 'DBMS_RCVCAT.GETDBID' must be declared
ORA-06550: line 1, column 7:

I found at http://blog.csdn.net/tianlesoftware/article/details/6232292 that it is due to not specifying nocatalog when connecting with rman. And that really helped.
Third try and this time I moved much ahead - I was wrong with assuming the online logs will be created according to the db_create_file_dest (so I should set db_create_online_log_dest_n at least to overcome this). However this time duplication is going forward - the data files are created according to the plan.
Because copy is done with backup as copy reuse datafile X auxiliary format new then no useless copying to storage local to primary and all bytes goes directly to the standby files, which is nice

To finish the standby I need now to add standby log files (though this is optional) and set log_archive_dest_state_2 to enable. The managed standby is already run by the duplicate command. One may want to switch this to recovery using current logfile.