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) :
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
]
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
[[email protected] 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.
Note: This post was originally posted in rene-ace.com
4 Comments. Leave new
Definitely a pointer to avoid a potential show stopper. Great stuff..
Nice Article !!
Thanks.
I just run into this, thanks for the great article!
Julian
Thanks best