Oracle DBA – A lifelong learning experience

Archived logs stuck ‘in-memory’ when building a standby

Posted by John Hallas on December 3, 2010

We had to build a new copy of a physical standby database (11.1.0.7) and the RMAN duplicate command had completed  successfully so all we needed to do was to let the archived logs catchup. After an hour or so this was the status as seen from v$archived_log


column "First Time" format A40
column applied format A10
column "Next Time" format A40
set linesize 120
set pagesize 1000

select   thread#, sequence#,       applied,
to_char(first_time,'DD-MON-YY:HH24:MI:SS') "First Time",
to_char(next_time,'DD-MON-YY:HH24:MI:SS') "Next Time"
from      v$archived_log
UNION
select   NULL,NULL,' ',NULL,null FROM DUAL
UNION
select   null,null,
db_unique_name,
database_role,
open_mode
from      v$database
/

and the output was (long list but necessary to see both threads). What baffled us why why there were in-memory logs and yet later logs  had been applied.

THREAD#  SEQUENCE# APPLIED    First Time                               Next Time
---------- ---------- ---------- ---------------------------------------- ----------------------------------------

1         52 YES        24-NOV-10:21:24:11                       24-NOV-10:21:29:36
1         53 YES        24-NOV-10:21:29:36                       24-NOV-10:21:35:55
1         54 YES        24-NOV-10:21:35:55                       24-NOV-10:21:42:11
1         55 IN-MEMORY  24-NOV-10:21:42:11                       24-NOV-10:21:48:03
1         56 YES        24-NOV-10:21:48:03                       24-NOV-10:22:09:50
1         57 YES        24-NOV-10:22:09:50                       24-NOV-10:22:18:13
1         58 YES        24-NOV-10:22:18:13                       24-NOV-10:22:25:24
1         59 IN-MEMORY  24-NOV-10:22:25:24                       24-NOV-10:22:39:35
1         60 YES        24-NOV-10:22:39:35                       24-NOV-10:23:26:43
1         61 YES        24-NOV-10:23:26:43                       24-NOV-10:23:40:52
1         62 YES        24-NOV-10:23:40:52                       24-NOV-10:23:49:48
1         63 YES        24-NOV-10:23:49:48                       25-NOV-10:00:01:44
1         64 YES        25-NOV-10:00:01:44                       25-NOV-10:00:42:18
1         65 YES        25-NOV-10:00:42:18                       25-NOV-10:01:06:59
1         66 YES        25-NOV-10:01:06:59                       25-NOV-10:01:45:39
1         67 YES        25-NOV-10:01:45:39                       25-NOV-10:02:22:43
1         68 YES        25-NOV-10:02:22:43                       25-NOV-10:02:54:43
1         69 YES        25-NOV-10:02:54:43                       25-NOV-10:03:19:12
1         70 YES        25-NOV-10:03:19:12                       25-NOV-10:03:49:34
1         71 YES        25-NOV-10:03:49:34                       25-NOV-10:04:42:09
1         72 YES        25-NOV-10:04:42:09                       25-NOV-10:05:24:45
1         73 YES        25-NOV-10:05:24:45                       25-NOV-10:05:57:18
1         74 YES        25-NOV-10:05:57:18                       25-NOV-10:06:51:03
1         75 YES        25-NOV-10:06:51:03                       25-NOV-10:07:14:35
1         76 YES        25-NOV-10:07:14:35                       25-NOV-10:07:56:43
1         77 YES        25-NOV-10:07:56:43                       25-NOV-10:08:19:18
1         78 YES        25-NOV-10:08:19:18                       25-NOV-10:08:40:28
1         79 YES        25-NOV-10:08:40:28                       25-NOV-10:08:53:41
1         80 YES        25-NOV-10:08:53:41                       25-NOV-10:09:04:04
1         81 YES        25-NOV-10:09:04:04                       25-NOV-10:09:15:15
1         82 YES        25-NOV-10:09:15:15                       25-NOV-10:09:29:44
1         83 YES        25-NOV-10:09:29:44                       25-NOV-10:09:39:34
1         84 YES        25-NOV-10:09:39:34                       25-NOV-10:09:56:35
1         85 YES        25-NOV-10:09:56:35                       25-NOV-10:10:02:46
1         86 YES        25-NOV-10:10:02:46                       25-NOV-10:10:09:41
1         87 YES        25-NOV-10:10:09:41                       25-NOV-10:10:09:57
1         88 YES        25-NOV-10:10:09:57                       25-NOV-10:10:11:57
1         89 YES        25-NOV-10:10:11:57                       25-NOV-10:10:13:09
1         90 YES        25-NOV-10:10:13:09                       25-NOV-10:10:21:49
1         91 YES        25-NOV-10:10:21:49                       25-NOV-10:10:24:15
1         92 YES        25-NOV-10:10:24:15                       25-NOV-10:10:25:30
2         58 YES        24-NOV-10:18:50:16                       24-NOV-10:18:57:09
2         59 YES        24-NOV-10:18:57:09                       24-NOV-10:19:08:05
2         60 YES        24-NOV-10:19:08:05                       24-NOV-10:19:17:30
2         61 YES        24-NOV-10:19:17:30                       24-NOV-10:19:24:48
2         62 YES        24-NOV-10:19:24:48                       24-NOV-10:19:37:14
2         63 YES        24-NOV-10:19:37:14                       24-NOV-10:19:53:13
2         64 YES        24-NOV-10:19:53:13                       24-NOV-10:20:06:28
2         65 YES        24-NOV-10:20:06:28                       24-NOV-10:20:18:04
2         66 YES        24-NOV-10:20:18:04                       24-NOV-10:20:37:18
2         67 IN-MEMORY  24-NOV-10:20:37:18                       24-NOV-10:20:58:40
2         68 YES        24-NOV-10:20:58:40                       24-NOV-10:21:13:17
2         69 YES        24-NOV-10:21:13:17                       24-NOV-10:21:19:58
2         70 IN-MEMORY  24-NOV-10:21:19:58                       24-NOV-10:21:22:28
2         71 YES        24-NOV-10:21:22:28                       24-NOV-10:21:24:10
2         72 IN-MEMORY  24-NOV-10:21:24:10                       24-NOV-10:21:25:53
2         73 IN-MEMORY  24-NOV-10:21:25:53                       24-NOV-10:21:27:43
2         74 YES        24-NOV-10:21:27:43                       24-NOV-10:21:29:36
2         75 IN-MEMORY  24-NOV-10:21:29:36                       24-NOV-10:21:31:52
2         76 IN-MEMORY  24-NOV-10:21:31:52                       24-NOV-10:21:33:48
2         77 YES        24-NOV-10:21:33:48                       24-NOV-10:21:35:52
2         78 IN-MEMORY  24-NOV-10:21:35:52                       24-NOV-10:21:37:40
2         79 IN-MEMORY  24-NOV-10:21:37:40                       24-NOV-10:21:39:53
2         80 YES        24-NOV-10:21:39:53                       24-NOV-10:21:42:10
2         81 IN-MEMORY  24-NOV-10:21:42:10                       24-NOV-10:21:43:57
2         82 IN-MEMORY  24-NOV-10:21:43:57                       24-NOV-10:21:46:05
2         83 YES        24-NOV-10:21:46:05                       24-NOV-10:21:48:01
2         84 IN-MEMORY  24-NOV-10:21:48:01                       24-NOV-10:22:02:26
2         85 IN-MEMORY  24-NOV-10:22:02:26                       24-NOV-10:22:13:17
2         86 IN-MEMORY  24-NOV-10:22:13:17                       24-NOV-10:22:15:41
2         87 YES        24-NOV-10:22:15:41                       24-NOV-10:22:18:12
2         88 IN-MEMORY  24-NOV-10:22:18:12                       24-NOV-10:22:20:41
2         89 IN-MEMORY  24-NOV-10:22:20:41                       24-NOV-10:22:22:56
2         90 YES        24-NOV-10:22:22:56                       24-NOV-10:22:25:21
2         91 IN-MEMORY  24-NOV-10:22:25:21                       24-NOV-10:22:27:55
2         92 IN-MEMORY  24-NOV-10:22:27:55                       24-NOV-10:22:30:00
2         93 YES        24-NOV-10:22:30:00                       24-NOV-10:22:39:34
2         94 IN-MEMORY  24-NOV-10:22:39:34                       24-NOV-10:23:05:58
2         95 IN-MEMORY  24-NOV-10:23:05:58                       24-NOV-10:23:36:08
2         96 IN-MEMORY  24-NOV-10:23:36:08                       24-NOV-10:23:38:13
2         97 YES        24-NOV-10:23:38:13                       24-NOV-10:23:40:49
2         98 IN-MEMORY  24-NOV-10:23:40:49                       24-NOV-10:23:43:55
2         99 IN-MEMORY  24-NOV-10:23:43:55                       24-NOV-10:23:48:07
2        100 YES        24-NOV-10:23:48:07                       24-NOV-10:23:49:45
2        101 IN-MEMORY  24-NOV-10:23:49:45                       24-NOV-10:23:53:31
2        102 IN-MEMORY  24-NOV-10:23:53:31                       24-NOV-10:23:58:01
2        103 YES        24-NOV-10:23:58:01                       25-NOV-10:00:01:41
2        104 IN-MEMORY  25-NOV-10:00:01:41                       25-NOV-10:00:11:18
2        105 YES        25-NOV-10:00:11:18                       25-NOV-10:00:42:16
2        106 YES        25-NOV-10:00:42:16                       25-NOV-10:01:09:47
2      107-129 YES
2        130 YES        25-NOV-10:10:13:07                       25-NOV-10:10:24:15
2        131 IN-MEMORY  25-NOV-10:10:24:15                       25-NOV-10:10:25:30

SID   PHYSICAL STANDBY                         MOUNTED

The documentation for the applied column states the following :-

Indicates whether an archived redo log file has been applied to the corresponding physical standby database. The value is always NO for local destinations.

This column is meaningful on a physical standby database for rows where REGISTRAR = RFS:

  • If REGISTRAR = RFS and APPLIED = NO, then the log file has been received but has not yet been applied.
  • If REGISTRAR = RFS and APPLIED = IN-MEMORY, then the log file has been applied in memory, but the datafiles have not yet been updated.
  • If REGISTRAR = RFS and APPLIED = YES, then the log file has been applied and the datafiles have been updated.

So the logs had been applied but the datafiles had not been updated. We waited a long time and more logs came along from the primary system and the applied column showed YES but the older ones did not change. We were convinced that the logs had been applied but how could we be sure. However one of our contractors got involved and said he had seen this elsewhere and if we bounced the instance then all would be well. We of course ignored him for thirty minutes and having found no other explanation did what he suggested. Cancel managed recovery, restart the instance and then restart managed recovery. Then all the logs, from both threads showed YES in the applied colun bar the latest logs.

At UKOUG I managed to get the chance to ask Larry Carpenter about this and he thought he had heard something about it and said it was likely to be a bug. I have searched MoS and Google but no further confirmation on this. Hopefully this may help someone else in the same position and if anybody has any more information please update me.

Advertisements

6 Responses to “Archived logs stuck ‘in-memory’ when building a standby”

  1. Kumar said

    Thank you John for the information. What I like most about your blogs are they are real world situations that can be instantly applied to a situation (should we encounter them).

    Kumar

  2. NiC said

    Did you try issuing a checkpoint?

    • John Hallas said

      Nick, I cannot remember. I think I still have the logs for what we did so I will have a look. I doubt we did that but I also doubt it would have made a difference as we cancelled and started managed recovery with no success and all new redo was applying.

      John

  3. Carl Berner said

    Hi All
    I have this issue with 11.2.0.1. I do not have your issue with 11.2.0.1
    However, the in_memory is only on the last archive log
    STANDBY
    1267 YES 06-MAR-11:17:15:10 07-MAR-11:00:11:24
    1268 YES 07-MAR-11:00:11:24 07-MAR-11:02:46:11
    1269 YES 07-MAR-11:02:46:11 07-MAR-11:03:20:32
    1270 YES 07-MAR-11:03:20:32 07-MAR-11:11:10:29
    1271 YES 07-MAR-11:11:10:29 07-MAR-11:22:08:30
    1272 YES 07-MAR-11:22:08:30 08-MAR-11:00:13:40
    1273 YES 08-MAR-11:00:13:40 08-MAR-11:10:00:16
    1274 YES 08-MAR-11:10:00:16 08-MAR-11:22:03:29
    1275 YES 08-MAR-11:22:03:29 09-MAR-11:00:16:27
    1276 IN-MEMORY 09-MAR-11:00:16:27 09-MAR-11:16:58:23
    Primary
    1273 2011-03-08 00:13:40 2011-03-08 10:00:16 YES
    1273 2011-03-08 00:13:40 2011-03-08 10:00:16 NO
    1274 2011-03-08 10:00:16 2011-03-08 22:03:29 NO
    1274 2011-03-08 10:00:16 2011-03-08 22:03:29 YES
    1275 2011-03-08 22:03:29 2011-03-09 00:16:27 NO
    1275 2011-03-08 22:03:29 2011-03-09 00:16:27 YES
    1276 2011-03-09 00:16:27 2011-03-09 16:58:23 NO
    1276 2011-03-09 00:16:27 2011-03-09 16:58:23 NO
    So it looks to be a bug I had a 10.2.0.4 Standby configured and I never had this issue.
    So maybe 11.2.0.2 is back normal like it should or a feature change.

    I did issue a log switched and the In-memory will go away.

    Thank you Best regards

  4. Khalid R said

    I now have a recently upgraded 11202 and the problem still appears for me. I did bounce the standby database, as a matter of fact I did these steps in sequence as suggested elsewhere on google: On primary log_archive_dest_state_2=defer; on the standby canceled standby, shutdown, startup mount, reapplied “ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

    Here is what happens, the latest log gets applied, but soon as there is a new log, applied status = “IN-MEMORY”. Here is the tail end of it

    75421 01/14/2013 11:02:30 AM 01/14/2013 11:05:32 AM RFS YES

    75422 01/14/2013 11:05:32 AM 01/14/2013 11:09:27 AM RFS YES

    75423 01/14/2013 11:09:27 AM 01/14/2013 11:12:28 AM RFS IN-MEMORY

    Script: I used
    SELECT to_char(a.SEQUENCE#) seq, a.FIRST_TIME,
    a.NEXT_TIME, a.registrar, a.applied
    FROM V$ARCHIVED_LOG a, v$database b
    where
    a.resetlogs_change# = b.resetlogs_change#
    AND a.FIRST_TIME > sysdate-60
    order by 1,2
    /

    BANNER
    ——————————————————————————–
    Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production
    PL/SQL Release 11.2.0.2.0 – Production
    CORE 11.2.0.2.0 Production
    TNS for Solaris: Version 11.2.0.2.0 – Production
    NLSRTL Version 11.2.0.2.0 – Production

  5. SAMER ALJAZZAR said

    most likely there is an active transaction which is not yet commited

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: