Pythian Blog: Technical Track

While upgrading to 12.1.0.2 I faced error ORA-01830 / ORA-06512

by Rene Antunez on Oct 20, 2016 12:00:00 AM

The other day I was running an upgrade for a client that is using ACLs ( Access Control Lists) from 11.2.0.3 to 12.1.0.2. If you have been doing upgrades to 12c, you know that when running the catctl.pl -n 4 catupgrd.sql it entails 73 steps. So this upgrade failed in step 65 with the following error (I have trimmed the output for reading purposes) : [code language="text"] Serial Phase #:65 Files: 1 Calling sqlpatch with LD_LIBRARY_PATH=/u01/dev/product/12.1.0/lib; export LD_LIBRARY_PATH;/u01/dev/product/12.1.0/perl/bin/perl -I /u01/dev/product/12.1.0/rdbms/admin -I /u01/dev/product/12.1.0/rdbms/admin/../../sqlpatch /u01/dev/product/12.1.0/rdbms/admin/../../sqlpatch/sqlpatch.pl -verbose -upgrade_mode_only > catupgrd_datapatch_upgrade.log 2> catupgrd_datapatch_upgrade.err returned from sqlpatch Time: 80s Serial Phase #:66 Files: 1 Time: 71s Serial Phase #:67 Files: 1 Time: 1s Serial Phase #:68 Files: 1 Time: 0s Serial Phase #:69 Files: 1 Time: 20s Grand Total Time: 4946s *** WARNING: ERRORS FOUND DURING UPGRADE *** ... REASON: catuppst.sql unable to run in Database: DEVSTAR Id: 0 ERRORS FOUND: during upgrade CATCTL ERROR COUNT=5 ------------------------------------------------------ Identifier XDB 16-09-25 12:27:05 Script = /u01/dev/product/12.1.0/rdbms/admin/ ERROR = [ORA-01830: date format picture ends before converting entire input string ORA-06512: at "SYS.XS_OBJECT_MIGRATION", line 167 ORA-06512: at line 28 ORA-06512: at line 69 ] ------------------------------------------------------ ------------------------------------------------------ Identifier XDB 16-09-25 12:27:05 Script = /u01/dev/product/12.1.0/rdbms/admin/ ERROR = [ORA-06512: at "SYS.XS_OBJECT_MIGRATION", line 167 ORA-06512: at line 28 ORA-06512: at line 69 ] STATEMENT = [as above] ------------------------------------------------------ ------------------------------------------------------ Identifier XDB 16-09-25 12:27:05 Script = /u01/dev/product/12.1.0/rdbms/admin/ ERROR = [ORA-06512: at line 28 ORA-06512: at line 69 ] STATEMENT = [as above] ------------------------------------------------------ ------------------------------------------------------ Identifier XDB 16-09-25 12:27:05 Script = /u01/dev/product/12.1.0/rdbms/admin/ ERROR = [ORA-06512: at line 69] STATEMENT = [as above] ------------------------------------------------------ ------------------------------------------------------ Identifier ORDIM 16-09-25 12:28:53 Script = /u01/dev/product/12.1.0/rdbms/admin/ ERROR = [ORA-20000: Oracle XML Database component not valid. Oracle XML Database must be installed and valid prior to Oracle Multimedia install, upgrade, downgrade, or patch. ORA-06512: at line 3 ] [/code] And the worst part of it was that the upgrade also corrupted my database , also a good point to stress out , have a good backup before attempting to do an upgrade [code language="text"] Sun Sep 25 13:55:52 2016 Checker run found 59 new persistent data failures Sun Sep 25 14:00:18 2016 Hex dump of (file 5, block 1) in trace file /u01/app/diag/rdbms/dev/dev/trace/de_ora_13476.trc Corrupt block relative dba: 0x01400001 (file 5, block 1) Bad header found during kcvxfh v8 Data in bad block: type: 0 format: 2 rdba: 0x01400001 last change scn: 0x0000.00000000 seq: 0x1 flg: 0x05 spare1: 0x0 spare2: 0x0 spare3: 0x0 consistency value in tail: 0x00000001 check value in block header: 0xa641 computed block checksum: 0x0 Reading datafile '/u01/dev/oradata/dev/system_01.dbf' for corruption at rdba: 0x01400001 (file 5, block 1) Reread (file 1, block 1) found same corrupt data (no logical check) [/code] So what I had to do was a restore of my database before the upgrade, as I couldn't even do a flashback due to the corrupt block. But to fix this error, I had to apply the patch 20369415 to the 12c binaries before I ran the catupgrd.sql [code language="text"] [oracle@dev 20369415]$ opatch lsinventory | grep 20369415 Patch 20369415 : applied on Sun Sep 25 14:49:59 CDT 2016 20369415 [/code] Once the patch was applied , I reran the upgrade, and now it finished successfully [code language="text"] Serial Phase #:65 Files: 1 Time: 133s Serial Phase #:66 Files: 1 Time: 78s Serial Phase #:68 Files: 1 Time: 0s Serial Phase #:69 Files: 1 Time: 275s Serial Phase #:70 Files: 1 Time: 171s Serial Phase #:71 Files: 1 Time: 0s Serial Phase #:72 Files: 1 Time: 0s Serial Phase #:73 Files: 1 Time: 20s ------------------------------------------------------ Phases [0-73] End Time:[2016_09_26 17:42:54] ------------------------------------------------------ Grand Total Time: 5352s LOG FILES: (catupgrd*.log) ... COMP_ID COMP_NAME VERSION STATUS -------------------- ---------------------------------------- -------- --------------- APEX Oracle Application Express 4.2.5.00 VALID .08 OWB OWB 11.2.0.3 VALID .0 AMD OLAP Catalog 11.2.0.3 OPTION OFF .0 SDO Spatial 12.1.0.2 VALID .0 ORDIM Oracle Multimedia 12.1.0.2 VALID .0 XDB Oracle XML Database 12.1.0.2 VALID .0 CONTEXT Oracle Text 12.1.0.2 VALID .0 OWM Oracle Workspace Manager 12.1.0.2 VALID .0 CATALOG Oracle Database Catalog Views 12.1.0.2 VALID .0 CATPROC Oracle Database Packages and Types 12.1.0.2 VALID .0 JAVAVM JServer JAVA Virtual Machine 12.1.0.2 VALID .0 XML Oracle XDK 12.1.0.2 VALID .0 CATJAVA Oracle Database Java Packages 12.1.0.2 VALID .0 APS OLAP Analytic Workspace 12.1.0.2 VALID .0 XOQ Oracle OLAP API 12.1.0.2 VALID .0 [/code] Conclusion This was a small post to make you aware that if you are using ACLs , you need to run the patch 20369415 to the 12c binaries so that you don't have to face a possible database corruption and have a harder time upgrading your database. Note: This post was originally posted in rene-ace.com

No Comments Yet

Let us know what you think

See all

See all

Subscribe by email
Follow us
Love Your Data
Pythian Logo

© 2024 All rights reserved.