背景
主库一个大容量磁盘组，备库多个小容量磁盘组场景下的问题。
主库磁盘组DATADG，备库分为DATA1和DATA2。
duplicate过程中转换方法
采用set newname方式转换路径
rman target sys/oracle@db11g auxiliary sys/oracle@db11gdg
run {
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate auxiliary channel c3 type disk;
allocate auxiliary channel c4 type disk;
set newname for datafile 1 to '+DATA1';
set newname for datafile 2 to '+DATA1';
set newname for datafile 3 to '+DATA1';
set newname for datafile 4 to '+DATA1';
set newname for datafile 5 to '+DATA1';
set newname for datafile 6 to '+DATA1';
set newname for datafile 7 to '+DATA2';
set newname for tempfile 1 to '+DATA1';
DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
DORECOVER
NOFILENAMECHECK;
switch datafile all;
switch tempfile all;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}
完成之后备库磁盘组使用情况如下
其中DATA1剩余可用空间1773MB
ASMCMD [+] > lsdg
State Type Rebal Sector Logical_Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
MOUNTED NORMAL N 512 512 4096 1048576 40960 3546 0 1773 0 N DATA1/
MOUNTED EXTERN N 512 512 4096 1048576 20480 17909 0 17909 0 N DATA2/
MOUNTED NORMAL N 512 512 4096 4194304 30720 19208 10240 4484 0 Y OCRDG/
ASMCMD [+] >
主库新增数据文件时备库转换参数优先级
备库未配置OMF且db_file_name_convert参数配置错误
当前备库参数配置如下
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string +DATADG, +DATA1, +DATADG, +DATA2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string
主库新增文件
SQL> alter tablespace TEST add datafile '+DATADG' size 2g ;
Tablespace altered.
备库添加文件报错
Sun Apr 09 20:15:51 2023
Errors in file /u01/app/oracle/diag/rdbms/db11gdg/db11g1/trace/db11g1_pr00_1601.trc:
ORA-01119: error in creating database file '+data1'
ORA-17502: ksfdcre:4 Failed to create file +data1
ORA-15041: diskgroup "DATA1" space exhausted
File #8 added to control file as 'UNNAMED00008'.
Originally created as:
'+DATADG/db11g/datafile/test.285.1133727341'
Recovery was unable to create the file as:
'+data1'
MRP0: Background Media Recovery terminated with error 1274
Errors in file /u01/app/oracle/diag/rdbms/db11gdg/db11g1/trace/db11g1_pr00_1601.trc:
ORA-01274: cannot add datafile '+DATADG/db11g/datafile/test.285.1133727341' - file could not be created
Sun Apr 09 20:15:52 2023
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Recovered data files to a consistent state at change 3268233
Sun Apr 09 20:15:52 2023
MRP0: Background Media Recovery process shutdown (db11g1)
备库数据文件如下
SQL> select name,status from v$datafile；
NAME STATUS
------------------------------------------------------------ -------
+DATA1/db11gdg/datafile/system.314.1133726567 SYSTEM
+DATA1/db11gdg/datafile/sysaux.315.1133726567 ONLINE
+DATA1/db11gdg/datafile/undotbs1.316.1133726583 ONLINE
+DATA1/db11gdg/datafile/users.318.1133726585 ONLINE
+DATA1/db11gdg/datafile/undotbs2.317.1133726583 ONLINE
+DATA1/db11gdg/datafile/test.313.1133726521 ONLINE
+DATA2/db11gdg/datafile/test1.256.1133726523 ONLINE
/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00008 RECOVER
8 rows selected.
恢复步骤
SQL> alter system set standby_file_management=manual;
System altered.
SQL> alter database create datafile '/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00008' as '+DATA2' size 2g;
Database altered.
SQL> alter system set standby_file_management=auto;
System altered.
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
SQL> select name,status from v$datafile;
NAME STATUS
------------------------------------------------------------ -------
+DATA1/db11gdg/datafile/system.314.1133726567 SYSTEM
+DATA1/db11gdg/datafile/sysaux.315.1133726567 ONLINE
+DATA1/db11gdg/datafile/undotbs1.316.1133726583 ONLINE
+DATA1/db11gdg/datafile/users.318.1133726585 ONLINE
+DATA1/db11gdg/datafile/undotbs2.317.1133726583 ONLINE
+DATA1/db11gdg/datafile/test.313.1133726521 ONLINE
+DATA2/db11gdg/datafile/test1.256.1133726523 ONLINE
+DATA2/db11gdg/datafile/test.263.1133727559 ONLINE
8 rows selected.
应对方案
备库配置OMF正确即可
Creating a Standby Database That Uses OMF or Oracle ASM
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.
OMF参数优先与db_file_name_convert参数，并且db_create_file_dest参数可以在线调整
当前备库参数配置如下
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string +DATADG, +DATA1, +DATADG, +DATA2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string +DATA2
主库新增文件
SQL> alter tablespace TEST add datafile '+DATADG' size 2g ;
Tablespace altered.
备库正常自动添加文件
Sun Apr 09 20:27:57 2023
Successfully added datafile 8 to media recovery
Datafile #8: '+DATA2/db11gdg/datafile/test.263.1133728069'
备库配置正确的db_file_name_convert
如果要转换的文件名匹配字符串列表中的多个模式，则第一个匹配的模式生效。
DB_FILE_NAME_CONVERT
当前备库参数配置如下
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string +DATADG, +DATA2, +DATADG, +DATA1
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string
主库新增文件
SQL> alter tablespace TEST add datafile '+DATADG' size 2g ;
Tablespace altered.
备库正常自动添加文件
Sun Apr 09 20:49:22 2023
Recovery created file +data2
Successfully added datafile 8 to media recovery
Datafile #8: '+DATA2/db11gdg/datafile/test.277.1133729353'
订阅号：DongDB手记
墨天轮：https://www.modb.pro/u/231198
最后修改时间：2023-04-10 21:14:53
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
