Unable to run database component configuration for webMethods 9.7 on Oracle 11.2 g XE

, ,
1

Dear Experts,

I am running into problems while running the database component configuration for wM 9.7 (free trial). I am using Oracle 11.2g express edition. I have browsed through internet and found that the below items needs to be done at the database

1> setting DB_CREATE_FILE_DEST
2> webMethods scripts require ALTER SESSION permission at the time of creating database tables

But I am not sure how to do this as these are more related to a DBA tasks. Please let me know if anyone is aware of these tasks.

Let me know if you need further details in regards to this topic.

I see below errors:

Script(1): \common\db/scripts/oracle/storage/25/create/ora_str_c_ddl_tablespaces.sql

  • Script(2): \common\db/scripts/oracle/storage/25/create/ora_str_c_ddl_user.sql
  • Executing script : \common\db/scripts/oracle/storage/25/create/ora_str_c_ddl_tablespaces.sql
    Error: [wm-cjdbc36-0007][Oracle JDBC Driver][Oracle]ORA-06550: line 5, column 39:
    PL/SQL: ORA-00942: table or view does not exist
    ORA-06550: line 5, column 9:
    PL/SQL: SQL Statement ignored
    ORA-06550: line 13, column 41:
    PL/SQL: ORA-00942: table or view does not exist
    ORA-06550: line 13, column 9:
    PL/SQL: SQL Statement ignored
    ORA-06550: line 21, column 41:
    PL/SQL: ORA-00942: table or view does not exist
    ORA-06550: line 21, column 9:
    PL/SQL: SQL Statement ignored

CREATE STORAGE [26] FAILED
CDC ERROR [wm-cjdbc36-0007][Oracle JDBC Driver][Oracle]ORA-06550: line 5, column 39:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 5, column 9:
PL/SQL: SQL Statement ignored
ORA-06550: line 13, column 41:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 13, column 9:
PL/SQL: SQL Statement ignored
ORA-06550: line 21, column 41:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 21, column 9:
PL/SQL: SQL Statement ignored

Suspending execution…
CDC Error: java.sql.SQLException: [wm-cjdbc36-0007][Oracle JDBC Driver][Oracle]ORA-06550: line 5, column 39:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 5, column 9:
PL/SQL: SQL Statement ignored
ORA-06550: line 13, column 41:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 13, column 9:
PL/SQL: SQL Statement ignored
ORA-06550: line 21, column 41:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 21, column 9:
PL/SQL: SQL Statement ignored

Summary of installation errors

Error in executing file \common\db/scripts/oracle/storage/25/create/ora_str_c_ddl_tablespaces.sql
SQL error [wm-cjdbc36-0007][Oracle JDBC Driver][Oracle]ORA-06550: line 5, column 39:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 5, column 9:
PL/SQL: SQL Statement ignored
ORA-06550: line 13, column 41:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 13, column 9:
PL/SQL: SQL Statement ignored
ORA-06550: line 21, column 41:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 21, column 9:
PL/SQL: SQL Statement ignored

Following components could not be installed:

Storage 26

2

Mahesh,

Did you try un-installing the db, installation again, what all steps did you follow to resolve it. I had the similar issue,“IS is not coming up - webMethods - Software AG Tech Community & Forums” is the topic, please go through it and let us know latest updates on this thread.

Thanks,
RP.

3

RP,

I have already gone though the thread, let me know how to do the below:

1> setting DB_CREATE_FILE_DEST
2> webMethods scripts require ALTER SESSION permission at the time of creating database tables

1 Like
4

Mahesh,

As it is on your local, give a try on your DB and let us know the latest news.

GRANT CREATE VIEW TO &user;
GRANT CREATE TABLE TO &user;
GRANT ALTER SESSION TO &user;
GRANT CREATE CLUSTER TO &user;
GRANT CREATE SESSION TO &user;
GRANT CREATE SYNONYM TO &user;
GRANT CREATE SEQUENCE TO &user;
GRANT CREATE DATABASE LINK TO &user;
GRANT CREATE TYPE TO &user;
GRANT CREATE TABLE TO &user;
GRANT CREATE CLUSTER TO &user;
GRANT CREATE TRIGGER TO &user;
GRANT CREATE OPERATOR TO &user;
GRANT CREATE INDEXTYPE TO &user;
GRANT CREATE PROCEDURE TO &user;
GRANT GLOBAL QUERY REWRITE TO &user;

ALTER USER &user QUOTA UNLIMITED ON WEBMDATA;
ALTER USER &user QUOTA UNLIMITED ON WEBMINDX;

GRANT EXECUTE ON SYS.DBMS_ALERT TO &user;
GRANT SELECT on SYS.V_$PARAMETER TO &user;
GRANT EXECUTE ON SYS.DBMS_JOB TO &user;
GRANT EXECUTE ON SYS.UTL_SMTP TO &user;
GRANT EXECUTE on SYS.DBMS_METADATA TO &user;
GRANT EXECUTE on SYS.DBMS_OUTPUT TO &user;
GRANT EXECUTE on SYS.DBMS_LOB TO &user;
GRANT EXECUTE on SYS.PLITBLM TO &user;
GRANT EXECUTE on SYS.DBMS_UTILITY TO &user;

PS : Pass user details as part of & user.

Thanks,
RP.

5

I am currently using Oracle 10g XE for the database. This is due to the fact that Oracle 11g XE was not available at this point where I created my local environment.

I am running wM 9.5 for this.

Deriving from the original error messages it seems that Oracle 11g XE is not fully compatible with a real Oracle 11g.
The creation of the tablespaces is failing.

Regards,
Holger

6

Hmm… :frowning:

7

you may even open up the SQL \common\db/scripts/oracle/storage/25/create/ora_str_c_ddl_tablespaces.sql and try running the individual scripts to see if the creation of tablespaces is failing?

8

We have a webMethods IS/MWS env using a 11.2g oracle db. So I don’t think there are compatibility problems there.
We don’t use a xe version. We would have a professional version probably. (i’m not the db admin)

Second point
oracle user permissions:
For some components of the db configuration you need more then the permission to create/alter /delete tables.
There are several other oracle function that are being installed.
example: The wmarchive component installs packages on the db.
Keep this in mind when installing components.

Extra debug information:
you can add extra logging information when installing the db components:
jdbc:wm:oracle://:<1521|port>;serviceName=;SpyAttributes=(log=(file)spy_DCC1.log;logTName=yes;timestamp=yes)

9

Thanks Jo. I switched to Microsoft SQL server and its working fine now. I have to deal with the Oracle XE 11g later sometime but I will keep you updated.

10

I have currently an Oracle 10g XE running with my local wM 9.5.

This works when following some hints:

  • The XE needs to be modified a little bit, esp. when using MWS:
    • ALTER SYSTEM SET PROCESSES=150 SCOPE=SPFILE
  • When creating the users via the DB Configurator use the “Create Users and Tablespaces” option with your DBA system or sys account.
  • some Products or components might require additional grants which can only be granted by SYS (Need to be applied manually when running the DB Configurator as SYSTEM:
    • grant execute on SYS.UTL_SMTP (required for all schemas)
    • some more specific grants for Optimize

I will check this with Oracle 11g XE (and maybe a newer wM Version) later on, but this not yet scheduled.

Regards,
Holger

11

Yes! Oracle 10g XE works fine but Oracle 11g XE does not. Let me know when you test and please share your comments.

BTW nice DP :slight_smile:

12

Is this a 32bit-Version or a 64bit-Version of Oracle 11g XE?

I had tested a 32bit-Version on Win 7 64bit before I reverted to Oracle 10g XE.

The 64bit-version was not available at the time we had our Windows migrated from XP to 7 64bit.

Meanwhile it is available, but I was not yet able to test it.

Regards,
Holger

13

I used Oracle 11g XE 64-bit.

14

1.Create User called SAG ,Tablespace SAGTBS using webMethods DB Configurator and all Create all Components
2.Open SQLPLUS and login as SYSTEM and execute below sql
3.CONNECT SYS AS SYSDBA;
or
CONNECT SAG AS SYSDBA;
4.GRANT EXECUTE ON utl_smtp TO SAG; – This is for some reasons is not managed automatically by webMethods DB Configurator.
5. Recreate all components using webMethods DB Configurator using same user SAG

Here i have used SAG as username for the webMethods connection to oracle XE db

15

Hi,

with with which DBA-User was the step 1 performed? SYSTEM or SYS?

When using SYS, the steps 2-5 should not be neccessary as this should be done the configurator.

When only SYSTEM is known, then the grant statement should be performed by real DBA.

This depends on which tasks are performed by which DBA-User as they have different rights and possibilities.

Usually the custom created users (here: SAG) should not be able connect as SYSDBA for security reasons.
They usually need only CONNECT and RESSOURCE (and unlimited quota inside the available tablespace).

Regards,
Holger