Manually collect data from the database

About this task

BigFix Endpoints collects the data from the Oracle database automatically without manual intervention by using the 'Get Oracle Database Auditing Data' fixlet. However, in some cases, it is not possible. Below are a few examples when manually collecting database data is not possible:
  • Oracle Database account SYSDBA is disabled or has restricted access to the database (for example, when Oracle Database Vault feature is in use.)
  • On Unix systems, it is disabled to connect locally by a database instance owner to a Oracle database as SYSDBA without a password.
  • On Windows system, the option to connect locally by a System Admin account to an Oracle database as SYSDBA without a password is disabled.
  • The database would like to collect the Oracle Database Inventory data is below version 11.2.
Note: Make sure that you collect the Oracle data with the script downloaded from the same version of the fixlet and the BigFix Inventory server. Different versions may be incompatible.
In such situations, to manually collect the database data, you can execute the Oracle Measurements SQL script (later called Oracle script) on every applicable database instance and container. To do so, perform the below steps:
  1. Download the Oracle script by clicking a link in fixlet ‘Get Oracle Database Auditing Data’ in the BigFix console.
  2. Log in on a computer that has SQL Plus installed and configured for the Oracle database client. You can choose either of the following:
    1. Log in to every computer on which Oracle databases are installed and connect to every Oracle database instance locally using the SQL Plus database client.
    2. Use the SQL Plus client to connect remotely to each Oracle database and database container.
  3. Use encoding AMERICAN_AMERICA.AL32UTF8 in SQL Plus by setting the NLS_LANG variable to AMERICAN_AMERICA.AL32UTF8 before starting SQL Plus.
    1. On Unix, use the following command in your shell command:

      NLS_LANG=AMERICAN_AMERICA.AL32UTF8; export NLS_LANG

    2. On Windows, use the following command in your cmd command:

      NLS_LANG=AMERICAN_AMERICA.UTF8

  4. Provide credentials and connection details to the SQL Plus client to log in to Oracle database.
    1. If a SYSDBA account cannot be used (for example, due to database object access restrictions imposed by Oracle Database Vault feature), make sure that you use credentials for a database user that has the appropriate permissions for accessing the database objects. Information about the required permissions is described in the Oracle Script.
  5. Execute the SQL script on every database instance and database container.

  6. Create an archive that contains all CSV files produced by the Oracle Script. BigFix supports both ZIP and tar gzip formats. The file should have the *.zip name extension for ZIP format and *.tar.gz or *.tgz for ‘tar gzip’ format. One archive file can contain files collected from multiple databases and computers.

  7. Upload all archived files on BigFix Inventory server into a folder whose location is defined by oracleAuditingManuallyUploadedFilesPath setting. The default folder location for Windows and Linux is <installation_dir>/wlp/usr/servers/server1/data/sam/oracleAuditingManuallyUploaded.

Below are sample instructions on how to manually collect the database data using a local connection to user SYSDBA. If you would use a different database account or collect the data remotely, refer to Collecting the data or contact your Database Administrator.

  • Log in to the computer where Oracle Database is installed.
  • Switch to an appropriate user:
    • On Unix, switch to a user who is the owner of an Oracle Database instance.
    • On Windows, switch to a user who has permissions to connect to an Oracle Database user as SYSDBA without a password.
  • Copy the HCL_db_usage_tracking_queries.sql SQL script on that computer.
  • Execute the NLS_LANG=AMERICAN_AMERICA.AL32UTF8; export NLS_LANG command in your shell session.
  • Log in to the Oracle database using the sqlplus / as SYSDBA command. You may change this command to provide the credentials as needed.
  • In the SQL Plus command line, execute the script using the @./BFI_db_usage_tracking_queries.sql command.
  • If the database is containerized:
    • Switch to every container using the ALTER SESSION SET CONTAINER=<<container name>> command.
    • Execute the auditing script using the @./BFI_db_usage_tracking_queries.sql command on each container.

In your database configuration, if the connection without a password is disabled or SYSDBA user has limited access to the data (for example, when the Database Vault feature is enabled), you need to modify parameters for starting the SQL Plus tool.