Category Archives: Oracle Database

Naming Oracle Container Databases

containers

The introduction of container databases in Oracle 12.1 created a whole new world of Oracle Databases dividing them into non-Container Databases (we’d been using these all the way through Oracle 11g, we just didn’t know it) and Container Databases.

Starting with Oracle 20c all Oracle Databases will be container databases, so we’ll no longer be able to stick with the (to many at least), more familiar non-Container Database architecture.

From the 20c documentation:

Note: A multitenant container database is the only supported architecture in Oracle Database 20c. While the documentation is being revised, legacy terminology may persist. In most cases, “database” and “non-CDB” refer to a CDB or PDB, depending on context. In some contexts, such as upgrades, “non-CDB” refers to a non-CDB from a previous release.

Great! That won’t be confusing at all… (Yes, I’m being sarcastic). Hopefully, the documentation will be revised very quickly.

I’ve been teaching the Oracle Education Oracle Database: Managing Multitenant Architecture course ever since the release of 12.1 and I’ve additionally been managing multitenant container databases for multiple customers for many years now.

Over the years I’ve seen many different naming conventions for naming multitenant container databases and the pluggable databases that are deployed inside them.

Oracle, and many of Oracle’s customers, have been referring to the Root Container (CDB$ROOT) of a container database as the CDB, and the pluggable databases inside a container database (which, according to the Oracle documentation are also containers themselves) as a PDB.

One big issue with the CDB/PDB terminology is that it can be hard, at least in English, to distinguish between CDB and PDB when speaking. So, naming a container database cdb1 and naming a pluggable database inside of it pdb1 can make things very hard to understand when having conversations about container databases. “Wait, did you say cdb? Or pdb?”

The container database architecture is really designed to be used with the clients and/or applications connecting to the pluggable databases as if they were the old style stand-alone databases (or non-container databases are they are now called). Generally, nobody except the DBA team is going to be connecting to the root container. Additionally, and probably most importantly, regular database users and/or applications don’t care that they are connecting to a pluggable database. So, adding pdb as a suffix to all your pluggable databases really doesn’t add any value.

I feel pretty confident that the current naming scheme that I’m using with many of my customers actually works in real life. It’s basically two rules:

  • Rule One:
    Name the actual container database with a “con” suffix.
  • Rule Two:
    Name the pluggable database what you would have previously named a standalone database and never add a pdb suffix or prefix.

Let’s look at some examples:

  1. You want to create a ‘playground’ database named orcl.
    The root container would be named orclcon and there would be a single pluggable database named orcl.
  2. You want to create a container database on a production server.
    The root container would be named prodcon and there would be a pluggable database named prod.
  3. You only have one database server (and therefore it is the production server), but you want three databases, prod, test, and dev.
    The root container would be named prodcon and there would be three pluggable databases: prod, test and dev.
  4. You have three database servers, let’s say dev, test, and prod.
    Three container databases named devcon, testcon and prodcon, one per server, each containing a single pluggable named dev, test, and prod respectively.

    Here, however, things could get a bit more interesting. Let’s say we decide to give each developer their own pluggable database. Our dev team is composed of Jill, John, and Jane. We’d still have devcon as the name of the container database that supports development databases, but instead of a single pluggable named dev, we’d have three pluggables named jill, john, and jane.

    Now let’s say that on your test server, you decide that you are going to have four databases: test, integration testing, quality assurance, and training. We’d still have testcon as the name of the container database, but we’ll now have four pluggables named test, int, qa, and train.

After playing with different naming schemes over the years, I’ve found that the naming scheme above works the best in the real world.


Connecting to Oracle with Passwords with Spaces

PasswordWithSpaces

Every once in a while I’ll be in the situation where two things intersect: I’ll want to log into the database using the command line directly (using sqlplus, sqlcl, expdp, rman, etc.) and someone has created a password with spaces in it for the user I want to connect as.

One might assume that you’d only have to escape the double quotes around the password (using \” ), but it turns out you actually need to provide an extra set of double quotes around the password when it has spaces in addition to the escaped double quotes.

[oracle@thecloud ~]$ sqlplus sys@orcl as sysdba
SQL*Plus: Release 18.0.0.0.0 - Production on Sat Dec 14 06:14:33 2019
Version 18.6.0.0.0
Copyright (c) 1982, 2018, Oracle.  All rights reserved.
Enter password:
Connected to:
Oracle Database 18c EE Extreme Perf Release 18.0.0.0.0 - Production
Version 18.6.0.0.0
SYS@orcl AS SYSDBA> create user bob identified by "Password with spaces";
User created.
SYS@orcl AS SYSDBA> grant create session to bob;
Grant succeeded.
SYS@orcl AS SYSDBA> exit

[oracle@thecloud ~]$ sqlplus bob/\""Password with spaces\""@orcl 
SQL*Plus: Release 18.0.0.0.0 - Production on Sat Dec 14 06:18:26 2019
Version 18.6.0.0.0
Copyright (c) 1982, 2018, Oracle.  All rights reserved.
Connected to:
Oracle Database 18c EE Extreme Perf Release 18.0.0.0.0 - Production
Version 18.6.0.0.0
BOB@orcl > exit

If bob’s password was just Password and not “Password with spaces” then we’d be able to connect using either of the below:

[oracle@thecloud ~]$ sqlplus bob/Password@orcl
SQL*Plus: Release 18.0.0.0.0 - Production on Sat Dec 14 06:18:26 2019
Version 18.6.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved. 
Connected to: 
Oracle Database 18c EE Extreme Perf Release 18.0.0.0.0 - Production 
Version 18.6.0.0.0
 BOB@orcl > exit
[oracle@thecloud ~]$ sqlplus bob/"Password"@orcl
SQL*Plus: Release 18.0.0.0.0 - Production on Sat Dec 14 06:18:26 2019 
Version 18.6.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved. 
Connected to: 
Oracle Database 18c EE Extreme Perf Release 18.0.0.0.0 - Production 
Version 18.6.0.0.0
BOB@orcl > exit

Once the password has spaces in it, you must include the ‘double double quotes’ and escape the first set of quotes as in the first example.

 


orabasetab is wrong if you install as grid

Traditionally when you install the Oracle Grid Infrastructure for RAC, the owner of the GI software is a user named grid and the owner of the database software is a user named oracle.

With the 19c version of the GI, the software is shipped as a zip file that you extract. Unfortunately, there is a bug (if you are doing a traditional install) in this extract where the orabasetab file (located here if you are using a traditional OFA compliant install:   /u01/app/19.0.0.0/grid/install/orabasetab) has the following contents:

#orabasetab file is used to track Oracle Home associated with Oracle Base
/u01/app/19.0.0/grid:/u01/app/oracle:OraGI19Home1:N:

Which is fine if you are going to use only the oracle user as the owner of both the GI software and the database software, but if you want to install the GI software as the grid user and the database software as the oracle user then you’ll need to modify the file as so:

#orabasetab file is used to track Oracle Home associated with Oracle Base
# 2019-09-29 Rich Soule updated the original value below to the new value
# so the GI could be installed as the grid user
#/u01/app/19.0.0/grid:/u01/app/oracle:OraGI19Home1:N:
/u01/app/19.0.0/grid:/u01/app/grid:OraGI19Home1:N:

If you don’t do this then there is a real good chance you’ll see the following when you attempt your GI install: Error 49802 initializing ADR

Good luck with your RAC installs…

Rich


Starting Oracle Database on Linux 7 using systemd and making OEM start only after the database is up

Recently I created a new virtual machine for the Oracle Database Admin, Install and Upgrade class that I teach at ACC. Previously I’d used Oracle Virtual Box on my local machine and then uploaded the image to Oracle Cloud and used Ravello to give each of my students their own server.

It was actually pretty straight forward:

  1. Upload the latest Oracle Linux (7.6) ISO that I got from eDelivery.oracle.com.
  2. Create a new blank machine with the following:
    1. 4 CPUs
    2. 24 GB of RAM
    3. 200 GB of disk
    4. Mout the uploaded ISO as a CD-ROM
    5. An elastic IP
    6. Services as follows:
      1. SSH (port 22)
      2. HTTPS (port 443)
      3. VNC (port 5901)
      4. HTTPS (port 7803 for Cloud Control)
  3. Start the image, configuring Oracle Linux 7 with the following:
    1. Server with GUI for software
    2. A static IP address (I used 10.0.0.15, but you could use anything).
    3. IP filtering so my ACC students could access the servers while they are in the labs at ACC and I could access the machines from home
    4. Partition the disk into a 16 GB swap partition, a 10 GB /home partition and then the rest of the disk as the root partition.
    5. When it came time to reboot the server, remove the CD-ROM image and update the configuration before rebooting so the image boots up using the disk.
  4. Install Oracle 12c and create an emrep repository database for OEM 13.3.
  5. Install OEM 13.3.
  6. Install Oracle 11g and create a database that will be upgraded during the course.

At this point everything was great, but since I teach 3-hour classes on Mondays and Wednesdays and shut the servers down between classes, my databases and OEM need to come up cleanly. Oracle has documentation on creating services to automatically start up databases on Linux/Unix, but it uses the old System V method for starting services (which, to be fair does still work on Linux 7). Since this was a Linux 7 server, I wanted to use the new systemd method. Tim’s rather fantastic site had the basic framework, but where he used scripts that he called from the service, I wanted to use dbstart and dbshut so that we could maintain startup and shutdown from a single file (/etc/oratab) rather than modifying a script.

I created the following file:

[root@dba ~]# vim /usr/lib/systemd/system/oracle-database.service
[Unit]
Description=The Oracle Database Service
After=syslog.target network.target

[Service]
# systemd ignores PAM limits, so set any necessary limits in the service.
# Not really a bug, but a feature.
# https://bugzilla.redhat.com/show_bug.cgi?id=754285
LimitMEMLOCK=infinity
LimitNOFILE=65535

Type=oneshot
RemainAfterExit=yes
User=oracle
Group=oinstall
Restart=no

ExecStart=/usr/bin/echo 'Starting Oracle Databases with Y in /etc/oratab'
ExecStart=/u01/app/oracle/product/12.2.0/dbhome_1/bin/dbstart /u01/app/oracle/product/12.2.0/dbhome_1
ExecStart=/usr/bin/echo 'dbstart has completed'

ExecStop=/usr/bin/echo 'Stopping Oracle Databases'
ExecStop=/u01/app/oracle/product/12.2.0/dbhome_1/bin/dbshut /u01/app/oracle/product/12.2.0/dbhome_1
ExecStop=/usr/bin/echo 'dbshut has completed'

[Install]
WantedBy=multi-user.target

I then enabled the service using the following:

[root@dba ~]# systemctl daemon-reload
[root@dba ~]# systemctl enable oracle-database

While the above worked great to start the database (tested with a reboot of the server), it didn’t address another issue. Unlike the database, Oracle Enterprise Manager comes with ‘out of the box’ scripts to start and stop OEM. They are the old style System V scripts that run out of /etc/init.d, and it didn’t really seem worth going through the trouble of converting them to the new systemd format. Unfortunately, the OEM scripts always assume that the database is already up and running. If your repository database is running on the same server as your OMS (which isn’t really that big of a deal if your hardware can handle it) that can be fixed by modifying the OEM startup script and adding in a ‘check to make sure your database is up and running before you start OEM’ section. The content in bold below was added to the out of the box OEM script after the initial comments in the file.

[root@dba ~]# vim /etc/init.d/gcstartup
# 2019-03-05 Rich Soule
# OEM should only startup if the emrep database is already up and running
# on the local machine so the below was added to make sure that happens.
#################### Begin Rich Soule Added Lines #######################
if [ "$1" = "start" ]
then
  counter=0
  while [ $counter -le 24 ]
  do
    ((counter++))
    if ! /usr/bin/ps -e | /usr/bin/grep -q ora_pmon_emrep
    then
      echo 'OEM is waiting on Oracle database to start'
      sleep 10
    else
      break
    fi
   done
   if [ $counter -ge 24 ]
   then
     echo 'Oracle database did not start in time, exiting OEM startup'
     exit 1
   fi
   echo 'Oracle database started, waiting 20 more seconds for database to open'
   sleep 20
  echo 'OMS will now attempt to start as per remainer of the /etc/init.d/gcstartup script'
fi
####################  End Rich Soule Added Lines ########################

The above first checks to make sure that gcstartup was called with the start argument. If so then we’ll check if there is an ora_pmon_emrep process running (emrep is the name of my OEM repository database). If it isn’t running, we’ll wait 10 seconds and check again, but only for 240 seconds total. Once that process is found, we break out of our while do loop. If we did hit our 240-second limit, then we exit out of the gcstartup script totally, otherwise, we wait 20 seconds for the database to open and then continue along with the rest of the gcstartup script.

So far this has been working like a charm.

Note that you could do the same type of thing with OEM and instead have the script make a connection to a remote server every 10 seconds to make sure that the remote OEM repository was up before attempting to start OEM.

Happy Linux-ing and Oracle-ing!


Oracle Broke DICTIONARY in 12.2+ PDBs

2019-08-09 Update: Still broken in 18.6 on Oracle Cloud.

When Oracle introduced pluggable databases in Oracle 12.1, they were supposed to be indistinguishable from non-pluggable databases as far as clients were concerned. Basically, this was true. But one thing that has since broken, at least in 12.2 and higher (confirmed in 18.3), is the comments on the DICTIONARY view.

I (and my students at ACC) use the DICTIONARY view or DICT for short quite a bit as it’s a quick way to find the data dictionary view that you are looking for.

DICTIONARY (or the DICT synonym) is great. It’s got two columns, TABLE_NAME and COMMENTS. Comments are very useful to search through (make sure to use UPPER as they are mixed case) because sometimes the table name can be a bit opaque.

The entry for DICTIONARY is pretty clear:

select * 
  from dict 
 where table_name = 'DICTIONARY';
TABLE_NAME COMMENTS 
DICTIONARY Description of data dictionary tables and views

The above is exactly what you get when you are connected to the root container (CDB$ROOT) of any version of a multitenant database.

However, if you connect to any pluggable database other than version 12.1 (12.1 worked as expected), you’ll get the following for the same query:

TABLE_NAME   COMMENTS   
DICTIONARY   (null)

It turns out that in 12.2 databases and higher, there is only a single comment in the DICTIONARY view when you are connected to a pluggable database:

select * 
  from dict 
 where comments is not null;
TABLE_NAME         COMMENTS 
DBA_CONTAINER_DATA Describes default and object-specific CONTAINER_DATA attributes

And, interestingly enough, in a PDB, DBA_CONTAINER_DATA has no rows! So the only comment in the DICTIONARY view in a PDB is for a view that has no data in PDBs. Obviously, someone dropped the ball here.

In a 12.1 pluggable database, you’d get a LOT of comments in the DICTIONARY view:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 64bit Production

SYS@devpdb > select count(*) from dict where comments is not null;

COUNT(*)
----------
2882

I pointed this out to Oracle Support back in August of 2018. After first not believing me and asking me what I might have done to my data dictionary, and claiming that they couldn’t reproduce it, and then admitting that they could reproduce it, a bug was logged.

Currently, they are here:

“The bug is at Sev2 and will be worked upon by Dev as per the priority.”

The bug number is 28998679.

You can make the DICTIONARY view work in PDBs by creating a database link to the root container and getting the comments from there. Obviously (I hope), updating the actual view name (DICTIONARY) and/or public synonym (DICT) wouldn’t be supported, so you should create your own public synonym and/or view. Of course, I’m not sure how you’d get developers to use it since they are expecting DICTIONARY/DICT to just work…

As an aside, I came across this when building an APEX application for my students that would let them use an interactive report against DICTIONARY to search for interesting data dictionary views.


SQL Developer SSH Connections In Depth

Capture

Available now: A 29-page white paper on SQL Developer SSH Connections that explains in detail how SSH connections should be configured between your desktop/laptop and your organization’s database!

The vast majority of explanations out on the internet of how to configure SSH connections between Oracle SQL Developer and an Oracle Database use incredibly simplistic explanations, often using the oracle os account as the ssh user (absolutely a worst practice!), and not including important things like how firewalls will impact connections, how your ssh server should be configured, etc.

I wrote a 29-page white paper which has been published on the Insum.ca website. It covers the above and more in exacting detail. If you click the link above you’ll be taken to a description of the white paper and then to a page where you’ll be able to download it after supplying a name and email (a fair trade for the amount of time and effort I put into the white paper…).

Note: The image above is from the white paper and is used for explanation purposes only… None of those things actually exist except for the WordPress external firewall that protects just this blog, or, actually, all the blogs at WordPress.com.


Beware: Oracle RMAN Password Only Encrypted Compressed Backups to Tape Might Not Decrypt

So, a few weekends ago I had a BIG scare. I was moving an Oracle Database Appliance from one data center to another. Oracle said the only supported method to do so was to wipe the machine back to bare metal and rebuild it in the new data center with all the new IP addresses and network settings. To prepare for this I did a cold backup of a database to tape (really Oracle Cloud Backup) and used the following RMAN commands:

RMAN> set encryption on identified by ******************* only;
RMAN> backup device type sbt incremental level 0
      tag '2018_06_29_SERVER_MOVE' force as compressed backupset database;

Unfortunately, when it came time to read that backup from Oracle Cloud Backup (our configured sbt device), I got the following error:

ORA-19913: unable to decrypt backup

As I worked through the issue with Oracle Support for a very long time I eventually started working with some support folks who said the following:

“I’ve seen issues in the past when you do a password only encrypted compressed backup to tape where it can’t be decrypted. You shouldn’t do that.”

I think this is potentially one of the biggest bugs in the history of Oracle (if you can’t trust a backup then something is very very wrong!) if the issue is really there… I’ve yet to be able to do any additional testing of this, but figured I’d give folks a warning.

As an aside, I did take a disk backup before the tape backup and then copied the disk backup to a mounted NFS drive. I was able to move that disk backup over to the server and restore from it, so no customer data was harmed during the server move.