Another question about dataguard
- From: Kamus <kamusis@xxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 07 Jan 2005 12:32:29 +0800
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@xxxxxxxxx>
那么多1G的邮箱，我能用来干什么:-)
A Oracle8i & 9i Certified DBA from China
--
//www.freelists.org/webpage/oracle-l
Other related posts:
- » Another question about dataguard
