JRuby in SQL Developer Data Modeler on Mac

In SQL Developer Data Modeler you have Libraries under the meny Tools -> Design Rules and Transformations. But when you select that one you may have seen this error message:

Notice the second line with Jruby lib is red. This is because the JRuby is not installed (or cannot be found) on your machine.

I found the solution to this in Heli's book on SQL Developer Data Modeler, but thought I could add the easy way to solve this on your Mac. You need to download an extension to your JDK installation. A jar-file can be downloaded from http://jruby.org/download (There are two main versions, I decided to go for the the 1.X one). Look for the jar-file, currently I downloaded jruby-complete-1.7.23.jar.

You need to figure out where to put this jar file. In SQL Developer Data Modeler, check your java.library.path setting by going to Help -> About. Then click on the Properties tab. There are a lot of them, so in stead of scrolling down, start writing in the search field "java.l" as shown:

In my case, one of the directories in the search path is /Users/oisene/Library/Java/Extensions. It did not exist on my system, so I created it and copied the downloaded Jruby jar file into this directory. After a restart of SQL Developer Data Modeler you can go to Tools -> Design Rules and Transformations -> Libraries to verify that you no have the error message and the line with Jruby lib is black.

ORA-01105

This post is meant to be found by people googling the error message ORA-01105 and ORA-19808 on RAC. The following error messages may be seen after starting an instance:

ORA-01105: mount is incompatible with mounts by other instances
ORA-19808: recovery destination parameter mismatch

When this happened to me I ran this command on both instances:

show parameter recovery

it showed there was a mismatch for the parameter db_recovery_file_dest_size. Probably I forgot to add "sid=*" to the alter system command one time when I increased the limit. Since one instance was up I could update the parameter for both instances with:

alter system set db_recovery_file_dest_size=700G sid='*';

The instance that was started must be shut down and started again.

This means that this parameter has to be the same across the instances during startup, but Oracle allows you to set them different with alter system.

As shown here you can correct parameters in spfile for an instance that won't start from a running instance. This is a favourite feature, and whenever I have to change memory parameters that requires restart of the instance, I keep one instance running so I can correct the settings from there if needed.

My take away from OOW15

A big conference like Oracle Open World is an excellent opportunity to learn more. But with all the books, blog posts, and on-demand learning on Internet, I really did not have to go to OOW this year to keep myself busy learning. Of course, I learned a lot, but for me, the biggest reward is the inspiration and ideas for future projects, as well as meeting friends in the community including Oracle employees who take part in it.

Regarding inspiration I think that the attention given to SQL and PL/SQL is awesome. This is much more than the code itself. It is about the ability to do more and more complex analysis for each new version of the database. You can do it with minimal amount of code, code that executes very close to the data.

It is also about Application Express that is a quick and responsive web interface for your data; web pages generated in the database with the ability to manipulate and report on the data. I am a DBA that keeps telling other DBAs that if they can choose APEX for a project they should do so. Anything else will be more complex with more layers (even with Oracle REST Data Service as a proxy).  It is built on technology the DBA understands and can control.

In the era of Big Data and Cloud computing I think the most fascinating stuff is data mining. It has been around for years, but thanks to an renewed focus, data mining algorithms are cool again. Oracle Advanced Analytics is more than complex algorithms. You need to explore the data, and the more you know SQL in general, and analytical functions in particular, the better you are prepared for such a project.

I'm getting the impression that beside the Oracle database, preferably running on Exadata, I need nothing more than perhaps a couple of tiny applications servers in front, and network, of course.

OUGN16 - Call for paper

The biggest conference for Oracle users in the Nordics is not far away. Be there as a speaker and attendee. Sure you have a war story or another experience worth sharing. Last year OUGN received 250 abstracts, please, do it again!

Follow this link to submit your abstracts:

http://c4p.ougn.no/

Deadline is November 15.

See you on the ship (which is a boat with other boats on it, according to Millsap)!

Who is your hero in the Oracle community?

I have my mental list of people in the Oracle community I look up to. They are people who help through social networks, emails, presentations, books, blog posts, or in a friendly conversation. Now, Oracle has recognised that there are many such individuals in the community, people that know a lot and spend much time sharing it with others.

In fact they have asked us to vote on our developer heroes, and they will award them during a big celebration at Oracle Open World. Sure you have met one SQL guru, or a PL/SQL programmer, or someone really concerned about database design? Since I am a DBA I think that we should support our database developers since they are doing their part to make sure that the database, and database application that we will support later is as good as possible.

It starts with a good design, then hopefully as much as possible of the hard work will be done in SQL or PL/SQL close to the data, and finally presented beautifully in a web application done in APEX, possibly delivered through  Oracle REST Data Services to make it extra secure.

What are the alternatives? A bunch of coders that starts out without any planning or previous knowledge of the database (because it is just a persistent storage they don't want to relate to).  They also want to do as much as possible in their own app far away, so they happily offload half the database to do so, and blame it on the network if it does not perform. The end result is a a mess that does not perform or scale, but you get to manage it from release until eternity.

Here is what you need to give something back before it is too late:

https://community.oracle.com/community/database/awards

Function to let user B see all tables of user A

In case you do not want to grant a user access to data dictionary tables like DBA_TABLES, but will let user B see the list of all tables belonging to user A, you can work around it with a pipelined function in schema A:

create type str_set as table of varchar2(30);
/

create or replace function a_tables return str_set pipelined is
l_str varchar2(30);
begin
for l_str in (select table_name from user_tables)
loop
pipe row(l_str.table_name);
end loop;
return;
end;
/
grant execute on a_tables to B;

Then user B can see the list of A's table with:

select * from table(a.a_tables);

Trying to solve this with a view in schema A that selects on USER_TABLES does not work, but prove me wrong, please.

Gather statistics on spatial index

When you run dbms_stats.gather_table_stats on tables with spatial indexes or dbms_stats.gather_index_stats directly on the spatial index the routine may return without an error even if no statistics gathering took place. You can verify this by looking at the LAST_ANALYZED column in DBA_INDEXES:

select owner,index_name,last_analyzed
from dba_indexes 
where index_type='DOMAIN';

Another way to verify if statistics gathering for these indexes took place is to look for tables with names that start with MDXT:

select owner,table_name 
from dba_tables
where table_name like 'MDXT_%';

The user that owns these indexes must be granted CREATE TABLE directly and not through a role. 

You can find some details about these tables in Doc ID 1610877.1 on My Oracle Support.  In case you have many tables that starts with MDXT you can see what are actually needed by running this:

select sdo_index_owner, index_name, sdo_index_table 
from mdsys.all_sdo_index_info;

MDXT-tables whose number does not appear among the tables listed in sdo_index_table (name starts with MDRT) can be dropped according to the note at MOS.

Orphan Processes in the database

When you need to find the OS-process on the database server for an oracle session (dedicated server) you can join v$session with v$process:

select p.spid
from v$session s join v$process p on(s.paddr=p.addr)
where s.sid=42;

But if you kill a session with 'alter system kill session ...'  the link between these views are broken because the value in v$session.addr changes. In order to look for these orphan processes run this query:

select spid, program from v$process 
    where program!= 'PSEUDO' 
    and addr not in (select paddr from v$session)
    and addr not in (select paddr from v$bgprocess)
    and addr not in (select paddr from v$shared_server);

You may check with OS tools like ps on Linux to see that these are indeed dead processes or with strace to see what they are doing and eventually kill them.

ORA-00942

When I had my first Oracle DBA course some years before the start of this millennium I made a special mental note about a very common error message, ORA-00942, "table or view does not exist".

In most programming languages there are a similar message for a common situation; when you refer to an object that does not exist, quite often because you wrote the name wrong. What I found a bit remarkable when I heard about it first time was that ORA-942 may mean two things, either that the table or view does indeed not exist, or you do not have access to it. An example of the latter is when some user has created a table in her schema, and you try to query it, but you have not been granted a privilege to do so. Instead of you receiving some error message like "access denied", Oracle responds with the same error message; "table or view does not exist", even if it does exist. It may have added "for you" to make it more correct. I think there is a good reason why this is so, and I think it has to do with optimisation.

If you enable sql trace, you can see Oracle runs queries against obj$ and objauth$.

select obj#,type#,ctime,mtime,stime, status, dataobj#, flags, oid$, spare1, spare2 
from obj$ 
where owner#=:1 and name=:2 and namespace=:3 
and remoteowner is null 
and linkname is null and subname is null

select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0),2))
from objauth$ 
where obj#=:1 
group by grantee#,privilege#,nvl(col#,0) 
order by grantee#

The first checks for the object's existence, the second looks for privileges granted on the object. By the way, the results from these queries may be cached so you won't always see these in the trace file. Exactly what is going on behind the scene here is unknown to me, and I also think that the implementation of this has changed during the years with different versions of the database. But if you look at the second query, that fact that no row is returned may come from either the fact that no privileges have been given, or that the table does not exist. So, instead of going an extra round in the dictionary to see if the table exists since no privileges exist on it, Oracle simply returns the same error message "table or view does not exist".

It is probably a good security practise to not inform unprivileged users about the existence of objects they have no access to, but I thought it was a smart optimisation by Oracle back then, because I was used to see error messages like "access denied". The way they have done this saves extra work. By know I think I have lost the attention of everyone except my best oracle nerd friends, so I thought I could sneak in a confession here at the end. Some years ago I started to practise ORA-942 in my own life. You know in those situations where people suspect you know some secret and start asking away about stuff you are not allowed to pass on? Instead of me having the burden of creating excuses or explaining why I can't tell, I simply answer "I don't know". That saves me a lot of extra work and I don't consider it a worse practice than mining other people's brain.

Have a nice weekend!

Search and history in SQL Developer

Should be obvious, but I just discovered that it is much easier to change preferences if you search for it in the search field instead of wandering around in the Preferences GUI looking for the place where you can set it:

And it is pretty fast, try to look for the place where you choose the OCI client, just write OCI in the field. And similarly, if you think you wrote some smart code a few days ago, just search for it in the SQL history:

If you can't find it, it is because you have forgotten to increase Days to keep history from 7 which seems to be default. Go back to preferences and crank it up. 30 days, why not? 

DBMS_INDEX_UTL

Here the other day I came across this package in a PL/SQL procedure written by someone else. From the name I reckoned it was a standard package from Oracle, but I had never seen it before. It is not mentioned in the manual Database PL/SQL Packages and Types Reference, and I could not find much about it at My Oracle Support either. Anyway, with SQL Developer you'll get what you need by hitting Shift-F4 (with the cursor at the name of the package). The API is pretty good documented in the comments. The package is used to rebuild indexes, either for a named table, a named schema, or a list of indexes plus some stuff I didn't bother to look into.

Now, there may be a reason why this is not well documented and better known.

To a junior DBA

At UKOUG Tech conference in Liverpool last year I had an interesting talk with a DBA that had two years of experience. Being 23 years the DBA naturally stood out from the crowd. I talked about my thoughts on what is important for a DBA, and since I spend way too much on user group activities and conferences I argued that going to such events are useful for a DBA, and continued with other ramblings about what I think is important for a DBA to do. I hope I stopped soon enough, but stop talking and start listening is an art. Anyway, I decided to write down a list of recommendations for a new DBA. This is what I think is important.

Backup and recovery stands out. The database is your responsibility, that is why you are hired as a DBA. In the database, which in earlier times also was called a databank, the data is the money.  If you can't get the database back after a disaster they will blame you and no other. In fact if you can imagine one reason why you in some situation will not be able to recover the database you should mail it to your manager now. The concept of backup and recovery should be easy to grasp, but realities are that many different scenarios exist and how to deal with them depends on the situation. There are plenty of stuff written on this from masters of Recovery Manager (RMAN) to people more dedicated to risk management. This subject is more than just getting the database back running, it is also about keeping the system available, guard against data loss and testing systems for faults.

• Have a fire drill once in a while to test your routines and skills.
• Get some clear policies in place like, how much data are you allowed to lose, and how much time (downtime) do you have to recover your database. 

The costs and implementation will depend on  this. If you cannot lose any data and management starts talking about 99.999% uptime a simple backup is not enough. "How much data can you lose?" Is a phrase that can freak people out, but if you can't lose data, is your system  and routines really prepared for that?

Tuning and Optimisation is by many thought of as your problem and your problem only. Reality is that when a system is not performing it is usually not the DBA's fault. Obviously, a badly configured database will not work well, but the Oracle database performs remarkably well with default configurations.  Your database depends on the server it is running on, the network between the database and connected clients, and so on. But most frequently you will discover this: the reason for bad performance is bad design.

Design as in "invented by a developer who is a single user on his own PC with lots of CPU and memory, but little data and load to reveal bad algorithms". Tuning is a word looked down on by some in the community since it came from an era where quick fixes and esoteric queries would reveal problems in the database; with the right secret revealed to you, you could do some magic and have everything run perfectly. Still some people are tempted by black magic, so be critical to what Google brings you.

Optimisation on the other hand is taking what you have, see where the time is wasted, and take away as much as you can (or want) by replacing one or a few parts of the job by something that runs better. Often a redesign is necessary, or if you are lucky, fiddling with an index may do the job.

My best advice is to find a way to identify where time is wasted and zooming in on that. This means profiling in some way and work from there. By having some methods ready to detect waste or a time sink, you can stop spending time improving on what is already working reasonably well. In other words, stop the parameter tweaking (nobody changes the db_block_size these days), take one step back and ask, what is going on here. What you learned in computer science at the university may come very useful here. To put your mind on the right track I cannot think of a better place to start than to read through the papers at Method R, especially Thinking Clearly About Performance.

Get involved in a user group and go to conferences, meetups or whatever is easy for you. Many companies have many developers or IT-workers, but only one or two DBAs. In the community there are people who love to talk about their work, they will share experience with you, and tell you what is not in the manuals. If your manager won't send you to a conference, here are some arguments you may try:

• One course covers one subject, but a conference covers many, during one intensive conference you can get inputs and learning in many fields.
• Problems you may have at work can be discussed by others at the conference. Chances are that what you are going through is interesting and valuable input to others and you are likely not the first one to go through this.
• Make friends and discover that you are not alone. Getting friends and contacts outside your pool is possibly the best part of it. Meeting people IRL and being in touch on many channels is priceless to me.
• Many conferences are run by the community, meaning that a demand for a profit margin is lower at conferences than at a course delivered by a company. It translates to more learning for the money than at most other places.

Find a social network that works for you and become visible. Twitter works well for me, I meet likeminded people there and discover other things we have in common besides Oracle tech. Most of my friends I have now come from the Twitter community.

Take notes and start a blog Starting as a DBA today must be different from how it was 10-20 years ago. As you go, take notes and post it on your own blog. Even mistakes; DBAs are busy, if other can learn from your mistakes it is a valuable contribution. The problems you have now may be more relevant for another junior DBA than a beginner's post written many years ago. And, you will be surprised how useful your procedures will be later when you have to go back and perform a task you haven't done for a while.

If you are a senior DBA reading this, maybe you should blog about what you learned and want to pass on? I think many have had an experience that stood out, something that put their mind on the right track. Blog about it!