Oracle database troubleshooting

The following issues can occur when you are using Oracle as the Infrastructure Management Database.

Symptom

Error message

Solution

Reference

Installation

Common error messages recorded in the following log files when installing TrueSight Infrastructure Managementwith Oracle as the database:

  • pw\pronto\oracleDBAIface.log
  • pw\pronto\logs\pronet_db.log
  • pw\pronto\logs\tsim_server_install_log.txt log
  • IO exception: The Network Adapter could not establish the connection
  • Listener refused the connection with the following error: 
    ORA-12505, TNS:listener does not currently know of SID given in connect descriptor The Connection descriptor used by the client was: <client name: descriptor>
  • ORA-01017: invalid username/password; logon denied
  • ORA-01119: error in creating database file '<root directory>:\app\Administrator\oradata\orcl1\data.dbf'
  • ORA-27040: file create error, unable to create file
  • OSD-04002: unable to open file O/S-Error: (OS 3) The system cannot find the path specified
  • ORA-27044: unable to write the header block of file
  • OSD-04008: WriteFile() failure, unable to write to file
  • O/S-Error: (OS 112) There is not enough space on the disk.
  • The parameter [processes] is set lower than expected value. It is set to [500]. Expected value is [1000].
  • The parameter [job_queue_processes] is set lower than expected value. It is set to [0]. Expected value is [20].
  • The parameter [parallel_execution_message_size] is set lower than expected value. It is set to [2148]. Expected value is [4096]
  • ORA-27038: created file already exists
  • OSD-04010: <create> option specified, file already exists
  • There is one more oracle user [PROACT] having bppm schema on the same oracle sid/service.
  • ORA-01031: insufficient privileges

Refer to error messages table in the online document.

Installation fails due to incorrect Oracle character set



Failure Error: NLS_CHARACTERSET is set to [WE8MSWIN1252]. It needs to be set to [AL32UTF8]

To resolve this issue, contact the Oracle Administrator to change the Database Character Set to Unicode (AL32UTF8), then run the installer again.

For your reference, requirements for character sets are:

  • Database Character set to AL32UTF8.
  • National Character set to AL16UTF16.
  • If you set the NLS_LENGTH_SEMANTICS to BYTE, set the DB_BLOCK_SIZE value to 8192.
  • If you set the NLS_LENGTH_SEMANTICS value to CHAR, set the DB_BLOCK_SIZE value to 16384.
  • Use the default values for all the other fields.

Configuration

pw dbconfig set and test commands fail while configuring with an Oracle RAC.

The pw dbconfig set and pw dbconfig test commands fail when configuring  TSIM with an Oracle RAC.



Example:

pw dbconfig set

Error: Validating connection parameters failed.

Message: [For MainUser : IO Error: The Network Adapter could not establish the connection, SQLState:08006]

pw dbconfig test

Error: For MainUser : IO Error: The Network Adapter could not establish the connection, SQLState:08006

If you are using SCAN IP, then custom pronet.conf should look like below:

pronet.api.database.database=oracle
pronet.api.database.oracle.rac=true
pronet.api.database.oracle.rac.count=1
pronet.api.database.oracle.rac.host.1=<scanip_host>
pronet.api.database.oracle.rac.port.1=1521
pronet.api.database.sid=<ABC>


If you are using 2 hosts then custom pronet.conf should look like below:

pronet.api.database.database=oracle
pronet.api.database.oracle.rac=true
pronet.api.database.oracle.rac.count=2
pronet.api.database.oracle.rac.host.1=<host1>
pronet.api.database.oracle.rac.port.1=1521
pronet.api.database.oracle.rac.host.2=<host2>
pronet.api.database.oracle.rac.port.2=1521
pronet.api.database.sid=<ABC>


Run runjava api.database.DbUpCheck to verify the output is success.

Restart the TrueSight Infrastructure Management to reflect new changes.


Guidelines to share a single Oracle database instance

  1. Multiple TrueSight Infrastructure Managements shared on a single Oracle database instance
  2. One TrueSight Infrastructure Managementand TrueSight Operations Management Report Engine shared on a single Oracle database instance


  1. Multiple TrueSight Infrastructure Managements
  2. One TrueSight Infrastructure Management and TrueSight Operations Management Report Engine

CPU/Platform


8 vCPU; Frequency 2.6 GHz and above

Oracle Database Enterprise Edition, 64-bit

RAM

64 GB

Storage configuration

1 TB, 10 K SAS disk or SAN storage, 2000 IOPS

Note: For Linux systems, at least 3 GB of space is required in the /tmp directory.


Note

 The parameter values listed in the guidelines are for reference only. These values might vary depending on your database configuration

Performance and Scalability

Size of the Oracle data file

If you are using TrueSight Infrastructure Management on Oracle, and if Oracle runs out of free space, the following error message is written to the TrueSight.log file.

ORA-01653:  unable to extend table <Table Name> by <###> in tablespace <Table Space Name>

Example:

unable to extend table PROACT.AUTODISCOVERY_ERRORS by 8 in tablespace NGP_TS

Or

ORA-01654: unable to extend index AUTOPROACT._PATROL__SCA_0_STATS_5_N_1 by 1024 in tablespace PNET_TS_AUTO

This error occurs if the tablespace is full or if there is insufficient disk space to extend the tablespace after you have run TrueSight Infrastructure Management for a while.

Solution:

Contact the Oracle Administrator to create a new data file, and extend the tablespace by performing one of the following tasks:

  • Add a DATAFILE to TrueSight Infrastructure Managementtablespace.
  • Resize the DATAFILE.
  • Enable AUTOEXTEND.

As a best practice, you can monitor the Oracle tablespace using the Oracle KM. For information on configuring Oracle DB for monitoring, see STEP 5 at Set up Infrastructure Management Self-Monitoring for SQLAnywhere and Oracle DBs.

Unable to extend the Oracle System tablespaces SYSTEM or SYSAUX


ORA-01658: unable to create INITIAL extent for segment in tablespace SYSAUX

Recommended SYSTEM tablespace size is 2 GB and SYSAUX tablespace size is 3 GB to avoid this error.

Verify and fix one or all of the following in these tablespaces if required:

  • The SYSTEM tablespace is not the default or temporary tablespace for the normal database users.
  • Look for space consuming tables such as AUD$ in the SYSTEM tablespace and move it to a different tablespace.
  • Query and check for objects consuming most of the space on these tablespaces and either move those outside for later use or delete it if you do not want those objects.
  • Look for tables in SYSTEM and SYSAUX tablespaces that store historical query data, AWR snapshot, and audit information, which can be either deleted or truncated.
  • If you cannot perform any of the above, add additional disk space to these tablespaces.


Maximum number of processes exceeded.

When using the same database for multiple TrueSight Infrastructure Managements, you  get the following exceptions: Could not open connection or a got minus one from a read call IO error.

Exception stack trace 1:

PERSISTENCE SERVICE :: An exception has been thrown from Persistence Service-> org.hibernate.exception.JDBCConnectionException: Could not open connection

javax.persistence.PersistenceException: org.hibernate.exception.JDBCConnectionException: Could not open connection

 at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1763)

 at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1677)

at org.hibernate.jpa.internal.QueryImpl.getResultList(QueryImpl.java:458)

Exception stack trace 2:

Caused by: java.sql.SQLRecoverableException: IO Error: Got minus one from a read call

at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:489)

at

oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:553)

at

oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:254)



To resolve this issue, you must increase the processes in multiples of hundred. The default value is 1000. You can increase it in multiples of hundreds like 1100,1200, and 1500 based on the traffic that you are monitoring.

To increase the number of processes allowed on a database server do the following:

1. Log on to the database as sys/****@<SID> as sysdba (your user credentials) and run  the following command:

alter system set processes =1500 scope=spfile;

2. Restart the database.

Note

Restarting the database after executing the command increases the number of processes.

3. Restart TrueSight Infrastructure Management after restoring the database connection.

ORA-00060 error is logged in the TrueSight.log

java.sql.SQLException: ORA-00060: deadlock detected while waiting for resource

  • If this exception is logged only during failover or failback in application high-availability deployment, you can ignore this exception.
  • If this exception is frequently logged, follow the tasks in Diagnosing and reporting an issue section to find out the cause for deadlock.



Oracle tablespace is growing exponentially


Run the following utility when the Size of the table index is very high. This utility is for rebuilding the indexes on TrueSight Infrastructure Management Oracle database.

  1. Query to get the Table, Index Name, and its size in MB

    SQL> select i.table_name "Table Name", u.segment_name "Index Name", sum(u.bytes)/1024/1024 "(MB)"from user_extents u join user_ind_columns i on u.segment_name = i.index_name

  2. Run the following script from sqlplus logging in as TrueSight Infrastructure Management oracle database user:

    Sqlplus<TSIMDBUSERNAME/TSIMDBUSER_PWD/@SID                                           
    Sql> @Rebuild_Index_Oracle.sql

    This rebuilds the indexes and log the details of indexes rebuilt into tsim_db_index_rebuild.log


Diagnosing and reporting an issue

Task

Action

Steps

Reference

1

Review requirements

Confirm the correct Oracle version, the required hardware requirements, and tuning recommendations are met. Check the bmc online documentation for hardware requirement and tuning information for Oracle database.


2

Self-resolve

  1. Run the latest Health Check Tool and review and follow the recommendation in the report.
  2. Contact Oracle Administrator to execute OracleSchemaPreInstallCheck.sql script. Check the online documentation for the detailed step.
  3. Review the report (oracle_specific_PreInstall_log.html) generated by the script and follow tuning recommendation in the output.

The generated report will have suggestions related to parameters SGA, MEMORY_TARGET, OPEN_CURSORS, SESSION_CACHED_CURSORS, CURSOR_SHARING and REDO Logs Size.

For TrueSight Operations Management Health Check Tool - INCLUDES VIDEO, refer to Knowledge Article number 000223338 (Support logon ID required).

For OracleSchemaPreInstallCheck.sql script, refer to Running the OracleSchemaPreInstallCheck.sql script


3

Collect diagnostics and report


If issues related to database are still exist, raise a BMC Support case after collecting information below:

  • Dump data (pw_server.jar) on TrueSight Infrastructure Management, generated by pw dump 1 command.
  • reports folder from Health Check Tool in Task 2.
  • Contact Oracle Administrator to collect:
    • Oracle alert log:  alert_<Oracle_Instancename>.log from Oracle Database server.
    • Oracle AWR and ADDM report from Oracle Database server that covers the time frame of the issue.
    • oracle_specific_PreInstall_log.html generated in Task 2.
    • Verify if  two TrueSight Infrastructure Managements  or any other bmc products installed and shared on one oracle SID.
    • Check if Dataguard is used in this Oracle environment.


 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*