C Diagnostic Tips and Details

Syntax

bdschecksw Required_Params [Options]

The table below describes the requireed and optional parameters used with bdschecksw.

Exit Status

The bdschecksw script returns one of the following status codes.

Example

1. On the Oracle Database server, source the environment using the hadoop_<hcluster>.env file in $ORACLE_HOME/bigdatasql.

2. If Kerberos is enabled, kinit as the oracle Linux user on the Oracle Database server. If possible, also kinit on each of the Big Data SQL datanodes as the oracle user.

   Note:

   You can run this test without running kinit on the datanodes, but then offloading in the test will not work. You will eventually need to kinit on the datanodes in order to verify that offloading is working.

3. Create a text file and add several of lines of random text.

4. Copy the text file into hdfs as /user/oracle/test.txt.

   $ hadoop fs -put test.txt /user/oracle/test.txt
   

5. Define an Oracle external table of type ORACLE_HDFS:

   1. CREATE TABLE bds_test (line VARCHAR2(4000)) 
        ORGANIZATION EXTERNAL
           ( TYPE ORACLE_HDFS DEFAULT DIRECTORY DEFAULT_DIR LOCATION ('/user/oracle/test.txt') ) 
        REJECT LIMIT UNLIMITED;  
      

   2. Select * from bds_test;

   3. select n.name, s.value /* , s.inst_id, s.sid */ from v$statname n, gv$mystat s where n.name like '%XT%' and s.statistic# = n.statistic#;

6. Define a Hive table:

   1. Connect to Hive via Hue, the Hive/Beeline command line, or using Oracle SQL Developer with a Hive JDBC driver.

   2. CREATE TABLE bds_test_hive (line string);

   3. LOAD DATA INPATH '/user/oracle/test.txt' OVERWRITE INTO TABLE bds_test_hive;

7. Define an external ORACLE_HIVE table:

   CREATE TABLE bds_test_hive (line VARCHAR2(4000))   
       ORGANIZATION EXTERNAL
       ( TYPE ORACLE_HIVE DEFAULT DIRECTORY DEFAULT_DIR
         ACCESS PARAMETERS
           (com.oracle.bigdata.tablename=default.bds_test_hive)  
       )
     REJECT LIMIT UNLIMITED;  
   

The table below identifies objects on the Hadoop server that can provide helpful information for troubleshooting Big Data SQL.

1. User issues a SELECT query involving an Oracle Big Data SQL external table.

2. Database sees that one of the objects in the SQL is an External table of type ORACLE_HIVE

3. Database identifies the cluster name from the com.oracle.bigdata.cluster parameter on the External table definition else uses the default cluster.

4. Database identifies the Hive table name from the com.oracle.bigdata.tablename parameter, else assumes the Hive table name is the same as the Oracle table name.

5. Database knows that the ORACLE_HIVE External table implementation uses an external procedure which is invoked through the extprocbds_<dbname>_<hcluster> multi-threaded agent.

   Note:

   The first phase of the query requires getting the Hive metadata. If you get an error during this first phase, you’ll likely see an error that begins as follows. Notice the “OPEN” in ODCIEXTTABLEOPEN)

   ORA-29913: error in executing ODCIEXTTABLEOPEN callout

6. Database uses the public database link BDSQL$_DEFAULT_CLUSTER or BDSQL$_<hcluster> to find the connect string to ask the listener to connect the database session to a thread of the extprocbds_dbname>_hcluster> multi-threaded agent

   1. extprocbds_<dbname>_<hcluster> was previously started by Oracle Clusterware and is using configuration information from the $ORACLE_HOME/bigdatasql/bigdata_config directory.

   2. extprocbds_<dbname>_<hcluster> has spawned a JVM running Hadoop client libraries using the above configuration information. The Hadoop client libraries were copied from the Oracle Big Data Appliance to the Oracle Database server when you ran the bds-exa-install.sh script.

7. extprocbds_<dbname>_<hcluster> uses its JVM and the Hive metastore client library to call the Hive metastore (using a URL such as thrift://hostname>:9083) to get metadata (columns, inputformat, serde, other table properties) for the Hive table.

   1. At this point, if the Hive metastore is protected by Kerberos authentication, the Hive client libraries running in the extprocbds JVM on the Oracle Database server will try to send the local Kerberos ticket to the Hive server. This will be the ticket owned by the oracle Linux user account who is running the database

8. extprocbds_<dbname>_<hcluster> calls the Hive metastore to get a list of input paths that hold the data behind the Hive table.

9. extprocbds_<dbname>_<hcluster> converts the list of input paths into a list of splits/blocks using Hadoop MapReduce libraries and logic. Then it asks the HDFS namenode for the location (including replicas) of all of the splits /blocks.

   1. Again, if HDFS is protected by Kerberos, the Kerberos ticket from the oracle Linux user account on the database will be need to be used.

   2. If compression is used, at this point the JVM might have to load specific compression Java or native libraries. If these are non-standard libraries, you will need to install them on both the Oracle Database server and the Hadoop side. For instance, LZO compression requires an additional install and configuration performed on both the database-side on the Hadoop-side.

   At this point, the “description” phase is done and the database knows the structure of the Hive table as well as the location of all of the blocks of data (including replicas). This information is also known as the metadata payload. We now begin the “fetch” phase.

10. The database intelligent storage layer, KCFIS (Kernel Cache File Intelligent Storage), which is also used on Oracle Exadata systems, compares the hostnames of where the blocks of data are stored to a list of active BDSQL server hosts being maintained by the Grid’s diskmon process. (You can see diskmon’s list of BDSQL server hosts in V$CELL).

    Note:

    The second phase of the query requires fetching the data. If you get an error during this second phase, you’ll likely see an error that begins as folllows. Notice the “FETCH” in ODCIEXTTABLEFETCH) :

    ORA-29913: error in executing ODCIEXTTABLEFETCH callout
    

11. Assuming that the list of datanode hostnames matches the list of BDSQL hostnames, the database sends a list of local blocks (also called Granules) to each of the BDSQL servers. The database also sends the BDSQL servers metadata about the table, columns, and structure it is accessing. It does this in parallel and asynchronously for performance

    Note:

    The database statistics “cell XT granules requested for predicate offload” and “cell XT granule bytes requested for predicate offload” are updated at this point

12. The BDSQL process running on the data nodes checks the SQL_ID against its local list of quarantined SQL_IDs. If the SQL_ID matches the quarantine list, then the BDSQL process on the datanode will return an error. However, the user should not see this error. Instead, the database will first try another cell, then try to do the work itself. (See Steps 15 and 16).

13. Assuming the SQL_ID is not quarantined by the BDSQL process on the datanode, the BDSQL process will do its SmartScan work against the list of blocks/granules sent to it.

    Tip:

    See the blog entry Big Data SQL Quick Start. Storage Indexes - Part10 in The Data Warehouse Insider for details about Storage Indexes and other aspects of SmartScan processing.

    1. The BDSQL offload process has previously read its configuration information from /opt/oracle/bigdatasql/bdcell-12.1/bigdata.properties.

    2. The BDSQL process has previously loaded a JVM based on the properties defined in the above configuration.

    3. If the Hive table has special InputFormat or Serde classes, the JVM will load those classes assuming it can find them based on the classpath defined in the above configuration. For some common InputFormats (such as delimited text), Oracle has written C code that can handle those formats faster than regular Java code.

    4. If Kerberos authentication is used, then the BDSQL’s JVM will send its local Kerberos ticket to the HDFS datanode process. This is the Kerberos ticket associated with the oracle Linux user on the datanode where BDSQL is running.

    5. If Sentry authorization is used, the oracle Linux user’s Kerberos ticket’s identity needs to have been granted access to the Hive table and underlying HDFS data.

    6. The BDSQL server will update statistics like “cell XT granule IO bytes saved by StorageIndex” as it runs.

14. The database kcfis layer will collect results as they are returned from the BDSQL processes on the datanodes and send the next batch of blocks/granules to the BDSQL processes.

    1. The database will update the “cell interconnect bytes returned by XT smart scan” statistic as bytes are returned

15. If there are issues with a BDSQL process for a given block, the database will try to send the work to a different BDSQL process (it will pick a location that has a replica of the block that failed).

    1. The database will update the “cell XT granule predicate offload retries” statistic.

16. If the database is unable to get the BDSQL processes to successfully offload a block even after retrying, then the database will “fallback” and have the JVM in the extprocbds_<db>_<cluster> do the work.

    1. This will be slower as the raw data will need to be moved to the database server for processing.

    2. If the Hive table has any special InputFormats or Serdes, the extprocbds_<db>_<cluster>’s JVM will need to load them based on the classpath configuration defined on the database’s bigdata.properties file.

17. The results from the external table data source continue to be collected until all input paths/blocks/granules are handled.

1. User queries one of the Oracle Big Data SQL data dictionary views, such as all_hive_tables.

   In Oracle Big Data SQL 2.0 and earlier, if this was the user_hive_* view and the user was not a DBA, then the user needed to be listed in the SYS.HIVE_URI$ table. Oracle Big Data SQL 3.0 removed the HIVE_URI$ check.

2. The view accesses the GetHiveTable pl/sql pipelined table function.

3. The GetHiveTable function is implemented by the HiveMetadata type which is implemented as an external procedure using the SYS.DBMSHADOOPLIB library.

4. The Oracle Database spawns a new instance of the “extproc” for this database session. The extproc reads the $ORACLE_HOME/hs/admin/extproc.ora file for settings.

   You can set TRACE_LEVEL=ON for tracing of the C code. Log file will be written to $ORACLE_HOME/hs/log.

   By default, there may be an error in the extproc.ora, causing an “Error in assignment statement” message in the log . The statement “SET EXTPROC_DLLS=” (with no value after the equal sign) is not valid. Comment this line out if you want to use TRACE_LEVEL=ON .

5. The extproc attaches the libkubsagt.so library (as in SYS.DBMSHADOOPLIB).

6. Libkubsagt12.so initiates a JVM and loads the HiveMetadata.jar.

   1. The JVM uses the configuration information in $ORACLE_HOME/bigdatasql/bigdata_config/ to identify the list of clusters and their Hive metastore connection information.

   2. Logging for the JVM is based on $ORACLE_HOME/bigdatasql/bigdata_config/bigdata-log4j.properties. Log files will be written to $ORACLE_HOME/bigdatasql/log . There will be a new log file for each database session.

7. The Java code in HiveMetadata.jar uses the Hive metastore client libraries to connect to the Hive metastore to retrieve data about all of the databases and all of the tables.

   1. If the Hive metastore is protected by Kerberos, the JVM will try to send the Kerberos ticket of the oracle Linux user who is running the database

8. The Java code returns the request data back to the database.

• Restarting the extprocbds_<db>_<hcluster>:

  $  crsctl stop res bds_<dbname>_<hcluster>
  

  Quick way, but not the best way: kill the extprocbds_* process and wait for it to come back

• Restarting the extproc.

  This begins a new database session.

• Restarting the Oracle Big Data SQL software on the datanodes:

  • Use Cloudera Manager or the Ambari Web UI.

  • Quick way, but not the best way: kill the bdsqloflsrv process and wait for it to come back.

  • Command line method on an Oracle Big Data Appliance (logged on as root on node1):

    $ bdacli stop big_data_sql_cluster
    $ bdacli start big_data_sql_cluster
    

• Checking for Oracle Big Data SQL quarantines on a single datanode:

  $ bdscli -e list quarantine detail 
  

  To check for quarantines on all datanodes:

  $ dcli -g cells.lst bdscli -e list quarantine detail
  

• Clearing Oracle Big Data SQL quarantines on a single datanode:

   $ bdscli -e drop quarantine all
  

  To clear quarantines on all datanodes:

  $ dcli -g cells.lst bdscli -e drop quarantine all

• Checking statistics for proper offloading:

  • Use the Sql Monitor hint: /*+ MONITOR*/.

  • Query XT statistics. Ensure that “retries” is zero and “bytes returned” is greater than zero.

• Looking for log files on the datanodes:

  1. Clear quarantines on all datanodes

  2. Set Log property in /opt/oracle/bigdatasql/bdcell-12.1/bigdata-log4j.properties on datanodes.

  3. Restart bdsqloflsrv on datanodes.

  4. Cd to the log file directory: /opt/oracle/bigdatasql/bdcell-12.1/log.

  5. tail -f bigdata-log4j.log

  6. Ensure that your query has data on the node you are looking at (i.e. your query should need to access files with many blocks. If you only touch a small number of blocks, the result may be that your datanode is not be asked to do any work)

  7. Make a new database session (to reset XT statistics) and Run query.

     Tip:

     Use the /*+MONITOR*/ hint if you want to be sure to see it in SQL Monitor.

     You should see new entries in the datanode’s bigdata-log4j.log.

  8. On the Oracle Database server, query XT statistics and check that retries=0 and bytes returned>0.

• Looking for log files on the database:

  1. Clear quarantines on all data nodes

  2. Make a new database session (to reset XT statistics)

  3. Find out what instance your session is connected to (in case you got load-balanced to a different database server than the one you logged on to):

     select host_name from v$instance;
     

  4. Log in to that instance’s database server at the Linux level.

  5. Set log properties in $ORACLE_HOME/bigdatasql/bigdata-log4j.properties.

  6. Restart extprocbds_<db>_<hcluster> on that instance to pick up the log property changes

  7. Turn on XT tracing:

     This command turns on external table logging:

     alter session set "_xt_trace"="low","compilation","execution";
     

     This command adds additional tracing:

     alter session set events 'trace[KCFIS] disk high, memory high';
     

  8. Run the query.

     Tip:

     Use the /*+ MONITOR */ hint if you want to be sure to see it in SQL Monitor.

  9. Query XT statistics and see if retries=0 and bytes returned>0.

     select n.name, s.value /* , s.inst_id, s.sid */ from v$statname n, gv$mystat s where n.name like '%XT%' and s.statistic# = n.statistic# ;
     

  10. Look at JVM log file: $ORACLE_HOME/bigdatasql. (Look for the one with the same PID as the extprocbds_* process.)

  11. Look at database trace_file:

       select value from v$diag_info WHERE name = 'Default Trace File';
      

• You can add JVM properties to the bigdata.properties file as shown below. This can be good for hard-to-spot low-level Kerberos issues.

  java.options=-Dsun.security.krb5.debug=true
  

• The extproc and extprocbds_<dbname>_<hcluster> processes run the JVMs on the database and the bdsqloflsrv process runs the JVM on the datanode. You can see this by running the “jps” command:

  $ORACLE_HOME/bigdatasql/jdk*/bin/jps
  

• If you are very comfortable with your Java skills, you can also use Oracle JVisualVM or Oracle JConsole to connect to the JVMs.

If you see "wrong number or types of arguments in a call to 'FETCH_OPEN' in the error stack