Welcome, Guest
Username: Password: Remember me

TOPIC: MR Trace and Oracle 12

MR Trace and Oracle 12 2 years 5 months ago #441

  • Ron Crisco
  • Ron Crisco's Avatar
  • OFFLINE
  • Posts: 31
  • Thank you received: 2
If you are having difficulties with MR Trace and Oracle 12, it may be due to changes in the way Oracle tells us where trace files are located. The parameter USER_DUMP_DEST has been deprecated, and may or may not point to the right file system directory.

If you have troubles, just execute the following PL/SQL as SYS user, and the directory objects that MR Trace needs will be corrected.
begin
    for v in (select g.inst_id, v.value from gv$instance g, v$diag_info v where v.name = 'Diag Trace')
    loop
      execute immediate 'CREATE OR REPLACE DIRECTORY METHODR_UDUMP_' || v.inst_id || ' AS ''' || v.value || '''' ;
      execute immediate 'GRANT READ ON DIRECTORY METHODR_UDUMP_' || v.inst_id || ' TO PUBLIC';
      execute immediate 'CREATE OR REPLACE DIRECTORY METHODR_UDUMP_DEST AS ''' || v.value || '''';
      execute immediate 'GRANT READ ON DIRECTORY METHODR_UDUMP_DEST TO PUBLIC';
    end loop;
end;
/

If you wish to change the "PUBLIC" to a single role or schema, please feel free, but please do not alter the directory names. MR Trace needs those as is.

Yes, I do understand the code is not very elegant and executes the METHODR_UDUMP_DEST more than necessary in RAC environments with more than 1 node. Consider that refactoring an exercise for the reader :)

Ron Crisco
Last Edit: 2 years 5 months ago by Ron Crisco.
The administrator has disabled public write access.