[20181108]12c user_dump_dest與background_dump_dest引數.txt

--//前一段時間在自己的機器安裝了12c for windows,經常做一個10046,我發現12c的引數user_dump_dest與background_dump_dest跟以

--//前不一樣.

1.環境:

SCOTT@test01p> @ ver1

PORT_STRING VERSION BANNER CON_ID

------------------------------ -------------- -------------------------------------------------------------------------------- ----------

IBMPC/WIN_NT64-9.1.0 12.2.0.1.0 Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0

SCOTT@test01p> show parameter dump

NAME TYPE VALUE

--------------------- ------- --------------------------------------------------

background_core_dump string partial

background_dump_dest string E:\APP\ORACLE\PRODUCT\12.2.0\DBHOME_1\RDBMS\TRACE

core_dump_dest string E:\app\oracle\diag\rdbms\test\test\cdump

max_dump_file_size string unlimited

shadow_core_dump string none

user_dump_dest string E:\APP\ORACLE\PRODUCT\12.2.0\DBHOME_1\RDBMS\TRACE

SCOTT@test01p> show spparameter dump

SID NAME TYPE VALUE

-------- ----------------------------- --------- -----------------

* background_core_dump string

* background_dump_dest string

* core_dump_dest string

* max_dump_file_size string

* shadow_core_dump string

* user_dump_dest string

--//可以發現這些引數在spfile根本沒有定義.但是不知道為什麼12c預設background_dump_dest,user_dump_dest引數指向了

--//E:\APP\ORACLE\PRODUCT\12.2.0\DBHOME_1\RDBMS\TRACE.

2.11g下:

SYS@book> @ &r/ver1

PORT_STRING VERSION BANNER

------------------------------ -------------- --------------------------------------------------------------------------------

x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SYS@book> show parameter dump

NAME TYPE VALUE

--------------------- ------- ------------------------------------------

background_core_dump string partial

background_dump_dest string /u01/app/oracle/diag/rdbms/book/book/trace

core_dump_dest string /u01/app/oracle/diag/rdbms/book/book/cdump

max_dump_file_size string unlimited

shadow_core_dump string partial

user_dump_dest string /u01/app/oracle/diag/rdbms/book/book/trace

--//我檢查發現E:\APP\ORACLE\PRODUCT\12.2.0\DBHOME_1\RDBMS\TRACE目錄,確實發現有檔案寫入:

E:\app\oracle\product\12.2.0\dbhome_1\rdbms\trace>dir | grep 2018/11/08

2018/11/08 09:17 <DIR> .

2018/11/08 09:17 <DIR> ..

2018/11/08 09:17 2,548 test_ora_6508.trc

2018/11/08 09:17 1,094 test_ora_6640.trc

--//今天僅僅2個,實際上跟蹤檔案還是寫入E:\APP\ORACLE\diag\rdbms\test\test\trace:

SCOTT@test01p> select * from v$diag_info;

INST_ID NAME VALUE CON_ID

---------- ------------------------------ ------------------------------------------------------------ ----------

1 Diag Enabled TRUE 0

1 ADR Base E:\APP\ORACLE 0

1 ADR Home E:\APP\ORACLE\diag\rdbms\test\test 0

1 Diag Trace E:\APP\ORACLE\diag\rdbms\test\test\trace 0

1 Diag Alert E:\APP\ORACLE\diag\rdbms\test\test\alert 0

1 Diag Incident E:\APP\ORACLE\diag\rdbms\test\test\incident 0

1 Diag Cdump E:\app\oracle\diag\rdbms\test\test\cdump 0

1 Health Monitor E:\APP\ORACLE\diag\rdbms\test\test\hm 0

1 Default Trace File E:\APP\ORACLE\diag\rdbms\test\test\trace\test_ora_6764.trc 0

1 Active Problem Count 0 0

1 Active Incident Count 0 0

11 rows selected.

SCOTT@test01p> select * from V$process where spid in (6640,6508);

no rows selected

--//奇怪並沒有查詢到相關程式.不知道為什麼12c改動這個引數.檢查alert檔案僅僅發現

2018-11-08T09:17:46.618931+08:00

Adjusting the default value of parameter parallel_max_servers

from 160 to 108 due to the value of parameter processes (180)

Starting ORACLE instance (normal) (OS id: 6508)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

2018-11-08T09:17:47.950007+08:00

CLI notifier numLatches:7 maxDescs:321

2018-11-08T09:17:48.900661+08:00

All SGA segments were allocated at startup

2018-11-08T09:17:49.464693+08:00

LICENSE_MAX_SESSION = 0

LICENSE_SESSIONS_WARNING = 0

Initial number of CPU is 4

Number of processor cores in the system is 4

Number of processor sockets in the system is 1

Using LOG_ARCHIVE_DEST_1 parameter default value as E:\app\oracle\product\12.2.0\dbhome_1\RDBMS

Autotune of undo retention is turned on.

IMODE=BR

ILAT =32

2018-11-08T09:17:50.619759+08:00

--//實際上引數background_dump_dest,user_dump_dest引數指向了E:\APP\ORACLE\PRODUCT\12.2.0\DBHOME_1\RDBMS\TRACE.並沒有什麼.

--//只不過我以前看跟蹤檔案位置的指令碼有問題.指令碼如下:

$ cat pp.sql

column tracefile format a80 new_value tracefile

SELECT value ||

CASE

WHEN instr(dbms_utility.port_string, 'WIN_NT') > 0 then '\'

ELSE '/'

END || (

SELECT instance_name FROM v$instance) || '_ora_' || (

SELECT spid ||

CASE

WHEN traceid is not null then '_' || traceid

ELSE null

END

FROM v$process

WHERE addr = (

SELECT paddr FROM v$session

WHERE sid = (

SELECT sid FROM v$mystat WHERE rownum = 1))) || '.trc' as tracefile

FROM v$parameter

WHERE name = 'user_dump_dest' ;

SCOTT@test01p> @ pp

TRACEFILE

-----------------------------------------------------------------------------

E:\APP\ORACLE\PRODUCT\12.2.0\DBHOME_1\RDBMS\TRACE\test_ora_6764.trc

--//這樣看到就和實際不符.

SYS@test> alter system set user_dump_dest='E:\APP\ORACLE\diag\rdbms\test\test\trace';

System altered.

SYS@test> alter system set background_dump_dest='E:\APP\ORACLE\diag\rdbms\test\test\trace';

System altered.

--//這樣看到就與實際情況一下了.

SCOTT@test01p> @ pp

TRACEFILE

------------------------------------------------------------------

E:\APP\ORACLE\diag\rdbms\test\test\trace\test_ora_6764.trc

3.更正我前面一個錯誤:

--//在pdb下看:

SCOTT@test01p> show spparameter dump

SID NAME TYPE VALUE

-------- ----------------------------- -------- ----------

* background_core_dump string

* background_dump_dest string

* core_dump_dest string

* max_dump_file_size string

* shadow_core_dump string

* user_dump_dest string

--//在cdb下看:

SYS@test> show spparameter dump

SID NAME TYPE VALUE

-------- ----------------------------- -------- ----------------------------

* background_core_dump string

* background_dump_dest string E:\APP\ORACLE\diag\rdbms\test\test\trace

* core_dump_dest string

* max_dump_file_size string

* shadow_core_dump string

* user_dump_dest string E:\APP\ORACLE\diag\rdbms\test\test\trace

--//兩者不同,實際上pdb是從cdb繼承下來,pdb下設定的引數儲存在cdb下的sys.pdb_spfile$,參考連結:

http://blog.itpub.net/267265/viewspace-1072674/

[20181108]12c user_dump_dest與background_dump_dest引數.txt

相關文章