故障重现
# 主库添加datafile成功 alter tablespace crm2 add datafile '+DATA' size 20g autoextend on next 500m maxsize unlimited; # 备库报错信息如下 Tue Sep 11 14:33:11 2018 Errors in file /u01/app/oracle/diag/rdbms/crmsn/crmsn/trace/crmsn_pr00_9744.trc: ORA-01119: error in creating database file '+DATA' ORA-17502: ksfdcre:4 Failed to create file +DATA ORA-15041: diskgroup "DATA" space exhausted File #29 added to control file as 'UNNAMED00029'. Originally created as: '+DATA/crmpn/datafile/crm2.290.986567455' Recovery was unable to create the file as a new OMF file. MRP0: Background Media Recovery terminated with error 1274 Errors in file /u01/app/oracle/diag/rdbms/crmsn/crmsn/trace/crmsn_pr00_9744.trc: ORA-01274: cannot add datafile '+DATA/crmpn/datafile/crm2.290.986567455' - file could not be created Managed Standby Recovery not using Real Time Apply Recovery interrupted! Recovered data files to a consistent state at change 14132183932 Tue Sep 11 14:33:12 2018 MRP0: Background Media Recovery process shutdown (crmsn) |
分析解决
备库的REDO已经停止应用
$ dgmgrl sys/12jca7or1B@crmsn DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production Copyright (c) 2000, 2009, Oracle. All rights reserved. Welcome to DGMGRL, type "help" for information. Connected. DGMGRL> show configuration; Configuration - dg_db Protection Mode: MaxPerformance Databases: crmpn - Primary database crmsn - Physical standby database Error: ORA-16766: Redo Apply is stopped Fast-Start Failover: DISABLED Configura |
查看主备库datafile
col name for a50 select file#,name,status from v$datafile; FILE# NAME STATUS ---------- -------------------------------------------------- ------- 1 +DATA/crmpn/datafile/system.263.986184015 SYSTEM 2 +DATA/crmpn/datafile/sysaux.264.986184017 ONLINE 3 +DATA/crmpn/datafile/undotbs1.265.986184019 ONLINE 4 +DATA/crmpn/datafile/users.267.986184023 ONLINE 5 +DATA/crmpn/datafile/crm2.269.986193943 ONLINE 6 +DATA/crmpn/datafile/crm2.270.986193967 ONLINE 7 +DATA/crmpn/datafile/crm2.271.986193987 ONLINE 8 +DATA/crmpn/datafile/crm2.272.986194007 ONLINE 9 +DATA/crmpn/datafile/crm2.273.986194027 ONLINE 10 +DATA/crmpn/datafile/crm2.274.986194045 ONLINE 11 +DATA/crmpn/datafile/crm2_index.275.986194067 ONLINE 12 +DATA/crmpn/datafile/crm2_index.276.986194087 ONLINE 13 +DATA/crmpn/datafile/crm2_index.277.986194107 ONLINE 14 +DATA/crmpn/datafile/crm2_index.278.986194127 ONLINE 15 +DATA/crmpn/datafile/crm2_index.279.986194147 ONLINE 16 +FRA/crmpn/datafile/crm2.782.986554913 ONLINE 17 +FRA/crmpn/datafile/crm2.828.986554943 ONLINE 18 +FRA/crmpn/datafile/crm2.761.986554971 ONLINE 19 +FRA/crmpn/datafile/crm2.760.986554999 ONLINE 20 +FRA/crmpn/datafile/crm2.759.986555027 ONLINE 21 +FRA/crmpn/datafile/crm2.758.986555053 ONLINE 22 +FRA/crmpn/datafile/crm2.757.986555081 ONLINE 23 +FRA/crmpn/datafile/crm2.756.986555107 ONLINE 24 +DATA/crmpn/datafile/crm2.292.986565271 ONLINE 25 +FRA/crmpn/datafile/crm2.754.986555587 ONLINE 26 +FRA/crmpn/datafile/crm2.753.986555617 ONLINE 27 +FRA/crmpn/datafile/crm2.752.986555647 ONLINE 28 +DATA/crmpn/datafile/crm2.291.986565475 ONLINE 29 +DATA/crmpn/datafile/crm2.290.986568897 ONLINE |
查看备库
col name for a56 select file#,name,status from v$datafile; FILE# NAME STATUS ---------- -------------------------------------------------------- ------- 1 +DATA/crmsn/datafile/system.270.986233135 SYSTEM 2 +DATA/crmsn/datafile/sysaux.271.986233143 ONLINE 3 +DATA/crmsn/datafile/undotbs1.269.986233129 ONLINE 4 +DATA/crmsn/datafile/users.272.986233149 ONLINE 5 +DATA/crmsn/datafile/crm2.258.986231763 ONLINE 6 +DATA/crmsn/datafile/crm2.259.986231887 ONLINE 7 +DATA/crmsn/datafile/crm2.260.986232023 ONLINE 8 +DATA/crmsn/datafile/crm2.261.986232147 ONLINE 9 +DATA/crmsn/datafile/crm2.262.986232273 ONLINE 10 +DATA/crmsn/datafile/crm2.263.986232397 ONLINE 11 +DATA/crmsn/datafile/crm2_index.264.986232523 ONLINE 12 +DATA/crmsn/datafile/crm2_index.265.986232649 ONLINE 13 +DATA/crmsn/datafile/crm2_index.266.986232773 ONLINE 14 +DATA/crmsn/datafile/crm2_index.267.986232889 ONLINE 15 +DATA/crmsn/datafile/crm2_index.268.986233003 ONLINE 16 +DATA/crmsn/datafile/crm2.288.986555163 ONLINE 17 +DATA/crmsn/datafile/crm2.289.986555185 ONLINE 18 +DATA/crmsn/datafile/crm2.290.986555207 ONLINE 19 +DATA/crmsn/datafile/crm2.291.986555231 ONLINE 20 +DATA/crmsn/datafile/crm2.292.986555253 ONLINE 21 +DATA/crmsn/datafile/crm2.293.986555275 ONLINE 22 +DATA/crmsn/datafile/crm2.294.986555297 ONLINE 23 +DATA/crmsn/datafile/crm2.295.986555319 ONLINE 24 +DATA/crmsn/datafile/crm2.296.986565357 ONLINE 25 +DATA/crmsn/datafile/crm2.297.986555675 ONLINE 26 +DATA/crmsn/datafile/crm2.298.986555699 ONLINE 27 +DATA/crmsn/datafile/crm2.299.986555721 ONLINE 28 +FRA/crmsn/datafile/crm2.329.986565801 ONLINE 29 /u01/app/oracle/product/11.2.0.4/db_1/dbs/UNNAMED00029 RECOVER # |
恢复REDO应用
alter system set standby_file_management=manual; alter database datafile 29 offline drop; alter system set standby_file_management=auto; alter database recover managed standby database using current logfile disconnect; |
在主库删除datafile
alter tablespace crm2 drop datafile 29; # 正常情况下，备库29号文件也会被删除 # 删除前使用以下命令查看新创建的文件上是否有数据 col owner for a10 col SEGMENT_NAME for a20 col FILE_NAME for a50 SELECT E.OWNER , E.SEGMENT_TYPE AS SEGMENT_TYPE , E.SEGMENT_NAME AS SEGMENT_NAME , F.FILE_NAME AS FILE_NAME , SUM(E.BYTES)/1024/1024 AS SEGMENT_SIZE FROM DBA_EXTENTS E INNER JOIN DBA_DATA_FILES F ON E.FILE_ID= F.FILE_ID WHERE F.FILE_ID =29 GROUP BY E.OWNER,E.SEGMENT_TYPE,E.SEGMENT_NAME,F.FILE_NAME ORDER BY 5 DESC; # 如果有数据需要迁移，索引需要rebuild，如以下语句 alter index BFBHDD9.PK_JHDITEM rebuild tablespace jxc online; alter index BFBHDD9.PK_SKTXSJLC_SKFS rebuild tablespace jxc online; |
其他命令参考
alter database create datafile 29 as '+FRA' size 20g autoextend on next 500m maxsize unlimited;
|