A large number of diagnostic processes are generated using redo logs.

A large number of diagnostic processes are generated using redo logs.
A large number of diagnostic processes generated by redo logs

This article is an original article. For more information, see the source:

Http://write.blog.csdn.net/postedit/41249705

1. Obtain an archive log during the time when the archive log is created: You can query the v $ archived_log view and locate it in combination with the completion_time column.
2. dump the archived log

  ALTER SYSTEM DUMP LOGFILE '/u01/oracle/V7323/dbs/arch1_76.dbf'; 

-- Change the path to the current redo archive path.

The preceding command generates a trace file in user_dump_dest. Upload the trace file to linux (either the root user or the oracle user)

3.

[root@hosta ~]# grep -A2 "^REDO RECORD" his_ora_29032886_dump_arch.trc > redo.log 

4.

[Root @ hosta ~] # Grep OBJ: redo. log | awk-F "OBJ: "'{print $2}' | awk '{print $1}' | sort-n | uniq-c | sort-n-r2038512 4294967295 <---- 2038012 times. 107 60635 60 60464 30 59848 29 62992 60669 9 59810 8 60706 59842

OBJ: 4294967295. This is the redo record of the undo object, which appears for 2038012 times. That is to say, the most redo operation is the undo operation.

[root@hosta ~]# grep OBJ: redo.log |awk -F "OBJ:" '{print $2}' | more4294967295 SCN:0x0001.96090e1b SEQ:  1 OP:5.24294967295 SCN:0x0001.96090e1e SEQ:  1 OP:5.44294967295 SCN:0x0001.96090e1f SEQ:  1 OP:5.24294967295 SCN:0x0001.96090e20 SEQ:  1 OP:5.44294967295 SCN:0x0001.96090e21 SEQ:  1 OP:5.24294967295 SCN:0x0001.96090e22 SEQ:  1 OP:5.44294967295 SCN:0x0001.96090e23 SEQ:  1 OP:5.24294967295 SCN:0x0001.96090e24 SEQ:  1 OP:5.44294967295 SCN:0x0001.96090e25 SEQ:  1 OP:5.24294967295 SCN:0x0001.96090e26 SEQ:  1 OP:5.44294967295 SCN:0x0001.96090e27 SEQ:  1 OP:5.24294967295 SCN:0x0001.96090e28 SEQ:  1 OP:5.44294967295 SCN:0x0001.96090e29 SEQ:  1 OP:5.24294967295 SCN:0x0001.96090e29 SEQ:  2 OP:5.4

Note that the last column above is op, which is the operation flag code.

OP:5.2 Undo HeaderOP:5.4 Commit

5.

[root@hosta ~]# grep -A2 "^CHANGE #" his_ora_29032886_dump_arch.trc > redo_c.log 

6.

[root@hosta ~]# grep OBJ: redo_c.log |awk -F "OBJ:" '{print $2}'|awk '{print $1}'|sort -n|uniq -c |sort -n -r

--- This is to sort object_id in reverse order according to the number of occurrences. For example:

[root@hosta ~]# grep OBJ: redo_c.log |awk -F "OBJ:" '{print $2}'|awk '{print $1}'|sort -n|uniq -c |sort -n -r3057384 42949672951018128 15    279 60669    174 60635

This indicates that OBJ: 4294967295 appears for 3057384 times;
OBJ: occurs 1018128 times in 15.
OBJ: 4294967295. This is the redo record of undo.
OBJ: 15, which can be queried using the following statement: select object_name from dba_objects where object_id = '15 ';
You can locate the redo log surge caused by object_name.

Next, let's check the operation that caused the redo log surge:

[root@hosta ~]# grep OBJ: redo_c.log | moreCHANGE #1 TYP:0 CLS:15 AFN:1 DBA:0x00400009 OBJ:4294967295 SCN:0x0001.96090e1b SEQ:  1 OP:5.2CHANGE #2 TYP:0 CLS:16 AFN:1 DBA:0x0040000a OBJ:4294967295 SCN:0x0001.96090e1a SEQ:  1 OP:5.1CHANGE #3 TYP:2 CLS: 1 AFN:1 DBA:0x0040006a OBJ:15 SCN:0x0001.96090e1b SEQ:  1 OP:11.5CHANGE #1 TYP:0 CLS:15 AFN:1 DBA:0x00400009 OBJ:4294967295 SCN:0x0001.96090e1e SEQ:  1 OP:5.4CHANGE #1 TYP:0 CLS:15 AFN:1 DBA:0x00400009 OBJ:4294967295 SCN:0x0001.96090e1f SEQ:  1 OP:5.2CHANGE #2 TYP:0 CLS:16 AFN:1 DBA:0x0040000a OBJ:4294967295 SCN:0x0001.96090e1e SEQ:  1 OP:5.1CHANGE #3 TYP:2 CLS: 1 AFN:1 DBA:0x0040006a OBJ:15 SCN:0x0001.96090e1f SEQ:  1 OP:11.5CHANGE #1 TYP:0 CLS:15 AFN:1 DBA:0x00400009 OBJ:4294967295 SCN:0x0001.96090e20 SEQ:  1 OP:5.4CHANGE #1 TYP:0 CLS:15 AFN:1 DBA:0x00400009 OBJ:4294967295 SCN:0x0001.96090e21 SEQ:  1 OP:5.2CHANGE #2 TYP:0 CLS:16 AFN:1 DBA:0x0040000a OBJ:4294967295 SCN:0x0001.96090e20 SEQ:  1 OP:5.1CHANGE #3 TYP:2 CLS: 1 AFN:1 DBA:0x0040006a OBJ:15 SCN:0x0001.96090e21 SEQ:  1 OP:11.5

Note that the last column above is op, which is the operation flag code.

OP: 5.1 Undo RecorderOP: 5.2 Undo HeaderOP: 5.4 CommitOP: 11.5 Update Row Piece, that is, the update operation. Based on obj: 15, you can confirm the update on the object.

References:
Http://www.traveldba.com/archives/479
Http://blog.csdn.net/duanbeibei/article/details/6091507