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'