Oracle Standard Edition and SQL Developer 4.1

ImageFeatured

SQL Developer is still on his way, growing every day !
It’s last free release is now 4.1.2 dated October 24th 2015. You can read what is the new stuff here, but today we speak about licensing compatibility.
What’s happen when you use it against your Standard Enterprise Database ? Does it trig or not some features related to Diagnostic or Tuning Pack without telling you anything ?

Let’s try to clarify those points, stepping on different parts of the product.

Test case

Database  12C using 12.1.0.1  Standard Edition on linux 64  VM Virtual Box.

See database release and options:
SQL> select parameter, value from v$option where value='TRUE' ;
PARAMETER                                                        VALUE
---------------------------------------------------------------- -----
Objects                                                          TRUE
Connection multiplexing                                          TRUE
Connection pooling                                               TRUE
Database queuing                                                 TRUE
Incremental backup and recovery                                  TRUE
Instead-of triggers                                              TRUE
Parallel load                                                    TRUE
Proxy authentication/authorization                               TRUE
Plan Stability                                                   TRUE
Coalesce Index                                                   TRUE
Transparent Application Failover                                 TRUE
Sample Scan                                                      TRUE
Java                                                             TRUE
OLAP Window Functions                                            TRUE
Flashback Data Archive                                           TRUE
DICOM                                                            TRUE
XStream                                                          TRUE

17 rows selected.

SQL> select banner  from v$version ;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Release 12.1.0.1.0 - 64bit Production
PL/SQL Release 12.1.0.1.0 - Production
CORE    12.1.0.1.0      Production
TNS for Linux: Version 12.1.0.1.0 - Production
NLSRTL Version 12.1.0.1.0 - Production

Parameter  control_management_pack_access correctly set to  NONE:

SQL> show parameter control_management_pack_access
NAME                                 TYPE        VALUE
control_management_pack_access       string      NONE

SQLDeveloper usage

First of all, Enterprise Manager pack usage is already a point for the developpers, you can set pack usage for each connection using:  ‘Tools /Preferences /Database/Licensing’.  There is a choice for:  Changement Pack, Tuning Pack and Diagnostic Pack.
SQLDEV4-1

Three options are  available:

  • Full,  for undefined (default value, see above),
  • white,  no licence for this connection,
  • Check,  Pack is licensiong to this connection.

Leave this like that for now and see what happens.
Two parts are really concerned regarding licensing: Performance report under DBA windows and some delivered report from ‘Data Dictionary Reports’ section. Lets have a look to both of them during our tour, but before that take some times to read once again the licensing rule on the Oracle  documentation.

DBA Window

Full of useful informations: let’s check folder by folder.

Database Configuration
No special feature for licensing on this folder, checking of database feature usage could be done from there.

Database Status
‘Status Report’ is OK and without concern. ‘DB instance’ is a fully new one, you can follow dynamically all the main statistics of your database. The waits part in the bottom left was first suspect for me, because it looks like performance tab ‘Active sessions’ view of Enterprise Manager or DB console with regular select on v$active_session_history:
image_thumb3

So i put a trace on it, using DBMS_MONITOR, and guess what, good job was done there, the underlying views are V$WAITCLASSMETRIC and V$SYSTEM_WAIT_CLASS. No select on ASH, which is really nice as from the setup of control_management_pack_access,  there’s no row in it:
SQL> select count(*) from v$active_session_history ;
COUNT(*)
----------
0

By the way, all the others SELECT of the monitoring come from legacy views, accessible without any Pack. Thanks Dev !!

Data Pump
On Export or Import job, I wanted to verify that parallel mode or compression are unable during the wizard worflow. These two features are not part of Standard Edtion. Here’s the result if I setup a full database export with four threads:
SQLEDV4-ExportWIZ -1

The ORA-39002 and ORA-39094  are not generated by the tool but by the database itself.
For compression mode, it should be the same thing even if i don’t find the option in the wizard.

Performance
On the first click on the box or on the left sign plus this pop up is shown:
SQLDEV4 - Perf 1

Click on “OK” or closing the box is the same, you have absolutely no access to the features, good protection there.

RMAN Backup/Recovery
Features not available in Standard Edition:

  • TABLE Recovery
  • Multiplex destination savesets
  • Bloc change tracking
  • Bloc Recovery
  • Parallélisme pour la sauvegarde et la récupération

‘Backup Jobs’
From there, you can see all the current or past RMAN Backup jobs and from the “Actions” button you can use wizards to create customs backups or recovery jobs. The wizards will help you in writing a script file where all the RMAN commands corresponding to your workflow will be recorded. You then use this file on command line like this:

$RMAN target  sys@[tnsname] @commandFile

Bellow is an exemple of what will be in the command file for a FULL OFFLINE DATABASE BACKUP:
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
SET ENCRYPTION ON FOR ALL TABLESPACES ALGORITHM 'AES128'RUN {
ALLOCATE CHANNEL SQLDEV_BACKUP_DISK0 TYPE DISK FORMAT '';
ALLOCATE CHANNEL SQLDEV_BACKUP_DISK1 TYPE DISK FORMAT '';
ALLOCATE CHANNEL SQLDEV_BACKUP_DISK2 TYPE DISK FORMAT '';
ALLOCATE CHANNEL SQLDEV_BACKUP_DISK3 TYPE DISK FORMAT '';
BACKUP AS BACKUPSET TAG '%TAG' DATABASE;
BACKUP AS BACKUPSET TAG '%TAG' ARCHIVELOG ALL NOT BACKED UP;
RELEASE CHANNEL SQLDEV_BACKUP_DISK0;
RELEASE CHANNEL SQLDEV_BACKUP_DISK1;
RELEASE CHANNEL SQLDEV_BACKUP_DISK2;
RELEASE CHANNEL SQLDEV_BACKUP_DISK3;
}
ALTER DATABASE OPEN;

Despite the fact the FORMAT is not correctly qualified, you can see that four channels are allocated giving a parallelism of four which is not correct for Standard Editon. There is no check against licensing made during script generation.
This is the same for all the options you will set with the wizards, for exemple you could add “HIGH” compression or define “Bloc Recovery”, the command script will always be generated with all the features in it .
You have to verifiy that it’s not against your current licensing policy.

‘Backup Sets’ or ‘Image Copies’ are mainly listing features from the controlfile and crosscheck or cleanup RMAN commands generation, nothing related to licensing.

‘RMAN settings’
Graphical output of “SHOW ALL” command, and CONFIGURE commands interface. One more time, be aware there’s no licensing check against the command . You can generate without any warning these  two:

CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO BACKUPSET PARALLELISM 4;
CONFIGURE COMPRESSION ALGORITHM 'HIGH';

Resource Manager
This feature is only enabled for Enterprise Edition, and should not be used for SE.
As soon as you want to change (by adding or removing directive), activate a resource plan or create a new one, the database answer is an ORA-00439 “Feature not enabled”:
SQLDEV4 - Activate Plan

SQL Translator Framework
Not concerned by database licensing options.

Scheduler
DBMS_SCHEDULER package is available with all license type.

Security
Not concerned by database licensing options.

Storage
The DDL code is generated using any configuration option with the different wizard. The database will respond with “ORA-00039” if any related feature is not supported. See bellow if we try to create a new tablespace with compression:
SQLDEV4 - CRTB

Reports

Reports are predefined and preformatted queries. Many of them are available out of the box after installation and   most of them take care of pack usage but caution should be taken with some. Let’s look deeper to the “suspicious” one specially in the “Data Dictionary ” part.

‘Database Administration/Waits and Events/Events In The Past  X minutes’
The main and the sub-queries are against  v$active_session_history wich is part of  Diagnostic Pack license. The warning pop up says that clearly:
SQLDEV4 - DP req 2

Go on by clicking ‘OK’ or close the box, you have no access to the report.

Same effect for reports ‘Events in the Wait Class For Past 1 Hour’ and ‘Segments For Past 1 Hour’: queries are against v$active_session_history, Warning ‘Diagnostic Pack Required’ poped up.

‘Database Administration/ Memory/Dynamic Memory’
The queries are against fixed tables DBA_HIST_SNAPSHOT et DBA_HIST_SGA, as stated in licensing guide here, Diagnostic Pack licence is required for access to DBA_HIST_SGA but not for DBA_HIST_SNAPSHOT, which can only populated if AWR are running. With control_management_pack_acces set to NONE, no information should be displayed: ‘No data to display’. There’s no need to warning box there.

‘Database Administration/Top  SQL/TOP SQL by Waits’.
Warning you could fire Diagnostic Pack or Tuning Pack usage !
There is a sub-select against  v$active_session_history in the main query, Child report query ‘Explain Plan’ is also against  v$active_session_history for database release > 11, Child report query ‘SQL Tuning Advice’ call DBMS_SQLTUNE (Tuning Pack) several times and child report query ‘SQL Elapse Time History’ is against DBA_HIST_SQLSTAT. All of this without any warning box !  Don’t use this report to stay in conformity with your licence agreement !

‘Database Administration/Sessions/Sessions’
In release prior to 4.1, The child report query ‘Waits’  was againt v$active_session_history for Database >11.1 (without any warning pop up). This is corrected in 4.1.0.19 and the query is now against v$session_wait. But there is still something wrong with child report query ‘Explain Plan’. There are two options for the query and one of them is still against v$active_session_history.
This report is very useful so if you want to use it without fear, copy it and make your own set removing any references to ASH.

Tip: To access the report query and/or modify it. Select the report, use contextual menu (right clic) ‘Copy’ and ‘Paste’ it under ‘User Defined Reports’ and choose ‘Edit’. You will find the SQL query under ‘Master Report’ or ‘Child Reports’.

‘Database Administration/Locks/Blocking Locks By User’
The main query is OK but the child report query ‘SQL From blocking session’ used v$active_session_history as joint to v$sql. No warning pop up there.
Make your own by modifying the query !

‘ASH et AWR’: ‘ASH Report for the past 30 Minutes’, ‘ASH Rows Detail’, ‘Daily ASH Statistics Chart’ and ‘Last AWR Report’ all need Diagnostic Pack Licensing. Each attempt to execute them give warning ‘Diagnostic Pack Required’ pop up.

Reports without licensing aspect  that you can run in any case:

  • ‘Database Administration/Waits and Events/Waits for Past Hour’ and the others: ‘Waits for Past Hour in 1st RAC Instance’, ‘Waits for Past Hour in 2ndt RAC Instance’ and ‘Waits for Past Hour in Nth RAC Instance’ queries are against gv$waitclassmetric_history (x$kewmevmv) and gv$system_wait_class (x$kslscs) not in the restricted list. ‘Waits for Past Hour in 1st RAC Instance’ and ‘Waits for Past Hour in 2ndt RAC Instance’ are not usable because warning ‘Diagnostic Pack Required’ pop up, but this is wrong.
  • ‘Database Administration/Cursors/Cursors by session’ (v$open_cursor , gv$session)
  • ‘Database Administration/Cursors/Cursor Detail’ (v$open_cursor)
  • ‘Database Administration/Alert Log/Alert Log’ (x$dbgalertext)
  • ‘Database Administration/ Memory/SGA Statistics’ (v$sga_stat)
  • ‘Database Administration/Database Parameters/Non-Default Parameters’ (v$parameter)
  • ‘Database Administration/Database Parameters/All Parameters’ (v$parameter)
  • ‘Database Administration/Top SQL/Top SQL By Buffer Gets’, ‘Database Administration/Top SQL/Top SQL By Buffer Gets/Rows Proc’ , ‘Database Administration/Top SQL/Top SQL By CPU’, ‘Database Administration/Top SQL/Top SQL By Disk Reads’, ‘Database Administration/Top  SQL/Top  SQL By Executions’ (v$sql)
  • ‘Database Administration/Sessions/Active Sessions Count’, ‘Database Administration/Sessions/Active Sessions’, ‘Database Administration/Sessions/Background Sessions’, ‘Database Administration/Sessions/Inactives Sessions’, ‘Database Administration/Sessions/Sessions count By OS User’, ‘Database Administration/Sessions/Sessions count by Status’, ‘Database Administration/Sessions/Session CPU and Time Statistics’, ‘Database Administration/Sessions/Sessions by Module’, ‘Database Administration/Sessions/Sessions by Username’, ‘Database Administration/Sessions/System Sessions’ (v$session)
  • ‘Database Administration/Storage’: nothing to take care about, all queries are against dictionnary views accessible without any pack or options.
  • ‘Database Administration/All Tables’: same
  • ‘Database Administration/Users’: same
  • ‘Database Administration/Locks/Locks by Users’ (v$process,v$lock, v$resource, obj$, user$ et v$session)

All other reports are correct and without concern regarding llicensing. If report or query is done against option not installed or not used the query result will be empty, this will not fire any feature usage.

Other impacts

Under ‘Tools’ menu, you have by default a choice of several features. ‘Monitor SQL’ is the access to Database Real Time Monitoring feature part of Tuning pack. This is cleary stated by the warning pop-up ‘Tuning Pack Required’:
SQLDEV4 - Tools Monitor

SQLDEV4 - TP required

Don’t answer ‘Yes’ as it would change the preferences for your connection and validate the use of the Tuning Pack for it.  Click on ‘No’ or close the bow using the upper right cross.
The best is to not have this feature in the tools box.
To do that: under ‘Tools’ menu choice ‘Features’ expand ‘Database’ go down to ‘Oracle SQL Developer – Real Time Monitoring’ and uncheck it. Apply your change, restart SQL Developer as requested and it’s done.

SQLDEV4 - Manage Feature

I also wish to remove ‘Monitor Sessions …’  as it’s based on the ‘Sessions’ reports which, as we’ve seen upward, request against v$active_session_history, but i can’t see any possibility for that. Maybe “That Jeff Smith” could give us an answer, let see …

Summary

The use of SQL Developer against a Standard Edition is not firing any Enterprise Edition  features or options if:

1)  You set up correctly management pack usage for your connections in your ‘Preferences’ and you don’t answer ‘Yes’ in the warning pop up when it ask ‘Yes’ or ‘No’.

2)  You create your own “User defined Reports” for those which query against v$active_session_history. The most to black list are ‘Sessions’, ‘Blocking Locks By User’ and  ‘TOP SQL by Waits’.

3) You suppress Features not applicable to your Standard Edition using “Tools/Features” choice (but it’s all/none choice).

4) You don’t forget to set  control_mangement_pack_acess to NONE at database level.

Note:
Same remarks applied to the use of Standard Edition One (SE1) as it shared all SE features except RAC.
For next Standard Edition 2 (see MOS  note 2027072.1), we have to wait for a documentation upgrade and some testing.
Références:
Database licensing guide http://docs.oracle.com/database/121/DBLIC/toc.htm

Eric Leygonie

About Eric Leygonie

Eric Leygonie has written 51 post in this blog.

Directeur technique agence régionale chez Easyteam

Leave a Reply

%d bloggers like this: