How to trace

--Instance-level trace

alter system|session set sql_trace=true;

--session-level SQL tracing
select sid,serial# from v$session where username='ABC' ;
execute dbms_monitor.session_trace_enable(session_id=>123, serial_num=>123);
execute dbms_monitor.session_trace_disable(session_id=>123, serial_num=>123);

Tracing with database control
--The DBMS_MONITOR packages has procedures that will let you enable tracing at these levels:
*session level
*Module level
*Client ID level
*Service Level
*Action

ORA-3136 in 10.2

Mon Jun 4 01:59:08 2007
WARNING: inbound connection timed out (ORA-3136)

> oerr ora 3136
03136, 00000, "inbound connection timed out"
// *Cause: Inbound connection was timed out by the server because
// user authentication was not completed within the given time
// specified by SQLNET.INBOUND_CONNECT_TIMEOUT or its default value
// *Action: 1) Check SQL*NET and RDBMS log for trace of suspicious connections.
// 2) Configure SQL*NET with a proper inbound connect timeout value
// if necessary.

The log in sqlnet.log is

***********************************************************************
Fatal NI connect error 12170.

VERSION INFORMATION:
TNS for HPUX: Version 10.2.0.2.0 - Production
Oracle Bequeath NT Protocol Adapter for HPUX: Version 10.2.0.2.0 - Production
TCP/IP NT Protocol Adapter for HPUX: Version 10.2.0.2.0 - Production
Time: 04-JUN-2007 01:59:08
Tracing not turned on.
Tns error struct:
ns main err code: 12535
TNS-12535: TNS:operation timed out
ns secondary err code: 12606
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0
Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.22.100.155)(PORT=1341))

According to metalink notes: 345197.1 and 316901.1, the default value in 10.2 is 60 seconds ( 0 second in 10.1)

Hence, we might need to monitor the frequency to decide whether need to set INBOUND_CONNECT_TIMEOUT =0


Oracle also recommends set for both listener and database.
Hence as below to listener.ora is advised.

INBOUND_CONNECT_TIMEOUT_ = 0

DB Monitoring scripts

什么时候发生checkpoint?

我们知道了checkpoint会刷新脏数据，但什么时候会发生checkpoint呢？以下几种情况会触发checkpoint。
1.当发生日志组切换的时候
2.当符合LOG_CHECKPOINT_TIMEOUT[限制了上一检查点和最近的重做记录之间的秒数];
LOG_CHECKPOINT_INTERVAL[恢复过程中将要被读的重做记录的数目,最优=redo log/os(512)];
fast_start_io_target[恢复需要的数据块数目];
fast_start_mttr_target[允许DBA指定数据库进行崩溃恢复需要的秒数]参数设置的时候;
3.当运行ALTER SYSTEM SWITCH LOGFILE的时候;
4.当运行ALTER SYSTEM CHECKPOINT的时候;
5.当运行alter tablespace XXX begin backup，end backup的时候;
6.当运行alter tablespace ,datafile offline的时候;

Have a Rest

Testing ...

DBMS_SERVER_ALERT

Yesterday(Fridday and not working on Saturday), I configured four server-generated alerts on four database, which sends critical alert via SMS. Unfortunately , I didn't expect one of the databases was so busy in the night (DB Wait time critcal threshold exceeded) , SMS beep keeps coming. What a night.

The firewall port has number opened, caused I can't access DB Control 's web page. Only things at end is having SYSMAN and password. No choice have to try to explore using DBMS_SERVER_ALERT to temporarily increase the threshold.

Google ... 10g OEM document ...
Here is thing learned quickly.

select * from dba_thresholds where metrics_name='Database Wait Time Ratio';
--easy than using DBMS_SERVER_ALERT.GET_THRESHOLD
BEGIN
DBMS_SERVER_ALERT.SET_THRESHOLD(
metrics_id => DBMS_SERVER_ALERT.DATABASE_WAIT_TIME,
warning_operator => DBMS_SERVER_ALERT.OPERATOR_GE,
warning_value => '95',
critical_operator => DBMS_SERVER_ALERT.OPERATOR_GE,
critical_value => '99',
observation_period => 1,
consecutive_occurrences => 3,
instance_name => 'orcl10g2',
object_type => DBMS_SERVER_ALERT.OBJECT_TYPE_SYSTEM,
object_name => NULL
);
END;
/

--for blocking session count
--session blocking for CUSPA
select * from dba_thresholds where metrics_name ='Blocked User Session Count';

BEGIN
DBMS_SERVER_ALERT.SET_THRESHOLD(
metrics_id => DBMS_SERVER_ALERT.BLOCKED_USERS,
warning_operator => DBMS_SERVER_ALERT.OPERATOR_GE,
warning_value => '50',
critical_operator => DBMS_SERVER_ALERT.OPERATOR_GE,
critical_value => '100',
observation_period => 1,
consecutive_occurrences => 15,
instance_name => 'orcl10g2',
object_type => DBMS_SERVER_ALERT.OBJECT_TYPE_SESSION,
object_name => NULL
);
END;
/

Hope I can sleep well tonight.

Ref Oracle 10g doc:
Enterprise Manager Oracle Database and Database-Related Metric Reference Manual
Database PL/SQL Packages and Types Reference

Log Miner

我们可以使用logminer分析其它instance（版本可不一致）的重做日志文件，但是必须遵循以下要求：

1. LogMiner日志分析工具仅能够分析Oracle 8以后的产品

2. LogMiner必须使用被分析数据库实例产生的字典文件，且安装LogMiner数据库的字符集必须和被分析数据库的字符集相同

3. 被分析数据库平台必须和当前LogMiner所在数据库平台一样，且block size相同。



使用logminer

1. 安装logminer：

要安装LogMiner工具，必须首先要运行下面这样两个脚本，
$ORACLE_HOME/rdbms/admin/dbmslm.sql
$ORACLE_HOME/rdbms/admin/dbmslmd.sql.
这两个脚本必须均以SYS用户身份运行。



2. 创建数据字典文件

首先在init.ora初始化参数文件中，添加参数UTL_FILE_DIR，该参数值为服务器中放置数据字典文件的目录。如：
UTL_FILE_DIR = (D:\Oracle\logs)



重新启动数据库，使新加的参数生效，然后创建数据字典文件：
SQL> EXECUTE dbms_logmnr_d.build(
dictionary_filename => ' logmn_ora817.dat',
dictionary_location => ' D:\Oracle\logs ');

创建数据字典是让LogMiner引用涉及到内部数据字典中的部分时使用对象的名称，而不是系统内部的16进制的ID。如果我们要分析的数据库中的表有变化，影响到库的数据字典也发生变化，就需要重新创建该字典。



3. 添加要分析的日志文件

Logminer可以用来分析在线的重做日志文件和归档日志文件，但是我们一般建议使用归档的日志文件。

a.添加新的日志文件：
SQL> EXECUTE dbms_logmnr.add_logfile(
LogFileName=>' D:\database\oracle\oradata\ora817\archive \ ARC01491.001 ', Options=>dbms_logmnr.new);

b.添加另外的日志文件到列表
SQL> EXECUTE dbms_logmnr.add_logfile(
LogFileName=>' D:\database\oracle\oradata\ora817\archive \ ARC01491.002', Options=>dbms_logmnr.addfile);



c. 移去一个日志文件

SQL> EXECUTE dbms_logmnr.add_logfile(
LogFileName=>' D:\database\oracle\oradata\ora817\archive \ ARC01491.002', Options=>dbms_logmnr. REMOVEFILE);



创建了要分析的日志文件，就可以对其进行分析。



4. 进行日志分析

SQL> EXECUTE dbms_logmnr.start_logmnr(
DictFileName=>' D:\Oracle\logs\ logmn_ora817.dat ');

可以使用相应的限制条件：

时间范围：对dbms_logmnr.start_logmnr使用StartTime和EndTime参数

SCN范围：对dbms_logmnr.start_logmnr使用StartScn和EndScn参数



5.观察结果：

主要是查询v$logmnr_contents：

SQL> desc v$logmnr_contents;


通过字段sql_redo可以得到该日志文件中进行过的sql操作，通过sql_undo可以得到撤销的sql语句。

还可以用这样的sql对日志文件中的所有的操作分类统计：

select operation,count(*)from v$logmnr_contents group by operation;


视图v$logmnr_contents中的分析结果仅在我们运行过程'dbms_logmrn.start_logmnr'这个会话的生命期中存在。这是因为所有的LogMiner存储都在PGA内存中，所有其他的会话是看不到它的，同时随着会话的结束而清除分析结果。



最后，使用过程DBMS_LOGMNR.END_LOGMNR终止日志分析事务，PGA内存区域将被清除。


查看日志分析的结果,通过查询v$logmnr_contents可以查询到
a、查看DML操作,示例:
SELECT operation,sql_redo,sql_undo,FROM V$logmnr_contents
WHERE seg_name = 'QIUYB';

OPERATION SQL_REDO SQL_UNDO
---------- -------------------------- --------------------------
INSERT inser into qiuyb.qiuyb ... delete from qiuyb.qiuyb...

其中operation指的是操作，sql_redo指的是实际的操作，sql_undo指的是用于取消的相反的操作。

b、查看DDL操作,示例:
SELECT timstamp,sql_redo FROM v$logmnr_contents
WHERE upper(sql_redo) like '%TRUNCATE%';