oracle log º¸±â - alert, trace
1. alert.log °æ·Î ¾Ë¾Æº¸±â
(bdump Æú´õ)
select value
from v$parameter
where name='background_dump_dest'
==============================================================================================================
10046 Æ®·¹À̽º ÆÄÀÏÀÇ ·¹º§
Level 0 : Æ®·¹À̽º ¾È ÇÔ. SQL_TRACE=false ·Î ¼³Á¤ÇÑ °Í°ú µ¿ÀÏÇÑ È¿°ú
Level 1 : ÀϹÝÀûÀÎ SQL Æ®·¹À̽º(SQL_TRACE=true) Á¤º¸ Á¦°ø
Level 4 : SQL Æ®·¹À̽º Á¤º¸¿Í ¹ÙÀÎµå º¯¼ö °ª Á¦°ø
Level 8 : SQL Æ®·¹À̽º Á¤º¸¿Í ´ë±âÀ̺¥Æ® Á¤º¸ Á¦°ø
Level 12 : SQL Æ®·¹À̽º Á¤º¸, ´ë±â À̺¥Æ® Á¤º¸, ±×¸®°í ¹ÙÀÎÆ® º¯¼ö °ª Á¤º¸ Á¦°ø
Æ®·¹À̽º ÆÄÀÏÀÇ »ý¼º À§Ä¡
»ç¿ëÀÚ ¼¼¼Ç Æ®·¹À̽º : USER_DUMP_DEST(UDUMP) ·Î ÁöÁ¤ÇÑ µð·ºÅ丮
¹é±×¶ó¿îµå ÇÁ·Î¼¼½º : BACKGROUND_DUMP_DEST(BDUMP) ·Î ÁöÁ¤ÇÑ µð·ºÅ丮
Å©±â´Â Æ®·¹À̽º ·¹º§, ¼öÇà½Ã°£, ¼¼¼ÇÀÇ È°µ¿, MAX_DUMP_FILE_SIZE ÆĶó¹ÌÅÍ¿¡ ÀÇÇØ ´Ù¸§
10046 Trace Event »ç¿ë¹ý
10046 Æ®·¹À̽º À̺¥Æ®´Â ÀÚ½ÅÀÇ ¼¼¼Ç ¶Ç´Â ´Ù¸¥ ¼¼¼Ç¿¡ ¼³Á¤ÇÒ ¼ö ÀÖ´Ù.
¼¼¼Ç ¼öÇà µµÁß¿¡ ÀÓÀÇÀÇ ½ÃÁ¡¿¡, Æ®·¹À̽ºÀÇ È°¼ºÈ ¶Ç´Â ºñ È°¼ºÈ°¡ °¡´ÉÇÏ´Ù.
ºñÈ°¼ºÈ µÉ °æ¿ì ORACLEÀº Æ®·¹À̽º ÆÄÀÏÀÇ ±â·ÏÀ» Áß´ÜÇÑ´Ù.
1> Show parameter user_dump_dest ·Î trace ÆÄÀÏ ¸¸µé¾î Áú °æ·Î ¸¦ È®ÀÎ ÇÑ´Ù.
alter system set user_dump_dest=<value>·Î trace ÆÄÀÏ °æ·Î¸¦ ¼³Á¤ÇÒ ¼ö ÀÖ´Ù.
2> alter session set timed_statistics=true; ·Î ¼³Á¤
3> alter session set max_dump_file_size=unlimited·Î ¼³Á¤
4> alter sessoin set events '10046 trace name context forever, level 8' ·Î ¼³Á¤
-->Æ®·¹À̽º ÆÄÀÏ »ý¼º ½ÃÀÛ
5> alter session set events '10046 trace name context off'·Î ¼³Á¤
-->Æ®·¹À̽º ÆÄÀÏ »ý¼º Áß´Ü
DBMS_SUPPORT ÆÐÅ°Áö°¡ ¼³Ä¡µÈ °æ¿ì
1>exec sys.dbms_support.start_trace;
-->Æ®·¹À̽º ÆÄÀÏ¿¡ ´ë±â À̺¥Æ® Á¤º¸¸¦ ±â·ÏÇÑ´Ù.
2>exec sys.dbms_support.start_trace(waits=> true, binds=>true)
-->Æ®·¹À̽º ÆÄÀÏ¿¡ ´ë±â À̺¥Æ® ¹× ¹ÙÀÎµå º¯¼ö °ªµéÀ» ±â·Ï
3>exec sys.dbms_support.stop_trace;
-->Æ®·¹À̽º¸¦ ÇØÁ¦ÇÑ´Ù.
Âü°í>
DBMS_SUPPORT ÆÐÅ°Áö´Â oralce_home/rdbms/admin µð·ºÅ丮¿¡ ÀÖ´Â dbmssupl.sql À» ¼öÇàÇØ¾ß ÇÑ´Ù.
´Ù¸¥ »ç¿ëÀÚÀÇ ¼¼¼Ç Æ®·¹À̽º Çϱâ
1>select sid, serial#
from v$session
2>exec dbms_system.set_bool_param_in_session( - sid => <sid>, - serial#=><serial#>, - param => 'TIMED_STATISTICS', - bval => true);
3>exec dbms_system.set_int_param_in_session(- sid=><sid>, -serial#=><serial#>,- param => 'MAX_DUMP_FILE_SIZE', - intval => unlimited);
4> exec dbms_support.start_trace_in_session( - sid=><serial#> , - serial#=><serial#>, - waits => true, - binds => true);
Oradubug ±â´É
1> ¼¼¼ÇÀÇ O/S ÇÁ·Î¼¼½º ID(SPID) ¶Ç´Â ¿À¶óŬ ÇÁ·Î¼¼½º ID(PID)¸¦ ¾Ë¾Æ¾ß ÇÕ´Ï´Ù.
select a.username, b.spid os_process_id, b.pid oracle_process_id
from v$session a, v$process b
where a.paddr = b.addr
and a.username = upper(':user_name');
2>alter system set timed_statistics = true;
3>oradebug setospid <os_process_id>;
4>oradubug unlimited;
5>oradebug event 10046 trace name context forever, level 8;
6>oradebug event 10046 trace name context off;
Ãâó: http://kkamagistory.tistory.com/322?category=620111 [Karas.]