记录一次因备库空间不足造成的DG crash问题

此篇文章由SR case及MOS文章整理而成。

背景

收到数据库监控报警，生产库ASM空间不足，使用率已达到90%以上。第一反应是不是DG又报错了？赶紧连上灾备环境。还真是，告警日志中报错如下：

Fri Aug 4 01:55:24 2018

Primary database is in MAXIMUM PERFORMANCE mode

RFS[170477]: Assigned to RFS process 25297178

RFS[170477]: No standby redo logfiles created

Creating archive destination file : /arch/2_150668_864251862.dbf (2097152 blocks)

RFS[170474]: Possible network disconnect with primary database

果然，又是DG的问题。

原因分析

查看磁盘空间，发现是数据盘空间使用率达到100%。想起来上周在主库上增加了几个数据文件，应该就是增加数据文件引起的问题。

报错时间点standby端日志如下：

Fri Aug 03 17:43:36 2018

Archived Log entry 127686 added for thread 2 sequence 150103 rlc 864251862 ID 0x45f9245c dest 2:

RFS[170399]: No standby redo logfiles created

RFS[170399]: Opened log for thread 2 sequence 150104 dbid 1034179463 branch 864251862

Fri Aug 03 17:43:39 2018

Media Recovery Log /arch/2_150103_864251862.dbf

Fri Aug 03 17:44:17 2018

Successfully added datafile 294 to media recovery

Datafile #294: '/dgdata/PROD/datafile/NFPRODX/datafile/o1_mf_apps_ts__fp88yhkq_.dbf'

Errors in file /prod/db/11.2.0/admin/diag/rdbms/prodx/PROD/trace/PROD_pr00_12189842.trc:

ORA-19502: write error on file "/dgdata/PROD/datafile/PRODX/datafile/o1_mf_apps_ts__fp88zl84_.dbf", block number 591232 (block size=8192)

ORA-27063: number of bytes read/written is incorrect

IBM AIX RISC System/6000 Error: 28:No space left on device

Additional information: -1

Additional information: 1048576

File #295 added to control file as 'UNNAMED00295'.

Originally created as:

'+PRODDATA/prod/datafile/apps_ts_tx_data.578.983208635'

Recovery was unable to create the file as a new OMF file.

Errors with log /arch/2_150103_864251862.dbf

从上面的日志可以看出，在media recocery时，Datafile#294正常增加到standby数据库中。但是，Datafile#295确因空间不足报错。File #295 added to control file as 'UNNAMED00295'。

解决方案

首先，当然是空间问题。扩容，或者删除不需要的文件。

其次，尝试解决问题。

直接启动recovery进程：

SQL> alter database recover managed standby database disconnect from session;

查看日志：

Mon Aug 13 10:31:56 2018

alter database recover managed standby database disconnect from session

Attempt to start background Managed Standby Recovery process (NFPROD)

Mon Aug 13 10:31:56 2018

MRP0 started with pid=27, OS id=28835918

MRP0: Background Managed Standby Recovery process started (NFPROD)

started logmerger process

Mon Aug 13 10:32:01 2018

Managed Standby Recovery not using Real Time Apply

MRP0: Background Media Recovery terminated with error 1111

Errors in file /prod/db/11.2.0/admin/diag/rdbms/nfprodx/PROD/trace/PROD_pr00_30474654.trc:

ORA-01111: name for data file 295 is unknown - rename to correct file

ORA-01110: data file 295: '/prod/db/11.2.0/dbs/UNNAMED00295'

ORA-01157: cannot identify/lock data file 295 - see DBWR trace file

ORA-01111: name for data file 295 is unknown - rename to correct file

ORA-01110: data file 295: '/prod/db/11.2.0/dbs/UNNAMED00295'

Recovery Slave PR00 previously exited with exception 1111

MRP0: Background Media Recovery process shutdown (PROD)

Completed: alter database recover managed standby database disconnect from session

MRP0进程异常结束，看来直接起的方式不可行。

（红色字体的UNNAMED00295路径后面会用到）

尝试另一种方法：

SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

SQL>ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;

SQL>Alter database create datafile '/prod/db/11.2.0/dbs/UNNAMED00295' as ' /dgdata/ datafile/PRODX/datafile/datafile_295.dbf’ ;

###这里要注意的是，/prod/db/11.2.0/dbs/UNNAMED00295这个文件名是告警日志中报错显示出的文件名，而且这个文件不一定真的存在。MOS的解释是Create a empty datafile which same structure as the datafile and it would need all archivelogs from time of creation for recovery。如果文件已经存在，则使用另一个命令来操作，具体可参考MOS。

SQL>ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

查看日志，DG恢复正常。

图片来源：http://www.123rf.com.cn/

参考文档：How to resolve ORA-01111 ORA-01110 ORA-01157 in a physical standby database (文档ID 1416554.1)

---END---