Sep 202012
ERROR: java.sql.SQLException: Got minus one from a read call

This error occurs when the connection from the manager to the database is not successful.

To fix this error:

1. Check the status of the database using the following command. The status for the ArcSight database should be “OPEN”.

root# su oracle
oracle$ sqlplus / as sysdba
SQL> select instance_name,status from gv$instance;

2. Check the status of the Oracle listener using the following command. The output should include the Start Date and Uptime if it is running.

cd /usr/local/arcsight/db/bin
./arcdbutil lsnrctl status

3. Check the TCP.INVITED_NODES parameter in the “sqlnet.ora” file (/home/oracle/OraHome11g/network/admin/sqlnet.ora). The manager IP address or host name should be included as a valid host in this parameter.

Sep 202012

As QRadar needs both public and private key to be in clear-text format, you should follow the steps below to extract the keys from a pfx file:

1. The following command exports the private key and saves it in “key.pem”.

# openssl pkcs12 -in filename.pfx -nocerts -out key.pem

2. The following command exports the public key and saves it in “cert.pem”.

# openssl pkcs12 -in filename.pfx -clcerts -nokeys -out cert.pem

3. The following command removes the passphrase from the private key.

# openssl rsa -in key.pem -out server.key

Once these steps are done, “server.key” can be imported in QRadar as a private key, and “cert.pem” can be used as the public key.

Sep 202012
ERROR: TNS-00584: Valid node checking configuration error

This error occurs when there is an invalid or unreachable hostname in the TCP.INVITED_NODES list in the “sqlnet.ora” file (/home/oracle/OraHome11g/network/admin/sqlnet.ora).

To fix this error:

  1. Check the nodes in the sqlnet.ora file to make sure they are all reachable; OR
  2. Comment out the TCP.VALIDNODE_CHECKING parameter in the “sqlnet.ora” file.
Sep 202012
Log in to sqlplus

To log in to sqlplus you need to SSH to the Oracle server as root and run the following commands:

root# su oracle
oracle$ sqlplus / as sysdba
Query – Explore ArcSight database
oracle$ sqlplus / as sysdba
-- Assumption1: ArcSight DB Name is "arcsight"
-- Assumption2: ArcSight DB Username is "arcsight"
SQL> connect arcsight@arcsight
SQL> select * from tab where TNAME LIKE 'ARC_EVENT%';
SQL> select column_name from all_tab_columns where table_name = 'ARC_EVENT_GEO_LOCATION';
Query – Database start time
select	to_char(startup_time, 'HH24:MI DD-MON-YY') "Startup time"
from	v$instance
Query – Database size  and available free space
col "Database Size" format a20
col "Free space" format a20
col "Used space" format a20
select	round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"
,	round(sum(used.bytes) / 1024 / 1024 / 1024 ) - 
	round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"
,	round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
from    (select	bytes
	from	v$datafile
	union	all
	select	bytes
	from 	v$tempfile
	union 	all
	select 	bytes
	from 	v$log) used
,	(select sum(bytes) as p
	from dba_free_space) free
group by free.p
 Query – Available space on each tablespace
set linesize 150
column tablespace_name format a20 heading 'Tablespace'
column sumb format 999,999,999
column extents format 9999
column bytes format 999,999,999,999
column largest format 999,999,999,999
column Tot_Size format 999,999 Heading 'Total| Size(Mb)'
column Tot_Free format 999,999,999 heading 'Total Free(MB)'
column Pct_Free format 999.99 heading '% Free'
column Chunks_Free format 9999 heading 'No Of Ext.'
column Max_Free format 999,999,999 heading 'Max Free(Kb)'
set echo off
select a.tablespace_name,sum(a.tots/1048576) Tot_Size,
     sum(a.sumb/1048576) Tot_Free,
     sum(a.sumb)*100/sum(a.tots) Pct_Free,
     sum(a.largest/1024) Max_Free,sum(a.chunks) Chunks_Free
     (select tablespace_name,0 tots,sum(bytes) sumb,
             max(bytes) largest,count(*) chunks
      from dba_free_space a
      group by tablespace_name
      select tablespace_name,sum(bytes) tots,0,0,0
      from dba_data_files
      group by tablespace_name) a
group by a.tablespace_name order by pct_free;
Query – Delete archive logs
oracle$ rman TARGET /
RMAN> delete archivelog all;