Multi-Process Multi-Threaded Architecture in Oracle 12c

Configure Oracle instance in multi-process multi-threaded architecture to reduce system CPU/Memory resources usage.

Overview

In default Unix/Linux architecture, every Oracle process including background and foreground ones runs as a dedicated OS process.

In 12c release 1 ( 12.1.0.x ) multi-process multi-threaded architecture, those four background processes ( PMON, DBW, VKTM and PSP) run as dedicated OS processes, the rest processes can be configured running as a thread, which largely saves CPU and Memory usage.

Configure Multi-Process Multi-Threaded for Background Processes

  • In default Unix/Linux architecture, every process runs as a dedicated OS process
$ ps -eLo "pid tid comm args"|grep cdb2
 6163 6163 ora_pmon_cdb2 ora_pmon_cdb2
 6165 6165 ora_psp0_cdb2 ora_psp0_cdb2
 6167 6167 ora_vktm_cdb2 ora_vktm_cdb2
 6171 6171 ora_gen0_cdb2 ora_gen0_cdb2
 6173 6173 ora_mman_cdb2 ora_mman_cdb2
 6177 6177 ora_diag_cdb2 ora_diag_cdb2
 6179 6179 ora_dbrm_cdb2 ora_dbrm_cdb2
 6181 6181 ora_vkrm_cdb2 ora_vkrm_cdb2
 6183 6183 ora_dia0_cdb2 ora_dia0_cdb2
 6185 6185 ora_dbw0_cdb2 ora_dbw0_cdb2
 6187 6187 ora_lgwr_cdb2 ora_lgwr_cdb2
 6189 6189 ora_ckpt_cdb2 ora_ckpt_cdb2
 6191 6191 ora_lg00_cdb2 ora_lg00_cdb2
 6193 6193 ora_smon_cdb2 ora_smon_cdb2
 6195 6195 ora_lg01_cdb2 ora_lg01_cdb2
...............
................
.............
  • Change parameter and shutdown database
SQL> alter system set threaded_execution=true scope=spfile;
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
  • Startup database by using password authentication only
SQL> connect / as sysdba
ERROR:
ORA-01017: invalid username/password; logon denied

SQL> connect sys as sysdba
Enter password: 
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 524288000 bytes
Fixed Size 2926320 bytes
Variable Size 390072592 bytes
Database Buffers 125829120 bytes
Redo Buffers 5459968 bytes
Database mounted.
Database opened.
SQL>
  • In multi-process multi-threaded architecture, most processes runs as a thread of  OS process
$ ps -eLo "pid tid comm args"|grep cdb2
13433 13433 ora_pmon_cdb2 ora_pmon_cdb2
13435 13435 ora_psp0_cdb2 ora_psp0_cdb2
13437 13437 ora_vktm_cdb2 ora_vktm_cdb2
13454 13454 ora_dbw0_cdb2 ora_dbw0_cdb2
13441 13441 ora_scmn_cdb2 ora_u004_cdb2
13441 13442 oracle ora_u004_cdb2
13441 13443 ora_gen0_cdb2 ora_u004_cdb2
13441 13444 ora_mman_cdb2 ora_u004_cdb2
13441 13450 ora_dbrm_cdb2 ora_u004_cdb2
13441 13455 ora_lgwr_cdb2 ora_u004_cdb2
13441 13456 ora_ckpt_cdb2 ora_u004_cdb2
13441 13457 ora_lg00_cdb2 ora_u004_cdb2
13441 13458 ora_smon_cdb2 ora_u004_cdb2
13441 13459 ora_lg01_cdb2 ora_u004_cdb2
13441 13461 ora_lreg_cdb2 ora_u004_cdb2
13441 13473 ora_rvwr_cdb2 ora_u004_cdb2
13447 13447 ora_scmn_cdb2 ora_u005_cdb2
13447 13448 oracle ora_u005_cdb2
13447 13449 ora_diag_cdb2 ora_u005_cdb2
13447 13451 ora_vkrm_cdb2 ora_u005_cdb2
13447 13452 ora_dia0_cdb2 ora_u005_cdb2
13447 13460 ora_reco_cdb2 ora_u005_cdb2
13447 13462 ora_pxmn_cdb2 ora_u005_cdb2
13447 13463 ora_mmon_cdb2 ora_u005_cdb2
13447 13464 ora_mmnl_cdb2 ora_u005_cdb2
13447 13465 ora_d000_cdb2 ora_u005_cdb2
13447 13466 ora_s000_cdb2 ora_u005_cdb2
13447 13467 ora_n000_cdb2 ora_u005_cdb2
13447 13474 oracle_13474_cd ora_u005_cdb2
13447 13475 ora_tmon_cdb2 ora_u005_cdb2
13447 13476 ora_arc0_cdb2 ora_u005_cdb2
13447 13477 ora_arc1_cdb2 ora_u005_cdb2
13447 13478 ora_arc2_cdb2 ora_u005_cdb2
13447 13479 ora_arc3_cdb2 ora_u005_cdb2
13447 13480 ora_tt00_cdb2 ora_u005_cdb2
13447 13481 ora_smco_cdb2 ora_u005_cdb2
13447 13482 ora_w000_cdb2 ora_u005_cdb2
13447 13483 ora_w001_cdb2 ora_u005_cdb2
13447 13484 ora_aqpc_cdb2 ora_u005_cdb2
13447 13486 ora_p000_cdb2 ora_u005_cdb2
13447 13487 ora_p001_cdb2 ora_u005_cdb2
13447 13488 ora_p002_cdb2 ora_u005_cdb2
13447 13489 ora_p003_cdb2 ora_u005_cdb2
13447 13490 ora_p004_cdb2 ora_u005_cdb2
13447 13491 ora_p005_cdb2 ora_u005_cdb2
13447 13492 ora_p006_cdb2 ora_u005_cdb2
13447 13493 ora_p007_cdb2 ora_u005_cdb2
13447 13494 ora_qm02_cdb2 ora_u005_cdb2
13447 13496 ora_q002_cdb2 ora_u005_cdb2
13447 13497 ora_q003_cdb2 ora_u005_cdb2
13447 13572 ora_q004_cdb2 ora_u005_cdb2
13447 13602 ora_q005_cdb2 ora_u005_cdb2
13447 13616 ora_cjq0_cdb2 ora_u005_cdb2
13447 13640 ora_p008_cdb2 ora_u005_cdb2
13447 13641 ora_p009_cdb2 ora_u005_cdb2
13447 13642 ora_p00a_cdb2 ora_u005_cdb2
13447 13643 ora_p00b_cdb2 ora_u005_cdb2
13447 13647 ora_p00c_cdb2 ora_u005_cdb2
13447 13648 ora_p00d_cdb2 ora_u005_cdb2
13447 13649 ora_p00e_cdb2 ora_u005_cdb2
13447 13650 ora_p00f_cdb2 ora_u005_cdb2
13447 13651 ora_p00g_cdb2 ora_u005_cdb2
13447 13652 ora_p00h_cdb2 ora_u005_cdb2
13447 13653 ora_p00i_cdb2 ora_u005_cdb2
13447 13654 ora_p00j_cdb2 ora_u005_cdb2
13447 13661 ora_p00k_cdb2 ora_u005_cdb2
13447 13662 ora_p00l_cdb2 ora_u005_cdb2
13447 13663 ora_p00m_cdb2 ora_u005_cdb2
13447 13664 ora_p00n_cdb2 ora_u005_cdb2
13447 13731 ora_j000_cdb2 ora_u005_cdb2
13447 13732 ora_j001_cdb2 ora_u005_cdb2
$ps -eaf | grep cdb2
oracle 13433 1 0 14:44 ? 00:00:00 ora_pmon_cdb2
oracle 13435 1 0 14:44 ? 00:00:00 ora_psp0_cdb2
oracle 13437 1 11 14:44 ? 00:01:02 ora_vktm_cdb2
oracle 13441 1 0 14:44 ? 00:00:03 ora_u004_cdb2
oracle 13447 1 24 14:44 ? 00:02:16 ora_u005_cdb2
oracle 13454 1 0 14:44 ? 00:00:00 ora_dbw0_cdb2

There are only 6 OS processes for all background proesses

Configure Multi-Process Multi-Threaded for Foreground Processes

  • Create multiple sessions by using below script, we can see the foreground processes are using OS processes instead of threads.
$ cat create_sessions.sh
#!/bin/bash
for ((i=1; i <= 40 ; i++))
do
  nohup sqlplus -S "system/password"@cdb2<< EOT &
  begin
    dbms_lock.sleep(1020);
  end;
/
EOT
sleep 1;
done
$ ps -eLo "pid tid comm args"|grep cdb2
13433 13433 ora_pmon_cdb2 ora_pmon_cdb2
13435 13435 ora_psp0_cdb2 ora_psp0_cdb2
13437 13437 ora_vktm_cdb2 ora_vktm_cdb2
13441 13441 ora_scmn_cdb2 ora_u004_cdb2
13441 13442 oracle ora_u004_cdb2
13441 13443 ora_gen0_cdb2 ora_u004_cdb2
13441 13444 ora_mman_cdb2 ora_u004_cdb2
13441 13450 ora_dbrm_cdb2 ora_u004_cdb2
13441 13455 ora_lgwr_cdb2 ora_u004_cdb2
13441 13456 ora_ckpt_cdb2 ora_u004_cdb2
13441 13457 ora_lg00_cdb2 ora_u004_cdb2
13441 13458 ora_smon_cdb2 ora_u004_cdb2
13441 13459 ora_lg01_cdb2 ora_u004_cdb2
13441 13461 ora_lreg_cdb2 ora_u004_cdb2
13441 13473 ora_rvwr_cdb2 ora_u004_cdb2
13447 13447 ora_scmn_cdb2 ora_u005_cdb2
13447 13448 oracle ora_u005_cdb2
13447 13449 ora_diag_cdb2 ora_u005_cdb2
13447 13451 ora_vkrm_cdb2 ora_u005_cdb2
13447 13452 ora_dia0_cdb2 ora_u005_cdb2
13447 13460 ora_reco_cdb2 ora_u005_cdb2
13447 13462 ora_pxmn_cdb2 ora_u005_cdb2
13447 13463 ora_mmon_cdb2 ora_u005_cdb2
13447 13464 ora_mmnl_cdb2 ora_u005_cdb2
13447 13465 ora_d000_cdb2 ora_u005_cdb2
13447 13466 ora_s000_cdb2 ora_u005_cdb2
13447 13467 ora_n000_cdb2 ora_u005_cdb2
13447 13474 oracle_13474_cd ora_u005_cdb2
13447 13475 ora_tmon_cdb2 ora_u005_cdb2
13447 13476 ora_arc0_cdb2 ora_u005_cdb2
13447 13477 ora_arc1_cdb2 ora_u005_cdb2
13447 13478 ora_arc2_cdb2 ora_u005_cdb2
13447 13479 ora_arc3_cdb2 ora_u005_cdb2
13447 13480 ora_tt00_cdb2 ora_u005_cdb2
13447 13481 ora_smco_cdb2 ora_u005_cdb2
13447 13482 ora_w000_cdb2 ora_u005_cdb2
13447 13483 ora_w001_cdb2 ora_u005_cdb2
13447 13484 ora_aqpc_cdb2 ora_u005_cdb2
13447 13486 ora_p000_cdb2 ora_u005_cdb2
13447 13487 ora_p001_cdb2 ora_u005_cdb2
13447 13488 ora_p002_cdb2 ora_u005_cdb2
13447 13489 ora_p003_cdb2 ora_u005_cdb2
13447 13490 ora_p004_cdb2 ora_u005_cdb2
13447 13491 ora_p005_cdb2 ora_u005_cdb2
13447 13492 ora_p006_cdb2 ora_u005_cdb2
13447 13493 ora_p007_cdb2 ora_u005_cdb2
13447 13494 ora_qm02_cdb2 ora_u005_cdb2
13447 13497 ora_q003_cdb2 ora_u005_cdb2
13447 13602 ora_q005_cdb2 ora_u005_cdb2
13447 13616 ora_cjq0_cdb2 ora_u005_cdb2
13447 13640 ora_p008_cdb2 ora_u005_cdb2
13447 13641 ora_p009_cdb2 ora_u005_cdb2
13447 13642 ora_p00a_cdb2 ora_u005_cdb2
13447 13643 ora_p00b_cdb2 ora_u005_cdb2
13447 17191 ora_w002_cdb2 ora_u005_cdb2
13454 13454 ora_dbw0_cdb2 ora_dbw0_cdb2
17362 17362 ora_scmn_cdb2 ora_u000_cdb2
17362 17364 oracle ora_u000_cdb2
17362 17654 ora_w003_cdb2 ora_u000_cdb2
18611 18611 oracle_18611_cd oraclecdb2 (LOCAL=NO)
18615 18615 oracle_18615_cd oraclecdb2 (LOCAL=NO)
18619 18619 oracle_18619_cd oraclecdb2 (LOCAL=NO)
18623 18623 oracle_18623_cd oraclecdb2 (LOCAL=NO)
18642 18642 oracle_18642_cd oraclecdb2 (LOCAL=NO)
18646 18646 oracle_18646_cd oraclecdb2 (LOCAL=NO)
18650 18650 oracle_18650_cd oraclecdb2 (LOCAL=NO)
18654 18654 oracle_18654_cd oraclecdb2 (LOCAL=NO)
18658 18658 oracle_18658_cd oraclecdb2 (LOCAL=NO)
18662 18662 oracle_18662_cd oraclecdb2 (LOCAL=NO)
18666 18666 oracle_18666_cd oraclecdb2 (LOCAL=NO)
18670 18670 oracle_18670_cd oraclecdb2 (LOCAL=NO)
18674 18674 oracle_18674_cd oraclecdb2 (LOCAL=NO)
18678 18678 oracle_18678_cd oraclecdb2 (LOCAL=NO)
18682 18682 oracle_18682_cd oraclecdb2 (LOCAL=NO)
18686 18686 oracle_18686_cd oraclecdb2 (LOCAL=NO)
18690 18690 oracle_18690_cd oraclecdb2 (LOCAL=NO)
18694 18694 oracle_18694_cd oraclecdb2 (LOCAL=NO)
18698 18698 oracle_18698_cd oraclecdb2 (LOCAL=NO)
18702 18702 oracle_18702_cd oraclecdb2 (LOCAL=NO)
18706 18706 oracle_18706_cd oraclecdb2 (LOCAL=NO)
18710 18710 oracle_18710_cd oraclecdb2 (LOCAL=NO)
18714 18714 oracle_18714_cd oraclecdb2 (LOCAL=NO)
18718 18718 oracle_18718_cd oraclecdb2 (LOCAL=NO)
18722 18722 oracle_18722_cd oraclecdb2 (LOCAL=NO)
18726 18726 oracle_18726_cd oraclecdb2 (LOCAL=NO)
18730 18730 oracle_18730_cd oraclecdb2 (LOCAL=NO)
18734 18734 oracle_18734_cd oraclecdb2 (LOCAL=NO)
18738 18738 oracle_18738_cd oraclecdb2 (LOCAL=NO)
18742 18742 oracle_18742_cd oraclecdb2 (LOCAL=NO)
18746 18746 oracle_18746_cd oraclecdb2 (LOCAL=NO)
18750 18750 oracle_18750_cd oraclecdb2 (LOCAL=NO)
18754 18754 oracle_18754_cd oraclecdb2 (LOCAL=NO)
18758 18758 oracle_18758_cd oraclecdb2 (LOCAL=NO)
18764 18764 oracle_18764_cd oraclecdb2 (LOCAL=NO)
18776 18776 oracle_18776_cd oraclecdb2 (LOCAL=NO)
18780 18780 oracle_18780_cd oraclecdb2 (LOCAL=NO)
18785 18785 oracle_18785_cd oraclecdb2 (LOCAL=NO)
18789 18789 oracle_18789_cd oraclecdb2 (LOCAL=NO)
18793 18793 oracle_18793_cd oraclecdb2 (LOCAL=NO)

  • Add DEDICATED_THROUGH_BROKER_LISTENER=ON to listener.ora:
DEDICATED_THROUGH_BROKER_LISTENER=ON
$lsnrctl stop

$lsnrctl start
  • Check and configure LOCAL_LISTENER if not yet
SQL> show parameter local_listener
NAME            TYPE    VALUE
--------------- ------- ------
local_listener  string  CDB2
  • Create multiple sessions  by running previous  create_sessons.sh sript again, and check the foreground processes using threads now. Please note the database has been bounced, so the process IDs are different from previous ones.
SQL> select spid, stid,pname from v$process order by 3,1,2;
SPID STID PNAME
------------------------ ------------------------ -----
24990 25028 AQPC
24990 25019 ARC0
24990 25020 ARC1
24990 25021 ARC2
24990 25022 ARC3
24990 25624 CJQ0
24984 24999 CKPT
24990 25009 D000
24984 24993 DBRM
24997 24997 DBW0
24990 24995 DIA0
24990 24992 DIAG
24984 24986 GEN0
24984 25000 LG00
24984 25002 LG01
24984 24998 LGWR
24984 25004 LREG
24984 24988 MMAN
24990 25007 MMNL
24990 25006 MMON
24990 25012 N000
24990 25030 P000
24990 25031 P001
24990 25032 P002
24990 25033 P003
24990 25034 P004
24990 25035 P005
24990 25036 P006
24990 25037 P007
24990 25609 P008
24990 25610 P009
24990 25611 P00A
24990 25612 P00B
24990 25631 P00C
24990 25632 P00D
24990 25633 P00E
24990 25634 P00F
24990 25635 P00G
24990 25636 P00H
24990 25637 P00I
24990 25638 P00J
24990 25648 P00K
24990 25650 P00L
24990 25651 P00M
24990 25652 P00N
24990 25654 P00O
24990 25655 P00P
24990 25656 P00Q
24990 25657 P00R
24990 25658 P00S
24990 25659 P00T
24990 25660 P00U
24990 25661 P00V
24976 24976 PMON
24978 24978 PSP0
24990 25005 PXMN
24990 25388 Q002
24990 25392 Q003
24990 25555 Q004
24990 25306 QM02
24990 25003 RECO
24984 25016 RVWR
24990 25011 S000
24984 24984 SCMN
24990 24990 SCMN
25771 25771 SCMN
24990 25024 SMCO
24984 25001 SMON
24990 25018 TMON
24990 25023 TT00
24990 24994 VKRM
24980 24980 VKTM
24990 25025 W000
24990 25026 W001
24990 25017
24990 25645
24990 25649
24990 25664
24990 25667
24990 25679
24990 25683
24990 25686
24990 25689
24990 25692
24990 25695
24990 25698
24990 25702
24990 25705
24990 25708
24990 25711
24990 25714
24990 25717
24990 25720
24990 25723
24990 25726
24990 25729
24990 25732
24990 25735
24990 25738
24990 25741
24990 25744
24990 25747
24990 25758
24990 25761
24990 25764
24990 25767
25771 25773
25771 25776
25771 25779
25771 25782
25771 25785
25771 25788
25771 25791
25771 25794
25771 25797
116 rows selected.
$ ps -eLo "pid tid comm args"|grep cdb2
24976 24976 ora_pmon_cdb2 ora_pmon_cdb2
24978 24978 ora_psp0_cdb2 ora_psp0_cdb2
24980 24980 ora_vktm_cdb2 ora_vktm_cdb2
24997 24997 ora_dbw0_cdb2 ora_dbw0_cdb2
24984 24984 ora_scmn_cdb2 ora_u004_cdb2
24984 24985 oracle ora_u004_cdb2
24984 24986 ora_gen0_cdb2 ora_u004_cdb2
24984 24988 ora_mman_cdb2 ora_u004_cdb2
24984 24993 ora_dbrm_cdb2 ora_u004_cdb2
24984 24998 ora_lgwr_cdb2 ora_u004_cdb2
24984 24999 ora_ckpt_cdb2 ora_u004_cdb2
24984 25000 ora_lg00_cdb2 ora_u004_cdb2
24984 25001 ora_smon_cdb2 ora_u004_cdb2
24984 25002 ora_lg01_cdb2 ora_u004_cdb2
24984 25004 ora_lreg_cdb2 ora_u004_cdb2
24984 25016 ora_rvwr_cdb2 ora_u004_cdb2
24990 24990 ora_scmn_cdb2 ora_u005_cdb2
24990 24991 oracle ora_u005_cdb2
24990 24992 ora_diag_cdb2 ora_u005_cdb2
24990 24994 ora_vkrm_cdb2 ora_u005_cdb2
24990 24995 ora_dia0_cdb2 ora_u005_cdb2
24990 25003 ora_reco_cdb2 ora_u005_cdb2
24990 25005 ora_pxmn_cdb2 ora_u005_cdb2
24990 25006 ora_mmon_cdb2 ora_u005_cdb2
24990 25007 ora_mmnl_cdb2 ora_u005_cdb2
24990 25009 ora_d000_cdb2 ora_u005_cdb2
24990 25011 ora_s000_cdb2 ora_u005_cdb2
24990 25012 ora_n000_cdb2 ora_u005_cdb2
24990 25017 oracle_25017_cd ora_u005_cdb2
24990 25018 ora_tmon_cdb2 ora_u005_cdb2
24990 25019 ora_arc0_cdb2 ora_u005_cdb2
24990 25020 ora_arc1_cdb2 ora_u005_cdb2
24990 25021 ora_arc2_cdb2 ora_u005_cdb2
24990 25022 ora_arc3_cdb2 ora_u005_cdb2
24990 25023 ora_tt00_cdb2 ora_u005_cdb2
24990 25024 ora_smco_cdb2 ora_u005_cdb2
24990 25025 ora_w000_cdb2 ora_u005_cdb2
24990 25026 ora_w001_cdb2 ora_u005_cdb2
24990 25028 ora_aqpc_cdb2 ora_u005_cdb2
24990 25030 ora_p000_cdb2 ora_u005_cdb2
24990 25031 ora_p001_cdb2 ora_u005_cdb2
24990 25032 ora_p002_cdb2 ora_u005_cdb2
24990 25033 ora_p003_cdb2 ora_u005_cdb2
24990 25034 ora_p004_cdb2 ora_u005_cdb2
24990 25035 ora_p005_cdb2 ora_u005_cdb2
24990 25036 ora_p006_cdb2 ora_u005_cdb2
24990 25037 ora_p007_cdb2 ora_u005_cdb2
24990 25306 ora_qm02_cdb2 ora_u005_cdb2
24990 25388 ora_q002_cdb2 ora_u005_cdb2
24990 25392 ora_q003_cdb2 ora_u005_cdb2
24990 25555 ora_q004_cdb2 ora_u005_cdb2
24990 25609 ora_p008_cdb2 ora_u005_cdb2
24990 25610 ora_p009_cdb2 ora_u005_cdb2
24990 25611 ora_p00a_cdb2 ora_u005_cdb2
24990 25612 ora_p00b_cdb2 ora_u005_cdb2
24990 25624 ora_cjq0_cdb2 ora_u005_cdb2
24990 25631 ora_p00c_cdb2 ora_u005_cdb2
24990 25632 ora_p00d_cdb2 ora_u005_cdb2
24990 25633 ora_p00e_cdb2 ora_u005_cdb2
24990 25634 ora_p00f_cdb2 ora_u005_cdb2
24990 25635 ora_p00g_cdb2 ora_u005_cdb2
24990 25636 ora_p00h_cdb2 ora_u005_cdb2
24990 25637 ora_p00i_cdb2 ora_u005_cdb2
24990 25638 ora_p00j_cdb2 ora_u005_cdb2
24990 25645 oracle_25645_cd ora_u005_cdb2
24990 25648 ora_p00k_cdb2 ora_u005_cdb2
24990 25649 oracle_25649_cd ora_u005_cdb2
24990 25650 ora_p00l_cdb2 ora_u005_cdb2
24990 25651 ora_p00m_cdb2 ora_u005_cdb2
24990 25652 ora_p00n_cdb2 ora_u005_cdb2
24990 25654 ora_p00o_cdb2 ora_u005_cdb2
24990 25655 ora_p00p_cdb2 ora_u005_cdb2
24990 25656 ora_p00q_cdb2 ora_u005_cdb2
24990 25657 ora_p00r_cdb2 ora_u005_cdb2
24990 25658 ora_p00s_cdb2 ora_u005_cdb2
24990 25659 ora_p00t_cdb2 ora_u005_cdb2
24990 25660 ora_p00u_cdb2 ora_u005_cdb2
24990 25661 ora_p00v_cdb2 ora_u005_cdb2
24990 25664 oracle_25664_cd ora_u005_cdb2
24990 25667 oracle_25667_cd ora_u005_cdb2
24990 25679 oracle_25679_cd ora_u005_cdb2
24990 25683 oracle_25683_cd ora_u005_cdb2
24990 25686 oracle_25686_cd ora_u005_cdb2
24990 25689 oracle_25689_cd ora_u005_cdb2
24990 25692 oracle_25692_cd ora_u005_cdb2
24990 25695 oracle_25695_cd ora_u005_cdb2
24990 25698 oracle_25698_cd ora_u005_cdb2
24990 25702 oracle_25702_cd ora_u005_cdb2
24990 25705 oracle_25705_cd ora_u005_cdb2
24990 25708 oracle_25708_cd ora_u005_cdb2
24990 25711 oracle_25711_cd ora_u005_cdb2
24990 25714 oracle_25714_cd ora_u005_cdb2
24990 25717 oracle_25717_cd ora_u005_cdb2
24990 25720 oracle_25720_cd ora_u005_cdb2
24990 25723 oracle_25723_cd ora_u005_cdb2
24990 25726 oracle_25726_cd ora_u005_cdb2
24990 25729 oracle_25729_cd ora_u005_cdb2
24990 25732 oracle_25732_cd ora_u005_cdb2
24990 25735 oracle_25735_cd ora_u005_cdb2
24990 25738 oracle_25738_cd ora_u005_cdb2
24990 25741 oracle_25741_cd ora_u005_cdb2
24990 25744 oracle_25744_cd ora_u005_cdb2
24990 25747 oracle_25747_cd ora_u005_cdb2
24990 25758 oracle_25758_cd ora_u005_cdb2
24990 25761 oracle_25761_cd ora_u005_cdb2
24990 25764 oracle_25764_cd ora_u005_cdb2
24990 25767 oracle_25767_cd ora_u005_cdb2
25771 25771 ora_scmn_cdb2 ora_u000_cdb2
25771 25772 oracle ora_u000_cdb2
25771 25773 oracle_25773_cd ora_u000_cdb2
25771 25776 oracle_25776_cd ora_u000_cdb2
25771 25779 oracle_25779_cd ora_u000_cdb2
25771 25782 oracle_25782_cd ora_u000_cdb2
25771 25785 oracle_25785_cd ora_u000_cdb2
25771 25788 oracle_25788_cd ora_u000_cdb2
25771 25791 oracle_25791_cd ora_u000_cdb2
25771 25794 oracle_25794_cd ora_u000_cdb2
25771 25797 oracle_25797_cd ora_u000_cdb2
$ ps -eaf | grep cdb2
oracle 24976 1 0 16:24 ? 00:00:00 ora_pmon_cdb2
oracle 24978 1 0 16:24 ? 00:00:00 ora_psp0_cdb2
oracle 24980 1 10 16:24 ? 00:00:30 ora_vktm_cdb2
oracle 24984 1 1 16:24 ? 00:00:03 ora_u004_cdb2
oracle 24990 1 37 16:24 ? 00:01:45 ora_u005_cdb2
oracle 24997 1 0 16:24 ? 00:00:00 ora_dbw0_cdb2
oracle 25771 1 2 16:26 ? 00:00:03 ora_u000_cdb2

There is only one extra OS process for 40 client sessions

  • Turn off MPMT architecture.
SQL> alter system set threaded_execution=true scope=spfile;
System altered.
SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down.

Restore JAVA Virtual Machine and Oracle Multimedia

SYMPTOM

When installing 12.1.0.2 Oracle options from DBCA, there are memory issues during install JVM and Oracle multimedia, so the installation failed by leaving those two options with status ‘LOADING’, those two unhealthy options could not be removed cleanly by DBCA, so manual removing and reinstalling are required for fixing it.

SQL> select COMP_NAME,VERSION,STATUS,SCHEMA 
from dba_registry
order by 3;

COMP_ID COMP_NAME VERSION STATUS SCHEMA ------- ------------------------ ------------ -------- ------ JServer JAVA Virtual Machine LOADING SYS Oracle Multimedia LOADING ORDSYS Oracle Database Catalog Views 12.1.0.2.0 VALID SYS Oracle Database Packages and Types 12.1.0.2.0 VALID SYS Oracle Workspace Manager 12.1.0.2.0 VALID WMSYS Oracle Text 12.1.0.2.0 VALID CTXSYS Oracle XML Database 12.1.0.2.0 VALID XDB
7 rows selected.

Please note your issue and situation might be different. Please raise a SR to Oracle Support to get advice for critical environment, otherwise it may cause issue worse and results in database unrecoverable which will need full database restore and recover, because manually removing and installing JVM and Multimedia is a  highly destructive action according to Oracle Support.

Remove and Reinstall Oracle JVM

  • Cold backup of whole database.
  • Removing Oracle unhealthy JVM :
Shutdown the database instance
Create the following REMOVAL script, full_rmjvm.sql
Run it from a new SQL*Plus session

spool full_rmjvm.log
set echo on
connect / as sysdba
startup mount
alter system set "_system_trig_enabled" = false scope=memory;
alter system enable restricted session;
alter database open;
@?/rdbms/admin/catnojav.sql
@?/xdk/admin/rmxml.sql
@?/javavm/install/rmjvm.sql
truncate table java$jvm$status;
select * from obj$ where obj#=0 and type#=0;
delete from obj$ where obj#=0 and type#=0;
commit;
select owner, count(*) from all_objects
where object_type like '%JAVA%' group by owner;
select obj#, name from obj$
where type#=28 or type#=29 or type#=30 or namespace=32;
select o1.name from obj$ o1,obj$ o2
where o1.type#=5 and o1.owner#=1 and o1.name=o2.name and o2.type#=29;
shutdown immediate
set echo off
spool off
exit
-- End of File full_rmjvm.sql
  • Check the log full_rmjvm.log, we can see JVM was not installed at all, in this case, we just reload it in next step.
SQL> Rem =====================================================================
SQL> Rem Check CATJAVA and JAVAVM status; conditionally abort the script
SQL> Rem =====================================================================
SQL> 
SQL> WHENEVER SQLERROR EXIT;
SQL> 
SQL> BEGIN
 2 IF dbms_registry.status('CATJAVA') IS NULL THEN
 3 RAISE_APPLICATION_ERROR(-20000,
 4 'CATJAVA has not been loaded into the database.');
 5 END IF;
 6 IF dbms_registry.is_loaded('JAVAVM') != 1 THEN
 7 RAISE_APPLICATION_ERROR(-20000,
 8 'JServer is not operational in the database; ' ||
 9 'JServer is required to remove CATJAVA from the database.');
 10 END IF;
 11 END;
 12 /
BEGIN
*
ERROR at line 1:
ORA-20000: CATJAVA has not been loaded into the database. 
ORA-06512: at line 3
  • Install the JVM
    Be sure the REMOVAL script, full_rmjvm.sql, completed successfully
    Create the following INSTALL script, full_jvminst.sql
    Run it from a new SQL*Plus session
    
    -- Start of File full_jvminst.sql
    spool full_jvminst.log;
    set echo on
    connect / as sysdba
    startup mount
    alter system set "_system_trig_enabled" = false scope=memory;
    alter database open;
    select obj#, name from obj$
    where type#=28 or type#=29 or type#=30 or namespace=32;
    @?/javavm/install/initjvm.sql
    select count(*), object_type from all_objects
    where object_type like '%JAVA%' group by object_type;
    @?/xdk/admin/initxml.sql
    select count(*), object_type from all_objects
    where object_type like '%JAVA%' group by object_type;
    @?/xdk/admin/xmlja.sql
    select count(*), object_type from all_objects
    where object_type like '%JAVA%' group by object_type;
    @?/rdbms/admin/catjava.sql
    select count(*), object_type from all_objects
    where object_type like '%JAVA%' group by object_type;
    shutdown immediate
    set echo off
    spool off
    exit
    -- End of File full_jvminst.sql
  • Check log and found no errors.
$ls -ltr full*jvm*
-rw-r--r--. 1 oracle oinstall 784 Nov 15 21:51 full_rmjvm.sql
-rw-r--r--. 1 oracle oinstall 2948 Nov 15 21:52 full_rmjvm.log
-rw-r--r--. 1 oracle oinstall 855 Nov 15 21:58 full_jvminstall.sql
-rw-r--r--. 1 oracle oinstall 9885882 Nov15 22:06 full_jvminst.log

$grep ORA- full_jvminst.log
129 dbms_output.put_line('retrying because of ORA-01545');
SQL> ## ORA-00028 it means that the database has not been restarted
SQL> ## ORA-29539: Java system classes already installed
SQL> ## ORA-29554: unhandled Java out of memory condition
 17 -- If the class does not exist an ORA-29532 (Uncaught Java
 27 -- If the class does not exist, an ORA-29532 (Uncaught Java
 43 -- If the class does not exist an ORA-29532 (Uncaught Java
 59 -- If the class does not exist an ORA-29532 (Uncaught Java
543 -- If the java schema object does not exist an ORA-29532 (Uncaught Java
 32 -- exist, an ORA-29532 (Uncaught Java exception) will occur.
 42 -- exist, an ORA-29532 (Uncaught Java exception) will occur.
 58 -- exist an ORA-29532 (Uncaught Java exception) will occur.
 74 -- exist an ORA-29532 (Uncaught Java exception) will occur.
SQL> -- in the current session it will cause an ORA-29549 (session state cleared)
SQL> ## ORA-01476: divisor is equal to zero
SQL>
  • Validate the Installation
SQL> column owner format A30
-- Validation Query 1
select count(*), object_type
from all_objects
where object_type like '%JAVA%'
and owner = 'SYS'
group by object_type;

COUNT(*) OBJECT_TYPE ---------- --------------- 297 JAVA DATA 911 JAVA RESOURCE 27473 JAVA CLASS 2 JAVA SOURCE
SQL> select owner, count(*)
from all_objects 
where object_type like '%JAVA%'
and owner = 'SYS'group by owner;

OWNER   COUNT(*)
------  ---------
SYS     28683

SQL> select owner, object_type, count(*)
from all_objects 
where object_type like '%JAVA%'
and status <> 'VALID'
and owner = 'SYS'
group by owner, object_type;

no rows selected
SQL>select comp_id,COMP_NAME,VERSION,STATUS,SCHEMA 
from dba_registry
order by 3;
COMP_ID COMP_NAME VERSION STATUS SCHEMA
------- ---------------------------------- ---------- ------ ------ CATALOG Oracle Database Catalog Views 12.1.0.2.0 VALID SYS CONTEXT Oracle Text 12.1.0.2.0 VALID CTXSYS OWM Oracle Workspace Manager 12.1.0.2.0 VALID WMSYS XDB Oracle XML Database 12.1.0.2.0 VALID XDB CATJAVA Oracle Database Java Packages 12.1.0.2.0 VALID SYS CATPROC Oracle Database Packages and Types 12.1.0.2.0 VALID SYS JAVAVM JServer JAVA Virtual Machine 12.1.0.2.0 VALID SYS XML Oracle XDK 12.1.0.2.0 VALID SYS ORDIM Oracle Multimedia LOADING ORDSYS 9 rows selected.
  • ADDITIONAL REMINDER: If you previously had any of the following database options installed:
    • Oracle Multimedia (ORDSYS, ORDIM)
    • Oracle Spatial (MDSYS)
    • Oracle Warehouse Builder (OWBSYS)
    • OLAP

    These options will need to be re-installed to reload their java dependencies.  This can be achieved by contacting the respective support teams for each product by logging a new Service Request.

Reinstall Oracle Multimedia

Install Oracle Mutlmedia according  to “Multimedia User’s Guide” section B Managing Oracle Multimedia Installations

  1. Check the prerequisites are existed including PL/SQL, Oracle JVM, Oracle XML Database, and Oracle XDK.
    SQL> select version,status 
    from dba_registry
    where comp_id='JAVAVM'; SQL> select version, status from dba_registry where comp_id='XDB'; SQL> select version, status from dba_registry where comp_id='XML';
  2. Go to /rdbms/admin directory and run catcon.pl  script to create the users and grant the appropriate privileges. The first parameter is the tablespace for Oracle Multimedia; the second parameter is the tablespace for Oracle Spatial and Graph.

    $ perl catcon.pl -u SYS -d $ORACLE_HOME/ord/admin -b ordinst ordinst.sql '--pSYSAUX' '--pSYSAUX' 
    catcon: ALL catcon-related output will be written to ordinst_catcon_14555.lst
    catcon: See ordinst*.log files for output generated by scripts
    catcon: See ordinst_*.lst files for spool files, if any
    Enter Password: 
    catcon.pl: completed successfully
  3. Run the script to install the Oracle Multimedia types and packages.
    $ perl catcon.pl -u SYS -d $ORACLE_HOME/ord/im/admin -b catim catim.sql
    catcon: ALL catcon-related output will be written to catim_catcon_14649.lst
    catcon: See catim*.log files for output generated by scripts
    catcon: See catim_*.lst files for spool files, if any
    Enter Password: 
    catcon.pl: completed successfully
  4. Verify the installation of Oracle Multimedia.
SQL> CONNECT sys as sysdba
SQL> execute sys.validate_ordim;
PL/SQL procedure successfully completed.

If the validation procedure detects invalid objects, it lists the first few invalid objects and sets the registry entry to INVALID; otherwise, it silently sets the Oracle Multimedia registry entry  VALID.

SQL> select version, status 
from dba_registry
where comp_id='ORDIM';
VERSION STATUS ------------ ------- 12.1.0.2.0 VALID
  • Check options validations.
SQL>select comp_id,COMP_NAME,VERSION,STATUS,SCHEMA 
from dba_registry
order by 4;
COMP_ID COMP_NAME VERSION STATUS SCHEMA --------- --------------------- ------------ ---------- ------ ------ ORDIM Oracle Multimedia 12.1.0.2.0 VALID ORDSYS CONTEXT Oracle Text 12.1.0.2.0 VALID CTXSYS OWM Oracle Workspace Manager 12.1.0.2.0 VALID WMSYS XDB Oracle XML Database 12.1.0.2.0 VALID XDB CATJAVA Oracle Database Java Packages 12.1.0.2.0 VALID SYS CATPROC Oracle Database Packages and Types 12.1.0.2.0 VALID SYS JAVAVM JServer JAVA Virtual Machine 12.1.0.2.0 VALID SYS XML Oracle XDK 12.1.0.2.0 VALID SYS CATALOG Oracle Database Catalog Views 12.1.0.2.0 VALID SYS 9 rows selected. SQL> select count(*) from dba_objects where status!='VALID'; COUNT(*) ---------- 0