审计文件
1. Audit_file_dest
[root@oracle-db-19c ~]# su - oracle
[oracle@oracle-db-19c ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Nov 20 11:50:53 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> alter session set container=PDB1;
Session altered.
SQL> set pagesize 200
SQL> set linesize 200
SQL>
SQL> show user
USER is "SYS"
SQL>
SQL> show parameter audit_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /u01/app/oracle/admin/cdb1/adu
mp
SQL>
哪些情况会被审计?
- 以sysdba 方式登录会被审计
[oracle@oracle-db-19c ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Nov 20 11:57:20 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL>
[oracle@oracle-db-19c adump]$ ls -ltr
total 4
-rw-r-----. 1 oracle oinstall 1507 Nov 20 11:57 cdb1_ora_30553_20221120115720048386907062.aud
[oracle@oracle-db-19c adump]$
- 停止数据库也会被审计
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
[oracle@oracle-db-19c adump]$ ls -ltr
total 16
-rw-r-----. 1 oracle oinstall 1068 Nov 20 12:00 cdb1_m000_25668_20221120120012359190974994.aud
-rw-r-----. 1 oracle oinstall 1068 Nov 20 12:00 cdb1_m000_25668_20221120120012755384041110.aud
-rw-r-----. 1 oracle oinstall 2915 Nov 20 12:00 cdb1_ora_30553_20221120115720048386907062.aud
-rw-r-----. 1 oracle oinstall 838 Nov 20 12:00 cdb1_ora_30737_20221120120057702560103828.aud
[oracle@oracle-db-19c adump]$
- 启动数据库也会被审计
SQL>
SQL> startup
ORACLE instance started.
Total System Global Area 629145392 bytes
Fixed Size 9137968 bytes
Variable Size 197132288 bytes
Database Buffers 415236096 bytes
Redo Buffers 7639040 bytes
Database mounted.
Database opened.
SQL>
[oracle@oracle-db-19c adump]$ ls -ltr
total 28
-rw-r-----. 1 oracle oinstall 1068 Nov 20 12:00 cdb1_m000_25668_20221120120012359190974994.aud
-rw-r-----. 1 oracle oinstall 1068 Nov 20 12:00 cdb1_m000_25668_20221120120012755384041110.aud
-rw-r-----. 1 oracle oinstall 2915 Nov 20 12:00 cdb1_ora_30553_20221120115720048386907062.aud
-rw-r-----. 1 oracle oinstall 838 Nov 20 12:00 cdb1_ora_30737_20221120120057702560103828.aud
-rw-r-----. 1 oracle oinstall 878 Nov 20 12:05 cdb1_ora_30737_20221120120539770965217776.aud
-rw-r-----. 1 oracle oinstall 1785 Nov 20 12:05 cdb1_ora_30941_20221120120539856661622377.aud
-rw-r-----. 1 oracle oinstall 1212 Nov 20 12:06 cdb1_ora_30965_20221120120544264166143021.aud
[oracle@oracle-db-19c adump]$
将审计 adump文件夹删除,会出现哪些情况(无法登录,无法停止数据库,无法启动数据库)
如何解决: 以oralce用户重新建立adump文件夹。便可以做以上操作。
结果是启动不了的。
[oracle@oracle-db-19c cdb1]$ ls -tlr
total 0
drwxr-x---. 2 oracle oinstall 44 Nov 2 15:02 xdb_wallet
drwxr-x---. 2 oracle oinstall 36 Nov 2 15:28 pfile
drwxr-x---. 4 oracle oinstall 100 Nov 15 14:00 dpdump
drwxr-x---. 2 oracle oinstall 6 Nov 20 12:10 adump
[oracle@oracle-db-19c cdb1]$ rmdir adump
[oracle@oracle-db-19c cdb1]$ ls -ltr
total 0
drwxr-x---. 2 oracle oinstall 44 Nov 2 15:02 xdb_wallet
drwxr-x---. 2 oracle oinstall 36 Nov 2 15:28 pfile
drwxr-x---. 4 oracle oinstall 100 Nov 15 14:00 dpdump
[oracle@oracle-db-19c cdb1]$
[oracle@oracle-db-19c ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Nov 20 12:14:21 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
ERROR:
ORA-09925: Unable to create audit trail file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 9925
ORA-01075: you are currently logged on
Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied
Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied
SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
[oracle@oracle-db-19c ~]$
[oracle@oracle-db-19c ~]$
重新建立adump 文件夹,便可以登录数据库
[oracle@oracle-db-19c cdb1]$
[oracle@oracle-db-19c cdb1]$ mkdir adump
[oracle@oracle-db-19c cdb1]$
[oracle@oracle-db-19c cdb1]$ ls -ltr
total 0
drwxr-x---. 2 oracle oinstall 44 Nov 2 15:02 xdb_wallet
drwxr-x---. 2 oracle oinstall 36 Nov 2 15:28 pfile
drwxr-x---. 4 oracle oinstall 100 Nov 15 14:00 dpdump
drwxr-xr-x. 2 oracle oinstall 6 Nov 20 12:15 adump
[oracle@oracle-db-19c cdb1]$
[oracle@oracle-db-19c ~]$
[oracle@oracle-db-19c ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Nov 20 12:16:07 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL>
如何将审计目录搬家? (更改审计参数里的配置路径)
SQL> show parameter audit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /u01/app/oracle/admin/cdb1/adu
mp
audit_sys_operations boolean TRUE
audit_syslog_level string
audit_trail string DB
unified_audit_common_systemlog string
unified_audit_sga_queue_size integer 1048576
unified_audit_systemlog string
SQL>
alter system set audit_file_dest='/home/oracle/adump' scope=spfile;
要使得上条指令生效,需要将数据库实例停止并重启,审计文件路径就会被更改。使用以下命令核查 show parameter audit
SQL> show parameter audit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /u01/app/oracle/admin/cdb1/adu
mp
audit_sys_operations boolean TRUE
audit_syslog_level string
audit_trail string DB
unified_audit_common_systemlog string
unified_audit_sga_queue_size integer 1048576
unified_audit_systemlog string
SQL>
SQL>
SQL>
SQL> alter system set audit_file_dest='/home/oracle/adump' scope=spfile;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL> startup
ORACLE instance started.
Total System Global Area 629145392 bytes
Fixed Size 9137968 bytes
Variable Size 197132288 bytes
Database Buffers 415236096 bytes
Redo Buffers 7639040 bytes
Database mounted.
Database opened.
SQL> show parameter audit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /home/oracle/adump
audit_sys_operations boolean TRUE
audit_syslog_level string
audit_trail string DB
unified_audit_common_systemlog string
unified_audit_sga_queue_size integer 1048576
unified_audit_systemlog string
SQL>
为了使得数据库恢复原来的审计路径,做了以下操作:、
SQL>
SQL> alter system set audit_file_dest='/u01/app/oracle/admin/cdb1/adump' scope=spfile;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup
ORACLE instance started.
Total System Global Area 629145392 bytes
Fixed Size 9137968 bytes
Variable Size 197132288 bytes
Database Buffers 415236096 bytes
Redo Buffers 7639040 bytes
Database mounted.
Database opened.
SQL>
SQL> show parameter audit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /u01/app/oracle/admin/cdb1/adu
mp
audit_sys_operations boolean TRUE
audit_syslog_level string
audit_trail string DB
unified_audit_common_systemlog string
unified_audit_sga_queue_size integer 1048576
unified_audit_systemlog string
SQL>
诊断文件
SQL>
SQL> show parameter diag
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
diagnostic_dest string /u01/app/oracle
SQL>
SQL> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cdb_cluster_name string
cell_offloadgroup_name string
db_file_name_convert string
db_name string cdb1
db_unique_name string cdb1
global_names boolean FALSE
instance_name string cdb1
lock_name_space string
log_file_name_convert string
pdb_file_name_convert string
processor_group_name string
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string cdb1
SQL>
保存跟踪文件的目录如下:
/u01/app/oracle/diag/rdbms/数据库唯一名/实例名/trace
数据库唯一名、实例名: 参考 db_unique_name 、instance_name 。 使用show parameter name 可获得 。
[oracle@oracle-db-19c trace]$ pwd
/u01/app/oracle/diag/rdbms/cdb1/cdb1/trace
[oracle@oracle-db-19c trace]$
警报日志:
alert_<sid>.log
[oracle@oracle-db-19c trace]$ ls -ltr alert_cdb1.log
-rw-r-----. 1 oracle oinstall 584560 Nov 20 12:36 alert_cdb1.log
[oracle@oracle-db-19c trace]$
- 数据库的启动、停止
- 记录所有非默认值的初始化参数
- 记录日志的切换情况
- 记录检查点的完成情况
- 记录数据库工作时遭遇的错误信息
[oracle@oracle-db-19c trace]$ cat alert_cdb1.log | ORA-
bash: ORA-: command not found...
[oracle@oracle-db-19c trace]$
用户进程跟踪文件:
-rw-r-----. 1 oracle oinstall 11770 Nov 20 12:29 cdb1_ora_31570.trc
-rw-r-----. 1 oracle oinstall 2174 Nov 20 12:29 cdb1_ora_31849.trm
-rw-r-----. 1 oracle oinstall 20898 Nov 20 12:29 cdb1_ora_31849.trc
-rw-r-----. 1 oracle oinstall 922 Nov 20 12:30 cdb1_ora_31942.trm
-rw-r-----. 1 oracle oinstall 1303 Nov 20 12:30 cdb1_ora_31942.trc
-rw-r-----. 1 oracle oinstall 2422 Nov 20 12:33 cdb1_ora_31952.trm
-rw-r-----. 1 oracle oinstall 12388 Nov 20 12:33 cdb1_ora_31952.trc
-rw-r-----. 1 oracle oinstall 2174 Nov 20 12:35 cdb1_ora_32337.trm
-rw-r-----. 1 oracle oinstall 20899 Nov 20 12:35 cdb1_ora_32337.trc
-rw-r-----. 1 oracle oinstall 922 Nov 20 12:35 cdb1_ora_32465.trm
-rw-r-----. 1 oracle oinstall 1303 Nov 20 12:35 cdb1_ora_32465.trc
-rw-r-----. 1 oracle oinstall 1021 Nov 20 12:36 cdb1_ora_32488.trm
-rw-r-----. 1 oracle oinstall 1606 Nov 20 12:36 cdb1_ora_32488.trc
[oracle@oracle-db-19c trace]$
用户进程跟踪文件的作用:
- 把一个用户执行的sql指令保存到用户进程跟踪文件中。
- 截获用户所发出的sql信息保存到用户进程跟踪文件。(SQL优化的时候可以用到)
SQL优化中后台打开跟踪。作用非常大。
1.截获 scott用户进程执行的SQL语句
SQL> select sid,serial#,username,machine from v$session where username='SCOTT';
SID SERIAL# USERNAME MACHINE
---------- ---------- ------------------------------ ----------------------------------------------------------------
396 28716 SCOTT oracle-db-19c
SQL>
2. 打开、关闭sql跟踪session捕获sql语句时需要执行以下命令:(注意:打开时间,当完成追踪后及时关闭)
EXEC DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(396,28716,TRUE); ---开启时候
EXEC DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(396,28716,FALSE); --- 关闭捕获追踪文件,长时间开启会使得追踪文件暴增。
SQL>
SQL>
SQL> select sid,serial#,username,machine from v$session where username='SCOTT';
SID SERIAL# USERNAME MACHINE
---------- ---------- ------------------------------ ----------------------------------------------------------------
396 28716 SCOTT oracle-db-19c
SQL> EXEC DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(396,28716,TRUE);
PL/SQL procedure successfully completed.
SQL> EXEC DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(396,28716,FALSE);
PL/SQL procedure successfully completed.
SQL>
3.如何查看跟踪文件的服务器进程:SPID oraclecdb1就是服务器进程
SQL> select sid,serial#,username,machine from v$session where username='SCOTT';
SID SERIAL# USERNAME MACHINE
---------- ---------- ------------------------------ ----------------------------------------------------------------
396 28716 SCOTT oracle-db-19c
SQL> select paddr from v$session where sid=396;
PADDR
----------------
000000006A9319C8
SQL> select spid from v$process where addr='000000006A9319C8';
SPID
------------------------
34966
SQL>
[oracle@oracle-db-19c trace]$ ps -ef | grep 34966
oracle 34966 1 0 13:54 ? 00:00:01 oraclecdb1 (LOCAL=NO)
oracle 35622 30246 0 14:17 pts/2 00:00:00 grep --color=auto 34966
[oracle@oracle-db-19c trace]$
-rw-r-----. 1 oracle oinstall 1056 Nov 20 13:07 cdb1_ora_32488.trm
-rw-r-----. 1 oracle oinstall 8785 Nov 20 13:07 cdb1_ora_32488.trc
-rw-r-----. 1 oracle oinstall 1348 Nov 20 13:30 cdb1_m003_33690.trm
-rw-r-----. 1 oracle oinstall 5719 Nov 20 13:30 cdb1_m003_33690.trc
-rw-r-----. 1 oracle oinstall 585539 Nov 20 14:00 alert_cdb1.log
-rw-r-----. 1 oracle oinstall 48088 Nov 20 14:00 cdb1_ora_34966.trm
-rw-r-----. 1 oracle oinstall 348143 Nov 20 14:00 cdb1_ora_34966.trc
-rw-r-----. 1 oracle oinstall 935 Nov 20 14:21 cdb1_lgwr_32432.trm
-rw-r-----. 1 oracle oinstall 1275 Nov 20 14:21 cdb1_lgwr_32432.trc
-rw-r-----. 1 oracle oinstall 1256 Nov 20 14:21 cdb1_mmon_32456.trm
-rw-r-----. 1 oracle oinstall 3618 Nov 20 14:21 cdb1_mmon_32456.trc
-rw-r-----. 1 oracle oinstall 3284 Nov 20 14:21 cdb1_m001_33182.trm
-rw-r-----. 1 oracle oinstall 24851 Nov 20 14:21 cdb1_m001_33182.trc
-rw-r-----. 1 oracle oinstall 1548 Nov 20 14:23 cdb1_dbrm_32420.trm
-rw-r-----. 1 oracle oinstall 8698 Nov 20 14:23 cdb1_dbrm_32420.trc
-rw-r-----. 1 oracle oinstall 2158 Nov 20 14:23 cdb1_m003_34363.trm
-rw-r-----. 1 oracle oinstall 14593 Nov 20 14:23 cdb1_m003_34363.trc
-rw-r-----. 1 oracle oinstall 3522 Nov 20 14:24 cdb1_m004_32961.trm
-rw-r-----. 1 oracle oinstall 28624 Nov 20 14:24 cdb1_m004_32961.trc
-rw-r-----. 1 oracle oinstall 4183 Nov 20 14:24 cdb1_m002_32710.trm
-rw-r-----. 1 oracle oinstall 36150 Nov 20 14:24 cdb1_m002_32710.trc
-rw-r-----. 1 oracle oinstall 4083 Nov 20 14:24 cdb1_m000_32468.trm
-rw-r-----. 1 oracle oinstall 33802 Nov 20 14:24 cdb1_m000_32468.trc
[oracle@oracle-db-19c trace]$
[oracle@oracle-db-19c trace]$ pwd
/u01/app/oracle/diag/rdbms/cdb1/cdb1/trace
[oracle@oracle-db-19c trace]$
4.oralce 提供了一个小程序tkprof将二进制追踪文件转化为.txt文件
[oracle@oracle-db-19c trace]$ vim cdb1_ora_34966.trc
[oracle@oracle-db-19c trace]$ tkprof cdb1_ora_34966.trc cdb1_ora_34966_trace.txt
TKPROF: Release 19.0.0.0.0 - Development on Sun Nov 20 14:29:42 2022
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
[oracle@oracle-db-19c trace]$
[oracle@oracle-db-19c trace]$ cat cdb1_ora_34966_trace.txt | grep emp
select user#,password,datats#,tempts#,type#,defrole,resource$,ptime,
scott.emp where empno=7566
t.rowcnt,t.blkcnt,t.empcnt,t.avgspc,t.chncnt,t.avgrln,t.analyzetime,
[oracle@oracle-db-19c trace]$
汇总小结:
后台进程的跟踪文件:
<sid>_进程名_进程pid.trc
记录后台进程工作时的状态信息和报错信息
只与故障诊断相关,与性能无关!
通过警报日志的报错信息概要找到有意义的trc文件
用户进程的跟踪文件
<sid>_ora_服务进程的系统pid.trc
记录user process所发出的信息
可以通过命令截获user process发出的sql语句
与故障诊断和性能调整都相关
如何截获user process发出的sql语句
SQL> select sid,serial#,username,machine from v$session where username='SCOTT';
SID SERIAL# USERNAME MACHINE
---------- ---------- ------------------------------ ----------------------------------------------------------------
396 28716 SCOTT oracle-db-19c
SQL> select paddr from v$session where sid=396;
PADDR
----------------
000000006A9319C8
SQL> select spid from v$process where addr='000000006A9319C8';
SPID
------------------------
34966
SQL>
打开sql跟踪:
EXEC DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(sid,serial#,TRUE);
SQL> EXEC DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(396,28716,TRUE);
PL/SQL procedure successfully completed.
SQL>
关闭sql跟踪
EXEC DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(sid,serial#,FALSE);
SQL> EXEC DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(396,28716,FALSE);
PL/SQL procedure successfully completed.
SQL>
查找跟踪文件:
select spid from v$process p,v$session s where p.addr=s.paddr and s.sid=396;
SQL> select sid,serial#,username,machine from v$session where username='SCOTT';
SID SERIAL# USERNAME MACHINE
---------- ---------- ------------------------------ ----------------------------------------------------------------
396 28716 SCOTT oracle-db-19c
SQL> select paddr from v$session where sid=396;
PADDR
----------------
000000006A9319C8
SQL> select spid from v$process where addr='000000006A9319C8';
SPID
------------------------
34966
SQL> select spid from v$process p,v$session s where p.addr=s.paddr and s.sid=396;
SPID
------------------------
34966
SQL>
使用tkprof程序格式化用户跟踪文件
[oracle@oracle-db-19c trace]$ vim cdb1_ora_34966.trc
[oracle@oracle-db-19c trace]$ tkprof cdb1_ora_34966.trc cdb1_ora_34966_trace.txt
TKPROF: Release 19.0.0.0.0 - Development on Sun Nov 20 14:29:42 2022
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
[oracle@oracle-db-19c trace]$
[oracle@oracle-db-19c trace]$ cat cdb1_ora_34966_trace.txt | grep emp
select user#,password,datats#,tempts#,type#,defrole,resource$,ptime,
scott.emp where empno=7566
t.rowcnt,t.blkcnt,t.empcnt,t.avgspc,t.chncnt,t.avgrln,t.analyzetime,
[oracle@oracle-db-19c trace]$
使用tkprof 程序化用户跟踪文件的时候屏蔽无用的递归sql
[oracle@oracle-db-19c trace]$ tkprof cdb1_ora_34966.trc cdb1_ora_34966_trace.txt sys=no