I feel it’s going to be good fun - I’m only on my way to UKOUG 2006 but it’s already an adventure. I had the flight Ottawa -> London booked over Washington where I should have had 2 hours for connection. Registration went fine almost until the very end (had to give away my water though and Babette was finishing here coffee on the way) but at the US customs I’ve got the problem. Apparently, there is a new rule for transit passengers - they suspended permission for all international connections without a transit visa. It used to be so that transit passengers stay in the terminal and no need for any kind of visa. Not anymore and it’s valid “until further notice” as stated in their systems.

Being stubborn enough (the common quality here at Pythian ;-) and I’m not going to skip the fun just because of some stupid immigration rules so… I had to go back to the check-in counter, get my baggage and re-book my ticket to avoid a stopover in the States. They re-booked me on the direct flight Ottawa-London and that cost me only $200 after some negotiations (originally $2000+) and 2 hours of my time early in the morning. Unfortunately, I’ve got split up with Babette and I hope she is fine on her own there.

Babette, if you read this post by any chance, I should be in the Hotel on Monday at about 11:00. I also left you a message at the receptions desk.

Back to the UKOUG - I have three sessions there. These are first three right on Tuesday after the Opening Technical Keynote by Tom Kyte:

• Extending Oracle 10g Grid Control - New Metrics, Targets and Plugins - 11:00-12:00, Hall 6
• Refreshing Test Databases Using Oracle Transportable Tablespaces and Veritas Storage Checkpoints - 12:10-12:55, Hall 6. Here is the surprise - I’ll show how to do the same with ZFS. I’m really excited about that.
• Oracle 10g Change Tracking Inside Out - 13:50-14:00, Hall 9. Consider this if you want to get down to X$ tables and tracing.

Babette has two presentations the same day:

• Moving from Oracle Names to Oracle Internet Directory - 11:00-12:00, Hall 11b

• Oracle 10g Data Pump 101 - 17:50-18:35, Hall 5

See you there! I’m off to my flight. Again…

While reviewing some material in advance of my presentation at UKOUG Conference 2006, I found an interesting change in RMAN behavior in Oracle 10g.

The difference is in the way RMAN handles the case when an incremental level 1 backup is taken without an available level 0 backup. This probably won’t affect anyone much, but I found it interesting. And actually, there are scenarios in which it might cause issues.

Pre-10g behavior (also in effect in 10g by setting compatibility to < 10.0.0) produces a level 0 backup in case incremental level 1 is run without level 0.

Oracle 10g behavior is to take a level one backup assuming that a checkpoint SCN of the last level 0 backup is the datafile creation SCN.
It doesn't look like a big deal but imagine the following scenario (I didn't test it - just thinking logically):
Incremental backup strategy is "standard".
Sunday - level 0 backup.
Monday-Saturday - level 1 cumulative.

Now, imagine that on Monday there is an issue and the backup is not available anymore -- either it's lost, or recovery with resetlogs has been done. The next incremental backup stores all the blocks, so we have a valid backup -- no problem here.

However, the keyword in the backup strategy is cumulative. The next incremental level 1 cumulative backup still doesn’t find the level 0 backup and again copies every block in the database. This continues until the next level 0 backup is taken on Sunday. Assuming DBAs are always unlucky, the backup destination tapes will fill up before the incremental level 1 backup is run. Typical, isn’t it?

How can we avoid this? By taking the level 0 backup first time and not regular level 1.

Note that “differential” (when no cumulative keyword is specified) incremental backup won’t cause any issues, as it takes only those blocks changed since the last incremental backup.

Recently we’ve had quite a few migrations to 10g Release 2 and several times been hit by one issue – some users consistently get locked with status LOCKED(TIMED). One good example is with the DBSNMP and SYSMAN users, but more important are locked production accounts.

It turned out that the FAILED_LOGIN_ATTEMPTS attribute for the DEFAULT profile has been changed in 10.2.0.2 (actually 10.2.0.1 and above) from UNLIMITED to the value of 10. Well, that’s good from security point of view. On the other hand, this is really dangerous, especially during or after migrations while chances are high that some process will try to connect with wrong credentials. This can easily end up with a service outage because an application can’t connect.

One way to resolve it is to change the DEFAULT profile. However, I would recommend leaving it 10 by default and, instead, create a new profile and assign the critical production users to this profile:

CREATE PROFILE DEFAULT_10GR1
    LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED;
ALTER USER [USERNAME] PROFILE DEFAULT_10GR1;

The next step should be to review this policy with your security officer. By the way, this must be a substantive discussion – a production DBA should be keen to avoid service outages by any means, while a security officer’s priority is preventing unauthorized access.

PS: Interesting that the issue with the DBSNMP account is supposed to be resolved by creating a special profile – MONITORING_PROFILE. There are few notes on Metalink about it like 336629.1.

PPS: Just now I found out that Laurent has already mentioned that a while ago (and thanks to him) the change was finally documented in the Readme note for Oracle 10.2.

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.