Datatombraider's Blog

2014/02/07

Standby DB ignores specified diskgroup and creates datafiles in db_create_file_dest

Filed under: Oracle — datatombraider @ 23:54

The issue described here is documented in “Oracle® Data Guard Concepts and Administration 11g Release 2 (11.2)” and not a bug but the intended behaviour. It’s easily overlooked, so it might be worth sharing.

In a tiered storage environment you usually use different diskgroups. We have +FAST for critical data and +SLOW for historical data. If we create a new tablespace or add a new data file to an existing tablespace we have to specify +SLOW for historical data:

create tablespace bla datafile ‘+SLOW’ size 10m;

In the primary database the datafile is correctly created in diskgroup +SLOW but on the standby using standby_file_management=AUTO it will be created in +FAST:

PRIMARY> select file_name from dba_data_files where tablespace_name=’BLA’;

FILE_NAME
——————————————————————————–
+SLOW/pri/datafile/bla.287.838042203

STANDBY> select file_name from dba_data_files where tablespace_name=’BLA’;

FILE_NAME
——————————————————————————–
+FAST/stdby/datafile/bla.777.838042205

Setting db_file_name_convert to whatever values has no effect. If the standby database uses OMF it creates the data files always in db_create_file_dest, no matter what was specified on the primary. There’s no way to change it because it’s the documented and intended behaviour.

This issue is potentially dangerous because different diskgroups may have different performance and/or recovery specifications. If the data files on the standby are created silently in the wrong diskgroup, it could led to a bad surprise after a switchover. Nothing i want 😉

Therefore i decided to drop the usage of db_create_files on databases using multiple diskgroups for data files.

The disadvantage is that you always have to specify the desired diskgroup, but the advantage outweighs this little inconvenience.

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.