Home » RDBMS Server » Server Administration » high REDO generation (oracle 10.2.0.4.0 linux 2.6)
| high REDO generation [message #574083] |
Mon, 07 January 2013 00:16  |
kesavansundaram
Messages: 183
Registered: October 2007
Location: MUMBAI
|
Senior Member |
|
|
Team,
Redo is getting generated very high. how to find out the reason ? database kept under 2 node cluster. chcked alert log trace and log writer trace files. pasted the content as below:
--alert log trace from node1 ( node2 also has same type of message ). Archive destination disk group - TXCOM_BACKUP_01 having enough space ( 80gb )
Mon Jan 7 00:49:10 2013
Thread 1 advanced to log sequence 448546 (LGWR switch)
Current log# 1 seq# 448546 mem# 0: +TXCOM_DATA_01/txcom/onlinelog/group_1.274.785770579
Current log# 1 seq# 448546 mem# 1: +TXCOM_DATA_01/txcom/onlinelog/group_1.302.802265189
Mon Jan 7 00:49:10 2013
SUCCESS: diskgroup TXCOM_BACKUP_01 was mounted
SUCCESS: diskgroup TXCOM_BACKUP_01 was dismounted
SUCCESS: diskgroup TXCOM_BACKUP_01 was mounted
SUCCESS: diskgroup TXCOM_BACKUP_01 was dismounted
Mon Jan 7 00:49:17 2013
Thread 1 cannot allocate new log, sequence 448547
Checkpoint not complete
Current log# 1 seq# 448546 mem# 0: +TXCOM_DATA_01/txcom/onlinelog/group_1.274.785770579
Current log# 1 seq# 448546 mem# 1: +TXCOM_DATA_01/txcom/onlinelog/group_1.302.802265189
Mon Jan 7 00:49:20 2013
Thread 1 advanced to log sequence 448547 (LGWR switch)
Current log# 6 seq# 448547 mem# 0: +TXCOM_DATA_01/txcom/onlinelog/group_6.297.802264729
Current log# 6 seq# 448547 mem# 1: +TXCOM_DATA_01/txcom/onlinelog/group_6.307.802265283
Mon Jan 7 00:49:20 2013
SUCCESS: diskgroup TXCOM_BACKUP_01 was mounted
SUCCESS: diskgroup TXCOM_BACKUP_01 was dismounted
SUCCESS: diskgroup TXCOM_BACKUP_01 was mounted
SUCCESS: diskgroup TXCOM_BACKUP_01 was dismounted
SQL> show parameter log_archive_max_processes
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_max_processes integer 2
SQL> show parameter log_archive_start
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_start boolean TRUE
--log file size is 50mb
SQL> select group#,thread#,sequence#,sum(bytes)/1024/1024 "Size(MB)",status from v$log
2 group by group#,thread#,sequence#,status
3 order by thread#;
GROUP# THREAD# SEQUENCE# Size(MB) STATUS
---------- ---------- ---------- ---------- ----------------
1 1 448291 50 ACTIVE
2 1 448294 50 CURRENT
5 1 448290 50 ACTIVE
6 1 448292 50 ACTIVE
7 1 448293 50 ACTIVE
3 2 495823 50 CURRENT
4 2 495822 50 ACTIVE
8 2 495819 50 ACTIVE
9 2 495820 50 ACTIVE
10 2 495821 50 ACTIVE
10 rows selected.
I just need to find out why much archies are generated in a very short period of time in both nodes ?
node1 has generated 233 files and in node2, 76 files. as of now iam purging the archives. but need to suppress redo generation.
MIN(SEQUENCE#) MAX(SEQUENCE#) THREAD# COUNT(SEQUENCE#)
-------------- -------------- ---------- ----------------
448314 448546 1 233
495888 495963 2 76
In the alert log, I am able to see the archive destination disk group ( TXCOM_BACKUP_01 ) is getting DISMOUNTED and again getting MOUNTED during every archive file generation. Please guide me.
Mon Jan 7 00:49:20 2013
SUCCESS: diskgroup TXCOM_BACKUP_01 was mounted
SUCCESS: diskgroup TXCOM_BACKUP_01 was dismounted
SUCCESS: diskgroup TXCOM_BACKUP_01 was mounted
SUCCESS: diskgroup TXCOM_BACKUP_01 was dismounted
archive destination parameter in both nodes are not configured. it should read diskgroup name. ( +TXCOM_BACKUP_01 ) and corresponding size limit. Should i configure this ?
SQL> show parameter db_recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string
db_recovery_file_dest_size big integer 0
---log writer trace file content
*** 2013-01-04 13:06:52.281
*** SERVICE NAME:() 2013-01-04 13:06:52.280
*** SESSION ID:(154.1) 2013-01-04 13:06:52.280
Maximum redo generation record size = 156160 bytes
Maximum redo generation change vector size = 150676 bytes
tkcrrsarc: (WARN) Failed to find ARCH for message (message:0x10)
tkcrrpa: (WARN) Failed initial attempt to send ARCH message (message:0x10)
*** 2013-01-04 13:21:42.903
Warning: log write time 500ms, size 9897KB
*** 2013-01-04 16:15:42.617
tkcrrsarc: (WARN) Failed to find ARCH for message (message:0x1)
tkcrrpa: (WARN) Failed initial attempt to send ARCH message (message:0x1)
*** 2013-01-04 18:17:24.088
Warning: log write time 840ms, size 1452KB
*** 2013-01-04 19:10:55.689
Warning: log write time 830ms, size 1025KB
*** 2013-01-04 21:10:33.025
tkcrrsarc: (WARN) Failed to find ARCH for message (message:0x1)
tkcrrpa: (WARN) Failed initial attempt to send ARCH message (message:0x1)
*** 2013-01-05 06:47:16.727
tkcrrsarc: (WARN) Failed to find ARCH for message (message:0x1)
tkcrrpa: (WARN) Failed initial attempt to send ARCH message (message:0x1)
*** 2013-01-05 07:11:00.104
tkcrrsarc: (WARN) Failed to find ARCH for message (message:0x1)
tkcrrpa: (WARN) Failed initial attempt to send ARCH message (message:0x1)
*** 2013-01-05 07:14:16.029
tkcrrsarc: (WARN) Failed to find ARCH for message (message:0x1)
tkcrrpa: (WARN) Failed initial attempt to send ARCH message (message:0x1)
*** 2013-01-05 07:20:55.373
Warning: log write time 580ms, size 2009KB
*** 2013-01-05 07:21:01.087
Warning: log write time 600ms, size 3876KB
*** 2013-01-05 08:17:16.914
Warning: log write time 960ms, size 819KB
*** 2013-01-05 08:17:17.859
Warning: log write time 950ms, size 1523KB
*** 2013-01-05 08:17:18.486
Warning: log write time 620ms, size 5343KB
*** 2013-01-05 08:17:23.865
Warning: log write time 510ms, size 11498KB
*** 2013-01-05 08:17:28.175
Warning: log write time 600ms, size 2306KB
*** 2013-01-05 08:17:31.521
Warning: log write time 620ms, size 4745KB
*** 2013-01-05 08:17:37.175
Warning: log write time 500ms, size 3877KB
*** 2013-01-05 08:17:45.774
Warning: log write time 540ms, size 5147KB
*** 2013-01-05 08:17:46.867
Warning: log write time 500ms, size 2609KB
*** 2013-01-05 08:17:47.966
Warning: log write time 500ms, size 2737KB
*** 2013-01-05 08:17:52.374
Warning: log write time 520ms, size 4426KB
tkcrrsarc: (WARN) Failed to find ARCH for message (message:0x1)
tkcrrpa: (WARN) Failed initial attempt to send ARCH message (message:0x1)
*** 2013-01-05 08:18:04.998
Warning: log write time 550ms, size 2602KB
*** 2013-01-05 08:18:31.710
Warning: log write time 520ms, size 642KB
*** 2013-01-05 08:18:47.160
Warning: log write time 560ms, size 1469KB
*** 2013-01-05 08:27:43.090
Warning: log write time 610ms, size 1399KB
*** 2013-01-05 08:28:48.175
Warning: log write time 550ms, size 1924KB
*** 2013-01-05 08:28:51.058
Warning: log write time 570ms, size 525KB
*** 2013-01-05 08:30:10.273
Warning: log write time 540ms, size 1387KB
*** 2013-01-05 08:30:16.239
Warning: log write time 510ms, size 1988KB
*** 2013-01-05 08:30:34.671
Warning: log write time 610ms, size 1301KB
*** 2013-01-05 08:31:00.371
Warning: log write time 510ms, size 571KB
*** 2013-01-05 08:32:05.890
Warning: log write time 560ms, size 2021KB
*** 2013-01-05 08:32:13.680
Warning: log write time 580ms, size 4703KB
*** 2013-01-05 08:32:14.419
Warning: log write time 740ms, size 2705KB
*** 2013-01-05 08:32:14.929
Warning: log write time 510ms, size 2129KB
*** 2013-01-05 08:32:15.607
Warning: log write time 680ms, size 2574KB
*** 2013-01-05 08:32:16.416
Warning: log write time 650ms, size 2404KB
*** 2013-01-05 10:18:01.563
Warning: log write time 1180ms, size 1024KB
*** 2013-01-05 17:15:39.259
Warning: log write time 590ms, size 1680KB
*** 2013-01-06 01:18:23.044
Warning: log write time 500ms, size 2737KB
*** 2013-01-06 03:11:22.249
tkcrrsarc: (WARN) Failed to find ARCH for message (message:0x1)
tkcrrpa: (WARN) Failed initial attempt to send ARCH message (message:0x1)
*** 2013-01-06 04:11:28.720
tkcrrsarc: (WARN) Failed to find ARCH for message (message:0x1)
tkcrrpa: (WARN) Failed initial attempt to send ARCH message (message:0x1)
*** 2013-01-06 06:21:29.802
tkcrrsarc: (WARN) Failed to find ARCH for message (message:0x1)
tkcrrpa: (WARN) Failed initial attempt to send ARCH message (message:0x1)
*** 2013-01-06 07:47:41.885
Warning: log write time 500ms, size 2367KB
*** 2013-01-06 07:47:42.661
Warning: log write time 770ms, size 9648KB
*** 2013-01-06 20:21:56.232
Warning: log write time 2550ms, size 1024KB
*** 2013-01-06 20:22:08.704
Warning: log write time 2500ms, size 1024KB
*** 2013-01-06 23:17:09.196
tkcrrsarc: (WARN) Failed to find ARCH for message (message:0x1)
tkcrrpa: (WARN) Failed initial attempt to send ARCH message (message:0x1)
*** 2013-01-07 00:17:11.018
tkcrrsarc: (WARN) Failed to find ARCH for message (message:0x1)
tkcrrpa: (WARN) Failed initial attempt to send ARCH message (message:0x1)
tkcrrsarc: (WARN) Failed to find ARCH for message (message:0x1)
tkcrrpa: (WARN) Failed initial attempt to send ARCH message (message:0x1)
should i bring the database to mount stage and set log_archive_max_proesses to high count ? now value is 2 ( default )
Please guide me
Thank you
kesavan
|
|
|
|
| Re: high REDO generation [message #574093 is a reply to message #574083] |
Mon, 07 January 2013 01:28  |
kesavansundaram
Messages: 183
Registered: October 2007
Location: MUMBAI
|
Senior Member |
|
|
found the issue. this is a bug.( LGWR unconditionally writes to trace file ) yes, we need to increase the count: log_archive_max_proesses to greater than 10 ( atleast )
since current release version: 10.2.0.4.0, i can go for patch set 10.2.0.5 as per Metalink as this is fixed in these releases.
Thank you
kesavan
|
|
|
|
|
|
Goto Forum:
Current Time: Sun Feb 23 09:26:13 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
