| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: imported database inconsistencies
Steve - The best decision I made was to start cloning the test instances. I
believe it will eliminate problems like you describe. And you get to verify
your backups. Several times I've discovered a small problem with the backup
that caused a problem on recovery. Boy was I relieved to fix that before my
job was at stake.
Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com
-----Original Message-----
Sent: Thursday, May 22, 2003 4:08 PM
To: Multiple recipients of list ORACLE-L
List,
I have a test instance of our database that I create every so often by exporting our production instance, and recreating the database and on a different server. Well now that test instance is actually seeing some usage, and a couple of forms that work properly on the production instace, but produce errors on the test instance. The errors are actually invalid number errors. I can actually duplicate the error by executing queries in sqlplus. The same query on the same tables, actually very static tables(same number of rows on each instance). I don't have date time stamps on the table in question, so I can't verify that no one has updated the production instance...but well the tables are SO static that really is unlikely. I have compared the rows the query should return on both instances, and they are identical.
Again the same query causes an error in one instance, but not the other. The query is fairly straight forward.
Select *
from ref_codes
where to_number('1') IN (to_number(RV_LOW_VALUE),
to_number(RV_ABBREVIATION))
and RV_DOMAIN ='PRICING_CD'
The Error is
WHERE ( to_number('1') IN (to_number(RV_LOW_VALUE) .....
I have doublechecked the character set of both databases match. I have verified that the RV_LOW_VALUE columns contain only numeric characters for the columns that should be returned. I did this by selecting 'x'||RV_LOW_VALUE||'x', and verifying that there were no special characters or spaces returned in the query.
The only difference I am aware of between the two instances is that one has OPTIMIZER_MODE set to "CHOOSE"(test instance where error is occuring), and the other is set to "RULE"(production queries function as expected).
Anyone have an idea where to look next. I am going to bounce the test instance, and change optimizer mode shortly. I just don't know what to look for after that.
Steve
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Steve McClure
INET: smcclure_at_usscript.com
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services ---------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services ---------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Thu May 22 2003 - 16:41:51 CDT
 |
 |