There are a lot of ancient things that prove to be still valuable today.
Recently I came across something, which I thought I could or should share here.Your tnsnames.ora file, centrally managed in a distributed environment.

Imagine you have an environment with a reasonably big bunch of PC’s. Wether you are running Linux or Windows, or if you are running some Terminal Server Forrest, where deploying, packaging and launching applications cost an arm and a leg. To keep consistency across the environment, shortcuts are not an option.
I have seen the most exotic solutions, distributing a tnsnames.ora file. Pushing versions of tnsnames.ora out to workstations, have login-procedures check and copy files… Basically every trick in the book will, at one time, get you in the situation where you have a client, connecting to a database, containing something other than the end user expects… With the appropriate consequences…
It is my firm believe that the best way still is not to distribute the file.

The coolest thing (therefore) would be to have something (obviously extremely simple), containing all your database connection aliases.
Of course you can than add each and any every facility to maintain and update this file. Be it version control or automatic deployment. Basically this one copy of your file is the source of all truth, which of course adds some importance to the file itself.

These options give you a very good set of opportunities to organize your redirection-setup the way you need it.
To me, having this centralized tnsnames-setup, brings advantages in connection troubleshooting as there are no occurances of file-discrepancies.ifile = works and you get connected… or it doesn’t, no chance of missing that last update.

Last week we were struck by an issue, which turned out to be a bite from a bug!
SYSAUX table-space had quickly filled up to the “my data-file is full”-limit, which in the end was fixed by adding a data-file.

Strange thing though, that for a very small footprint database, we now have a very big SYSAUX table-space.

Some investigation brought me to the Unified Auditing being standard active in database 12c (you can read up on that background with my friend Ann Sjökvisthere).
We are faced though with a different (and possibly a little more obscure) Bug 20077418 – RECLAIMING THE SECUREFILE LOB SEGEMENT IN 12.1 Standard Edition.
What this bug boils down to is the following:
There is a lot of audit data recorded by default, the ORA_SECURECONFIG profile is running out of the box. I haven’t taken the time to figure exactly out what is written, where and how, but I know it involves a LOB segment (SYS_LOB0000091833C00014$ by SYSAUD) which is, in our case in comparison to the total database size, HUGHE!! The management of this audit data, usually driven by DBMS_AUDIT_MGMT, has absolutely no effect on this segment (at least not on shrinking it).

Searching for the mentioned bug you just find to EE bugs (18109788 & 22272580) but they at least they give _some_ clues… The actual bug is undisclosed and in status 11 (being worked on).
In the end it means that auditing is fine, even in SE, but, for the moment, restrain yourself… The data you gather cannot be managed (yet). And for the rest:

When installing a new WebLogic Domain for any a-specific Oracle (Fusion) Middleware application or any other implementation requiring a WebLogic domain like ORDS for instance, a new ‘home’ is created under [MW-home]/user_domains/. [MW-Home] translates, for instance, to /u01/oracle/product/Middleware.

To start your brand-new domain, or perhaps and rather, to automate the startup of your domain, you would use the supplied [MW-home]/user_domains/[DomainName]/startWebLogic.sh command-file.
This file will start the Weblogic domain (the Admin Server) and the deployed components. After this start, you will be able to follow through with the administration over the web-console. Typically its URL is: http://[ServerName]:[PortNumber]/console.

One nasty thing you can run into, is that starting the server can require you to enter username and password during the run of [MW-home]/user_domains/[DomainName]/startWebLogic.sh. Of course this is rather annoying because it requires interaction which is not good for auto-start. Regular input-tooling you can wrap around this command-file, for example with input redirection, would require you to save your username / password combination in plain text. That is certainly never a good idea!!

Luckily there is a trick to enable your WebLogic domain to start without this interaction. And it also makes sure that username & password are not stored in plain text. Actually it is quite easy to get this facility in place.

This is how:

Go to [MW-home]/user_domains/[DomainName]/Servers/AdminServer/security and create a plain text file called boot.properties.

Basically, this is now a plain-text recording of the username and password on the server, which seems quite scary.

Good thing though, is that when you have successfully run [MW-home]/user_domains/[DomainName]/startWebLogic.sh command file, which will now continuously run through, username and password will be encrypted:

You will now be able to call [MW-home]/user_domains/[DomainName]/startWebLogic.sh from your startup-script without having to worry about the need to interactively entering username / password or have to worry about plain text storage of these to artifacts.

Of course we all know GUI stands for Graphical User Interface, just as CLI stands for Command Line Interface, right!
Or, rather, a GUI is this nice, flashy screen where you can easily roam with your mouse, comparable to a multiple choice quiz, where the right answer is there for the picking.
A CLI on the other hand is this dark, mysterious blinking cursor… Nothing happens unless you know more or less what you are doing. Comparable to an open questions quiz.

Sparked by a recent Twitter discussion, I decided I should probably write the umpth blog post about this to make my contribution to this lasting dispute.

Disclaimer:This post discusses GUI in relation to system administration, not necessarily in relation to data-entry or data manipulation applications that are used in front offices all over the world. I guess CLI has no place in a world like that…

Why GUI sucks?
I have done my fair share of installing, scripting, ad-hoc fiddling, testing and trying. And, I have found myself in the situation where I worked with younger computer geeks or even in situations where nobody had the time to figure anything out – stuff just had to be made to work.

Probably in the few lines above, we could already have the basics for this discussion!

But, why then does GUI suck?

GIU’s suck because they are limiting, labor (or rather RSI) intense and require you, the operator, to be there, physically clicking away on your computer.

Limiting
They are limiting, or at least most of the time they are, because it is often quite hard to get a visual representation of each and every function of a device / program / system etc. If you consider, for instance, a networking device and then try to imagine having to create a GUI that lets the operator configure and define each and every parameter of a specific VLAN or VPN. And then also bear in mind that the GUI has to stay crisp, clean and intuitive.
For this reason, I have seen many vendors who have created a GUI for basic setup only, relying on the professionals to find their way in the CLI. They GUI can then stay intuitive enough to at least get the basics done.

GUI’s that aim not to be limiting, of which there also are a few out there too, need to sacrifice a lot of the things that a good GUI should stand for:

Short click paths (3 clicks from anywhere to get where you want to be)

Intuitive (don’t have to guess or read a manual to use a GUI)

So, what you end up with, then, is a maze of riddles, where you can easily spend a good day setting up some new functionality. Somehow I believe this is not what the designers had set out for nor is it a valid solution for most tasks at hand.

Labor intense
I personally find GUI’s often, quite labor intense. Not just for the absence of the ability to automate tasks, though. Especially if there is a lot of specific configuration that needs to be done, you often end up left and right clicking until your hands start hurting.
And, in the end, you always end up with the eerie feeling that you missed out on that one specific setting that would really put the icing on your configuration.

Operator presence
Last, but not least… For a GUI to work, you need to be at your workstation. Period.
Anybody who has ever worked on automated testing of applications that rely on a GUI, knows about the hideous crime of having to script test-cases, either working with hidden button-labels, screen coordinates, etc. Where these scripts fail every other day because a developer moved a window to a better spot or used a new button-label. You end up coding your application just to make it testable.
No, GUI requires operator presence, making it useless for automation or scaling.

The bliss of CLI
Okay, middle Ages… or Stone Age…
Nothing really fancy, just a black (or, if you are feeling frivoled, you may choose some nice color) square on your screen with a blinking underscore – most often. And then you say; GUI sucks?

One of the challenges in this hyper fast moving world full of smart phones, tablet PC’s and what have you, loaded with intuitive and fast apps, is to realize that actually “hard core IT” is hard core.
You need to learn your stuff first, know what you do and know about the consequences of choices you make. You will have to learn to be able to walk the walk and to talk the talk. Once you have mastered that, this blinking underscore is no longer a roadblock but a invitation! Just like after mastering a foreign language, you will know what to say and do to open up the potential at your fingertips.

And now, reality
Of course, the above is ranting is just one side of the story.
It is even just one side of the story in hard core IT!

As already stated above, sometimes there is no time to really dive into stuff and get to know the tools you need to get to work for you. I am pretty sure we all have been in a place where we needed to get a project done or some functionality realized, where we just did not have the right devices.

What are your options at such a moment?
Get a hardcore IT specialist who does “talk the talk”?
Probably it will not be cheap and probably it will be a very thorough configuration, but just not exactly as you need it to be… Though still a valid option, even in a number of cases it’s a no-go.
This… is where a good GUI comes in handy.
It will allow you, yourself, to organize that which needs organizing in an orderly fashion. Okay, the GUI will have to be accurate and well thought through, but I that goes for all interfacing, that is also true for the CLI.

Seeing this story unfold… I guess I still think GUI sucks. (sorry!)
But GUI has a place, a very well earned place in a super-fast and highly demanding world. Still I am convinced that if you are working in a highly professional environment, having to do intricate stuff on ever live environments, I would say a good script for a CLI is the only way you can create some assurance that whatever change you need to execute will actually have a predictable result.

And putting in the effort of learning how to use any CLI? Well, I guess that’s why it is called “professional IT”.

This blog post is inspired on work I have been doing on Standard Edition databases and the returning confusion about what is and what is not part of Standard Edition.

DBA_FEATURE_USAGE_STATISTICS is a tool in determining license usage for the Oracle database. It is good to understand the implications of each entry, know what is happening in your database and thus be able to have a substantial conversation about the usage of your license, being SE, SEO, SE2 or EE!

This list is the full list of DBA_FEATURE_USAGE_STATISTICS and I have found no source where there is a mapping of these features to database editions. As it is a lot of tedious work I call upon the community to help complete the list and make it as accurate as can be. So, if you have news, improvements, other bits of information, please send it to me and I will make sure it gets added!

WARNING: Still… with all the work that goes into these answers, it is not the law, it is a very serious interpretation of facts which will pay a part in helping you make the right decision when it comes to database licensing.

Feature

Standard Edition

Active Data Guard – Real-Time Query on Physical Standby

NO !

ADDM

NO !

Advanced Replication

NO !

Application Express

YES

ASO native encryption and checksumming

NO – EE option !

Audit Options

NO !

Automatic Maintenance – Optimizer Statistics Gathering

YES

Automatic Maintenance – Space Advisor

YES

Automatic Maintenance – SQL Tuning Advisor

NO !

Automatic Memory Tuning

Automatic Segment Space Management (system)

YES

Automatic Segment Space Management (user)

Automatic SGA Tuning

YES

Automatic SQL Execution Memory

YES

Automatic SQL Tuning Advisor

NO !

Automatic Storage Management

Automatic Undo Management

Automatic Workload Repository

AWR Baseline

NO !

AWR Baseline Template

NO !

AWR Report

NO !

Backup BASIC Compression

Backup BZIP2 Compression

Backup Encryption

Backup HIGH Compression

Backup LOW Compression

Backup MEDIUM Compression

Backup Rollforward

Backup ZLIB Compression

Baseline Adaptive Thresholds

Baseline Static Computations

Bigfile Tablespace

Block Media Recovery

NO !

Change Data Capture

NO !

Change-Aware Incremental Backup

Character Semantics

Character Set

Client Identifier

Clusterwide Global Transactions

Compression Advisor

Crossedition Triggers

CSSCAN

Data Guard

NO !

Data Mining

NO – EE option !

Data Recovery Advisor

Database Migration Assistant for Unicode

Database Replay: Workload Capture

NO ! 1

Database Replay: Workload Replay

NO ! 1

DBMS_STATS Incremental Maintenance

Deferred Open Read Only

Deferred Segment Creation

NO !

Direct NFS

Dynamic SGA

Editioning Views

Editions

EM Database Control

EM Grid Control

EM Performance Page

Encrypted Tablespaces

Exadata

Extensibility

File Mapping

Flashback Data Archive

NO ! 2

Flashback Database

NO !

GoldenGate

NO – EE option ! 3

HeapCompression

Hybrid Columnar Compression

NO !

Instance Caging

NO !

Internode Parallel Execution

Job Scheduler

Label Security

NO – EE option !

LOB

Locally Managed Tablespaces (system)

YES

Locally Managed Tablespaces (user)

Locator

YES

Logfile Multiplexing

Long-term Archival Backup

Materialized Views (User)

NO !

Messaging Gateway

NO !

MTTR Advisor

Multi Section Backup

Multiple Block Sizes

Object

OLAP – Analytic Workspaces

NO – EE option !

OLAP – Cubes

NO – EE option !

Oracle Database Vault

NO – EE option !

Oracle Java Virtual Machine (system)

YES

Oracle Java Virtual Machine (user)

Oracle Managed Files

Oracle Multimedia

Oracle Multimedia DICOM

Oracle Secure Backup

Oracle Text

Oracle Utility Datapump (Export)

Oracle Utility Datapump (Import)

Oracle Utility External Table

Oracle Utility Metadata API

Oracle Utility SQL Loader (Direct Path Load)

Parallel SQL DDL Execution

NO !

Parallel SQL DML Execution

NO !

Parallel SQL Query Execution

NO !

Partitioning (system)

YES

Partitioning (user)

NO – EE option !

PL/SQL Native Compilation

Quality of Service Management

NO !

Read Only Tablespace

Real Application Clusters (RAC)

YES 4

Real-Time SQL Monitoring

Recovery Area

Recovery Manager (RMAN)

YES

Resource Manager

NO !

Restore Point

Result Cache

NO !

RMAN – Disk Backup

RMAN – Tape Backup

Rules Manager

SecureFile Compression (system)

YES

SecureFile Compression (user)

SecureFile Deduplication (system)

YES

SecureFile Deduplication (user)

SecureFile Encryption (system)

YES

SecureFile Encryption (user)

SecureFiles (system)

YES

SecureFiles (user)

Segment Advisor (user)

Segment Shrink

Semantics/RDF

NO !

Server Flash Cache

Server Parameter File

Services

Shared Server

Spatial

NO – EE option !

SQL Access Advisor

SQL Monitoring and Tuning pages

NO – EE option !

SQL Performance Analyzer

NO !

SQL Plan Management

NO !

SQL Profile

SQL Repair Advisor

SQL Tuning Advisor

SQL Tuning Set (system)

YES

SQL Tuning Set (user)

SQL Workload Manager

Streams (system)

YES 5

Streams (user)

Transparent Data Encryption

Transparent Gateway

YES – option

Transportable Tablespace

NO ! 6

Tune MView

Undo Advisor

Very Large Memory

Virtual Private Database (VPD)

NO ! 7

Workspace Manager

Unless used for upgrade to Enterprise Edition.

Unless used without history table optimization.

Goldengate can also be used with Standard Edition, it is a separate product.

RAC on Enterprise Edition is an option.

No capture from redo.

Import transportable tablespaces in all editions.

Policies on XDB$ACL$xd_sp in sys.v_$vpd_policy are internal ( “out of the box”) policies that are used by XDB to control the access to certain internal tables. All the logic is implemented in the xdb.DBMS_XDBZ package and there is no way one can control / influence the way this is working.

It was the second half of 2011 when the broader introduction of Standard Edition database security tooling was introduced in The Benelux. Dbvisit Standby was the tool and protecting data in Standard Edition databases was the deal.

I remember the first meetings vividly! The Standard Edition database? Many people had not heard of this edition or, more frighteningly, the ones that knew about it, ignored it. Standard Edition was not something to be taken seriously, let alone used to run your production system on.
Still this time marked the start of the silent (r)evolution and the rising of the Standard Edition.

Since those days many things have changed.
With the continued attention and drive for promoting Oracle Standard Edition (SE) database, the visibility of this edition has flourished.
Obviously the economic hardship of the last years have encouraged companies to review their IT budgets. The investment friendly character of SE have helped its growth, especially in such times.
During the second half of 2013 the first broader initiatives around SE started to become visible. One of the highlights of the SE uprise was the world premiere of the Standard Edition Round Table during Harmony 2014 in Helsinki Finland organized by Ann Sjökvist, Philippe Fierens and myself, the same people that lead the Standard Edition community today.

With the increased attention, worries also came. The Standard Edition and Standard Edition One editions had no cap on the number of cores per processor. This means that modern servers, running SE, equipped with huge amounts of processor cores, bring tremendous processing power at extremely low cost.
Signs of change became visible with the postponed release of Oracle database patch release 12.1.0.2.0 for SE.And now, in 2015, Standard Edition is a tool to distinguish yourself with. Many IT consultancy firms advertise their SE-expertise and have increased visibility in this respect. Many new initiatives have been fired up to help give Standard Edition the punch it needs for the even more serious jobs. News on Standard Edition is spread by a range of blog posts (like this one) as a result and UKOUG_Tech15 is even hosting a Standard Edition track! We have come a long way!!

And finally, with the release of Oracle Standard Edition Two, on the first of September 2015, the future of Oracle Standard Edition has been secured. The release of version 12.1.0.2.0 marks a new era for this Smart Edition.
Standard Edition Two retains many of the important advantages of Standard Edition and Standard Edition One while capping the processor core factor at a very usable level.

Yes, Oracle Standard Edition is a solid product in the Oracle stack and is still capable to help Oracle offer the most complete software operations stack, especially due to the development and deployment capabilities of APEX.
An unbeatable, endlessly scalable and super affordable solution on the market today.
We have come a long way to witness the rising of the Standard Edition!

dbms_redefinition actually is a nifty, but powerful little toolkit that let’s you change table-definitions without actually locking the table in such a manner that it would prevent regular operations from being interrupted.

One thing I noticed, and which I want to share here has lots to do with the house keeping that is automatically done by dbms_redefinition. Actually it talks about some of the bits it didn’t brush up after itself.

dbms_redefinition works using triggers and materialized views to help switch from your current active production table, via a so-called interim table, back to your shiny new, redefined production table. You can follow this beautifully by querying the dba_segments view along the way.
For this it obviously creates this materialized view and the other required components and it removes them after you finish your redefinition-trip. After all that is done, you can just remove your interim table and be done with it.

At least, that is what happened in most of the cases and is what you would expect!

Though, in some cases… it proved impossible to drop the interim table. To me this was somewhat scary… did the redefinition not finish, or did it not finish correctly?

What happened?

There was this table that I redefined. It had referential integrity constraints (aka. foreign key constraints) pointing towards it. Of course dbms_redefinition neatly created version of these to the interim table to be sure nothing went wrong.

When finishing redefinition (with dbms_redefinition.finish_redef_table) most of the interim bits and pieces are cleared away and you just have to drop your interim table manually (okay, we can discuss if this actually would / could / should be automated, but let’s leave that).

But… when you are then manually dropping this interim table (in a busy production system, I tend to want to be careful and just issue ‘drop table int_<tablename>‘. That does not work. dbms_redefinition “forgets” to remove these referential integrity constraints in the other tables (which are neatly names tmp$$_<constraintname>).
This than means either issue ‘drop table int_<tablename> cascade constraints‘, which is more then the basic ‘drop table‘ or find these constraints and remove them manually first: