Oracle数据库如何定位trace file位置

用一个示例来说明吧。

在导入master key时，出现错误：

ADMINISTER KEY MANAGEMENT
  IMPORT KEYS WITH SECRET "my_secret"
  FROM '/tmp/export.exp'
  IDENTIFIED BY keypwd
  5    WITH BACKUP;
ADMINISTER KEY MANAGEMENT
*
ERROR at line 1:
ORA-46655: no valid keys in the file from which keys are to be imported
1
2
3
4
5
6
7
8
9

另一个错误：

ADMINISTER KEY MANAGEMENT
  IMPORT KEYS WITH SECRET "my_secret"
  FROM '/tmp/export.exp'
  IDENTIFIED BY keypwd
  5    WITH BACKUP;
ADMINISTER KEY MANAGEMENT
*
ERROR at line 1:
ORA-46654: cannot add the imported keys to the keystore
1
2
3
4
5
6
7
8
9

第2个错误提示让看trace file，第一个错误虽然没提示，但详细的错误信息也在trace file中。

SQL> !oerr ora 46654
46654, 00000, "cannot add the imported keys to the keystore"
// *Cause: The operation of writing the imported keys to the keystore failed.
// *Action: Check the contents of the trace file for more information on the
//          cause of the failure and retry the command.

SQL> !oerr ora 46655
46655, 00000, "no valid keys in the file from which keys are to be imported"
// *Cause: The specified keystore, from which keys are to be imported, did not
//         contain any valid keys.
// *Action: None.
1
2
3
4
5
6
7
8
9
10
11

根据Oracle的文档，运行以下命令以定位trace file:

select s.sid,s.serial#,s.audsid,s.username,s.osuser, s.client_identifier, s.sql_trace,s.action, p.spid, p.TRACEFILE
from v$session s,v$process p
where s.paddr=p.addr
and s.username is not null;

...
TRACEFILE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/orcl/ORCL/trace/ORCL_ora_32145.trc
...
1
2
3
4
5
6
7
8
9
10

然后就可以找到准确的错误信息：

Trace file /u01/app/oracle/diag/rdbms/orcl/ORCL/trace/ORCL_ora_32379.trc
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.20.0.0.0
Build label:    RDBMS_19.20.0.0.0DBRU_LINUX.X64_230621
ORACLE_HOME:    /u01/app/oracle/product/19c/dbhome_1
System name:    Linux
Node name:      instance-20231013-1024-db19c-iaas
Release:        5.4.17-2136.322.6.2.el7uek.x86_64
Version:        #2 SMP Sat Aug 19 11:55:11 PDT 2023
Machine:        x86_64
Instance name: ORCL
Redo thread mounted by this instance: 1
Oracle process number: 56
Unix process pid: 32379, image: oracle@instance-20231013-1024-db19c-iaas (TNS V)
1
2
3
4
5
6
7
8
9
10
11
12
13
14