CreateAEDB.pl fails during CA Workload Automation AE upgrade.

Document ID : KB000008532
Last Modified Date : 14/02/2018
Show Technical Document Details
Issue:

CA WAAE upgrade to 11.3.6 SP6 failed during database upgrade.

When we try to upgrade the database using CreateAEDB.pl, manually, it results in the following error:

#perl CreateAEDB.pl
CreateAEDB: Do you want to create the Oracle tablespaces, users, and roles? (y|[n]) > n

CreateAEDB: Tablespaces and database users will not be created.

CreateAEDB: Service Identifier [AEDB]? > AEDB
CreateAEDB: aedbadmin user password [aedbadmin]? >
CreateAEDB: Confirm aedbadmin user password? >
CreateAEDB: autosys user password [autosys]? >
CreateAEDB: Confirm autosys user password ? >
CreateAEDB: JRE directory [/opt/CA/WorkloadAutomationAE/JRE_WA]? >
CreateAEDB: Data tablespace name [AEDB_DATA]? > AEDB_DATA
CreateAEDB: Index tablespace name [AEDB_INDEX]? > AEDB_INDEX
CreateAEDB: Base version 11.3.6.3.0 found. CreateAEDB will upgrade the CA Workload Automation AE database.
CreateAEDB: Continue and upgrade the database tables where required? (y|[n])> y

CreateAEDB: CA Workload Automation AE database was not created or updated. Please check /tmp/CreateAEDB/install_aedb.log for details.


Futher, the /tmp/CreateAEDB/install_aedb.log shows:

sqlplus -S aedbadmin/******@AEDB @count.sql  2>&1
Return code = 44544
SELECT COUNT(*) FROM dba_tables WHERE table_name = UPPER('UJO_ALAMODE') AND owner = UPPER('aedbadmin')
                     *
ERROR at line 1:
ORA-00942: table or view does not exist


sqlplus -S aedbadmin/******@AEDB @base_ver.sql  2>&1
Return code = 256

11.3.6.3.0

SP2-0584: EXIT variable "vresult" was non-numeric
Usage: { EXIT | QUIT }  [ SUCCESS | FAILURE | WARNING | n |
       <variable> | :<bindvariable> ]  [ COMMIT | ROLLBACK ]

Current base version::11.3.6.3.0.
CA Workload Automation AE database was not created or updated.

Environment:
CA Workload Automation AE 11.3.6 and higher.
Cause:

 

CreateAEDB.pl failed because the AEDBADMIN Oracle user did not have 'SELECT_CATALOG_ROLE'  privilege granted in the Oracle database.

Resolution:

 

Grant the 'SELECT_CATALOG_ROLE' privilege to Oracle user AEDBADMIN using the following Oracle statement:

SQL> GRANT 'SELECT_CATALOG_ROLE' TO AEDBADMIN

 

The AEDBADMIN user is by default set up with the following privileges during initial AEDB creation.

GRANT CREATE VIEW TO AEDBADMIN

GRANT CREATE PUBLIC SYNONYM TO AEDBADMIN

GRANT 'CONNECT' TO AEDBADMIN

GRANT 'EXECUTE_CATALOG_ROLE' TO AEDBADMIN

GRANT 'RESOURCE' TO AEDBADMIN

GRANT 'SELECT_CATALOG_ROLE' TO AEDBADMIN

Ensure the following Oracle SQL query results match in your environment

SQL> select PRIVILEGE, GRANTEE from dba_sys_privs where grantee='AEDBADMIN';

PRIVILEGE                         GRANTEE
--------------------------------  ------------------------------
CREATE PUBLIC SYNONYM             AEDBADMIN
CREATE VIEW                       AEDBADMIN


SQL> select GRANTED_ROLE, GRANTEE from dba_role_privs where grantee='AEDBADMIN';

GRANTED_ROLE                     GRANTEE
------------------------------   ------------------------------
UJOADMIN                         AEDBADMIN
EXECUTE_CATALOG_ROLE             AEDBADMIN
SELECT_CATALOG_ROLE              AEDBADMIN
CONNECT                          AEDBADMIN
RESOURCE                         AEDBADMIN

 

Additional Information:

 

CA DocOps: CreateAEDB Script-Create a Database

Oracle: Configuring Privilege and Role Authorization