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 Go to next message
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 Go to previous messageGo to next message
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
Re: high REDO generation [message #574097 is a reply to message #574093] Mon, 07 January 2013 01:37 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thank you to let us know.

Regards
Michel
Previous Topic: ORA-01115:ORA-27070:
Next Topic: ORA-4030 out of process memory when trying to allocate 246296 bytes (QERHJ hash-joi,kllcqas:kllslt
Goto Forum:
  


Current Time: Thu Mar 28 08:32:59 CDT 2024