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