数据库连接时可以以sysdba或sysoper登录,两者区别如下
sysdba:show user返回sys,全部数据库权限
sysoper:show user返回public,比sysdba少4个权限(不能创建数据库、不能启动数据库到受限会话访问模式、不能进行热备份、不能进行数据库的不完全恢复)
跟踪文件记录了数据库工作状态的日志,便于我们针对数据库问题进行排查解决
跟踪文件分为四类:
审计文件:记录超级用户(sysdba、sysoper)的连接、数据库的停止、数据库的启动
show parameter audit_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /u01/app/oracle/admin/sundb/adump
如果审计文件目录不存在、空间不够、权限不够创建审计文件,数据库无法启动。
诊断文件:分为三种
show parameter diagnostic_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
diagnostic_dest string /u01/app/oracle
默认情况下diagnostic_dest继承于ORACLE_BASE这个环境变量
警报日志文件:记录数据库启动和停止的过程及异常,记录oracle报错信息,日志切换情况,检查点的完成情况,记录所有非默认值的初始化参数,
存放目录在/u01/app/oracle/diag/rdbms/sundb/sundb/trace下,文件名为 alert_实例名.log
后台进程跟踪文件:记录进程的日志,用来做故障诊断
存放目录在/u01/app/oracle/diag/rdbms/sundb/sundb/trace下,文件名为 实例名_进程名字_系统pid
用户进程跟踪文件:记录用户进程所发出的SQL语句及执行计划,可以用来做SQL优化。默认情况下oracle不会将用户的SQL存入到该文件中,如果需要SQL跟踪,可以执行以下程序包。
存放目录在/u01/app/oracle/diag/rdbms/sundb/sundb/trace下,文件名为 实例名_ora_系统pid
在sys用户下
select sid,serial# from v$session where username='SCOTT';
SID SERIAL#
---------- ----------
37 16
Elapsed: 00:00:00.01
exec DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(37,16,TRUE);
在scott用户下执行一条SQL语句
select * from emp;
然后再sys用户下关掉SQL跟踪
exec DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(37,16,FALSE);
找到刚才的trc文件
tkprof sundb_ora_4413.trc 1.txt
more 1.txt
TKPROF: Release 11.2.0.1.0 - Development on Wed May 2 12:08:25 2018
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Trace file: sundb_ora_4413.trc
Sort options: default
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
SQL ID: 4ttqgu8uu8fus
Plan Hash: 3956160932
SELECT *
FROM
EMP
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 8 0 14
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 8 0 14
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84
Rows Row Source Operation
------- ---------------------------------------------------
14 TABLE ACCESS FULL EMP (cr=8 pr=0 pw=0 time=0 us cost=3 size=532 card=14)
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 8 0 14
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 8 0 14
TKPROF: Release 11.2.0.1.0 - Development on Wed May 2 12:08:25 2018
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Trace file: sundb_ora_4413.trc
Sort options: default
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
SQL ID: 4ttqgu8uu8fus
Plan Hash: 3956160932
SELECT *
FROM
EMP
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 8 0 14
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 8 0 14
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84
Rows Row Source Operation
------- ---------------------------------------------------
14 TABLE ACCESS FULL EMP (cr=8 pr=0 pw=0 time=0 us cost=3 size=532 card=14)
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
SQL ID: 4ttqgu8uu8fus
Plan Hash: 3956160932
SELECT *
FROM
EMP
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 8 0 14
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 8 0 14
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84
Rows Row Source Operation
------- ---------------------------------------------------
14 TABLE ACCESS FULL EMP (cr=8 pr=0 pw=0 time=0 us cost=3 size=532 card=14)
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows