Tag Archives: Oracle

Database Service Firewall : Access Control to a PDB in RAC

johannes-plenio-276383-unsplash

(Photo by Johannes Plenio, on Unsplash)

Conferences are great. Not only for the technical content, also for the people. Recently during DOAG, I had very interesting conversations (yes, several conversations ๐Ÿ™‚ ) with Martin Berger about how to control who is connecting to which database in a complex environment. Among other topics, we mentioned that it was possible, starting with Oracle 12.2, to set Access Control Lists to allow connections to a database service (in Non-CDB or PDB) from specific IP addresses.
This new feature Database Service Firewall was introduced with Oracle 12.2. It should not to be confused with Database Firewall, which is a dedicated system used to monitor traffic from and to databases, and is part of Oracle Audit Vault and Database Firewall product.

As I never used Database Service Firewall, I decided to give it a try in a Multitenant environment with RAC.
My lab is a 2-node RAC cluster with Grid Infrastructure 18, a 18.3 RAC Container database called metal, and one pluggable database called opeth.

Continue reading

Create READ ONLY + AWR access on database targets in Oracle Enterprise Manager 12.1.0.5 – EM CLI edition

rodion-kutsaev-760882-unsplash.jpg

(Picture by Rodion Kutsaev, via Unsplash)

I like CLIs. I really like CLIs. Especially when 90% of my previous and quite long blog post can be summarized with only 3 commands ๐Ÿ™‚

It was about creating restricted read only access for users, using a role and a named credential. But EM CLI can greatly simplify this task. To better understand what follows, please read the previous post explaining how to Create READ ONLY + AWR access on database targets in Oracle Enterprise Manager 12.1.0.5 first.

Continue reading

Create READ ONLY + AWR access on database targets in Oracle Enterprise Manager 12.1.0.5

A critical application is recently having a creepy behaviour in production, so its developers are willing to understand what is going on in the database and troubleshoot in an effective way. Let’s give them access to all the databases related to this application throught Oracle Enterprise Manager 12.1.0.5.

The following procedure is mainly relevant with this version, as Oracle Enterprise Manager 13c has a new set of privileges that should be more appropriate.

Continue reading

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