Standards... keep them updated!

I like this definition from Business Dictionary:

A couple of weeks ago I was working on a database refresh. As the database is not so big (800 GB) and it is a test environment I decided to recreate the database from the scratch and import the data instead of using the restore/recover approach (Data Pump does a great job recreating everything :o)  - both databases use the same version and platform (looking back I should use the ordinary restore/recover process).

Four or five years ago I created a set of scripts to create databases. It is pretty nice: there is just one script that needs customization (the parameters script), and after defining all parameters it is just a matter of starting the main script, and we can sit and relax watching the database being created - as if that were possible in our multitask work environments :o( Anyway, I used these scripts and the database was created successfully, so I started the import and less than 2 minutes later it just failed:

Processing object type SCHEMA_EXPORT/USER ORA-39126: Worker unexpected fatal error in KUPW$WORKER.PUT_DDLS [USER:"ABC"] ORA-01403: no data found ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95 ORA-06512: at "SYS.KUPW$WORKER", line 8164 ----- PL/SQL Call Stack -----   object      line  object   handle    number  name 0x78a3b9a8   19028  package body SYS.KUPW$WORKER 0x78a3b9a8    8191  package body SYS.KUPW$WORKER 0x78a3b9a8   15631  package body SYS.KUPW$WORKER 0x78a3b9a8    3944  package body SYS.KUPW$WORKER 0x78a3b9a8    8874  package body SYS.KUPW$WORKER 0x78a3b9a8    1651  package body SYS.KUPW$WORKER 0x78a12340       2  anonymous block

To help to identify the issue I ran Data Pump again, this time using the parameter TRACE hoping to get more information about the error – see note Export/Import DataPump Parameter TRACE - How to Diagnose Oracle Data Pump [ID 286496.1].

Analyzing the trace generated by the failing work process it was possible to identify the error occurred just after calling the function DBMS_METADATA.CONVERT:

KUPW:12:37:14.549: 1: DBMS_METADATA.CONVERT returned.              

KUPW:12:37:14.560: 1: In procedure DETERMINE_FATAL_ERROR

Considering Data Pump stores the data to recreate the DDL statements in XML format and DBMS_METADATA.CONVERT is used to transform XML documents, I suspected something was wrong with the XSLT style sheet used to get the DDL. Looking at table METAXSL$ under SYS schema it is possible to identify the style sheet used to transform the XML coming from Data Pump dump file. Once you get the style sheet name (column SCRIPT), look at table METASTYLESHEET to get the complete style sheet. Well, the style sheet looked ok; I compared it against the source database, and both records were the same (for reference the style sheet name is kuuser).

Back to zero ground, I decided to run Data Pump again this time tracing the Data Pump worker sessions using event 10046. Analyzing the trace files I got one more table: SYS.METAXSLPARAM$ - checking ORACLE_HOME/rdbms/admin/catmeta.sql, I see it is used to define all the valid transformation parameters for each dictionary object type and transform the user can set through the DBMS_METADATA.SET_TRANSFORM_PARAM method. Comparing it against the source database I found 1 mismatch: a missing back slash in the reord with the column PARAM equal to PRS_DELIM - it should be "\{]`" instead of "{]`".

Hmm, the back slash is not a strange character; it is the default SQL*Plus escape character! BTW, one of the scripts has the command SET ESCAPE ON, as the convention used to create the data files is DB_NAME.filename.dbf, so the scripts use something like this when specifying file names: &&DATABASE_NAME\.filename.dbf

Database recreated and lesson learned: standards are good, we just need to keep them up to date, reviewing and evaluating them on a regular basis.

Later I found on Oracle Support site this note: DataPump Import (IMPDP) Fails with Errors ORA-39126 ORA-1403 No Data Found [ID 729346.1]. This time if I had checked Oracle Support at first I would have saved some time.