It has been almost close to 3 months since we had a successful Oracle 10gR2 (10.2.0.4) clusterware upgrade with nearly 100 database to Oracle 11gR2 (11.2.0.2) at our site. However, our association with an unforeseen bugs and adjusting our application code (a very minimal) to confront the significant changes in the behavior with11gR2 functionality is yet to come to a happy ending . I wanna share yet another 11gR2 upgrade aftermath fairy tale (BUG) that we have come across about a few days ago. Before I start of summarizing what had happen, I must confess it indeed put me in a catch-22 situation and luckily it was with the non-production RAC database, I managed to overcome from the pain. To cut the long story short, here is what happened...
In order to fix some of the application bugs that encountered with the close of business(COB) process, the development team wanted to simulate the test, thereby, requested me to take a logical backup (export) of one of the schema in a RAC database that was recently upgraded to 11gR2. The actual plan was like, performing a logical backup (export) for the schema, simulating the COB test (application) failure, dropping the schema followed by the schema restore (import). The schema contains over 35 thousand objects where every table has only two columns and one of the columns defined XMLTYPE data type. I knew it is bit unusual.
Once the test was over, I dropped the schema and initiated the import just before I left home for the day. The very next morning I promptly jumped to the bottom of the import logfile to ensure everything went smoothly. By bad, what I had seen was a bit disappointing because the import completed with around 15 errors. A quick scan through the logfile from top to down shows that data import has been failed in 15 tables with the following errors:
ORA-31693: Table data object "SCHEMA"."TABLE_NAME" failed to load/unload and is being skipped due to error:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00217: invalid character 27 (U+001B)
Error at line 1
Subsequent search over the internet and in the MOS for the above listed errors really doesn't help me resolving the issue. As per some initial suggestion and workaround, I did run the utlrp.sql script (to recompile a few invalid objects in the SYS schema) and ensure there is no database option left INVALID post database upgrade. After those couple of checks, I gave another try to upload the data into those tables, but, failed with the same errors.
I then opened a Service Request (SR) with Oracle support and seek their assistance to get me out the situation. Right after analyzing the import logfile and other details, the engineer was quick to mention the following lines:
This errors is due to a problem in the 11.2 XML parser and this is still under investigation with the Development team under bug 11877267, So please try as a workaround to import the same dump file on a 10.2 database with the parameter version=10.2 and use a DBlink to copy those tables from the 10g database to the 11g.
I was really caught in a catch-22 situation as I had no Oracle 10g database to perform the workaround mentioned by the support engineer. Moreover, the engineer himself wasn't sure about the workaround could work out or not. When I informed the application about the situation, they initially told me they can go ahead without the data into those tables. After a set of meetings with the application team and discussing the ways to recover the database, they agreed to copy the data from another environment (in fact another schema of the same database). This decision came like a life savior for me.
Though we have done 3 RAC environments cluster+database upgrade to 11gR2, we are yet to upgrade the major production 8 node RAC environment. I really hope by the time we perform the critical production upgrade, we should be able to address most of the issues we come across post 11gR2 upgrade.
Since no solution available yet for the bug in the context, I strongly recommend you to be careful when you have a similar requirements of mine.
Reference
Bug 11877267: EXPDP/IMPDP ARE NOT ABLE TO HANDLE HEXADECIMAL CHARACTER REFERENCES IN XMLTYPE
In order to fix some of the application bugs that encountered with the close of business(COB) process, the development team wanted to simulate the test, thereby, requested me to take a logical backup (export) of one of the schema in a RAC database that was recently upgraded to 11gR2. The actual plan was like, performing a logical backup (export) for the schema, simulating the COB test (application) failure, dropping the schema followed by the schema restore (import). The schema contains over 35 thousand objects where every table has only two columns and one of the columns defined XMLTYPE data type. I knew it is bit unusual.
Once the test was over, I dropped the schema and initiated the import just before I left home for the day. The very next morning I promptly jumped to the bottom of the import logfile to ensure everything went smoothly. By bad, what I had seen was a bit disappointing because the import completed with around 15 errors. A quick scan through the logfile from top to down shows that data import has been failed in 15 tables with the following errors:
ORA-31693: Table data object "SCHEMA"."TABLE_NAME" failed to load/unload and is being skipped due to error:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00217: invalid character 27 (U+001B)
Error at line 1
Subsequent search over the internet and in the MOS for the above listed errors really doesn't help me resolving the issue. As per some initial suggestion and workaround, I did run the utlrp.sql script (to recompile a few invalid objects in the SYS schema) and ensure there is no database option left INVALID post database upgrade. After those couple of checks, I gave another try to upload the data into those tables, but, failed with the same errors.
I then opened a Service Request (SR) with Oracle support and seek their assistance to get me out the situation. Right after analyzing the import logfile and other details, the engineer was quick to mention the following lines:
This errors is due to a problem in the 11.2 XML parser and this is still under investigation with the Development team under bug 11877267, So please try as a workaround to import the same dump file on a 10.2 database with the parameter version=10.2 and use a DBlink to copy those tables from the 10g database to the 11g.
I was really caught in a catch-22 situation as I had no Oracle 10g database to perform the workaround mentioned by the support engineer. Moreover, the engineer himself wasn't sure about the workaround could work out or not. When I informed the application about the situation, they initially told me they can go ahead without the data into those tables. After a set of meetings with the application team and discussing the ways to recover the database, they agreed to copy the data from another environment (in fact another schema of the same database). This decision came like a life savior for me.
Though we have done 3 RAC environments cluster+database upgrade to 11gR2, we are yet to upgrade the major production 8 node RAC environment. I really hope by the time we perform the critical production upgrade, we should be able to address most of the issues we come across post 11gR2 upgrade.
Since no solution available yet for the bug in the context, I strongly recommend you to be careful when you have a similar requirements of mine.
Reference
Bug 11877267: EXPDP/IMPDP ARE NOT ABLE TO HANDLE HEXADECIMAL CHARACTER REFERENCES IN XMLTYPE
1 comment:
Thanks a lot for your input. I have a similar problem and could not figure out why. In my case I did an expdp from 10g, impdp into 11g (worked!), expdp 11g (no problem), impdp into similar testdb also 11g (same problem as you). I know which character caused the problem but did not understand why. Never assumed it could be a bug...
Post a Comment