Home » Server Options » Data Guard » redo logs not applying (Oracle DB 10.2 RHEL4)
redo logs not applying [message #329486] Wed, 25 June 2008 09:59 Go to next message
TLegend33
Messages: 203
Registered: March 2005
Senior Member
We have been having issues with out standby db server. Since those issues have been resolved, I brought our standby db up to sync with our production db:

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup nomount pfile='<pfile>'
ORACLE instance started.

Total System Global Area 7197425664 bytes
Fixed Size                  2082208 bytes
Variable Size            1191184992 bytes
Database Buffers         5989466112 bytes
Redo Buffers               14692352 bytes
SQL> alter database mount standby database;

Database altered.

SQL> alter database recover managed standby database disconnect;

Database altered.


However, the archived redo logs are not being transported from the primary db to the secondary db.

SQL> SELECT DESTINATION, STATUS, ARCHIVED_THREAD#, ARCHIVED_SEQ#
FROM V$ARCHIVE_DEST_STATUS
WHERE STATUS <> 'DEFERRED' AND STATUS <> 'INACTIVE'; 

DESTINATION                    STATUS    ARCHIVED_THREAD# ARCHIVED_SEQ#
------------------------------ --------- ---------------- -------------
/d10/oradata/PROD/ARCH      VALID                    1          9584
PROD_S                      VALID                    1          9565

SQL> SELECT LOCAL.THREAD#, LOCAL.SEQUENCE# FROM
(SELECT THREAD#, SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=1)
LOCAL WHERE
LOCAL.SEQUENCE# NOT IN
(SELECT SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND
THREAD# = LOCAL.THREAD#);  

   THREAD#  SEQUENCE#
---------- ----------
         1       9567
         1       9568
         1       9569
         1       9570
         1       9571
         1       9572
         1       9573
         1       9574
         1       9575
         1       9576
         1       9577

   THREAD#  SEQUENCE#
---------- ----------
         1       9578
         1       9579
         1       9580
         1       9581
         1       9582
         1       9583

17 rows selected.


With the standby db down I received the below message in my alert.log:
Error 1034 received logging on to the standby
Tue Jun 24 09:14:41 2008


I've also witnessed this error when the listener is down on the standby server:
Error 12541 received logging on to the standby
Check whether the listener is up and running.
Sun Jun 22 18:52:25 2008


What concerns me is that the standby db and the listener are both running, but I'm getting no error messages in the primary db alert.log. What else can I check?
Thanks.
Re: redo logs not applying [message #329488 is a reply to message #329486] Wed, 25 June 2008 10:03 Go to previous messageGo to next message
TLegend33
Messages: 203
Registered: March 2005
Senior Member
Here is some further information:
SQL> select destination, status, recovery_mode from v$archive_dest_status
  2  where status <> 'DEFERRED' and status <> 'INACTIVE';

DESTINATION                    STATUS    RECOVERY_MODE
------------------------------ --------- -----------------------
/d10/oradata/PROD/ARCH      VALID     IDLE
PROD_S                      VALID     UNKNOWN


SQL> select process, client_process, sequence#, status from V$managed_standby;

PROCESS   CLIENT_P  SEQUENCE# STATUS
--------- -------- ---------- ------------
ARCH      ARCH           9566 CLOSING
ARCH      ARCH           9584 CLOSING


It appears I need to modify my standby destination to managed.

[Updated on: Wed, 25 June 2008 10:09]

Report message to a moderator

Re: redo logs not applying [message #329489 is a reply to message #329486] Wed, 25 June 2008 10:09 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
On the standby, do all the archived redo logfiles exist?
I suspect not.
If not, manually copy them across & "register" them with the standby DB.

SQL> select process, client_process, sequence#, status from V$managed_standby;

PROCESS   CLIENT_P  SEQUENCE# STATUS
--------- -------- ---------- ------------
ARCH      ARCH          14194 CLOSING
ARCH      ARCH              0 CONNECTED
MRP0      N/A           14195 APPLYING_LOG
RFS       UNKNOWN           0 IDLE
RFS       LGWR          14195 IDLE


what does
SQL> archive log list
show on both Primary & standby?

[Updated on: Wed, 25 June 2008 10:26] by Moderator

Report message to a moderator

Re: redo logs not applying [message #329493 is a reply to message #329489] Wed, 25 June 2008 10:30 Go to previous messageGo to next message
TLegend33
Messages: 203
Registered: March 2005
Senior Member
You are correct, not all of the archived redo logs exist on the standby db. I could manually transfer them from primary to secondary, however I don't believe that would correct the issue of the redo logs not transferring over per the managed standby settings currently in effect. I would like to correct that before I begin any manual intervention.
Thanks.



Sorry, I just saw your response:
primary:
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /d10/oradata/PROD/ARCH
Oldest online log sequence     9578
Next log sequence to archive   9585
Current log sequence           9585

secondary:
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /d10/oradata/PROD/ARCH
Oldest online log sequence     9544
Next log sequence to archive   0
Current log sequence           9567

[Updated on: Wed, 25 June 2008 10:32]

Report message to a moderator

Re: redo logs not applying [message #329495 is a reply to message #329486] Wed, 25 June 2008 10:32 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
It is your chioce; then You're On Your Own (YOYO).
Re: redo logs not applying [message #329496 is a reply to message #329495] Wed, 25 June 2008 10:35 Go to previous messageGo to next message
TLegend33
Messages: 203
Registered: March 2005
Senior Member
Sorry, I just saw your response:
primary:
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /d10/oradata/PROD/ARCH
Oldest online log sequence     9578
Next log sequence to archive   9585
Current log sequence           9585


secondary:
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /d10/oradata/PROD/ARCH
Oldest online log sequence     9544
Next log sequence to archive   0
Current log sequence           9567
Re: redo logs not applying [message #329497 is a reply to message #329486] Wed, 25 June 2008 10:41 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Unless you have backed up copies of 9567 - 9578, the standby DB can not be made current.
Re: redo logs not applying [message #329499 is a reply to message #329497] Wed, 25 June 2008 10:46 Go to previous messageGo to next message
TLegend33
Messages: 203
Registered: March 2005
Senior Member
The archived redo logs 9567-9578 currently exist in the archive log destination on the primary db.
The information displayed from v$archive_dest_status interests me because the recovery_mode is displayed as 'unknown'. Is there a way I can modify this on the primary db?
Thanks.
Re: redo logs not applying [message #329504 is a reply to message #329486] Wed, 25 June 2008 10:57 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
from standby

  1  SELECT MAX(R.SEQUENCE#) LAST_SEQ_RECD, MAX(L.SEQUENCE#) LAST_SEQ_SENT
  2* FROM V$ARCHIVED_LOG R, V$LOG L WHERE R.DEST_ID=2 AND L.ARCHIVED='YES'
SQL> /

LAST_SEQ_RECD LAST_SEQ_SENT
------------- -------------
        14194         14196

Re: redo logs not applying [message #329509 is a reply to message #329504] Wed, 25 June 2008 11:06 Go to previous messageGo to next message
TLegend33
Messages: 203
Registered: March 2005
Senior Member
standby:
SQL> SELECT MAX(R.SEQUENCE#) LAST_SEQ_RECD, MAX(L.SEQUENCE#) LAST_SEQ_SENT
FROM V$ARCHIVED_LOG R, V$LOG L WHERE R.DEST_ID=2 AND L.ARCHIVED='YES';  2  

LAST_SEQ_RECD LAST_SEQ_SENT
------------- -------------
         9564          9567
Re: redo logs not applying [message #329510 is a reply to message #329486] Wed, 25 June 2008 11:07 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://download.oracle.com/docs/cd/B10501_01/server.920/a96653/manual_recovery.htm
Re: redo logs not applying [message #329800 is a reply to message #329510] Thu, 26 June 2008 10:36 Go to previous messageGo to next message
gaperumal
Messages: 8
Registered: June 2008
Location: Chennai India
Junior Member
V$Archive_dest must be first view you must looking into that. It will show the error msg.

Please check out this
Re: redo logs not applying [message #329803 is a reply to message #329800] Thu, 26 June 2008 10:40 Go to previous messageGo to next message
TLegend33
Messages: 203
Registered: March 2005
Senior Member
primary db:
SQL> select dest_id, dest_name, error from v$archive_dest;

   DEST_ID DEST_NAME                      ERROR
---------- ------------------------------ -----------------------------------------------------------------
         1 LOG_ARCHIVE_DEST_1
         2 LOG_ARCHIVE_DEST_2
         3 LOG_ARCHIVE_DEST_3
         4 LOG_ARCHIVE_DEST_4
         5 LOG_ARCHIVE_DEST_5
         6 LOG_ARCHIVE_DEST_6
         7 LOG_ARCHIVE_DEST_7
         8 LOG_ARCHIVE_DEST_8
         9 LOG_ARCHIVE_DEST_9
        10 LOG_ARCHIVE_DEST_10


standby db:
SQL> select dest_id, dest_name, error from v$archive_dest;

   DEST_ID DEST_NAME                      ERROR
---------- ------------------------------ -----------------------------------------------------------------
         1 LOG_ARCHIVE_DEST_1
         2 LOG_ARCHIVE_DEST_2
         3 LOG_ARCHIVE_DEST_3
         4 LOG_ARCHIVE_DEST_4
         5 LOG_ARCHIVE_DEST_5
         6 LOG_ARCHIVE_DEST_6
         7 LOG_ARCHIVE_DEST_7
         8 LOG_ARCHIVE_DEST_8
         9 LOG_ARCHIVE_DEST_9
        10 LOG_ARCHIVE_DEST_10
        11 STANDBY_ARCHIVE_DEST
Re: redo logs not applying [message #329834 is a reply to message #329510] Thu, 26 June 2008 12:34 Go to previous messageGo to next message
TLegend33
Messages: 203
Registered: March 2005
Senior Member
Thanks, this document is very helpful.
I'm curious before I begin though:
Suppose I manually copy over the first archived redo log and connect with:
ALTER DATABASE RECOVER AUTOMATIC STANDBY DATABASE;

then apply that log file. If I then either shutdown the standby db or issue:
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

Will the standby db then begin accepting the logs from my primary db automatically, or are there further modifications that I need to make for this to occur?

Thanks for your help!
Re: redo logs not applying [message #331166 is a reply to message #329834] Wed, 02 July 2008 08:11 Go to previous messageGo to next message
TLegend33
Messages: 203
Registered: March 2005
Senior Member
I was having these issues for a few reasons. One, because I had the default log_archive_max_processes=2, rather than log_archive_max_processes=4. Once I upped this parameter on both the primary and standby, my redo apply started right back up. Apparently, my standby db was unavailable for so long, I had to reinitialize the redo transporting on the primary db:
alter system set log_archive_dest_state_2=enable;
I also needed to create the same number of standby redo logs in my standby as the number of redo logs in my primary db.
Thanks for your help.
Re: redo logs not applying [message #331214 is a reply to message #329486] Wed, 02 July 2008 10:44 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Thanks for posting the update.

FWIW - On my production primary server I have log_archive_max_processes=2 & it is working flawlessly.

I suspect that it was some other change that broke your logjam.
Re: redo logs not applying [message #337420 is a reply to message #329486] Wed, 30 July 2008 18:07 Go to previous message
sunil_v_mishra
Messages: 506
Registered: March 2005
Senior Member
some time logs are not copied b'coz we dont have proper sharing of archive destination Wink this happes after applying any o/s patch...

Such thing had occured many time in my production environment Very Happy
Previous Topic: About Data Guard
Next Topic: ORA-01111 UNNAMED file
Goto Forum:
  


Current Time: Sun Dec 11 06:34:14 CST 2016

Total time taken to generate the page: 0.08626 seconds