Oracloid Blog

If you are using Oracle Data Pump to backup tables containing LONG or LONG RAW columns, then you might be surprised when trying a recovery. Well, you tested it already. Didn’t you? ;-)

Right now I’m in the middle of a production migration. Earlier this week while testing this migration, I noticed couple strange errors during Data Pump import:

ORA-31693: Table data object “OWNER”.”TABLE” failed to load/unload and is being skipped due to error:
ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind


At some point during verification of all functionality, users notified me that they can’t see Discoverer reports stored in the database. After some poking around we figured that reports are stored in EUL5_DOCUMENTS table (thanks to Rob Hamel for help) and that table contains LONG RAW column. Guess what? This table was empty in the new migrated database.

It turned out that Data Pump Import (impdp) doesn’t allow importing LONG data into LONG columns. Instead, it requires that LONG and LONG RAW type columns are converted to LOB datatype (CLOB/BLOB) and only then it is able to import tables with LONG columns. Otherwise, those tables are empty.

One may argue that LONG data types are supposed to be converted to LOBs but, hey, let’s get back to the reality - either there is no time for that or no interest to take a risk and touch working applications. In fact, in the HA environment no one will approve combination of data move and application refactoring to be done at the same time - only one by one to mitigate the risks. Oracle Data Pump doesn’t give a chance for this and old Export/Import utilities should be used. Even CTAS (Create Table As Select) over database link doesn’t work failing with ORA-00997: illegal use of LONG datatype.

Now go back to you databases backed up with Data Pump Export and run:
select DECODE(count(*),0,'Not much','I''m screwed '||count(*)||' times') "What's up?"
from dba_tab_columns
where data_type like 'LONG%'
and owner not in ('SYS','SYSTEM','OUTLN','EXFSYS','SYSMAN','WMSYS',
'and whatever other standard schemas where Oracle ignores its own recommendations');

Today is Sunday and I don’t want to finish on the negative note. So here is a good one…

Yesterday, I went to Produce Depot to grab some fruits and vegetables. By the way, if you live in Canada and don’t know this shop, I strongly recommend to go find one near you. It’s has great selection of quality vegetables and fruits. They are very fresh and prices are surprisingly low.

So back to the story… I picked up couple of broccoli and was looking around for a bag. I probably looked really lost because a man passing by offered me his bag that he prepared in advance… even a second one as my broccoli didn’t fit into a single bag and off he went for another two bags. How cool is that?! I don’t remember anything even close to that back in Germany.

This is just a single example but you know what? It’s amazing how friendly and open people are here in Canada. Well, I can’t say that people are unfriendly in Germany but you always feel the distance. It’s just cultural and, to be fair, as soon as you get to know someone closer – they are nice and charming people.

PS: In the meantime, migration has finished. Everything works like a charm. That was a long on-call weekend and I, perhaps, deserved an unscheduled holiday for tomorrow. This is another good news. Life is good!

While testing a migration today with one of our clients, I figured out that schema export using Data Pump doesn’t capture public synonyms on the objects in this schema. This behavior is different from the older Export utility which includes public synonyms with schema objects. Update: This is actually the same behavior as old Export utility.

I can think of two workarounds:

• Use Data Pump to export/import public schema including only synonyms but I couldn’t think of an easy way to filter only public synonyms on objects in specified schemas.
• Generate a script that creates public synonyms. You can run the following on the source database, for example:

SELECT 'CREATE PUBLIC SYNONYM ' || synonym_name || ' FOR '
|| table_owner || '.' || table_name || ';' cmd
FROM dba_synonyms
WHERE TABLE_OWNER IN ([list of schemas]) AND owner='PUBLIC';

Does anyone know how to make Data Pump include public synonyms with schema export?

Updated:
Thanks to Paul for Export behavior correction. Also see below Andrew’s more complete version of the query including database links.

Concerned which objects are exported by Data Pump on full, schema, and table levels? Check views - DATABASE_EXPORT_OBJECTS, SCHEMA_EXPORT_OBJECTS, and TABLE_EXPORT_OBJECTS.

I still couldn’t get Data Pump filter out only synonyms I need. So far the best I’ve come up with:
INCLUDE=SYNONYM:"IN (SELECT synonym_name FROM dba_synonyms WHERE table_owner='TEST')"
However, this also captures private synonyms that happen to have the same name.

A few days ago Kevin Closson (keep an eye on this new blog and expect to find something interesting there) mentioned that Oracle 8.1.7 is one of the most mature releases (at least I understood it this way). I agree with him that many people still use it but how many? Google Trend is our friend - enter “oracle 8i, oracle 9i, oracle 10g” and take in the popularity trends:

It looks like Oracle 10g became dominant somewhere in the 2005 Q3. Unfortunately, trends are only available since 2004, and there is no data on when 9i took over 8i. I find it interesting that the popularity of 8i is decreasing at about the same pace as 9i.

Now, note that this is the trend of search keywords including Oracle 8i. But hey, assuming that 8.1.7 is the most stable release, people wouldn’t search too much about it. If that’s the case, then we should multiply this trend by something to estimate number of 8i environments. Maybe by two?

How would you interpret this graph?

Another interesting observation - click on the “Regions” tab under the popularity graph:

Is anyone running Oracle in North America or Europe?

This will help when you need to investigate some past changes to your database when auditing was not enabled. This doesn’t imply that you don’t need auditing. On the contrary, I see no reason not to use it in any and every database. However, often we get systems “as is” and we need a working method now and not the next time it happens.

Here is the story. One night my team got paged about a materialized view (MV) refresh that failed with “ORA-01017: invalid username/password; logon denied”. This was an MV on the table from a remote database using a database link.

Obviously, someone changed the password (ruling out modification of the database link and materialized view). After some questioning, we couldn’t figure out who the culprit was. The client became very worried that someone unauthorized changed the password, and so it became our top priority to figure out who/when/why.

The first thing to look at is whether auditing is enabled in a given instance. As usual, it’s never there when you need it — AUDIT_TRAIL=NONE. My first estimation of the time frame when the password was changed was the 24 hours between last MV refresh and the first failure. But that’s not good enough.

DBA_USERS doesn’t contain a column with the time of the password change, but it’s based on the SYS.USER$ table, and there is a PTIME column there. Fair enough — the timestamp there was within the 24 hours range I mentioned above: the time was 12:40:30. That’s better already.

DBAs often use the SYSDBA account for a simple login. In case you don’t know, all SYSDBA connections are logged, regardless of audit_trial parameter. So let’s have a look at those audit files. First, we need to check the audit_file_dest parameter. If it’s empty, audit files are created in $ORACLE_HOME/rdbms/audit (that can be platform-specific). Here I found a few suspicious files created at 12:40 and 12:37. I don’t believe in coincidence, so this is probably the incident.

(more…)

How many times did you find yourself looking whether a certain feature is part of Oracle Standard Edition or it requires an Enterprise license?

I used to find myself in that situation quite often. I know there is a feature matrix somewhere but every time I end up looking for it over and over again. Worse yet - often I can’t even recall that magic combination of keywords leading to the page I need.
As it always happens, I find something by a chance when I don’t need it. Today is no different but this time I’m posting the reference in the blog so that me and anyone else can use this magic link - Metalink Note 271886.1 Differences Between Different Editions of Oracle Database 10G.

It includes all version of Oracle 10g databases:

• Standard Edition One

• Standard Edition

• Enterprise Edition

• Personal Edition

The first part of the document lists options available for EE. They are actually included into Personal Edition as well with few exceptions. Note that these option must be licensed separately in addition to an Enterprise Edition license. As usual - your salesman will be happy to take your call. ;-)

More interesting is the second part of the document with the feature matrix. This note rocks!

PS: Funny one - the feature matrix in the note says that “Backup and recovery” is not available for Standard and Standard One Editions so you won’t be able to have a backup with SE. ;-) OK, OK - read the comments there. ;-)

Update 02-Nov-2006:

Here is another good reference - Oracle Database Licensing Information 10g Release 2. This document includes Express Edition as well.

In the first chapter, you find detailed description of five editions, feature matrix, special cases (such as RAC with SE), CRS licensing, “Processor” definition and ratings and much more. For example, did you know that you don’t need a separate license for a database dedicated to an RMAN repository?

Chapter 2 covers separately licensed database options as well as management packs. Noticed one funny fragment there in description of Oracle Change Management Pack - “The Change Management Pack includes the following features: … Update database object definitions (ALTER TABLE) …”. Have you ever run ALTER TABLE on your database? ;)