OraNA :: Oracle News Aggregator

Starting with 11g Oracle can perform block media recovery using flashback logs to get good copies of the blocks.

Flashback database is not enabled by default so first step would be to enable it. When enabled a process RVWR (Recovery Writer) copies modified blocks to flashback buffer. Later this buffer (more...)

I’ve installed Oracle Grid Infrastructure on one of my standalone test servers. This software includes Oracle ASM as volume manager and Oracle Restart for managing Oracle components. As recommended, Grid Infrastructure and RDBMS software are installed under different users.

Grid Infrastructure: 11.2.0.3.0
RDBMS: 11.2.0. (more...)

In this post I will describe how to change Oracle SID using utility DBNEWID. As I can see DBNEWID is available from 9i version but I’ve never heard about it. Till now, I have used procedure where I’m manually editing and re-creating control file.

But I think it’s much better (more...)

This will be just short post about restriction on parallel DML that I wasn't aware of. Last week we had to perform quick update of several million rows in very big table. Parallel DML has proven to be the best tool to speed up large DML operation. A DML can (more...)

Last month we had annoying problems caused by stuck Oracle process holding locks and not releasing them. Every few days one process hangs with status “ACTIVE” and “SQL*Net message from client” wait event.

In that state it holds locks and never releases them. As this is active OLTP database, new sessions arrive wanting those locked resources but they could not get them. So after some time you get lots of waiters and if you don’t react quickly maximum number of processes could be reached. Then your database refuses new connections and you're system becomes unresponsive.

To complicate a problem little (more...)

This post could be interesting for those who are running active/passive failover clusters with Oracle Clusterware used for managing resources. In this case owner of the Clusterware installation is not the same as owner of the Oracle RDBMS installation. Among others, Clusterware is assigned for managing listener resource (start,stop,check).

Till 11g version listener logs were by default written in “$ORACLE_HOME/network/log” but with 11g ADR (Automatic Diagnostic Repository) is introduced and location for listener log is changed. From 11g default location is “$ORACLE_BASE/diag/tnslsnr/<hostname>/listener_<dbname>/trace”.

I’ve noticed that when you start listener as owner of Oracle RDBMS everything is working fine. Listener log (more...)

To create database link in another schema you must execute “create database link” logged in that schema or you can use workaround noted in this post:

How to Create a Database Link in Another User’s Schema

Neil Johnson wrote nice post on that subject and I’m using his method for creating database links in another user’s schema. So check it out - link is above.


Recently I had situation when I didn’t know password of the user on the local and remote database. But I had to create database link to compile some views and procedures.

In that case my (more...)

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 11.1.0.7 on OEL 5.7


I will post shortened version of the query with fake table names and columns.

SELECT
   p.name p_name,
   r.status r_status
   FROM table_1 r,
   (          
       SELECT  ri.r_id,            
       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.

Environment:
Source DB (Oracle 11.1.0.7 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".

SELECT *
  FROM tab1 cc, 
             tab2@db2 cfp
 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 11.1.0.7 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
as
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...)

We had serious problems with Oracle TNS Listener last few days. Problems were happening on very critical system - most active OLTP database.

Every few hours/minutes, in irregular intervals, TNS listener suddenly crashes which forces our Red Hat Cluster Manager to restart instance and even switch database to another node. Business systems that have relied on this database were experiencing great difficulties.

It is worth to mention that everything worked without any problems for about 2 years without any intervention on OS or database configuration.

Environment:
OS: Red Hat Enterprise Linux ES release 4 (64bit)
DB: Oracle EE 10.2. (more...)

I've received following error while trying to perform full database export using datapump to NFS:

Export: Release 10.2.0.4.0 - Production on Tuesday, 31 May, 2011 11:27:50

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Release 10.2.0.4.0 - Production
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31641: unable to create dump file "/backup-nfs/testdb/export/exportfull.dmp"
ORA-27086: unable to lock file - already in use
Linux Error: 37: No locks available
Additional information: 10

In this case problem was lying in NFS locking deamons - on most Unix-like (more...)

We are currently in process of replacing our old database servers with new ones. During the evaluation process we’re checking various technical solutions to find the best option in terms of price/performance.

For me, real life experience is the most important when we are talking about database server performance so I’ve posted question on Oracle-L lists to get some answers about noted servers and performance.

DBA’s from Oracle-L lists helped me a lot. Thanks guys!

As replay to my question David Robillard posted answer that is worth publishing on this blog. It is nicely summarized project check list that can (more...)

Colleague reported to me that execution of his query is taking too long. After quick look it was obvious that this was ideal tunning candidate.
I will note my tuning process in this blog post.

Table and column names are changed.

DB: Oracle 10.2.0.2.0
OS: Solaris 9 64-bit

Query:

SELECT
       ROUND (
          SUM (poz_traj) / 60,2)
          mins,
       SUM (amount) iznos,
       COUNT (*) pozivi
  FROM 
       (SELECT co.con_nr
          FROM usr.ugov@remote_db co,
               usr.ugov_item@remote_db ci,
               usr.np_zah@remote_db np
         WHERE     1 = 1
               AND co.ugov_id = ci.ugov_id
               AND ci.ugov_item_id = np.ugov_item_id
               AND NP.kol_id  (more...)

You can use Oracle Grid Control for checking policy violations, to keep compliance with company security or configuration standards. Grid Control comes with pre-installed set of policies and recommendations of best practices for databases.

With having that in mind I checked Critical Policy Violations for my database and noticed this critical violations:

...
Execute Privileges on DBMS_JOB to PUBLIC 
Restricted Privilege to Execute UTL_TCP
Restricted Privilege to Execute UTL_HTTP 
Restricted Privilege to Execute UTL_SMTP 
Execute Privileges on DBMS_LOB to PUBLIC 
Execute Privileges on UTL_FILE To PUBLIC
...

It is recommended to remove excessive privileges from some users to prevent possible attacks for (more...)