Data … as usual

All things about data by Laurent Leturgez

Monthly Archives: December 2016

How to Get Oracle Global Temporary Table (GTT) size

Leave a comment Posted by on December 21, 2016

A very quick post to detail a script I wrote about GTT, and more precisely how to know the size occupied by a Session GTT (Global Temporary Table).

GTT are allocated as a temporary segment in the temporary tablespace. So we can get information about it in the V$SORT_USAGE view.

The problem, in this view, you don’t have the name of your table, nor the object id, but having a look in the underlying object makes me discovering the X$KTSSO view which have this information.

I wrote the following query (very quickly) to retrieve this information (This script has been tested on Oracle 11.2.0.4).


set lines 400 pages 500 trimspool on
select s.sid, s.serial#, s.program,o.obj#,o.name,decode(bitand(o.flags, 2), 0, 'N', 2, 'Y', 'N') temporary,
temp_obj.ktssoexts extents, temp_obj.ktssoblks blocks, temp_obj.ktssoblks*blk_sz.bs bytes
from obj$ o, 
	(select * from x$ktsso) temp_obj,
	(select value bs from v$parameter where name='db_block_size') blk_sz, 
	v$session s, 
	tab$ t
where o.obj# =temp_obj.KTSSOOBJN
and t.obj#=o.obj# 
and bitand(o.flags, 2)=2
and s.saddr=temp_obj.ktssoses;

       SID    SERIAL# PROGRAM                                                OBJ# NAME                           T    EXTENTS     BLOCKS      BYTES
---------- ---------- ------------------------------------------------ ---------- ------------------------------ - ---------- ---------- ----------
        43       1209 sqlplus@oel6.localdomain (TNS V1-V3)                  88956 T_SALES                        Y         35       4480   36700160
        43       1209 sqlplus@oel6.localdomain (TNS V1-V3)                   5187 PLAN_TABLE$                    Y          1        128    1048576
        35       1317 SQL Developer                                          5187 PLAN_TABLE$                    Y          1        128    1048576

This script can give you some clues about values to set for statistics on these GTTs when they are used accross many sessions and you don’t really know their size.

That’s it for today.

 

Advertisement
11g, tools, tuning

Database Load heatmap with AWR and Python

2 Comments Posted by on December 15, 2016

Recently, one of my customer asked me to design him a tool to quickly see the database load. He already did that for another Rdbms with a heatmap.

To do that, I used two distinct part in my work: Data Extraction, and Data Vizualisation.

Data Extraction from AWR

As he has licensed his databases with the Diagnostic pack , I decided to use AWR repository, and as I’m a bit lazy, I found a query from Marcin Przepiorowski (@pioro) and I modified it to pivot data.


col "00-01_ " for 90.99
col "01-02_ " for 90.99
col "02-03_ " for 90.99
col "03-04_ " for 90.99
col "04-05_ " for 90.99
col "05-06_ " for 90.99
col "06-07_ " for 90.99
col "07-08_ " for 90.99
col "08-09_ " for 90.99
col "09-10_ " for 90.99
col "10-11_ " for 90.99
col "11-12_ " for 90.99
col "12-13_ " for 90.99
col "13-14_ " for 90.99
col "14-15_ " for 90.99
col "15-16_ " for 90.99
col "16-17_ " for 90.99
col "17-18_ " for 90.99
col "18-19_ " for 90.99
col "19-20_ " for 90.99
col "20-21_ " for 90.99
col "21-22_ " for 90.99
col "22-23_ " for 90.99
col "23-24_ " for 90.99

WITH t AS
  (SELECT TO_CHAR(mtime,'YYYY/MM/DD') mtime,
    TO_CHAR(mtime,'HH24') d,
    LOAD AS value
  FROM
    (SELECT to_date(mtime,'YYYY-MM-DD HH24') mtime,
      ROUND(SUM(c1),2) AAS_WAIT,
      ROUND(SUM(c2),2) AAS_CPU,
      ROUND(SUM(cnt),2) AAS,
      ROUND(SUM(load),2) LOAD
    FROM
      (SELECT TO_CHAR(sample_time,'YYYY-MM-DD HH24') mtime,
        DECODE(session_state,'WAITING',COUNT(*),0)/360 c1,
        DECODE(session_state,'ON CPU',COUNT( *),0) /360 c2,
        COUNT(                               *)/360 cnt,
        COUNT(                               *)/360/cpu.core_nb load
      FROM dba_hist_active_sess_history,
        (SELECT value AS core_nb FROM v$osstat WHERE stat_name='NUM_CPU_CORES'
        ) cpu
      WHERE sample_time > sysdate - 30
      GROUP BY TO_CHAR(sample_time,'YYYY-MM-DD HH24'),
        session_state,
        cpu.core_nb
      )
    GROUP BY mtime
    )
  )
SELECT mtime,
  NVL("00-01_ ",0) "00-01_ ",
  NVL("01-02_ ",0) "01-02_ ",
  NVL("02-03_ ",0) "02-03_ ",
  NVL("03-04_ ",0) "03-04_ ",
  NVL("04-05_ ",0) "04-05_ ",
  NVL("05-06_ ",0) "05-06_ ",
  NVL("06-07_ ",0) "06-07_ ",
  NVL("07-08_ ",0) "07-08_ ",
  NVL("08-09_ ",0) "08-09_ ",
  NVL("09-10_ ",0) "09-10_ ",
  NVL("10-11_ ",0) "10-11_ ",
  NVL("11-12_ ",0) "11-12_ ",
  NVL("12-13_ ",0) "12-13_ ",
  NVL("13-14_ ",0) "13-14_ ",
  NVL("14-15_ ",0) "14-15_ ",
  NVL("15-16_ ",0) "15-16_ ",
  NVL("16-17_ ",0) "16-17_ ",
  NVL("17-18_ ",0) "17-18_ ",
  NVL("18-19_ ",0) "18-19_ ",
  NVL("19-20_ ",0) "19-20_ ",
  NVL("20-21_ ",0) "20-21_ ",
  NVL("21-22_ ",0) "21-22_ ",
  NVL("22-23_ ",0) "22-23_ ",
  NVL("23-24_ ",0) "23-24_ "
FROM t pivot( SUM(value) AS " " FOR d IN ('00' AS "00-01",'01' AS "01-02",'02' AS "02-03",'03' AS "03-04",'04' AS "04-05",'05' AS "05-06",'06' AS "06-07",'07' AS "07-08",
                                          '08' AS "08-09",'09' AS "09-10",'10' AS "10-11", '11' AS "11-12",'12' AS "12-13",'13' AS "13-14",'14' AS "14-15",'15' AS "15-16",
                                          '16' AS "16-17",'17' AS "17-18",'18' AS "18-19",'19' AS "19-20",'20' AS "20-21",'21' AS "21-22", '22' AS "22-23",'23' AS "23-24") 
            )
ORDER BY mtime

Nota: this query can be used to extract Database Load, Active Average Session (AAS), CPU part of AAS, and the Wait part of AAS. You just have to replace the value alias in the WITH Block.

Another point, I didn’t look at the plan, so I didn’t tune the statement … if you have some tricks to tune it … feel free to comment 😉

This query gives this kind of result:

MTIME      00-01_  01-02_  02-03_  03-04_  04-05_  05-06_  06-07_  07-08_  08-09_  09-10_  10-11_  11-12_  12-13_  13-14_  14-15_  15-16_  16-17_  17-18_  18-19_  19-20_  20-21_  21-22_  22-23_  23-24_
---------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- -------
2016/11/23    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.03    0.03    0.00    0.04    0.02    0.00    0.00    0.04    0.01    0.01    0.01    0.04    0.01
2016/11/24    0.02    0.03    0.01    0.03    0.05    0.02    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00
2016/12/06    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.01    0.01    0.02    0.02    0.00    0.00    0.00    0.00
2016/12/07    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.01    0.00    0.00    0.04    0.03    0.01    0.02    0.01    0.02    0.01    0.01    0.06    0.03
2016/12/08    0.03    0.05    0.02    0.02    0.03    0.03    0.02    0.02    0.03    0.02    0.01    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00
2016/12/12    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.03    0.02    0.05    0.02
2016/12/13    0.03    0.01    0.01    0.01    0.02    0.03    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00
2016/12/14    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.02    0.02    0.02    0.02    0.18    0.03    0.02    0.01    0.01    0.00    0.00    0.00    0.00    0.00

When the result is greater than 1, your database is loaded, but if it’s greater or equal that 2 … it’s really overloaded. In both cases, you have to analyze why ?

The problem with this kind of results is it’s only numbers and you have to analyze the complete results to detect when the problem occurs, here comes the data vizualisation part 😉

Data Vizualisation with Python

On the Oracle database Area, a guy has made a lot for visualization of performance data, it’s Kyle Hailey (@dbavigil). And Visualize your data is a very important thing to quickly identify bottleneck. So if you’re interested by data vizualisation you can visit his blog, you will find a lot of resources to extract AWR data and to visualize them (and other stuff).

So I decided to use Python to vizualise database load data extracted previously. To do that, I used python 3.4.5 with a bunch of packages installed:

  • cx_Oracle to connect Oracle Database and execute queries on your Oracle Database
  • numpy for arrays manipulation
  • plotly to graph the heatmap

The python script is available by following this URL: https://app.box.com/s/irjzi64lbne6xhw3t57aixqiy95toxht. Then, you have to modify it to enter the connection string (line 33).

My customer asked me that my script generates an HTML file  to visualize the heatmap in a browser (One of the reason I used plotly).

This script generates the file in /var/tmp (but you can modify it at the end) and then open the browser locally (So don’t forget to export X11 display if you run it on a linux server).

Below you can see the heatmap (X Axis is the hour range, Y Axis represent a day analyzed, the most recent day is on the first line of the heatmap.).

heatmap_s

This heatmap will help my customer to quickly visualize his database load, I hope it will help you too ;).

Of course, you can use the tool of your choice to visualize data (Tableau, Excel, Kibana etc.)

 

 

AWR, Development, Oracle, tools

How Oracle 12.2 manage Editions.

Leave a comment Posted by on December 8, 2016

Recently, I wrote a blog post about suspected new editions: Enteprise Core edition and Standard Core Edition.

This morning, I received a mention from twitter coming from Franck Pachot:

In GV$INSTANCE definition, we can see that edition is encoded in X$KSUXSINST view (in the column KSUXSEDITION). The corresponding code is this one:

  • 2 = PO = Personal Ed.
  • 4 = SE = Standard Ed.
  • 8 = EE = Enterprise Ed.
  • 16 = XE = eXpress Ed.
  • 32 = CS = Standard Core Ed.
  • 64 = CE = Enterprise Core Ed.
  • 128 = HP = Enterprise Ed. High Perf
  • 256 = XP = Enteprise Ed. Extreme Perf

How Oracle use these codes to determine Oracle Edition ?

When, you relink your Oracle kernel, you use ins_rdbms.mk makefile located in $ORACLE_HOME/rdbms/lib and you run a make command with specific targets.

For example, if you wan to link Core Enterprise Edition, you will run this (See my previous blog post):

$ make -f ins_rdbms.mk edition_coreenterprise ioracle
Deploying Oracle Database Core Enterprise Edition
mv -f /u01/app/oracle/product/12.2.0/dbhome_1/lib/libvsn12.a /u01/app/oracle/product/12.2.0/dbhome_1/lib/libvsn12_backup.a.dbl
cp /u01/app/oracle/product/12.2.0/dbhome_1/lib/libvsn12_cee.a.dbl /u01/app/oracle/product/12.2.0/dbhome_1/lib/libvsn12.a
chmod 755 /u01/app/oracle/product/12.2.0/dbhome_1/bin

In fact, it uses a library which is copied as $ORACLE_HOME/lib/libvsn12.a and then this library is linked to Oracle kernel.

There are many libraries in the 12.2 kernel:

$ cd $ORACLE_HOME/lib
$ ls libvsn* -l
-rw-r--r-- 1 oracle oinstall 10774 Nov 28 17:47 libvsn12.a
-rw-r--r-- 1 oracle oinstall 10742 Oct  4 00:46 libvsn12.a.default
-rw-r--r-- 1 oracle oinstall 10774 Nov 28 17:40 libvsn12_backup.a.dbl
-rw-r--r-- 1 oracle oinstall 10790 Oct  4 00:46 libvsn12_cee.a.dbl
-rw-r--r-- 1 oracle oinstall 10790 Oct  4 00:46 libvsn12_cse.a.dbl
-rw-r--r-- 1 oracle oinstall 10774 Oct  4 00:46 libvsn12_hp.a.dbl
-rw-r--r-- 1 oracle oinstall 10766 Oct  4 00:46 libvsn12_std.a.dbl
-rw-r--r-- 1 oracle oinstall 10774 Oct  4 00:46 libvsn12_xp.a.dbl

After a closer look inside those libraries, I found they are containing two object files (.o)

$ ar -t libvsn12_hp.a.dbl
vsnhp.o
vsnfhp.o
$ ar -t libvsn12_cee.a.dbl
vsncee.o
vsnfcee.o
$ ar -t libvsn12_std.a.dbl
vsnstd.o
vsnfstd.o

etc...

After extracting these files from the library, and reading the ELF section, we can see:

  • the vsnXXX.o file contains the banner
  • the vsnfXXX.o file contains other things but I don’t know what yet.
$ ar -x libvsn12_cee.a.dbl
$ objdump -s vsncee.o

vsncee.o:     file format elf64-x86-64

Contents of section .comment:

.../...

Contents of section .rodata:
 0000 4f726163 6c652044 61746162 61736520  Oracle Database
 0010 31326320 456e7465 72707269 73652045  12c Enterprise E
 0020 64697469 6f6e202d 20436f72 65202564  dition - Core %d
 0030 2e25642e 25642e25 642e2564 20257300  .%d.%d.%d.%d %s.
 0040 00000000 40000000 2d203634 62697420  ....@...- 64bit
 0050 50726f64 75637469 6f6e0000 00000000  Production......

$ objdump -s vsnfcee.o

vsnfcee.o:     file format elf64-x86-64

Contents of section .comment:

.../...

Contents of section text.unlikely:
 0000 90909090 90909090 90909090 90909090  ................
 0010 66905548 89e5b840 00000048 89ec5dc3  f.UH...@...H..].
Contents of section .eh_frame:
 0000 14000000 00000000 01000178 100c0708  ...........x....
 0010 90010000 00000000 2c000000 1c000000  ........,.......
 0020 00000000 00000000 10000000 00000000  ................
 0030 04030000 000e1004 03000000 0c061086  ................
 0040 02040900 0000c600                    ........

If we compare these sections (text.unlikely and .eh_frame) between many Edition library files, we can see the following points:

  • Eh_frame section contains exception unwinding and source language information. They shouldn’t be very different:
$ objdump -s -j .eh_frame vsnfcee.o vsnfxp.o vsnfhp.o vsnfstd.o vsnfcse.o

vsnfcee.o:     file format elf64-x86-64

Contents of section .eh_frame:
 0000 14000000 00000000 01000178 100c0708  ...........x....
 0010 90010000 00000000 2c000000 1c000000  ........,.......
 0020 00000000 00000000 10000000 00000000  ................
 0030 04030000 000e1004 03000000 0c061086  ................
 0040 02040900 0000c600                    ........

vsnfxp.o:     file format elf64-x86-64

Contents of section .eh_frame:
 0000 14000000 00000000 01000178 100c0708  ...........x....
 0010 90010000 00000000 2c000000 1c000000  ........,.......
 0020 00000000 00000000 10000000 00000000  ................
 0030 04030000 000e1004 03000000 0c061086  ................
 0040 02040900 0000c600                    ........

vsnfhp.o:     file format elf64-x86-64

Contents of section .eh_frame:
 0000 14000000 00000000 01000178 100c0708  ...........x....
 0010 90010000 00000000 2c000000 1c000000  ........,.......
 0020 00000000 00000000 10000000 00000000  ................
 0030 04030000 000e1004 03000000 0c061086  ................
 0040 02040900 0000c600                    ........

vsnfstd.o:     file format elf64-x86-64

Contents of section .eh_frame:
 0000 14000000 00000000 01000178 100c0708  ...........x....
 0010 90010000 00000000 2c000000 1c000000  ........,.......
 0020 00000000 00000000 10000000 00000000  ................
 0030 04030000 000e1004 03000000 0c061086  ................
 0040 02040900 0000c600                    ........

vsnfcse.o:     file format elf64-x86-64

Contents of section .eh_frame:
 0000 14000000 00000000 01000178 100c0708  ...........x....
 0010 90010000 00000000 2c000000 1c000000  ........,.......
 0020 00000000 00000000 10000000 00000000  ................
 0030 04030000 000e1004 03000000 0c061086  ................
 0040 02040900 0000c600                    ........

Indeed, these sections are identical.

  • Now let’s see the .text.unlikely section:
$ objdump -s -j text.unlikely vsnfcee.o vsnfxp.o vsnfhp.o vsnfstd.o vsnfcse.o

vsnfcee.o:     file format elf64-x86-64

Contents of section text.unlikely:
 0000 90909090 90909090 90909090 90909090  ................
 0010 66905548 89e5b840 00000048 89ec5dc3  f.UH...@...H..].

vsnfxp.o:     file format elf64-x86-64

Contents of section text.unlikely:
 0000 90909090 90909090 90909090 90909090  ................
 0010 66905548 89e5b800 01000048 89ec5dc3  f.UH.......H..].

vsnfhp.o:     file format elf64-x86-64

Contents of section text.unlikely:
 0000 90909090 90909090 90909090 90909090  ................
 0010 66905548 89e5b880 00000048 89ec5dc3  f.UH.......H..].

vsnfstd.o:     file format elf64-x86-64

Contents of section text.unlikely:
 0000 90909090 90909090 90909090 90909090  ................
 0010 66905548 89e5b804 00000048 89ec5dc3  f.UH.......H..].

vsnfcse.o:     file format elf64-x86-64

Contents of section text.unlikely:
 0000 90909090 90909090 90909090 90909090  ................
 0010 66905548 89e5b820 00000048 89ec5dc3  f.UH... ...H..].

At the first look, they seem identical … but they are not, the section highlighted in red is different, and as my platform is linux and so little-endian platform, we have to read the highlighted blocks like this:

  • vsnfstd.o : 0x00 04 = 4
  • vsnfcse.o 0x00 20 = 32
  • vsnfcee.o : 0x00 40 = 64
  • vsnfhp.o : 0x00 80 = 128
  • vsnfxp.o : 0x01 00 = 256

So Oracle have just some libraries that encore the edition, and this code seems to enable some option at the runtime depending on the value included in the library (which is much more secure than having a list of enabled options embedded in a library).

Another thing to mention, in my instance, no trace of Enterprise Edition nor Express and Personal Edition, even if they are coded in GV$INSTANCE view’s code.

UPDATE: It seems that Stefan Koehler (@OracleSK) has the same conclusion but using another method:

12c, Internals, Oracle

Oracle 12.2, new release … new editions or just a cloudy feature ?

1 Comment Posted by on December 1, 2016

Recently I have created an Oracle 12.2 database in the Oracle Cloud (Extreme Performance). I was able to test some of the new features.

And usually, when I test a new release of Oracle, I have a look into the ins_rdbms.mk file to see if there are some new options to link the Oracle kernel, and in this release I found some interesting stuff.

As I said before, my instance was in the Extreme Performance, it was normal that, when connected, I got this banner:

Connected to:
Oracle Database 12c EE Extreme Perf Release 12.2.0.1.0 - 64bit Production

And when I had a look to the V$INSTANCE view, there’s a column EDITION. This column is already here in 12.1 and is documented.

If you have a closer look to the documentation (12.1), you can see different editions:

  • CORE EE: CORE Enterprise Edition
  • CORE SE: CORE Standard Edition
  • EE: Enterprise Edition
  • PO: Personal Edition
  • SE: Standard Edition
  • XE: Express Edition

Ok for EE, PO, SE and XE … they are well known edition, but what about those “Core” Editions (Standard and Enterprise).

Note: on 1st December, 12.2 documentation mention only Core EE, EE, PO and XE (Might be a doc bug) 

I had a look to both (12.1 and 12.2) ins_rdbms.mk files (located in $ORACLE_HOME/lib folder). For On premises installations (11.2 & 12.1), no trace of these new editions, but they are available on Oracle Cloud Platform

  • Oracle 12.1.0.2
$ grep -i edi /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/lib/ins_rdbms.mk
edition_corestandard:
        $(SILENT)$(ECHO) "Deploying Oracle Database Core Standard Edition"
edition_coreenterprise:
        $(SILENT)$(ECHO) "Deploying Oracle Database Core Enterprise Edition"
edition_standard:
        $(SILENT)$(ECHO) "Deploying Oracle Database Standard Edition"
edition_enterprise:
        $(SILENT)$(ECHO) "Deploying Oracle Database Enterprise Edition"
edition_highperf:
        $(SILENT)$(ECHO) "Deploying Oracle Database Enterprise Edition High Performance"
edition_extremeperf:
        $(SILENT)$(ECHO) "Deploying Oracle Database Enterprise Edition Extreme Performance"
  • Oracle 12.2.0.1
$ grep -i edi /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/lib/ins_rdbms.mk
edition_corestandard:
        $(SILENT)$(ECHO) "Deploying Oracle Database Core Standard Edition"
edition_coreenterprise:
        $(SILENT)$(ECHO) "Deploying Oracle Database Core Enterprise Edition"
edition_standard:
        $(SILENT)$(ECHO) "Deploying Oracle Database Standard Edition"
edition_enterprise:
        $(SILENT)$(ECHO) "Deploying Oracle Database Enterprise Edition"
edition_highperf:
        $(SILENT)$(ECHO) "Deploying Oracle Database Enterprise Edition High Performance"
edition_extremeperf:
        $(SILENT)$(ECHO) "Deploying Oracle Database Enterprise Edition Extreme Performance"

ohoohhh ;), let’s try to activate those rules and relink the kernel

$ make -f ins_rdbms.mk edition_coreenterprise ioracle
Deploying Oracle Database Core Enterprise Edition
mv -f /u01/app/oracle/product/12.2.0/dbhome_1/lib/libvsn12.a /u01/app/oracle/product/12.2.0/dbhome_1/lib/libvsn12_backup.a.dbl
cp /u01/app/oracle/product/12.2.0/dbhome_1/lib/libvsn12_cee.a.dbl /u01/app/oracle/product/12.2.0/dbhome_1/lib/libvsn12.a
chmod 755 /u01/app/oracle/product/12.2.0/dbhome_1/bin

 - Linking Oracle
rm -f /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/lib/oracle
/u01/app/oracle/product/12.2.0/dbhome_1/bin/orald  -o /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/lib/oracle -m64 -z noexecstack -Wl,--disable-new-dtags -L/u01/app/oracle/product/12.2.0/dbhome_1/rdbms/lib/ -L/u01/app/oracle/product/12.2.0/dbhome_1/lib/ -L/u01/app/oracle/product/12.2.0/dbhome_1/lib/stubs/   
-Wl,-E /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/lib/opimai.o /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/lib/ssoraed.o /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/lib/ttcsoi.o -Wl,--whole-archive -lperfsrv12 -Wl,--no-whole-archive /u01/app/oracle/product/12.2.0/dbhome_1/lib/nautab.o 
/u01/app/oracle/product/12.2.0/dbhome_1/lib/naeet.o /u01/app/oracle/product/12.2.0/dbhome_1/lib/naect.o /u01/app/oracle/product/12.2.0/dbhome_1/lib/naedhs.o /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/lib/config.o  
-ldmext -lserver12 -lodm12 -lofs -lcell12 -lnnet12 -lskgxp12 -lsnls12 -lnls12  -lcore12 -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lxml12 -lcore12 -lunls12 -lsnls12 -lnls12 -lcore12 -lnls12 -lclient12  -lvsn12 -lcommon12 
-lgeneric12 -lknlopt `if /usr/bin/ar tv /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/lib/libknlopt.a | grep xsyeolap.o > /dev/null 2>&1 ; then echo "-loraolap12" ; fi` -lskjcx12 -lslax12 -lpls12  -lrt -lplp12 
-ldmext -lserver12 -lclient12  -lvsn12 -lcommon12 -lgeneric12 `if [ -f /u01/app/oracle/product/12.2.0/dbhome_1/lib/libavserver12.a ] ; then echo "-lavserver12" ; else echo "-lavstub12"; fi`
 `if [ -f /u01/app/oracle/product/12.2.0/dbhome_1/lib/libavclient12.a ] ; then echo "-lavclient12" ; fi` -lknlopt -lslax12 -lpls12  -lrt -lplp12 -ljavavm12 -lserver12  -lwwg  `cat /u01/app/oracle/product/12.2.0/dbhome_1/lib/ldflags` 
   -lncrypt12 -lnsgr12 -lnzjs12 -ln12 -lnl12 -lngsmshd12 -lnro12 `cat /u01/app/oracle/product/12.2.0/dbhome_1/lib/ldflags`    -lncrypt12 -lnsgr12 -lnzjs12 -ln12 -lnl12 -lngsmshd12 -lnnzst12 -lzt12 -lztkg12 -lmm -lsnls12 -lnls12  -lcore12
 -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lxml12 -lcore12 -lunls12 -lsnls12 -lnls12 -lcore12 -lnls12 -lztkg12 `cat /u01/app/oracle/product/12.2.0/dbhome_1/lib/ldflags`    -lncrypt12 -lnsgr12 -lnzjs12 -ln12 -lnl12 -lngsmshd12 -lnro12
 `cat /u01/app/oracle/product/12.2.0/dbhome_1/lib/ldflags`    -lncrypt12 -lnsgr12 -lnzjs12 -ln12 -lnl12 -lngsmshd12 -lnnzst12 -lzt12 -lztkg12   -lsnls12 -lnls12  -lcore12 -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lxml12 -lcore12 -lunls12
 -lsnls12 -lnls12 -lcore12 -lnls12 `if /usr/bin/ar tv /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/lib/libknlopt.a | grep "kxmnsd.o" > /dev/null 2>&1 ; then echo " " ; else echo "-lordsdo12 -lserver12"; fi` 
-L/u01/app/oracle/product/12.2.0/dbhome_1/ctx/lib/ -lctxc12 -lctx12 -lzx12 -lgx12 -lctx12 -lzx12 -lgx12 -lordimt12 -lclsra12 -ldbcfg12 -lhasgen12 -lskgxn2 -lnnzst12 -lzt12 -lxml12 -lgeneric12 -locr12 -locrb12 -locrutl12 -lhasgen12
 -lskgxn2 -lnnzst12 -lzt12 -lxml12 -lgeneric12  -lgeneric12 -lorazip -loraz -llzopro5 -lorabz2 -lipp_z -lipp_bz2 -lippdcemerged -lippsemerged -lippdcmerged  -lippsmerged -lippcore  -lippcpemerged -lippcpmerged  -lsnls12 -lnls12  -lcore12 
-lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lxml12 -lcore12 -lunls12 -lsnls12 -lnls12 -lcore12 -lnls12 -lsnls12 -lunls12  -lsnls12 -lnls12  -lcore12 -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lxml12 -lcore12 -lunls12 -lsnls12 -lnls12 
-lcore12 -lnls12 -lasmclnt12 -lcommon12 -lcore12  -laio -lons  -lfthread12   `cat /u01/app/oracle/product/12.2.0/dbhome_1/lib/sysliblist` -Wl,-rpath,/u01/app/oracle/product/12.2.0/dbhome_1/lib -lm   
 `cat /u01/app/oracle/product/12.2.0/dbhome_1/lib/sysliblist` -ldl -lm   -L/u01/app/oracle/product/12.2.0/dbhome_1/lib `test -x /usr/bin/hugeedit -a -r /usr/lib64/libhugetlbfs.so && 
test -r /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/lib/shugetlbfs.o && echo -Wl,-zcommon-page-size=2097152 -Wl,-zmax-page-size=2097152 -lhugetlbfs`
test ! -f /u01/app/oracle/product/12.2.0/dbhome_1/bin/oracle || (\
           mv -f /u01/app/oracle/product/12.2.0/dbhome_1/bin/oracle /u01/app/oracle/product/12.2.0/dbhome_1/bin/oracleO &&\
           chmod 600 /u01/app/oracle/product/12.2.0/dbhome_1/bin/oracleO )
mv /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/lib/oracle /u01/app/oracle/product/12.2.0/dbhome_1/bin/oracle
chmod 6751 /u01/app/oracle/product/12.2.0/dbhome_1/bin/oracle

That seems to work, now let’s start the instance and let’s connect:

$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Nov 28 16:41:28 2016

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition - Core 12.2.0.1.0 - 64bit Production

New banner … but are there any new features ?

If we compare EE Core edition and EE Extreme perf, it seems there are some difference in the field of enabled options:

  • EE Core Edition (12.2)
SQL> select parameter,value from v$option where value!='TRUE' order by 1;

PARAMETER                      VALUE
------------------------------ ------------------------------
ASM Proxy Instance             FALSE *
Active Data Guard              FALSE
Adaptive Execution Plans       FALSE
Advanced Analytics             FALSE
Automatic Storage Management   FALSE *
Cache Fusion Lock Accelerator  FALSE
Change Data Capture            FALSE
DICOM                          FALSE
Data Mining                    FALSE
Exadata Discovery              FALSE
Global Data Services           FALSE
I/O Server                     FALSE * 
Management Database            FALSE *
OLAP                           FALSE
Oracle Data Guard              FALSE
Oracle Database Vault          FALSE *
Oracle Label Security          FALSE *
Partitioning                   FALSE
Real Application Clusters      FALSE *
Real Application Security      FALSE
Real Application Testing       FALSE
Spatial                        FALSE
Unified Auditing               FALSE *
  • EE Extreme Perf Edition and High Perf Edition (12.2)
SQL> select parameter,value from v$option where value!='TRUE' order by 1;

PARAMETER                      VALUE
------------------------------ ------------------------------
ASM Proxy Instance             FALSE
Automatic Storage Management   FALSE
I/O Server                     FALSE
Management Database            FALSE
Oracle Database Vault          FALSE
Oracle Label Security          FALSE
Real Application Clusters      FALSE
Unified Auditing               FALSE

And if we have a look to “traditional” EE that we can choose from the Cloud Interface … (12.2)

SQL> select parameter,value from v$option where value!='TRUE' order by 1;

PARAMETER                      VALUE
------------------------------ ----------------------------------------
ASM Proxy Instance             FALSE
Automatic Storage Management   FALSE
I/O Server                     FALSE
Management Database            FALSE
Oracle Database Vault          FALSE
Oracle Label Security          FALSE
Real Application Clusters      FALSE
Unified Auditing               FALSE

OK, so Enterprise Core Edition and Enterprise Edition look differents in terms of available options. And like Franck Pachot pointed it out, the Core EE is like a traditional EE but with all paid options disabled and other EE features disabled (DataGuard for example).

 

On the field of licensing, these Core editions seem to be unavailable. If you buy Oracle License, on-premises or in the cloud, you only have the choice between SE, EE and EE High Perf and EE Extreme perf (in the cloud for HP and XP), no Core Editions.

If we go back to ins_rdbms.mk file, it’s possible to link these editions only in Oracle Cloud Service for 12.1 and 12.2, but Oracle Enterprise Edition 12.2 is impossible to link (because of missing libraries), but if Core Edition is a core based license model, Core Edition and Actual Enterprise Edition are licensed per core not per socket, no difference on this field:

$ make -f ins_rdbms.mk edition_enterprise ioracle
Deploying Oracle Database Enterprise Edition
mv -f /u01/app/oracle/product/12.2.0/dbhome_1/lib/libvsn12.a /u01/app/oracle/product/12.2.0/dbhome_1/lib/libvsn12_backup.a.dbl
cp /u01/app/oracle/product/12.2.0/dbhome_1/lib/libvsn12_ee.a.dbl /u01/app/oracle/product/12.2.0/dbhome_1/lib/libvsn12.a
cp: cannot stat `/u01/app/oracle/product/12.2.0/dbhome_1/lib/libvsn12_ee.a.dbl': No such file or directory
make: *** [edition_enterprise] Error 1

Oracle Standard Edition and Standard Core Edition are both able to be linked.

As a conclusion, the “Core” Edition could be :

  • A new licensing model for Cloud and On Premises platform and will be released later. As a consequence, a new licensing model will appear for Standard Edition, not per Socket, but per Core like EE (the Socket model would be abandoned ?)
  • A new licensing model for Cloud platform … only
  • A bug ?

The future will tell us 😉

 

12c, Cloud, licensing, Oracle, SE