Oracle Database includes an advanced fault diagnosability infrastructure for collecting and managing diagnostic data, so as to diagnose and resolve database problems. Diagnostic data includes the trace files, dumps, and core files that are also present in previous releases, plus new types of diagnostic data that enable customers and Oracle Support to identify, investigate, track, and resolve problems quickly and effectively. Show
9.1 About the Oracle Database Fault Diagnosability InfrastructureOracle Database includes a fault diagnosability infrastructure for preventing, detecting, diagnosing, and resolving database problems.
9.1.1 Fault Diagnosability Infrastructure OverviewThe fault diagnosability infrastructure aids in preventing, detecting, diagnosing, and resolving problems. The problems that are targeted in particular are critical errors such as those caused by code bugs, metadata corruption, and customer data corruption. When a critical error occurs, it is assigned an incident number, and diagnostic data for the error (such as trace files) are immediately captured and tagged with this number. The data is then stored in the Automatic Diagnostic Repository (ADR)—a file-based repository outside the database—where it can later be retrieved by incident number and analyzed. The goals of the fault diagnosability infrastructure are the following:
The keys to achieving these goals are the following technologies:
9.1.2 Incidents and ProblemsA problem is a critical error in a database instance, Oracle Automatic Storage Management (Oracle ASM) instance, or other Oracle product or component. An incident is a single occurrence of a problem.
9.1.2.1 About Incidents and ProblemsTo facilitate diagnosis and resolution of critical errors, the fault diagnosability infrastructure introduces two concepts for Oracle Database: problems and incidents. A problem is a critical error in a database instance, Oracle Automatic Storage Management (Oracle ASM) instance, or other
Oracle product or component. Critical errors manifest as internal errors, such as An incident is a single occurrence of a problem. When a problem (critical error) occurs multiple times, an incident is created for each occurrence. Incidents are timestamped and tracked in the Automatic Diagnostic Repository (ADR). Each incident is identified by a numeric incident ID, which is unique within the ADR. When an incident occurs, the database:
Diagnosis and resolution of a critical error usually starts with an incident alert. Incident alerts are displayed on the Cloud Control Database Home page or Oracle Automatic Storage Management Home page. The Database Home page also displays in its Related Alerts section any critical alerts in the Oracle ASM instance or other Oracle products or components. After viewing an alert, you can then view the problem and its associated incidents with Cloud Control or with the ADRCI command-line utility. See Also:
9.1.2.2 Incident Flood ControlIt is conceivable that a problem could generate dozens or perhaps hundreds of incidents in a short period of time. This would generate too much diagnostic data, which would consume too much space in the ADR and could possibly slow down your efforts to diagnose and resolve the problem. For these reasons, the fault diagnosability infrastructure applies flood control to incident generation after certain thresholds are reached. A flood-controlled incident is an incident that generates an alert log entry, is recorded in the ADR, but does not generate incident dumps. Flood-controlled incidents provide a way of informing you that a critical error is ongoing, without overloading the system with diagnostic data. You can choose to view or hide flood-controlled incidents when viewing incidents with Cloud Control or the ADRCI command-line utility. Threshold levels for incident flood control are predetermined and cannot be changed. They are defined as follows:
In addition, after 50 incidents for the same problem key occur in one hour, or 250 incidents for the same problem key occur in one day, subsequent incidents for this problem key are not recorded at all in the ADR. In these cases, the database writes a message to the alert log indicating that no further incidents will be recorded. As long as incidents continue to be generated for this problem key, this message is added to the alert log every ten minutes until the hour or the day expires. Upon expiration of the hour or day, normal recording of incidents for that problem key begins again. 9.1.2.3 Related Problems Across the TopologyFor any problem identified in a database instance, the diagnosability framework can identify related problems across the topology of your Oracle Database installation. In a single instance environment, a related problem could be identified in the local Oracle ASM instance. In an Oracle RAC environment, a related problem could be identified in any database instance or Oracle ASM instance on any other node. When investigating problems, you are able to view and gather information on any related problems. A problem is related to the original problem if it occurs within a designated time period or shares the same execution context identifier. An execution context identifier (ECID) is a globally unique identifier used to tag and track a single call through the Oracle software stack, for example, a call to Oracle Fusion Middleware that then calls into Oracle Database to retrieve data. The ECID is typically generated in the middle tier and is passed to the database as an Oracle Call Interface (OCI) attribute. When a single call has failures on multiple tiers of the Oracle software stack, problems that are generated are tagged with the same ECID so that they can be correlated. You can then determine the tier on which the originating problem occurred. 9.1.3 Fault Diagnosability Infrastructure ComponentsThe fault diagnosability infrastructure consists of several components, including the Automatic Diagnostic Repository (ADR), various logs, trace files, the Enterprise Manager Support Workbench, and the ADRCI Command-Line Utility.
9.1.3.1 Automatic Diagnostic Repository (ADR)The ADR is a file-based repository for database diagnostic data such as traces, dumps, the alert log, health monitor reports, and more. It has a unified directory structure across multiple instances and multiple products. The database, Oracle Automatic Storage Management (Oracle ASM), the listener, Oracle Clusterware, and other Oracle products or components store all diagnostic data in the ADR. Each instance of each product stores diagnostic data underneath its own home directory within the ADR. For example, in an Oracle Real Application Clusters environment with shared storage and Oracle ASM, each database instance and each Oracle ASM instance has an ADR home directory. ADR's unified directory structure, consistent diagnostic data formats across products and instances, and a unified set of tools enable customers and Oracle Support to correlate and analyze diagnostic data across multiple instances. With Oracle Clusterware, each host node in the cluster has an ADR home directory. Note: Because all diagnostic data, including the alert log, are stored in the ADR, the initialization parameters 9.1.3.2 Alert LogThe alert log is an XML file that is a chronological log of messages and errors. There is one alert log in each ADR home. Each alert log is specific to its component type, such as database, Oracle ASM, listener, and Oracle Clusterware. For the database, the alert log includes messages about the following:
You can view the alert log in text format (with the XML tags stripped) with Cloud Control and with the ADRCI utility. There is also a text-formatted version of the alert log stored in the ADR for backward compatibility. However, Oracle recommends that any parsing of the alert log contents be done with the XML-formatted version, because the text format is unstructured and may change from release to release. 9.1.3.3 Trace Files, Dumps, and Core FilesTrace files, dumps, and core files contain diagnostic data that are used to investigate problems. They are stored in the ADR.
9.1.3.3.1 Trace Files Each server and background process can write to an associated trace file. Trace files are updated periodically over the life of the process and can contain information on the process environment, status, activities, and errors. In addition, when a process detects a critical error, it writes information about the error to its trace file. The SQL trace facility also creates trace files, which provide performance information on individual SQL statements. You can enable SQL tracing for a session or an instance. Trace file names are platform-dependent. Typically, database background
process trace file names contain the Oracle SID, the background process name, and the operating system process number, while server process trace file names contain the Oracle SID, the string "ora", and the operating system process number. The file extension is Oracle Database includes tools that help you analyze trace files. For more information on application tracing, SQL tracing, and tracing tools, see Oracle Database SQL Tuning Guide. 9.1.3.3.2 Dumps A dump is a specific type of trace file. A dump is typically a one-time output of diagnostic data in response to an event (such as an incident), whereas a trace tends to be continuous output of diagnostic data. When an incident occurs, the database writes one or more dumps to the incident directory created for the incident. Incident dumps also contain the incident number in the file name. 9.1.3.3.3 Core Files A core file contains a memory dump, in an all-binary, port-specific format. Core file names include the string "core" and the operating system process ID. Core files are useful to Oracle Support engineers only. Core files are not found on all platforms. 9.1.3.4 DDL LogThe data definition language (DDL) log is a file that has the same format and basic behavior as the alert log, but it only contains the DDL statements issued by the database. The DDL log is created only for the RDBMS component and only
if the The DDL log contains one log record for each DDL statement issued by the database. The DDL log is included in IPS incident packages. There are two DDL logs that contain the same information. One is an XML file, and the other is a text file. The DDL log is stored in the log/ddl subdirectory of the ADR home. 9.1.3.5 Debug LogAn Oracle Database component can detect conditions, states, or events that are unusual, but which do not inhibit correct operation of the detecting component. The component can issue a warning about these conditions, states, or events. The debug log is a file that records these warnings. These warnings recorded in the debug log are not serious enough to warrant an incident or a write to the alert log. They do warrant a record in a log file because they might be needed to diagnose a future problem. The debug log has the same format and basic behavior as the alert log, but it only contains information about possible problems that might need to be corrected. The debug log reduces the amount of information in the alert log and trace files. It also improves the visibility of debug information. The debug log is included in IPS incident packages. The debug log's contents are intended for Oracle Support. Database administrators should not use the debug log directly. Note: Because there is a separate debug log starting with Oracle Database 12c, the alert log and the trace files are streamlined. They now contain fewer warnings of the type that are recorded in the debug log. 9.1.3.6 Other ADR ContentsIn addition to files mentioned in the previous sections, the ADR contains health monitor reports, data repair records, SQL test cases, incident packages, and more. These components are described later in the chapter. 9.1.3.7 Enterprise Manager Support WorkbenchThe Enterprise Manager Support Workbench (Support Workbench) is a facility that enables you to investigate, report, and in some cases, repair problems (critical errors), all with an easy-to-use graphical interface. The Support Workbench provides a self-service means for you to gather first-failure diagnostic data, obtain a support request number, and upload diagnostic data to Oracle Support with a minimum of effort and in a very short time, thereby reducing time-to-resolution for problems. The Support Workbench also recommends and provides easy access to Oracle advisors that help you repair SQL-related problems, data corruption problems, and more. 9.1.3.8 ADRCI Command-Line UtilityThe ADR Command Interpreter (ADRCI) is a utility that enables you to investigate problems, view health check reports, and package first-failure diagnostic data, all within a command-line environment. You can then upload the package to Oracle Support. ADRCI also enables you to view the names of the trace files in the ADR, and to view the alert log with XML tags stripped, with and without content filtering. For more information on ADRCI, see Oracle Database Utilities. 9.1.4 Structure, Contents, and Location of the Automatic Diagnostic RepositoryThe Automatic Diagnostic Repository (ADR) is a directory structure that is stored outside of the database. It is therefore available for problem diagnosis when the database is down. The ADR root directory is known as ADR base. Its location is set by the
Within ADR base, there can be multiple ADR homes, where each ADR home is the root directory for all diagnostic data—traces, dumps, the alert log, and so on—for a particular instance of a particular Oracle product or component. For example, in an Oracle Real Application Clusters environment with Oracle ASM, each database instance, Oracle ASM instance, and listener has an ADR home. ADR homes reside in ADR base subdirectories that are named according to the product or component type. Figure 9-1 illustrates these top-level subdirectories. Note: Additional subdirectories might be created in the ADR depending on your configuration. Some products automatically purge expired diagnostic data from ADR. For other products, you can use the ADRCI utility The location of each ADR home is given by the following path, which starts at the ADR base directory: diag/product_type/product_id/instance_id As an example, Table 9-1 lists the values of the various path components for an Oracle Database instance. Table 9-1 ADR Home Path Components for Oracle Database
For example, for a database with a SID and database unique name both equal to ADR_base/diag/rdbms/orclbi/orclbi/
Similarly, the ADR home path for the Oracle ASM instance in a single-instance environment would be: ADR_base/diag/asm/+asm/+asm/ ADR Home Subdirectories Within each ADR home directory are subdirectories that contain the diagnostic data. Table 9-2 lists some of these subdirectories and their contents. Table 9-2 ADR Home Subdirectories
Figure 9-2 illustrates the complete directory hierarchy of the ADR for a database instance. ADR in an Oracle Clusterware Environment Oracle Clusterware uses ADR and has its own Oracle home and Oracle base. The ADR directory structure for Oracle Clusterware is different from that of a database instance. There is only one instance of Oracle Clusterware on a system, so Clusterware ADR homes use only a system's host name as a differentiator. When Oracle Clusterware is configured, the ADR home uses ADR_base/diag/crs/dbprod01/crs/ ADR in an Oracle Real Application Clusters Environment In an Oracle Real Application Clusters (Oracle RAC) environment, each node can have ADR base on its own local storage, or ADR base can be set to a location on shared storage. You can use ADRCI to view aggregated diagnostic data from all instances on a single report. ADR in Oracle Client Each installation of Oracle Client includes an ADR for diagnostic data associated with critical failures in any of the Oracle Client components. The ADRCI utility is installed with Oracle Client so that you can examine diagnostic data and package it to enable it for upload to Oracle Support. Viewing ADR Locations with the V$DIAG_INFO View The SELECT * FROM V$DIAG_INFO; INST_ID NAME VALUE ------- --------------------- ------------------------------------------------------------- 1 Diag Enabled TRUE 1 ADR Base /u01/oracle 1 ADR Home /u01/oracle/diag/rdbms/orclbi/orclbi 1 Diag Trace /u01/oracle/diag/rdbms/orclbi/orclbi/trace 1 Diag Alert /u01/oracle/diag/rdbms/orclbi/orclbi/alert 1 Diag Incident /u01/oracle/diag/rdbms/orclbi/orclbi/incident 1 Diag Cdump /u01/oracle/diag/rdbms/orclbi/orclbi/cdump 1 Health Monitor /u01/oracle/diag/rdbms/orclbi/orclbi/hm 1 Default Trace File /u01/oracle/diag/rdbms/orclbi/orclbi/trace/orcl_ora_22769.trc 1 Active Problem Count 8 1 Active Incident Count 20 The following table describes some of the information displayed by this view. Table 9-3 Data in the V$DIAG_INFO View
Viewing Critical Errors with the V$DIAG_CRITICAL_ERROR View The The following example shows the
output for the SELECT * FROM V$DIAG_CRITICAL_ERROR; FACILITY ERROR ---------- ---------------------------------------------------------------- ORA 7445 ORA 4030 ORA 4031 ORA 29740 ORA 255 ORA 355 ORA 356 ORA 239 ORA 240 ORA 494 ORA 3137 ORA 227 ORA 353 ORA 1578 ORA 32701 ORA 32703 ORA 29770 ORA 29771 ORA 445 ORA 25319 OCI 3106 OCI 3113 OCI 3135 The following table describes the information displayed by this view. Table 9-4 Data in the V$DIAG_CRITICAL_ERROR View
9.2 About Investigating, Reporting, and Resolving a ProblemYou can use the Enterprise Manager Support Workbench (Support Workbench) to investigate and report a problem (critical error), and in some cases, resolve the problem. You can use a "roadmap" that summarizes the typical set of tasks that you must perform. Note: The tasks described in this section are all Cloud Control–based. You can also accomplish all of these tasks (or their equivalents) with the
9.2.1 Roadmap — Investigating, Reporting, and Resolving a ProblemYou can begin investigating a problem by starting from the Support Workbench home page in Cloud Control. However, the more typical workflow begins with a critical error alert on the Database Home page. Figure 9-3 illustrates the tasks that you complete to investigate, report, and in some cases, resolve a problem. The following are task descriptions. Subsequent sections provide details for each task.
9.2.2 Task 1: View Critical Error Alerts in Cloud ControlYou begin the process of investigating problems (critical errors) by reviewing critical error alerts on the Database Home page or Oracle Automatic Storage Management Home page. To view critical error alerts:
9.2.3 Task 2: View Problem DetailsYou continue your investigation from the Incident Manager Problem Details page. To view problem details:
9.2.4 Task 3: (Optional) Gather Additional Diagnostic InformationYou can perform the following activities to gather additional diagnostic information for a problem. This additional information is then automatically included in the diagnostic data uploaded to Oracle Support. If you are unsure about performing these activities, then check with your Oracle Support representative.
9.2.5 Task 4: (Optional) Create a Service RequestAt this point, you can create an Oracle Support service request and record the service request number with the problem information. To create a service request:
9.2.6 Task 5: Package and Upload Diagnostic Data to Oracle SupportFor this task, you use the quick packaging process of the Support Workbench to package and upload the diagnostic information for the problem to Oracle Support. Quick packaging has a minimum of steps, organized in a guided workflow (a wizard). The wizard assists you with creating an incident package (package) for a single problem, creating a zip file from the package, and uploading the file. With quick packaging, you are not able to edit or otherwise customize the diagnostic information that is uploaded. However, quick packaging is the more direct, straightforward method to package and upload diagnostic data. To edit or remove sensitive data from the diagnostic information, enclose additional user files (such as application configuration files or scripts), or perform other customizations before uploading, you must use the custom packaging process, which is a more manual process and has more steps. See "Reporting Problems" for instructions. If you choose to follow those instructions instead of the instructions here in Task 5, do so now and then continue with Task 6: Track the Service Request and Implement Any Repairs when you are finished. Note: The Support Workbench uses Oracle Configuration Manager to upload the diagnostic data. If Oracle Configuration Manager is not installed or properly configured, the upload may fail. In this case, a message is displayed with a request that you upload the file to Oracle Support manually. You can upload manually with My Oracle Support. For more information about Oracle Configuration Manager, see Oracle Configuration Manager Installation and Administration Guide. To package and upload diagnostic data to Oracle Support:
When the Quick Packaging wizard is complete, if a new draft service request was created, then the confirmation message contains a link to the draft service request in My Oracle Support in Cloud Control. You can review and edit the service request by clicking the link. The package created by the Quick Packaging wizard remains available in the Support Workbench. You can then modify it with custom packaging operations (such as adding new incidents) and upload again at a later time. See "Viewing and Modifying Incident Packages". 9.2.7 Task 6: Track the Service Request and Implement Any RepairsAfter uploading diagnostic information to Oracle Support, you might perform various activities to track the service request, to collect additional diagnostic information, and to implement repairs. Among these activities are the following:
9.3 Diagnosing ProblemsThis section describes various methods to diagnose problems in an Oracle database.
9.3.1 Identifying Problems ReactivelyThis section describes how to identify Oracle database problems reactively.
9.3.1.1 Viewing Problems with the Support WorkbenchYou can use the Support Workbench home page in Cloud Control to view all the problems or the problems in a specific time period. To access the Support Workbench home page (database or Oracle ASM):
To view problems and incidents:
To view details for a particular problem:
9.3.1.2 Adding Problems Manually to the Automatic Diagnostic RepositoryYou can use Support Workbench in Cloud Control to manually add a problem to the ADR. System-generated problems, such as critical errors generated internally to the database are automatically added to the Automatic Diagnostic Repository (ADR) and tracked in the Support Workbench. From the Support Workbench, you can gather additional diagnostic data on these problems, upload diagnostic data to Oracle Support, and in some cases, resolve the problems, all with the easy-to-use workflow that is explained in "About Investigating, Reporting, and Resolving a Problem". There may be a situation in which you want to manually add a problem that you noticed to the ADR, so that you can put that problem through that same workflow. An example of such a situation might be a global database performance problem that was not diagnosed by Automatic Diagnostic Database Monitor (ADDM). The Support Workbench includes a mechanism for you to create and work with such a user-reported problem. To create a user-reported problem:
9.3.1.3 Creating Incidents ManuallyYou can create incidents manually by using the Automatic Diagnostic Repository Command Interpreter (ADRCI) utility. To create an incident manually by using the ADRCI utility:
9.3.2 Identifying Problems Proactively with Health MonitorYou can run diagnostic checks on a database with Health Monitor.
9.3.2.1 About Health MonitorOracle Database includes a framework called Health Monitor for running diagnostic checks on the database.
9.3.2.1.1 About Health Monitor Checks Health Monitor checks (also known as checkers, health checks, or checks) examine various layers and components of the database. Health checks detect file corruptions, physical and logical block corruptions, undo and redo corruptions, data dictionary corruptions, and more. The health checks generate reports of their findings and, in many cases, recommendations for resolving problems. Health checks can be run in two ways:
Health Monitor checks store findings, recommendations, and other information in the Automatic Diagnostic Repository (ADR). Health checks can run in two modes:
All the health checks can be run in DB-online mode. Only the Redo Integrity Check and the DB Structure Integrity Check can be used in DB-offline mode. 9.3.2.1.2 Types of Health Checks Health monitor runs several different types of checks. Health monitor runs the following checks:
9.3.2.2 Running Health Checks ManuallyHealth Monitor can run health checks manually either by using
the
9.3.2.2.1 Running Health Checks Using the DBMS_HM PL/SQL Package The
Most health checks accept input parameters. You can view parameter names and descriptions with the SELECT c.name check_name, p.name parameter_name, p.type, p.default_value, p.description FROM v$hm_check_param p, v$hm_check c WHERE p.check_id = c.id and c.internal_check = 'N' ORDER BY c.name; Input parameters are passed in the BEGIN DBMS_HM.RUN_CHECK ( check_name => 'Transaction Integrity Check', run_name => 'my_run', input_params => 'TXN_ID=7.33.2'); END; / See Also:
9.3.2.2.2 Running Health Checks Using Cloud Control Cloud Control provides an interface for running Health Monitor checkers. To run a Health Monitor Checker using Cloud Control:
9.3.2.3 Viewing Checker ReportsAfter a checker has run, you can view a report of its execution. The report contains findings, recommendations, and other information. You can view reports using Cloud Control, the ADRCI utility, or the
9.3.2.3.1 About Viewing Checker Reports Results of checker runs (findings, recommendations, and other information) are stored in the ADR, but reports are not generated immediately.
When you request a report with the The preferred method to view checker reports is with Cloud Control. 9.3.2.3.2 Viewing Reports Using Cloud Control You can also view Health Monitor reports and findings for a given checker run using Cloud Control. To view run findings using Cloud Control:
9.3.2.3.3 Viewing Reports Using DBMS_HM You can view Health Monitor checker reports with the This function enables you to request HTML, XML, or text formatting. The default format is text, as shown in the following SQL*Plus example: SET LONG 100000 SET LONGCHUNKSIZE 1000 SET PAGESIZE 1000 SET LINESIZE 512 SELECT DBMS_HM.GET_RUN_REPORT('HM_RUN_1061') FROM DUAL; DBMS_HM.GET_RUN_REPORT('HM_RUN_1061') ----------------------------------------------------------------------- Run Name : HM_RUN_1061 Run Id : 1061 Check Name : Data Block Integrity Check Mode : REACTIVE Status : COMPLETED Start Time : 2007-05-12 22:11:02.032292 -07:00 End Time : 2007-05-12 22:11:20.835135 -07:00 Error Encountered : 0 Source Incident Id : 7418 Number of Incidents Created : 0 Input Parameters for the Run BLC_DF_NUM=1 BLC_BL_NUM=64349 Run Findings And Recommendations Finding Finding Name : Media Block Corruption Finding ID : 1065 Type : FAILURE Status : OPEN Priority : HIGH Message : Block 64349 in datafile 1: '/u01/app/oracle/dbs/t_db1.f' is media corrupt Message : Object BMRTEST1 owned by SYS might be unavailable Finding Finding Name : Media Block Corruption Finding ID : 1071 Type : FAILURE Status : OPEN Priority : HIGH Message : Block 64351 in datafile 1: '/u01/app/oracle/dbs/t_db1.f' is media corrupt Message : Object BMRTEST2 owned by SYS might be unavailable 9.3.2.3.4 Viewing Reports Using the ADRCI Utility You can create and view Health Monitor checker reports using the ADRCI utility. To create and view a checker report using ADRCI:
9.3.2.4 Health Monitor ViewsInstead of requesting a checker report, you can view the results of a specific checker run by directly querying the ADR data from which reports are created. This data is available through the views The following example queries the SELECT run_id, name, check_name, run_mode, src_incident FROM v$hm_run; RUN_ID NAME CHECK_NAME RUN_MODE SRC_INCIDENT ---------- ------------ ---------------------------------- -------- ------------ 1 HM_RUN_1 DB Structure Integrity Check REACTIVE 0 101 HM_RUN_101 Transaction Integrity Check REACTIVE 6073 121 TXNCHK Transaction Integrity Check MANUAL 0 181 HMR_tab$ Dictionary Integrity Check MANUAL 0 . . . 981 Proct_ts$ Dictionary Integrity Check MANUAL 0 1041 HM_RUN_1041 DB Structure Integrity Check REACTIVE 0 1061 HM_RUN_1061 Data Block Integrity Check REACTIVE 7418 The next example queries the
SELECT type, description FROM v$hm_finding WHERE run_id = 1061; TYPE DESCRIPTION ------------- ----------------------------------------- FAILURE Block 64349 in datafile 1: '/u01/app/orac le/dbs/t_db1.f' is media corrupt FAILURE Block 64351 in datafile 1: '/u01/app/orac le/dbs/t_db1.f' is media corrupt See Also:
9.3.2.5 Health Check Parameters ReferenceSome health checks require parameters. Parameters with a default value of Table 9-6 Parameters for Data Block Integrity Check
Table 9-7 Parameters for Redo Integrity Check
Table 9-8 Parameters for Undo Segment Integrity Check
Table 9-9 Parameters for Transaction Integrity Check
Table 9-10 Parameters for Dictionary Integrity Check
9.3.3 Gathering Additional Diagnostic DataThis section describes how to gather additional diagnostic data using alert log and trace files.
9.3.3.1 Viewing the Alert LogYou can view the alert log with a text editor, with Cloud Control, or with the ADRCI utility. To view the alert log with Cloud Control:
To view the alert log with a text editor:
See Also: Oracle Database Utilities for information about using the ADRCI utility to view a text version of the alert log (with XML tags stripped) and to run queries against the alert log 9.3.3.2 Finding Trace FilesTrace files are stored in the Automatic Diagnostic Repository (ADR), in the To find the trace file for your current session:
To find all trace files for the current instance:
To determine the trace file for each Oracle Database process:
See Also:
9.3.4 Creating Test Cases with SQL Test Case BuilderSQL Test Case Builder is a tool that automatically gathers information needed to reproduce the problem in a different database instance. A SQL test case is a set of information that enables a developer to reproduce the execution plan for a specific SQL statement that has encountered a performance problem. This section contains the following topics:
9.3.4.1 Purpose of SQL Test Case BuilderSQL Test Case Builder automates the process of gathering and reproducing information about a problem and the environment in which it occurred. For most SQL components, obtaining a reproducible test case is the most important factor in bug resolution speed. It is also the longest and most painful step for users. The goal of SQL Test Case Builder is to gather as much as information related to an SQL incident as possible, and then package it in a way that enables Oracle staff to reproduce the problem on a different system. The output of SQL Test Case Builder is a set of scripts in a predefined directory. These scripts contain the commands required to re-create all the necessary objects and the environment on another database instance. After the test case is ready, you can create a zip file of the directory and move it to another database, or upload the file to Oracle Support. 9.3.4.2 Concepts for SQL Test Case BuilderKey concepts for SQL Test Case Builder include SQL incidents, types of information recorded, and the form of the output. This section contains the following topics:
9.3.4.2.1 SQL Incidents In the fault diagnosability infrastructure of Oracle Database, an incident is a single occurrence of a problem. A SQL incident is a SQL-related problem. When a problem (critical error) occurs multiple times, the database creates an incident for each occurrence. Incidents are timestamped and tracked in the Automatic Diagnostic Repository (ADR). Each incident has a numeric incident ID, which is unique within the ADR. SQL Test Case Builder is accessible any time on the command line. In Oracle Enterprise Manager Cloud Control (Cloud Control), the SQL Test Case pages are only available after a SQL incident is found. 9.3.4.2.2 What SQL Test Case Builder Captures SQL Test Case Builder captures permanent information about a SQL query and its environment. The information includes the query being executed, table and index definitions (but not the actual data), PL/SQL packages and program units, optimizer statistics, SQL plan baselines, and initialization parameter settings. Starting with Oracle Database 12c, SQL Test Case Builder also captures and replays transient information, including information only available as part of statement execution. SQL Test Case Builder supports the following:
See Also:
9.3.4.2.3 Output of SQL Test Case Builder The output of SQL Test Case Builder is a set of files that contains commands required to re-create the environment and all necessary objects. By default, SQL Test Case Builder stores the files in the following directory, where incnum refers to the incident number and runnum refers to the run number:
For example, a valid output file name could be as follows:
You can also specify a particular directory for storing the SQL Test Case Builder files by creating a directory object with the name
Note: The
database administrator must have read and write access permissions to the operating system directory specified in the directory object You can also specify a name for a test case using the If you do not specify a test case name, then a default test case name having the following format is used by SQL Test Case Builder:
Here, connectionId is the database connection ID, sqlId is the SQL statement ID, sequenceNumber is the internal sequence number, and sessionId is the database session ID. You can also specify any additional information to include in the output of SQL Test Case Builder using the
You can view
the information about all the test cases generated by SQL Test Case Builder by querying the
Note: The See Also:
9.3.4.3 User Interfaces for SQL Test Case BuilderYou can access SQL Test Case Builder either through Cloud Control or using PL/SQL on the command line. This section contains the following topics:
9.3.4.3.1 Graphical Interface for SQL Test Case Builder Within Cloud Control, you can access SQL Test Case Builder from the Incident Manager page or the Support Workbench page. This section contains the following topics:
9.3.4.3.1.1 Accessing the Incident Manager From the Incidents and Problems section on the Database Home page, you can navigate to the Incident Manager. To access the Incident Manager:
9.3.4.3.1.2 Accessing the Support Workbench From the Oracle Database menu, you can navigate to the Support Workbench. To access the Support Workbench:
9.3.4.3.2 Command-Line Interface for SQL Test Case Builder The This package consists of various subprograms for the SQL Test Case Builder, some of which are listed in the following table. Table 9-11 SQL Test Case Functions in the DBMS_SQLDIAG Package
9.3.4.4 Running SQL Test Case BuilderYou can run SQL Test Case Builder using Cloud Control. Assumptions This tutorial assumes the following:
To run SQL Test Case Builder:
9.4 Reporting ProblemsUsing the Enterprise Manager Support Workbench (Support Workbench), you can create, edit, and upload custom incident packages. With custom incident packages, you have fine control over the diagnostic data that you send to Oracle Support.
9.4.1 Incident PackagesYou can collect diagnostic data into an intermediate logical structure called an incident package (package).
9.4.1.1 About Incident PackagesFor the customized approach to uploading diagnostic data to Oracle Support, you first collect the data into an intermediate logical structure called an incident package (package). A package is a collection of metadata that is stored in the Automatic Diagnostic Repository (ADR) and that points to diagnostic data files and other files both in and out of the ADR. When you create a package, you select one or more problems to add to the package. The Support Workbench then automatically adds to the package the problem information, incident information, and diagnostic data (such as trace files and dumps) associated with the selected problems. Because a problem can have many incidents (many occurrences of the same problem), by default only the first three and last three incidents for each problem are added to the package, excluding any incidents that are over 90 days old. You can change these default numbers on the Incident Packaging Configuration page of the Support Workbench. After the package is created, you can add any type of external file to the package, remove selected files from the package, or edit selected files in the package to remove sensitive data. As you add and remove package contents, only the package metadata is modified. When you are ready to upload the diagnostic data to Oracle Support, you first create a zip file that contains all the files referenced by the package metadata. You then upload the zip file through Oracle Configuration Manager. Note: If you do not have Oracle Configuration Manager installed and properly configured, then you must upload the zip file manually through My Oracle Support. 9.4.1.2 About Correlated Diagnostic Data in Incident PackagesTo diagnose problem, it is sometimes necessary to examine not only diagnostic data that is directly related to the problem, but also diagnostic data that is correlated with the directly related data. Diagnostic data can be correlated by time, by process ID, or by other criteria. For example, when examining an incident, it may be helpful to also examine an incident that occurred five minutes after the original incident. Similarly, while it is clear that the diagnostic data for an incident should include the trace file for the Oracle Database process that was running when the incident occurred, it might be helpful to also include trace files for other processes that are related to the original process. Thus, when problems and their associated incidents are added to a package, any correlated incidents are added at the same time, with their associated trace files. During the process of creating the physical file for a package, the Support Workbench calls upon the Incident Packaging Service to finalize the package. Finalizing means adding to the package any additional trace files that are correlated by time to incidents in the package, and adding other diagnostic information such as the alert log, health checker reports, SQL test cases, configuration information, and so on. Therefore, the number of files in the zip file may be greater than the number of files that the Support Workbench had previously displayed as the package contents. The Incident Packaging Service follows a set of rules to determine the trace files in the ADR that are correlated to existing package data. You can modify some of those rules in the Incident Packaging Configuration page in Cloud Control. Because both initial package data and added correlated data may contain sensitive information, it is important to have an opportunity to remove or edit files that contain this information before uploading to Oracle Support. For this reason, the Support Workbench enables you to run a command that finalizes the package as a separate operation. After manually finalizing a package, you can examine the package contents, remove or edit files, and then generate and upload a zip file. Note: Finalizing a package does not mean closing it to further modifications. You can continue to add diagnostic data to a finalized package. You can also finalize the same package multiple times. Each time that you finalize, any new correlated data is added. 9.4.1.3 About Quick Packaging and Custom PackagingThe Support Workbench provides two methods for creating and uploading an incident package: the quick packaging method and the custom packaging method. Quick Packaging—This is the more automated method with a minimum of steps, organized in a guided workflow (a wizard). You select a single problem, provide a package name and description, and then schedule upload of the package contents, either immediately or at a specified date and time. The Support Workbench automatically places diagnostic data related to the problem into the package, finalizes the package, creates the zip file, and then uploads the file. With this method, you do not have the opportunity to add, edit, or remove package files or add other diagnostic data such as SQL test cases. However, it is the simplest and quickest way to get first-failure diagnostic data to Oracle Support. Quick packaging is the method used in the workflow described in "About Investigating, Reporting, and Resolving a Problem". When quick packaging is complete, the package that was created by the wizard remains. You can then modify the package with custom packaging operations at a later time and manually reupload. Custom Packaging—This is the more manual method, with more steps. It is intended for expert Support Workbench users who want more control over the packaging process. With custom packaging, you can create a new package with one or more problems, or you can add one or more problems to an existing package. You can then perform a variety of operations on the new or updated package, including:
You might conduct these operations over several days, before deciding that you have enough diagnostic information to send to Oracle Support. With custom packaging, you create the zip file and request the upload to Oracle Support as two separate steps. Each of these steps can be performed immediately or scheduled for a future date and time. 9.4.1.4 About Correlated PackagesCorrelated packages provide a means of packaging and uploading diagnostic data for related problems. A database instance problem can have related problems in other database instances or in Oracle Automatic Storage Management instances. After you create and upload a package for one or more database instance problems (the "main package"), you can create and upload one or more correlated packages, each with one or more related problems. You can accomplish this only with the custom packaging workflow in Support Workbench. 9.4.2 Packaging and Uploading Problems with Custom PackagingYou use Support Workbench (Support Workbench) to create and upload custom incident packages (packages). Before uploading, you can manually add, edit, and remove diagnostic data files from the package. To package and upload problems with custom packaging:
See Also:
9.4.3 Viewing and Modifying Incident PackagesAfter creating an incident package with the custom packaging method, you can view or modify the contents of the package before uploading the package to Oracle Support. In addition, after using the quick packaging method to package and upload diagnostic data, you can view or modify the contents of the package that the Support Workbench created, and then reupload the package. To modify a package, you choose from among a selection of packaging tasks, most of which are available from the Customize Package page.
9.4.3.1 Viewing Package DetailsThe Package Details page contains information about the incidents, trace files, and other files in a package, and enables you to view and add to the package activity log. To view package details:
9.4.3.2 Accessing the Customize Package PageThe Customize Package page is used to perform various packaging tasks, such as adding and removing problems; adding, removing, and scrubbing (editing) package files; and generating and uploading the package zip file. To access the Customize Package page:
9.4.3.3 Editing Incident Package Files (Copying Out and In)The Support Workbench enables you to edit one or more files in an incident package. You may want to do this to delete or overwrite sensitive data in the files. To edit package files, you must first copy the files out of the package into a designated directory, edit the files with a text editor or other utility, and then copy the files back into the package, overwriting the original package files. The following procedure assumes that the package is already created and contains diagnostic data. To edit incident package files:
9.4.3.4 Adding an External File to an Incident PackageYou can add any type of external file to an incident package. To add an external file to an incident package:
9.4.3.5 Removing Incident Package FilesYou can remove one or more files of any type from the incident package. To remove incident package files:
9.4.3.6 Viewing and Updating the Incident Package Activity LogThe Support Workbench maintains an activity log for each incident package. Most activities that you perform on a package, such as adding or removing files or creating a package zip file, are recorded in the log. You can also add your own notes to the log. This is especially useful if multiple database administrators are working with packages. To view and update the incident package activity log:
9.4.4 Creating, Editing, and Uploading Correlated PackagesAfter you upload a package to Oracle Support, you can create and upload one or more correlated packages. This is recommended if critical alerts appeared in the Related Alerts section of the Database Home page. The correlated packages are associated with the original package, also known as the main package. The main package contains problems that occurred in a database instance. Correlated packages contain problems that occurred on other instances (Oracle ASM instances or other database instances) and that are related problems for the problems in the main package. There can be only one correlated package for each related instance. To create, edit, and upload a correlated package:
9.4.5 Deleting Correlated PackagesYou delete a correlated package with the Support Workbench for the target for which you created the package. For example, if you created a correlated package for an Oracle ASM instance target, access the Support Workbench for that Oracle ASM instance. To delete a correlated package:
9.4.6 Setting Incident Packaging PreferencesYou can set incident packaging preferences. Examples of incident packaging preferences include the number of days to retain incident information, and the number of leading and trailing incidents to include in a package for each problem. By default, if a problem has many incidents, only the first three and last three incidents are packaged. You can change these and other incident packaging preferences with Cloud Control or with the ADRCI utility. To set incident packaging preferences with Cloud Control:
See Also:
9.5 Resolving ProblemsThis section describes how to resolve database problems using advisor tools, such as SQL Repair Advisor and Data Recovery Advisor, and the resource management tools, such as the Resource Manager and related APIs.
9.5.1 Repairing SQL Failures with the SQL Repair AdvisorIn the rare case that a SQL statement fails with a critical error, you can run the SQL Repair Advisor to try to repair the failed statement.
9.5.1.1 About the SQL Repair AdvisorYou run the SQL Repair Advisor after a SQL statement fails with a critical error. The advisor analyzes the statement and in many cases recommends a patch to repair the statement. If you implement the recommendation, the applied SQL patch circumvents the failure by causing the query optimizer to choose an alternate execution plan for future executions. You can run the SQL Repair Advisor using either Cloud Control or 9.5.1.2 Running the SQL Repair Advisor Using Cloud ControlYou can run the SQL Repair Advisor from the Problem Details page of the Support Workbench of Cloud Control. To run the SQL Repair Advisor using Cloud Control:
9.5.1.3 Running the SQL Repair Advisor Using the DBMS_SQLDIAG Package SubprogramsYou
can run the SQL Repair Advisor using the Typically, you do so when you are notified of a critical error caused by your SQL statement and that you followed the workflow described in "About Investigating, Reporting, and Resolving a Problem". You run the SQL Repair Advisor by creating and executing a diagnostic task using the Note: Starting with Oracle Database 19c, you can also use a single subprogram To run the SQL Repair Advisor using the DBMS_SQLDIAG package subprograms:
9.5.1.4 Viewing, Disabling, or Removing a SQL Patch Using Cloud ControlAfter you apply a SQL patch with the SQL Repair Advisor, you can view it to confirm its presence, disable it, or remove it using Cloud Control. One reason to disable or remove a patch is if you install a later release of Oracle Database that fixes the bug that caused the failure in the patched SQL statement. To view, disable, or remove a SQL patch using Cloud Control:
9.5.1.5 Disabling or Removing a SQL Patch Using DBMS_SQLDIAG Package SubprogramsAfter you apply a SQL patch with the SQL Repair Advisor, you can disable or remove it using the To disable a SQL patch using DBMS_SQLDIAG package subprogram: Run the procedure The following example disables the SQL patch
To remove a SQL patch using DBMS_SQLDIAG package subprogram: Run the procedure The following example removes the SQL patch
See Also:
9.5.1.6 Exporting and Importing a Patch Using DBMS_SQLDIAG Package SubprogramsA patch created
using the SQL Repair Advisor can be exported out of one system and imported into another system using Patches can be exported out of one system and imported into another by using a staging table. Like with SQL diagnosis sets, the operation of inserting into the staging table is called as "pack", and the operation of creating patches from staging table data is called as "unpack". To export and import a patch using the DBMS_SQLDIAG package subprograms:
9.5.2 Repairing Data Corruptions with the Data Recovery AdvisorYou use the Data Recovery Advisor to repair data block corruptions, undo corruptions, data dictionary corruptions, and more. The Data Recovery Advisor integrates with the Enterprise Manager Support Workbench (Support Workbench), with the Health Monitor, and with the RMAN utility to display data corruption problems, assess the extent of each problem (critical, high priority, low priority), describe the impact of a problem, recommend repair options, conduct a feasibility check of the customer-chosen option, and automate the repair process. The Cloud Control online help provides details on how to use the Data Recovery Advisor. This section describes how to access the advisor from the Support Workbench. The Data Recovery Advisor is automatically recommended by and accessible from the Support Workbench when you are viewing health checker findings that are related to a data corruption or other data failure. The Data Recovery Advisor is also available from the Advisor Central page. To access the Data Recovery Advisor in Cloud Control:
9.5.3 Quarantine for Execution Plans for SQL Statements Consuming Excessive System ResourcesStarting with Oracle Database 19c, you can use the SQL Quarantine infrastructure (SQL Quarantine) to quarantine execution plans for SQL statements that are terminated by the Resource Manager for consuming excessive system resources in an Oracle database. An individual SQL statement may have multiple execution plans, and if it attempts to use the execution plan that is quarantined, then that SQL statement is not allowed to run, thus preventing database performance degradation.
9.5.3.1 About Quarantine for Execution Plans for SQL StatementsYou can use the SQL Quarantine infrastructure (SQL Quarantine) to quarantine execution plans for SQL statements that are terminated by the Resource Manager for consuming excessive system resources in an Oracle database. The quarantined execution plans for such SQL statements are not allowed to run again, thus preventing database performance degradation. Using the Resource Manager, you can configure limits for SQL statements for consuming system resources (Resource Manager thresholds). The Resource Manager terminates SQL statements that exceed the Resource Manager thresholds. In the earlier Oracle Database releases, if a SQL statement that is terminated by the Resource Manager runs again, the Resource Manager allows it to run again and terminates it again when it exceeds the Resource Manager thresholds. Thus, it is a waste of system resources to allow such SQL statements to run again. Starting with Oracle Database 19c, you can use SQL Quarantine to automatically quarantine execution plans of SQL statements terminated by the Resource Manager, so that they are not allowed to run again. SQL Quarantine information is periodically persisted to the data dictionary. When resource manager terminates a SQL statement, it may be several minutes before the statement is quarantined. Additionally, SQL Quarantine can
also be used to create quarantine configurations for execution plans of SQL statements by specifying thresholds for consuming various system resources (similar to the Resource Manager thresholds) using the The following are the steps to manually set quarantine thresholds for an execution plan for a SQL statement using the
You can also perform the
following operations related to quarantine configurations using the
Note:
For example, consider a resource plan of the Resource Manager that limits execution time for SQL statements to be 10 seconds (Resource Manager threshold). Consider a SQL statement Q1 for which this resource plan is application. When Q1 exceeds execution time of 10 seconds, it gets terminated by the Resource Manager. SQL Quarantine then creates a quarantine configuration for Q1 specific to that execution plan and stores this execution time of 10 seconds as a quarantine threshold in the quarantine configuration. If Q1 is executed again with the same execution plan and the Resource Manager threshold is still 10 seconds, then SQL Quarantine does not allow Q1 to execute, because it refers to the quarantine threshold of 10 seconds to determine that Q1 will be eventually terminated by the Resource Manager as Q1 takes at least 10 seconds to execute. If the Resource Manager threshold is changed to 5 seconds and Q1 is executed again with the same execution plan, then SQL Quarantine does not allow Q1 to execute, because it refers to the quarantine threshold of 10 seconds to determine that Q1 will be eventually terminated by the Resource Manager as Q1 takes at least 10 seconds to execute. If the Resource Manager threshold is changed to 15 seconds and Q1 is executed again with the same execution plan, then SQL quarantine allows Q1 to execute, because it refers to the quarantine threshold of 10 seconds to determine that Q1 takes at least 10 seconds to execute, but there is a possibility that Q1 may complete its execution within 15 seconds. Note: A quarantine threshold is specific to an execution plan for a SQL statement, and it is automatically set by SQL Quarantine based on the Resource Manager threshold that is exceeded by the SQL statement and its execution plan. You can also manually set a quarantine threshold for a specific execution plan for a SQL statement by using the See Also:
9.5.3.2 Creating a Quarantine Configuration for an Execution Plan of a SQL StatementYou can create a quarantine configuration for an execution plan of a SQL statement using any of these The
following example creates a quarantine configuration for an execution plan having the hash value of
If you do not specify an execution plan or specify it as The following example creates a quarantine
configuration for all the execution plans for a SQL statement having the SQL ID of
The following example creates a quarantine configuration for all the execution plans for a SQL statement
The See Also:
9.5.3.3 Specifying Quarantine Thresholds in a Quarantine ConfigurationAfter
creating a quarantine configuration for an execution plan for a SQL statement, you can specify quarantine thresholds for it using the You can specify quarantine thresholds for the following resources
in a quarantine configuration using the
In the following example, the quarantine threshold specified for CPU time is 5 seconds and elapsed time is 10 seconds for the quarantine configuration
When the SQL statement is executed using the execution plan specified in this quarantine configuration, and if the Resource Manager threshold for CPU time is 5 seconds or less, or elapsed time is 10 seconds or less, then the SQL statement is not allowed to run. Note: If any of the Resource Manager thresholds is equal to or less than a quarantine threshold specified in a SQL statement's quarantine configuration, then that SQL statement is not allowed to run, if it uses the execution plan specified in its quarantine configuration. Querying quarantine thresholds for a quarantine configuration You can query a quarantine threshold for a quarantine configuration using the
Deleting quarantine thresholds from a quarantine configuration You can delete a quarantine threshold from a quarantine configuration by specifying
9.5.3.4 Enabling and Disabling a Quarantine ConfigurationYou can enable or disable a quarantine configuration using the The following example disables the quarantine configuration having the name
The following example enables the quarantine configuration having the name
9.5.3.5 Viewing the Details of a Quarantine ConfigurationYou can query the The
9.5.3.6 Deleting a Quarantine ConfigurationThe unused quarantine configurations are automatically purged or deleted after 53 weeks. You can also delete a quarantine
configuration using the The following example disables automatic deletion of the quarantine configuration
The following example enables automatic deletion of the quarantine configuration
The following example deletes the quarantine
configuration
9.5.3.7 Viewing the Details of Quarantined Execution Plans of SQL StatementsYou can query the The following columns of the
9.5.3.8 Transferring Quarantine Configurations from One Database to Another DatabaseYou can transfer quarantine
configurations from one database to another database using the For example, you may have tested the quarantine configurations on a test database and confirmed that they have performed well. You may then want to load these quarantine configurations into a production database. The following example describes the steps to transfer quarantine configurations from one database (source
database) to another database (destination database) using the
9.5.3.9 Example: Quarantine for an Execution Plan of a SQL Statement Consuming Excessive System ResourcesThis example shows how an execution plan of a SQL statement is quarantined when it exceeds a resource consumption limit configured using the Resource Manager.
See Also:
What Windows 10 feature simplifies the configuration?Purview simplifies the configuration set-up and provides an advanced set of capabilities. Applies to: Windows 10. Windows 11.
Which utility should be used to monitor a Windows 10 computer's physical resources?On Windows 10, you can use Performance Monitor to analyze data, such as processor, hard drive, memory, and network usage, but first, you must know how to open the tool. Here are three ways to open Performance Monitor: Open Start, do a search for Performance Monitor, and click the result.
Which Windows 10 feature captures a screenshot each time the user clicks a screen item?Use the Snipping Tool
The Windows Snipping Tool captures all or part of your PC screen. After you capture a snip, it's automatically copied to the Snipping Tool window. From there you can edit, save, or share the snip. Press Windows logo key+Shift+S.
Which Windows 10 Service controls the flow of network traffic in Windows 10 quizlet?remote computer. —This service controls the flow of network traffic in Windows 10. Service (QoS). much data can be sent and the format of the data as it crosses the network.
|