PATROL for Oracle: Tablespaces does not get discover

Product

PATROL for Oracle

Component

PATROL for Oracle

Applies to

PATROL for Oracle KM 9.7.xx (Applicable to 9.7.11.xx)

Problem

After configuring Oracle Extended Tablespaces Monitoring (ORACLE_ETSM), tablespaces are not getting discovered.

Cause

Multiple causes.

Solution

ETSM monitoring method:

Make sure ETSM History is enabled from the monitoring policy as the Oracle KM behave differently if monitoring tablespaces for historic data is on or off.

To understand the difference, please check the following documentation pages:

ETSM (ORACLE_ETSM)
Using the ETSM applications to monitor tablespaces

When monitoring tablespaces with data files that reside in the ASM Storage:

Patrol OS user need to be able to query v$asm_diskgroup_stat when connecting to the instance (sqlplus) using the patrol DB user.
Output must have some value not zero (0) as below:

SQL> select count (*) from v$asm_diskgroup_stat;

COUNT(*)
----------
0

Make sure the primary group of the patrol OS user the same as primary group of the Oracle user. Or Oracle DBA can work this out the best way as per requirement..

Primary group can be identify by running "id -a" command from Patrol user and Oracle user.

Additional Information:

How to troubleshoot when tablespaces are not discovered?

1. Make sure all the requirements listed in the documentation are met. Refer Oracle KM v9.7.11

https://docs.bmc.com/docs/PATROL4Oracle/97/system-requirements-603829078.html

2. If all requirements are met, collect CollPopulateContainer Debug

From PCO:
RightClick on ETSM>> Knowledge Module Command >>CollPopulateContainer Debug >> ON

From TSPS:
Collect Oracle KM debug log. Refer KA 000284767
https://bmcsites.force.com/casemgmt/sc_KnowledgeArticle?sfdcid=kA33n000000XxAvCAK&type=FAQ

3. If following query result with "no rows selected" output:

+DATA/kmtest/datafile destination is an ASM path!
asmQuery = select 'DATA' dum1, free_mb/decode(type, 'NORMAL', 2,
'HIGH' , 3,
'EXTERN', 1)
SPACE_AVAIL,
(total_mb - free_mb) SPACE_USED
from v$asm_diskgroup_stat
where name = 'DATA'
and (state= 'MOUNTED' OR state= 'CONNECTED');

asmResult =
no rows selected

Verify the result from outside of Patrol, refer below example for Unix:
a. Connect to the machine where the db is via ssh with Patrol OS user:
b: Run:
                export ORACLE_SID=xxxx
                export ORACLE_HOME=/xx/xxxx/xxxxx/xxxxxx
                export PATH=$PATH:$ORACLE_HOME/bin

c. Connect with sqlplus as below:
sqlplus <PatrolDBuser>/<password>

d. Excecute:
show user

e. And execute the query:

select 'DATA' dum1, free_mb/decode(type, 'NORMAL', 2,
'HIGH' , 3,
'EXTERN', 1)
SPACE_AVAIL,
(total_mb - free_mb) SPACE_USED
from v$asm_diskgroup_stat
where name = 'DATA'
and (state= 'MOUNTED' OR state= 'CONNECTED');

f. If you get same result "no rows selected" please check with DBA to fix it. Making the primary group of the patrol OS user the same as primary group of the Oracle user or the way Oracle DBA thinks it is best.

4. If the following is present in CollPopulateContainer debug:

Encountered the following error messages when inserting into table P$DATA_OS_SPACE.
errors = >>>ORA-00942: table or view does not exist

Encountered the following error messages when querying table P$AUTOEXTENDEDLINKS.
errors = >>>ORA-00942: table or view does not exist

And at the same in CollAvailability debug:

Warning: Could not retrieve p$archdest data:
ORA-00942: table or view does not exist

That means that for some reason p$archdest PATROL view was not created in the database at the time of configuration, please ask your DBA to drop PATROL database monitoring account in cascade and configure Oracle KM again for Oracle DB instance and ETSM.

5. If the Patrol Agent and Oracle KM are installed on AIX 7.2 upgraded to TL5 SP1, check if the following error is present in the Agent error logs:

"Unable to set supplementary groups"

If yes, check the following Article and apply IBM official Patch:

PATROL Agent: Problems starting the PATROL Agent, PatrolCLI and Patrol Console after AIX upgrade to 7.2 TL5 SP1

6. Check the Patrol Agent error logs, files PatrolAgent-<hostname>-<port no>.errs located at $PATROL_HOME/log directory. And if the following error is present:

'Loading of following KMs failed - ORACLE_ETSM.kml

Make sure files ORACLE_ETSM.kml and ORACLE_ETSM.xml exist in Patrol3/lib/knowledge directory , with the above message most likely the files are missing for some reason, install the Oracle KM again or copy the files from another Oracle KM working installation, before verify that versions are the same.

7. If the monitored Database instance is a Container DB (CDB) check if the Patrol DB user has C## prefixed. If the following error is present in the PDB_Collector debug:

ORA-01435: user does not exist

It is most likely C## is missing.

8. If after following all the above steps tablespaces are still not discovered, please provide which exact steps were followed along with the debug files to BMC Customer Support for assistance.

Knowledge Article