Oracle database scan using Discover does not return any results
Article ID: 160170
Updated On:
Products
Issue/Introduction
When trying to run a Discover scan against an Oracle database, the scan completes in a few seconds and does not return any results.
Resolution
One common cause for this problem is an incorrect table query, which causes the Discover scanner to be unable to list the tables to be scanned in the Oracle database.
Check the sqldatabasecrawler.properties file to see what table query is currently being used. You can find this file on the Discover server in \Vontu\Protect\config (Windows) or /opt/Vontu/Protect/config (Linux).
Below is a table query that works in many environments. You may need to modify this query to suit your Oracle installation; consult your database administrator if you need more information.
driver_table_query.oracle = select ut.table_name, ut.iot_type from user_tables ut where not exists (select unt.table_name from user_nested_tables unt where unt.table_name = ut.table_name) and (ut.iot_type is null or not ut.iot_type = 'IOT_OVERFLOW')
To test the table query, perform the following steps:
- Login to Sqlplus using the same account that is defined in the Discover Target properties.
- Run the following query to find out what tables exist in Oracle:
select ut.table_name, ut.iot_type from user_tables ut where not exists (select unt.table_name from user_nested_tables unt where unt.table_name = ut.table_name) and (ut.iot_type is null or not ut.iot_type = 'IOT_OVERFLOW');
- If no rows are returned, and there are no errors related to permissions, then the user defined in the target has no tables. If you query the user dictionary tables, nothing will be returned.
To change the query used by the Discover scan, perform the following steps:
- Comment out the below query in sqldatabasecrawler.properties, by adding a # in front of the line:
driver_table_query.oracle = select ut.table_name, ut.iot_type from user_tables ut where not exists (select unt.table_name from user_nested_tables unt where unt.table_name = ut.table_name) and (ut.iot_type is null or not ut.iot_type = 'IOT_OVERFLOW')
- Uncomment out this query and use it instead:
driver_table_query.oracle = select dt.owner||'.'||dt.table_name from dba_tables dt where not exists (select dnt.table_name from dba_nested_tables dnt where dnt.table_name = dt.table_name and dnt.owner = dt.owner) and (dt.iot_type is null or not dt.iot_type = 'IOT_OVERFLOW')
This will query the system dictionary rather than the user dictionary. This query requires that the scanning account be granted select on dba_tables and dba_nested_tables.
- Save the file and restart the Vontu Monitor service.
Another cause, in some cases (possibly including Oracle RAC installations), the database connection string needs to use a fully qualified name, rather than the Oracle database SID alone. This problem causes an error in the ScanDetail log (ORA-12154: TNS:could not resolve service name).
To resolve this problem, change the database connection string from the standard format (oracle:@//oracleserver.company.com:1521/mydatabase) to the following format: (oracle:@//oracleserver.company.com:1521/mydatabase.mydomain.net).
Feedback
