How to Add Datafiles to Databases with Configured with Physical Standby

 
It’s always prudent to check for your database’ current configuration before adding new datafiles. A mistake can cause serious problems if your database has a physical standby configured.

The following parameters are important when checking for your standby database configuration:
- standby_file_management
- db_file_name_convert
- db_create_file_dest (on standby)
- archive_log_dest parameters (delay and etc).

It is also important to note that the parameters mentioned above (except for achive_log_dest) are only being used by the standby database. Thus, if there is a mismatch between the primary and the standby database, the parameters on the standby database are followed.

standby_file_management (AUTO) – If this parameter is set to auto, all datafiles added on the primary database are automatically recreated on the standby database. As long as there are no errors encountered on the standby database (i.e. space errors or missing filesystems).

db_file_name_convert – If standby_file_management is set to auto, it is advisable to also enable this parameter (but not required). This automatically renames the datafile in standby depending on your specification.

db_create_file_dest – This parameter is used to enforce OMF (Oracle Managed Files). This parameter cannot be set in conjunction with db_file_name_convert, doing so would result in ORA-02096 (means another parameter is set that is incompatible with this one).

standby_file_management (MANUAL) – If this parameter is set to manual, you must recreate the datafile you added in production to the standby by reexecuting the same create datafiles command in the standby database.

If you need more help with database issues, feel free to send me an email (jimmycdo@yahoo.com) and I'll gladly help.

About Jimbart