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

troubleshoot - scheduler job not running

troubleshoot - scheduler job not running

sqlplus  Scott/tiger

-- list scheduler job

set lines 120
col SCHEDULE_NAME format a25
col SCHEDULE_TYPE form   a10
prompt from dba_scheduler_jobs
prompt =============
select job_name, schedule_name, schedule_type, enabled from dba_scheduler_jobs where owner=upper('&schema') ;

-NOTE in the enabled column - TRUE means working, FALSE means not working

-- identify scheduler running jobs
 select
   rj.job_name,
   s.username,
   s.sid,
   s.serial#,
   p.spid,
   s.lockwait,
   s.logon_time
from 
   user_scheduler_running_jobs rj,
   v$session s,
   v$process p
where
   rj.session_id = s.sid
and
   s.paddr = p.addr
order by
   rj.job_name
;



SELECT max(log_date) FROM user_scheduler_job_run_details where job_name='SCH_JOB_NAME_HERE'' 


-- to kill by sid


select 'alter system kill session '||chr(39)||s.sid||','||s.serial#||chr(39)||';'
from user_scheduler_running_jobs rj,
     v$session s,
     v$process p
where rj.session_id = s.sid
  and s.paddr = p.addr
order by rj.job_name;


-- to kill by by pid

select 'kill -9 '||p.spid
from
   user_scheduler_running_jobs rj,
   v$session s,
   v$process p
where
   rj.session_id = s.sid
and
   s.paddr = p.addr
order by
   rj.job_name
;


useful commands
exec dbms_scheduler.disable('SCH_JOB_NAME_HERE');
exec dbms_scheduler.enable('"SCH_JOB_NAME_HERE"');

exec dbms_scheduler.stop_job('SCH_JOB_NAME_HERE');