Running a Database Health Check

You’d like to run a comprehensive diagnostic health check on your database. You’d like to find out if there’s any data dictionary or file corruption, as well as any other potential problems in the database.

You can use the database health monitoring infrastructure to run a health check of your database. You can run various integrity checks, such as transaction integrity checks and dictionary integrity checks.
You can get a list of all the health checks you can run by querying the V$HM_CHECK view:

SQL> select name from v$hm_check where internal_check='N';


NAME                                                            
----------------------------------------------------------------
DB Structure Integrity Check                                    
CF Block Integrity Check                                        
Data Block Integrity Check                                      
Redo Integrity Check                                            
Transaction Integrity Check                                     
Undo Segment Integrity Check                                    
Dictionary Integrity Check                                      
ASM Allocation Check                                            

8 rows selected.

Once you decide on the type of check, specify the name of the check in the DBMS_HM package's RUN_CHECK procedure, as shown here:

begin
dbms_hm.run_check('Dictionary Integrity Check','testrun10');
end;
 /
PL/SQL procedure successfully completed.
SQL>

set long 100000
set longchunksize 1000
set pagesize 1000
set linesize 512
select dbms_hm.get_run_report('testrun10') from dual;

DBMS_HM.GET_RUN_REPORT('TESTRUN10')
-----------------------------------------------------------------------------
Basic Run Information
 Run Name                     : testrun10
 Run Id                       : 21
 Check Name                   : Dictionary Integrity Check
 Mode                         : MANUAL
 Status                       : COMPLETED
 Start Time                   : 2015-07-13 14:12:02.470000 +03:00
 End Time                     : 2015-07-13 14:12:06.153000 +03:00
 Error Encountered            : 0
 Source Incident Id           : 0
 Number of Incidents Created  : 0

Input Paramters for the Run
 TABLE_NAME=ALL_CORE_TABLES
 CHECK_MASK=ALL

Run Findings And Recommendations


SQL>
-------------------------------------------------------------------------------

In this example, fortunately, there are no findings and thus no recommendations, since the dictionary health check didn’t find any problems. You can also go to Advisor Central ? Checkers and run a report from the Run Detail page for any health check you have run. Use the show hm_run, create report, and show report commands to view health check reports with the ADRCI utility. You can use the views V$HM_FINDING and V$HM_RECOMMENDATION to investigate the findings as well as the recommendations
pursuant to a health check.

Creating a SQL Test Case

You need to create a SQL test case in order to reproduce a SQL failure on a different machine, either to support your own diagnostic efforts, or to enable Oracle Support to reproduce the failure.

In order to create a SQL test case, first you must export the SQL statement along with several bits of useful information about the statement. The following example shows how to capture the SQL statement that is throwing an error. In this example, the user SH is doing the export (you can’t do the export as the user SYS).First, connect to the database as SYSDBA and create a directory to hold the test case:

SQL> conn / as sysdba
Connected.
SQL> create or replace directory TEST_DIR1 as 'c:\myora\diagnsotics\incidents\';
Directory created.
SQL> grant read,write on directory TEST_DIR1 to sh;
Grant succeeded.
SQL>

Then grant the DBA role to the user through which you will create the test case, and connect as that user:

SQL> grant dba to sh;
Grant succeeded.
SQL> conn sh/sh
Connected.

SQL> select * from my_mv where max_amount_sold >100000 order by 1;

Now you’re ready to export the SQL statement and relevant information, which you can import to a different system later on. Use the EXPORT_SQL_TESTCASE procedure to export the data, as shown here:

SQL> set serveroutput on
SQL> declare mycase clob;
2 begin
3 dbms_sqldiag.export_sql_testcase
4 (directory =>'TEST_DIR1',
5 sql_text => 'select * from my_mv where max_amount_sold >100000 order by 1',
6 user_name => 'SH',
7 exportData => TRUE,
8 testcase => mycase
9 );
10 end;
11 /
PL/SQL procedure successfully completed.
SQL>

Once the export procedure completes, you are ready to perform the import, either on the same or on a different server. The following example creates a new user named TEST, and imports the test case into that user’s schema. Here are the steps for importing the SQL statement and associated information
into a different schema.
SQL> conn /as sysdba
Connected.
SQL> create or replace directory TEST_DIR2 as 'c:\myora\diagnsotics\incidents\'; /
Directory created.
SQL> grant read,write on directory TEST_dir2 to test;
Transfer all the files in the TEST_DIR1 directory to the TEST_DIR2 directory. Then grant the DBA role to user TEST, and connect as that user:
SQL> grant dba to test;
Grant succeeded.
SQL> conn test/test
Connected.
Perform the import of the SQL data as the user TEST, by invoking the IMPORT_SQL_TESTCASE procedure, as shown here:
SQL> begin
2 dbms_sqldiag.import_sql_testcase
3 (directory=>'TEST_DIR2',
4 filename=>'oratcb1_008602000001main.xml',
5 importData=>TRUE
6 );
7 end;
8 /
PL/SQL procedure successfully completed.
SQL>
The user TEST will now have all the objects to execute the SQL statement that you want to investigate. You can verify this by issuing the original select statement. It should give you the same output as under the SH schema.

How It Works
Oracle offers the SQL Test Case Builder (TCB) to reproduce a SQL failure. You can create a test case through Enterprise Manager or through a PL/SQL package. The “Solution” section of this recipe shows how create a test case using the EXPORT_SQL_TESTCASE procedure of the DBMS_SQLDIAG package. There are several variants of this package, and our example shows how to use a SQL statement as the source for creating a SQL test case. Please review the DBMS_SQLDIAG.EXPORT_TESTCASE procedure in Oracle’s PL/SQL Packages manual for details about other options to create test cases.

Often, you’ll find yourself trying to provide a test case for Oracle, without which the Oracle Support personnel won’t be able to investigate a particular problem they are helping you with. The SQL Test Case Builder is a tool that is part of Oracle Database 11g, and its primary purpose is to help you quickly obtain a reproducible test case. The SQL Test Case Builder helps you easily capture pertinent information relating to a failed SQL statement and package it in a format that either a developer or an Oracle support person can use to reproduce the problem in a different environment.
You access the SQL Test Case Builder through the DBMS_SQLDIAG package. To create a test case, you must first export the SQL statement, including all the objects that are part of the statement, and all other related information. The export process is very similar to an Oracle export with the EXPDP command, and thus uses a directory, just as EXPDP does. Oracle creates the SQL test case as a script that contains the statements that will re-create the necessary database objects, along with associated runtime information such as statistics, which enable you to reproduce the error. The following are the various types of information captured and exported as part of the test case creation process:
• SQL text for the problem statement
• Table data—this is optional, and you can export a sample or complete data.
• The execution plan
• Optimizer statistics
• PL/SQL functions, procedure, and packages
• Bind variables
• User privileges
• SQL profiles
• Metadata for all the objects that are part of the SQL statement
• ?Dynamic sampling results
• Runtime information such as the degree of parallelism, for example
In the DBMS_SQLDIAG package, the EXPORT_SQL_TESTCASE procedure exports a SQL test case for a SQL  statement to a directory. The IMPORT_SQL_TESTCASE procedure imports the test case from a directory. In the EXPORT_SQL_TESTCASE procedure, here is what the attributes stand for:
DIRECTORY: The directory where you want to store the test case files
SQL_TEXT: The actual SQL statement that’s throwing the error
TESTCASE: The name of the test case
EXPORTDATA: By default, Oracle doesn’t export the data. You can set this parameter to TRUE in order to export the data. You can optionally limit the amount of data you want to export, by specifying a value for the Sampling
Percent attribute. The default value is 100.
The Test Case Builder automatically exports the PL/SQL package specifications but not the package body. However, you can specify that the TCB export the package body as well. The export process creates several files in the directory you specify. Of these files, the file in the format oratcb1_008602000001main.xml contains the metadata for the test case.