适用范围

Oracle Database - Enterprise Edition - Version 11.2.0.4.0 and later

问题概述

Oracle DataGuard duplicate时如果pr(primary)和dr(standby)中数据文件对应diskgroup名称一致是否需要设置db_file_name_convert参数？

问题原因

SQL> startup nomount pfile='bak_pfile.ora';
ORACLE instance started.

Total System Global Area 5328027648 bytes
Fixed Size                  2262608 bytes
Variable Size            1040189872 bytes
Database Buffers         4278190080 bytes
Redo Buffers                7385088 bytes
SQL> show parameter convert

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert                 string
log_file_name_convert                string
SQL> show parameter db_create_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      +DATA01
SQL> 


contents of Memory Script:
{
   set newname for clone tempfile  1 to new;
   switch clone tempfile all;
   set newname for clone datafile  1 to new;
   set newname for clone datafile  2 to new;
   set newname for clone datafile  3 to new;
   set newname for clone datafile  4 to new;
   set newname for clone datafile  5 to new;
   set newname for clone datafile  6 to new;
   set newname for clone datafile  7 to new;
   backup as copy reuse
   datafile  1 auxiliary format new
   datafile  2 auxiliary format new
   datafile  3 auxiliary format new
   datafile  4 auxiliary format new
   datafile  5 auxiliary format new
   datafile  6 auxiliary format new
   datafile  7 auxiliary format new
   ;
   sql 'alter system archive log current';
}

### 将数据文件 name=+OCR_VOT/orcl/datafile/test02.256.1124031449 创建到  name=+DATA01/orclbak/datafile/test02.301.1124031935
input datafile file number=00007 name=+OCR_VOT/orcl/datafile/test02.256.1124031449
output file name=+DATA01/orclbak/datafile/test02.301.1124031935 tag=TAG20221221T150526
channel prmy1: datafile copy complete, elapsed time: 00:00:01
channel prmy1: starting datafile copy
input datafile file number=00004 name=+DATA01/orcl/datafile/users.259.1122886301
output file name=+DATA01/orclbak/datafile/users.288.1124031937 tag=TAG20221221T150526
channel prmy1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 21-DEC-22
starting media recovery

archived log for thread 1 with sequence 120 is already on disk as file +DATA01/orclbak/archivelog/2022_12_21/thread_1_seq_120.295.1124031939
archived log for thread 1 with sequence 121 is already on disk as file +DATA01/orclbak/archivelog/2022_12_21/thread_1_seq_121.261.1124031939
archived log file name=+DATA01/orclbak/archivelog/2022_12_21/thread_1_seq_120.295.1124031939 thread=1 sequence=120
archived log file name=+DATA01/orclbak/archivelog/2022_12_21/thread_1_seq_121.261.1124031939 thread=1 sequence=121
media recovery complete, elapsed time: 00:00:00
Finished recover at 21-DEC-22
Finished Duplicate Db at 21-DEC-22
released channel: prmy1
released channel: stby1

Recovery Manager complete.

解决方案

Oracle DataGuard duplicate时如果pr(primary)和dr(standby)中数据文件对应diskgroup名称一致时，如果不设置db_file_name_convert参数，Oracle会自动将数据文件创建到db_create_file_dest参数指定的路径，所以建议按标准设置db_file_name_convert参数。

参考文档

Usage and Limitation of db_file_name_convert and log_file_name_convert (Doc ID 1367014.1)