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

最新推荐文章于 2024-08-21 23:41:45 发布

congse3359

最新推荐文章于 2024-08-21 23:41:45 发布

阅读量1.1k

点赞数

文章标签：数据库

   在dataguard的配置中，db_file_name_convert是比较常用的参数。如果备库中参数db_create_file_dest未设置，则数据文件路径转换是由参数db_file_name_convert决定。
但如果参数db_file_name_convert和db_create_file_dest都设置时，要注意：
测试环境：oracle version:11.2.0.4 standby_file_management=AUTO 主备同为文件系统
测试结论：
1.在备库创建时:
    a.通过restore还原的数据文件，如果备份中的数据文件是由手动命名的文件，则备库中数据文件的位置是由备份中的数据文件原位置和参数db_file_name_convert决定。
    b.通过restore还原的数据文件，如果备份中的数据文件是由OMF命名的文件，则备库中数据文件的位置是由参数db_create_file_dest决定。
    c.通过recover生成的数据文件，主库手动命名的文件和OMF命名的文件，备库中数据文件的位置都是由参数db_create_file_dest决定。
3.在备库正常使用时：
    a.MRP进程做日志应用，主库手动命名的文件和OMF命名的文件，备库中数据文件的位置都是由参数db_create_file_dest决定。
3.在备库启动时:
    a.之前创建备库时通过restore还原的数据文件，并且该文件是手动命名的文件，该文件启动时依赖参数db_file_name_convert。如果db_file_name_convert不正确，会报ORA-10458，ORA-01157，ORA-01110错误。
    b.之前创建时通过restore还原的数据文件，并且该文件是OMF命名的文件，该文件启动时不依赖参数db_file_name_convert。
    c.通过recover或MRP进程产生的数据文件，并且该文件是手动命名的文件或OMF命名的文件,该文件启动时不依赖参数db_file_name_convert。

参考文档： Dataguard DB/LOG FILE NAME CONVERT has been set but files are created in a different directory (文档 ID 1348512.1)

Dataguard DB/LOG FILE NAME CONVERT has been set but files are created in a different directory (文档 ID 1348512.1)

转到底部

In this Document

Symptoms

Changes

Cause

Solution

References

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.2 and later
Information in this document applies to any platform.
***Checked for relevance on 02-Apr-2013***

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28539951/viewspace-2123446/