Wednesday, December 4, 2013

Database 12c: Initialization parameter THREADED_EXECUTION

THREADED_EXECUTION

Property Description
Parameter type Boolean
Default value false
Modifiable No
Range of values true | false
Basic No
Oracle RAC If specified, all instances must use the same value

THREADED_EXECUTION specifies whether to enable the multithreaded Oracle model. Starting in Oracle Database 12c, the multithreaded Oracle model enables Oracle processes on UNIX and Linux to run as operating system threads in separate address spaces.

By default, some background processes on UNIX and Linux always use threaded execution; the remaining Oracle processes run as operating system processes. Thus, an "Oracle process" is not always equivalent to an "operating system process."

Note:

When this initialization parameter is set to TRUE, which enables the multithreaded Oracle model, operating system authentication is not supported. Attempts to connect to the database using operating system authentication (for example,CONNECT / AS SYSDBA or CONNECT / ) when this initialization parameter is set to TRUE receive an ORA-01031 "insufficient privileges" error.

The solution to this error is to always use the password when connecting to the database.

Also, when this initialization parameter is set to TRUE, the DEDICATED_THROUGH_BROKER_listener-name=ON parameter should be added to the listener.ora file, where listener-name is the name of the database listener. This enables the server to spawn threads when connections to the database are requested through the listener.

 

Scenario1: Threaded_execution=false

[oracle@gc12c ~]$ ps -T -fu oracle

UID        PID  SPID  PPID  C STIME TTY          TIME CMD
oracle    2303  2303     1  0 19:16 ?        00:00:00 ora_pmon_cdborcl
oracle    2305  2305     1  0 19:16 ?        00:00:00 ora_psp0_cdborcl
oracle    2307  2307     1  1 19:16 ?        00:00:03 ora_vktm_cdborcl
oracle    2311  2311     1  0 19:16 ?        00:00:00 ora_gen0_cdborcl
oracle    2313  2313     1  0 19:16 ?        00:00:00 ora_mman_cdborcl
oracle    2317  2317     1  0 19:16 ?        00:00:00 ora_diag_cdborcl
oracle    2319  2319     1  0 19:16 ?        00:00:00 ora_dbrm_cdborcl
oracle    2321  2321     1  0 19:16 ?        00:00:00 ora_dia0_cdborcl
oracle    2323  2323     1  0 19:16 ?        00:00:00 ora_dbw0_cdborcl
oracle    2325  2325     1  0 19:16 ?        00:00:00 ora_lgwr_cdborcl
oracle    2327  2327     1  0 19:16 ?        00:00:00 ora_ckpt_cdborcl
oracle    2329  2329     1  0 19:16 ?        00:00:00 ora_smon_cdborcl
oracle    2331  2331     1  0 19:16 ?        00:00:00 ora_reco_cdborcl
oracle    2333  2333     1  0 19:16 ?        00:00:00 ora_lreg_cdborcl
oracle    2335  2335     1  0 19:16 ?        00:00:00 ora_mmon_cdborcl
oracle    2337  2337     1  0 19:16 ?        00:00:00 ora_mmnl_cdborcl
oracle    2339  2339     1  0 19:16 ?        00:00:00 ora_d000_cdborcl
oracle    2341  2341     1  0 19:16 ?        00:00:00 ora_s000_cdborcl
oracle    2343  2343     1  0 19:16 ?        00:00:00 ora_s001_cdborcl
oracle    2355  2355     1  0 19:16 ?        00:00:00 ora_tmon_cdborcl
oracle    2357  2357     1  0 19:16 ?        00:00:00 ora_arc0_cdborcl
oracle    2359  2359     1  0 19:16 ?        00:00:00 ora_arc1_cdborcl
oracle    2361  2361     1  0 19:16 ?        00:00:00 ora_arc2_cdborcl
oracle    2363  2363     1  0 19:16 ?        00:00:00 ora_arc3_cdborcl
oracle    2365  2365     1  0 19:16 ?        00:00:00 ora_tt00_cdborcl
oracle    2367  2367     1  0 19:16 ?        00:00:00 ora_smco_cdborcl
oracle    2369  2369     1  0 19:16 ?        00:00:00 ora_aqpc_cdborcl
oracle    2371  2371     1  0 19:16 ?        00:00:00 ora_w000_cdborcl
oracle    2375  2375     1  0 19:16 ?        00:00:02 ora_p000_cdborcl
oracle    2377  2377     1  1 19:16 ?        00:00:02 ora_p001_cdborcl
oracle    2379  2379     1  0 19:16 ?        00:00:00 ora_p002_cdborcl
oracle    2381  2381     1  0 19:16 ?        00:00:00 ora_p003_cdborcl
oracle    2409  2409     1  0 19:16 ?        00:00:00 ora_qm02_cdborcl
oracle    2413  2413     1  0 19:16 ?        00:00:00 ora_q002_cdborcl
oracle    2415  2415     1  0 19:16 ?        00:00:00 ora_q003_cdborcl
oracle    2419  2419     1  0 19:16 ?        00:00:00 ora_cjq0_cdborcl
oracle    2425  2425     1  0 19:16 ?        00:00:00 ora_p004_cdborcl
oracle    2427  2427     1  0 19:16 ?        00:00:00 ora_p005_cdborcl
oracle    2433  2433     1  0 19:16 ?        00:00:00 ora_p006_cdborcl
oracle    2437  2437     1  0 19:16 ?        00:00:00 ora_p007_cdborcl
oracle    2441  2441     1  0 19:16 ?        00:00:00 ora_p008_cdborcl
oracle    2445  2445     1  0 19:16 ?        00:00:00 ora_p009_cdborcl
oracle    2503  2503     1  0 19:20 ?        00:00:00 ora_j000_cdborcl
oracle    2505  2505     1  0 19:20 ?        00:00:00 ora_j001_cdborcl
-- Total 44 processes

Scenario2: Threaded_execution=true

SQL> alter system set threaded_execution=true scope=spfile;

System altered.

SQL> shutdown abort
ORACLE instance shut down.
SQL> conn sys/password as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.

[oracle@gc12c ~]$ ps -fu oracle
UID        PID  PPID  C STIME TTY          TIME CMD
oracle    2797     1  0 19:59 ?        00:00:00 ora_pmon_cdborcl
oracle    2799     1  0 19:59 ?        00:00:00 ora_psp0_cdborcl
oracle    2801     1  1 19:59 ?        00:00:01 ora_vktm_cdborcl
oracle    2805     1  0 19:59 ?        00:00:00 ora_u004_cdborcl
oracle    2811     1  5 19:59 ?        00:00:09 ora_u005_cdborcl
oracle    2817     1  0 19:59 ?        00:00:00 ora_dbw0_cdborcl

[oracle@gc12c ~]$ ps -T -fu oracle
UID        PID  SPID  PPID  C STIME TTY          TIME CMD
oracle    2797  2797     1  0 19:59 ?        00:00:00 ora_pmon_cdborcl
oracle    2799  2799     1  0 19:59 ?        00:00:00 ora_psp0_cdborcl
oracle    2801  2801     1  1 19:59 ?        00:00:00 ora_vktm_cdborcl
oracle    2805  2805     1  0 19:59 ?        00:00:00 ora_u004_cdborc
oracle    2805  2806     1  0 19:59 ?        00:00:00 ora_u004_cdborc
oracle    2805  2807     1  0 19:59 ?        00:00:00 ora_u004_cdborc
oracle    2805  2808     1  0 19:59 ?        00:00:00 ora_u004_cdborc
oracle    2805  2814     1  0 19:59 ?        00:00:00 ora_u004_cdborc
oracle    2805  2818     1  0 19:59 ?        00:00:00 ora_u004_cdborc
oracle    2805  2819     1  0 19:59 ?        00:00:00 ora_u004_cdborc
oracle    2805  2820     1  0 19:59 ?        00:00:00 ora_u004_cdborc
oracle    2805  2822     1  0 19:59 ?        00:00:00 ora_u004_cdborc
oracle    2811  2811     1  0 19:59 ?        00:00:00 ora_u005_cdborc
oracle    2811  2812     1  0 19:59 ?        00:00:00 ora_u005_cdborc
oracle    2811  2813     1  0 19:59 ?        00:00:00 ora_u005_cdborc
oracle    2811  2815     1  0 19:59 ?        00:00:00 ora_u005_cdborc
oracle    2811  2821     1  0 19:59 ?        00:00:00 ora_u005_cdborc
oracle    2811  2823     1  0 19:59 ?        00:00:00 ora_u005_cdborc
oracle    2811  2824     1  0 19:59 ?        00:00:00 ora_u005_cdborc
oracle    2811  2825     1  0 19:59 ?        00:00:00 ora_u005_cdborc
oracle    2811  2826     1  0 19:59 ?        00:00:00 ora_u005_cdborc
oracle    2811  2827     1  0 19:59 ?        00:00:00 ora_u005_cdborc
oracle    2811  2828     1  0 19:59 ?        00:00:00 ora_u005_cdborc
oracle    2811  2835     1  0 19:59 ?        00:00:00 ora_u005_cdborc
oracle    2811  2836     1  0 19:59 ?        00:00:00 ora_u005_cdborc
oracle    2811  2837     1  0 19:59 ?        00:00:00 ora_u005_cdborc
oracle    2811  2838     1  0 19:59 ?        00:00:00 ora_u005_cdborc
oracle    2811  2839     1  0 19:59 ?        00:00:00 ora_u005_cdborc
oracle    2811  2840     1  0 19:59 ?        00:00:00 ora_u005_cdborc
oracle    2811  2841     1  0 19:59 ?        00:00:00 ora_u005_cdborc
oracle    2811  2842     1  0 19:59 ?        00:00:00 ora_u005_cdborc
oracle    2811  2844     1  5 19:59 ?        00:00:01 ora_u005_cdborc
oracle    2811  2845     1  6 19:59 ?        00:00:02 ora_u005_cdborc
oracle    2811  2846     1  0 19:59 ?        00:00:00 ora_u005_cdborc
oracle    2811  2847     1  0 19:59 ?        00:00:00 ora_u005_cdborc
oracle    2811  2861     1  0 19:59 ?        00:00:00 ora_u005_cdborc
oracle    2811  2862     1  0 19:59 ?        00:00:00 ora_u005_cdborc
oracle    2811  2867     1  0 19:59 ?        00:00:00 ora_u005_cdborc
oracle    2811  2868     1  0 19:59 ?        00:00:00 ora_u005_cdborc
oracle    2811  2869     1  0 19:59 ?        00:00:00 ora_u005_cdborc
oracle    2811  2871     1  0 19:59 ?        00:00:00 ora_u005_cdborc
oracle    2811  2872     1  0 19:59 ?        00:00:00 ora_u005_cdborc
oracle    2817  2817     1  0 19:59 ?        00:00:00 ora_dbw0_cdborc
oracle    2875  2875  2137  0 19:59 pts/0    00:00:00 ps -T -fu oracl

SQL> conn system/password@cdborcl
Connected.
SQL> select s.program,s.process,p.spid,p.stid,p.sosid from v$session s, v$process p where s.paddr=p.addr;

PROGRAM                                      PROCESS    SPID       STID       SOSID
-------------------------------------------- ---------- ---------- ---------- ----------
oracle@gc12c.dbaglobe.com (PMON)             2797       2797       2797       2797
oracle@gc12c.dbaglobe.com (PSP0)             2799       2799       2799       2799
oracle@gc12c.dbaglobe.com (VKTM)             2801       2801       2801       2801
oracle@gc12c.dbaglobe.com (GEN0)             2805_2807  2805       2807       2805_2807
oracle@gc12c.dbaglobe.com (SCMN)             2805_2805  2805       2805       2805_2805
oracle@gc12c.dbaglobe.com (MMAN)             2805_2808  2805       2808       2805_2808
oracle@gc12c.dbaglobe.com (TMON)             2811_2835  2811       2835       2811_2835
oracle@gc12c.dbaglobe.com (DIAG)             2811_2813  2811       2813       2811_2813
oracle@gc12c.dbaglobe.com (SCMN)             2811_2811  2811       2811       2811_2811
oracle@gc12c.dbaglobe.com (DBRM)             2805_2814  2805       2814       2805_2814
oracle@gc12c.dbaglobe.com (DIA0)             2811_2815  2811       2815       2811_2815
oracle@gc12c.dbaglobe.com (DBW0)             2817       2817       2817       2817
oracle@gc12c.dbaglobe.com (LGWR)             2805_2818  2805       2818       2805_2818
oracle@gc12c.dbaglobe.com (CKPT)             2805_2819  2805       2819       2805_2819
oracle@gc12c.dbaglobe.com (SMON)             2805_2820  2805       2820       2805_2820
oracle@gc12c.dbaglobe.com (RECO)             2811_2821  2811       2821       2811_2821
oracle@gc12c.dbaglobe.com (LREG)             2805_2822  2805       2822       2805_2822
oracle@gc12c.dbaglobe.com (MMON)             2811_2823  2811       2823       2811_2823
oracle@gc12c.dbaglobe.com (MMNL)             2811_2824  2811       2824       2811_2824
sqlplus@gc12c.dbaglobe.com (TNS V1-V3)       2911       2980       2980       2980
oracle@gc12c.dbaglobe.com (ARC0)             2811_2836  2811       2836       2811_2836
oracle@gc12c.dbaglobe.com (ARC1)             2811_2837  2811       2837       2811_2837
oracle@gc12c.dbaglobe.com (ARC2)             2811_2838  2811       2838       2811_2838
oracle@gc12c.dbaglobe.com (ARC3)             2811_2839  2811       2839       2811_2839
oracle@gc12c.dbaglobe.com (TT00)             2811_2840  2811       2840       2811_2840
oracle@gc12c.dbaglobe.com (SMCO)             2811_2841  2811       2841       2811_2841
oracle@gc12c.dbaglobe.com (AQPC)             2811_2842  2811       2842       2811_2842
oracle@gc12c.dbaglobe.com (CJQ0)             2811_2861  2811       2861       2811_2861
oracle@gc12c.dbaglobe.com (W000)             2811_2862  2811       2862       2811_2862
oracle@gc12c.dbaglobe.com (QM02)             2811_2869  2811       2869       2811_2869
oracle@gc12c.dbaglobe.com (W001)             2811_2939  2811       2939       2811_2939
oracle@gc12c.dbaglobe.com (Q002)             2811_2871  2811       2871       2811_2871
oracle@gc12c.dbaglobe.com (Q003)             2811_2872  2811       2872       2811_2872
oracle@gc12c.dbaglobe.com (W002)             2811_2970  2811       2970       2811_2970

34 rows selected.

Database Alert Log:
NOTE: remote asm mode is local (mode 0x1; from cluster type)
Starting background process PMON
Starting background process PSP0
PMON started with pid=2, OS id=2797
Starting background process VKTM
PSP0 started with pid=3, OS id=2799
2013-12-04 19:59:05.539000 +08:00
VKTM started with pid=4, OS id=2801 at elevated priority
VKTM running at (1)millisec precision with DBRM quantum (100)ms
Starting background process GEN0
Starting background process MMAN
GEN0 started with pid=5, OS id=2805_2807
MMAN started with pid=7, OS id=2805_2808
Starting background process DIAG
Starting background process DBRM
DIAG started with pid=9, OS id=2811_2813
Starting background process DIA0
DBRM started with pid=11, OS id=2805_2814
Starting background process DBW0
DIA0 started with pid=12, OS id=2811_2815
Starting background process LGWR
DBW0 started with pid=13, OS id=2817
Starting background process CKPT
LGWR started with pid=14, OS id=2805_2818
Starting background process SMON
CKPT started with pid=15, OS id=2805_2819
Starting background process RECO
SMON started with pid=16, OS id=2805_2820
Starting background process LREG
RECO started with pid=17, OS id=2811_2821
Starting background process MMON
LREG started with pid=18, OS id=2805_2822
Starting background process MMNL
MMON started with pid=19, OS id=2811_2823
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
MMNL started with pid=20, OS id=2811_2824
starting up 2 shared server(s) ...
2013-12-04 19:59:06.774000 +08:00
ORACLE_BASE from environment = /u01/app/oracle
ALTER DATABASE   MOUNT
2013-12-04 19:59:08.715000 +08:00
Using default pga_aggregate_limit of 2048 MB
2013-12-04 19:59:10.837000 +08:00
Successful mount of redo thread 1, with mount id 2818372491
Database mounted in Exclusive Mode
Lost write protection disabled
Ping without log force is disabled.
Completed: ALTER DATABASE   MOUNT
2013-12-04 19:59:19.026000 +08:00
alter database open
Starting background process TMON
LGWR: STARTING ARCH PROCESSES
Starting background process ARC0
TMON started with pid=8, OS id=2811_2835
ARC0 started with pid=26, OS id=2811_2836
2013-12-04 19:59:20.045000 +08:00
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
Starting background process ARC1
Starting background process ARC2
ARC1 started with pid=27, OS id=2811_2837
Starting background process ARC3
ARC2 started with pid=28, OS id=2811_2838
ARC1: Archival started
ARC2: Archival started
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
ARC2: Becoming the heartbeat ARCH
ARC3 started with pid=29, OS id=2811_2839
Thread 1 opened at log sequence 13
  Current log# 1 seq# 13 mem# 0: /u01/app/oracle/oradata/cdborcl/redo01.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
SMON: enabling cache recovery
[2811_2834] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:1913054 end:1913114 diff:60 ms (0.1 seconds)
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Starting background process SMCO
Database Characterset is WE8MSWIN1252
SMCO started with pid=31, OS id=2811_2841
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process AQPC
AQPC started with pid=32, OS id=2811_2842
2013-12-04 19:59:21.245000 +08:00
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
Opening pdb PDB$SEED (2) with no Resource Manager plan active
db_recovery_file_dest_size of 4800 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
2013-12-04 19:59:22.251000 +08:00
Starting background process CJQ0
CJQ0 started with pid=33, OS id=2811_2861
Completed: alter database open

 

Known Errors:

SQL> conn / as sysdba
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.