Oracle APEX 20.1 Admin Account Locked - Troubleshooting

I'm working on the Upgrade project where Oracle APEX 19.1 to Oracle APEX 20.1. All the steps which are given in the documentation I've followed. Able to login the the APEX Admin as well. Here the story begans, my other team members didn't observed there is a change in the ADMIN password and tried multiple times with OLD Password, then the ADMIN user was locked. Now even though I've correct password could not able to login to the APEX admin user. 

How do we know this?

Oracle Apex Workspace Admin login had an issue, where it was configured with a passwd which was mistakenly used old and that leads to lock the ADMIN user blocked.

This APEX Login failed and say that account is blocked. There could be two reasons. First, the browser cache holding the old and wrong password so not allowing you to log in to the ADMIN account. You could try on a different browser if you are in Chrome(login issue), use Firefox or Internet Explorer try to login.

Changing browser did not work!  Then follow the below-given steps.

Navigate to the apex install director (un-compressed software location). Example (/opt/apex_20.1). First, connect to your database with the sys user.

 The better approach is to run apex_util.set_security_group_id(with appropriate arguments) and apex_util.unlock_account() procedures to unlock the admin account in Oracle Apex.

This issue we will explore more in detail now. First, get the securit_group_id

 
select * from APEX_WORKSPACE_APEX_USERS;
-- To get the APEX related users info
select * from DBA_USERS where username like 'APEX%';
-- If you found LOCKED please unlock with the following
ALTER USER APEX_INSTANCE_ADMIN_USER ACCOUNT UNLOCK;

-- switch the session to APEX ADMIN session
alter session set current_schema = APEX_200100;
-- Get all the ADMIN user details
SELECT * FROM wwv_flow_fnd_user WHERE  user_name = 'ADMIN';

-- setting secuirty group 

begin
wwv_flow_api.set_security_group_id(p_security_group_id=>nvl(wwv_flow_application_install.get_workspace_id,11workspace_id000));
end;
/

Now use that security_group_id and unlock with the following procedure from the apex folder.

 
alter session set current_schema = APEX_200100;
Session altered.

Run the above query in SQL Developer get the g_security_group_id value.

begin
    wwv_flow_security.g_security_group_id := 1467836176007596;
    wwv_flow_fnd_user_api.UNLOCK_ACCOUNT('ADMIN');
    commit;
end;
/  

PL/SQL procedure successfully completed.

SELECT * FROM wwv_flow_fnd_user WHERE  user_name = 'ADMIN';
or 
SELECT user_id,first_name,last_name, default_schema FROM wwv_flow_fnd_user WHERE  user_name = 'ADMIN';

begin 
  apex_util.edit_user(p_user_id => '11workspace_id000', 
  p_user_name => 'ADMIN', 
  p_web_password => 'Welcome1', 
  p_new_password => 'Welcome1');
end;
/

commit;
/

The above mentioned step resolved the issue!

Write your comments/Suggestions on this issue.

SOA Database Adapter experiment

Hey, Here I am going to create a Database user which can be used for demonstrate the database adapter.

 grant connect, resource to soademo identified by soademo;

create a table

 CREATE TABLE CREDITCARDINFO
(
SSN VARCHAR2(15) NOT NULL,
FIRST_NAME VARCHAR2(30),
LAST_NAME VARCHAR2(30),
CCNUMBER VARCHAR2(20) NOT NULL,
CREDITRATING NUMBER,
STATUS VARCHAR2(20) NOT NULL
);

create a Table using XE Apex tool

Insert records in to table
Now, lets do insert operation on the table that was created following records for testing purpose

 insert into CREDITCARDINFO VALUES (
    '111-11-1111',
    'Neena',
    'Kochhar',
    '1234-1234-1234-1234',
 '3'
 , 'VALID'
);

insert into CREDITCARDINFO VALUES (
    '222-22-2222',
    'Steven',
    'King',
    '5678-5678-5678-5678',
 '4'
 , 'VALID'
);

insert into CREDITCARDINFO VALUES (
    '333-33-3333',
    'Lex',
    'Devarakonda',
    '4321-4321-4321-4321',
 '5'
 , 'INVALID'
);

insert into CREDITCARDINFO VALUES (
    '444-44-4444',
    'Alexander',
    'Hunold',
    '8765-8765-8765-8765',
 '1'
 , 'VALID'
);

Now lets create a datasource that uses above created schema that is soademo.