异构dataguard下的db_file_name_convert设置

环境:
主库:win2012 server
从库:centos 6
db:11.2.0.4

1.主库上创建表空间

create tablespace tps_win01
logging datafile 'c:\oracle\app\oradata\win11g\tps_win01.dbf' size 50m
autoextend on
next 10m maxsize 2048m
extent management local
segment space management auto;

2.从库日志看到创建的文件

Media Recovery Waiting for thread 1 sequence 140 (in transit)
Recovery of Online Redo Log: Thread 1 Group 4 Seq 140 Reading mem 0
  Mem# 0: /u01/app/oracle/oradata/ora11g/stdbyredo04.log
Fri Dec 15 01:34:29 2023
WARNING: File being created with same name as in Primary
Existing file may be overwritten
Recovery created file C:\ORACLE\APP\ORADATA\WIN11G\TPS_WINAA01.DBF
Successfully added datafile 8 to media recovery
Datafile #8: '/u01/app/oracle/product/11.2.0.4/db_1/dbs/C:ORACLEAPPORADATAWIN11GTPS_WINAA01.DBF'

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ora11g/system01.dbf
/u01/app/oracle/oradata/ora11g/sysaux01.dbf
/u01/app/oracle/oradata/ora11g/undotbs01.dbf
/u01/app/oracle/oradata/ora11g/users01.dbf
/u01/app/oracle/oradata/ora11g/tps_hxl01.dbf
/u01/app/oracle/oradata/ora11g/tps_goldengate01.dbf
/u01/app/oracle/oradata/ora11g/tps_win01.dbf
/u01/app/oracle/product/11.2.0.4/db_1/dbs/C:ORACLEAPPORADATAWIN11GTPS_WINAA01.DBF

存放路径不是我们预期的

3.查看参数db_file_name_convert

SQL> show parameters db_file_name_convert;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert                 string      win11g, ora11g

这样配置在linux->linux是没问题的,但现在是win->linux,没有按照预期的转换

4.尝试修改数据文件名称

SQL> alter database recover managed standby database canel;
SQL> alter system set standby_file_management=manual;
SQL>alter database rename file '/u01/app/oracle/product/11.2.0.4/db_1/dbs/C:ORACLEAPPORADATAWIN11GTPS_WINAA01.DBF' to '/u01/app/oracle/oradata/ora11g/tps_winaa01.dbf';
alter database rename file '/u01/app/oracle/product/11.2.0.4/db_1/dbs/C:ORACLEAPPORADATAWIN11GTPS_WINAA01.DBF' to '/u01/app/oracle/oradata/ora11g/tps_winaa01.dbf'
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01121: cannot rename database file 8 - file is in use or recovery
ORA-01110: data file 8:
'/u01/app/oracle/product/11.2.0.4/db_1/dbs/C:ORACLEAPPORADATAWIN11GTPS_WINAA01.DBF'

好像不能直接修改,数据库需要先修改到mount模式

5.数据库修改到mount状态下

SQL> shutdown immediate
SQL> startup mount

6.数据文件拷贝到正确的目录

cp /u01/app/oracle/product/11.2.0.4/db_1/dbs/C:ORACLEAPPORADATAWIN11GTPS_WINAA01.DBF /u01/app/oracle/oradata/ora11g/tps_winaa01.dbf

7.再次修改
SQL>alter database rename file '/u01/app/oracle/product/11.2.0.4/db_1/dbs/C:ORACLEAPPORADATAWIN11GTPS_WINAA01.DBF' to '/u01/app/oracle/oradata/ora11g/tps_winaa01.dbf';

8.打开数据库
SQL> alter database open;

9.参数修改为自动
SQL>alter system set standby_file_management=auto;

10.应用日志
SQL> alter database recover managed standby database using current logfile disconnect from session;

11.修改参数db_file_name_convert

SQL> alter system set db_file_name_convert='C:\oracle\app\oradata\win11g','/u01/app/oracle/oradata/ora11g' scope=both;
alter system set log_file_name_convert='C:\oracle\app\oradata\win11g','/u01/app/oracle/oradata/ora11g' scope=both
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

alter system set db_file_name_convert='C:\oracle\app\oradata\win11g','/u01/app/oracle/oradata/ora11g' scope=spfile;

12.重启生效

alter database recover managed standby database cancel;
shutdown immediate
startup
alter database recover managed standby database using current logfile disconnect from session;

发现没有设置正确

主库创建表空间,从库发现数据文件不是放置在预期的地方
Successfully added datafile 9 to media recovery
Datafile #9: '/u01/app/oracle/product/11.2.0.4/db_1/dbs/C:ORACLEAPPORADATAWIN11GTPS_WINBB01.DBF'

正确的设置如下:
alter system set db_file_name_convert='C:\ORACLE\APP\ORADATA\WIN11G\','/u01/app/oracle/oradata/ora11g/' scope=spfile;

最后一个目录需要使用\(win)或是/(linux)结束