Oracle REST Data Service apex_pu

Question

I tried to install Oracle REST Data Service and I got this error: (on java -jar ords.war install)

SEVERE: The pool named: apex_pu is not correctly configured, 
error: ORA-01017: invalid username/password; logon denied

oracle.dbtools.common.jdbc.ConnectionPoolException: 
The pool named: apex_pu is not correctly configured, 
error: ORA-01017: invalid username/password; logon denied

        at oracle.dbtools.common.jdbc.ConnectionPoolException.badConfiguration(ConnectionPoolException.java:88)
        at oracle.dbtools.common.config.db.DatabasePools.badConfiguration(DatabasePools.java:198)

Answer 1

The pool mentioned "apex_pu" is contained in the file conf/apex_pu.xml This file is for ORDS_PUBLIC_USER.

The error could be from 1 of 2 reasons.

1 - ORDS_PUBLIC_USER account passwd has changed or expired. Change it and edit the apex_pu.xml file and place a ! in front of the password. This will trigger ORDS to encode the password at next startup.

<entry key="db.password">!NewPASSWORD</entry>

2 - The DB also throws this error if the proxy grant has been revoke from the user to execute the REST call as. If this has happened the grant can be reissued with the following

alter user USERNAME grant connect through ords_public_user

Answer 2

I solved the problem by changing "Default tablespace" for user APEX_PUBLIC_USER.

java -jar ords.war uninstall 

then again

java -jar ords.war install

Answer 3

The new ORDS 3 doesn't necessarily need APEX to function.

ORDS 3 can connect to it's own schemas without the use of APEX, it can also connect to Oracle NOSQL Database: Oracle REST Enables Oracle Database and Oracle NoSQL

Basically you now have two methods to set up ORDS

1. With APEX

   java -jar ords.war setup

Say no (2) for verify/install Oracle REST Data Services schema

Say yes (1) for PL/SQL Gateway (APEX, you still get REST through apex_rest_public_user)

This uses the APEX Public users. (APEX_PUBLIC_USER, APEX_LISTENER, APEX_REST_PUBLIC_USER)

2. Oracle REST Data services

   java -jar ords.war install

This creates the ORDS schemas (ORDS_PUBLIC_USER, ORDS_METADATA)

java -jar ords.war setup

Say Yes (1) for verify/install Oracle REST Data Services schema

Say No (2) for PL/SQL Gateway (Apex)

You are probably giving the APEX password to the ORDS user.

Hope this clarifies.

Answer 4

Change username, password in the file apex_pu.xml to values in the apex.xml.

Answer 5

You need to change APEX_PUBLIC_USER password:

java -jar ords.war user APEX_PUBLIC_USER

And re enter password.

Answer 6

After you have installed Oracle REST Data Services, you have to run the script @apex_rest_config.sql, in order to create APEX_LISTENER and APEX_REST_PUBLIC_USER accounts.

Answer 7

As noted above by Rice the user ORDS_PUBLIC_USER has to be released from expiration. When working on container DB this user can exists in Container-DB as well as in ROOT-DB. You have to check the root-DB, where it must not be expired there as well.

Reinstallation as suggested by Cioanta is not a sustainable solution. The primary cause of the error is that ORDS_PUBLIC_USER is assigned to DEFAULT user profile and thus it's password expires after 180 days. This should be given for a noninteractive user of a connection service, even though it's the default after oracle's setup script.

To avoid to problem to recure each 180 days do this:

select profile from DBA_USERS where username = 'ORDS_PUBLIC_USER';
--> DEFAULT
select * from V$PARAMETER where name like 'common_user_prefix';
-- > C##

CREATE PROFILE C##UNLIMITED_PWD_EXPIRATION LIMIT
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL UNLIMITED
CONNECT_TIME UNLIMITED
IDLE_TIME UNLIMITED
SESSIONS_PER_USER UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
PRIVATE_SGA UNLIMITED
COMPOSITE_LIMIT UNLIMITED
PASSWORD_LIFE_TIME UNLIMITED
PASSWORD_GRACE_TIME DEFAULT
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_LOCK_TIME 1
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_VERIFY_FUNCTION VERIFY_FUNCTION_11G;

-- optional, ist hier ja schon gesetzt
alter profile <User_profile> limit password_life_time UNLIMITED;

ALTER USER ORDS_PUBLIC_USER PROFILE C##UNLIMITED_PWD_EXPIRATION;
SELECT username, account_status, created, lock_date, expiry_date FROM dba_users  where username = 'ORDS_PUBLIC_USER';
-- no expiration date set nomore