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

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 :confused:

whats in the alert log

"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

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

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.

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#;

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.

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.

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

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....

It'll be great if you could post the eventual outcome of the TAR.

I got solution from Oracle Tar.

1. I did cold backup of primary DB on March 8, executed the logMiner, the log sequence#29205 includes the special Dict Begin/End.

SQL> SELECT NAME FROM V$ARCHIVED_LOG
2 WHERE (SEQUENCE#=(SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG
3 WHERE DICTIONARY_BEGIN = 'YES' AND STANDBY_DEST= 'NO'));

NAME
--------------------------------------------------------------------------------
arch_1_29205.arc

2. I stopped to do something else.

3. on March 17, did another cold backup copy of DB; then used this cold backup copies(datafiles) as base, and used March 20's backup controlfile.
So I did "recover database using backup controlfile until change SCN", I recovered the Logical DB until March 20.
The end SCN in log 30074(March 20)

4. Then I started LSP " alter database start logical standby apply initial SCN"
the start SCN in log 30075(March 20).

I thought what I did very logically and reasonablly. But Oracle Tar told me --
Go Back to register more logs, until it includes "DICTIONARY_BEGIN = 'YES'".

So I went back to register all logs happened on March 8(#29205), it works.

I asked WHY?
They answered:
"The recovery process will start to check all the logs since the log miner dictionary had been built. Maybe it won't need to apply any of those logs but it will be the starting point.
"

Thanks a lot for everyone!

Betty

Aah..thanks for the update.