It's all about the answers!

Ask a question

Using jts/setup to configure Oracle to support the RTC 4.0.2 Data Warehouse


1
1
Leigh Light (33269) | asked Jun 10 '13, 2:54 p.m.
edited Jun 10 '13, 2:56 p.m.
I am using the jts/setup script to configure Oracle to support the RTC 4.0.2 Data Warehouse.  I have completed the Oracle configuration for support of the jts server data warehouse but the setup script returned errors when I try to "create tables" when configuring Oracle to support the ccm server data warehouse.

My DBA looked into what was happening in Oracle and found that setup is trying to create the same tables it previously created in support of the jts server.

The oracle db user does have administrator privileges and the jts server data warehouse setup "test connection and create tables" completed without error.  The ccm server data warehouse "test connection" works without error.

Do we need to create two Oracle tablespaces/dbdw users to support two oracle data warehouses - one for each server or does the data warehouse use one tablespace to support data collection for the jts and ccm servers?  If it's one data warehouse in support of the two servers, why the errors when jts/setup is trying to "create tables" for the ccm server?

Any help that can be provided to clear up this problem would be appreciated. Thank you.

Accepted answer


permanent link
Stephen Long (14311013) | answered Jun 11 '13, 4:43 a.m.

One potential gotcha is that the DW users default role must be "DBA", once you have granted it DBA permissions. We had similar issues when initialising the Data Warehouse tables.

We used the script below to create the DW user:

  CREATE USER dw IDENTIFIED BY password DEFAULT TABLESPACE dw QUOTA UNLIMITED ON dw TEMPORARY TABLESPACE dwtmp;
  GRANT CREATE PROCEDURE, CREATE SESSION, CREATE TABLE, CREATE VIEW TO dw;
  GRANT "AQ_ADMINISTRATOR_ROLE" TO dw;
  GRANT "DBA" TO dw;
  GRANT "MGMT_USER" TO dw;
  ALTER USER dw DEFAULT ROLE "AQ_ADMINISTRATOR_ROLE", "DBA", "MGMT_USER";

Once your RTC installation (including RRDI) is complete, you can then remove the DBA role from the DW user with the following script.

  REVOKE "AQ_ADMINISTRATOR_ROLE" FROM dw;
  REVOKE "DBA" FROM dw;
  REVOKE "MGMT_USER" FROM dw;
  ALTER USER dw DEFAULT ROLE NONE;
  ALTER USER dw TEMPORARY TABLESPACE RIS_TEMP;
  ALTER USER dw QUOTA UNLIMITED ON VNF_32K;
  ALTER USER dw QUOTA UNLIMITED ON VNF_IDX;
  ALTER USER dw QUOTA UNLIMITED ON USERS;
  ALTER USER dw QUOTA UNLIMITED ON VSTR_32K;
  ALTER USER dw QUOTA UNLIMITED ON VSTR_IDX;
  GRANT CREATE SESSION TO dw;
  GRANT CONNECT TO dw;
  GRANT DROP ANY TABLE TO dw;
  GRANT EXECUTE ANY PROCEDURE TO dw;
  GRANT ANALYZE ANY TO dw;
BEGIN FOR TABNAME IN (SELECT OWNER,TABLE_NAME FROM ALL_TABLES WHERE OWNER IN ('RIODS','RIDW','CONFIG','RIASSET','RISCHK','RICALM')) LOOP EXECUTE IMMEDIATE 'GRANT SELECT,UPDATE,DELETE,INSERT ON ' || TABNAME.OWNER || '."' || TABNAME.TABLE_NAME || '" TO dw'; END LOOP; END; /
BEGIN FOR VNAME IN (SELECT OWNER,VIEW_NAME FROM ALL_VIEWS WHERE OWNER IN ('RIDW','RICALM')) LOOP    EXECUTE IMMEDIATE 'GRANT SELECT ON ' || VNAME.OWNER || '."' || VNAME.VIEW_NAME || '" TO dw'; END LOOP; END; /

Leigh Light selected this answer as the correct answer

Comments
Leigh Light commented Jun 11 '13, 9:48 a.m. | edited Jun 11 '13, 9:56 a.m.

Hi Stephan,


Our user is a DBA.  jts/setup created the tablespace on oracle for the jts server during the data warehouse setup without error.  This indicates that there is not a permission issue.

My Oracle Administrator looked through the errors and tells me that the ccm data warehouse setup is trying to create the same tablespace that it did during the jts data warehouse setup. 

We are trying to track down why the jts/setup script attempts to configure the same tablespace for both the jts and ccm servers during the data warehouse configuration steps.

Leigh


Leigh Light commented Jun 11 '13, 9:55 a.m.

I've passed on the permissions you listed to my DBA just to confirm that we aren't missing one.


Thanks for responding,
Leigh 

One other answer



permanent link
Indradri Basu (1.8k1514) | answered Jun 10 '13, 3:32 p.m.
Hi Leigh, this sounds unlikely unless there has been a mistake in specifying the database parameters while running the setup. You may want to look here  (specially point no 3) to ensure tablespaces were created correctly.

Comments
Leigh Light commented Jun 11 '13, 9:52 a.m.

 Hi Indradri,


During the data warehouse setup, the script kicked off when configuring the data warehouse using jts/setup, creates the tablespace on oracle. This is why the data warehouse user on Oracle needs to be DBA.

While during jts and ccm server configuration, the tablespace is created configured by a human DBA.

So the sizing parameters you referenced do not apply to the data warehouse setup.

Thanks for responding,
Leigh

Your answer


Register or to post your answer.


Dashboards and work items are no longer publicly available, so some links may be invalid. We now provide similar information through other means. Learn more here.