OraFAQ Forum: Server Administration » Huge archive generation

Home » RDBMS Server » Server Administration » Huge archive generation (Oralce 10g (10.2.0.4.0) on AIX 5.3)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Huge archive generation [message #549663]

Mon, 02 April 2012 23:48

raj9999
Messages: 49
Registered: June 2011

Member

In normal days size of archives generated in a day is 14-15GB. But since yesterday morning, almost 150GB of archives have been generated and are still getting generated(200MB every 1-2 minutes).

There was a sudden reboot of server yesterday morning. At that time there was heavy load of transactions on database. Can it be a reason that smon is still doing recovery? (I am not sure on this). Also, Undo tablespace is increased from 18 GB to 50 GB since yesterday (autoextend on).

Now we are running out of space for archive file system (can't delete them also until they are transferred to DR)

Size of redo log is 200MB.
This database supports around 2500 users.

performance wise I don't see any hit. Also wait events are normal. (only few db file sequential read)

Kindly help in finding the query/session which are causing this much huge amount of archives?

Report message to a moderator

Re: Huge archive generation [message #549664 is a reply to message #549663]

Mon, 02 April 2012 23:53

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Kindly help in finding the query/session which are causing this much huge amount of archives?
DBMS_LOGMNR can reveal what DML is contained in REDO logs.

Report message to a moderator

Re: Huge archive generation [message #549667 is a reply to message #549664]

Tue, 03 April 2012 00:31

raj9999
Messages: 49
Registered: June 2011

Member

Do i need to check sql_redo, sql_undo from V$LOGMNR_CONTENTS or you are talking about something else? Please provide the query if possible.

Report message to a moderator

Re: Huge archive generation [message #549684 is a reply to message #549667]

Tue, 03 April 2012 01:05

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

SQL> desc V$LOGMNR_CONTENTS 
 Name                         
 -----------------------------
 SCN                          
 CSCN                         
 TIMESTAMP                    
 COMMIT_TIMESTAMP             
 THREAD#                      
 LOG_ID                       
 XIDUSN                       
 XIDSLT                       
 XIDSQN                       
 PXIDUSN                      
 PXIDSLT                      
 PXIDSQN                      
 RBASQN                       
 RBABLK                       
 RBABYTE                      
 UBAFIL                       
 UBABLK                       
 UBAREC                       
 UBASQN                       
 ABS_FILE#                    
 REL_FILE#                    
 DATA_BLK#                    
 DATA_OBJ#                    
 DATA_OBJD#                   
 SEG_OWNER                    
 SEG_NAME                     
 TABLE_NAME                   
 SEG_TYPE                     
 SEG_TYPE_NAME                
 TABLE_SPACE                  
 ROW_ID                       
 SESSION#                     
 SERIAL#                      
 USERNAME                     
 SESSION_INFO                 
 TX_NAME                      
 ROLLBACK                     
 OPERATION                    
 OPERATION_CODE               
 SQL_REDO                     
 SQL_UNDO                     
 RS_ID                        
 SEQUENCE#                    
 SSN                          
 CSF                          
 INFO                         
 STATUS                       
 REDO_VALUE                   
 UNDO_VALUE                   
 SQL_COLUMN_TYPE              
 SQL_COLUMN_NAME              
 REDO_LENGTH                  
 REDO_OFFSET                  
 UNDO_LENGTH                  
 UNDO_OFFSET                  
 DATA_OBJV#                   
 SAFE_RESUME_SCN              
 XID                          
 PXID                         
 AUDIT_SESSIONID

Which columns do you think answer the questions: "finding the query/session which are causing this much huge amount of archives?"

Regards
Michel

Report message to a moderator

Re: Huge archive generation [message #549690 is a reply to message #549663]

Tue, 03 April 2012 01:27

John Watson
Messages: 8962
Registered: January 2010
Location: Global Village

Senior Member

If the the sessions are still connected, this will give you the session IDs:

select sid,value from v$sesstat natural join v$statname where name='redo size' order by 2;

then join to v$session and v$sql to see what they are doing.

Report message to a moderator

Re: Huge archive generation [message #549695 is a reply to message #549684]

Tue, 03 April 2012 02:01

raj9999
Messages: 49
Registered: June 2011

Member

Hi Michel,

When I am not able to understand the problem and the possible solution, then only I have come here for help. You can reply with a possible answer also instead of asking question again.
Anyway, Thanks for your help.

Hi John,

below is the query output (4-5 rows with highest value).
Largest is of SMOM.

      SID      VALUE
   ---------- ----------
      8693   36847168
      8679   37257664
      8801  484244248
      8802  488521036
      8798 1.9716E+11

2569 rows selected.

SQL> @sess
Enter value for sid: 8798
old   1: select sid,serial#,username,status,machine,program,SQL_HASH_VALUE from v$session where sid=&sid
new   1: select sid,serial#,username,status,machine,program,SQL_HASH_VALUE from v$session where sid=8798

       SID    SERIAL# USERNAME                       STATUS   MACHINE                                                          PROGRAM                                          SQL_HASH_VALUE
---------- ---------- ------------------------------ -------- ---------------------------------------------------------------- ------------------------------------------------ --------------
      8798          1                                ACTIVE   sunpath31                                                         oracle@sunpath31 (SMON)                                        0

SQL> /
Enter value for sid: 8802
old   1: select sid,serial#,username,status,machine,program,SQL_HASH_VALUE from v$session where sid=&sid
new   1: select sid,serial#,username,status,machine,program,SQL_HASH_VALUE from v$session where sid=8802

       SID    SERIAL# USERNAME                       STATUS   MACHINE                                                          PROGRAM                                          SQL_HASH_VALUE
---------- ---------- ------------------------------ -------- ---------------------------------------------------------------- ------------------------------------------------ --------------
      8802          3                                ACTIVE   sunpath31                                                         oracle@sunpath31 (DBW0)                                        0

SQL> /
Enter value for sid: 8801
old   1: select sid,serial#,username,status,machine,program,SQL_HASH_VALUE from v$session where sid=&sid
new   1: select sid,serial#,username,status,machine,program,SQL_HASH_VALUE from v$session where sid=8801

       SID    SERIAL# USERNAME                       STATUS   MACHINE                                                          PROGRAM                                          SQL_HASH_VALUE
---------- ---------- ------------------------------ -------- ---------------------------------------------------------------- ------------------------------------------------ --------------
      8801          3                                ACTIVE   sunpath31                                                         oracle@sunpath31 (DBW1)                                        0

SQL> /
Enter value for sid: 8679
old   1: select sid,serial#,username,status,machine,program,SQL_HASH_VALUE from v$session where sid=&sid
new   1: select sid,serial#,username,status,machine,program,SQL_HASH_VALUE from v$session where sid=8679

       SID    SERIAL# USERNAME                       STATUS   MACHINE                                                          PROGRAM                                          SQL_HASH_VALUE
---------- ---------- ------------------------------ -------- ---------------------------------------------------------------- ------------------------------------------------ --------------
      8679          1 AGENT1                         INACTIVE sunpath21                                                         diskr-mon-all@sunpath81 (TNS V1-V3)                     0

SQL> @sess
Enter value for sid: 8693
old   1: select sid,serial#,username,status,machine,program,SQL_HASH_VALUE from v$session where sid=&sid
new   1: select sid,serial#,username,status,machine,program,SQL_HASH_VALUE from v$session where sid=8693

       SID    SERIAL# USERNAME                       STATUS   MACHINE                                                          PROGRAM                                          SQL_HASH_VALUE
---------- ---------- ------------------------------ -------- ---------------------------------------------------------------- ------------------------------------------------ --------------
      8693          2 AGENT1                         INACTIVE sunpath21                                                         diskr-mon-all@sunpath81 (TNS V1-V3)                     0

Report message to a moderator

Re: Huge archive generation [message #549696 is a reply to message #549695]

Tue, 03 April 2012 02:08

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

Hi Michel,

When I am not able to understand the problem and the possible solution, then only I have come here for help. You can reply with a possible answer also instead of asking question again.
Anyway, Thanks for your help.

Did you read the columns definition of the view? It is then obvious to know which SESSION# uses the more REDO_LENGTH.
A little effort is of course required.

Regards
Michel

Report message to a moderator

Re: Huge archive generation [message #551669 is a reply to message #549696]

Fri, 20 April 2012 01:13

Rektanocrit
Messages: 24
Registered: September 2011
Location: Philippines

Junior Member

I encountered something like that before, what I did is checked the archive logs using notepad++, and saw some string in it, that string is one of our user, after that i checked all the sql statements that user run/execute and found out that s/he performed a bad sql statement (infinite loop). By the way, i saw her username in all of our archive logs. I fixed her sql statement, then restart the database.

Report message to a moderator

Previous Topic:	SQL Plus no access
Next Topic:	Download patchset

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Jan 07 21:55:43 CST 2025