oracle 11G新搭建了一套DG，今天将DATAGUARD 的监控增加上去，结果报lstandby_lag 延时严重，

select open_mode, database_role from v$database;

OPEN_MODE DATABASE_ROLE
-------------------- ----------------
READ ONLY PHYSICAL STANDBY
recover managed standby database using current logfile disconnect from session;

查看alert日志：

ALTER DATABASE RECOVER managed standby database using current logfile disconnect from session
Attempt to start background Managed Standby Recovery process (sod)
Sun Jul 17 01:00:43 2016
MRP0 started with pid=32, OS id=131113
MRP0: Background Managed Standby Recovery process started (sod)
started logmerger process
Sun Jul 17 01:00:48 2016
Managed Standby Recovery starting Real Time Apply
Sun Jul 17 01:00:48 2016
********
ORA-01186: file 9 failed verification tests
ORA-01157: cannot identify/lock data file 9 - see DBWR trace file
ORA-01111: name for data file 9 is unknown - rename to correct file
ORA-01110: data file 9: '/db1/oracle/11.2.0/dbs/UNNAMED00009'
File 9 not verified due to error ORA-01157
MRP0: Background Media Recovery terminated with error 1111
*******
ORA-01111: name for data file 9 is unknown - rename to correct file
ORA-01110: data file 9: '/db1/oracle/11.2.0/dbs/UNNAMED00009'
ORA-01157: cannot identify/lock data file 9 - see DBWR trace file
ORA-01111: name for data file 9 is unknown - rename to correct file
ORA-01110: data file 9: '/db1/oracle/11.2.0/dbs/UNNAMED00009'
Managed Standby Recovery not using Real Time Apply
Completed: ALTER DATABASE RECOVER managed standby database using current logfile disconnect from session
Recovery Slave PR00 previously exited with exception 1111
MRP0: Background Media Recovery process shutdown (sod)

之前的经验是一般是主从的数据文件的目录不一致，db_file_name_convert 和 log_file_name_convert 没有设置导致的，查看主库的该参数，已经设置正确，且主库和从库的目录结构是一致的，
后来查看从库的 standby_file_management 参数，竟然是这各参数没有设置成auto

sql>show parameter standby
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest string ?/dbs/arch
standby_file_management string MANUAL
sql>alter system set standby_file_management=auto;

问题解决，重建创建数据文件到正确的目录

alter system set standby_file_management=manual;
alter database create datafile '/db1/oracle/11.2.0/dbs/UNNAMED00009' as '/db1/oracle/data/xxx_01.dbf';
alter system set standby_file_management=auto;
recover managed standby database using current logfile disconnect from session;

ITPub博客

ORA-01111,ORA-01110,ORA-01157,ORA-01111,ORA-01110

最新文章