DMU reports “Need conversion” on table WRI$_SQLSET_DEFINITIONS in Data dictionary

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.

2018_10_26_14_28_56_Database_Migration_Assistant_for_Unicode_sli3775d

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 ?

Continue reading “DMU reports “Need conversion” on table WRI$_SQLSET_DEFINITIONS in Data dictionary”

Advertisements

ODC Appreciation Day : DMU – Oracle Database Migration Assistant for Unicode

330910

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

Continue reading “ODC Appreciation Day : DMU – Oracle Database Migration Assistant for Unicode”

Problem with datapatch, sqlpatch_bootstrap.sql and obj$

hermes-rivera-471873-unsplash

(Picture by Hermes Rivera, via Unsplash)

This blog post describes a very specific problem I encountered while using datapatch.

I recently patched 400+ 12.1.0.2 databases on RHEL 7.5 with Database Proactive Bundle Patch + OJVM PSU 12.1.0.2.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 12.1.0.2.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
/ccv/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_27945_2018_10_08_15_25_07/sqlpatch_invocation.log
for information on how to resolve the above errors.

SQL Patching tool complete on Mon Oct  8 15:25:09 2018

Continue reading “Problem with datapatch, sqlpatch_bootstrap.sql and obj$”