The crash with 11.2.0.2.9 binaries

I have run a test database and after short time have been informed it crashed. Today's investigation shows that it is bound with parallel recovery done automatically after/during startup. Here IMHO the excellent article on the subject.

Short way to enable one SQL plan from many

Assuming one has a SQL cursor on a database, for which exist many plans and one wants to get rid of all of them but one here is the quickest way I find to do that:

declare
  n number;
begin
  n:=dbms_spm.load_plans_from_cursor_cache(sql_id=>'gzwhs3zc68ju5', plan_hash_value=>156135624, fixed =>'NO', enabled=>'YES');
end;
/

• sql_id is an identifier of a SQL we want to create SQL plan baseline for
• plan_hash_value is a hash value for the plan wa want to use
• fixed is an attribute, which provides some priority for fixed plans over non-fixed and disables automatic SQL tuning to implement new findings at once (findings are stored as non-fixed plan baselines) - I would say this is reasonable to use it, though in the example it is set to NO
• enabled is self-explained

Analyzing chained and migrated rows

Two articles from the renowned authors:

1. Analyze-this by J. Lewis
   Shortcut:
   

   -- gathering statistics populates among others user_tables.chain_cnt
   analyze table [tbl] compute statistics for table;
   -- check gathered data
   -- delete statistics 
   -- otherwise the optimizer will use the chain_cnt to modify 
   -- the cost of indexed access to the table
   analyze table [tbl] delete statistics; 
   exec dbms_stats.gather_table_stats([owner], [tbl]);
   

2. Detect-chained-and-migrated-rows-in-oracle by T.Poder
   Excerpt:
   One way to estimate the impact of chained rows is to just look into the "table fetch continued row" statistic - one can run query/workload and measure this metric from v$sesstat (with snapper for example). And one more way to estimate the total number of chained pieces would be to run something like SELECT /*+ FULL(t) */ MIN(last_col) FROM t and see how much the "table fetch continued row" metric increases throughout the full table scan. The last_col would be the (physical) last column of the table. Note that if a wide row is chained into let's say 4 pieces, then you'd see the metric increase by 3 for a row where 4th row piece had to be fetched.

Duplicating database manually on the same host

The assumption is to create a copy of the database on the same host in order to run 2 independent database instances. It can be done in many ways:

• with RMAN 
• with expdp and standard dump
• with expdp and transportable tablespaces
• with cp command and dbnewid tool

I present here the last way.

1. --shutdown cleanly a database to be copied
   shutdown immediate; 
   

2. change the database directory name to something different - it is needed in order to preserve original datafiles, when we would change their paths. By default the Oracle RDBMS deletes original data files when they exist under both paths (ie. old one and the new one; we use OMF) - by directory change we prevent this behaviour - for example new directory name would be TEST.ORIG. After whole operation we return back the original name.
3. copy all the datafiles, logfiles and controlfiles to a new directory with cp command

   ## for example 
   cp -r TEST.ORIG TES2
   

4. create new init.ora with new path to the controlfiles, new unique name and other paths with would conflict with   - for example create pfile='inittes2.ora' from spfile
5. startup nomount - fix possible errors
6. mount the database - fix possible errors
7. change the data files paths -

   select 'alter database rename file '''||name||''' to '''
     ||regexp_replace(name, '[old prefix]', '[new prefix]')
     ||''';' ddl1 
   from v$datafile 
   union all
   select 'alter database rename file '''||name||''' to '''
     ||regexp_replace(name, '[old prefix]', '[new prefix]')
     ||''';' ddl1 
   from v$tempfile 
   union all
   select 'alter database rename file '''||member||''' to '''
     ||regexp_replace(member, '[old prefix]', '[new prefix]')
     ||''';' ddl1 
   from v$logfile
   ;
   
   

8. shutdown immediate

9. ## ensure open_cursors are set to bigger number than 
   ## all files of the database
   ## (apparently the nid utility opens them all at once)
   nid target=sys dbname=cd2;
   

10. change parameter file db_name to new name

11. startup mount
    alter database open resetlogs;
    

12. restore old files to the old path and startup the original database

Summarizing there are only 2 things, which one must care about - implicit removal of database files in old location (which must be prevented; not sure if this happens without OMF) and the open_cursors parameter, which must be set higher than the number of database files (not sure but better to count temporary and log files as well).

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.