I am working on 11GR2 and it seemed , my physical standby seemed out of sync , when I query the related views , then I discovered , there is lag. I also tried to stop and start the MRP process and found following lines from alert.log. the network connectivity seemed fine and there is no issue with it. also, in alert.log , database says , unable to find the status for the redologs.
I also did the ls with the redolog file , but it's not there. also ,one other physical standby , when I tried to locate the redolog physically , it also says the same and that standby is working fine with no lag.
please offer your expert opinion on this.
SQL> select max(sequence#) from v$archived_log where applied='YES' and dest_id=2 ;
MAX(SEQUENCE#)
--------------
38486
SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
2 FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
3 4 (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
5 WHERE
6 ARCH.THREAD# = APPL.THREAD#
7 ;
Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1 40117 38486 1631
Using STANDBY_ARCHIVE_DEST parameter default value as /u099/archivelog/mlprddb
Wed Oct 29 15:29:08 2014
alter database recover managed standby database disconnect from session
ORA-1153 signalled during: alter database recover managed standby database disconnect from session...
Wed Oct 29 15:30:43 2014
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
Wed Oct 29 15:30:44 2014
MRP0: Background Media Recovery cancelled with status 16037
Errors in file /u000/app/oracle/diag/rdbms/mlmelb/mlprddb/trace/mlprddb_pr00_3745.trc:
ORA-16037: user requested cancel of managed recovery operation
Recovery interrupted!
Wed Oct 29 15:30:45 2014
MRP0: Background Media Recovery process shutdown (mlprddb)
Managed Standby Recovery Canceled (mlprddb)
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
Wed Oct 29 15:30:55 2014
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION
Attempt to start background Managed Standby Recovery process (mlprddb)
Wed Oct 29 15:30:55 2014
MRP0 started with pid=23, OS id=8357
MRP0: Background Managed Standby Recovery process started (mlprddb)
started logmerger process
Wed Oct 29 15:31:00 2014
Managed Standby Recovery not using Real Time Apply
Parallel Media Recovery started with 16 slaves
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Errors in file /u000/app/oracle/diag/rdbms/mlmelb/mlprddb/trace/mlprddb_mrp0_8357.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u302/oradata/mlprddb/redo/mlprddb_redo_1b.dbf'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 1 thread 1: '/u301/oradata/mlprddb/redo/mlprddb_redo_1a.dbf'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3
Clearing online redo logfile 1 /u301/oradata/mlprddb/redo/mlprddb_redo_1a.dbf
Clearing online log 1 of thread 1 sequence number 40116
Errors in file /u000/app/oracle/diag/rdbms/mlmelb/mlprddb/trace/mlprddb_mrp0_8357.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u302/oradata/mlprddb/redo/mlprddb_redo_1b.dbf'