目录
1、跟踪前配置：
SQL> oradebug setmypid;
SQL> oradebug tracefile_name;
2、跟踪数据库启动、数据库创建表操作
打开：【SQL>alter session set events '10046 trace name context forever,level 12';】 关闭：【SQL>alter session set events '10046 trace name context off 】
3、跟踪控制文件
打开：【SQL>alter session set events 'immediate trace name CONTROLF level 8';】
关闭：【SQL>alter session set events '10046 trace name context off 】
根据事件：alter system set events 'immediate trace name controlf level 3';
4、跟踪参数文件
打开：【SQL>alter session set sql_trace=TRUE;】 关闭：【SQL>alter session set sql_trace=FALSE;】
5、配置自动跟踪（SQL即可跟踪）
打开：【SQL>set autotrace on explain;】 关闭：【SQL>set autotrace off explain;】
6、配置自动跟踪（只跟踪）
打开：【SQL>set autotrace traceonly explain】 关闭：【SQL>set autotrace off explain】
7、dump undo的头文件
SQL> select xidusn, xidslot, xidsqn,ubafil,ubablk,ubasqn,ubarec from v$transaction; SQL> select file_id, file_name,tablespace_name from dba_data_files where tablespace_name='UNDOTBS1'; SQL> select file_id,block_id,SEGMENT_ID,SEGMENT_NAME from dba_rollback_segs where SEGMENT_ID=33; [SEGMENT_ID=XIDUSN] alter system dump undo header '_SYSSMU14_4160903802$';
8、dump redo log 文件的方法
（1）根据scn
SQL> select max(ktuxescnw * power(2, 32) + ktuxescnb) from x$ktuxe; ---两次进行
SQL> alter system dump logfile '/oracle/app/oracle/oradata/mesorcl/redo05.log' scn min 10151351 scn max 10160576;
（2）全部dump
alter system dump logfile '/oracle/app/oracle/oradata/mesorcl/redo05.log';
（3）根据块dump[查询对象的段，根据段中的blk
SQL> select file_id, block_id startblock, blocks, block_id+blocks-1 endblock from dba_extents where segment_name='TEST' and owner='TEST';
FILE_ID STARTBLOCK BLOCKS ENDBLOCK
---------- ---------- ---------- ----------
4 136 8 143
alter system dump logfile '/oracle/app/oracle/oradata/mesorcl/redo05.log' dba min 4 136 dba max 4 134
（4）根据文件编号group#,sequence#，进行dump
select * from from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME 1 1 21 209715200 512 1 YES INACTIVE 9987169 2021/5/24 22:24:53 9987172 2021/5/24 22:24:55 2 1 22 209715200 512 1 YES INACTIVE 9987172 2021/5/24 22:24:55 9987200 2021/5/24 22:25:51 3 1 23 209715200 512 1 YES INACTIVE 9987200 2021/5/24 22:25:51 10058532 2021/5/26 0:24:50 4 1 24 209715200 512 1 YES INACTIVE 10058532 2021/5/26 0:24:50 10141265 2021/5/27 7:00:40 5 1 25 209715200 512 1 NO CURRENT 10141265 2021/5/27 7:00:40 281474976710655
alter system dump logfile '/oracle/app/oracle/oradata/mesorcl/redo05.log' rda min 5 25 rda max 5 25
- RBA: 0x000605.00017b82.01a4 由三部分组成：
- 0x000605： 日志的序列号
- 00017b82： redo 日志中块号
- 01a4：日志文件中块内部的偏移量
（5）根据layer+opcode，一般不用
alter system dump logfile '/oracle/app/oracle/oradata/mesorcl/redo05.log' layer 11 opcode 2;
（6）根据session事件dump
alter seesion set events 'immediate trace name redohdr level 10';
9、redo块，undo块、数据块的16进制信息转换
（1）varchar转换
SQL> select UTL_RAW.CAST_TO_VARCHAR2(replace('74 65 73 74',' ')) from dual;
UTL_RAW.CAST_TO_VARCHAR2(REPLACE('74657374',''))
-----------------------------------------------------
test
（2）number转换
SQL> select UTL_RAW.CAST_TO_VARCHAR2(replace('74 65 73 74',' ')) value from dual;
value
-----------
test
select UTL_RAW.CAST_TO_NUMBER(replace('c2,12',',')) value from dual;
value
-----------
1700
10、redo块，undo块、数据块的16进制信息反向转换：
select dump('LLL9','16') from dual;
DUMP('LLL9','16')
Typ=96 Len=4: 4c,4c,4c,39
11、uba命令值转换
SELECT DBMS_UTILITY.data_block_address_file(TO_NUMBER(LTRIM('0x00c00e1e','0x'), 'xxxxxxxx')) AS file_no,DBMS_UTILITY.data_block_address_block(TO_NUMBER(LTRIM('0x00c00e1e','0x'),'xxxxxxxx')) AS block_no from dual;
12、从表中查出每行数据的文件号和块号（可以dump这些块）
select dbms_rowid.rowid_relative_fno(rowid) as fileno,dbms_rowid.rowid_block_number(rowid) as fileblockno,id,name from test3 FILENO FILEBLOCKNO ID NAME 1 2161 1 lll 1 2161 1 lll 1 2161 2 LLL2 1 2161 3 LLL3 1 2161 4 LLL4 1 2161 5 LLL5 1 2161 6 test 1 2161 9 LLL
13、备份控制文件,以trace方式导出-【根据此文件，进行控制文件的重建】
SQL> alter database backup controlfile to trace as '/oracle/LLL_util_cancel.ctl';
14、alias log
alias log='/bin/tail -100f /oracle/app/oracle/diag/rdbms/nccporcl/nccporcl/trace/alert_nccporcl.log'
15、数据库SQL的col格式化
col name for a10;
col member for 9999;
文章评论