主库新添加数据文件后 备库修改数据文件路径——备库OMF管理方式

文档课题：备库db_create_file_dest和db_file_name_convert&log_file_name_convert同时存在，主库添加数据文件后会按照omf的路径生成数据文件，如何将新添加的数据文件路径修改到和其它数据文件路径一样？

系统：oel 7.9 64位

数据库：oracle 11.2.0.4 64位

环境：rac(双节点) + dg

以下测试过程：

主库添加数据文件.

> alter tablespace users add datafile '+data' size 100m autoextend on;

> select file#,name,status,bytes/1024/1024 from v$datafile

     FILE# NAME                                               STATUS  BYTES/1024/1024

---------- -------------------------------------------------- ------- ---------------

         1 +DATA/orcl/datafile/system.256.1107818145          SYSTEM              740

         2 +DATA/orcl/datafile/sysaux.257.1107818147          ONLINE              700

         3 +DATA/orcl/datafile/undotbs1.258.1107818147        ONLINE              105

         4 +DATA/orcl/datafile/users.259.1107818147           ONLINE            16.25

         5 +DATA/orcl/datafile/undotbs2.267.1107818231        ONLINE               50

         6 +DATA/orcl/datafile/system.319.1110101255          SYSTEM               20

         7 +DATA/orcl/datafile/users.555.1117045771           ONLINE              100

7 rows selected.

说明：+DATA/orcl/datafile/users.553.1116979173为新增数据文件.

以下操作均在备库执行.

> select file#,name from v$datafile;

     FILE# NAME

---------- -----------------------------------------------------------------

         1 /u01/app/oracle/oradata/system.256.1107818145

         2 /u01/app/oracle/oradata/sysaux.257.1107818147

         3 /u01/app/oracle/oradata/undotbs1.258.1107818147

         4 /u01/app/oracle/oradata/users.259.1107818147

         5 /u01/app/oracle/oradata/undotbs2.267.1107818231

         6 /u01/app/oracle/oradata/system.319.1110101255

         7 /u01/app/oracle/oradata/ORCLDG/datafile/o1_mf_users_kmjs15z3_.dbf

7 rows selected.

说明：可以看到o1_mf_users_kmjs15z3_.dbf为备库端新增文件.

> show parameter db_create

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_create_file_dest                  string      /u01/app/oracle/oradata

db_create_online_log_dest_1          string

db_create_online_log_dest_2          string

db_create_online_log_dest_3          string

db_create_online_log_dest_4          string

db_create_online_log_dest_5          string

备库omf路径为/u01/app/oracle/oradata.

> alter database recover managed standby database cancel;

> alter system set standby_file_management=manual;

[oracle@hisdbdg ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Sun Oct 2 14:37:15 2022

 

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

 

connected to target database: ORCL (DBID=1635598952)

 

RMAN> backup as copy datafile 7 format '/u01/app/oracle/oradata/users.553.1116979173';

Starting backup at 02-OCT-22

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=130 device type=DISK

channel ORA_DISK_1: starting datafile copy

input datafile file number=00007 name=/u01/app/oracle/oradata/ORCLDG/datafile/o1_mf_users_kmjs15z3_.dbf

output file name=/u01/app/oracle/oradata/users.553.1116979173 tag=TAG20221002T143939 RECID=15 STAMP=1117031980

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03

Finished backup at 02-OCT-22

SQL> alter database datafile 7 offline for drop;

SQL> select name,file#,status from v$datafile;

NAME                                                                   FILE# STATUS

----------------------------------------------------------------- ---------- -------

/u01/app/oracle/oradata/system.256.1107818145                              1 SYSTEM

/u01/app/oracle/oradata/sysaux.257.1107818147                              2 ONLINE

/u01/app/oracle/oradata/undotbs1.258.1107818147                            3 ONLINE

/u01/app/oracle/oradata/users.259.1107818147                               4 ONLINE

/u01/app/oracle/oradata/undotbs2.267.1107818231                            5 ONLINE

/u01/app/oracle/oradata/system.319.1110101255                              6 SYSTEM

/u01/app/oracle/oradata/ORCLDG/datafile/o1_mf_users_kmjs15z3_.dbf          7 RECOVER

SQL> shutdown immediate

SQL> startup mount;

[oracle@hisdbdg oradata]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Sun Oct 2 18:33:51 2022

 

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

 

connected to target database: ORCL (DBID=1635598952, not open)

RMAN> switch datafile 7 to copy;

 

using target database control file instead of recovery catalog

datafile 9 switched to datafile copy "/u01/app/oracle/oradata/users.555.1117045771"

SQL> alter database datafile 7 online;

Database altered.

SQL> select name from v$datafile;

NAME

-----------------------------------------------------------------

/u01/app/oracle/oradata/system.256.1107818145

/u01/app/oracle/oradata/sysaux.257.1107818147

/u01/app/oracle/oradata/undotbs1.258.1107818147

/u01/app/oracle/oradata/users.259.1107818147

/u01/app/oracle/oradata/undotbs2.267.1107818231

/u01/app/oracle/oradata/system.319.1110101255

/u01/app/oracle/oradata/users.555.1117045771

7 rows selected.

SQL> alter database open;

Database altered.

SQL > alter system set standby_file_management=auto;

SQL > alter system set db_create_file_dest=’’