Monday, November 23, 2015


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.

Sunday, November 8, 2015

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.

Friday, October 30, 2015

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:

Deadline is November 15.

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

Monday, September 28, 2015

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:

Sunday, September 27, 2015

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);
for l_str in (select table_name from user_tables)
pipe row(l_str.table_name);
end loop;
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.

Monday, July 27, 2015

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.

Sunday, July 5, 2015

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.