Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Another question about dataguard

Another question about dataguard

From: Kamus <kamusis_at_gmail.com>
Date: Fri, 07 Jan 2005 12:32:29 +0800
Message-Id: <20050107120723.6029.KAMUSIS@gmail.com> 





When the primary site looses connect to the standby site, how can I
estimate that damn 30sec's LGWR stall time?



I did a test as below.



    
  
  1.   make a procedure sp_insert at primary site:
create or replace procedure sp_insert is
 i number;
begin
  for i in 1..600000
  loop
      insert into t_time(timestamp) values(sysdate);
      commit;
      --dbms_lock.sleep(seconds => 1);
  end loop; 
end sp_insert;






2.my initial parameters at primary site are:
SQL> show parameter log_archive


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest                     string
log_archive_dest_1                   string      LOCATION=/oradata/ctsdb/archiv
                                                 e
log_archive_dest_10                  string
log_archive_dest_2                   string      SERVICE=CTSDB.STANDBY LGWR ASY
                                                 NC NET_TIMEOUT=10
log_archive_dest_3                   string
log_archive_dest_4                   string
log_archive_dest_5                   string
log_archive_dest_6                   string
log_archive_dest_7                   string

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_8                   string
log_archive_dest_9                   string
log_archive_dest_state_1             string      enable
log_archive_dest_state_10            string      enable
log_archive_dest_state_2             string      enable
log_archive_dest_state_3             string      enable
log_archive_dest_state_4             string      enable
log_archive_dest_state_5             string      enable
log_archive_dest_state_6             string      enable
log_archive_dest_state_7             string      enable
log_archive_dest_state_8             string      enable

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_9             string      enable
log_archive_duplex_dest              string
log_archive_format                   string      %t_%s.dbf
log_archive_max_processes            integer     2
log_archive_min_succeed_dest         integer     1
log_archive_start                    boolean     TRUE
log_archive_trace                    integer     0





3. I ran sp_insert at primary site:


SQL> exec sp_insert();



4. When the network is ok, alertlog seems very good:





Thu Jan  6 14:00:32 2005


LGWR: Completed archiving  log 3 thread 1 sequence 340
Creating archive destination LOG_ARCHIVE_DEST_2: 'CTSDB.STANDBY'
LGWR: Beginning to archive log 2 thread 1 sequence 341
Thread 1 advanced to log sequence 341


  Current log# 2 seq# 341 mem# 0: /oradata/ctsdb/redo02.log
Thu Jan  6 14:00:33 2005

ARC1: Evaluating archive   log 3 thread 1 sequence 340
ARC1: Archive destination LOG_ARCHIVE_DEST_2: Previously completed
ARC1: Beginning to archive log 3 thread 1 sequence 340


Creating archive destination LOG_ARCHIVE_DEST_1: '/oradata/ctsdb/archive/1_340.dbf'
ARC1: Completed archiving  log 3 thread 1 sequence 340





5. After I unlugged the standby site's network and kept checking the
alertlog at primary site:





Thu Jan  6 14:17:10 2005


Creating archive destination LOG_ARCHIVE_DEST_2: 'CTSDB.STANDBY'
LGWR: Error 2 creating archivelog file 'CTSDB.STANDBY'
Thu Jan  6 14:17:42 2005


Errors in file /export/home/oracle/app/oracle/admin/ctsdb/bdump/ctsdb_lgwr_416.trc:
ORA-00002: Message 2 not found;  product=RDBMS; facility=ORA
LGWR: Completed archiving  log 2 thread 1 sequence 344
Thread 1 advanced to log sequence 344


  Current log# 2 seq# 344 mem# 0: /oradata/ctsdb/redo02.log
Thu Jan  6 14:17:42 2005


ARC1: Evaluating archive   log 3 thread 1 sequence 343
ARC1: Beginning to archive log 3 thread 1 sequence 343
Creating archive destination LOG_ARCHIVE_DEST_1: '/oradata/ctsdb/archive/1_343.dbf'
Thu Jan  6 14:17:55 2005


kccrsz: expanded controlfile section 11 from 433 to 447 records
  requested to grow by 4 record(s); added 1 block(s) of records
ARC1: Completed archiving  log 3 thread 1 sequence 343



Thu Jan  6 14:19:49 2005


Creating archive destination LOG_ARCHIVE_DEST_2: 'CTSDB.STANDBY'
LGWR: Error 2 creating archivelog file 'CTSDB.STANDBY'
Thu Jan  6 14:20:21 2005


Errors in file /export/home/oracle/app/oracle/admin/ctsdb/bdump/ctsdb_lgwr_416.trc:
ORA-00002: Message 2 not found;  product=RDBMS; facility=ORA
LGWR: Completed archiving  log 1 thread 1 sequence 345
Thread 1 advanced to log sequence 345


  Current log# 1 seq# 345 mem# 0: /oradata/ctsdb/redo01.log
Thu Jan  6 14:20:21 2005


ARC1: Evaluating archive   log 2 thread 1 sequence 344
ARC1: Beginning to archive log 2 thread 1 sequence 344
Creating archive destination LOG_ARCHIVE_DEST_1: '/oradata/ctsdb/archive/1_344.dbf'
ARC1: Completed archiving  log 2 thread 1 sequence 344



Thu Jan  6 14:21:55 2005


Creating archive destination LOG_ARCHIVE_DEST_2: 'CTSDB.STANDBY'
LGWR: Error 2 creating archivelog file 'CTSDB.STANDBY'
Thu Jan  6 14:22:26 2005


Errors in file /export/home/oracle/app/oracle/admin/ctsdb/bdump/ctsdb_lgwr_416.trc:
ORA-00002: Message 2 not found;  product=RDBMS; facility=ORA
LGWR: Completed archiving  log 3 thread 1 sequence 346
Thread 1 advanced to log sequence 346


  Current log# 3 seq# 346 mem# 0: /oradata/ctsdb/redo03.log
Thu Jan  6 14:22:27 2005


ARC0: Evaluating archive   log 1 thread 1 sequence 345
ARC0: Beginning to archive log 1 thread 1 sequence 345
Creating archive destination LOG_ARCHIVE_DEST_1: '/oradata/ctsdb/archive/1_345.dbf'
ARC0: Completed archiving  log 1 thread 1 sequence 345





at this time(14:17:10-14:17:41, 14:19:49-14:20:21, 14:21:55-14:22:26, more
than 30 seconds) all the dml at primary site is stalled, even the select
operation on t_time table.



After this test, I checked t_time table's data:
SQL> select count(*) from t_time 


 2   where timestamp=to_date('2005-1-6 14:17:09','yyyy-mm-dd
hh24:mi:ss');



  COUNT(*)





      1842



SQL> select count(*) from t_time 


 2   where timestamp>=to_date('2005-1-6 14:17:10','yyyy-mm-dd hh24:mi:ss') 
 3   and timestamp<=to_date('2005-1-6 14:17:41','yyyy-mm-dd
hh24:mi:ss');



  COUNT(*)





         0



SQL> select count(*) from t_time 


 2   where timestamp=to_date('2005-1-6 14:17:42','yyyy-mm-dd
hh24:mi:ss');



  COUNT(*)





      680



so, clearly, at 14:17:10 - 14:17:41, no data could insert into primary
site. 



-- 
Kamus <kamusis_at_gmail.com>

那么多1G的邮箱，我能用来干什么:-)
A Oracle8i & 9i Certified DBA from China

--
http://www.freelists.org/webpage/oracle-l


Received on Thu Jan 06 2005 - 22:31:37 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US