oracle 11g ADG 由于磁盘空间不足导致同步问题

应用软件厂商反映adg 备库端数据已经好几天没有同步了，问题查看

发现adg备库没有应用日志进程：

SQL>  select PROCESS,PID, STATUS ,CLIENT_PROCESS  from v$managed_standby;
PROCESS          PID STATUS       CLIENT_P
--------- ---------- ------------ --------
ARCH           14981 CONNECTED    ARCH
ARCH           14983 CONNECTED    ARCH
ARCH           14985 CONNECTED    ARCH
ARCH           14987 CONNECTED    ARCH
 

开启应用日志：

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Database altered.

SQL> select open_mode,PROTECTION_LEVEL,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;

OPEN_MODE            PROTECTION_LEVEL     DATABASE_ROLE    SWITCHOVER_STATUS
-------------------- -------------------- ---------------- --------------------
READ ONLY MAXIMUM PERFORMANCE  PHYSICAL STANDBY NOT ALLOWED

发现日志还是没有正常运用，于是查看告警日志发现：

Errors in file /u01/app/dbBase/diag/rdbms//trace/adg_dbw0_116630.trc:
ORA-01186: file 81 failed verification tests
ORA-01157: cannot identify/lock data file 81 - see DBWR trace file
ORA-01111: name for data file 81 is unknown - rename to correct file
ORA-01110: data file 81: '/u01/app/11.2.0/product/db_1/dbs/UNNAMED00081'
File 81 not verified due to error ORA-01157
MRP0: Background Media Recovery terminated with error 1111
Errors in file /u01/app/dbBase/diag/rdbms_89050.trc:
ORA-01111: name for data file 81 is unknown - rename to correct file
ORA-01110: data file 81: '/u01/app/11.2.0/product/db_1/dbs/UNNAMED00081'
ORA-01157: cannot identify/lock data file 81 - see DBWR trace file
ORA-01111: name for data file 81 is unknown - rename to correct file
ORA-01110: data file 81: '/u01/app/11.2.0/product/db_1/dbs/UNNAMED00081'
Managed Standby Recovery not using Real Time Apply

查看操作系统中无此文件，

adg[/home/oracle]file /u01/app/11.2.0/product/db_1/dbs/UNNAMED00081
/u01/app/11.2.0/product/db_1/dbs/UNNAMED00081: cannot open `/u01/app/11.2.0/product/db_1/dbs/UNNAMED00081' (No such file or directory)

但是数据库中又有文件记录：

SQL> select name from v$datafile;

NAME
------------------------------------------------------------
+DATADG//datafile/base.374.989050553
/u01/app/11.2.0/product/db_1/dbs/UNNAMED00081

怀疑备库空间不足时，在主库中新建文件导致备库在asm磁盘外建立了文件，应用厂商也有次怀疑进而确认了；

进行修改：

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=manual；

alter database create datafile '/u01/app/11.2.0/product/db_1/dbs/UNNAMED00081' as new;

重新查看后发现此文件已经在asm中重新创建

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO scope=both;

System altered.

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

Database altered.

SQL> select open_mode,PROTECTION_LEVEL,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;

OPEN_MODE            PROTECTION_LEVEL     DATABASE_ROLE    SWITCHOVER_STATUS
-------------------- -------------------- ---------------- --------------------
READ ONLY WITH APPLY MAXIMUM PERFORMANCE  PHYSICAL STANDBY NOT ALLOWED

此次由于备库磁盘空间不足的导致主备同步异常处理完毕。参考mos文章 739618.1

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=507873078661649&id=739618.1&_afrWindowMode=0&_adf.ctrl-state=gbvq07n86_4