异构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)结束
