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

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>

Use ADRCI or Support Workbench to package the incident.

step 1
adrci> show alert -p "message_text like '%incident%'"

ADR Home = /oramisc01/oracle/diag/rdbms/demoprim/demo1:
*************************************************************************
Output the results to file: /ora01/oracle/temp/alert_33812_140499732489984_demo_1.ado


step 2
adrci> show problem

ADR Home = /oramisc01/oracle/diag/rdbms/demoprim/demo1:
*************************************************************************
PROBLEM_ID           PROBLEM_KEY                                                 LAST_INCIDENT        LASTINC_TIME                
-------------------- ----------------------------------------------------------- -------------------- ----------------------------------------
1                    ORA 600 [ktbdchk1: bad dscn]                                80396                2018-08-19 16:39:16.523000 -04:00
1 rows fetched

step 3
adrci> show incident

ADR Home = /oramisc01/oracle/diag/rdbms/demoprim/demo1:
*************************************************************************
INCIDENT_ID          PROBLEM_KEY                                                 CREATE_TIME
-------------------- ----------------------------------------------------------- ----------------------------------------
80393                ORA 600 [ktbdchk1: bad dscn]                                2018-08-19 16:38:39.543000 -04:00
80394                ORA 600 [ktbdchk1: bad dscn]                                2018-08-19 16:39:14.585000 -04:00
80395                ORA 600 [ktbdchk1: bad dscn]                                2018-08-19 16:39:15.629000 -04:00
80396                ORA 600 [ktbdchk1: bad dscn]                                2018-08-19 16:39:16.523000 -04:00
4 rows fetched

step 4
show incident -mode detail -p "incident_id=80393"

step 5
First, create the package using the problem ID displayed by the SHOW PROBLEM command.
adrci > ips create package problem 1 correlate all

step 6
Next, create a zip to send to Oracle Support by specifying the package number displayed by the above command.
adrci > ips generate package 1 in "/tmp"

step 6
send the zip file to oracle