Oracle Database Discussions

Hello everybody,

I am having a logical standby database and it seems the SQL apply is hanging.

Following are my query results as well as alertlog outputs.

On Standby :_

APPLIED SEQ# - 1905
NEWEST SEQ # - 9162

V$LOGSTDBY_STATE - STATE IDLE

AlertLog

LOGSTDBY status: ORA-16128: User initiated stop apply successfully completed
Tue Jun 23 18:15:44 2009
Completed: ALTER DATABASE STOP LOGICAL STANDBY APPLY
Tue Jun 23 18:16:02 2009
ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE
Tue Jun 23 18:16:03 2009
ALTER DATABASE START LOGICAL STANDBY APPLY (oracle)
Tue Jun 23 18:16:03 2009
with optional part
IMMEDIATE
Attempt to start background Logical Standby process
LSP0 started with pid=19, OS id=10421
LOGSTDBY status: ORA-16111: log mining and apply setting up
Tue Jun 23 18:16:03 2009
LOGMINER: Parameters summary for session# = 1
LOGMINER: Number of processes = 3, Transaction Chunk Size = 201
LOGMINER: Memory Size = 30M, Checkpoint interval = 150M
LOGMINER: session# = 1, preparer process P002 started with pid=22 OS id=2294
LOGMINER: session# = 1, builder process P001 started with pid=21 OS id=2292
LOGMINER: session# = 1, reader process P000 started with pid=20 OS id=2290
Tue Jun 23 18:16:03 2009
LOGMINER: Begin mining logfile: /u01/app/oracle/archive/oracle/ARCH_ORACL_9105_1_663312703.arc
Tue Jun 23 18:16:03 2009
LOGMINER: Turning ON Log Auto Delete
Tue Jun 23 18:16:03 2009
LOGMINER: End mining logfile: /u01/app/oracle/archive/oracle/ARCH_ORACL_9105_1_663312703.arc
LOGSTDBY Analyzer process P003 started with pid=23 OS id=2297
LOGSTDBY Apply process P004 started with pid=24 OS id=2299
LOGSTDBY Apply process P006 started with pid=26 OS id=2303
LOGSTDBY Apply process P008 started with pid=28 OS id=2307
Tue Jun 23 18:16:03 2009
LOGSTDBY Parameter: DISABLE_APPLY_DELAY =
LOGSTDBY Parameter: REAL_TIME =
Completed: ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE
LOGSTDBY Apply process P005 started with pid=25 OS id=2301
LOGSTDBY Apply process P007 started with pid=27 OS id=2305
Tue Jun 23 18:24:02 2009
RFS LogMiner: Client enabled and ready for notification
Tue Jun 23 18:24:02 2009
RFS LogMiner: Registered logfile [u01/app/oracle/archive/oracle/ARCH_ORACL_9142_1_663312703.arc] to LogMiner session id [1]
Tue Jun 23 18:24:03 2009


As per above alert log file archives are transferring successfully from primary to logical standby but they aren't applying to Standby database.

I did following

SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

but still V$LOGSTDBY_STATE - STATE shows IDLE

What could be the reason for this SQL Apply hanging..

any help suggestions would be appreciated.

Thanks