ORA-1830 / ORA-06512 While upgrading to 12.1.0.2

16 Oct ORA-1830 / ORA-06512 While upgrading to 12.1.0.2

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, and 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) :

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 > 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
]

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

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)

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

[oracle@dev 20369415]$ opatch lsinventory | grep 20369415
Patch 20369415 : applied on Sun Sep 25 14:49:59 CDT 2016
20369415

Once the patch was applied , I reran the upgrade, and now it finished successfully

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

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.