primary库新增数据文件后，standby库无法创建文件并终止数据同步

2017-08-09 12:16 abce 阅读(659) 评论(0) 编辑收藏举报

主库是RAC环境，使用asm存放数据文件，备库是操作系统本地文件系统存放数据文件。
在主库执行以下操作：

1	`SQL> alter tablespace ysdv add datafile` `'+data'` `size 1024m autoextend on next 100m maxsize 10240m;`

备库的alert日志报以下错误：

Mon Jul 31 13:00:59 2017
Errors in file /d01/app/oracle/diag/rdbms/dvstby/dvstby/trace/dvstby_pr00_3809.trc:
ORA-01119: error in creating database file '+DATA'
ORA-17502: ksfdcre:4 Failed to create file +DATA
ORA-15001: diskgroup "DATA" does not exist or is not mounted
ORA-15077: could not locate ASM instance serving a required diskgroup
ORA-29701: unable to connect to Cluster Synchronization Service
File #7 added to control file as 'UNNAMED00007'.
Originally created as:
'+DATA/dv/datafile/dv.328.950783567'
Recovery was unable to create the file as a new OMF file.
Errors with log /d01/app/oracle/arch/2_1729_930568019.dbf
MRP0: Background Media Recovery terminated with error 1274
Errors in file /d01/app/oracle/diag/rdbms/dvstby/dvstby/trace/dvstby_pr00_3809.trc:
ORA-01274: cannot add datafile '+DATA/dv/datafile/dv.328.950783567' - file could not be created
Mon Jul 31 13:02:12 2017
Recovery interrupted!
Recovery stopped due to failure in applying recovery marker (opcode 17.30).
Datafiles are recovered to a consistent state at change 172502737 but controlfile could be ahead of datafiles.
Mon Jul 31 13:02:13 2017
MRP0: Background Media Recovery process shutdown (dvstby)

查看备库参数设置：

SQL> show parameter convert
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert                 string      +DATA/dv/DATAFILE/, /d01/app
                                                 /oracle/oradata/dvstby/dataf
                                                 ile/, +DATA/dv/TEMPFILE/, /s
                                                 01/app/oracle/oradata/dvstby
                                                 /tempfile/
log_file_name_convert                string      +DATA/dv/ONLINELOG/, /d01/ap
                                                 p/oracle/oradata/dvstby/onli
                                                 nelog/
SQL> show parameter standby_file_management
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_file_management              string      AUTO
SQL> 

原因是主备库的文件路径不一样，备库在自动转换文件名称时失败。

修改方案：
1.将stndby_file_management设置为manual

SQL> show parameter standby_file_management
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_file_management              string      AUTO
SQL> alter system set standby_file_management='MANUAL';

2.找出unname的数据文件

SQL> select name from v$datafile;
 
NAME
--------------------------------------------------------------------------------
/d01/app/oracle/oradata/dvstby/datafile/system.293.930567941
/d01/app/oracle/oradata/dvstby/datafile/sysaux.294.930567941
/d01/app/oracle/oradata/dvstby/datafile/undotbs1.323.937148625
/d01/app/oracle/oradata/dvstby/datafile/users.296.930567941
/d01/app/oracle/oradata/dvstby/datafile/undotbs2.324.937148779
/d01/app/oracle/oradata/dvstby/datafile/dv.306.930571875
/d01/app/oracle/dvuct/11.2.0/dbhome_1/dbs/UNNAMED00007
/d01/app/oracle/oradata/dvstby/datafile/dv.325.931996247
/d01/app/oracle/oradata/dvstby/datafile/dv.326.931996261
/d01/app/oracle/oradata/dvstby/datafile/dv.327.931996267
 
10 rows selected.

3.重命名/重建数据文件，指定正确的名称

1	`SQL> alter database create datafile` `'/d01/app/oracle/dvuct/11.2.0/dbhome_1/dbs/UNNAMED00007'` `as` `'/d01/app/oracle/oradata/dvstby/datafile/dv.328.950783567';`

4.再次查看

SQL> select name from v$datafile;
 
NAME
--------------------------------------------------------------------------------
/d01/app/oracle/oradata/dvstby/datafile/system.293.930567941
/d01/app/oracle/oradata/dvstby/datafile/sysaux.294.930567941
/d01/app/oracle/oradata/dvstby/datafile/undotbs1.323.937148625
/d01/app/oracle/oradata/dvstby/datafile/users.296.930567941
/d01/app/oracle/oradata/dvstby/datafile/undotbs2.324.937148779
/d01/app/oracle/oradata/dvstby/datafile/dv.306.930571875
/d01/app/oracle/oradata/dvstby/datafile/dv.328.950783567
/d01/app/oracle/oradata/dvstby/datafile/dv.325.931996247
/d01/app/oracle/oradata/dvstby/datafile/dv.326.931996261
/d01/app/oracle/oradata/dvstby/datafile/dv.327.931996267
 
10 rows selected.

5.将stndby_file_management设置为auto

1	`SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO scope=both;`

6.开启mrp

1	`SQL> alter database recover managed standby database using current logfile disconnect;`

7.验证mrp已经启动

SQL> select process, status , sequence# from v$managed_standby;
 
PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
ARCH      CLOSING            3231
ARCH      CONNECTED             0
ARCH      CLOSING            1791
ARCH      CLOSING            3232
ARCH      CLOSING            1792
MRP0      APPLYING_LOG       3166
RFS       IDLE                  0
RFS       IDLE                  0
RFS       IDLE                  0
RFS       IDLE                  0
RFS       IDLE               1793
RFS       IDLE                  0
RFS       IDLE                  0
RFS       IDLE               3233
 
14 rows selected.
 
SQL>