Yesterday colleague reported that he’s receiving wrong/odd result from query so we decided to examine closely what's happening. Query had more then 150 lines with inline view, group by clause, several outer joins, function, decodes and cases included. So the first step was to exclude all the stuff that wasn’t important for clearer diagnosis.
Environment: Oracle EE 126.96.36.199 on OEL 5.7
I will post shortened version of the query with fake table names and columns.
FROM table_1 r,
MAX ( DECODE (ri.c_name, 'SPACE', ri. (more...)
My DBA friend Dejan from Baze Podataka
database related site wrote post
about Oracle Bug 8943287 which reminded me how dangerous bug this is. I was completely unaware about this bug till few months ago.
For this blog post I want to write about “ORA-01578: ORACLE data block corrupted”
error and recovery. It is easy to recover from such error if you have RMAN backup - you, of course, have RMAN backup? (if not, create one) Even if you don’t have RMAN backup you can perform block recovery from OS based backups (datafile copies).
But can you perform block recovery (more...)
Two weeks ago a colleague DBA asked me what would be the best way to move SYSTEM tablespace with minimum downtime. There are several ways to perform that task and I will note what would be the best way in my opinion.
Messing with SYSTEM tablespace is always tricky so my goal would be minimum risk
and minimum downtime
My testing environment is 11gR1 database on Linux 32bit OS.
After quick search on that subject I’ve noticed that DBA’s are comfortable with moving/renaming other datafiles but SYSTEM or UNDO are making problems. Some tablespaces are essential and cannot be taken (more...)
I’ve just realized that I’m never fully satisfied after tuning queries with database links. Yes, I manage to improve performance significantly but I’m always missing some answers.
Case explained in this blog post is not exception also.
Source DB (Oracle 188.8.131.52 on Solaris 64bit)
Remote DB (Oracle 10.2.0.4 on Solaris 64bit)
(Table names and values are changed )
One of the developers reported me problem with this simple query:
To get better output click "View Source".
FROM tab1 cc,
WHERE cc.c_id = cfp.b_id
AND cc.code = (more...)
Last few days we had significant load problems with one of our database servers. Server was choking under minor database load which caused applications to be non-responsive.
This is 8-core 32-bit OEL5 (2.6.18-92.el5PAE) server with 4GB RAM running two Oracle 10g (10.2.0.4) SE databases. SGA's of those databases where 1,2 GB and 700MB with pga_aggregate_target 64M and 128M.
As databases were small (under 50G) and not very active I didn't expected any performance problems.
Using vmstat, mpstat, top and sar tools I've noticed that we were having slight issues with swapping, despite that there (more...)
Last few days I’ve been playing with export and import of 750 GB database. During this whole trial and error process I’ve learned several things that were unfamiliar to me. In this blog post I want to share some of the stuff that I learnt.
I have Oracle 10.2.0.4 database on Solaris 64bit which I want to migrate to Oracle 184.108.40.206 on Linux 64bit. It is 750GB large database with mostly partitioned tables (compressed partitions).
Let's get to the subject of the post.
I won't talk much about export operation - just to mention (more...)
If you are running 10.2.0.2 in your production environment check document [ID 4604970.8] on Oracle support site.
This week colleague noticed that query, he wanted to use for some reports, returns different results than he expected. He made some additional checks using Microsof Excel and confirmed that something is wrong. He was getting incorrect results.
My first suspicion was that probably something is wrong with query or maybe logical corruption happened.
Example of the queries (I’ve changed names of the table/columns):
-- partitioned_table (date range partitioned table)
create table temp_tab_2011
select col, date_to, sum (col1) (more...)
I’ve decided to perform this test case and publish blog post after reading this topic on OTN forums - http://bit.ly/mLwaA9
How to recover lost datafile when you don’t have valid backup of your database. Your database is running in archivelog mode
and you have all necessary archive logs.
In situation when you don’t have valid backup and your datatabase is running in noarchivelog mode
- your datafile is lost forever
So take regular backups of your database and enable archivelog mode ;-)
Environment for this test:
OS: OEL 5
DB: Oracle EE 10.2.0.4
To check am (more...)