隐式参数_trace_files_public决定了Oracle产生的trace文件是否公开,该参数默认值为FALSE,也就是非DBA/OINSTALL组的用户是没有权限读取数据库产生的trace文件的;在某些场合中我们需要让非DBA组的用户也能访问trace文件,就可以通过修改该参数实现。请看下面的例子:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
|
SQL>
select
*
from
v$version;
BANNER
----------------------------------------------------------------
Oracle
Database
10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS
for
Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
SQL> col
name
for
a20
SQL> col value
for
a20
SQL> col describ
for
a40
SQL>
SELECT
x.ksppinm
NAME
, y.ksppstvl VALUE, x.ksppdesc describ
2
FROM
SYS.x$ksppi x, SYS.x$ksppcv y
3
WHERE
x.inst_id = USERENV (
'Instance'
)
4
AND
y.inst_id = USERENV (
'Instance'
)
5
AND
x.indx = y.indx
6
AND
x.ksppinm
LIKE
'%_trace_files_public%'
7
order
by
x.ksppinm;
NAME
VALUE DESCRIB
-------------------- -------------------- ----------------------------------------
_trace_files_public
FALSE
Create
publicly accessible trace files
SQL> oradebug setmypid;
Statement processed.
SQL> oradebug ipc;
Information written
to
trace file.
SQL> oradebug tracefile_name;
/s01/10gdb/admin/YOUYUS/udump/youyus_ora_10268.trc
SQL> !ls -l /s01/10gdb/admin/YOUYUS/udump/youyus_ora_10268.trc
-rw-r
----- 1 maclean oinstall 4206 Aug 11 20:51 /s01/10gdb/admin/YOUYUS/udump/youyus_ora_10268.trc
/*所产生的trace文件权限为640,非oinstall组用户无权限读取该文件*/
SQL>
alter
system
set
"_trace_files_public"
=
true
;
alter
system
set
"_trace_files_public"
=
true
*
ERROR
at
line 1:
ORA-02095: specified initialization parameter cannot be modified
/*修改该参数需要重启实例*/
SQL>
alter
system
set
"_trace_files_public"
=
true
scope=spfile;
System altered.
SQL> startup
force
;
ORACLE instance started.
Total System
Global
Area 1577058304 bytes
Fixed
Size
2084264 bytes
Variable
Size
922747480 bytes
Database
Buffers 637534208 bytes
Redo Buffers 14692352 bytes
Database
mounted.
Database
opened.
SQL> oradebug setmypid;
Statement processed.
SQL> oradebug ipc;
Information written
to
trace file.
SQL> oradebug tracefile_name;
/s01/10gdb/admin/YOUYUS/udump/youyus_ora_10430.trc
SQL> ! ls -l /s01/10gdb/admin/YOUYUS/udump/youyus_ora_10430.trc
-rw-r
--r-- 1 maclean oinstall 5471 Aug 11 20:54 /s01/10gdb/admin/YOUYUS/udump/youyus_ora_10430.trc
/*other组用户也具有了读权限*/
SQL> ! ls -l /s01/10gdb/admin/YOUYUS/
total 24
drwxr-x
--- 2 maclean oinstall 4096 Aug 11 20:56 adump
drwxr-x
--- 2 maclean oinstall 4096 Aug 11 20:54 bdump
drwxr-x
--- 2 maclean oinstall 4096 Aug 5 21:35 cdump
drwxr-x
--- 2 maclean oinstall 4096 Aug 5 21:36 dpdump
drwxr-x
--- 2 maclean oinstall 4096 Aug 5 21:37 pfile
drwxr-x
--- 2 maclean oinstall 4096 Aug 11 20:54 udump
/*请注意修改_trace_files_public为
true
,并不会修改trace所在目录的权限,Oracle默认建立bdump/udump等trace目录时分配的权限为750,other组用户无法进入这些目录,需要修改目录权限为755,即o+r+x*/
SQL> ! chmod o+r+x /s01/10gdb/admin/YOUYUS/*dump
SQL> ! ls -l /s01/10gdb/admin/YOUYUS/
total 24
drwxr-xr-x 2 maclean oinstall 4096 Aug 11 20:56 adump
drwxr-xr-x 2 maclean oinstall 4096 Aug 11 20:54 bdump
drwxr-xr-x 2 maclean oinstall 4096 Aug 5 21:35 cdump
drwxr-xr-x 2 maclean oinstall 4096 Aug 5 21:36 dpdump
drwxr-x
--- 2 maclean oinstall 4096 Aug 5 21:37 pfile
drwxr-xr-x 2 maclean oinstall 4096 Aug 11 20:54 udump
/*需要注意的另一点是修改_trace_files_public参数并不会引起既有的trace文件的权限被修改,典型的例子是alert log告警日志*/
[maclean@rh2 bdump]$ ls -l
total 20
-rw-r
----- 1 maclean oinstall 12971 Aug 11 21:17 alert_YOUYUS.log
-rw-r
--r-- 1 maclean oinstall 690 Aug 11 21:12 youyus_lgwr_10514.trc
SQL>
SELECT
x.ksppinm
NAME
, y.ksppstvl VALUE, x.ksppdesc describ
2
FROM
SYS.x$ksppi x, SYS.x$ksppcv y
3
WHERE
x.inst_id = USERENV (
'Instance'
)
4
AND
y.inst_id = USERENV (
'Instance'
)
5
AND
x.indx = y.indx
6
AND
x.ksppinm
LIKE
'%_trace_files_public%'
7
order
by
x.ksppinm;
NAME
VALUE DESCRIB
-------------------- -------------------- ----------------------------------------
_trace_files_public
FALSE
Create
publicly accessible trace files
SQL>
alter
system
set
"_trace_files_public"
=
true
scope=spfile;
System altered.
SQL> startup
force
;
ORACLE instance started.
Total System
Global
Area 1577058304 bytes
Fixed
Size
2084264 bytes
Variable
Size
922747480 bytes
Database
Buffers 637534208 bytes
Redo Buffers 14692352 bytes
Database
mounted.
Database
opened.
SQL> !ls -l
total 32
-rw-r
----- 1 maclean oinstall 21189 Aug 11 21:20 alert_YOUYUS.log
-rw-r
--r-- 1 maclean oinstall 690 Aug 11 21:12 youyus_lgwr_10514.trc
-rw-r
--r-- 1 maclean oinstall 690 Aug 11 21:20 youyus_lgwr_11136.trc
|
本文转自maclean_007 51CTO博客,原文链接:http://blog.51cto.com/maclean/1277527