[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.
--//已经建议更换更大的存储,不然估计最近一段时间要认真监测.不然还会出现磁盘空间不足的情况.
