oracle同步数据adg_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/jgbadg/jgbadg/trace/jgbadg_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/jgbadg/jgbadg/trace/jgbadg_pr00_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/adg/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://blog.csdn.net/hzcyhujw/article/details/83025311