what is causing account lock ?

check profile

     col PROFILE format a10

     col LIMIT format a15

     select * from dba_profiles where resource_type='PASSWORD' order by 1,2;

option 1

turn on trace : alter system set events '1017 trace name errorstack level 10';

     check listener , alert.log

     turn off trace : alter system set events '1017 trace name errorstack off';

option 2

col dbusername form a15

col userhost form a15

col event_timestamp form a30

select dbusername,userhost,event_timestamp 

   from unified_audit_trail 

   where return_code=1017 and 

   event_timestamp>sysdate-1/12 order by 3

option 3

     AUDIT connect whenever  NOT SUCCESSFUL;

     AUDIT SESSION WHENEVER NOT SUCCESSFUL;

     

        

   -- oracle doc : 352389.1

     AUDIT SESSION WHENEVER NOT SUCCESSFUL;

     select userid, userhost, terminal, clientid, TO_CHAR(NTIMESTAMP# , 'DD-MON-YYYY HH24:MI:SSxFF'), SESSIONTIMEZONE  from aud$ where returncode=1017

     and NTIMESTAMP# > sysdate - 1/12 ;

option 4

   -- oracle doc : 221944.1

     SQL>AUDIT CREATE SESSION BY ACCESS WHENEVER NOT SUCCESSFUL;

     SQL>AUDIT CONNECT BY ACCESS WHENEVER NOT SUCCESSFUL;

     

      col USERID format a10

      col USERHOST format a50

      col TERMINAL format a10

      col userhost  form a20

     select returncode, action#, userid, userhost, terminal, TO_CHAR(NTIMESTAMP# , 'DD-MON-YYYY HH24:MI:SSxFF') from aud$ where returncode='1017' and action#=100

    and NTIMESTAMP# > sysdate - 1/12 ;

     

     SELECT TO_CHAR(NTIMESTAMP# , 'DD-MON-YYYY HH24:MI:SSxFF'), SESSIONTIMEZONE   from aud$ where returncode=1017

     and NTIMESTAMP# > sysdate - 1/12 ;

Oracle 19c - CDB / PDB - useful sqls 

sqlplus / as sysdba

set lines 200

COL name FORMAT A30

col CON_ID form 999999

col OPEN_TIME FORMAT a40

col pdb form a30

COLUMN pdb_name FORMAT A20

SELECT con_id, name, open_mode, restricted, open_time  FROM gv$containers;

SELECT name, cdb, open_mode                     FROM   v$database;

SELECT name, pdb                                FROM   v$services  ORDER BY name;

select PDB_ID,PDB_NAME,CON_ID,STATUS,LOGGING,FORCE_LOGGING from dba_pdbs ORDER BY pdb_name;

SELECT con_id, name, open_mode, restricted      FROM   v$pdbs ORDER BY name;

select inst_id, con_id, name, recovery_status, open_mode from gv$pdbs

# the end

 

oracle 19c  - CDB/ PDB  _ Save the state of PDB

-- save the state, so that pdb will open when cdb is restarted

sqlplus / as sysdba

alter pluggable database all open;

ALTER PLUGGABLE DATABASE demo SAVE STATE instances=all;

-- check 

COLUMN con_name FORMAT A20

COLUMN instance_name FORMAT A20

SELECT con_name, instance_name, state FROM dba_pdb_saved_states;

# The end

limit PDB's to 3 in CDB in Oracle 19c

sqlplus / as sysdba

show parameter max_pdbs

ALTER SYSTEM SET max_pdbs=3 scope=both sid='*';

show parameter max_pdbs

# the end