Mario Talavera Writes

- My Development Journal

Oracle 12c Primer – Up And Running

with 2 comments

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.

Enjoy!

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.

image

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).
Notice the one-to-many relationship defined above.  A server has one common database which can have one or many pluggable databases with one or many user schemas each.  Got it? Good. Multiple things factor into how many PDBs to have such as team responsibilities, nature of data or, just as important, licensing!  Lets roll with the layout shown for now.
Common Database (CDB) – As part of the installation process, you would install one CDB.
  • 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.
image

 

Pluggable Database (PDB) –  Likewise, the installer will create at least one of these.
  • 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.
image
  • A PDB can be in an open (writable) or a closed state.  It needs to be open in order to connect to it.
image
  • Similarly, you would close a PDB as follows.  More on why you do this later.
image
  • Connecting to a user schema inside our PDB will require us to use this Service Name, INSTEAD OF, the CDB’s entry.
 Image(15)
Notice the prompt changes from our CDB to the PDB when we connect to the later.  It even shows our SID (for CDB at least).  This is not default behavior but immensely helpful.  Go to this post for all the information on this.  Very neat.
Common Users and Regular Users - Contrasting previous Oracle DB releases, 12c has two types of users.  A database user can be either a common user or a local user.
  • 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.
Enterprise Manager – Is no more!!  At least, EM of old is gone.
  • 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.
Few Gotchas – This is where it hurt for me as I looked at 12c for the first time.
  • 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.
Neat things – After a few days of tinkering, I admit 12c’s new ways bring in some amazing features to the table.
  • 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.
About these ads

Written by mariotalavera

September 20, 2013 at 10:49 am

Posted in SQL

Tagged with

2 Responses

Subscribe to comments with RSS.

  1. You’re architecture diagram show multiple CDBs which is incorrect

    http://docs.oracle.com/cd/E16655_01/server.121/e17633/glossary.htm#BGBIDFEB

    http://docs.oracle.com/cd/E16655_01/server.121/e17633/cdblogic.htm

    http://docs.oracle.com/cd/E16655_01/server.121/e17633/cdbovrvw.htm

    And here’s a handy way to connect to PDBs with the same name from different CDBs
    sqlplus matt@'(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SID=ZEUS)(SERVICE_NAME=matt_test)))’

    sqlplus matt@'(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SID=SUEZ)(SERVICE_NAME=matt_test)))’

    mattdee

    September 20, 2013 at 12:54 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 58 other followers

%d bloggers like this: