Oracle XML export fails with ORA-00936: missing expression

Article:TECH175215  |  Created: 2011-11-23  |  Updated: 2012-03-20  |  Article URL http://www.symantec.com/docs/TECH175215
Article Type
Technical Solution


Issue



The logical export is able to backup some tables, but fails when backing up others.


Error



The bporaexp or bporaexp64 debug log will show the first 2048 bytes of the query being executed and then a resulting failure.

 

10:04:07.578 [2515130] <2> logconnections: BPRD CONNECT FROM 3.3.3.3.40325 TO 1.1.1.1.1556 fd = 8

10:04:08.416 [2515130] <4> dbc_RemoteWriteFile: INF - RemoteWriteFile status = 0

10:04:08.416 [2515130] <16> OAOraExecuteQuery: ERR - ERROR CODE = 936 ORA-00936: missing expression

10:04:08.416 [2515130] <4> VxBSAWriteToProgressLog: INF - entering VxBSAWriteToProgressLog

 

The progress file will also show the query and the failure.

 

2011/12/02 10:04:07:022

Preparing:

select TO_CHAR(NUMERO,'TM9'), NAME, DESCRIPTION, TYPE, STATUS, DATE, HEURE, OPERANT from OLYA21BKMF.EODTRT

status = OCI_ERROR

ERROR CODE = 936

ORA-00936: missing expression

2011/12/02 10:04:09:022

Preparing:

select TO_CHAR(NUMERO,'TM9'), NAME, DESCRIPTION, TYPE, STATUS, DATE, HEURE, OPERANT from OLYA21BKMF.EODTRT

status = OCI_ERROR

ERROR CODE = 936

ORA-00936: missing expression


The job details for the Application backup job handling the export will show the root failure leading to an exit status 6.

 

12/2/2011 10:04:03 PM - begin writing

12/2/2011 10:04:07 PM - Info bphdb(pid=236) dbclient(pid=236) wrote first buffer(size=1150)        

12/2/2011 10:04:09 PM - Error bpbrm(pid=4476) from client Master: ERR - status = OCI_ERROR    

12/2/2011 10:04:10 PM - Error bpbrm(pid=4476) from client Master: ERR - ERROR CODE = 936 ORA-00936: missing expression  

12/2/2011 10:04:10 PM - Error bpbrm(pid=4476) from client Master: ERR - Aborting archive...     

12/2/2011 10:04:11 PM - Error bpbrm(pid=4476) from client Master: ERR - Database Archiver terminated with errors.  

12/2/2011 10:04:11 PM - Info bphdb(pid=236) dbclient waited 0 times for empty buffer, delayed 0 times  

12/2/2011 10:04:11 PM - Info bphdb(pid=236) done. status: 6         

12/2/2011 10:04:21 PM - end writing; write time: 00:00:18

the backup failed to back up the requested files(6)

 

The job details for the Automatic backup job that initiates the export template will show the root failure leading to an exit status 6.

 

12/2/2011 10:03:24 PM - Info bphdb(pid=6060) Backup started          

12/2/2011 10:03:24 PM - Info bphdb(pid=6060) Database validation buffer = :DBNAME:ORCL:DBHOME:<path>:DBNETNAME:<SID>       

12/2/2011 10:03:24 PM - Info bphdb(pid=6060) DBNAME=<SID> DBHOME=<path> DBNETNAME=<SID> DBTEMPLATENAME=myexport.tpl        

12/2/2011 10:04:13 PM - Error bpbrm(pid=3096) from client Master: ERR - failed executing command <"C:/Program Files/Veritas/NetBackup/bin/bpdbsbora.exe" -backup -rw -t "myexport.tpl">

12/2/2011 10:04:13 PM - Error bpbrm(pid=3096) from client Master: ERR - exit status: <1>    

12/2/2011 10:04:13 PM - Error bpbrm(pid=3096) from client Master: ERR - bphdb exit status = 29: failed trying to exec a command

12/2/2011 10:04:13 PM - end writing

12/2/2011 10:04:18 PM - Info bphdb(pid=6060) done. status: 29: failed trying to exec a command   

failed trying to exec a command(29)

 


Environment



Any currently available version of Oracle and NetBackup


Cause



This failure occurs because the Oracle reserved keyword DATE is used as column name within the table.  SQL*Plus and other Oracle query tools will report a similar failure when operating on the improperly named column.

 

SQL> select NAME, DESCRIPTION, DATE, OPERANT

SQL> from sys.problem;

select NAME, DESCRIPTION, DATE, OPERANT from sys.problem

*

ERROR at line 1:

ORA-00936: missing expression

 


Solution



All versions of Oracle, up to and including Oracle 11g, do not support the use of Oracle reserved words such as 'DATE', 'TAPE' and 'FILE' for Oracle schema objects.  As a result, when bporaexp tries to query for these column names, Oracle returns the errors indicated above.

 

The errors noted above are normal and expected.  The solution recommended by Oracle is to change the column names to non-reserved words.  For a full list of reserved words that should be avoided when naming schema objects, see the Oracle documentation for the release that is in use.

 

This document lists the reserved words for Oracle 11g R1;

http://download.oracle.com/docs/cd/B28359_01/appdev.111/b31231/appb.htm  

 

If the goal is to backup the database, then RMAN can be used to affect a backup (instead of exporting) as the backup only operates on the Oracle blocks containing the data and does not reference the tables or columns by name.

 

 

Symantec Corporation has acknowledged that the above-mentioned issue (ETrack 2715807) is present in the current version(s) of the product(s) mentioned in this article.  Symantec Corporation is committed to product quality and satisfied customers.  This issue was scheduled to be addressed in the following release(s):

 

NetBackup 7.6

 

When released, please visit this link for download and README information:

 http://www.symantec.com/business/support/index?page=landing&key=15143 (for NetBackup Enterprise Server)

 

Please note that Symantec Corporation reserves the right to remove any fix from the targeted release if it does not pass quality assurance tests or introduces new risks to overall code stability. Symantec's plans are subject to change and any action taken by you based on the above information or your reliance upon the above information is made at your own risk.

 


Supplemental Materials

SourceETrack
Value2715807
Description

(primary) Oracle XML export fails if reserved words are used as column names in exported data


SourceETrack
Value2720058
Description

(7.0 fix) Oracle XML export fails if reserved words are used as column names in exported data


SourceETrack
Value2715340
Description

(7.0.1 bundle) A group of fixes for several bporaexp64 issues including this one.



Article URL http://www.symantec.com/docs/TECH175215


Terms of use for this information are found in Legal Notices