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.
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-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
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.
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.
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.
This command will initiate a desk.
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.
- Set undo_management = ‘manual’
We create a Rollback Segment
Restart the instance again and configure the parameter rollback_segments, UNDO_MANAGEMENT must remain to manual value.
- Set rollback_segments=’my_rollback’
Is very easy.
HTH – Antonio NAVARRO
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.
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
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
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.