ORA-16112 - Logical Standby database log apply service hanging....

[Betty]

Hi,

Is there anyone who experienced such situation --
I am trying to build a Logical Standby Database(9i), everything is fine, standby database is up, log transport service is working well, standby DB can receive the logs transported from primary database.
Then I enable Log Aplly service:
SQL>ALTER DATABASE START LOGICAL STANDBY APPLY INITIAL;
After this cmd, the LSP have been up and running for 22 hours, it seems endless, I kept on checking the status, execept get "ORA-16111: log mining and apply setting up" at the beginning, always see below message:
"
SQL> SELECT TYPE,STATUS FROM V$LOGSTDBY;

TYPE STATUS
--------------------------------------------------------------------------------
COORDINATOR
ORA-16112: log mining and apply stopping

SQL> SELECT NAME, VALUE FROM V$LOGSTDBY_STATS WHERE NAME = 'coordinator state';

NAME
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
coordinator state
INITIALIZING
"
I do have a large DB(about 77G), is it normal to spend so long time to initialze LSP?
And do I need to do anything if see ORA-16112: apply stopping?

I thought LSP might stop, so try to re-enable LSP, got below error message:
"SQL> ALTER DATABASE START LOGICAL STANDBY APPLY;
ALTER DATABASE START LOGICAL STANDBY APPLY
*
ERROR at line 1:
ORA-16105: Logical Standby is already running in background

Thanks a lots!

Betty

[davey23uk]

whats in the alert log

[Betty]

"tail -100 alter_dev.log" -- get below message :

ALTER DATABASE REGISTER LOGICAL LOGFILE '/devfin1/standbyArc/arch_1_30075.arc'
Sun Mar 20 19:42:47 2005
There are 1 logfiles specified.
ALTER DATABASE REGISTER LOGICAL LOGFILE
Completed: ALTER DATABASE REGISTER LOGICAL LOGFILE '/devfin1/
Sun Mar 20 19:43:06 2005
ALTER DATABASE REGISTER LOGICAL LOGFILE '/devfin1/standbyArc/arch_1_30076.arc'
Sun Mar 20 19:43:06 2005
There are 1 logfiles specified.
ALTER DATABASE REGISTER LOGICAL LOGFILE
Completed: ALTER DATABASE REGISTER LOGICAL LOGFILE '/devfin1/
Sun Mar 20 19:46:57 2005
ALTER DATABASE START LOGICAL STANDBY APPLY INITIAL 3180452738
Sun Mar 20 19:46:57 2005
ALTER DATABASE START LOGICAL STANDBY APPLY
with optional part
INITIAL 3180452738
LSP0 started with pid=15
Sun Mar 20 19:46:58 2005
Attempt to start background Logical Standby process
Completed: ALTER DATABASE START LOGICAL STANDBY APPLY INITIAL
Sun Mar 20 19:46:58 2005
LOGSTDBY event: ORA-16111: log mining and apply setting up

[davey23uk]

ok when I did this i got caught on the number of processes on the standby database - was exceeding the limit. Can't remmeber if this was recorded in the alert log though

[Betty]

I issued "recover database until change 3180452737 using backup controlfile" ;
Then "alter database open resetlogs";
Then register log arch_1_30075.arc;
Then "ALTER DATABASE START LOGICAL STANDBY APPLY INITIAL 3180452738".

I don't think the LSP hanging is due to I specified the SCN when I apply INITIAL.
The end SCN=3180452737 in arch_1_30074.arc, the start SCN=3180452738 in arch_1_30075.arc.

[Axr2]

I'm not really up on logical standby's. But here's a useful link http://download-west.oracle.com/docs...ly.htm#1017636

Can you upload a spooled output of the below?

COLUMN STATUS FORMAT A50
COLUMN TYPE FORMAT A12
SELECT TYPE, HIGH_SCN, STATUS FROM V$LOGSTDBY;

COLUMN NAME FORMAT A35
COLUMN VALUE FORMAT A35
SELECT NAME, VALUE FROM V$LOGSTDBY_STATS
WHERE NAME LIKE 'coordinator%' or NAME LIKE 'transactions%';

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YY HH24:MI:SS';
COLUMN STATUS FORMAT A60
SELECT EVENT_TIME, STATUS, EVENT FROM DBA_LOGSTDBY_EVENTS
ORDER BY EVENT_TIME, COMMIT_SCN;

SELECT FILE_NAME, SEQUENCE#, FIRST_CHANGE#, NEXT_CHANGE#,
TIMESTAMP, DICT_BEGIN, DICT_END, THREAD# FROM DBA_LOGSTDBY_LOG
ORDER BY SEQUENCE#;

SELECT APPLIED_SCN, NEWEST_SCN FROM D BA_LOGSTDBY_PROGRESS;

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YY HH24:MI:SS';

SELECT L.SEQUENCE#, L.FIRST_TIME,
(CASE WHEN L.NEXT_CHANGE# < P.READ_SCN THEN 'YES'
WHEN L.FIRST_CHANGE# < P.APPLIED_SCN THEN 'CURRENT'
ELSE 'NO' END) APPLIED
FROM DBA_LOGSTDBY_LOG L, DBA_LOGSTDBY_PROGRESS P
ORDER BY SEQUENCE#;

[Betty]

SQL> SELECT TYPE, HIGH_SCN, STATUS FROM V$LOGSTDBY;

TYPE HIGH_SCN STATUS
------------ ---------- --------------------------------------------------
COORDINATOR ORA-16112: log mining and apply stopping

SQL> SELECT NAME, VALUE FROM V$LOGSTDBY_STATS WHERE NAME LIKE 'coordinator%' or NAME LIKE 'transactions%';

NAME VALUE
----------------------------------- -----------------------------------
coordinator state INITIALIZING
transactions ready 0
transactions applied 0
coordinator uptime 2546

SQL> SELECT EVENT_TIME, STATUS, EVENT FROM DBA_LOGSTDBY_EVENTS ORDER BY EVENT_TIME, COMMIT_SCN;

EVENT_TIME STATUS
------------------ ------------------------------------------------------------
EVENT
--------------------------------------------------------------------------------
20-MAR-05 19:46:58 ORA-16111: log mining and apply setting up

SQL> SELECT FILE_NAME, SEQUENCE#, FIRST_CHANGE#, NEXT_CHANGE#, TIMESTAMP, DICT_BEGIN, DICT_END, THREAD# FROM DBA_LOGSTDBY_LOG ORDER BY SEQUENCE#;

FILE_NAME
--------------------------------------------------------------------------------
SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# TIMESTAMP DIC DIC THREAD#
---------- ------------- ------------ ------------------ --- --- ----------
/devfin1/standbyArc/arch_1_30075.arc
30075 3180452738 3180453321 20-MAR-05 19:42:48 NO NO 1

/devfin1/standbyArc/arch_1_30076.arc
30076 3180453321 3180453354 20-MAR-05 19:43:06 NO NO 1

/devfin1/standbyArc/arch_1_30077.arc
30077 3180453354 3180460541 20-MAR-05 21:23:38 NO NO 1
......too many logfiles...OMITTED.......
..............................OMMITED...
/devfin1/standbyArc/arch_1_30218.arc
30218 3181709641 3181720027 22-MAR-05 13:56:47 NO NO 1

/devfin1/standbyArc/arch_1_30219.arc
30219 3181720027 3181726092 22-MAR-05 14:01:57 NO NO 1


145 rows selected.

SQL> SELECT L.SEQUENCE#, L.FIRST_TIME, (CASE WHEN L.NEXT_CHANGE# < P.READ_SCN THEN 'YES' WHEN L.FIRST_CHANGE# < P.APPLIED_SCN THEN 'CURRENT' ELSE 'NO' END) APPLIED FROM DBA_LOGSTDBY_LOG L, DBA_LOGSTDBY_PROGRESS P ORDER BY SEQUENCE#;

SEQUENCE# FIRST_TIME APPLIED
---------- ------------------ -------
30075 20-MAR-05 18:55:06 NO
30076 20-MAR-05 18:58:06 NO
30077 20-MAR-05 18:58:10 NO
.......from 30078 till 30217...OMITED...HERE...
30218 22-MAR-05 13:46:58 NO
30219 22-MAR-05 13:56:41 NO
145 rows selected.

[Axr2]

Are there any lsp trace files? Nothing new in your alert log yet, I presume..

Btw, what version and platform? If it's 9.2.0.1, it might be buggy (based on what people have to say on metalink). Your best bet is to open a tar.

[Betty]

No any new message on alert file since "alter database start logical standby apply initial change SCN"; (2 days gone)
No LSP trace file;

The platform is Suse Linux 9.0
DB: 9.2.0.4

[Betty]

Got answer from metalink --
There is an unpublished Bug 2185415, which is fixed in 9.2.0.2

But my DB is 9.2.0.4, i am going to open a Tar to ask....