Oracle 12C : Multithreaded Architecture

Oracle 12c: Multithreaded architecture

Oracle introduced multithreaded architecture in Oracle 12C in Linux platform. When the listener receiving a request , it will not spawn an OS processes like it used to be but passes the request to the database and spin them out in threads..

Benefits

• Improve Parallel Performance

               Now that listener passes the tasks to database process to multiple threads instead of running each task on OS processes. It is a "Many-to-One" versus "One -to-One" relationship.

• Low CPU and Memory usage

Demonstration

SQL> show parameter threaded_execution;

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------------
threaded_execution                   boolean     FALSE

SQL> !ps -ef|grep orcl|grep -v grep

oracle    3669     1  0 Aug29 ?        00:00:13 ora_w005_orcl

oracle   18149     1  0 Oct31 ?        00:00:03 ora_w003_orcl

oracle   20216 20213  0 10:08 ?        00:00:00 oracleorcl (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

oracle   20748     1  0 10:45 ?        00:00:00 ora_p00w_orcl

oracle   20752     1  0 10:45 ?        00:00:00 ora_p00x_orcl

oracle   20756     1  0 10:45 ?        00:00:00 ora_p00y_orcl

oracle   20760     1  0 10:45 ?        00:00:00 ora_p00z_orcl

oracle   27517     1  0 Aug04 ?        00:03:11 ora_pmon_orcl

oracle   27521     1  0 Aug04 ?        00:03:51 ora_psp0_orcl

oracle   27525     1  0 Aug04 ?        00:02:24 ora_vktm_orcl

oracle   27531     1  0 Aug04 ?        00:00:14 ora_gen0_orcl

oracle   27535     1  0 Aug04 ?        00:00:04 ora_mman_orcl

oracle   27543     1  0 Aug04 ?        00:00:02 ora_diag_orcl

oracle   27547     1  0 Aug04 ?        00:00:08 ora_dbrm_orcl

oracle   27551     1  0 Aug04 ?        01:36:03 ora_dia0_orcl

oracle   27555     1  0 Aug04 ?        00:25:19 ora_dbw0_orcl

oracle   27559     1  0 Aug04 ?        00:01:31 ora_lgwr_orcl

oracle   27563     1  0 Aug04 ?        00:22:29 ora_ckpt_orcl

oracle   27567     1  0 Aug04 ?        00:04:32 ora_lg00_orcl

oracle   27571     1  0 Aug04 ?        00:01:33 ora_smon_orcl

oracle   27575     1  0 Aug04 ?        00:00:19 ora_lg01_orcl

oracle   27579     1  0 Aug04 ?        00:00:01 ora_reco_orcl

oracle   27583     1  0 Aug04 ?        00:00:03 ora_lreg_orcl

oracle   27587     1  0 Aug04 ?        02:39:03 ora_mmon_orcl

oracle   27591     1  0 Aug04 ?        00:58:07 ora_mmnl_orcl

oracle   27595     1  0 Aug04 ?        00:00:00 ora_d000_orcl

oracle   27599     1  0 Aug04 ?        00:00:01 ora_s000_orcl

oracle   27632     1  0 Aug04 ?        02:34:03 ora_p000_orcl

oracle   27636     1  0 Aug04 ?        03:03:53 ora_p001_orcl

oracle   27640     1  0 Aug04 ?        01:12:06 ora_p002_orcl

oracle   27644     1  0 Aug04 ?        00:59:10 ora_p003_orcl

oracle   27648     1  0 Aug04 ?        00:02:05 ora_p004_orcl

oracle   27652     1  0 Aug04 ?        00:02:03 ora_p005_orcl

oracle   27656     1  0 Aug04 ?        00:00:09 ora_p006_orcl

oracle   27666     1  0 Aug04 ?        00:00:00 ora_tmon_orcl

oracle   27670     1  0 Aug04 ?        00:00:01 ora_tt00_orcl

oracle   27674     1  0 Aug04 ?        00:00:04 ora_smco_orcl

oracle   27682     1  0 Aug04 ?        00:00:00 ora_aqpc_orcl

oracle   27694     1  0 Aug04 ?        00:00:09 ora_p007_orcl

oracle   27698     1  0 Aug04 ?        00:00:09 ora_p008_orcl

oracle   27702     1  0 Aug04 ?        00:00:03 ora_p009_orcl

oracle   27706     1  0 Aug04 ?        00:00:03 ora_p00a_orcl

oracle   27710     1  0 Aug04 ?        00:00:03 ora_p00b_orcl

oracle   27714     1  0 Aug04 ?        00:00:03 ora_p00c_orcl

oracle   27718     1  0 Aug04 ?        00:00:03 ora_p00d_orcl

oracle   27722     1  0 Aug04 ?        00:00:03 ora_p00e_orcl

oracle   27726     1  0 Aug04 ?        00:00:03 ora_p00f_orcl

oracle   27730     1  0 Aug04 ?        00:00:03 ora_p00g_orcl

oracle   27734     1  0 Aug04 ?        00:00:03 ora_p00h_orcl

oracle   27738     1  0 Aug04 ?        00:00:03 ora_p00i_orcl

oracle   27742     1  0 Aug04 ?        00:00:03 ora_p00j_orcl

oracle   27746     1  0 Aug04 ?        00:00:03 ora_p00k_orcl

oracle   27750     1  0 Aug04 ?        00:00:03 ora_p00l_orcl

oracle   27754     1  0 Aug04 ?        00:00:03 ora_p00m_orcl

oracle   27758     1  0 Aug04 ?        00:00:03 ora_p00n_orcl

oracle   27762     1  0 Aug04 ?        00:00:03 ora_p00o_orcl

oracle   27766     1  0 Aug04 ?        00:00:03 ora_p00p_orcl

oracle   27770     1  0 Aug04 ?        00:00:03 ora_p00q_orcl

oracle   27774     1  0 Aug04 ?        00:00:03 ora_p00r_orcl

oracle   27778     1  0 Aug04 ?        00:00:03 ora_p00s_orcl

oracle   27782     1  0 Aug04 ?        00:00:03 ora_p00t_orcl

oracle   27786     1  0 Aug04 ?        00:00:03 ora_p00u_orcl

oracle   27790     1  0 Aug04 ?        00:00:03 ora_p00v_orcl

oracle   27850     1  0 Aug04 ?        01:18:23 ora_cjq0_orcl

oracle   27858     1  0 Aug04 ?        00:00:00 ora_qm02_orcl

oracle   27866     1  0 Aug04 ?        00:00:08 ora_q002_orcl

oracle   27870     1  0 Aug04 ?        00:00:00 ora_q003_orcl

oracle   29355     1  0 Aug04 ?        00:00:17 ora_w000_orcl

oracle   29565     1  0 Aug04 ?        00:00:16 ora_w001_orcl

oracle   31855     1  0 Aug04 ?        00:00:16 ora_w002_orcl

SQL> !ps -ef|grep orcl|grep -v grep|wc -l

70

SQL> alter system set threaded_execution = true scope=spfile

SQL> shutdown immediate;

SQL> startup;

ORA-01017: invalid username/password; logon denied
SQL> select status from v$instance;
ERROR:
ORA-01012: not logged on

SQL> quit

Note: once set, dba will no longer be able to connect to the database with OS authentication to connect to the database. 

sqlplus sys/password@orcl

ERROR:
ORA-12528: TNS:listener: all appropriate instances are blocking new connections

Enter user-name: sys as sysdba
Enter password:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> startup
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area 5027385344 bytes
Fixed Size                  2691952 bytes
Variable Size            1090522256 bytes
Database Buffers         3925868544 bytes
Redo Buffers                8302592 bytes
Database mounted.
Database opened.

SQL> show parameter threaded_execution;

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------------
threaded_execution                   boolean     TRUE

SQL>  !ps -ef|grep -v grep|grep orcl
oracle   20960     1  0 10:56 ?        00:00:00 ora_pmon_orcl
oracle   20964     1  0 10:56 ?        00:00:00 ora_psp0_orcl
oracle   20970     1  0 10:56 ?        00:00:00 ora_vktm_orcl
oracle   20976     1  9 10:56 ?        00:00:03 ora_u004_orcl
oracle   20990     1 35 10:56 ?        00:00:13 ora_u005_orcl
oracle   21004     1  0 10:56 ?        00:00:00 ora_dbw0_orcl

SQL>  !ps -ef|grep -v grep|grep orcl|wc -l;
6

SQL>

The number of processes dramatically reduced from 70 to 6! For details of the processes, look at v$process view. Now, that sessions are lumped up into one database process, killing the wrong process can be take down other crucial threads (uhh.. processes) as well.

Experiment

After a few days enabling the Multithreaded feature my linux VM starting to reboot itself every other day. messages log did not provided any reasons at all. It simply gone down right after the generic syslogd restarted.

Allocated 8 CPU. 

Red Hat 5.6 Tikanga

Linux oracle12c 2.6.18-238.el5 #1 SMP Sun Dec 19 14:22:44 EST 2010 x86_64 x86_64 x86_64 GNU/Linux

[root@oracle12c ~]# free -m
             total       used       free     shared    buffers     cached
Mem:         16050        596      15454          0         80        350
-/+ buffers/cache:        166      15884
Swap:         2015          0       2015

So, I decided to increase the CPU and memory to 16 and 64 Gig respectively as a test.

[oracle@oracle12c ~]$ free -m
             total       used       free     shared    buffers     cached
Mem:         64458        349      64108          0         33        171
-/+ buffers/cache:        145      64313
Swap:         2015          0       2015

[oracle@oracle12c ~]$ cat /proc/cpuinfo |grep processor|wc -l
16

Oracle 12C: Creating pluggable database - error: ORA-01276: Cannot add file

For some reason, I cannot create pluggable database if there are entry setting for DB_CREATE_FILE_DEST. Have to overwrite the setting. 

Refer to OCP Upgrade to Oracle Database 12C Exam Guide - Page 66-67. Page 67 did talk about letting Oracle to create the OMF datafile if the entry is set.

Seems like running the Create Pluggable command without specifying FILE_NAME_CONVERT.

Requirement: directory to the target database must be created prior to create the database.

[oracle@oracle12c data]$ pwd
/u01/data
[oracle@oracle12c data]$ ls -las
total 36
8 drwxrwxr-x 5    501 oinstall 4096 Jul 11  2013 .
4 drwxrwxrwx 6 root   root     4096 Jul 11  2013 ..
8 drwxrwxrwx 3    501 oinstall 4096 Jul 11  2013 orcl
8 drwxr-x--- 6 oracle oracle   4096 Jul 11  2013 ORCL
8 drwxr-x--- 2 oracle oracle   4096 Jul 11  2013 pdbseed
[oracle@oracle12c data]$ ls -la

create pluggable database pdbvcdb admin user vcadmin identified by passw0rd roles=(CONNECT)  FILE_NAME_CONVERT = ('/u01/data/ORCL','/u01/data/pdbvcdb');

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
/u01/data/ORCL/datafile/o1_mf_users_8xy20vdz_.dbf
/u01/data/ORCL/datafile/o1_mf_undotbs1_8xy20ykp_.dbf
/u01/data/ORCL/datafile/o1_mf_sysaux_8xy1tly9_.dbf
/u01/data/ORCL/datafile/o1_mf_system_8xy1y65o_.dbf
/u01/data/ORCL/datafile/vpxadmin.dbf
/u01/data/ORCL/datafile/vcdman.dbf

SQL> create pluggable database pdbvcdb admin user vcadmin identified by passw0rd roles=(CONNECT)  FILE_NAME_CONVERT = ('/u01/data/ORCL','/u01/data/pdbvcdb');
create pluggable database pdbvcdb admin user vcadmin identified by passw0rd roles=(CONNECT)  FILE_NAME_CONVERT = ('/u01/data/ORCL','/u01/data/pdbvcdb')
*
ERROR at line 1:
ORA-01276: Cannot add file
/u01/data/pdbvcdb/datafile/o1_mf_system_8xy22ryn_.dbf.  File has an Oracle
Managed Files file name.

SQL> show parameter DB_CREATE_FILE_DEST

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      /u01/data

SQL> alter session set DB_CREATE_FILE_DEST='/u01/data/pdbvcdb';
Session altered.

SQL> SHOW PARAMETER DB_CREATE_FILE_DEST;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      /u01/data/pdbvcdb

SQL> create pluggable database pdbvcdb admin user vcadmin identified by passw0rd roles=(CONNECT);
Pluggable database created.

SQL> col pdb_name format a20

SQL> col status format a20
SQL> select pdb_name, status from dba_pdbs;

PDB_NAME             STATUS
-------------------- --------------------
PDBORCL              NORMAL
PDB$SEED             NORMAL
PDBVCDB              NEW

Oracle Tablespace Administration

User needs to have sysdba permission to perform the following.
e.g
sqlplus "/as sysdba"

 Investigate current tablespace state:

column "Total MB"   format 99,999,999
select fs.tablespace_name "Tablespace",(df.totalspace - fs.freespace) "Used MB",fs.freespace "Free MB",
df.totalspace  "Total MB",round(100 * (fs.freespace / df.totalspace)) "Pct. Free"
from (select tablespace_name, round(sum(bytes) / 1048576) TotalSpace from
dba_data_files group by tablespace_name) df,(select tablespace_name,
round(sum(bytes) / 1048576) FreeSpace from dba_free_space
group by tablespace_name) fs where df.tablespace_name = fs.tablespace_name;


Tablespace            Used MB     Free MB    Total MB  Pct. Free
------------------- ----------- ----------- ----------- ----------
CENTERDS                 1       1,023       1,024        100
TRD                  2,712          52      32,764          0
SYSAUX                 981         109       1,090         10
UNDOTBS1               183      23,689      23,872         99
TRD_INDX             1,729          95       1,824          5
VC50                     1       1,023       1,024        100
CVCDDB                 214         810       1,024         79
TRXADMIN55              86         938       1,024         92
USERS                    4           1           5         20
SYSTEM                 967           3         970          0
11 rows selected.

Notes:

TRD tablespspace is running 0% free. That was the root cause for "ORA-1653: unable to extend table".

32gig is a standard maximum logical size an Oracle DBA could allocate for a tablespace. That is an indication that, this tablespace can no longer be extended/expanded but need to add a brand new datafile to it.

Every tablespace contains one or more datafiles. User should find out where they are and if they are autoextend.



select file_id, file_name, round(bytes/1024/1024, 2) MB, status, autoextensible, round(maxbytes/1024/1024,2)  max_mb from dba_data_files where tablespace_name like upper('TRD') order by file_id;

FILE_ID   FILE_NAME                                       MB    STATUS    AUT   MAX_MB
-------   --------------------------------------          ---   -------   ----- -----------
7         /home/oracle/app/oracle/oradata/orcl/TRD.dbf    32764 AVAILABLE YES   32767.98


Once finding out what is the situation for the tablespace. User should choose the right options below and implement it. User should not overallocate the tablespace extend. Gradually increasing would be ideal if the user is not familiar with the application growth rate.

Option 1: Add new datafile to existing tablespace TRD. Set maximum size to 5G and autoextend. It will grow as it needs up to 5 gig.

SYS>  alter tablespace TRD add datafile '/home/oracle/app/oracle/oradata/orcl/TRD3.dbf' size 200M autoextend on maxsize 5G;
Tablespace altered.

Option 2: Add a new TRD tablespace datafile as TRD4.dbf without autoextend. This is likely the most commonly used option. This will pre-allocate all the space from the physical storage for 5 gig regardless it is used or not.

SYS> alter tablespace TRD add datafile '/home/oracle/app/oracle/oradata/orcl/TRD4.dbf' size 5G;
Tablespace altered.

Option 3: If user realized later on that 5 gig isn't sufficient, s/he can use "resize" to grow it further the datafile has not reaching its max of 32G as yet.

SYS> alter database datafile '/home/oracle/app/oracle/oradata/orcl/TRD4.dbf' resize 6G;
Database altered.

Option 4: similar to option 3 except this is to grow the maxsize and flipping it to AUTOEXTEND mode.

SYS> alter database datafile '/home/oracle/app/oracle/oradata/orcl/TRD4.dbf' autoextend on maxsize 7G;
Database altered.

User should check an overall space allocation for the tablespace and physical storage consumption. Use this query to check how the tablespace or datafiles allocation looks like after the changes.

select file_id, file_name, round(bytes/1024/1024, 2) MB, status, autoextensible, round(maxbytes/1024/1024,2)  max_mb from dba_data_files where tablespace_name like upper('TRD') order by file_id;

User should also monitor the grow of the physical filesystem that housing the datafiles.

SYS> !df -h
Filesystem             Size  Used Avail Use% Mounted on
/dev/mapper/VolGroup00-LogVol00   156G  133G  15G   90% /
/dev/sda2             9.5G  6.4G  2.7G  71% /stage
/dev/sda1               99M   13M   82M   14% /boot
tmpfs                   16G   4.0G  12G   26% /dev/shm
/dev/mapper/VolGroup02-backup 59G   36G   21G   63% /backup
/dev/mapper/VolGroup02-u01

ODBC error: (HY000) - [Oracle][ODBC][Ora]ORA-01555: snapshot too old: rollback segment number with name "" too small.

This isn't a real ORA-01555 issue where extending the UNDO tablespace will fix the issue, It is an actually LOB datatype corruption issue.

Running Oracle datapump  "expdp" on the schema will also reveal the table that has the corruption although it will not reveal the specific column:

1. Identify if UNDO tablespace is enough to grow:
   
   col file_name format a60
   set pagesize 120
   set linesize 1000
   select file_id, file_name, round(bytes/1024/1024, 2) MB, status, autoextensible, round(maxbytes/1024/1024,2)  max_mb from dba_data_files where tablespace_name like upper ('UNDO%') order by file_id;
   
   
   
2. Create a temporary table as place holder:
   
   create table corrupted_lob_data (corrupted_rowid rowid);
   
   
   
3. Run the following procedure to catch the error 1555:
   
   set concat off
   declare
   error_1555 exception;
   pragma exception_init(error_1555,-1555);
   num number;
   begin
   for cursor_lob in (select rowid r, &&lob_column from &table_owner.&table_with_lob) loop
   begin
   num := dbms_lob.instr (cursor_lob.&&lob_column, hextoraw ('889911')) ;
   exception
   when error_1555 then
   insert into corrupted_lob_data values (cursor_lob.r);
   commit;
   end;
   end loop;
   end;
   /
   Enter value for
   
   lob_column: VALUE
   Enter value for table_owner: vpxadmin
   Enter value for table_with_lob: VPX_TEXT_ARRAY
   old 6: for cursor_lob in (select rowid r, &&lob_column from &table_owner.&table_with_lob) loop
   new 6: for cursor_lob in (select rowid r, VALUE from vpxadmin.VPX_TEXT_ARRAY) loop
   old 8: num := dbms_lob.instr (cursor_lob.&&lob_column, hextoraw ('889911')) ;
   new 8: num := dbms_lob.instr (cursor_lob.VALUE, hextoraw ('889911')) ;
   PL/SQL procedure successfully completed.
   
   
   
   
4. Take a look at the placeholder table for corrupted rowid. You might have more than one rows.
   
   SQL> select * from corrupted_lob_data;
   
   CORRUPTED_ROWID
   ------------------
   AAB7XAAAGAAEFjwAAC
   
   
   
5. To confirm if the row/s associated were really corrupted. Do this in SQLPLUS and it should error out the same way as it happens in the VCD log states. Note: DO NOT use SQLDEVELOPER to track this, it will not produced the exact error since it skip the contents of the LOB column.
   
   Select * from vpx_text_array where rowid=’AAB7XAAAGAAEFjwAAC’
   
   

This blog will take you as far as tracking the corrupted cells.