oracle trace file analyzer (tfa) - overview guide
TRANSCRIPT
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Oracle Trace File Analyzer (TFA) Overview Guide 12.1.2.8.4
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Safe Harbor Statement
The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle.
2
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Program Agenda
Why TFA?
Overview
Install & Configure
Analyze
Collect
Maintain
1
2
3
4
5
3
6
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Program Agenda
Why TFA?
Overview
Install & Configure
Analyze
Collect
Maintain
1
2
3
4
5
4
6
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Why TFA?
Running the necessary diagnostics & collecting the correct output across multiple cluster nodes is not easy – Data can be overwritten if not collected in time
– If too much data is collected the results can be very large
– Data collected may not include everything necessary to diagnose the problem
– Many different support tools with different syntaxes
– Majority of issue resolution time is spent obtaining the correct diagnostics
– When problems take a long time to resolve they cost your business money
5
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Why TFA?
Provides one interface for all diagnostic needs
Collects data across the cluster and consolidates it in one place
Collects all relevant diagnostic data at the time of the problem
Reduces time required to obtain diagnostic data, which saves your business money
6
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Program Agenda
Why TFA?
Overview
Install & Configure
Analyze
Collect
Maintain
1
2
3
4
5
7
6
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
• Operating Systems supported – Linux (OEL, RedHat, SUSE, Itanium &
zLinux)
– Oracle Solaris (SPARC & x86-64)
– AIX
– HPUX (Itanium & PA-RISC)
• All Oracle Database & Grid versions 10.2+ are supported
• You may already have TFA installed as it is included with: – Oracle Grid Infrastructure:
• 11.2.0.4+
• 12.1.0.2+
• Also available from Doc 1513912.1
8
Supported Platforms and Versions
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Monitoring By TFA & Automated Collections
9
Automatically detect event
Collect & package relevant
diagnostics
Store collection in TFA repository
Upload collection to Oracle Support for further help
Significant problem occurs
1
2
3
4
TFA DBA(s) / Sys Admin(s)
Oracle Grid Infrastructure & Database(s)
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Monitoring By DBA(s) / Sysadmin
10
Problem occurs
Collect & package relevant
diagnostics
Perform analysis using support tools where
required
Request collection using
tfactl diagcollect
Request analysis using tfactl
Upload package to Oracle Support for help resolving
Resolve problem
Need more help
?
Oracle Grid Infrastructure & Database(s)
TFA DBA(s) / Sys Admin(s)
1 2
3
4 5 6
7
No
Yes
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. | 11
• TFA daemon runs on each cluster node
• Or single instance when no Grid Infrastructure is used
• Command line communication is via tfactl command
• TFA Daemons on all nodes coordinate: • Script execution • Collection of diagnostics • Trimming of log contents
• Cluster wide collection output is consolidated on one node
Architecture
TFA Daemon
Remote Node
n
TFA Daemon
tfactl
Initiator Node ( Where command originated)
Cluster
Cluster wide
Collection
Alerts & Log files
Scripts
TFA Daemon
Remote Node
2
Scripts
TFA Daemon
Remote Node
1
Alerts & Log files Scripts
Scripts
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Program Agenda
Why TFA?
Overview
Install & Configure
Analyze
Collect
Maintain
1
2
3
4
5
12
6
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Install as root user
1. Download latest version, transfer to required machine & unzip: Doc 1513912.1
2. Execute:
• Will install on all cluster nodes
• Will auto discover relevant Oracle Software & Exadata Storage Servers
• Will start monitoring all discovered items for significant events & once auto collection is enabled will collect diagnostics when necessary
Install as non root user
1. Download latest version, transfer to required machine & unzip: Doc 1513912.1
2. Execute:
• Will not run a daemon
• Will not perform automatic collections
• Will not collect from remote hosts
• On-demand collections will work, but limited to local files you have read access to
• Use the JRE already available in the RDBMS home unless you have a later version available
13
Installation
./installTFALite ./installTFALite -extractto <dir> -javahome <jre_home>
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Key TFA Directories
• tfa/bin – Contains command line interface tfactl
• tfa/repository – Where collections are stored
• tfa/<node>/tfa_home/database – Contains Berkeley database that stores data about system
• tfa/<node>/tfa_home/diag – Tools for troubleshooting TFA itself
• tfa/<node>/tfa_home/diagnostics_to_collect – Things to be included in next collection then deleted
• tfa/<node>/tfa_home/log – Contains logs about TFA operation
• tfa/<node>/tfa_home/resources - Contains resource files such as log masking control files
• tfa/<node>/tfa_home/output – Contains extra meta data about the environment
14
Depending on how TFA was installed the tfa_home and bin directory may be in a different location, e.g. when installed as part of Grid Infrastructure the tfa_home path will be under grid_home
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
mask_strings.xml • If you want to mask sensitive data – Hostname
– IP address
– Etc
• Create mask_strings.xml – In the directory tfa_home/resources
– Define all data replacements you want
15
Data Masking
<mask_strings>
<mask_string>
<original>WidgetNode1</original>
<replacement>Node1</replacement>
</mask_string>
<mask_string>
<original>192.168.5.1</original>
<replacement>Node1-IP</replacement>
</mask_string>
<mask_string>
<original>WidgetNode2</original>
<replacement>Node2</replacement>
</mask_string>
<mask_string>
<original>192.168.5.2</original>
<replacement>Node2-IP</replacement>
</mask_string>
</mask_strings>
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
• Enable or disable user access
Configure User Access
tfactl
Grid Home owner
Database Home Owners
Everyone Else
Users & groups authorized to access tfactl by default
tfactl access enable
tfactl access disable
tfactl access add –user <user> [-local]
tfactl access remove –user <user> [-local]
tfactl access removeall [-local]
• Add users
• Remove users or everyone
All commands apply cluster wide unless -local used to restrict to local node
• Reset to default access
tfactl access reset
• List user access
tfactl access lsusers
16
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Enable Automatic Collections
17
Automatically detect event
Collect & package relevant
diagnostics
Store collection in TFA repository
Significant problem occurs
1
2
3
TFA DBA(s) / Sys Admin(s)
Oracle Grid Infrastructure & Database(s)
• Enable automatic collections & specify repository size:
• TFA will monitor alert logs for :
tfactl set autodiagcollect=ON reposizeMB=20480
ORA-297(01|02|03|08|09|10|40)
ORA-00600
ORA-07445
ORA-4(69|([7-8][0-9]|9([0-3]|[5-8])))
ORA-32701
ORA-494
CRS-16(07|10|11|12)
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Program Agenda
Why TFA?
Overview
Install & Configure
Analyze
Collect
Maintain
1
2
3
4
5
18
6
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Analyze
19
Problem occurs
Collect & package relevant
diagnostics
Perform analysis using support tools where
required
Request collection using
tfactl diagcollect
Request analysis using tfactl
Upload package to Oracle Support for help resolving
Resolve problem
Need More help
?
Oracle Grid Infrastructure & Database(s)
TFA DBA(s) / Sys Admin(s)
1 2
3
4 5 6
7
No
Yes
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
• Analyze all important recent log entries: • Search recent log entries:
20
Analyze
tfactl analyze –last 1d tfactl analyze -search “ora-006" -last 8h
Searching for “ora-00600”
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
• TFA includes all key database support tools
• tfactl provides a single interface to them all
Analyze
21
Most of these Support tools are only available in the My Oracle Support download, they are not included in the base Grid or Database install
Tool Description Details ORAchk Oracle Stack Health Checks on non-engineered
systems 1268927.2
EXAchk Oracle Stack Health Checks on Engineered Systems
1070954.1
oswatcher Collect and archive OS metrics, useful for instance / node evictions & performance Issues
301137.1
procwatcher Automate & capture database performance diagnostics & session level hangs
459694.1
oratop Near real-time database monitoring 1500864.1 sqlt Capture SQL trace date useful for tuning 215187.1
alertsummary Provides summary of events for one or more database or ASM alert files from all nodes
ls Lists all files TFA knows about for a given file name pattern across all nodes
Tool Description pstack Generate process stack for specified processes across all nodes
grep Search alert or trace files with a given database and file name
pattern, for a search string. summary High level summary of the configuration
vi Open alert or trace files for viewing a given database and file name pattern in the vi editor
tail Run a tail on an alert or trace files for a given database and file name pattern
param Show all database and OS parameters that match a specified pattern
dbglevel Set and unset multiple CRS trace levels with one command history Show the shell history for the tfactl shell
changes Report any noted changes in the system setup over a given time period. This includes database a parameters, OS parameters, patches applied etc
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. | 22
Oracle Stack Health Checks
• Oracle EXAchk for Engineered Systems
• Oracle ORAchk for everything else • Proactively scans for the most impactful problems across the layers of your stack
• Simplifies and streamlines how to investigate and analyze which known issues present a risk to you
• Lightweight tool that runs within your environment without requiring config data to be sent to Oracle
• High level reports show your system health risks with the ability to drill down into specific problems and understand their resolutions
• Can be configured to send email notifications when it detects problems
• Collection Manager, a companion Application Express web app, provides a single dashboard view of collections across your entire enterprise
ORAchk & EXAchk (Support Tools Bundle)
Most of the Support Tools bundle is not installed with the Database install and PSUs, however ORAchk is
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. | 23
OS Watcher (Support Tools Bundle)
Collect & Archive OS Metrics • Executes standard UNIX utilities (e.g. vmstat, iostat, ps,
etc) on regular intervals
• Built in Analyzer functionality to summarize, graph and report upon collected metrics
• Output is Required for node reboot and performance issues
• Simple to install, extremely lightweight
• Runs on ALL platforms (Except Windows)
• MOS Note: 301137.1 – OS Watcher Users Guide
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. | 24
Procwatcher (Support Tools Bundle)
Monitor & Examine Database Processes
• Single instance & RAC
• Generates session wait, lock and latch reports as well as call stacks from any problem process(s)
• Ability to collect stack traces of specific processes using Oracle Tools and OS Debuggers
• Typically reduces SR resolution for performance related issues
• Runs on ALL major UNIX Platforms
• MOS Note: 459694.1 – Procwatcher Install Guide
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. | 25
oratop (Support Tools Bundle)
Near Real-Time Database Monitoring
• Single instance & RAC
• Monitoring current database activities
• Database performance
• Identifying contentions and bottleneck
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Analyze
• Each tool can be run using tfactl in shell mode
• Start tfactl shell with
• Run a tool with the tool name
1. Where necessary set context with database <dbname>
2. Then run tool
3. Clear context with database
26
tfactl
tfactl > database MyDB
MyDB tfactl > database
tfactl > orachk
MyDB tfactl > oratop
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Program Agenda
Why TFA?
Overview
Install & Configure
Analyze
Collect
Maintain
1
2
3
4
5
27
6
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Collect
28
Problem occurs
Collect & package relevant
diagnostics
Perform analysis using support tools where
required
Request collection using tfactl diagcollect
Request analysis using tfactl
Upload package to Oracle Support for help resolving
Resolve problem
Need more help
?
Oracle Grid Infrastructure & Database(s)
TFA DBA(s) / Sys Admin(s)
1 2
3
4 5 6
7
No
Yes
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
• Trim & collect all important log files updated in the past 4 hours:
• Collections stored in the repository directory
• Change diagcollect timeframe with –last <n>h|d
29
Collect
tfactl diagcollect
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
One Command SRDCs
• For certain types of problems Oracle Support will ask you to run a Service Request Data Collection (SRDC)
• Previously this would have involved: – Reading many different support documents
– Collecting output from many different tasks
– Gathering lots of different diagnostics
– Packaging & uploading
• TFA can now run SRDC collections for you with a single command:
tfactl diagcollect -srdc <srdc_type>
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
SRDCs for ORA errors:
• ora600
• ora7445
• ora700
• ora4031
• ora4030
• ora27300
• ora27301
• ora27302
SRDC for database performance problems:
• dbperf
31
SRDC for all other types of internal database errors:
• internalerror
One Command SRDCs
And more coming soon…
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
ORA4031:
1. IPS Package
2. Patch Listing
3. AWR report
4. Memory information
5. RDA
Database Performance
1. ADDM report
2. AWR for good and problem period
3. AWR Compare Period report
4. ASH report for good and problem period
5. OS Watcher
6. IPS Package (if errors during problem period)
7. ORAchk (performance related checks)
One Command SRDCs – Examples of What’s Collected
tfactl diagcollect –srdc ora4031 tfactl diagcollect –srdc dbperf
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Manual Data Gathering vs One Command SRDC
Manual Data Gathering
1. Generate ADDM reviewing Document 1680075.1
2. Identify “good” and “problem” periods and gather AWR reviewing Document 1903158.1
3. Generate AWR compare report (awrddrpt.sql) using “good” and “problem” periods
4. Generate ASH report for “good” and “problem” periods reviewing Document 1903145.1
5. Collect OSWatcher data reviewing Document 301137.1
6. Check alert.log if there are any errors during the “problem” period
7. Find any trace files generated during the “problem” period
8. Collate and upload all the above files/outputs to SR
TFA SRDC
1. Run
2. Upload resulting zip file to SR
tfactl diagcollect –srdc dbperf
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
1. Enter default for event date/time and database name
2. Scans system to identify recent 10 events in the system (ORA600 example shown)
3. Once the relevant event is chosen, proceeds with diagnostic collection
34
One Command SRDC
tfactl diagcollect –srdc <srdc_type>
4. All required files are identified
5. Trimmed where applicable
6. Package in a zip ready to provide to support
Interactive Mode
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
One Command SRDC
35
tfactl diagcollect –srdc <srdc_type> -database <db> -for <time>
1. Parameters(date/time, DB name) are provided in the command
2. Does not prompt for any more information
3. All required files are identified
4. Trimmed where applicable
5. Package in a zip ready to provide to support
Silent Mode
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
One Command SRDCs
• Full command syntax:
• Uses all standard diagcollect options where relevant to the SRDC
36
tfactl diagcollect -srdc <srdc_type> [-tag <description>] [-z <filename>]
[-last <n><h|d>| -from <time> -to <time> | -for <time> ] [–database <database>]
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Program Agenda
Why TFA?
Overview
Install & Configure
Analyze
Collect
Maintain
1
2
3
4
5
37
6
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
• Option 1 – Applying standard PSUs will
automatically update TFA
– PSUs do not contain Support Tools Bundle updates
• Option 2 – To update with latest TFA & Support
Tools Bundle
1. Download latest version: Doc 1513912.1
2. Transfer zip to required machine
3. Unzip
4. Either:
a) execute installTFALite as root user
– TFA will find and update the existing installation
b) execute installTFALite -extractto as non root
38
Maintain
Upgrade to the latest version whenever possible to include bug fixes, new features & optimizations
./installTFALite
./installTFALite -extractto <dir> -javahome <jre_home>
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Safe Harbor Statement
The preceding is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle.
39