Locating data files on standby

Problem

The db_create_file_dest parameter indicates the default directory in which an Oracle RDBMS instance creates new files according to the OMF rules.
The scenario considered is this:

• we want to add new files
• despite the default location we have also additional one built upon lesser storage and designated for archive purposes
• there is also standby configured also with OMF, and file paths differ between both instances

The problem is how to place files in the archival location on standby with least effort?

Solution 1

One simply:

• adds data files on the primary with explicit specification of the file path - the files are added on primary to the right location, on the standby to the default one (instead of the archivel one)
• lists recently added files identifiers - this may be a mundane task especially if one adds data files to already existing tablespaces or is unable to provide simple condition on tablespace_names - in general this may be done on the dba_data_files or v$datafile view.
• next one creates a script with move operation
  

  -- first part is to move data files at the OS level
  select 
    'mv '||name||' [archival path]'||substr(name, instr(name, '/', -1))||';' bash1 
  from v$datafile where file# in ([ids list]);
  
  -- second part is to rename files at the db level
  select 
    'alter database rename datafile '''||name||''' to ''[archival path]'||substr(name, instr(name, '/', -1))||''';' sql1 
  from v$datafile where file# in ([ids list]);
  

  The best way of course is to format and spool the answers of those queries to scripts (or one script)
• now one have to switch off the managed standby mode (alter database recover managed standby cancel;)
• the next step is to switch standby_file_management to MANUAL (alter system set standby_file_management='MANUAL';)
• further one have to perform the core commands - run scripts generated earlier and move files to the final location and rename the paths at the database level
• the last steps are switching to AUTO mode again (alter system set standby_file_management='AUTO';) and starting the managed replication (recover managed standby database using current logfile disconnect)

Solution 2

I suppose this one is much more clever than the first. On the standby one simply changes temporarily the default OMF path to the archival location - of course there are elements we have no influence on like db_unique_name or datafile literal within the path, but these are of small importance and we may be prepared for it, if we assume earlier such scenario. Then it is enough to add data files - the standby will automatically place them into the location of choice - of course we have to be sure, no one adds other files at the moment. After the operation it is enough to set db_crate_file_dest back to previous setting.