get list of user accounts - either unlocked or password is expired.

 

SELECT    

     'ALTER '||

     SUBSTR(DBMS_LOB.SUBSTR(DBMS_METADATA.GET_DDL('USER',USERNAME),

     INSTR(DBMS_LOB.SUBSTR(DBMS_METADATA.GET_DDL('USER',USERNAME),500),'DEFAULT')-1) ,

     INSTR(DBMS_LOB.SUBSTR(DBMS_METADATA.GET_DDL('USER',USERNAME),500),'USER')) USER_PASS , USERNAME, PROFILE 

FROM  DBA_USERS 

WHERE USERNAME IN 

     (SELECT USERNAME  

      FROM DBA_USERS 

      WHERE (USERNAME  in

            (select username  from dba_users where (account_status like '%EXPIRED%' or account_status like '%LOCK%'))));

search a pattern in file using Python

 import re

pattern = re.compile("ORA-")

for line in open("x2.txt"):

    for match in re.finditer(pattern, line):

        print(line)

count number of files generated per day

 # count number of files generated per day

find . -type f -printf '%TY-%Tm-%Td\n' | sort | uniq -c

# count number of files generated per hour in last 600 minutes

find . -cmin -600  -type f -printf '%TY-%Tm-%Td-%TH\n' | sort | uniq -c

find . -maxdepth 1 -type f -printf '%TY-%Tm-%Td\n' | awk '{array[$0]+=1}END{ for(val in array) print val" "array[val]   }'|sort

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