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

Posted in: Oracle, Technical Track

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

[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.

Note: This post was originally posted in rene-ace.com

email

Author

Want to talk with an expert? Schedule a call with our team to get the conversation started.

About the Author

Currently I am an Oracle ACE ; Speaker at Oracle Open World, Oracle Developers Day, OTN Tour Latin America and APAC region and IOUG Collaborate ; Co-President of ORAMEX (Mexico Oracle User Group); At the moment I am an Oracle Project Engineer at Pythian. In my free time I like to say that I'm Movie Fanatic, Music Lover and bringing the best from México (Mexihtli) to the rest of the world and in the process photographing it ;)

4 Comments. Leave new

Definitely a pointer to avoid a potential show stopper. Great stuff..

Reply
Emad M Al-Mousa
October 23, 2016 1:47 pm

Nice Article !!

Thanks.

Reply

I just run into this, thanks for the great article!
Julian

Reply
rakesh kumar meena
July 3, 2017 9:54 am

Thanks best

Reply

Leave a Reply

Your email address will not be published. Required fields are marked *