今天日检 发现有一台机器 /u01 使用率非常高.


经过排查发现  一套备库数据库文件在本地 而没有在asm 中


查看数据库日志


Archived Log entry 22114 added for thread 1 sequence 221157 ID 0xf0a1891e dest 1:
Media Recovery Waiting for thread 1 sequence 221158 (in transit)
Recovery of Online Redo Log: Thread 1 Group 41 Seq 221158 Reading mem 0
  Mem# 0: +RECO_DM02/xx/standbylog/standby_group_02.log
Fri Dec 30 10:39:53 2016
Archived Log entry 22115 added for thread 4 sequence 482865 ID 0xf0a1891e dest 1:
Fri Dec 30 10:44:07 2016
Successfully added datafile 256 to media recovery
Datafile #256: '/u01/app/oracle/xx/datafile/o1_mf_erp_inde_d6clffrc_.dbf'
Fri Dec 30 10:45:02 2016
Errors in file /u01/app/oracle/diag/rdbms/xx//trace/xx_pr00_114426.trc:
ORA-19502: write error on file "/u01/app/oracle/xx/datafile/o1_mf_erp_inde_%u_.dbf", block number 1951744 (block size=8192)
ORA-27072: File I/O error
Additional information: 4
Additional information: 1951744
Additional information: 585728
File #257 added to control file as 'UNNAMED00257'.
Originally created as:
'+DATAC1/xx/datafile/erp_index.3306.931949013'
Recovery was unable to create the file as a new OMF file.
MRP0: Background Media Recovery terminated with error 1274
Non critical error ORA-48113 caught while writing to trace file "/u01/app/oracle/diag/rdbms/xx/trace/xx_pr00_114426.trc"
Error message: 
Writing to the above trace file is disabled for now on...
Errors in file /u01/app/oracle/diag/rdbms/xx/trace/xx_pr00_114426.trc:
ORA-01274: cannot add datafile '+DATAC1/xx/datafile/erp_index.3306.931949013' - file could not be created
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!






查询主库:


+DATAC1/xx/datafile/erp_index.3328.931948969 ERP_INDEX 256 2016-12-30
+DATAC1/xx/datafile/erp_index.3306.931949013 ERP_INDEX 257 2016-12-30
+DATAC1/xx/datafile/erp_index.3302.931949019 ERP_INDEX 258 2016-12-30




应该在12-30 号时,主库添加了文件,而备库 同步文件时，同步到了本地导致的


查看备库 db_create_file_dest  参数
SQL> show parameter db_create_file_dest
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      /u01/app/oracle






解决办法:
前提:ALTER SYSTEM SET standby_file_management='MANUAL' SCOPE=BOTH SID='*'; (之后要改回AUTO)
1.rename 现有文件
  alter database rename file '/u01/app/oracle/xx/datafile/o1_mf_erp_inde_d6clffrc_.dbf'  to '+DATA_DM02/xx/datafile/o1_mf_erp_inde_d6clffrc_.dbf';
2.创建失败的文件
alter database create datafile  '/u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/UNNAMED00257' as '+DATA_DM02/xx/datafile/erp_index.3306.931949013'
3.修改参数 
alter system set db_create_file_dest='+DATA_DM02' sid='*' scope=spfile;
问题解决