Chinaunix

标题: oracle 10g standby 奇怪的SEQUENCE [打印本页]

作者: clory_0 时间: 2008-02-13 11:52
标题: oracle 10g standby 奇怪的SEQUENCE
用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 时间: 2008-02-13 15:23
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 时间: 2008-02-18 16:02
up

作者: clory_0 时间: 2008-02-21 15:01

找到了原因,属于正常
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

欢迎光临 Chinaunix (http://bbs.chinaunix.net/)