oracle dataguard 备库参数db_file_name_convert/db_create_file_dest注意事项

In this Document

Applies to:

Symptoms

Database files on standby environment are created on the wrong place despite the fact that both :
DB_FILE_NAME_CONVERT
and
LOG_FILE_NAME_CONVERT
are correctly set on the standby database

As an example on primary database we create a tablespace on ASM DG DATA_USERS:
CREATE TABLESPACE TESTME DATAFILE
'+DATA_USERS' SIZE 10M AUTOEXTEND ON NEXT 4096M MAXSIZE UNLIMITED
LOGGING ONLINE PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K FLASHBACK ON;

This datafile is created :
+DATA_USERS/orcl/datafile/testme.282.754566507
As expected.

However on the standby with db file name convert in place:
SQL> select name,value from v$parameter where upper(name) like '%CONVER%';

NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
db_file_name_convert
+DATA_USERS/orcl, +DATA_USERS/stby

log_file_name_convert
+DATA_USERS/orcl, +DATA_USERS/stby

Oracle creates the datafile in this other directory :
+DATA_USERS/orcl_stby/datafile/testme.386.754566507

All other database files in standby are in the correct directory :
+DATA_USERS/stby

You would expect the database file to be created on the correct directory based on the db_file_name_convert settings :
+DATA_USERS/stby instead of +DATA_USERS/orcl_stby

Changes

Current standby settings :

standby_file_management AUTO
db_file_name_convert +DATA_USERS/orcl, +DATA_USERS/stby
log_file_name_convert +DATA_USERS/orcl, +DATA_USERS/stby
compatible 11.2.0.2.0
db_create_file_dest +DATA_USERS
db_name orcl
db_unique_name orcl_stby
dg_broker_start TRUE

Primary and standby database are using
Oracle Managed Files (OMF)
and
Oracle Automatic Storage Management (Oracle ASM).

standby_file_management is set to AUTO and the db_create_file_dest is set to +DATA_USERS.

Cause

In this case OMF is used and as such no matter what the value for DB_FILE_NAME_CONVERT is, the name of newly created datafiles will be based on DB_CREATE_FILE_DEST parameters and the OMF filename conversion.

ASM OMF filenames are unique and cannot be specified by the user or using DB_FILE_NAME_CONVERT, i.e., the name is generated by ASM code itself. Therefore, file names will be different from the DB_FILE_NAME_CONVERT parameter, and only the DB_CREATE_FILE_DEST is honored in the filename conversion.

Keep in mind that the ASM OMF format is <+ASM-DG>//datafile/xxxx.xx.xxx and this is the format that was used for that new created datafile.

Here's what the DG reference manual says :

-------------------------------------------------
Oracle? Data Guard
Concepts and Administration
11g Release 2 (11.2)

13.5 Creating a Standby Database That Uses OMF or Oracle ASM

Note: If OMF parameters are set on the standby, then new files on that standby are always created as OMF, regardless of how they were created on the primary. Therefore, if both the DB_FILE_NAME_CONVERT and DB_CREATE_FILE_DEST parameters are set on the standby, the DB_CREATE_FILE_DEST parameter takes precedence.
-------------------------------------------------

The location and name of the newly created datafile is expected based on the settings above.

The directory where the datafile was created "+DATA_USERS/orcl_stby/datafile" is the correct one based on the values of the db_create_file_dest, db_unique_name and standby_file_management parameters.

Now, why the rest of datafiles are in the correct "+DATA_USERS/stby/datafile" ?
That's probably because when the RMAN restore was executed the db_unique_name was different than now
or
standby_file_management was not set to AUTO and in this case *_convert parameters prevail.

Solution

What you need to do to make sure the *_file_name_convert parameters work as you expect is to disable OMF, by resetting standby_file_management to MANUAL.

The above can be done the first time the standby database is started after recreating the standby controlfile, otherwise DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT parameters will be ignored.
Note also that the DB_FILE_NAME_CONVERT and DB_CREATE_FILE_DEST parameters will just do the automatic rename of database files based on those patterns, but if the database files are already created with a different name after an RMAN restore you will still need to rename the database files manually.

If you need to rename any database file once it was created you can follow the steps detailed in:
Doc ID 564993.1 : How to rename/move a datafile in the same ASM diskgroup

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28539951/viewspace-2123446/，如需转载，请注明出处，否则将追究法律责任。