ORA-28000: the account is locked:Exception occurred: java.sql.SQLException:

 Error :

..
Exception occurred: java.sql.SQLException: ORA-28000: the account is locked
ORA-28000: the account is locked
...

The following ORACLE error:
ORA-28000: the account is locked
occurred while executing the SQL statement:

Cause :
​
The issue is caused by a locked APPS Database account.

The following script allows you to check the number of failed logins, which can lock the apps user :

select profile , resource_name, limit
from dba_profiles
where resource_name ='FAILED_LOGIN_ATTEMPTS';

Example output :

DEFAULT FAILED_LOGIN_ATTEMPTS : 10
AD_PATCH_MONITOR_PROFILE FAILED_LOGIN_ATTEMPTS : 3

Solution :

​To implement the solution test the following steps in a Development instance and then migrate accordingly:

1. Ensure that you have taken a backup of your Environment.

2. Increase the number of failed logins allowed before the apps user is locked(optional):

alter profile AD_PATCH_MONITOR_PROFILE limit failed_login_attempts unlimited;

alter profile default limit failed_login_attempts unlimited password_lock_time 1/1440;

3.Unlock the locked APPS User account :

alter user apps account unlock;

4. Rerun the failed cloning commands and confirm the error is resolved.

How update statement works in oracle

UPDATE command in sql is used to modify existing records in a table. How SQL UPDATE statement works inside the Oracle architecture and the sequence of steps which oracle follow internally for the execution of update statement is explained below.

DATE Syntax
UPDATE emp
SET column1 = value1, column2=value2, …
WHERE condition;


How does the update query execution occur?

1.SQL*PLUS checks the syntax on client side.
 
2. If syntax is correct the query is stamped as a valid sql statement and encrypted into  oci (oracle call interface) packets and sent via LAN using TCP to the server.

3. Once the packets reach the server the server process will rebuild the query and again perform a  syntax check on server side.

Then if syntax is correct server process will continue execution of the query.

4.The server process will go to the library cache. The library cache keeps the recently executed  sql statements along with their execution plan.

  In the library cache the server process will search from the mru (most recently used) end to the lru (least recently used) end for a match for the sql statement. It does this by using a hash algorithm that returns a hash value. If the hash value of the query we have written matches with that of the query in library cache then server process need not generate an execution plan (soft parsing) but if no match is found then server process has to proceed with the generation of execution plan (hard parsing).

Parsing is the process undertaken by oracle to generate an execution plan.


5. The first step in parsing involves performing a symantic check. This is nothing but   check for the existence of the obj and its structure in the database.


This check is done by server process in the data dictionary cache. Here server process will ask for the definition of the object, if already available within the data dictionary cache, server process will process the check. If not available then server process will retrieve the required information from the system tablespace.


6. After this in case of hard parsing the server process will approach the optimizer, who will read the sql statement and generate the execution plan of the query. The optimizer generates multiple execution plans during parsing.

7. After generation of the e-plan’s by the optimizer the server process will pick the best possible and cost effective e-plan and go to the library cache.

In the library cache the server process will keep the e-plan in the library cache along with the original sql text.
 
At this point in time the parsing ends and the execution of the sql statement will begin.

 
8. After generation of e-plan server process will keep the plan in the library cache on the MRU end .

Thereafter the plan is picked up by the server process and execution of the update will begin.

 
9. Server process will bring the required blocks from the specific datafile of the table  which has to be updated.

The blocks will be brought into database block buffers(or database buffer cache).
The blocks will be containing the original data of the table.


10. Then server process will bring equal no of empty blocks from the undo tablespace  and they will also be brought into the database block buffers(or database buffer  cache).


11. Server process will copy the original data from the userdata blocks into the empty rollback/undo blocks and create a before image.

12. Then server process will bring a set of userdata blocks into the pga (program  global area) and after performing filter operations the selected rows will be   updated with new content.


The above update process will continue until all the userdata blocks have been checked and updated.
​
After the update operation is complete then dbwriter will write the data back to the respective datafiles after a certain time gap.

How to identify whether the Downtime is required or not to change Oracle database parameters

 Sometimes we don't know whether we can change the parameters dynamically or not in the oracle database.

We can get those information from v$parameter view.

Below is the script to confirm it .

select NAME,ISINSTANCE_MODIFIABLE from  v$parameter where NAME='&Parameter';

If ISINSTANCE_MODIFIABLE values show TRUE then we can modify the parameter without bringing down the database 
if it is FALSE we need to bring down the database to modify the parameters .

Example:

select NAME,ISINSTANCE_MODIFIABLE from v$parameter where NAME='undo_management';

NAME                     ISINS 
------------------------------ ---------
undo_management          FALSE 

​select NAME,ISINSTANCE_MODIFIABLE from v$parameter where NAME='large_pool_size';

NAME                 ISINS   
-------------------- 
large_pool_size      TRUE 

Scripts to find Locks on Package in Oracle Database

​Sometimes when we try to compile or create  packages it may hang ,
Generally package compilation or creating doesn't take much time in the oracle database even if there is an issue with PL/SQL code .

So we need to find the cause when Compiling Oracle Packages takes a lot of Time.
Basically it happens due to locking sessions so we need to identify the locking sessions whatever using the same package and kill it .

Below are the scripts to find the locking session details .

Script 1:

select * from dba_ddl_locks where name like '%PACKAGE NAME%';

Script 2 :

SELECT s.sid,
l.lock_type,
l.mode_held,
l.mode_requested,
l.lock_id1,
FROM dba_lock_internal l,
 v$session s
    WHERE s.sid = l.session_id
    AND UPPER(l.lock_id1) LIKE '%PACKAGE NAME%'
    AND l.lock_type ='Body Definition Lock';

Script 3:
Get the session details which are blocking package definition.

COL sid    FOR 999999
COL lock_type  FOR A38
COL mode_held  FOR A12
COL mode_requested FOR A12
COL lock_id1  FOR A20
COL lock_id2  FOR A20
COL kill_sid    FOR A50
SELECT s.sid,
       l.lock_type,
       l.mode_held,
       l.mode_requested,
       l.lock_id1,
       'alter system kill session '''|| s.sid|| ','|| s.serial#|| ''' immediate;' kill_sid
FROM   dba_lock_internal l,
       v$session s
WHERE  s.sid = l.session_id
AND    UPPER(l.lock_id1) LIKE '%&package_name%'
AND    l.lock_type = 'Body Definition Lock'
/

Script 4: 
Check out what the offending session is doing .

SELECT s.sid,
       NVL(s.username, 'ORACLE PROC') username,
       s.osuser,
       p.spid os_pid,
       s.program,
       t.sql_text
FROM   v$session s,
       v$sqltext t,
       v$process p
WHERE  s.sql_hash_value = t.hash_value
AND    s.paddr = p.addr
AND    s.sid = &session_id
AND    t.piece = 0 -- optional to list just the first line
ORDER BY s.sid, t.hash_value, t.piece
/
Script 5:  
You can get more info about the offending sessions by using below SID and PID script.
if those sessions  are related to concurrent requests ,you can find the info from the scripts below .
Get the current running and previous runs history and take the necessary action .

You can Get the concurrent request ID and Name by using below scirpt .

set echo off pages 100 lines 202
column REQUEST heading 'Request' format a9
column PHASE heading 'Phase' format A8
column STATUS heading 'Status' format A8
column PROGRAM heading 'Program Name' format A40
column SHORT heading 'Short Name' format A15
column REQUESTOR heading 'Requestor' format A15
column START_TIME heading 'Start Time' format A15
column RUN_TIME justify left heading 'Time(e)' format 999999.9
column OSPID heading 'OSPID' format a5
column OS_PIDa heading 'OSPIDA' format a6
column SID heading 'SID' format 99999
column serial# heading 'Serial#' format 99999
select substr(fcrv.request_id,1,9)REQUEST,
decode(fcrv.phase_code,'P','Pending','R','Running','I','Inactive','Completed')PHASE,
decode(fcrv.status_code,
'A','Waiting',
'B','Resuming',
'C','Normal',
'F','Scheduled',
'G','Warning',
'H','On Hold',
'I','Normal',
'M','No Manager',
'Q','Standby',
'R','Normal',
'S','Suspended',
'T','Terminating',
'U','Disabled',
'W','Paused',
'X','Terminated',
'Z','Waiting',fcrv.status_code)STATUS,
substr(fcrv.program,1,40)PROGRAM,
substr(fcrv.PROGRAM_SHORT_NAME,1,15)SHORT,
substr(fcrv.requestor,1,15)REQUESTOR,
-- to_char(fcrv.actual_start_date,'MM/DD/RR HH24:MI')START_TIME,
round(((sysdate - fcrv.actual_start_date)*1440),1)RUN_TIME,
substr(fcr.oracle_process_id,1,7)OSPID,
s.sid,s.serial#
--substr(fcr.os_process_id,1,7)OS_PIDa
from apps.fnd_conc_req_summary_v fcrv,
apps.fnd_concurrent_requests fcr,
v$session s,
v$process p
where fcrv.phase_code = 'R'
and fcrv.request_id = fcr.request_id
and s.paddr = p.addr
and fcr.oracle_process_id = p.spid
and fcrv.concurrent_program_id not in ('40112','40113','36887')
--and trunc(fcrv.actual_start_date) like trunc(sysdate)
order by PHASE, STATUS, REQUEST desc;

Script 6: 
To know the history of the Requests /Previous Runs :

SELECT a.request_id,b.user_concurrent_program_name,to_char(a.actual_start_date,'DD-MON-RR HH24:MI:SS'),
to_char(a.actual_completion_date,'DD-MON-RR HH24:MI:SS'),
decode(a.phase_code,'R','Running','P','Inactive','C','Completed', a.phase_code),
decode(a.status_code,'E','Error',   'C','Normal',    
'X','Terminated', 'Q','On Hold', 'D','Cancelled', 'G','Warning', 
'R','Normal',  'W', 'Paused',   a.status_code),
a.ARGUMENT_TEXT FROM apps.fnd_concurrent_programs_vl b,apps.fnd_concurrent_requests a
WHERE  a.concurrent_program_id = b.concurrent_program_id and
b.user_concurrent_program_name like 
'%&CONCURRENT_PROGRAM%' order by 3;

Script 7:
Run the kill session command (kill_cmd) generated from the script 3 output in the first script if required .

alter system kill session '218,12455' immediate;

​

 

​​​​​​     NARESH NADHAN 'S                 Oracle Database Administrator "When we set out a long journey, we do feel that the destination is just around the corner. Taking the turn, we realize that we still have a long way to go. When I start writing this blog has a similar journey. I believe goal setting is a powerful process for thinking about our ideal future, and for motivating ourselves to turn our vision of   ​​​​​​future into reality. The process of setting goals helps us choose where we want to go in life. A Man with a goal in the mind makes himself majority." Naresh Nadhan holds a bachelor’s degree in science and Master of Computer Applications from VIKRMA SIMHAPURI University, SPSR Nellore. He has 3+ years of work experience involving installation and maintenance of various Oracle Products. Currently working as a Oracle Apps DBA having expertise in Patching, Cloning ,BS Implementations and Upgrade projects. ​He is a Oracle Cloud certified associate and blogs about his technical thoughts at https://nareshnadhans.weebly.com/  and https://nareshnadhas.blogspot.com/. I Promise, Nadhan’s Technologies is the one of the best blogs for Oracle E Business Suite and Oracle Databases to expand your skills and knowledge.  We will explain about both Oracle E Business Suite and Oracle Databases concepts. This blog is intended to share my minimum knowledge so the Concepts will be explained based on real time scenarios, it works which we do in companies. "