Oracle主库添加datafile备库空间不足错误处理

故障重现

# 主库添加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;
文章目录
  1. 1. 故障重现
  2. 2. 分析解决
    1. 2.1. 备库的REDO已经停止应用
    2. 2.2. 查看主备库datafile
  3. 3. 查看备库
  4. 4. 恢复REDO应用
  5. 5. 在主库删除datafile
  6. 6. 其他命令参考