Oracle improbable errors, or should I say bugs?

SQL> create trigger DUSER.H_SASTAB_BI BEFORE INSERT ON DUSER.H_SASTAB FOR EACH ROW BEGIN   SELECT DUSER.H_SEQ.nextval INTO :new.h_node_id FROM dual; END; /   Warning: Trigger created with compilation errors.   SQL> show err   LINE/COL ERROR -------- ------------------------------------------------------ 3/15     PLS-00302: component 'H_SEQ' must be declared

 

PLS-00302: component "string" must be declared

Cause: In a reference to a component (for example, in the name "A.B", "B" is a component of "A"), the component has not been declared. The component might be misspelled, its declaration might be faulty, or the declaration might be placed incorrectly in the block structure.

Action: Check the spelling and declaration of the component. Also confirm that the declaration is placed correctly in the block structure.

 

Straightforward: H_SEQ does not exist, right?

 

SQL> select owner, object_name, object_type        from dba_objects       where object_name = 'H_SEQ';   OWNER   OBJECT_NAME      OBJECT_TYPE ------- ---------------- -------------------------------------DUSER   H_SEQ            SEQUENCE

 

As DUSER owns the sequence let us try without qualifying the sequence:

 

SQL> create trigger DUSER.H_SASTAB_BI BEFORE INSERT ON DUSER.H_SASTAB FOR EACH ROW BEGIN   SELECT H_SEQ.nextval INTO :new.h_node_id FROM dual; END; /   Trigger created.

 

Wow! My best guess? There is a table with the same name as the owner DUSER. Therefore

when the sequence was prefixed by the schema name, actually Oracle was checking for

a column named H_SEQ in the DUSER table. As such column does not exist we received the

error PLS-00302: component 'H_SEQ' must be declared.

 

SQL> select owner, object_name, object_type        from dba_objects       where object_name = 'DUSER';   OWNER   OBJECT_NAME      OBJECT_TYPE ------- ---------------- -------------------------------------DUSER   DUSER            TABLE

 

Why should the application have a table with the same name as the application owner?

Well, to tell you the truth I really don't want to know :o)

 

Anyway, it does not make sense, Oracle should be smarter than that – it should realize this is

a sequence. So, a new guess: there is a bug. Indeed, BUG: 1218383.

Checking it, it seems something that was fixed in the past, but is back. BTW, the database with

this issue was migrated from 10.2.0.4 to 11.2.0.3.

 

A Sequence Prefixed by Schema Name Fails with PLS-00302: component 'SEQUENCE_NAME'

must be declared [ID 1371201.1].

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.

Changes...

Sometimes we get so used to the way things are that we forget things change, and normally such changes get us out of our comfort zone. Last Saturday I was working on another project, installing a 3 nodes RAC database, 11g release 2. Oracle has improved its installers, so normally they are able to do a good job checking and fixing grid and database pre-requisites, especially on Linux platforms. But this time I could not enjoy the convenience of a GUI as I was working remotely through (a slow) VPN. So before running a silent installation I set manually the SSH configuration between the nodes and ran the cluster verify utility:

 

node1:grid: ./runcluvfy.sh stage -pre crsinst –n node1,node2,node3 -fixup   WARNING: Could not access or create trace file path "/tmp/bootstrap/cv/log". Trace information could not be collected   Performing pre-checks for cluster services setup   Checking node reachability... node1.domain: node1.domain   Check: Node reachability from node "null"   Destination Node                    Reachable?   ------------------------------------  ------------------------   node1                                        no   node2                                        no   node3                                        no   Result: Node reachability check failed from node "null"   ERROR: Unable to reach any of the nodes Verification cannot proceed

 

At first it seems a DNS problem, but:

node1:grid: /usr/bin/nslookup node1 Server:         10.xxx.x.254 Address:        10.xxx.x.254#53   Name:   node1 Address: 200.xxx.xxx.54

 

Ping between nodes worked…

node1:grid: ping 200.xxx.xxx.54 PING 200.xxx.xxx.54 (200.xxx.xxx.54) 56(84) bytes of data. 64 bytes from 200.xxx.xxx.54: icmp_seq=1 ttl=64 time=0.027 ms 64 bytes from 200.xxx.xxx.54: icmp_seq=2 ttl=64 time=0.026 ms

 

Google returned 2 or 3 posts, but all them related to wrong server/domain names or the server name not set correctly in the DNS.

 

Finally I found the problem: the file /etc/nsswitch.conf was wrong. DNS was not part of the Name Service resolution :o(

 

The file should have a line like this:

hosts:  dns files

 

However the "hosts:" service line was:

hosts:  files

And the server names were not defined in the /etc/hosts files.

 

The Installation Guides did not make any mention to nsswitch.conf file. I found it on this book: Oracle® Database 2 Day + Real Application Clusters Guide topic 8.

 

The funny thing is the file was wrong on just one node (that node was used to run some connectivity tests before installing Oracle). Well, documentation is my friend, and I got one more item for my checklist – things change: all the time ;o)