(Free) Tools for identifying and diagnosing database (and not-the-database) issues

"Every problem is a database problem until the DBA proves otherwise." Plato, probably. Whether conducting a proactive review of a database or firefighting an outage or performance issue, most of my work as a consultant revolves around a handful of free, open-source and Oracle-provided utilities. In this session, I'll share these tools and show how I use them to find and solve database (and non-database) problems. The first part of the session focuses on reactive tools that will help you quickly isolate, contain, and extinguish fires. In the second, we'll dive into proactive and diagnostic solutions, demonstrating how to use them to catch and repair problematic configurations before they cause problems!

Transcript

1. Utah Oracle User Group Training Days 2024 (Free) Tools for identifying and diagnosing database (and not-the-database) issues Sean Scott Oracle ACE Director Managing Principal Consultant @oraclesean linkedin.com/in/soscott/ sean.scott@viscosityna.com @ViscosityNA www.viscosityna.com
2. DATABASE RELIABILITY ENGINEERING ⁘ DEVOPS & AUTOMATION HIGH AVAILABILITY ⁘ BUSINESS CONTINUITY ⁘ DISASTER RECOVERY MODERNIZATION ⁘ OBSERVABILITY ⁘ ENGINEERED SYSTEMS AutoUpgrade ⁘ Zero Downtime Migrations ⁘ Patching Real Application Clusters ⁘ Data Guard ⁘ Sharding Docker/Containers ⁘ Terraform ⁘ Ansible Exadata ⁘ Oracle Database Appliance AHF ⁘ TFA ⁘ GIMR ⁘ CHA Sean Scott Oracle ACE Director Managing Principal Consultant @oraclesean linkedin.com/in/soscott/ sean.scott@viscosityna.com @ViscosityNA www.viscosityna.com
3. 3 membership tiers Connect: @oracleace Facebook.com/OracleACEs aceprogram_ww@oracle.com 400+ technical experts helping peers globally The Oracle ACE Program recognizes and rewards community members for their technical and community contributions to the Oracle community Nominate yourself or someone you know: ace.oracle.com/nominate For more details on Oracle ACE Program: ace.oracle.com
4. @ViscosityNA www.viscosityna.com Oracle on Docker Running Oracle Databases in Linux Containers Download a free sample chapter: https:/ /oraclesean.com
8. @ViscosityNA www.viscosityna.com Autonomous Health Framework • Download from MOS Note 2550798.1 • AHF diagnostic collections are required by MOS for some SR • Cluster-aware ADR log inspection and management • Consumes Database, Grid Infrastructure, ASM logs • Incident management & alerting • Connects to MOS, OEM • SMTP, REST APIs
9. @ViscosityNA www.viscosityna.com AHF Diagnostic Collections diagcollect [ [component1] [component2] ... [componenteN] | [-srdc <srdc_profile>] | [-defips] ] [-sr <SR#>] [-node <all|local|n1,n2,..>] [-tag <tagname>] [-z <filename>] [-acrlevel <system,database,userdata>] [-last <n><m|h|d> | -from <time> -to <time> | -for <time>] [-nocopy] [-notrim] [-silent] [-cores] [-collectalldirs] [-collectdir <dir1,dir2..>] [-collectfiles <file1,..,fileN,dir1,..,dirN> [-onlycollectfiles] ]
10. @ViscosityNA www.viscosityna.com AHF Diagnostic Collections - components diagcollect [ [component1] [component2] ... [componenteN] | [-srdc <srdc_profile>] | [-defips] ] [-sr <SR#>] [-node <all|local|n1,n2,..>] [-tag <tagname>] [-z <filename>] [-acrlevel <system,database,userdata>] [-last <n><m|h|d> | -from <time> -to <time> | -for <time>] [-nocopy] [-notrim] [-silent] [-cores] [-collectalldirs] [-collectdir <dir1,dir2..>] [-collectfiles <file1,..,fileN,dir1,..,dirN> [-onlycollectfiles] ] diagcollect [component1] [component2] ... [componenteN] -acfs -afd -ahf -ashhtml -ashtext -asm -awrhtml -awrtext -cfgtools -cha -crs -crsclient -cvu -database -dataguard -dbclient -dbwlm -em -emagent -emagenti -emplugins -install -ips -ocm -oms -omsi -os -procinfo -qos -rhp -sosreport -tns -wls
11. @ViscosityNA www.viscosityna.com AHF Diagnostic Collections - SRDC profiles diagcollect ... -srdc <srdc_profile> diagcollect -srdc -help <srdc_profile> can be any of the following, DBCORRUPT Required Diagnostic Data Collection for a Generic Database Corruption DBDATAGUARD Required Diagnostic Data Collection for Data Guard issues including Broker Listener_Services SRDC - Data Collection for TNS-12516 / TNS-12518 / TNS-12519 / TNS-12520. Naming_Services SRDC - Data Collection for ORA-12154 / ORA-12514 / ORA-12528. ORA-00020 SRDC for database ORA-00020 Maximum number of processes exceeded ORA-00060 SRDC for ORA-00060. Internal error code. ORA-00494 SRDC for ORA-00494. ORA-00600 SRDC for ORA-00600. Internal error code. ... ora4023 SRDC - ORA-4023 : Checklist of Evidence to Supply ora4063 SRDC - ORA-4063 : Checklist of Evidence to Supply ora445 SRDC - ORA-445 or Unable to Spawn Process: Checklist of Evidence to Supply xdb600 SRDC - Required Diagnostic Data Collection for XDB ORA-00600 and ORA-07445 zlgeneric SRDC - Zero Data Loss Recovery Appliance (ZDLRA) Data Collection.
12. @ViscosityNA www.viscosityna.com AHF Diagnostic Collections - MOS upload, etc. diagcollect ... -defips -sr <SR#> -node <all|local|n1,n2,..> -defips Include in the default collection the IPS Packages for: ASM, CRS and Databases -sr Enter SR number to which the collection will be uploaded -node Specify comma separated list of host names for collection
13. @ViscosityNA www.viscosityna.com AHF Diagnostic Collections - Time ranges diagcollect ... -last <n><m|h|d> -since -from <time> -to <time> -for <time> -last <n><m|h|d> Files from last 'n' [m]inutes, 'n' [d]ays or 'n' [h]ours -since Same as -last. Kept for backward compatibility. -from "Mon/dd/yyyy hh:mm:ss" From <time> or "yyyy-mm-dd hh:mm:ss" or "yyyy-mm-ddThh:mm:ss" or "yyyy-mm-dd" -to "Mon/dd/yyyy hh:mm:ss" To <time> or "yyyy-mm-dd hh:mm:ss" or "yyyy-mm-ddThh:mm:ss" or "yyyy-mm-dd" -for "Mon/dd/yyyy" For <date> or "yyyy-mm-dd"
14. @ViscosityNA www.viscosityna.com AHF Diagnostic Collections - File management diagcollect ... -nocopy -notrim -tag <tagname> -z <zipname> -collectalldirs -collectdir <dir1,dir2..> -collectfiles <file1,..,fileN,dir1,..,dirN> [-onlycollectfiles] -nocopy Does not copy back the zip files to initiating node from all nodes -notrim Does not trim the files collected -tag <tagname> The files will be collected into tagname directory inside the repository -z <zipname> The collection zip file will be given this name in the collection repo -collectalldirs Collect all files from a directory marked "Collect All” flag to true -collectdir Specify a comma separated list of directories and the collection will include all files from these irrespective of type and time constraints in addition to the components specified -collectfiles Specify a comma separated list of files/directories and the collection will include the files and directories in addition to the components specified. if -onlycollectfiles is also used, then no other components will be collected.
15. @ViscosityNA www.viscosityna.com AHF Diagnostic Collections - File redaction diagcollect ... -mask | -sanitize tfactl set redact=mask tfactl set redact=sanitize tfactl set redact=none sanitize: Replaces sensitive data in collections with random characters mask: Replaces sensitive data in collections with asterisks (*)
16. @ViscosityNA www.viscosityna.com AHF Diagnostic Collections - Help/Examples tfactl diagcollect Trim and Zip all files updated in the last 1 hours as well as chmos/osw data from across the cluster and collect at the initiating node Note: This collection could be larger than required but is there as the simplest way to capture diagnostics if an issue has recently occurred. tfactl diagcollect -last 8h Trim and Zip all files updated in the last 8 hours as well as chmos/osw data from across the cluster and collect at the initiating node tfactl diagcollect -database hrdb,fdb -last 1d -z foo Trim and Zip all files from databases hrdb & fdb in the last 1 day and collect at the initiating node tfactl diagcollect -crs -os -node node1,node2 -last 6h Trim and Zip all crs files, o/s logs and chmos/osw data from node1 & node2 updated in the last 6 hours and collect at the initiating node
17. @ViscosityNA www.viscosityna.com AHF Diagnostic Collections - Help/Examples tfactl diagcollect -asm -node node1 -from "Mar/15/2022" -to "Mar/15/2022 21:00:00" Trim and Zip all ASM logs from node1 updated between from and to time and collect at the initiating node tfactl diagcollect -for "Mar/15/2022" Trim and Zip all log files updated on "Mar/15/2022" and collect at the collect at the initiating node tfactl diagcollect -for "Mar/15/2022 21:00:00" Trim and Zip all log files updated from 09:00 on "Mar/15/2022" to 09:00 on “Mar/16/2022"(i.e. 12 hours before and after the time given) and collect at the initiating node tfactl diagcollect -crs -collectdir /tmp_dir1,/tmp_dir2 Trim and Zip all crs files updated in the last 1 hours Also collect all files from /tmp_dir1 and /tmp_dir2 at the initiating node
19. @ViscosityNA www.viscosityna.com AHF - changes # Find changes made on the system tfactl changes # Times and ranges -for "YYYY-MM-DD" -from "YYYY-MM-DD" -to "YYYY-MM-DD" -from "YYYY-MM-DD HH24:MI:SS" -to "YYYY-MM-DD HH24:MI:SS" -last 6h -last 1d
20. @ViscosityNA www.viscosityna.com AHF - changes [root@node1 ~]# tfactl changes -last 2d Output from host : node2 ------------------------------ [Feb/02/2022 20:11:16.438]: Package: cvuqdisk-1.0.10-1.x86_64 Output from host : node1 ------------------------------ [Feb/02/2022 19:57:16.438]: Package: cvuqdisk-1.0.10-1.x86_64 [Feb/02/2022 20:11:16.438]: Package: cvuqdisk-1.0.10-1.x86_64
21. @ViscosityNA www.viscosityna.com AHF - events [root@node1 ~]# tfactl events -last 1d Output from host : node2 ------------------------------ Event Summary: INFO :3 ERROR :2 WARNING :0 Event Timeline: [Feb/02/2022 20:10:46.649 GMT]: [crs]: 2022-02-02 20:10:46.649 [ORAROOTAGENT(27881)]CRS-5822: Agent '/u01/app/19.3.0.0/grid/ bin/orarootagent_root' disconnected from server. Details at (:CRSAGF00117:) {0:1:3} in /u01/app/grid/diag/crs/node2/crs/trace/ ohasd_orarootagent_root.trc. [Feb/02/2022 20:11:12.856 GMT]: [crs]: 2022-02-02 20:11:12.856 [OCSSD(28472)]CRS-1601: CSSD Reconfiguration complete. Active nodes are node1 node2 . [Feb/02/2022 20:11:57.000 GMT]: [asm.+ASM2]: Reconfiguration started (old inc 0, new inc 4) [Feb/02/2022 20:28:31.000 GMT]: [db.db193h1.DB193H12]: Starting ORACLE instance (normal) (OS id: 24897) [Feb/02/2022 20:28:42.000 GMT]: [db.db193h1.DB193H12]: Reconfiguration started (old inc 0, new inc 4)
22. @ViscosityNA www.viscosityna.com AHF - grep # Find patterns in multiple files tfactl grep "ERROR" alert tfactl grep -i "error" alert,trace [root@node1 ~]# tfactl grep -i "error" alert Output from host : node1 ------------------------------ Searching 'error' in alert Searching /u01/app/grid/diag/asm/+asm/+ASM1/trace/alert_+ASM1.log +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+- 28: PAGESIZE AVAILABLE_PAGES EXPECTED_PAGES ALLOCATED_PAGES ERROR(s) 375:Errors in file /u01/app/grid/diag/asm/+asm/+ASM1/trace/+ASM1_ora_32035.trc: 378:Errors in file /u01/app/grid/diag/asm/+asm/+ASM1/trace/+ASM1_ora_32049.trc: 446:ERROR: /* ASMCMD */ALTER DISKGROUP ALL MOUNT 543: PAGESIZE AVAILABLE_PAGES EXPECTED_PAGES ALLOCATED_PAGES ERROR(s) 1034:Errors in file /u01/app/grid/diag/asm/+asm/+ASM1/trace/+ASM1_ora_28105.trc: ...
23. @ViscosityNA www.viscosityna.com AHF - tail # Tail logs by name or pattern tfactl tail alert_ # Tail all logs matching alert_ tfactl tail alert_ORCL1.log -exact # Tail for an exact match tfactl tail -f alert_ # Follow logs(local node only) [root@node1 ~]# tfactl tail -f alert_ Output from host : node1 ------------------------------ ==> /u01/app/grid/diag/asm/+asm/+ASM1/trace/alert_+ASM1.log <== NOTE: cleaning up empty system-created directory '+DATA/vgtol7-rac-c/OCRBACKUP/backup00.ocr.274.1095654191' 2022-02-03T12:23:35.194335+00:00 NOTE: cleaning up empty system-created directory '+DATA/vgtol7-rac-c/OCRBACKUP/backup01.ocr.274.1095654191' 2022-02-03T16:23:43.602629+00:00 NOTE: cleaning up empty system-created directory '+DATA/vgtol7-rac-c/OCRBACKUP/backup01.ocr.275.1095668599' ==> /u01/app/oracle/diag/rdbms/db193h1/DB193H11/trace/alert_DB193H11.log <== TABLE SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY: ADDED INTERVAL PARTITION SYS_P301 (44594) VALUES LESS THAN (TO_DATE(‘... SYS.WRI$_OPTSTAT_HISTGRM_HISTORY: ADDED INTERVAL PARTITION SYS_P304 (44594) VALUES LESS THAN (TO_DATE(‘... 2022-02-03T06:00:16.143988+00:00 Thread 1 advanced to log sequence 22 (LGWR switch) Current log# 2 seq# 22 mem# 0: +DATA/DB193H1/ONLINELOG/group_2.265.1095625353
24. @ViscosityNA www.viscosityna.com AHF - ps # List processes - default flags are "-ef" ps pmon ps <flags> pmon tfactl> ps pmon Output from host : vna1 ------------------------------ grid 15260 1 0 14:30 ? 00:00:00 asm_pmon_+ASM1 oracle 16883 1 0 14:31 ? 00:00:00 ora_pmon_VNA1 Output from host : vna2 ------------------------------ grid 8063 1 0 14:25 ? 00:00:00 asm_pmon_+ASM2 oracle 9929 1 0 14:27 ? 00:00:00 ora_pmon_VNA2...
25. @ViscosityNA www.viscosityna.com AHF - ps tfactl> ps aux pmon Output from host : vna1 ------------------------------ grid 15260 0.0 1.0 1556860 79508 ? Ss 14:30 0:00 asm_pmon_+ASM1 oracle 16883 0.0 0.8 2297012 66148 ? Ss 14:31 0:00 ora_pmon_VNA1 Output from host : vna2 ------------------------------ grid 8063 0.0 1.0 1556860 79896 ? Ss 14:25 0:00 asm_pmon_+ASM2 oracle 9929 0.0 0.8 2297012 66168 ? Ss 14:27 0:00 ora_pmon_VNA2
26. @ViscosityNA www.viscosityna.com AHF - alertsummary # Summarize events in database and ASM alert logs tfactl alertsummary [root@node1 ~]# tfactl alertsummary Output from host : node1 ------------------------------ Reading /u01/app/grid/diag/asm/+asm/+ASM1/trace/alert_+ASM1.log +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+- ------------------------------------------------------------------------ 02 02 2022 20:04:57 Database started ------------------------------------------------------------------------ 02 02 2022 20:07:41 Database started Summary: Ora-600=0, Ora-7445=0, Ora-700=0 ~~~~~~~ Warning: Only FATAL errors reported Warning: These errors were seen and NOT reported Ora-15173 Ora-15032 Ora-15017 Ora-15013 Ora-15326
27. @ViscosityNA www.viscosityna.com AHF - pstack # Print a stack trace for a process .------------------------------------------------------------------. | TOOLS STATUS - HOST : vna1 | +----------------------+--------------+--------------+-------------+ | Tool Type | Tool | Version | Status | +----------------------+--------------+--------------+-------------+ | AHF Utilities | alertsummary | 21.4.1 | DEPLOYED | | | calog | 21.4.1 | DEPLOYED | | | dbglevel | 21.4.1 | DEPLOYED | | | grep | 21.4.1 | DEPLOYED | | | history | 21.4.1 | DEPLOYED | | | ls | 21.4.1 | DEPLOYED | | | managelogs | 21.4.1 | DEPLOYED | | | menu | 21.4.1 | DEPLOYED | | | orachk | 21.4.1 | DEPLOYED | | | param | 21.4.1 | DEPLOYED | | | ps | 21.4.1 | DEPLOYED | | | pstack | 21.4.1 | DEPLOYED | | | summary | 21.4.1 | DEPLOYED | | | tail | 21.4.1 | DEPLOYED | | | triage | 21.4.1 | DEPLOYED | | | vi | 21.4.1 | DEPLOYED | +----------------------+--------------+--------------+-------------+
28. @ViscosityNA www.viscosityna.com AHF - pstack tfactl> pstack -h Output from host : vna1 ------------------------------ Error: pstack command not found in system. If its installed, please set the PATH and try again. yum install -y gdb tfactl> pstack mmon Output from host : vna1 ------------------------------ # pstack output for pid : 15318 #0 0x00007f33bac6928a in semtimedop () from /lib64/libc.so.6 #1 0x0000000011c58285 in sskgpwwait () #2 0x0000000011c543db in skgpwwait () #3 0x000000001144ccba in ksliwat () #4 0x000000001144c06c in kslwaitctx () #5 0x0000000011a6fd40 in ksarcv () #6 0x00000000038174fa in ksbabs () #7 0x0000000003835ab3 in ksbrdp () #8 0x0000000003c19a4d in opirip () #9 0x00000000024c23e5 in opidrv ()
29. @ViscosityNA www.viscosityna.com AHF - pstack # ahfctl pstack accepts standard flags Usage : /opt/oracle.ahf/tfa/bin/tfactl.pl [run] pstack <pid|process name> [-n <n>] [-s <secs>] Print stack trace of a running process <n> times. Sleep <secs> seconds between runs. e.g: /opt/oracle.ahf/tfa/bin/tfactl.pl pstack lmd /opt/oracle.ahf/tfa/bin/tfactl.pl pstack 2345 -n 5 -s 5 /opt/oracle.ahf/tfa/bin/tfactl.pl run pstack lmd /opt/oracle.ahf/tfa/bin/tfactl.pl run pstack 2345 -n 5 -s 5
30. @ViscosityNA www.viscosityna.com AHF - summary # Generate a system summary tfactl> summary -h --------------------------------------------------------------------------------- Usage : TFACTL [run] summary -help --------------------------------------------------------------------------------- Command : /opt/oracle.ahf/tfa/bin/tfactl [run] summary [OPTIONS] Following Options are supported: [no_components] : [Default] Complete Summary Collection -overview : [Optional/Default] Complete Summary Collection - Overview -crs : [Optional/Default] CRS Status Summary -asm : [Optional/Default] ASM Status Summary -acfs : [Optional/Default] ACFS Status Summary -database : [Optional/Default] DATABASE Status Summary -exadata : [Optional/Default] EXADATA Status Summary Not enabled/ignored in Windows and Non-Exadata machine -patch : [Optional/Default] Patch Details -listener : [Optional/Default] LISTENER Status Summary -network : [Optional/Default] NETWORK Status Summary -os : [Optional/Default] OS Status Summary -tfa : [Optional/Default] TFA Status Summary -summary : [Optional/Default] Summary Tool Metadata -json : [Optional] - Prepare json report -html : [Optional] - Prepare html report -print : [Optional] - Display [html or json] Report at Console -silent : [Optional] - Interactive console by defauly -history <num> : [Optional] - View Previous <numberof> Summary Collection History in Interpreter -node <node(s)> : [Optional] - local or Comma Separated Node Name(s) -help : Usage/Help. ---------------------------------------------------------------------------------
31. @ViscosityNA www.viscosityna.com AHF - summary tfactl> summary Executing Summary in Parallel on Following Nodes: Node : vna1 Node : vna2 LOGFILE LOCATION : /opt/oracle.ahf/…/log/summary_command_20220316151853_vna1_18097.log Component Specific Summary collection : - Collecting CRS details ... Done. - Collecting ASM details ... Done. - Collecting ACFS details ... Done. - Collecting DATABASE details ... Done. - Collecting PATCH details ... Done. - Collecting LISTENER details ... Done. - Collecting NETWORK details ... Done. - Collecting OS details ... Done. - Collecting TFA details ... Done. - Collecting SUMMARY details ... Done. Remote Summary Data Collection : In-Progress - Please wait ... - Data Collection From Node - vna2 .. Done. Prepare Clusterwide Summary Overview ... Done cluster_status_summary
32. @ViscosityNA www.viscosityna.com AHF - summary COMPONENT DETAILS STATUS +-----------+---------------------------------------------------------------------------------------------------+---------+ CRS .-----------------------------------------------. PROBLEM | CRS_SERVER_STATUS : ONLINE | | CRS_STATE : ONLINE | | CRS_INTEGRITY_CHECK : FAIL | | CRS_RESOURCE_STATUS : OFFLINE Resources Found | '-----------------------------------------------' ASM .-----------------------------. PROBLEM | ASM_DISK_SIZE_STATUS : OK | | ASM_BLOCK_STATUS : PASS | | ASM_CHAIN_STATUS : PASS | | ASM_INCIDENTS : FAIL | | ASM_PROBLEMS : FAIL | '-----------------------------' ACFS .-----------------------. OFFLINE | ACFS_STATUS : OFFLINE | ‘-----------------------' DATABASE .-----------------------------------------------------------------------------------------------. PROBLEM | ORACLE_HOME_NAME | ORACLE_HOME_DETAILS | +------------------+----------------------------------------------------------------------------+ | OraDB19Home1 | .------------------------------------------------------------------------. | | | | INCIDENTS | DB_BLOCKS | DATABASE_NAME | DB_CHAINS | PROBLEMS | STATUS | | | | +-----------+-----------+---------------+-----------+----------+---------+ | | | | PROBLEM | PASS | VNA | PROBLEM | PROBLEM | PROBLEM | | | | '-----------+-----------+---------------+-----------+----------+---------' | '------------------+----------------------------------------------------------------------------'
33. @ViscosityNA www.viscosityna.com AHF - summary COMPONENT DETAILS STATUS +-----------+---------------------------------------------------------------------------------------------------+---------+ ... PATCH .----------------------------------------------. OK | CRS_PATCH_CONSISTENCY_ACROSS_NODES : OK | | DATABASE_PATCH_CONSISTENCY_ACROSS_NODES : OK | '----------------------------------------------' LISTENER .-----------------------. OK | LISTNER_STATUS : OK | '-----------------------' NETWORK .---------------------------. OK | CLUSTER_NETWORK_STATUS : | '---------------------------' OS .-----------------------. OK | MEM_USAGE_STATUS : OK | '-----------------------' TFA .----------------------. OK | TFA_STATUS : RUNNING | '----------------------' SUMMARY .------------------------------------. OK | SUMMARY_EXECUTION_TIME : 0H:1M:52S | ‘------------------------------------' +-----------+---------------------------------------------------------------------------------------------------+---------+
34. @ViscosityNA www.viscosityna.com AHF - summary interactive menu ### Entering in to SUMMARY Command-Line Interface ### tfactl_summary>list Components : Select Component - select [component_number|component_name] 1 => overview 2 => crs_overview 3 => asm_overview 4 => acfs_overview 5 => database_overview 6 => patch_overview 7 => listener_overview 8 => network_overview 9 => os_overview 10 => tfa_overview 11 => summary_overview tfactl_summary>
35. @ViscosityNA www.viscosityna.com AHF - summary interactive menu tfactl_summary>5 ORACLE_HOME_DETAILS ORACLE_HOME_NAME +-----------------------------------------------------------------------------------+------------------+ .-------------------------------------------------------------------------------. OraDB19Home1 | DATABASE_DETAILS | DATABASE_NAME | +---------------------------------------------------------------+---------------+ | .-----------------------------------------------------------. | VNA | | | DB_BLOCKS | STATUS | DB_CHAINS | INSTANCE_NAME | HOSTNAME | | | | +-----------+--------+-----------+---------------+----------+ | | | | PASS | OPEN | FAIL | VNA1 | vna1 | | | | | PASS | OPEN | FAIL | VNA2 | vna2 | | | | '-----------+--------+-----------+---------------+----------' | | '---------------------------------------------------------------+---------------' +-----------------------------------------------------------------------------------+------------------+ tfactl_summary_databaseoverview>list Status Type: Select Status Type - select [status_type_number|status_type_name] 1 => database_clusterwide_status 2 => database_vna1 3 => database_vna2
36. @ViscosityNA www.viscosityna.com AHF - summary interactive menu tfactl_summary_databaseoverview>list Status Type: Select Status Type - select [status_type_number|status_type_name] 1 => database_clusterwide_status 2 => database_vna1 3 => database_vna2 tfactl_summary_databaseoverview>2 =====> database_sql_statistics =====> database_instance_details =====> database_components_version =====> database_system_events =====> database_hanganalyze =====> database_rman_stats =====> database_incidents =====> database_account_status =====> database_tablespace_details =====> database_status_summary =====> database_sqlmon_statistics =====> database_problems =====> database_statistics =====> database_group_details =====> database_pdb_stats =====> database_configuration_details
37. @ViscosityNA www.viscosityna.com AHF - analyze # Perform system analysis of DB, ASM, GI, system, OS Watcher logs/output tfactl analyze # Options: -comp [db|asm|crs|acfs|oratop|os|osw|oswslabinfo] # default=all -type [error|warning|generic] # default=error -node [all|local|nodename] # default=all -o filename # Output to filename # Times and ranges -for "YYYY-MM-DD" -from "YYYY-MM-DD" -to "YYYY-MM-DD" -from "YYYY-MM-DD HH24:MI:SS" -to "YYYY-MM-DD HH24:MI:SS" -last 6h -last 1d
38. @ViscosityNA www.viscosityna.com AHF - analyze # Perform system analysis of DB, ASM, GI, system, OS Watcher logs/output tfactl analyze # Options: -search "pattern" # Search in DB and CRS alert logs # Sets the search period to -last 1h # Override with -last xh|xd -verbose timeline file1 file2 # Shows timeline for specified files
39. @ViscosityNA www.viscosityna.com AHF - analyze INFO: analyzing all (Alert and Unix System Logs) logs for the last 1440 minutes... Please wait... INFO: analyzing host: node1 Report title: Analysis of Alert,System Logs Report date range: last ~1 day(s) Report (default) time zone: GMT - Greenwich Mean Time Analysis started at: 03-Feb-2022 06:27:46 PM GMT Elapsed analysis time: 0 second(s). Configuration file: /opt/oracle.ahf/tfa/ext/tnt/conf/tnt.prop Configuration group: all Total message count: 963, from 02-Feb-2022 08:01:39 PM GMT to 03-Feb-2022 04:23:43 PM GMT Messages matching last ~1 day(s): 963, from 02-Feb-2022 08:01:39 PM GMT to 03-Feb-2022 04:23:43 PM GMT last ~1 day(s) error count: 4, from 02-Feb-2022 08:03:31 PM GMT to 02-Feb-2022 08:11:12 PM GMT last ~1 day(s) ignored error count: 0 last ~1 day(s) unique error count: 3 Message types for last ~1 day(s) Occurrences percent server name type ----------- ------- -------------------- ----- 952 98.9% node1 generic 7 0.7% node1 WARNING 4 0.4% node1 ERROR ----------- ------- 963 100.0%
40. @ViscosityNA www.viscosityna.com AHF - analyze ... Unique error messages for last ~1 day(s) Occurrences percent server name error ----------- ------- ----------- ----- 2 50.0% node1 [OCSSD(30863)]CRS-1601: CSSD Reconfiguration complete. Active nodes are node1 . 1 25.0% node1 [OCSSD(2654)]CRS-1601: CSSD Reconfiguration complete. Active nodes are node1 node2 . 1 25.0% node1 [OCSSD(2654)]CRS-1601: CSSD Reconfiguration complete. Active nodes are node1 . ----------- ------- 4 100.0%
42. @ViscosityNA www.viscosityna.com AWR Comparisons define report_type = 'html'; define num_days = 0; define num_days2 = 0; define inst_num = 1; define inst_num2 = 1; define inst_name = ORCL; define db_name = ORCL; define dbid = 1234567890; define dbid2 = 1234567890; define begin_snap = 101; define end_snap = 102; define begin_snap2 = 201; define end_snap2 = 202; define report_name = my_awr_report @@?/rdbms/admin/awrddrpi
43. @ViscosityNA www.viscosityna.com AWR Comparisons
44. @ViscosityNA www.viscosityna.com AWR Comparisons
45. @ViscosityNA www.viscosityna.com AWR Comparisons
46. @ViscosityNA www.viscosityna.com AWR Comparisons
48. @ViscosityNA www.viscosityna.com Cluster Health Monitor • Collects OS statistics in real-time (usually 1/sec) • Useful for troubleshooting node hang/reboot/eviction • Differences between CHM and OSWatcher: • OSWatcher runs OS commands (vmstat, top, ps, meminfo, etc.) • OSWatcher runs w/user priority (won't run during high CPU load) • CHM collects data even when OSWatcher can't • CHM doesn't run top, traceroute, netstat • CHM and OSWatcher are complementary; however, CHM is preferred • FAQ: MOS Note 1328466.1
50. @ViscosityNA www.viscosityna.com Cluster Health Advisor • Analysis and visualization of Cluster Health Monitor (CHM) data • Reads CHM data directly from memory • Reads ASH data directly from SMR (no DB connection) • ~150 signals/second/target; smooths output to 5-second intervals
51. @ViscosityNA www.viscosityna.com Cluster Health Advisor - Modeling • Compares OS & DB activity against models • 30+ node & database problem models • 150+ OS & DB metric predictors • Interconnect, Global Cache, Cluster • Host CPU & memory • PGA memory stress • I/O & storage performance • Workload & session variations
52. @ViscosityNA www.viscosityna.com Cluster Health Advisor - "Normality Model" • Models continuously adjusted by target activity • Normality Models consider load similarity—not thresholds • Time, Day of the week • Signal persistence • Observed vs. predicted • Vector independent • Distinguishes spikes from deviant behavior, reducing false alarms
53. @ViscosityNA www.viscosityna.com Cluster Health Advisor - Calibration • Default models are conservative & designed to minimize noise, false alerts • DEFAULT_CLUSTER • DEFAULT_DB • Calibrating models improves sensitivity & accuracy • Six-hour "normal" workload is recommended • Cluster calibration should cover representative DB activity
54. @ViscosityNA www.viscosityna.com Cluster Health Advisor • CHM data was once challenging to process and read: chactl query diagnosis -cluster \ -start "2024-01-01 00:00:00" -end "2024-01-02 00:00:00" \ -htmlfile ~/cha_cluster.html chactl query diagnosis -db ORCL \ -start "2024-01-01 00:00:00" -end "2024-01-02 00:00:00" \ -htmlfile ~/cha_db_ORCL.html
55. @ViscosityNA www.viscosityna.com Cluster Health Advisor - AHF Scope More information on AHF Scope: https:/ /docs.oracle.com/en/engineered-systems/health-diagnostics/autonomous-health-framework/ahfug/ahf-scope.html
57. AHF Insights
58. @ViscosityNA www.viscosityna.com AHF Insights • Graphical, comprehensive report of a system • Drill-down into areas of interest • Related metrics displayed in a single view • Drag and click to zoom into specific times • All graphs remap to the new timeslice • Built-in analytics and recommendations • Run with: ahf analysis create --type insights
59. @ViscosityNA www.viscosityna.com AHF Insights
60. exachk & orachk
61. @ViscosityNA www.viscosityna.com AHF - ORAchk / EXAchk
62. @ViscosityNA www.viscosityna.com AHF - ORAchk / EXAchk
63. @ViscosityNA www.viscosityna.com AHF - ORAchk / EXAchk
64. @ViscosityNA www.viscosityna.com AHF - ORAchk / EXAchk
65. @ViscosityNA www.viscosityna.com AHF - ORAchk / EXAchk
66. Edb360, baby!
67. @ViscosityNA www.viscosityna.com eDB360 • Created by Carlos Sierra • Free, community-maintained tool for database health checks • Enterprise/Standard Edition; RAC/non-RAC; w/wout D&T Pack • May take a long time to run on some systems • VERY comprehensive, well-organized output! • Download: https:/ /github.com/sqldb360/sqldb360/releases • More information: https:/ /carlos-sierra.net/edb360-an-oracle-database-360-degree-view/
68. @ViscosityNA www.viscosityna.com eDB360 • Created by Carlos Sierra • Free, community-maintained tool for database health checks • Enterprise/Standard Edition; RAC/non-RAC; w/wout D&T Pack • May take a long time to run on some systems • VERY comprehensive, well-organized output! • Download: https:/ /github.com/sqldb360/sqldb360/releases • More information: https:/ /carlos-sierra.net/edb360-an-oracle-database-360-degree-view/
69. @ViscosityNA www.viscosityna.com eDB360 • Created by Carlos Sierra • Free, community-maintained tool for database health checks • Enterprise/Standard Edition; RAC/non-RAC; w/wout D&T Pack • May take a long time to run on some systems • VERY comprehensive, well-organized output! • Download: https:/ /github.com/sqldb360/sqldb360/releases • More information: https:/ /carlos-sierra.net/edb360-an-oracle-database-360-degree-view/
70. @ViscosityNA www.viscosityna.com eDB360 - Did I say "comprehensive?" eDB360 v204: 360-degree comprehensive report on an Oracle database 19.0.0.0.0 License:T. This report covers the time interval between 2023-03-20T10:15:51 and 2023-03-29T10:15:51. Days:8. Timestamp:2023-03-29T10:16:01. 1/7 2/7 3/7 4/7 5/7 6/7 7/7 1a. Database Configuration 1. System Under Observation html (13) 2. Load Profile - Per Sec html (70) 3. Load Profile - Per Txn html (39) 4. Load Profile - Count html (5) 5. Identification html (1) 6. Version html (1) 7. Options html (87) 8. Database html (1) 9. Instance html (1) 10. Pluggable Databases html (0) 11. Pluggable Databases Saved States html (0) 12. Database and Instance History html (1) 13. Instance Recovery html (1) 14. Database Properties html (42) 15. Registry html (15) 16. Registry SQL Patch html (2) 17. Registry History html (4) 18. Registry Hierarchy html (15) 19. Feature Usage Statistics html (243) 20. License html (1) 21. Resource Limit html (27) 22. HWM Statistics html (21) 23. Database Links html (1) 24. Application Schemas html (5) 25. Application Schema Objects html (10) 26. Modified Parameters html (6) 27. Non-default Parameters html (537) 28. All Parameters html (537) 29. Parameter File html (115) 30. PDB Parameter File html (0) 31. System Parameters Change Log html (0) 32. Memory Configuration html (19) 1b. Security 33. Users html (47) 34. Profiles html (36) 35. Users With Sensitive Roles Granted html (14) 36. Users With Inappropriate Tablespaces Granted html (0) 37. Proxy Users html (0) 38. Profile Password Verification Functions html (2) 39. Users with CREATE SESSION privilege html (4) 40. Roles (not default) html (1) 41. Role Privileges (not default) html (1) 42. System Grants (not default) html (44) 1c. Auditing 43. Default Object Auditing Options html (1) 44. Object Auditing Options html (214) 45. Statement Auditing Options html (0) 46. System Privileges Auditing Options html (0) 47. Audit related Initialization Parameters html (7) 48. Unified Auditing html (1) 49. Audit Configuration html (14) 50. Audit Trail Locations html (2) 51. Object Level Privileges (Audit Trail) html (1) 1d. Memory 52. SGA html (4) 53. SGA Info html (14) 54. SGA Stat html (1503) 55. PGA Stat html (21) 56. Memory Dynamic Components html (22) 57. Memory Target Advice html (0) 58. SGA Target Advice html (15) 59. PGA Target Advice html (14) 60. SQL Workarea Histogram html (14) 61. Memory Resize Operations html (69) 62. Memory Current Resize Operations html (0) 63. Memory Resize Operations Hist html (0) 64. Memory Target Advice Hist html (0) 1e. Resources (as per AWR and MEM) 65. CPU Demand Percentiles (AWR) html (20) 66. CPU Demand Series (Peak) for Instance 1 html line (204) 67. CPU Demand Series (Percentile) for Instance 1 html line (204) 68. Memory Size Percentiles (AWR) html (16) 69. Memory Size (MEM) html (2) 70. Memory Size (AWR) html (2) 71. Memory Size Series for Instance 1 html line (204) 72. Database Size on Disk html (5) 73. IOPS and MBPS Percentiles html (18) 74. IOPS Series for Instance 1 html line (203) 75. MBPS Series for Instance 1 html line (203) 76. R-IOPS Series for Instance 1 html line (203) 2a. Database Administration 84. Latches html (25) 85. Invalid Objects html (0) 86. Disabled Constraints html (0) 87. Enabled and not Validated Constraints html (0) 88. Non-indexed FK Constraints html (19) 89. Unusable Indexes html (0) 90. Invisible Indexes html (0) 91. Function-based Indexes html (0) 92. Bitmap Indexes html (0) 93. Reversed Indexes html (0) 94. Fat Indexes html (4) 95. Columns with Histogram on Long String html (68) 96. Hidden Columns html (189) 97. Virtual Columns html (3) 98. Tables not recently used html (75) 99. Indexes not recently used html (0) 100. Redundant Indexes(1) html (9) 101. Redundant Indexes(2) html (10) 102. Tables with more than 5 Indexes html (0) 103. Tables on KEEP Buffer Pool html (0) 104. Tables on RECYCLE Buffer Pool html (0) 105. Tables to be CACHED in Buffer Cache html (0) 106. Tables on KEEP Flash Cache html (0) 107. Tables on KEEP Cell Flash Cache html (0) 108. Tables set for Compression html (0) 109. Partitions set for Compression html (0) 110. Unindexed Partition Key Columns html (0) 111. Subpartitions set for Compression html (0) 112. Segments with non-default Buffer Pool html (0) 113. Segments with non-default Flash Cache html (0) 114. Segments with non-default Cell Flash Cache html (0) 115. Degree of Parallelism DOP on Tables html (0) 116. Tables with DOP Set html (0) 117. Degree of Parallelism DOP on Indexes html (0) 118. Indexes with DOP Set html (0) 119. Unused Columns html (0) 120. Columns with multiple Data Types html (18) 121. Jobs html (0) 122. Jobs Running html (0) 123. Scheduler Jobs html (23) 124. Scheduler Job Log for past 7 days html (3625) 125. Scheduler Windows html (9) 126. Scheduler Window Group Members html (28) 127. Advisor Parameters html (10000) 128. Advisor Execution Types html (10) 129. Advisor Tasks html (769) 130. Advisor Executions html (852) 131. Automated Maintenance Tasks html (3) 132. Automated Maintenance Task Tasks html (3) 133. Automated Maintenance Tasks History html (90) 134. Auto Task Job History html (154) 135. Current Blocking Activity html (0) 136. Sequences html (30) 137. Sequences used over 20% html (0) 138. Sequences prone to contention html (3) 139. Tables with more than 255 Columns html (0) 140. SQL using Literals or many children (by COUNT) html (14) 141. SQL using Literals or many children (by OWNER) html (14) 142. SQL consuming over 10GB of TEMP space html (0) 143. SQL with over 2GB of PGA allocated memory html (0) 144. Opened Cursors Current - Count per Session html (104) 145. Cached Cursors Count per Session html (150) 146. Cached Cursors Count per SQL_ID html (0) 147. Cached Cursors List per Session html (2533) 148. Session Cursor Cache Misses per Session html (78) 149. High Cursor Count html (18) 150. SQL with 100 or more unshared child cursors html (18) 151. Top SQL by Buffer Gets consolidating duplicates html (44) 152. Top SQL by number of duplicates html (103) 153. Libraries calling DBMS_STATS html (0) 154. Libraries doing ALTER SESSION html (24) 155. Workload Repository Control html (1) 156. ASH Info html (1) 157. ASH Retention html (1) 158. WRH$ Partitions html (41) 159. Segments with Next Extent at Risk html (0) 160. Libraries Version html (2) 161. Orphaned Synonyms html (0) 162. Last DDL by date html (4) 163. Last DDL by pdb and date html (4) 2b. Storage 164. Tablespace Usage Metrics html (10) 165. Tablespace html (10) 166. Tablespaces html (10) 167. Tablespace Groups html (0) 168. Default Tablespace Use html (6) 169. Temporary Tablespace Use html (1) 170. UNDO Stat html (576) 171. Tablespace Usage html (10) 172. Temp Tablespace Usage html (1) 173. Tablespace Quotas html (5) 174. Datafile html (20) 175. Data Files html (20) 176. Data Files Usage html (10) 177. Tempfile html (1) 178. Temp Files html (1) 179. I/O Statistics for DB Files html (32) 180. Kernel I/O taking long html (0) 181. Log Writer I/O taking long html (8) 182. I/O taking long html (25) 3a. Database Resource Management (DBRM) 254. Consumer Groups html (18) 255. Consumer Group Users and Roles html (3) 256. Resource Groups Mappings html (6) 257. Resource Groups Mapping Priorities html (11) 258. Resource Plan Directives html (47) 259. Resource Plans html (11) 260. Resource Plan Directives for PDBs html (0) 261. Resource Plans for PDBs html (0) 262. Active Resource Consumer Groups html (4) 263. Resource Consumer Group History html (64) 264. Resource Plan html (1) 265. Resource Plan History html (16) 266. RM Stats per Session html (148) 267. Resources Consumed per Consumer Group html (4) 268. Resources Consumed History html (244) 3b. Plan Stability 269. Report Auto Tuning Task txt 270. SQL Patches html (0) 271. SQL Profiles html (0) 272. SQL Plan Profiles Summary by Type and Status html (0) 273. SQL Profiles Summary by Creation Month html (0) 274. SQL Plan Baselines html (0) 275. SQL Plan Baselines Summary by Status html (0) 276. SQL Plan Baselines Summary by Creation Month html (0) 277. SQL Plan Baselines State by SQL html (0) 278. SQL Plan Directives html (134) 279. SQL Plan Directives - Objects html (436) 3c. Cost-based Optimizer (CBO) Statistics 280. CBO System Statistics html (13) 281. CBO System Statistics History html (0) 282. Stats History Availability html (1) 283. Default Values for DBMS_STATS html (45) 284. Tables Summary html (20) 285. Table Statistics Summary html (26) 286. Table Columns Summary html (6) 287. Indexes Summary html (5) 288. Ind Summary html (6) 289. Table Partitions Summary html (1) 290. Index Partitions Summary html (1) 291. Table Partitioning html (1) 292. Partitioning Keys and col Usage Statistics html (5) 293. Tables with Missing Stats html (0) 294. Tables with Stale Stats html (1) 295. Tables with Outdated Stats html (67) 296. Tables with Locked Stats html (0) 297. Global Temporary Tables with Stats html (0) 298. Temp Tables with Stats html (7) 299. Objects with many Stats Versions html (0) 300. Statistics Gathering History Report html 301. SYS Stats for WRH$, WRI$, WRM$ and WRR$ Tables html (363) 302. SYS Stats for WRH$, WRI$, WRM$ and WRR$ Indexes html (402) 303. Table Modifications for WRH$, WRI$, WRM$ and WRR$ html (468) 304. Columns with Histograms in Extended Statistics html (398) 3d. Performance Summaries 305. Wait Statistics html (9) 306. System Wait Class html (11) 307. SQL with changing Elapsed Time per Execution (list) html (2) 308. SQL with changing Elapsed Time per Execution (time series) html (141) 309. SQL with multiple Execution Plans html (23) 310. Top Plans html (15) 311. Result Cache related parameters html (3) 312. Result Cache status html (1) 313. Result Cache memory html (2) 314. Result Cache statistics html (16) 315. Client Result Cache statistics html (0) 316. AAS for past minute html (9) 317. Wait Class Metric for past minute html (4) 318. Event Metric for past minute html (25) 319. System Metric for past minute html (161) 320. System Metric Summary for past hour html (161) 3e. Operating System (OS) Statistics History 321. Operating System (OS) Statistics html (25) 322. OS Load and CPU Cores for Instance 1 html line (204) 323. OS Load and CPU Subscription Threshold for Instance 1 html line (204) 324. CPU Time Percent for Instance 1 html line (203) 325. CPU Busy and Idle Times Percent for Instance 1 html line (203) 326. CPU User and Sys Times Percent for Instance 1 html line (203) 327. Virtual Memory (VM) for Instance 1 html line (203) 328. CPU Busy Time per Instance html line (203) 329. CPU User Time per Instance html line (203) 330. CPU Sys Time per Instance html line (203) 331. CPU Nice Time per Instance html line (203) 332. CPU Idle Time per Instance html line (203) 333. CPU IO Wait Time per Instance html line (203) 334. CPU RSRC MGR Wait Time per Instance html line (203) 335. OS CPU Wait Time per Instance html line (0) 336. Virtual Memory (VM) IN-Bytes per Instance html line (203) 337. Virtual Memory (VM) OUT-Bytes per Instance html line (203) 3h. Sessions 338. Sessions Aggregate per Type html (4) 339. Sessions Aggregate per User and Type html (9) 340. Sessions Aggregate per Module and Action html (23) 4a. System Global Area (SGA) Statistics History 376. SGA Allocation html (4) 377. SGA Statistics for Instance 1 html line (203) 378. Subpools in the Shared Pool with largest changes html (8) 379. Memory allocation for "SQLA" html line (203) 380. Memory allocation for "KGLH0" html line (203) 381. Memory allocation for "KQR X PO" html line (95) 382. Memory allocation for "KGLS" html line (143) 383. Memory allocation for "KGLHD" html line (203) 384. Memory allocation for "KQR X SO" html line (4) 385. Memory allocation for "KGLDA" html line (170) 386. Free Memory in Shared Pool html line (203) 387. Memory allocations in Shared Pool for instance 1 html line (203) 4b. Program Global Area (PGA) Statistics History 388. PGA Statistics for Instance 1 html line (203) 4c. Reads to Buffer Cache 389. Buffer Cache Statistics in MB for Instance 1 html line (203) 390. Buffer Cache Statistics in Blocks for Instance 1 html line (203) 391. Buffer Cache Statistics in Blocks per second for Instance 1 html line (203) 4d. System Time Model 392. System Time Model (STM) for Instance 1 html line (203) 4e. System Time Model Components 393. STM: background elapsed time per Instance html line (203) 394. STM: background cpu time per Instance html line (203) 395. STM: RMAN cpu time (backup/restore) per Instance html line (203) 396. STM: DB time per Instance html line (203) 397. STM: DB CPU per Instance html line (203) 398. STM: connection management call elapsed time per Instance html line (203) 399. STM: sequence load elapsed time per Instance html line (203) 400. STM: sql execute elapsed time per Instance html line (203) 401. STM: parse time elapsed per Instance html line (203) 402. STM: hard parse elapsed time per Instance html line (203) 403. STM: PL/SQL execution elapsed time per Instance html line (203) 404. STM: inbound PL/SQL rpc elapsed time per Instance html line (203) 405. STM: PL/SQL compilation elapsed time per Instance html line (203) 406. STM: Java execution elapsed time per Instance html line (203) 407. STM: repeated bind elapsed time per Instance html line (203) 4f. AAS per Class and Top Event 408. Average Latency per Wait Class html (7) 409. User I/O AAS per Instance html line (203) 410. System I/O AAS per Instance html line (203) 411. Cluster AAS per Instance html line (0) 412. Commit AAs per Instance html line (203) 413. Concurrency AAS per Instance html line (203) 414. Application AAS per Instance html line (203) 415. Administrative AAS per Instance html line (100) 416. Configuration AAS per Instance html line (82) 417. Network AAS per Instance html line (203) 418. Queueing AAS per Instance html line (0) 419. Scheduler AAS per Instance html line (1) 420. Other AAS per Instance html line (203) 421. Average Latency per Wait Event html (27) 422. AAS waiting on Top User I/O Events html line (203) 423. AAS waiting on Top System I/O Events html line (203) 424. AAS waiting on Top Commit Wait Events html line (203) 425. AAS waiting on Top Concurrency Events html line (203) 426. AAS waiting on Top Application Events html line (203) 427. AAS waiting on Top Administrative Events html line (100) 428. AAS waiting on Top Configuration Events html line (82) 429. AAS waiting on Top Network Wait Events html line (203) 430. AAS waiting on Top Queueing Wait Events html line (0) 431. AAS waiting on Top Scheduler Wait Events html line (1) 432. AAS waiting on Top "Other" Class Top non-PX non-latch Wait Events html line (203) 433. AAS waiting on Top "Other" Class Top PX Wait Events html line (0) 434. AAS waiting on Top "Other" Class Top latch Wait Events html line (203) 4g. AAS Histogram for Top 24 Wait Events 435. Top 24 Wait Events html (24) 436. Administrative "Backup: MML write backup piece" Average Active Session Histogram html line (203) 437. System I/O "log file parallel write" Average Active Session Histogram html line (203) 438. Commit "log file sync" Average Active Session Histogram html line (203) 439. System I/O "RMAN backup & recovery I/O" Average Active Session Histogram html line (203) 440. Other "latch free" Average Active Session Histogram html line (203) 441. System I/O "control file parallel write" Average Active Session Histogram html line (203) 442. User I/O "direct path write" Average Active Session Histogram html line (203) 443. Other "oracle thread bootstrap" Average Active Session Histogram html line (203) 444. Network "SQL*Net more data from client" Average Active Session Histogram html line (203) 445. System I/O "db file async I/O submit" Average Active Session Histogram html line (203) 446. User I/O "Disk file operations I/O" Average Active Session Histogram html line (203) 447. Administrative "Backup: MML extended initialization" Average Active Session Histogram html line (203) 448. Administrative "Backup: MML create a backup piece" Average Active Session Histogram html line (203) 449. Administrative "Backup: MML commit backup piece" Average Active Session Histogram html line (203) 450. System I/O "control file sequential read" Average Active Session Histogram html line (203) 451. User I/O "db file sequential read" Average Active Session Histogram html line (203) 452. User I/O "direct path sync" Average Active Session Histogram html line (203) 453. User I/O "local write wait" Average Active Session Histogram html line (203) 454. Other "os thread creation" Average Active Session Histogram html line (203) 455. System I/O "log file sequential read" Average Active Session Histogram html line (203) 456. Application "enq: RO - fast object reuse" Average Active Session Histogram html line (203) 457. Other "PGA memory operation" Average Active Session Histogram html line (203) 458. User I/O "DG Broker configuration file I/O" Average Active Session Histogram html line (203) 459. User I/O "Data file init write" Average Active Session Histogram html line (203) 5a. Active Session History (ASH) 585. AAS per Wait Class for Instance 1 html line (204) 5b. Active Session History (ASH) on Wait Class 586. AAS Total per Instance html line (204) 587. AAS On CPU per Instance html line (204) 588. AAS waiting on User IO per Instance html line (33) 589. AAS waiting on System IO per Instance html line (137) 590. AAS waiting on Commit per Instance html line (56) 591. AAS waiting on Concurrency per Instance html line (2) 592. AAS waiting on Application per Instance html line (0) 593. AAS waiting on Administrative per Instance html line (53) 594. AAS waiting on Configuration per Instance html line (0) 595. AAS waiting on Network per Instance html line (19) 596. AAS waiting on Queueing per Instance html line (0) 597. AAS waiting on Scheduler per Instance html line (0) 598. AAS waiting on Other per Instance html line (53) 5c. Active Session History (ASH) on CPU and Top 24 Wait Events 599. Top 24 Wait Events html (24) 600. ASH CPU per Instance html line (204) 601. ASH CPU per Source html pie (15) 602. ASH Administrative "Backup: MML write backup piece" per Instance html line (40) 603. ASH Administrative "Backup: MML write backup piece" per Source html pie (3) 604. ASH System I/O "log file parallel write" per Instance html line (106) 605. ASH System I/O "log file parallel write" per Source html pie (2) 606. ASH Commit "log file sync" per Instance html line (56) 607. ASH Commit "log file sync" per Source html pie (4) 608. ASH Other "latch free" per Instance html line (41) 609. ASH Other "latch free" per Source html pie (14) 610. ASH System I/O "RMAN backup & recovery I/O" per Instance html line (8) 611. ASH System I/O "RMAN backup & recovery I/O" per Source html pie (2) 612. ASH System I/O "control file parallel write" per Instance html line (40) 613. ASH System I/O "control file parallel write" per Source html pie (5) 614. ASH Other "oracle thread bootstrap" per Instance html line (11) 615. ASH Other "oracle thread bootstrap" per Source html pie (3) 616. ASH User I/O "direct path write" per Instance html line (22) 617. ASH User I/O "direct path write" per Source html pie (2) 618. ASH Network "SQL*Net more data from client" per Instance html line (19) 619. ASH Network "SQL*Net more data from client" per Source html pie (2) 620. ASH System I/O "db file async I/O submit" per Instance html line (15) 621. ASH System I/O "db file async I/O submit" per Source html pie (2) 622. ASH Administrative "Backup: MML create a backup piece" per Instance html line (9) 623. ASH Administrative "Backup: MML create a backup piece" per Source html pie (3) 624. ASH User I/O "Disk file operations I/O" per Instance html line (5) 625. ASH User I/O "Disk file operations I/O" per Source html pie (5) 626. ASH Administrative "Backup: MML extended initialization" per Instance html line (10) 627. ASH Administrative "Backup: MML extended initialization" per Source html pie (2) 628. ASH Administrative "Backup: MML commit backup piece" per Instance html line (5) 629. ASH Administrative "Backup: MML commit backup piece" per Source html pie (3) 630. ASH Administrative "Backup: MML shutdown" per Instance html line (4) 631. ASH Administrative "Backup: MML shutdown" per Source html pie (4) 632. ASH User I/O "db file sequential read" per Instance html line (3) 633. ASH User I/O "db file sequential read" per Source html pie (3) 634. ASH Other "recovery area: computing obsolete files" per Instance html line (3) 635. ASH Other "recovery area: computing obsolete files" per Source html pie (2) 636. ASH User I/O "local write wait" per Instance html line (2) 637. ASH User I/O "local write wait" per Source html pie (2) 638. ASH Concurrency "resmgr:internal state change" per Instance html line (2) 639. ASH Concurrency "resmgr:internal state change" per Source html pie (2) 640. ASH System I/O "control file single write" per Instance html line (2) 641. ASH System I/O "control file single write" per Source html pie (2) 642. ASH System I/O "control file sequential read" per Instance html line (2) 643. ASH System I/O "control file sequential read" per Source html pie (3) 644. ASH User I/O "DG Broker configuration file I/O" per Instance html line (1) 645. ASH User I/O "DG Broker configuration file I/O" per Source html pie (2) 646. ASH User I/O "direct path sync" per Instance html line (2) 647. ASH User I/O "direct path sync" per Source html pie (3) 648. ASH Other "PGA memory operation" per Instance html line (2) 649. ASH Other "PGA memory operation" per Source html pie (3) 5d. System Statistics per Snap Interval 650. Cell Blocks html line (203) 651. Cell Commit Cache html line (203) 652. Cell Compression Units html line (0) 653. Cell Flash Cache Read Hits html line (203) 654. Cell I/O Bytes html line (203) 655. Cell Scans html line (203) 656. Cell Smart Scan Sessions html line (203) 657. Chained or Migrated Rows html line (203) 658. Checkpoints html line (203) 659. Commits and Rollbacks html line (203) 660. Current and Consistent Blocks html line (203) 661. Consistent Gets (direct and from cache) html line (203) 662. Cursor and SQL Area evicted html line (203) 663. En(De)cryption html line (203) 664. EHCC Compression Units (De)Compressed html line (0) 665. Enqueues html line (203) 666. Flash Cache Inserts and Evictions html line (203) 667. Global Blocks and Reads html line (203) 668. Logons html line (203) 669. Node Splits html line (203) 670. Parse Counts html line (203) 671. Physical Reads Blocks html line (203) 672. Physical Reads Blocks (direct and cache) html line (203) 673. Physical Reads Bytes html line (203) 674. Physical Reads Requests html line (203) 675. Physical Reads Total IO Requests html line (203) 676. Physical Writes Blocks html line (203) 677. Physical Writes Bytes html line (203) 6a. Active Session History (ASH) - Top Timed Classes 704. ASH Top Timed Classes for Instance 1 for 1 day html bar (7) 705. ASH Top Timed Classes for Instance 1 for 5 working days html bar (7) 706. ASH Top Timed Classes for Instance 1 for 7 days html bar (8) 707. ASH Top Timed Classes for Instance 1 for 7 working days html bar (7) 708. ASH Top Timed Classes for Instance 1 for 8 days of history html bar (8) 6b. Active Session History (ASH) - Top Timed Events 709. ASH Top Timed Events for Instance 1 for 1 day html bar (17) 710. ASH Top Timed Events for Instance 1 for 5 working days html bar (20) 711. ASH Top Timed Events for Instance 1 for 7 days html bar (28) 712. ASH Top Timed Events for Instance 1 for 7 working days html bar (21) 713. ASH Top Timed Events for Instance 1 for 8 days of history html bar (30) 6c. Active Session History (ASH) - Top SQL 714. ASH Top SQL for Instance 1 for 1 day html pie (15) 715. ASH Top SQL for Instance 1 for 5 working days html pie (15) 716. ASH Top SQL for Instance 1 for 7 days html pie (15) 717. ASH Top SQL for Instance 1 for 7 working days html pie (15) 718. ASH Top SQL for Instance 1 for 8 days of history html pie (15) 6d. Active Session History (ASH) - Top SQL - Time Series 719. ASH Top SQL for Instance 1 for 1 day html line (18) 720. ASH Top SQL for Instance 1 for 5 working days html line (55) 721. ASH Top SQL for Instance 1 for 7 days html line (121) 722. ASH Top SQL for Instance 1 for 7 working days html line (68) 723. ASH Top SQL for Instance 1 for 8 days of history html line (152) 6e. Active Session History (ASH) - Top Programs 724. ASH Top Programs for Instance 1 for 1 day html pie (15) 725. ASH Top Programs for Instance 1 for 5 working days html pie (15) 726. ASH Top Programs for Instance 1 for 7 days html pie (15) 727. ASH Top Programs for Instance 1 for 7 working days html pie (15) 728. ASH Top Programs for Instance 1 for 8 days of history html pie (15) 6f. Active Session History (ASH) - Top Modules and Actions 729. Operations profile of Top Modules for Cluster for 1 day html (30) 730. ASH Top Modules and Actions for Instance 1 for 1 day html pie (15) 731. Operations profile of Top Modules for Cluster for 5 working days html (29) 732. ASH Top Modules and Actions for Instance 1 for 5 working days html pie (15) 733. Operations profile of Top Modules for Cluster for 7 html (48) 734. ASH Top Modules and Actions for Instance 1 for 7 days html pie (15) 735. Operations profile of Top Modules for Cluster for 7 working days html (33) 736. ASH Top Modules and Actions for Instance 1 for 7 working days html pie (15) 737. Operations profile of Top Modules for Cluster for 8 days of history html (50) 738. ASH Top Modules and Actions for Instance 1 for 8 days of history html pie (15) 6g. Active Session History (ASH) - Top Users 739. ASH Top Users for Instance 1 for 1 day html pie (5) 740. ASH Top Users for Instance 1 for 5 working days html pie (5) 741. ASH Top Users for Instance 1 for 7 days html pie (5) 742. ASH Top Users for Instance 1 for 7 working days html pie (5) 743. ASH Top Users for Instance 1 for 8 days of history html pie (5) 6h. Active Session History (ASH) - Top PLSQL Procedures 744. ASH Top PLSQL Procedures for Instance 1 for 1 day html pie (10) 745. ASH Top PLSQL Procedures for Instance 1 for 5 working days html pie (14) 746. ASH Top PLSQL Procedures for Instance 1 for 7 days html pie (15) 747. ASH Top PLSQL Procedures for Instance 1 for 7 working days html pie (14) 748. ASH Top PLSQL Procedures for Instance 1 for 8 days of history html pie (15) 6i. Active Session History (ASH) - Top Data Objects 749. ASH Top Data Objects for Instance 1 for 1 day html pie (4) 750. ASH Top Data Objects for Instance 1 for 5 working days html pie (3) 751. ASH Top Data Objects for Instance 1 for 7 days html pie (6) 752. ASH Top Data Objects for Instance 1 for 7 working days html pie (3) 753. ASH Top Data Objects for Instance 1 for 8 days of history html pie (6) 6j. Active Session History (ASH) - Service and User 754. ASH Top Services and Users for Instance 1 for 1 day html pie (7) 755. ASH Top Services and Users for Instance 1 for 5 working days html pie (7) 756. ASH Top Services and Users for Instance 1 for 7 days html pie (6) 757. ASH Top Services and Users for Instance 1 for 7 working days html pie (7) 758. ASH Top Services and Users for Instance 1 for 8 days of history html pie (7) 6k. Active Session History (ASH) - Top PHV 759. ASH Top PHV for Instance 1 for 1 day html pie (1) 760. ASH Top PHV for Instance 1 for 5 working days html pie (2) 761. ASH Top PHV for Instance 1 for 7 days html pie (3) 762. ASH Top PHV for Instance 1 for 7 working days html pie (2) 763. ASH Top PHV for Instance 1 for 8 days of history html pie (4) 6l. Active Session History (ASH) - Top Signature 764. ASH Top FORCE_MATCHING_SIGNATURE for Instance 1 for 1 day html pie (1) 765. ASH Top FORCE_MATCHING_SIGNATURE for Instance 1 for 5 working days html pie (1) 766. ASH Top FORCE_MATCHING_SIGNATURE for Instance 1 for 7 days html pie (2) 767. ASH Top FORCE_MATCHING_SIGNATURE for Instance 1 for 7 working days html pie (1) 768. ASH Top FORCE_MATCHING_SIGNATURE for Instance 1 for 8 days of history html pie (2) 6m. Active Session History (ASH) - Top PDBs 7a. AWR/ADDM/ASH Reports 784. 1_3482_3483_max5wd1 (2023-03-22T16:00:49 to 2023-03-22T17:00:53) awr html awr diff html 785. 1_3602_3603_max5wd2 (2023-03-27T16:00:53 to 2023-03-27T17:00:54) awr html awr diff html 786. 1_3505_3506_max5wd3 (2023-03-23T15:00:21 to 2023-03-23T16:00:22) awr html awr diff html 787. 1_3559_3560_max7d1 (2023-03-25T21:00:27 to 2023-03-25T22:00:32) awr html awr diff html 788. 1_3631_3632_max7d2 (2023-03-28T21:00:49 to 2023-03-28T22:00:50) awr html awr diff html 789. 1_3463_3464_max7d3 (2023-03-21T21:00:13 to 2023-03-21T22:00:24) awr html awr diff html 790. 1_3475_3476_med7d (2023-03-22T09:00:12 to 2023-03-22T10:00:14) awr html 791. 1_3593_3594_min5wd (2023-03-27T07:00:23 to 2023-03-27T08:00:24) awr html 7b. SQL Sample 792. rank:1 13kk1vap2q7p4 et:2h cpu:2h io:0h type:SELECT planx(text) sqld360(zip) 793. rank:2 89vw467x4pvqv et:1h cpu:1h io:0h type:SELECT planx(text) sqld360(zip) 794. rank:3 b9mppsn1udu4h et:0h cpu:0h io:0h type:INSERT planx(text) sqld360(zip) 795. rank:1 0dfxfyy5r32qq cursors:122 planx(text) sqld360(zip) 796. rank:2 38z2dr4ssxswn cursors:114 planx(text) sqld360(zip) 797. rank:1 gd3aqp7xuk8yd signature:0(47) planx(text) sqld360(zip)
71. @ViscosityNA www.viscosityna.com eDB360 - Section 1 • System info • Archive, log modes • Flashback • Resetlogs dates • DG status • Registry information/versions • Correct patch application • DB links (IP/aliases) • Schema size, complexity • Parameters • Hidden • Differences between nodes • Active vs. spfile • Users, profiles, accounts • Auditing • SGA/PGA advice
72. @ViscosityNA www.viscosityna.com eDB360 - Section 2a • Invalid objects • Disabled, invalid constraints • Non-indexed FK • Unusable indexes • Partition key indexes • Orphaned synonyms • Function-based indexes • Reverse key indexes • Bitmap indexes • Latch misses • Jobs vs. scheduler jobs • Broken jobs • Advisor jobs running, correct • Sequences • Bad SQL • Space issues
73. @ViscosityNA www.viscosityna.com eDB360 - Section 2b • Bigfile use • Multiple block sizes • Offline, nologging tablespaces • Targets for coalesce • Quotas • Read-only, offline datafiles • OMF/OFA correct • Unused tempfiles • IO issues • SYSAUX occupants • One extent, zero row objects • Index size > table size • Segments in reserved TS • Recyclebin • Actual size > estimate • Tables w/o partitions
74. @ViscosityNA www.viscosityna.com eDB360 - Section 2d • RMAN backup logs • Warning and error messages • Misconfigured MM • BCT, optimization • Correct FRA setup • Orphaned files, restore points • Nologging objects • Unrecoverable objects • Redo logs • Sized properly • Adequate groups • Block size correct/consistent • No thread 0 • Proper multiplexing • SRL ≠ multiplexed! • Redo generation heat map
75. @ViscosityNA www.viscosityna.com eDB360 - Other sections • 2e: Data Guard • Protection mode • Switchover status • DG Broker use • N+1 SRL configured • Destinations set correctly • Apply/shipping lag • 3a: Resource Manager config • 3b: Plan stability/management • 3c-j: Based on other findings • Frequent SQL • Row-by-row execution • Blocking/locking/waiting • 6: Supplement findings from other sections/reports • 7: Advisories
76. groovy - RDA
77. @ViscosityNA www.viscosityna.com RDA • Information not typically found elsewhere • Memory, CPU, disk metrics • OS environment information, patches, packages, kernel settings • Network configuration and statistics • Download and FAQ at MOS Doc ID 314422.1
78. @ViscosityNA www.viscosityna.com RDA • Oracle Home inventory • Installed product information • Hidden/non-default params • Duplicate global/node params • DB registry • SGA resize • NLS settings • Java objects, roles • Controlfile configuration • Broken jobs • Services: failover, HA/LB setup • Audit, password info • PWFile users • Audits • Contents of DBA_ERRORS • Correct file permissions
79. @ViscosityNA www.viscosityna.com RDA • SCN health check • CRS and service status • Files/dirs under CRS_HOME • Files/dirs under ADR_HOMEs • Inappropriate files under ORACLE_HOME (Data Pump) • Logs outside diagnostic dir • grep individual logs: • alert.log • sqlnet.log • listener.log • DG Broker • CRS
80. upgrade utilities
81. @ViscosityNA www.viscosityna.com Pre- and post-upgrade scripts • Oracle Pre-Upgrade Utility: MOS Doc ID 884522.1 • preupgrd.sql, utluppkg.sql • DB Upgrade and Migration Diagnostics: MOS Doc ID 556610.1 • dbupgdiag.sql • Pre- and post-upgrade files/scripts generally located under $ORACLE_BASE/cfgtoollogs/$ORACLE_SID
82. SQL health check
83. @ViscosityNA www.viscosityna.com SQL Health Check • Data Dictionary Health Check: MOS Doc ID 136697.1 • hcheck.sql
85. @ViscosityNA www.viscosityna.com More AHF utilities • Built into AHF: • OSWatcher (oswbb) • Hang Manager • Procwatcher (prw) • Cluster Verification Utility (CVU) • Quality of Service Management (QoSM) • Memory Guard
86. @ViscosityNA www.viscosityna.com Database Security Assessment Tool (DBSAT) • Collector and Reporter identify database security risks • Accounts, privileges, and roles • Authorization and FGA controls • Auditing • Encryption • Database, network, and OS configuration • Discoverer searches for data matching PII/sensitive patterns • Information and download: MOS Note 2138254.1
87. @ViscosityNA www.viscosityna.com Linters • Evaluate code for errors, vulnerabilities, and style issues • Improve code quality • Potential linter targets • Backup scripts • Batch activities • ETL processing • Automated HA • cron jobs
88. @ViscosityNA www.viscosityna.com Linters - shellcheck • Linter for bash/sh/ksh/dash • Open source: https:/ /github.com/koalaman/shellcheck • Interactive site: https:/ /www.shellcheck.net • EPEL install: yum -y install epel-release && yum -y install ShellCheck • CLI: shellcheck <script-name> • Integration with vi, emacs, VSCode, and others
89. @ViscosityNA www.viscosityna.com D @oraclesean e oraclesean.com C https://www.linkedin.com/in/soscott/ ) https://github.com/oraclesean k sean.scott@viscosityna.com P https://youtube.com/@oracle_sean