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
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)
No comments:
Post a Comment