Wednesday, August 26, 2020



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

Tuesday, August 21, 2018

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');

Monday, August 20, 2018

date time in python

#!/usr/bin/env python

import io
import datetime
import traceback

AlertLogFile=r'test.dat'
EventDate=datetime.datetime(1, 1, 1, 0, 0)
OutputList=[]

try:
    with io.open(AlertLogFile,mode='r') as f:
        for line in f:
          EventDate=datetime.datetime.strptime(line.rstrip('\n'), '%Y-%m-%d %H:%M:%S')
          # EventDate=datetime.datetime.strptime(line.rstrip('\n'), '%a %b %d %H:%M:%S %Y')
          # OutputList.append([EventDate,line.rstrip('\n')])
          OutputList.append([EventDate,line.rstrip('\n')])
except:
    print(traceback.format_exc())

for o in OutputList:
    # use o[0].strftime('%a %b %d %H:%M:%S %Y') to get original Oracle style Format
    print('[%s] %s' % (o[0],o[1]))
    # print('[%s]' % (o[0]))



Data
oracle@lnx21426> cat test.dat
2016-11-03 12:01:13
2017-04-05 12:03:28
oracle@lnx21426>