Saturday, May 16, 2015

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!

Wednesday, March 4, 2015

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? 

Monday, March 2, 2015

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.

Saturday, January 31, 2015

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!

Saturday, December 20, 2014

Why was my abstract (not) accepted?

You know, this is my blog, but I am aware of my role in our user group. The opinions here are mine; I haven't stolen them. Selecting abstracts for our conference is done by many in our board, but I wanted to clarify how I look at the task, what speakers I look for to our conference.

Thursday this week we sent out emails to 71 people who will present on our next conference that starts March 12. Another group received a more disappointing email. Some have questioned why they did not make it (again).

As a user group I think we have some community duties. These came to my mind now:

  • Be there for our members, independent from any vendor, but still cooperating with Oracle and others.
  • Enlighten and show new stuff to our members.
  • Help them finding solutions to problems they may have at work - share experiences.
  • Networking; remind DBAs and other professionals that may be alone in their day job that they are not alone in the community.
  • Also network related: There are a lot of experts inside and outside Oracle worldwide that believe in what they are doing, and are willing to help. The community needs to know about them.
  • Assist in recruiting new people into our fields of technology; more members and new colleagues. 
  • Help new speakers into giving presentations, develop the community with even more good speakers.
This post is mostly about that last bullet point.

I have my heroes among the speakers. First I discovered Tom Kyte, and when he started blogging I discovered the Oaktable Network. From that group I can mention Cary Millsap, Jonathan Lewis, Christian Antognini, Kellyn Pot'Vin-Gorman and Douglas Burns, to name a few.  They always attract a lot of people. And I really enjoy the company of these, really smart, yet levels with you every time. The community is even more than the Oaktable, there are plenty of people who just love what they are doing without carrying any reward for it.

Many of these speakers have been rewarded for their community contributions through the Oracle Ace Program.  OUGN could never have staged such a good conference without the support from Oracle through the ACE program,  we have several ACEs every year and the ACE program covers a substantial amount of expenses. So if someone from OTN is reading this: Thank you!

In this program the best (according to some metric) are rewarded with status ACE Director (ACED). Before they got that far some started as ACE, this year the new entry level is ACE Associate. Now, according to someone's logic we should go through the list of submitted abstracts and pick all the ACEDs first, right?

Not according to mine. I think we have a duty in helping new speakers to the stage. One of the most uplifting experience I have in the community is to discover (new) people that spend time on becoming good at what they are doing through hard work, lots of lab testing, and blogging. The presentations that gave me most came from people who really care about what they talk about (call it passion, if you insist), not from a very slick and polished presentation.

This is one way we in OUGN can give back to the ACE program and the rest of the community: help the not so famous or people who aspire to become an ACE to get better at presenting their stuff. 

I like to think that some of those new ACEs became an ACE because they presented at our conference. One of our Norwegian members came to me after a conference was finished and said one certain presentation made the whole conference for him. It was delivered by one I invited to speak, he was not an ACE then, but is now. It is a lot of work to become an ACE, more then one conference experience, of course, but still I think we can contribute to it.

In addition to this group of speakers who I personally care much about, here is a list of others I look for:
  • Product managers and others in Oracle who shares and cares about the community. PMs for SQL Developer, APEX, among others, are the best ambassadors for Oracle.
  • People who are in the front with new trends and technologies.
  • Famous people who attract attendees, because we need them.
  • Really hard-core nerds that may not appeal to many, but makes the conference for the ones that go to the deep-dive session.
So, if your abstract did not make it, it means among other things:
  • We were blessed with lots of submitted abstracts (over 250 this year, not counting people who said they was going to submit, but forgot about it.)
  • We have to choose from different groups of speakers.
  • Something else happened. (This is the miscellaneous bucket including faults on our side.) 
If you made it, it means that we literally want you on board and that you will succeed. You may have the best experience after your presentation from introverts who want  to know more about what you are doing. And the last night while sailing back to Oslo and you will have no presentation to worry about, you will understand why quite a few want to come back to the OUGN Spring Seminar, aka The Boat Conference :-)

By the way, the worst thing about abstract selections is not to say no to the pros (ACEDs), they can handle it, I assume, but there are some less experienced people that deserves to be speaking, but we couldn't find slots for them.

Cheers to everybody.

Monday, November 10, 2014

Use Domains in SQL Developer Data Modeler

How long is an email address? 30 characters should be enough, I thought. And I was right for about three weeks or so until one of the smartest Oracle experts in Europe tried to submit an abstract at our call for paper site. His email address had 32 chars and therefore failed already at registration. A bit embarrassing.

The data model was created in SQL Developer Data Modeler (SDDM) and I had done something right. In the data model EMAIL was defined as a domain and not simply as VARCHAR2(30) in different places in the data model.

What I needed to do was to update the properties of this domain. Before you change a domain  you may want to know where the domain has been used so you get an idea of the consequences of the change. Just go to the Browser and expand the stuff under Domains, and by right clicking on the domain you select Properties and in the window that pops up you can see where the domain has been used by clicking on Used In:

You can change the definition of the domain in one place, of course, under Tools -> Domain Administration:

Simply select the domain, you have to click on the Modify button before you can update the Domain Properties on the right. 

The fix in the schema was to perform a few ALTER TABLE statements on the involved tables followed by some statements to compile objects that became INVALID afterwards. 

Friday, October 24, 2014

Restore XE database from an AWS instance

Of course there is nothing special about restoring a database from the cloud on your own down-on-earth server, but it helps making this post cool and cloudy.

When I deployed XE to an AWS instance I made a small script to backup the database once pr day (scheduled in crontab), and another to fetch the backupset to my server here on earth. Backup script looks like this:

#!/bin/sh

ORACLE_SID=XE
ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe
PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID ORACLE_HOME PATH

rman target / << EOF > $HOME/script/rman.$$
backup database plus archivelog delete input;
delete noprompt obsolete;
list backup of database summary;
EOF

By default the XE is installed without archiving so you need to change that when you install your production XE database. I also turned on auto backup of the controlfile. The RMAN configuration for the XE database looks like this:

CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/xe/dbs/snapcf_XE.f'; # default

It is mostly default values.

A script that runs on my own box simply does a recursive secure copy (scp):

scp -r -i ssh_key.pem ec2-user@42.42.42.42:/u01/app/oracle/fast_recovery_area/XE/backupset/`date +%Y_%m_%d` /home/alone/RMAN/backupset
scp -r -i ssh_key.pem ec2-user@42.42.42.42:/u01/app/oracle/fast_recovery_area/XE/backupset/`date +%Y_%m_%d` /home/alone/RMAN/autobackup

This fetches today's backup and stores it locally.

I installed XE on my own box and executed the following in RMAN to restore the database. (If the instance is up, take it down with shutdown abort first):

rman target /
startup nomount
restore controlfile from '/home/alone/RMAN/autobackup/2014_10_21/o1_mf_s_861554053_b4dvq5pl_.bkp';
alter database mount;
catalog start with '/home/alone/RMAN/autbackup/2014_10_21';
restore database;
recover database;
alter database open resetlogs;

That's it. 

But don't trust me on this procedure without trying it yourself. Remember a DBA can make mistakes, except when it comes to backup AND recovery.