Admin Reports do not work -- ORA-00959: tablespace 'ARREPORTS' does not exist, How to create it manually?

Products

• CA Risk Authentication
• CA Advanced Authentication
• CA Strong Authentication

Issue/Introduction

We are seeing error for below reports:

Risk Authentication Reports
- Exception User Report
- Rules Data Report

Strong Authentication Reports
- Authentication Activity
- OATH Token Details Report
- Credential Management Activity
- Configuration Management Activity

Administration Reports
- Administration Activity Report
- My Activity Reports
- User Activity Report
- User Creation Report
- Organization Report

This is the error message shown in the arcotadmin log file.

2021-03-03 07:12:16,620 EST : [[ACTIVE] ExecuteThread: '1' for queue: 'weblogic.kernel.Default (self-tuning)'] : INFO  : com.arcot.DEFAULT : Initializing Report, Id = AAC.ViewOrgActivityReport
2021-03-03 07:12:16,652 EST : [[ACTIVE] ExecuteThread: '1' for queue: 'weblogic.kernel.Default (self-tuning)'] : ERROR : com.arcot.DEFAULT : Error in Initializing the report ::  Error Code :959 Error State :42000 Error Message :ORA-00959: tablespace 'ARREPORTS' does not exist

java.sql.SQLSyntaxErrorException: ORA-00959: tablespace 'ARREPORTS' does not exist.

Environment

Release : 9.x

Component : AuthMinder(Arcot WebFort) aka Strong Authentication

RiskMinder(Arcot RiskFort) aka Risk Authentication

Resolution

We create Tablespace ARREPORTS and run the reports in this tablespace rather one which is used by the transactions so performance is not impacted of the transactions. It is possible that this tablespace is not created and in that case none of the reports will work which is the case here. 

This script is part of the db-config-for-common.sql and please ensure this was run to create the necessary tablespace for reports, if not then you can run in lower environment and validate if everything works fine.

---------------------------------------------------------------------------------------------------------------
------------------------------------Reporting Framework Schema for Arcot Admin Console -------------------------
----------------------------------------------------------------------------------------------------------------

DECLARE
filename varchar2(50) := 'tabspace_arreports_'|| to_char(current_timestamp, 'YYYY-MM-DD-HH24-MI-SS') || '.dat';
cursor c1 is select tablespace_name from user_tablespaces where tablespace_name = 'ARREPORTS';
BEGIN
FOR I IN c1 LOOP
  execute immediate 'DROP TABLESPACE '||I.tablespace_name||' including contents and datafiles';
END LOOP;
EXECUTE IMMEDIATE 'CREATE TABLESPACE ARREPORTS  DATAFILE '''||filename||''' SIZE 20M AUTOEXTEND ON NEXT 20M  MAXSIZE UNLIMITED DEFAULT STORAGE (INITIAL 10K NEXT 50K  MINEXTENTS 1 MAXEXTENTS 999) ONLINE';
END;
/

--- Create Report  Sequence Generator
DECLARE
cursor c1 is select sequence_name from user_sequences where sequence_name = 'ARTEMPTABLESEQUENCE';
BEGIN
FOR I IN c1 LOOP
  execute immediate 'DROP SEQUENCE '||I.sequence_name;
END LOOP;
EXECUTE IMMEDIATE 'CREATE SEQUENCE ARTEMPTABLESEQUENCE START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE';
END;
/

--- This table stores one row for each session across the server-instances
DECLARE
cursor c1 is select table_name from user_tables where table_name = 'ARREPORTTABLES';
BEGIN
FOR I IN c1 LOOP
  execute immediate 'DROP TABLE '||I.table_name;
END LOOP;
EXECUTE IMMEDIATE 'CREATE TABLE ARREPORTTABLES (  GLOBALID    INTEGER      NOT NULL,  SERVERINFO  VARCHAR2(50),  SESSIONID   VARCHAR2(255),  INSERTTIME  DATE,  PRIMARY KEY(GLOBALID))';
END;
/