[20170203]dg磁碟空間不足的處理.txt

[20170203]dg磁碟空間不足的處理.txt

--生產系統,上班檢查發現dg 停止日誌應用:

DGMGRL> show configuration
Configuration - dbcn

  Protection Mode: MaxPerformance
  Databases:
    dbcn    - Primary database
    dbcndg  - Physical standby database
      Error: ORA-16766: Redo Apply is stopped

    dbcndg2 - Physical standby database
      Error: ORA-16766: Redo Apply is stopped

Fast-Start Failover: DISABLED

Configuration Status:
ERROR

--檢查日誌才發現節前增加1個資料檔案:
Errors in file /u01/app/oracle/diag/rdbms/dbcndg2/dbcndg2/trace/dbcndg2_pr00_31684.trc:
ORA-19502: write error on file "/u01/app/oracle/oradata/dbcndg2/datafile/portal_emr19.dbf", block number 3377792 (block size=8192)
ORA-27072: File I/O error
Additional information: 4
Additional information: 3377792
Additional information: 278528
File #53 added to control file as 'UNNAMED00053'.
Originally created as:
'+DATAC1/dbcn/datafile/portal_emr19.dbf'

--//同事增加資料檔案時報錯,磁碟空間不足.同事工作太不認真了,增加資料檔案至少應該檢查磁碟空間是否充足,也應該檢查日誌是否應用.
--//記錄一下解決步驟:

SYS@dbcndg2> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@dbcndg2> startup nomount;
ORACLE instance started.
Total System Global Area 8.0973E+10 bytes
Fixed Size                  2261968 bytes
Variable Size            9932114992 bytes
Database Buffers         7.0867E+10 bytes
Redo Buffers              171487232 bytes

SYS@dbcndg2> alter database mount standby database;
Database altered.

--//檢查發現磁碟空間已經嚴重不足,僅僅剩下20G.刪除一些不需要的日誌也僅僅回收幾個G的空間.發現db_recovery_file_dest_size設定有一些大(50G).
--//因為dg上開啟flashback,試著設定小一點點看看:

SYS@dbcndg2> alter system set db_recovery_file_dest_size=20G;
System altered.

--//再次檢查空間已經足夠.先設定standby_file_management=manual,不然無法改名.
SYS@dbcndg2> alter system set standby_file_management=manual scope=memory;
System altered.

SYS@dbcndg2> alter database rename file '/u01/app/oracle/product/11.2.0.4/db_1/dbs/UNNAMED00053' to '/u01/app/oracle/oradata/dbcndg2/datafile/portal_emr19.dbf';
alter database rename file '/u01/app/oracle/product/11.2.0.4/db_1/dbs/UNNAMED00053' to '/u01/app/oracle/oradata/dbcndg2/datafile/portal_emr19.dbf'
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01141: error renaming data file 53 - new file '/u01/app/oracle/oradata/dbcndg2/datafile/portal_emr19.dbf' not found
ORA-01111: name for data file 53 is unknown - rename to correct file
ORA-01110: data file 53: '/u01/app/oracle/product/11.2.0.4/db_1/dbs/UNNAMED00053'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

--//才想起來UNNAMED00053檔案也沒有建立.必須使用alter database create datafile 命令:

alter database create datafile '/u01/app/oracle/product/11.2.0.4/db_1/dbs/UNNAMED00053' as '/u01/app/oracle/oradata/dbcndg2/datafile/portal_emr19.dbf';
--//等OK.

SYS@dbcndg2> alter database open read only ;

--//檢查日誌並沒有應用.傳輸正常.

在主機上執行:
DGMGRL> EDIT DATABASE dbcndg2 set state= APPLY-OFF;
Succeeded.
DGMGRL> EDIT DATABASE dbcndg2 set state= APPLY-ON;
Succeeded.

SYS@dbcn1> alter system set log_archive_dest_state_3=defer scope=memory;
System altered.

SYS@dbcn1> alter system set log_archive_dest_state_3=enable scope=memory;
System altered.

--//再次檢查日誌已經開始應該了.
select process,pid,status,group#,thread#,sequence#,block#,blocks,delay_mins from v$managed_standby order by thread#,group# ;

PROCESS       PID STATUS       GROUP# THREAD#  SEQUENCE#     BLOCK#     BLOCKS DELAY_MINS
--------- ------- ------------ ------ ------- ---------- ---------- ---------- ----------
RFS         18638 IDLE         N/A          0          0          0          0          0
RFS         17453 IDLE         N/A          0          0          0          0          0
RFS         16915 IDLE         N/A          0          0          0          0          0
RFS         16906 IDLE         N/A          0          0          0          0          0
RFS         17439 IDLE         N/A          0          0          0          0          0
RFS         18636 IDLE         N/A          0          0          0          0          0
ARCH        16869 CONNECTED    N/A          0          0          0          0          0
ARCH        16862 CLOSING      10           1       3286      73728       1315          0
RFS         18577 IDLE         5            1       3287     426078          2          0
ARCH        16873 CLOSING      9            1       3285     374784        554          0
MRP0        18441 APPLYING_LOG N/A          1       3255    2590716    7258272          0
ARCH        16867 CLOSING      14           2       2612      18432       1759          0
ARCH        16875 CLOSING      15           2       2611      96256       1905          0
RFS         18575 IDLE         3            2       2613     167454          1          0
14 rows selected.

--//已經建議更換更大的儲存,不然估計最近一段時間要認真監測.不然還會出現磁碟空間不足的情況.