redo logs not applying [message #329486] |
Wed, 25 June 2008 09:59  |
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   |
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   |
 |
BlackSwan
Messages: 26766 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   |
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 #329496 is a reply to message #329495] |
Wed, 25 June 2008 10:35   |
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 #329499 is a reply to message #329497] |
Wed, 25 June 2008 10:46   |
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 #329509 is a reply to message #329504] |
Wed, 25 June 2008 11:06   |
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 #329803 is a reply to message #329800] |
Thu, 26 June 2008 10:40   |
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   |
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   |
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.
|
|
|
|
|