SQL Quarantine In Exadata

SQL Quarantine In Exadata

 SQL Quarantine in Exadata may occur when the query got crashed while using a smart scan feature at cell storage server level and there can be many reasons of query’s crashed it may occur due to ORA-600 or ORA-7445. The oracle Exadata software quarantine a SQL statement to prevent software crash and cell storage server availability. If the quarantine for a SQL query completed then the next time when the SQL query got executed it’ll not use the smart scan feature again.

 

When the SQL statement got quarantine

The oracle Exadata software doesn’t quarantine a SQL statement at a very first time basically when the same query is getting crashed 3 times while using a smart scan at cell storage server then the Exadata software marks that query as quarantine and the corresponding alert will be generated in cell storage.

 

                             How To Check if the SQL query is quarantine

 In a real time environment, we usually get the alert of SQL quarantine and meanwhile we can also check the alert history of the cell server through which we can ensure that the query is quarantine at storage server level.

 

Cellcli > list quarantine

cellcli > list alerthistory

15_1 2018-09-07T14:19:44-04:00 critical "A SQL PLAN quarantine has been added. As a result, Smart Scan is disabled for SQL statements with the quarantined SQL plan. Quarantine id : 70 Quarantine type : SQL PLAN Quarantine reason : Crash Quarantine Plan : SYSTEM Quarantine Mode : FULL_Quarantine DB Unique Name : DB1 Incident id : 9 SQLID : b4ddqpjmc704q SQL Plan details : {SQL_PLAN_HASH_VALUE=9954606, PLAN_LINE_ID=4} In addition, the following disk regions have been quarantined, and Smart Scan will be disabled for these regions: Disk Region : {Grid Disk Name=DATA01_CD_09_dbm01celadm07, offset=240745709568, size=1M} Disk Region : {Grid Disk Name=DATA01_CD_09_dbm01celadm07, offset=240744660992, size=1M} Disk Region : {Grid Disk Name=DATA01_CD_09_dbm01celadm07, offset=240747806720, size=1M} Disk Region : {Grid Disk Name=DATA01_CD_09_dbm01celadm07, offset=240746758144, size=1M} "

 

[root@exatest ~] #dcli -l root -g /u01/onecommand/cell_group cellcli -e list quarantine

 

What if the SQL query not quarantined even after generating an alert in the alert history of a cell

If the Exadata software feels that query is an ideal candidate for quarantine to protect the software crashes and cell availability however even after generating the alert the query is not getting quarantine while we check at the storage level, then we can also manually quarantine it.

 CellCLI> create quarantine quarantinetype="SQLID", sqlid='<sqlid>';

               

                How To Drop the SQL from quarantine

In general, a quarantine can be removed if the quarantined entity is not expected to cause more problem. For Example, cell offload for problem SQL statement is disabled or a database patch is applied. When the cell is patched, all the quarantine will be removed automatically, it’s not necessary to remove them manually however if there is a situation in which we’ve to drop the quarantine then we can use below command.

 cellcli -e “drop quarantine all”