oracle 10g standby 奇怪的SEQUENCE

clory_0

用oracle 10g 测试安装了standby架构。2台机子。
测试后遇到了一些问题，还请高手指点一下，先谢谢了。

1. 在主数据库上查询v$archived_log，出现了相同的SEQUENCE，但是一个是applied，另外一个没有applied。为什么会出现这样的情况？

SQL> select THREAD#,SEQUENCE#,first_time,next_time,applied from v$archived_log;

  ..
  ..
   THREAD#  SEQUENCE# FIRST_TIM NEXT_TIME APP
---------- ---------- --------- --------- ---
         1         34 03-FEB-08 03-FEB-08 YES
         1         34 03-FEB-08 03-FEB-08 NO
         1         35 03-FEB-08 03-FEB-08 YES
         1         35 03-FEB-08 03-FEB-08 NO
         1         36 03-FEB-08 04-FEB-08 YES
         1         36 03-FEB-08 04-FEB-08 NO
         1         37 04-FEB-08 04-FEB-08 YES
         1         37 04-FEB-08 04-FEB-08 NO
         1         38 04-FEB-08 04-FEB-08 YES
         1         38 04-FEB-08 04-FEB-08 NO
         1         39 04-FEB-08 05-FEB-08 YES

   THREAD#  SEQUENCE# FIRST_TIM NEXT_TIME APP
---------- ---------- --------- --------- ---
         1         39 04-FEB-08 05-FEB-08 NO
         1         40 05-FEB-08 05-FEB-08 YES
         1         40 05-FEB-08 05-FEB-08 NO
         1         41 05-FEB-08 05-FEB-08 YES
         1         41 05-FEB-08 05-FEB-08 NO
         1         42 05-FEB-08 06-FEB-08 YES
         1         42 05-FEB-08 06-FEB-08 NO
         1         43 06-FEB-08 06-FEB-08 YES
         1         43 06-FEB-08 06-FEB-08 NO
         1         44 06-FEB-08 06-FEB-08 YES
         1         44 06-FEB-08 06-FEB-08 NO

2. 为什么一点要在主数据库上执行commit，才会将数据applied到从库上。
测试步骤如下：
   1） 在主库上
       create table test1(name varchar2(20));
           insert into test1 values('hello,this is test');
           alter system swtich logfile;
     2） 在从库上切换到open read only 发现没有数据在test1中。
       SQL> select * from test1;
           no rows selected

     3） 如果在主库上执行commit后，再在从库上切换到open read only ，发现数据已经有了，记得以前不用commit，不知道是哪出了问题还请指点一下，谢谢。
        SQL> select * from test1;
           NAME
------------------------------
            hello,this is test

clory_0

master server alter日志如下：
Sat Feb  2 16:27:01 2008
LNS: Standby redo logfile selected for thread 1 sequence 33 for destination LOG_ARCHIVE_DEST_2
Sun Feb  3 01:00:26 2008
Thread 1 advanced to log sequence 34
  Current log# 3 seq# 34 mem# 0: /home/oracle/u01/app/oracle/oradata/orcl/redo03.log
Sun Feb  3 01:00:29 2008
LNS: Standby redo logfile selected for thread 1 sequence 34 for destination LOG_ARCHIVE_DEST_2
Sun Feb  3 10:50:24 2008
Thread 1 advanced to log sequence 35
  Current log# 1 seq# 35 mem# 0: /home/oracle/u01/app/oracle/oradata/orcl/redo01.log
Sun Feb  3 10:50:26 2008
LNS: Standby redo logfile selected for thread 1 sequence 35 for destination LOG_ARCHIVE_DEST_2
Sun Feb  3 18:38:31 2008
Thread 1 advanced to log sequence 36
  Current log# 2 seq# 36 mem# 0: /home/oracle/u01/app/oracle/oradata/orcl/redo02.log
Sun Feb  3 18:38:33 2008
  Current log# 3 seq# 34 mem# 0: /home/oracle/u01/app/oracle/oradata/orcl/redo03.log
Sun Feb  3 01:00:29 2008
LNS: Standby redo logfile selected for thread 1 sequence 34 for destination LOG_ARCHIVE_DEST_2
Sun Feb  3 10:50:24 2008
Thread 1 advanced to log sequence 35
  Current log# 1 seq# 35 mem# 0: /home/oracle/u01/app/oracle/oradata/orcl/redo01.log
Sun Feb  3 10:50:26 2008
LNS: Standby redo logfile selected for thread 1 sequence 35 for destination LOG_ARCHIVE_DEST_2
Sun Feb  3 18:38:31 2008
Thread 1 advanced to log sequence 36
  Current log# 2 seq# 36 mem# 0: /home/oracle/u01/app/oracle/oradata/orcl/redo02.log
Sun Feb  3 18:38:33 2008
LNS: Standby redo logfile selected for thread 1 sequence 36 for destination LOG_ARCHIVE_DEST_2
Mon Feb  4 04:08:40 2008
Thread 1 advanced to log sequence 37
  Current log# 3 seq# 37 mem# 0: /home/oracle/u01/app/oracle/oradata/orcl/redo03.log


standby alter 日志如下：
Managed Standby Recovery starting Real Time Apply
parallel recovery started with 2 processes
Media Recovery Log /u01/app/oracle/arch/orcl1/1_33_645546548.arc
Media Recovery Waiting for thread 1 sequence 34 (in transit)
Sun Feb  3 10:30:46 2008
Recovery of Online Redo Log: Thread 1 Group 5 Seq 34 Reading mem 0
  Mem# 0 errs 0: /home/oracle/u01/app/oracle/oradata/orcl/stdbredo02.log
Sun Feb  3 10:50:25 2008
Primary database is in MAXIMUM PERFORMANCE mode
RFS[2]: Successfully opened standby log 4: '/home/oracle/u01/app/oracle/oradata/orcl/stdbredo01.log'
Sun Feb  3 10:50:27 2008
Media Recovery Waiting for thread 1 sequence 35 (in transit)
Sun Feb  3 10:50:27 2008
Recovery of Online Redo Log: Thread 1 Group 4 Seq 35 Reading mem 0
  Mem# 0 errs 0: /home/oracle/u01/app/oracle/oradata/orcl/stdbredo01.log
Sun Feb  3 18:38:33 2008
Primary database is in MAXIMUM PERFORMANCE mode
RFS[2]: Successfully opened standby log 4: '/home/oracle/u01/app/oracle/oradata/orcl/stdbredo01.log'
Sun Feb  3 18:38:35 2008
Media Recovery Waiting for thread 1 sequence 36 (in transit)
Sun Feb  3 18:38:35 2008
Recovery of Online Redo Log: Thread 1 Group 4 Seq 36 Reading mem 0
  Mem# 0 errs 0: /home/oracle/u01/app/oracle/oradata/orcl/stdbredo01.log
Mon Feb  4 04:08:42 2008
Primary database is in MAXIMUM PERFORMANCE mode
RFS[2]: Successfully opened standby log 4: '/home/oracle/u01/app/oracle/oradata/orcl/stdbredo01.log'
Mon Feb  4 04:08:43 2008
Media Recovery Waiting for thread 1 sequence 37 (in transit)
Mon Feb  4 04:08:43 2008
Recovery of Online Redo Log: Thread 1 Group 4 Seq 37 Reading mem 0
  Mem# 0 errs 0: /home/oracle/u01/app/oracle/oradata/orcl/stdbredo01.log

未见有报错信息。

[ 本帖最后由 clory_0 于 2008-2-13 15:25 编辑 ]

clory_0

up

clory_0

找到了原因,属于正常
select name,thread#,sequence#,first_time,next_time,applied from v$archived_log;
...
...
   THREAD#  SEQUENCE# FIRST_TIM NEXT_TIME APP
---------- ---------- --------- --------- ---
/u01/app/oracle/arch/orcl/1_91_645546548.arc
         1         91 20-FEB-08 20-FEB-08 NO

orcl1
         1         91 20-FEB-08 20-FEB-08 YES

/u01/app/oracle/arch/orcl/1_92_645546548.arc
         1         92 20-FEB-08 20-FEB-08 NO


NAME
--------------------------------------------------------------------------------
   THREAD#  SEQUENCE# FIRST_TIM NEXT_TIME APP
---------- ---------- --------- --------- ---
orcl1
         1         92 20-FEB-08 20-FEB-08 YES

/u01/app/oracle/arch/orcl/1_93_645546548.arc
         1         93 20-FEB-08 20-FEB-08 NO

orcl1
         1         93 20-FEB-08 20-FEB-08 YES


NAME
--------------------------------------------------------------------------------
   THREAD#  SEQUENCE# FIRST_TIM NEXT_TIME APP
---------- ---------- --------- --------- ---
orcl1
         1         94 20-FEB-08 20-FEB-08 YES

/u01/app/oracle/arch/orcl/1_94_645546548.arc
         1         94 20-FEB-08 20-FEB-08 NO

orcl1
         1         95 20-FEB-08 21-FEB-08 YES


NAME
--------------------------------------------------------------------------------
   THREAD#  SEQUENCE# FIRST_TIM NEXT_TIME APP
---------- ---------- --------- --------- ---
/u01/app/oracle/arch/orcl/1_95_645546548.arc
         1         95 20-FEB-08 21-FEB-08 NO

orcl1
         1         96 21-FEB-08 21-FEB-08 YES

/u01/app/oracle/arch/orcl/1_96_645546548.arc
         1         96 21-FEB-08 21-FEB-08 NO