@tracefile_read_last_by_mask filemask [regexp] [ignore_regexp]
– finds last trace by filemask and filters rows by regexp and filters out rows by ignore_regexp:
@ tracefile_by_mask.sql [mask] – finds and shows last 10 trace files by mask
Continue readingThis document is my attempt to bring together the available options that can be used to determine the root cause of an issue in order to create a roadmap to help support engineers narrow down the cause of concern.
It is a living document and will be edited and amended as time goes by. Please do check back again in the future.
Warning: these parameters should only be used in conjunction with an Oracle Support Engineer and are not intended for DBAs to self-triage; also they should not be left set after triage without discussion with an Oracle Support Engineer.
Continue readingSimple example: tracefiles for the last 5 days:
select fc.*
from v$diag_trace_file f
join v$diag_trace_file_contents fc
on f.adr_home=fc.adr_home
and f.trace_filename=fc.trace_filename
where f.modify_time >= systimestamp - interval'5' minute
and fc.timestamp >= systimestamp - interval'5' minute
and fc.component_name = 'SQL_Trace'
--and fc.section_name like 'kests%'
;
--or:
select tr.*
from v$diag_app_trace_file tf,
v$diag_sql_trace_records tr
where tf.sql_trace = 'Y'
and tf.modify_time > systimestamp - interval'5'minute
and tr.adr_home = tf.adr_home
and tr.trace_filename = tf.trace_filename
and tr.timestamp > systimestamp - interval'5'minute;
I posted a while back on how to use Tracing Hybrid Columnar Compression in an offload server so this is a quick follow up.
So this example should do both:
CellCLI > alter cell offloadGroupEvents = "immediate cellsrv.cellsrv_setparam('my_parameter, 'TRUE')", offloadGroupName = "SYS_122110_160621"
this will, of course, set a parameter temporarily until the next time the offload server is bounced, but also adding it to the offload group’s init.ora will take care of that.
I had noted in my first post that using the highest level of tracing caused timeout issues with the offload server heartbeat monitor. Heartbeat issues can also occur with expensive (and badly formed) regexp expressions. By default the heartbeat monitor is set to 6 seconds which is the maximum permitted to process 1MB data in the offload server and mark the task completed and is far more time than is reasonably expected to take.
Operations such as expensive tracing to disk or badly formed regexp expressions that cause that time period to be exceeded lead to this in the alert log:
State dump signal delivered to CELLOFLSRV<10180> by pid - 9860, uid - 3318
Thu Mar 5 12:26:31 2015 561 msec State dump completed for CELLOFLSRV<10180>
Clean shutdown signal delivered to CELLOFLSRV<10180> by pid - 9860, uid - 3318
CELLOFLSRV <10180> is exiting with code 1
where the restart server bounces the offload server to clear the perceived hang. Increasing the timeout via:
CellCLI> alter cell events = "immediate cellsrv.cellsrv_setparam('_cell_oflsrv_heartbeat_timeout_sec','60')"
enables the tracing to proceed without causing the restart server.
My point in writing this entry was to provide a work-around when tracing is needed but also to address a couple of blog posts I’d seen that recommend leaving it set at 60 or 90 seconds. This is not a good idea. The heartbeat exists to catch genuine but rare issues and leaving this set to an increased value will hinder the offload server restarting quickly to resume work. This is one parameter that shoud be reset to the default when the work-around is no longer needed unless otherwise directed by support.
Roger MacNicol
I posted a while back on how to use Tracing Hybrid Columnar Compression in an offload server so this is a quick follow up.
So this example should do both:
CellCLI > alter cell offloadGroupEvents = "immediate cellsrv.cellsrv_setparam('my_parameter, 'TRUE')", offloadGroupName = "SYS_122110_160621"
this will, of course, set a parameter temporarily until the next time the offload server is bounced, but also adding it to the offload group’s init.ora will take care of that.
I had previously commented on how to use the FPLIB facility in a trace event but the question came up today of how to trace HCC in an offload server. The facility name in this case is ADVCMP (Advanced Compression) and the hierarchy is:
ADVCMP_MAIN
ADVCMP_COMP
ADVCMP_DECOMP
No compression occurs on the cell so we are only interested in the decompression tracing in an offload server.
So in this case the correct syntax is [facility.event] so:
cellcli -e 'alter cell offloadgroupEvents = "trace[advcmp.advcmp_decomp.*] disk=lowest"'
I’ve noticed several people who were familiar with using trace events with cellsrv, were uncertain about how to use tracing the new offload server architecture. So whereas in the past you could have added tracing to SmartScan processing with:
> cellcli -e 'alter cell events = "trace[fplib.sage_data] disk=lowest, memory=lowest"'
the new syntax to turn it on is:
> cellcli -e 'alter cell offloadgroupEvents = "trace[fplib.sage_data] disk=lowest, memory=lowest"'
and to turn it off:
> cellcli -e 'alter cell offloadgroupEvents = "trace[fplib.sage_data] off"'
Do not use anything higher than “disk=medium, memory=medium” since the higher levels of tracing will cause the heartbeat monitor to fail with timeout issues.
The SmartScan trace events available under the fplib (Filter Processing Library) facility are:
fplib.fplib_main, fplib.sage_cache, fplib.sage_txn, fplib.sage_data, fplib.sage_index
In the cell trace directory hierarchy each offload server will have its own trace directories
diag/asm/cell/SYS_122010_150220/trace
diag/asm/cell/SYS_112331_141117/trace
In a future post I will cover how to use named offload groups to isolate the traces for a single session.
Roger MacNicol,
Data Storage Technology Group