Monday, August 5, 2013

Pluggable databases - logging in

User group technology days and meetings are not only great for the presentations but for the hallway conversations. In a short discussion in the hall, I realized I had forgotten to mention something in my presentation the other day about pluggable databases, an Oracle database 12c new feature, and didn't even realize until having this conversation that it would be of interest to others. It is something that is fairly simple once you figure it out, but can cause a few minutes of distress or a couple of hours of doubt of how one is even a DBA and surviving.
With the pluggable databases there is quite a bit of discussion around creating and moving the pluggable database from one system to another (unplug/plug). The database creation assistant (dbca) is easily used to create a contain or pluggable database.  Normally after creating the database in a Linux environment, the DBA goes into sqlplus from the command line and logs into the database and does some validations. You will probably find that logging into sqlplus connect / as sysdba gets you connected to the container database. But how does one get to the pluggable database? And even a better question, is the pluggable database even available?
Each of the pluggable databases can be open and closed individually. Shutting down the container database will shutdown all of the pluggable databases, but startup of the container database doesn't mean all of the pluggable databases are started. To verify the pluggable database is open, log in to the container database through sqlplus and run the following:
SQLPLUS> select name,open_mode from v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDBMM2                         MOUNTED

Notice that the PDBMM2 is only in MOUNTED state and not open. To then open the pluggable database run one of the following options:
SQLPLUS> alter pluggable database ALL open;
SQLPLUS> alter pluggable database PDBMM2 open;
SQLPLUS> alter pluggable database ALL EXCEPT PDBMM1;
Now that the database is open, connection to the pluggable database requires the same information we have needed to connect to any database, service name, port and host. If on the host the service name needs to be set or included with the login. The pluggable database connection is like logging into a normal database instance as in previous releases, so setting it as a ORACLE_SID or logging in with the service name included: username@PDBMM2.
The pluggable databases will be easily accessible through Enterprise Manager, and other tools like SQL Developer, but it is setup just like logging into a database instance in using the service and doesn't need the name of the container database, just the name of the pluggable database. From the server, logging in through SQLPLUS, this might be at first confusing if you are in the container database and trying to get to the pluggable database. It is a connect username@pdbmm2 that will get you there, or just setting the ORACLE_SID=PDBMM2 and then logging in through SQLPLUS.

10 comments:

  1. Fantastic article ! You havemade some very astute statements and I appreciate the the effort you have put into your writing. Its clear that you know what you are writing about. I am excited to read more of your sites content.

    Hadoop training

    ReplyDelete
  2. Nice blog you have. Keep the useful info coming!
    best gadget reviews

    ReplyDelete
  3. Quickbooks enterprise support Phone number Get 24/7 Quickbooks Enterprise support by reaching us at Quickbooks Enterprise Support Phone Number.We are available to resolve QuickBooks Enterprise issues through Certified QuickBooks Enterprise Support team. CAll our Quickbooks Enterprise Support number +1(833) 400-1001 and get assistance from our certified QuickBooks Technician.

    ReplyDelete
  4. Quickbooks enterprise support number +1( 833) 400-1001 is available to resolve QuickBooks Enterprise issues through QuickBooks Enterprise Support team. CAll our Quickbooks Enterprise Support number +1(833) 400-1001 and get assistance from our certified QuickBooks Technician.

    ReplyDelete
  5. Quickbooks enterprise support
    Resolve QuickBooks Enterprise issues through QuickBooks Enterprise Support team. CAll our Quickbooks Enterprise Support number +1(833) 400-1001 and get assistance from our certified QuickBooks Technician.

    ReplyDelete
  6. For much more enhanced results and optimized benefits, you can take help from experts making a call at 855-511-6911 QuickBooks Enterprise Support Phone Number Idaho

    ReplyDelete
  7. Great work. You have done an excellent job. Thank you for sharing such a knowledgeable piece of work. In case you are struggling with QuickBooks Error 15106 at any point in time, call +1-855-756-1077 and get instant assistance from experts.

    ReplyDelete
  8. Hey! Well-written blog. It is the best thing that I have read on the internet today. Moreover, if you are looking for the solution of QuickBooks Customer Service Number, visit at QuickBooks Customer Service Number to get your issues resolved quickly.

    ReplyDelete
  9. Hey! Lovely blog. Your blog contains all the details and information related to the topic. In case you are a QuickBooks user, here is good news for you. You may encounter any error like QuickBooks Error, visit at QuickBooks Customer Service Number for quick help.

    ReplyDelete