By chance, right after my “ODC Appreciation Day” post, I’ve been asked to convert a database from character set WE8ISO8859P1 to AL32UTF8 with DMU. Apart from a few well-known issues described in MOS note 2018250.1, I got a “Need conversion” row on table
WRI$_SQLSET_DEFINITIONS in data dictionary.
Section D.11 of MOS note 2018250.1 states that you can remove “Invalid Binary Representation” in AWR tables (WRI$_%, WRH$_%, WRR$_%) by following MOS note 782974.1 to drop and recreate AWR. I tried this solution as a last resort. Unfortunately, after using
catnoawr.sql, most of
WRI$_% tables are still there, only 3 of them are dropped. And of course,
WRI$_SQLSET_DEFINITIONS remains intact.
What is this table ? What does it contain ?
(Picture from Oracle.com)
It was very difficult for me to choose a topic for #ThanksODC this year 🙂 I finally decided to talk about something very useful, but maybe not so widespread.
For once, I am going to praise a GUI 🙂 : The Oracle Database Migration Assistant for Unicode (DMU). Migrating a database’s character set is not trivial. This is why DMU comes in handy.
(Picture by Hermes Rivera, via Unsplash)
EDIT : This bug has finally been fixed by patch 28809007 and should be included with release 20.1 🙂 !
This blog post describes a very specific problem I encountered while using datapatch.
I recently patched 400+ 18.104.22.168 databases on RHEL 7.5 with Database Proactive Bundle Patch + OJVM PSU 22.214.171.124.180717 as described in this previous blog post.
Everything went very well on all databases, except one, maybe one of the most critical databases because it is a repository centralizing information about all databases in our ecosystem.
Datapatch would not work properly and would output the following error :
SQL Patching tool version 126.96.36.199.0 Production on Mon Oct 8 15:25:07 2018
Copyright (c) 2012, 2017, Oracle. All rights reserved.
Connecting to database...OK
Bootstrapping registry and package to current versions...done
Error in bootstrap log /ccv/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_27945_2018_10_08_15_25_07/bootstrap1_SOMEDB.log:
Error at line 31: ORA-01427: single-row subquery returns more than one row
Prereq check failed, exiting without installing any patches.
Please refer to MOS Note 1609718.1 and/or the invocation log
for information on how to resolve the above errors.
SQL Patching tool complete on Mon Oct 8 15:25:09 2018