Oracle 12c Primer – Up And Running
UPDATE – Post has been revised since posted for accuracy. Thanks Matt.
Alright. Having recently installed Oracle DB 12c, I was disoriented for four days before things started to click. No joke, this latest release of Oracle latest database server is the biggest release-to-release departure of old to new I have witnessed so far… I’ll like to think I have a long way to go.
I’ll go over the items that most baffled me and attempt to explain what these changes mean and how to catch up to Oracle 12c. By the end of this post, maybe I can convince you there quite a few niceties that come from free for all our trouble. Hope it saves you some time as well.
Disclaimer – This post made sense a week into Oracle 12c… I am certain there are better explanations around.
The usual Oracle Installer rears its head again and does (did for me) provide a streamlined, uneventful installation. I think the later portion, where it sets up / installs main database (more later) is even better than before. It allows greater freedom to go into the server, after installation, and doing this later portion again. Neat.
Above is our reference layout for Oracle 12c’s ‘arrangement’. Get ready; this is a mouthful. For an Oracle 12c DB Deployment, we have the following:
- A 12c deployment can have one or more common databases as shown by CDB1, CDB2, CDB3 in illustration.
- A 12c deployment has one common database as shown by CDB1.
- This CDB can have one or more pluggable databases. PDB2’s ‘content’ is being shown above.
- The CDB has one pluggable database seed (PDB$SEED).
- Think of this as a System-wide operational databases of the instance it is in.
- The CDB will have our Common Users such as SYS and SYSTEM we are already familiar with.
- The installer will add an entry to the server’s tnsnames.ora as well. This entry will define the SID/Service name for your CDB. This is the SID entry we commonly end up naming ORCL.
- Connecting with the CDB users will use this default SID as usual.
- The server will use the SEED schema to create our PDBs.
- PDBs can also be created by cloning another PDB. This can be done both from SQLPlus and from SQL Developer.
- PDBs can be moved from one CDB to another one as a whole.
- Moving PDBs can be done within the same server or from one server to another.
- PDBs will host our user schemas.
- In the image above, you can see some of the Oracle Sample Schemas such as HR , OR etc. You can also see some of my own schemas; eBay and Movielens.
- An additional service entry needs to be added to the server’ tnsnames.ora file in order to connect to a schema inside a PDB. This is NOT done by default. Neither the installer nor the Database Configuration Assistant Tool will do this at PDB creation time.
- A PDB can be in an open (writable) or a closed state. It needs to be open in order to connect to it.
- Similarly, you would close a PDB as follows. More on why you do this later.
- Connecting to a user schema inside our PDB will require us to use this Service Name, INSTEAD OF, the CDB’s entry.
- Common Users belong to the CDB.
- Common Users can connect to both the CDB and PDBs as seen above.
- Users SYS, SYSTEM, etc. (DB Users) exist in the CDB.
- Users OE, HR, etc (Sample Schemas) & application databases (Movielens, EZPrints) exist inside a PDB.
- Old EM has been replaced by what seems to be a much simplified version properly titled EM Database Express 12c.
- Features seemingly dropped from EM make it to Cloud Control. This is a separate product.
- Its ALL Flash! Haven’t accessed from Chromebook yet but expectations are low.
- By default, connecting to EM as SYSDBA opens GUI for CDB.
- URL has changed! My EM is not at https://localhost:5500/em
- Much like having to activate a PDB before being able to connect to it, EM GUI per PDB has to activated independently. I found all the information I needed in this great post.
- Connecting as SYS to a CDB will not lists all my databases. Now I need to connect, as SYS, to the PDB where my schemas are.
- EM has been trimmed of features I now need to either setup an EM per PDB (please see last link) or, worse still, install a separate tool (Cloud Control).
- It seems we have to keep track of more things before we can get to ‘work’. Is my PDB open? Did I add TNSNAMES Entry for new PDB? Workflows have clearly changed for us.
- Grouping user schemas in a PDB enables us to manage these as a whole.
- A PDB can be plugged/unplugged in a few steps to transfer/restore on a different CDB. This could save a lot of time if you are rebuilding a failed server. for example.
- A lot of the features (like plugging a PDB) can be done from SQLDeveloper (Version 4.x Beta). This kind of makes up for moving features out of EM.
- Instead of keeping several semi-idle instances around for development, this load can be better managed from one 12c instance and multiple PDBs.