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

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

Problem with datapatch, sqlpatch_bootstrap.sql and obj$

hermes-rivera-471873-unsplash

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

Deinstalling previous Grid Infrastructure Home after an upgrade to 18c

the-creative-exchange-682637-unsplash

(Picture by The Creative Exchange, via Unsplash)

Yesterday, I upgraded my lab Grid Infrastructure version 12.1 to version 18c on Oracle Linux Server release 7. Everything went (approximately) OK, but this is not the subject of interest today. Afterwards, I wanted to get rid of the old 12.1 Grid Infrastructure home with the deinstall utility.

Continue reading

Issues encountered after applying Database Proactive Bundle Patch + OJVM PSU 12.1.0.2.180717

(Picture by Jannes Glas, via Unsplash)

I have to admit that I always volunteer at work when a patching has to be done. I like patching. Not the patching process itself, but the learning process. Sometimes it even helps me get better at troubleshooting.
Today I just want to gather in one post a few things that came up after applying Database Proactive Bundle Patch + OJVM PSU 12.1.0.2.180717, hoping this can help someone somewhere 🙂 I will update this post later if I encouter new issues.

Continue reading

Services are useful, especially for tracing

shane-hauser-170013-unsplash.jpg

(Picture by Shane Hauser, via Unsplash)

A fellow developer recently asked me if I could help him check if a bug had been fixed in production or not, because he could not trace (pun intented) recent changes. The problem consisted in 2 specific queries launched a gazillion times on an Oracle 12.1 database. Among other things, I wanted to get some traces, so I asked questions to better understand and narrow down what should be traced.
Continue reading

Oracle Trace File Analyzer (TFA) – 18.2.1 REST services installation and examples

Nesting dolls

(Picture by allysonmiller1969 [CC BY-SA 3.0], via Pixabay)

After having upgraded TFA in version 18.2.1, I really wanted to configure and start using its new REST support feature (since 18.2.0) : “Oracle Trace File Analyzer now includes REST support, allowing invocation & query over HTTPS. […] REST supports printing details, starting a diagcollect and downloading collections”

First, I simply tried to start TFA REST services :

# sudo /grid/infrastructure/home/bin/tfactl rest -start

Configuring TFA REST Services using ORDS :

This might take couple of minutes. Please be patient.

Adding Dependency Jars to ORDS

Adding users to ORDS :

Enter a password for user tfaadmin:
Confirm password for user tfaadmin:

Enter a password for user tfarest:
Confirm password for user tfarest:

Starting TFA REST Services

Failed to start TFA REST Services

URL : https://node01:9090/ords/tfactl/print/status

NOTE : The Standalone Oracle Rest Data Services (ORDS) setup feature utilizes file based user
authentication and is provided solely for use in test and development environments.

But I got an error : Failed to start TFA REST Services. What is the problem ?

Continue reading

Oracle Trace File Analyzer (TFA) – Installation/upgrade as root without SSH, and synchronization between nodes

Animal footprints

(Picture by Clker-Free-Vector-Images [CC BY-SA 3.0], via Pixabay)

Working on a 3-node Oracle 12.2.0.1 Grid Infrastructure cluster on Red Hat Enterprise Linux Server release 7.4, I need to upgrade TFA to the latest version which is 18.2.1, as of today. I don’t want to configure passwordless SSH user equivalency for root and need to use sudo. Official documentation indicates :

“If you do not want to use ssh, you can install on each host using a local install. Then use tfactl syncnodes to generate and deploy the relevant SSL certificates.”

Here are the steps I follow to upgrade TFA :

Continue reading

SQLcl, login.sql, format and alias

SQLcl

(Picture from Oracle.com)

There are (more than) two things that I love with SQLcl : formatting and aliases.

I work with several Grid Infrastructure clusters, hosting hundreds of databases accross multiple nodes. I am very often typing the same queries, this is where SQLcl comes in handy. You can use a login.sql file with SQLcl, just like you would use it with SQL*Plus.
After some tests, I found out that SQLcl looks for a login.sql file in directory and subdirectories pointed by the environment variable SQLPATH.

Continue reading

Prerequisites to debug PL/SQL in Oracle Database 12c with SQL Developer

ladybug-862107_640

(Picture by Nata-Ap [CC BY-SA 3.0], via Pixabay)

Why I am so obsessed with ACL ? I have no idea ! Actually it is the other way round, ACL are hunting me.
This morning, I helped a fellow developer who wanted to debug his new PL/SQL procedure with SQL Developer, on a non-CDB 12.1 Oracle Database.

His first attempt generated errors :

Connecting to the database test_debugger.
Executing PL/SQL: CALL DBMS_DEBUG_JDWP.CONNECT_TCP( 'his.host', 'random_port' )
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_DEBUG_JDWP", line 68
ORA-06512: at line 1
This session requires DEBUG CONNECT SESSION and DEBUG ANY PROCEDURE user privileges.
Process exited.
Disconnecting from the database test_debugger.

I issued this command, thinking it would fix the problem :

GRANT DEBUG CONNECT SESSION TO HIS_SCHEMA ;

But he encountered errors again :

Connecting to the database test_debugger.
Executing PL/SQL: CALL DBMS_DEBUG_JDWP.CONNECT_TCP( 'his.host', 'random_port' )
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.DBMS_DEBUG_JDWP", line 68
ORA-06512: at line 1
Process exited.
Disconnecting from the database test_debugger.

Continue reading