SORT BASED ON SPECIFIC INDEX

Today I have been reported an incidence indicating a Siebel report does not return the output in a specific order, in this case sorted by date. To know which is the statement that is behind the report, I had to  trace the user, several methods are possible, from seeing the current SQL executing by the user  to put a 10046 trace.

In this case it is a select that has the following form;

In this case the problem is clear, the SELECT statement does not perform ordination using the “order by” clause. Usually in these cases the ordering is based on the projection (COLUMN_1, COLUMN_2, … COLUMN_n) or in an index which is what solves the select. In this case the ordering is based on an index. The problem is that the execution plan has changed so it no longer works ordination.

The solution is to change the SQL, including the “order by” clause or maybe change the execution plan using a outline.

Antonio NAVARRO

RMAN-20052 error run the resync catalog command

Today I have a problem  when running a backup on a database, this database is a RAC. The error occurs when executing a RESYNC CATALOG command. The output from rman is the following

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of resync command on default channel at 07/15/2013 16:30:39
RMAN-20052: invalid datafile create SCN

Investigating the error, it seems that there is a difference between what we have in the controlfile and what is in the catalog. Looking references at MOS (formerly known as Metalink). I see a bug where we hit, This issue is due to bug 13000553

The solution is to apply the patch that solves it. We have two workarounds;

•  Make backup using controlfile.
•  Unregister and register databasethe in catalog may solve this issue.

Of course, the problem is in these two alternatives is that you lose information, such as scripts, historical (the CF has  CONTROL_FILE_RECORD_KEEP_TIME = 30) with “keep forever” clause.

Antonio NAVARRO

How to start Gnome GUI in Xming over SSH

Sometimes it is necessary to start a desktop or graphical environment for certain tasks. In this case we will use a desktop on Solaris using Xming. Xming is a free tool to use X  Terminal on Windows.

 

• Download Xming  here

 

We will use Putty as SSH protocol support. Since Xming can launch the desktop directly to the XLaunch setting it, but here we will launch the desktop from a console, so we must launch the command below, once we are already connected to the machine.

 

/usr/bin/gnome-session

 

This command will initiate a desk.

 

 

Change Undo mode to Rollback using pfile

Here are the steps to move from  UNDO mode to Rollback Segment using pfile like parameter file. The changes in the init.ora must be made with a text editor, of course, spfile is more powerful.

We create a tablespace for the Rollback Segment.

Restart the instance setting UNDO to manual.

1. Shutdown
2. Set undo_management = ‘manual’
3. Startup

We create a Rollback Segment

Restart the instance again and configure the parameter rollback_segments, UNDO_MANAGEMENT must remain to manual value.

1. Shutdown
2. Set rollback_segments=’my_rollback’
3. Startup

Is very easy.

HTH – Antonio NAVARRO

 

 

ORA-30014 Error during import OUTLN tables

Today I had a problem while moving outlines from a Development environment to Test environment. This operation is basically a export (in the database which are the outlines) and import (in the target database). You can find out more about the detail of the process at this link.

• Moving Outline Tables

Performing the import I got the following errors;

After analyzing the problem, and although there are several errors I have seen that the main error is;

ORA-30014: operation only supported in Automatic Undo Management mode

From documentation

Error: ORA-30014 (ORA-30014)
Text: operation only supported in Automatic Undo Management mode
—————————————————————————
Cause: the operation is only supported in automatic undo mode.
Action: restart instance in Automatic Undo Management mode before
retrying operation.

This database support Siebel and is currently involved in a process of upgrading. Siebel documentation recommending use of Rollback Segment instead of UNDO.  Activating UNDO (setting UNDO_MANAGEMENT to auto and unsetting rollback_segments) solves the problem.