Search

OakieTags

Who's online

There are currently 0 users and 39 guests online.

Recent comments

Affiliations

Oakies Blog Aggregator

Using the Self Service Portal with PDBaaS in EM 12.1.0.4

Following on from my earlier blog post on setting up Pluggable Database as a Service (PDBaaS) in EM 12.1.0.4, this post will cover how you actually use the Self Service Portal to create on demand an empty pluggable database within the container database I created for that earlier post. You’ll no doubt be pleased to know that this post is much shorter! :)

The main reason we want to do this, of course, is to automate the more mundane database administration tasks associated with creating a database. If we can leave these sorts of tasks more to the end user, while importantly ensuring they do not have free reign to just go about willy nilly creating databases, it removes these boring tasks from our hands, and at the same time, automates the tasks to ensure correct configuration, performance and so on.

The first step is to login as a Self Service user. You may recall from my earlier post that I created a user called DBaaS, so I’m going to login as that user. I provide the username and password and click “Login”:

ssportal01

This will take me to the bog standard Welcome screen. The first thing I want to do from here is make it easier for this user to get to the right place. Enterprise Manager 12c allows you to define home pages on a per user basis, so I need to get to the Self Service Portal first, by following the path Enterprise → Cloud → Self Service Portal:

ssportal02

Now I can select “Set Current Page as My Home” from the user menu on the top right of the screen next to “Setup” so that next time I login I see this page immediately:

ssportal03

You will see a confirmation message that the Home Page has been updated. Note that the default item to manage on this Infrastructure Cloud Self Service Portal is still set to “Infrastructure – Oracle VM”, so we need to swap that to “Databases” from the Manage dropdown list (unfortunately this can’t be set as a default):

ssportal04

Now I am on the Database Cloud Self Service Portal page, which is where I can see all the information about requests that have already been made. In this case, of course, there are none, so the first thing I need to do is request a pluggable database from the Request dropdown list. Note this is the only request I have available to me at this stage due to the way we defined the DBAAS user in the setup:

ssportal05

I am asked to select a service template from a list of those that have already been defined. Again, the user has only been granted access to one, so I choose that and click “Select”:

ssportal06

Next, I am presented with the “Create Pluggable Database” screen. There are a number of options I need to populate here:

  • Request Name – the name given to the Self Service request. This is populated with a default value, but you can of course change that to something more meaningful if you like.
  • Zone – The PaaS Infrastructure Zone where the PDB’s for this request will reside. Again, this will be populated by default, based on the zones the user has been given access to. In this case, the user can only use the East Coast Zone I created in my earlier post.
  • PDB Name – obviously, the name for the PDB that will be created. Make this something recognizable, rather than something generic like PDB6.
  • Database Service Name – Again, use a meaningful service name. This will be used as the service name part of the connect string.
  • Workload Size – Defines the resources allocated to the database service name, and is also used to derive the database resource management plan. Again, in this example only one workload is defined.
  • Schedule request – The date and time that the request will be scheduled to be created (if not being created immediately), as well as the duration for its lifespan.
  • Administrator credentials – The Pluggable Database administrator account which will be created by the request for this PDB.
  • Tablespace name – The name of the tablespace to be created as part of this request.

I can enter values for all these fields (or accept the defaults) and then click “Submit”:

ssportal07

Now I am returned to the “Database Cloud Self Service Portal” page, and I can see the request I just created is running:

ssportal08

The default for this page is manual refresh, so I can change that by clicking the “View Data” dropdwon list on the right hand side of the page:

ssportal09

After a few minutes, I’ll see the new service instance listed in the “Database Service Instances” region of the Self Service Portal page:

ssportal10

Notice on the left hand part of the screen, there is additional information pertaining to notification, as well as usage values that will now have changed. Enterprise Manager uses green, yellow and red to indicate usage that is at normal, warning or critical levels. The notification section is designed to alert and notify the Self Service users of pending expirations, as well as any new service templates that have been recently published and available for use. The Usage section provides information regarding current utilization in relationship to the quotas (seen on the right end of each bar) defined by the Cloud Administrator.

If I click on the service instance name, it takes me to the pluggable database page where I can see the connection details that can be used to connect to the database to perform application development tasks:

ssportal11

And that’s it! It’s dead easy, right? Now you can concentrate on the more interesting parts of database administration life, like performance management, configuring high availaiblity, eating donuts – OK, so maybe not eating donuts. :)

Advance your career contest

Want to advance your career ?

We’ve seen DBAs become managers, managers become directors, directors become VPs and CIOs go from lesser known companies to some of the best known in the world. Why did they get promoted? Because they brought in Delphix.

Delphix increases the speed, the agility of IT often enabling development teams to go twice as fast, an increase that is unprecedented.

Companies that have this advantage will outperform the competitors.

How do you learn Delphix? Up to now you had to buy Delphix but now for a short time we will be giving a few people copies of Delphix for learning purposes.

#222222;">Here’s the deal:
#222222;" />
#222222;" />#222222;">   – We will  provide 15 smart techies with a copy of the Delphix Engine good for 6 months 
#222222;" />#222222;">   – Then, we want to see who can demonstrate the coolest or whackiest use-case for Delphix involving…
#222222;" />#222222;">        * creating virtual environments
#222222;" />#222222;">        * securing or hardening environments
#222222;" />#222222;">        * improving analytics
#222222;" />#222222;">        * improving DevOps using Puppet, Chef, or your favorite scripting package
#222222;" />#222222;">   – Demonstrate and blog about it
#222222;" />
#222222;" />#222222;">The three coolest use-cases will be awarded prizes at Oracle Open World, #222222;">featured in video interviews, and their blogs will be promoted by Delphix.

More information coming.

For now feel free to send your information, who your are, what your blog is,  to kylelf@gmail.com if you are interested in being 1 of the 15.

What is Delphix?

 


#222222;" />#222222;">Delphix is a software solution to enable thin-cloning of Unix/Windows file-systems and databases (i.e. Oracle, SQL Server, PostgreSQL, and Sybase) to enable self-service provisioning of entire application stacks, eliminating the biggest infrastructure constraints in development and testing, thus increasing the tempo of DevOps for project, and allowing dedicated environments even for the most trivial of tasks (such as testing changes for tuning a single SQL statement).  This technology also provides new alternatives for backup, high-availability, and analytics/reporting/ETL, as well as data masking to reduce the surface area of risk in non-production environments.
#222222;" />
#222222;" />#222222;">Of course, that’s just me saying all that.  I work for Delphix, so you’d expect us to say any old thing, right?
#222222;" />
#222222;" />#222222;">But it really is true, and it really changes a lot of things.  Think cold fusion.  Think sliced bread.
#222222;" />
#222222;" />#222222;">And we’re looking for a few good folks to prove it.
#222222;" />
#222222;" />#222222;">This technology is fast becoming the new norm.  Right now, shops using Delphix have a distinct competitive advantage, but a year or two from now, shops not using Delphix will be falling behind faster, because they will be at a distinct disadvantage as more people settle into the new norm.
#222222;" />
#222222;" />#222222;">The same is true for database administration skills.  As talented as you are personally, you’re only one person, and even if you did nothing but script and automate all day every day, you can’t fight the changes in the very laws of physics that virtualized storage brings.  You need to learn new tools, to stay ahead.
#222222;" />
#222222;" />Businesspeople Running Towards Finish Line Two businessmen jumping and celebrating on the beach

Implications of threaded_execution = true in 12c

I had an interesting discussion as part of my latest presentation at the UKOUG RAC CIA & Database Combined SIG. Part of my talk was about the implications of the new threaded execution model in Oracle.

Since “we do not use Windows” (except for gaming) I can’t compare the Windows thread model to the new 12c implementation on UNIX. There are however interesting implications when switching to the new model, some of which I’d like to demonstrate here. First of all, threaded execution is not enabled by default. With 12.1.0.1.3 on top of Oracle Restart you get the either all or a subset of the following background and auxiliary processes for a CDB:

[oracle@london ~]$ ps -eLf | egrep -i "UID|$ORACLE_SID"
UID        PID  PPID   LWP  C NLWP STIME TTY          TIME CMD
oracle    6041     1  6041  0    1 08:56 ?        00:00:00 ora_pmon_CDB1
oracle    6043     1  6043  0    1 08:56 ?        00:00:00 ora_psp0_CDB1
oracle    6045     1  6045  2    1 08:56 ?        00:00:08 ora_vktm_CDB1
oracle    6049     1  6049  0    1 08:56 ?        00:00:00 ora_gen0_CDB1
oracle    6051     1  6051  0    1 08:56 ?        00:00:00 ora_mman_CDB1
oracle    6055     1  6055  0    1 08:56 ?        00:00:00 ora_diag_CDB1
oracle    6057     1  6057  0    1 08:56 ?        00:00:00 ora_dbrm_CDB1
oracle    6059     1  6059  0    1 08:56 ?        00:00:00 ora_dia0_CDB1
oracle    6061     1  6061  0    1 08:56 ?        00:00:00 ora_dbw0_CDB1
oracle    6063     1  6063  0    1 08:56 ?        00:00:00 ora_lgwr_CDB1
oracle    6065     1  6065  0    1 08:56 ?        00:00:00 ora_ckpt_CDB1
oracle    6067     1  6067  0    1 08:56 ?        00:00:00 ora_lg00_CDB1
oracle    6069     1  6069  0    1 08:56 ?        00:00:00 ora_lg01_CDB1
oracle    6071     1  6071  0    1 08:56 ?        00:00:00 ora_smon_CDB1
oracle    6073     1  6073  0    1 08:56 ?        00:00:00 ora_reco_CDB1
oracle    6075     1  6075  0    1 08:56 ?        00:00:00 ora_lreg_CDB1
oracle    6077     1  6077  0    1 08:56 ?        00:00:00 ora_rbal_CDB1
oracle    6079     1  6079  0    1 08:56 ?        00:00:00 ora_asmb_CDB1
oracle    6081     1  6081  0    1 08:56 ?        00:00:00 ora_mmon_CDB1
oracle    6083     1  6083  0    1 08:56 ?        00:00:00 ora_mmnl_CDB1
grid      6086     1  6086  0    1 08:56 ?        00:00:00 oracle+ASM_asmb_cdb1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle    6087     1  6087  0    1 08:56 ?        00:00:00 ora_d000_CDB1
oracle    6089     1  6089  0    1 08:56 ?        00:00:00 ora_s000_CDB1
oracle    6092     1  6092  0    1 08:56 ?        00:00:00 ora_mark_CDB1
oracle    6094     1  6094  0    1 08:56 ?        00:00:00 ora_ocf0_CDB1
oracle    6109     1  6109  0    1 08:56 ?        00:00:00 ora_o000_CDB1
grid      6111     1  6111  0    1 08:56 ?        00:00:00 oracle+ASM_ocf0_cdb1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
grid      6113     1  6113  0    1 08:56 ?        00:00:00 oracle+ASM_o000_cdb1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle    6118     1  6118  0    1 08:56 ?        00:00:00 ora_nss2_CDB1
oracle    6148     1  6148  0    1 08:56 ?        00:00:01 ora_p000_CDB1
oracle    6150     1  6150  0    1 08:56 ?        00:00:02 ora_p001_CDB1
oracle    6154     1  6154  0    1 08:56 ?        00:00:00 ora_o001_CDB1
grid      6156     1  6156  0    1 08:56 ?        00:00:00 oracle+ASM_o001_cdb1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle    6158     1  6158  0    1 08:56 ?        00:00:00 ora_tmon_CDB1
oracle    6160     1  6160  0    1 08:56 ?        00:00:00 ora_arc0_CDB1
oracle    6162     1  6162  0    1 08:56 ?        00:00:00 ora_arc1_CDB1
oracle    6164     1  6164  0    1 08:56 ?        00:00:00 ora_arc2_CDB1
oracle    6166     1  6166  0    1 08:56 ?        00:00:00 ora_arc3_CDB1
oracle    6169     1  6169  0    1 08:57 ?        00:00:00 ora_tt00_CDB1
oracle    6171     1  6171  0    1 08:57 ?        00:00:00 ora_smco_CDB1
oracle    6176     1  6176  0    1 08:57 ?        00:00:00 ora_aqpc_CDB1
oracle    6180     1  6180  0    1 08:57 ?        00:00:00 ora_p002_CDB1
oracle    6182     1  6182  0    1 08:57 ?        00:00:00 ora_p003_CDB1
oracle    6184     1  6184  0    1 08:57 ?        00:00:00 ora_p004_CDB1
oracle    6186     1  6186  0    1 08:57 ?        00:00:00 ora_p005_CDB1
oracle    6188     1  6188  0    1 08:57 ?        00:00:00 ora_p006_CDB1
oracle    6190     1  6190  0    1 08:57 ?        00:00:00 ora_p007_CDB1
oracle    6192     1  6192  0    1 08:57 ?        00:00:00 ora_qm02_CDB1
oracle    6194     1  6194  0    1 08:57 ?        00:00:00 ora_qm00_CDB1
oracle    6196     1  6196  0    1 08:57 ?        00:00:00 ora_q002_CDB1
oracle    6200     1  6200  0    1 08:57 ?        00:00:00 ora_q004_CDB1
oracle    6202     1  6202  0    1 08:57 ?        00:00:00 ora_q005_CDB1
oracle    6204     1  6204  0    1 08:57 ?        00:00:00 ora_q006_CDB1
oracle    6206     1  6206  0    1 08:57 ?        00:00:00 ora_q007_CDB1
oracle    6208     1  6208  0    1 08:57 ?        00:00:00 ora_q008_CDB1
oracle    6210     1  6210  0    1 08:57 ?        00:00:00 ora_q009_CDB1
oracle    6212     1  6212  0    1 08:57 ?        00:00:00 ora_q00a_CDB1
oracle    6292     1  6292  0    1 08:57 ?        00:00:00 ora_w000_CDB1
oracle    6318     1  6318  0    1 08:57 ?        00:00:00 ora_cjq0_CDB1
oracle    6320     1  6320  0    1 08:57 ?        00:00:00 oracleCDB1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle    6322     1  6322  0    1 08:57 ?        00:00:01 oracleCDB1 (LOCAL=NO)
oracle    6324     1  6324  0    1 08:57 ?        00:00:00 oracleCDB1 (LOCAL=NO)
oracle    6326     1  6326  0    1 08:57 ?        00:00:00 oracleCDB1 (LOCAL=NO)
oracle    6407     1  6407  0    1 08:57 ?        00:00:00 oracleCDB1 (LOCAL=NO)
oracle    6438     1  6438  0    1 08:57 ?        00:00:00 ora_p008_CDB1
oracle    6440     1  6440  0    1 08:57 ?        00:00:00 ora_p009_CDB1
oracle    6442     1  6442  0    1 08:57 ?        00:00:01 oracleCDB1 (LOCAL=NO)
oracle    6490     1  6490  0    1 08:58 ?        00:00:00 ora_p00a_CDB1
oracle    6492     1  6492  0    1 08:58 ?        00:00:00 ora_p00b_CDB1
oracle    6640  5653  6640  0    1 09:01 pts/0    00:00:00 egrep -i UID|CDB1

Quite a few more than in 9i it seems. Most of the background processes are documented in the database reference. As you can see, there are only processes (-> NLWP = 1), no threads. If you wondered about the strange output, bear with me, there is a reason I chose those flags to the ps command.

The change with the threaded model

Let’s enable threaded execution. It’s simple:

SYS@CDB$ROOT> alter system set threaded_execution=true scope=spfile;

System altered.

SYS@CDB$ROOT> shutdown immediate

Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@CDB$ROOT> startup
ORA-01017: invalid username/password; logon denied

There’s the first surprise. Not to worry, it’s just that OS level authentication doesn’t work anymore, but you can continue by providing your username and password:

SYS@CDB$ROOT> conn sys as sysdba
Enter password:
Connected.
SYS@CDB$ROOT> alter database mount;

Database altered.

SYS@CDB$ROOT> alter database open;

Database altered.

SYS@CDB$ROOT>

What does that mean for our processes? First let’s use the old way of checking for Oracle processes using just ps -ef:

[oracle@london ~]$ ps -ef | egrep -i "UID|$ORACLE_SID"
UID        PID  PPID  C STIME TTY          TIME CMD
oracle    7138     1  0 09:04 ?        00:00:00 ora_pmon_CDB1
oracle    7140     1  0 09:04 ?        00:00:00 ora_psp0_CDB1
oracle    7142     1  2 09:04 ?        00:00:05 ora_vktm_CDB1
oracle    7146     1  0 09:04 ?        00:00:01 ora_u004_CDB1
oracle    7152     1  4 09:04 ?        00:00:08 ora_u005_CDB1
oracle    7158     1  0 09:04 ?        00:00:00 ora_dbw0_CDB1
grid      7173     1  0 09:04 ?        00:00:00 oracle+ASM_asmb_cdb1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
grid      7202     1  0 09:04 ?        00:00:00 oracle+ASM_ocf0_cdb1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
grid      7226     1  0 09:04 ?        00:00:00 oracle+ASM_o000_cdb1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
grid      7229     1  0 09:04 ?        00:00:00 oracle+ASM_o001_cdb1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
grid      7401     1  0 09:06 ?        00:00:00 oracle+ASM_o002_cdb1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle    7407     1  0 09:06 ?        00:00:00 oracleCDB1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle    7494     1  0 09:06 ?        00:00:00 oracleCDB1 (LOCAL=NO)
oracle    7526     1  0 09:07 ?        00:00:00 oracleCDB1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle    7573     1  1 09:07 ?        00:00:00 oracleCDB1 (LOCAL=NO)
oracle    7575     1  3 09:07 ?        00:00:02 oracleCDB1 (LOCAL=NO)
oracle    7578     1  0 09:07 ?        00:00:00 oracleCDB1 (LOCAL=NO)
oracle    7592     1  0 09:07 ?        00:00:00 oracleCDB1 (LOCAL=NO)
oracle    7594     1  0 09:07 ?        00:00:00 oracleCDB1 (LOCAL=NO)
oracle    7596     1  0 09:07 ?        00:00:00 oracleCDB1 (LOCAL=NO)
oracle    7691  5653  0 09:08 pts/0    00:00:00 egrep -i UID|CDB1
[oracle@london ~]$

The list is considerably shorter, and you will notice some strange process names: u004 and u005. If you squint you see that PMON, PSP0, VKTM and DBW0 are still processes. I am checking for threads (or light weight processes as they are also called) in the next step:

[oracle@london ~]$ ps -eLf | egrep -i "UID|$ORACLE_SID"
UID        PID  PPID   LWP  C NLWP STIME TTY          TIME CMD
oracle    7138     1  7138  0    1 09:04 ?        00:00:00 ora_pmon_CDB1
oracle    7140     1  7140  0    1 09:04 ?        00:00:00 ora_psp0_CDB1
oracle    7142     1  7142  2    1 09:04 ?        00:00:11 ora_vktm_CDB1
oracle    7146     1  7146  0   13 09:04 ?        00:00:00 ora_u004_CDB1
oracle    7146     1  7147  0   13 09:04 ?        00:00:00 ora_u004_CDB1
oracle    7146     1  7148  0   13 09:04 ?        00:00:00 ora_u004_CDB1
oracle    7146     1  7149  0   13 09:04 ?        00:00:00 ora_u004_CDB1
oracle    7146     1  7155  0   13 09:04 ?        00:00:00 ora_u004_CDB1
oracle    7146     1  7159  0   13 09:04 ?        00:00:00 ora_u004_CDB1
oracle    7146     1  7160  0   13 09:04 ?        00:00:00 ora_u004_CDB1
oracle    7146     1  7161  0   13 09:04 ?        00:00:00 ora_u004_CDB1
oracle    7146     1  7162  0   13 09:04 ?        00:00:00 ora_u004_CDB1
oracle    7146     1  7163  0   13 09:04 ?        00:00:00 ora_u004_CDB1
oracle    7146     1  7165  0   13 09:04 ?        00:00:00 ora_u004_CDB1
oracle    7146     1  7166  0   13 09:04 ?        00:00:00 ora_u004_CDB1
oracle    7146     1  7167  0   13 09:04 ?        00:00:00 ora_u004_CDB1
oracle    7152     1  7152  0   46 09:04 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7153  0   46 09:04 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7154  0   46 09:04 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7156  0   46 09:04 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7164  0   46 09:04 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7168  0   46 09:04 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7169  0   46 09:04 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7170  0   46 09:04 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7172  0   46 09:04 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7174  0   46 09:04 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7175  0   46 09:04 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7224  0   46 09:04 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7227  0   46 09:04 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7249  0   46 09:05 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7361  0   46 09:06 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7362  0   46 09:06 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7365  0   46 09:06 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7366  0   46 09:06 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7367  0   46 09:06 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7396  0   46 09:06 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7397  0   46 09:06 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7404  0   46 09:06 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7405  0   46 09:06 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7416  0   46 09:06 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7418  0   46 09:06 ?        00:00:01 ora_u005_CDB1
oracle    7152     1  7419  0   46 09:06 ?        00:00:02 ora_u005_CDB1
oracle    7152     1  7420  0   46 09:06 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7421  0   46 09:06 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7422  0   46 09:06 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7423  0   46 09:06 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7424  0   46 09:06 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7425  0   46 09:06 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7426  0   46 09:06 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7427  0   46 09:06 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7428  0   46 09:06 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7430  0   46 09:06 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7431  0   46 09:06 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7432  0   46 09:06 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7433  0   46 09:06 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7434  0   46 09:06 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7435  0   46 09:06 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7576  0   46 09:07 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7618  0   46 09:07 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7619  0   46 09:07 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7696  0   46 09:08 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7697  0   46 09:08 ?        00:00:00 ora_u005_CDB1
oracle    7158     1  7158  0    1 09:04 ?        00:00:00 ora_dbw0_CDB1
grid      7173     1  7173  0    1 09:04 ?        00:00:00 oracle+ASM_asmb_cdb1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
grid      7226     1  7226  0    1 09:04 ?        00:00:00 oracle+ASM_o000_cdb1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
grid      7229     1  7229  0    1 09:04 ?        00:00:00 oracle+ASM_o001_cdb1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
grid      7401     1  7401  0    1 09:06 ?        00:00:00 oracle+ASM_o002_cdb1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle    7494     1  7494  0    1 09:06 ?        00:00:00 oracleCDB1 (LOCAL=NO)
oracle    7526     1  7526  0    1 09:07 ?        00:00:00 oracleCDB1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle    7573     1  7573  0    1 09:07 ?        00:00:00 oracleCDB1 (LOCAL=NO)
oracle    7575     1  7575  1    1 09:07 ?        00:00:02 oracleCDB1 (LOCAL=NO)
oracle    7578     1  7578  0    1 09:07 ?        00:00:00 oracleCDB1 (LOCAL=NO)
oracle    7592     1  7592  0    1 09:07 ?        00:00:00 oracleCDB1 (LOCAL=NO)
oracle    7594     1  7594  0    1 09:07 ?        00:00:00 oracleCDB1 (LOCAL=NO)
oracle    7596     1  7596  0    1 09:07 ?        00:00:00 oracleCDB1 (LOCAL=NO)
oracle    7810     1  7810  0    1 09:10 ?        00:00:00 oracleCDB1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle    7840  5653  7840  0    1 09:11 pts/0    00:00:00 egrep -i UID|CDB1

This is where it’s interesting. First let’s match this to the database, focusing on the background processes.

SYS@CDB$ROOT> select pname, pid, sosid, spid, stid, execution_type
  2  from v$process where background = 1
  3  order by pname
  4  /

PNAME        PID SOSID                    SPID                     STID                     EXECUTION_
----- ---------- ------------------------ ------------------------ ------------------------ ----------
AQPC          43 7152_7416                7152                     7416                     THREAD
ARC0          35 7152_7362                7152                     7362                     THREAD
ARC1          36 7152_7365                7152                     7365                     THREAD
ARC2          37 7152_7366                7152                     7366                     THREAD
ARC3          38 7152_7367                7152                     7367                     THREAD
ASMB          22 7146_7167                7146                     7167                     THREAD
CJQ0          68 7152_7576                7152                     7576                     THREAD
CKPT          15 7146_7160                7146                     7160                     THREAD
DBRM          11 7146_7155                7146                     7155                     THREAD
DBW0          13 7158                     7158                     7158                     PROCESS
DIA0          12 7152_7156                7152                     7156                     THREAD
DIAG           9 7152_7154                7152                     7154                     THREAD
GEN0           5 7146_7148                7146                     7148                     THREAD
LG00          16 7146_7161                7146                     7161                     THREAD
LG01          18 7146_7163                7146                     7163                     THREAD
LGWR          14 7146_7159                7146                     7159                     THREAD
LREG          20 7146_7165                7146                     7165                     THREAD
MARK          28 7152_7175                7152                     7175                     THREAD
MMAN           7 7146_7149                7146                     7149                     THREAD
MMNL          24 7152_7169                7152                     7169                     THREAD
MMON          23 7152_7168                7152                     7168                     THREAD
NSS2          33 7152_7249                7152                     7249                     THREAD
O002          40 7152_7397                7152                     7397                     THREAD
PMON           2 7138                     7138                     7138                     PROCESS
PSP0           3 7140                     7140                     7140                     PROCESS
Q002          54 7152_7428                7152                     7428                     THREAD
Q004          56 7152_7430                7152                     7430                     THREAD
Q005          57 7152_7431                7152                     7431                     THREAD
Q006          58 7152_7432                7152                     7432                     THREAD
Q007          59 7152_7433                7152                     7433                     THREAD
Q008          60 7152_7434                7152                     7434                     THREAD
Q009          61 7152_7435                7152                     7435                     THREAD
QM00          53 7152_7427                7152                     7427                     THREAD
QM02          44 7152_7426                7152                     7426                     THREAD
RBAL          21 7146_7166                7146                     7166                     THREAD
RECO          19 7152_7164                7152                     7164                     THREAD
SCMN           6 7146_7146                7146                     7146                     THREAD
SCMN          10 7152_7152                7152                     7152                     THREAD
SMCO          41 7152_7404                7152                     7404                     THREAD
SMON          17 7146_7162                7146                     7162                     THREAD
TMON          34 7152_7361                7152                     7361                     THREAD
TT00          39 7152_7396                7152                     7396                     THREAD
VKTM           4 7142                     7142                     7142                     PROCESS
W000          42 7152_7405                7152                     7405                     THREAD

44 rows selected.

Here you clearly see which one of the background processes is a thread, and which isn’t. The threads will have multiple STIDs per SPID, or thread IDs to the process:

SYS@CDB$ROOT> select count(spid),spid,execution_type from v$process where background = 1 group by spid, execution_type;

COUNT(SPID) SPID                     EXECUTION_
----------- ------------------------ ----------
          1 7158                     PROCESS
         12 7146                     THREAD
          1 7138                     PROCESS
          1 7140                     PROCESS
          1 7142                     PROCESS
         29 7152                     THREAD

6 rows selected.

Circling back to the OS level for SPID 7146-can you see the threads there as well? You can:

[oracle@london ~]$ ps -eLf | egrep -i "UID|7146"
UID        PID  PPID   LWP  C NLWP STIME TTY          TIME CMD
oracle    7146     1  7146  0   13 09:04 ?        00:00:00 ora_u004_CDB1
oracle    7146     1  7147  0   13 09:04 ?        00:00:00 ora_u004_CDB1
oracle    7146     1  7148  0   13 09:04 ?        00:00:00 ora_u004_CDB1
oracle    7146     1  7149  0   13 09:04 ?        00:00:00 ora_u004_CDB1
oracle    7146     1  7155  0   13 09:04 ?        00:00:00 ora_u004_CDB1
oracle    7146     1  7159  0   13 09:04 ?        00:00:00 ora_u004_CDB1
oracle    7146     1  7160  0   13 09:04 ?        00:00:00 ora_u004_CDB1
oracle    7146     1  7161  0   13 09:04 ?        00:00:00 ora_u004_CDB1
oracle    7146     1  7162  0   13 09:04 ?        00:00:00 ora_u004_CDB1
oracle    7146     1  7163  0   13 09:04 ?        00:00:00 ora_u004_CDB1
oracle    7146     1  7165  0   13 09:04 ?        00:00:00 ora_u004_CDB1
oracle    7146     1  7166  0   13 09:04 ?        00:00:00 ora_u004_CDB1
oracle    7146     1  7167  0   13 09:04 ?        00:00:00 ora_u004_CDB1
oracle    8156  6876  8156  0    1 09:19 pts/2    00:00:00 egrep -i UID|7146
[oracle@london ~]$

But what about user sessions?

Let’s connect to the database (a PDB in this case) as a user.

[oracle@london ~]$ sqlplus martin@localhost/pdb4
...
SQL*Plus: Release 12.1.0.1.0 Production on Fri Jul 18 09:22:39 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Last Successful login time: Fri Jul 18 2014 09:21:27 -04:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

MARTIN@PDB4> select sys_context('userenv','sid') from dual;

SYS_CONTEXT('USERENV','SID')
--------------------------------------------------------------------------------
280
MARTIN@PDB4>

I can see the session from the CDB root:

SYS@CDB$ROOT> select s.username, s.sid, s.serial#, s.con_id, p.spid, p.sosid, p.stid, p.execution_type
  2  from v$session s, v$process p
  3  where s.sid = 280 and s.paddr = p.addr;

USERNAME          SID    SERIAL#     CON_ID SPID                     SOSID                    STID                     EXECUTION_
---------- ---------- ---------- ---------- ------------------------ ------------------------ ------------------------ ----------
MARTIN            280        407          4 8672                     8672                     8672                     PROCESS

SYS@CDB$ROOT>

Interestingly – as the output shows – user processes seem to remain UNIX processes (there is a twist to that too, bear with me). Remember that this process has been created using a net service and the EZConnect syntax. What about my session that bypasses the net*8 layer? I connected using sqlplus sys as sysdba:

SYS@CDB$ROOT> select sys_context('userenv','sid') from dual;

SYS_CONTEXT('USERENV','SID')
------------------------------------------------------------------------------------------------------------------------------------------------------
249

SYS@CDB$ROOT> select s.username, s.sid, s.serial#, s.con_id, p.spid, p.sosid, p.stid, p.execution_type
  2  from v$session s, v$process p
  3  where s.sid = 249 and s.paddr = p.addr;

USERNAME          SID    SERIAL#     CON_ID SPID                     SOSID                    STID                     EXECUTION_
---------- ---------- ---------- ---------- ------------------------ ------------------------ ------------------------ ----------
SYS               249        685          1 7152                     7152_8696                8696                     THREAD

SYS@CDB$ROOT>

Now that’s a thread. Surprised? Let’s test on a non CDB. First I try to connect by bypassing the listener:

[oracle@london ~]$ sqlplus martin

SQL*Plus: Release 12.1.0.1.0 Production on Fri Jul 18 10:34:58 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Enter password:
Last Successful login time: Fri Jul 18 2014 10:33:25 -04:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

MARTIN@NCDB> select sys_context('userenv','sid') from dual;

SYS_CONTEXT('USERENV','SID')
--------------------------------------------------------------------------------
249

MARTIN@NCDB>
-- in a different session
SYS@NCDB> select s.username, s.sid, s.serial#, s.con_id, p.spid, p.sosid, p.stid, p.execution_type
  2  from v$session s, v$process p
  3  where s.sid = 249 and s.paddr = p.addr;

USERNAME                              SID    SERIAL#     CON_ID SPID                     SOSID                    STID                     EXECUTION_
------------------------------ ---------- ---------- ---------- ------------------------ ------------------------ ------------------------ ----------
MARTIN                                249         13          0 14634                    14634_15337              15337                    THREAD

This session is a thread. Now using the listener:

[oracle@london ~]$ tnsping ncdb

TNS Ping Utility for Linux: Version 12.1.0.1.0 - Production on 18-JUL-2014 10:36:33

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = london.example.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = NCDB)))
OK (0 msec)
[oracle@london ~]$ sqlplus martin@ncdb

SQL*Plus: Release 12.1.0.1.0 Production on Fri Jul 18 10:36:36 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Enter password:
Last Successful login time: Fri Jul 18 2014 10:36:18 -04:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

MARTIN@NCDB> select sys_context('userenv','sid') from dual;

SYS_CONTEXT('USERENV','SID')
--------------------------------------------------------------------------------
249

And what’s the guess?

SYS@NCDB> r
  1  select s.username, s.sid, s.serial#, s.con_id, p.spid, p.sosid, p.stid, p.execution_type
  2  from v$session s, v$process p
  3* where s.sid = 249 and s.paddr = p.addr

USERNAME                              SID    SERIAL#     CON_ID SPID                     SOSID                    STID                     EXECUTION_
------------------------------ ---------- ---------- ---------- ------------------------ ------------------------ ------------------------ ----------
MARTIN                                249         17          0 15507                    15507                    15507                    PROCESS

You were right-a process. But so far I haven’t shown you my listener.ora file. So far in the testing it didn’t use DEDICATED_THROUGH_BROKER_listener = ON. Let’s set this, and reload the listener. Here is the complete example:

[oracle@london ~]$ sqlplus martin@ncdb

SQL*Plus: Release 12.1.0.1.0 Production on Fri Jul 18 10:45:18 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Enter password:
Last Successful login time: Fri Jul 18 2014 10:36:38 -04:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

MARTIN@NCDB> select sys_context('userenv','sid') from dual;

SYS_CONTEXT('USERENV','SID')
--------------------------------------------------------------------------------
14

-- and in another session

SYS@NCDB> select s.username, s.sid, s.serial#, s.con_id, p.spid, p.sosid, p.stid, p.execution_type
  2  from v$session s, v$process p
  3  where s.sid = 14 and s.paddr = p.addr;

USERNAME               SID    SERIAL#     CON_ID SPID                     SOSID                    STID                     EXECUTION_
--------------- ---------- ---------- ---------- ------------------------ ------------------------ ------------------------ ----------
MARTIN                  14         25          0 14634                    14634_16287              16287                    THREAD

The listener parameter DEDICATED_THROUGH_BROKER_
allows you to create user sessions as threads. And now to wrap up, how do you kill that session? On my Oracle Linux 6.5 system I killed the LWP:

[oracle@london ~]$ ps -eLf | egrep -i "UID|16287"
UID        PID  PPID   LWP  C NLWP STIME TTY          TIME CMD
oracle   14634     1 16287  0   32 10:45 ?        00:00:00 ora_u005_NCDB
oracle   16531 16503 16531  1    1 10:48 pts/4    00:00:00 egrep -i UID|16287
[oracle@london ~]$ kill -9 16287

Which terminates my session:

MARTIN@NCDB> select user from dual
  2  /
select user from dual
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 14634 Thread ID: 16287
Session ID: 14 Serial number: 25

My database stayed up and running. Please do NEVER EVER kill the SPID when threaded_execution is enabled!!! You might bring the database down.

Disclaimer

As with everything on this blog, don’t take it literally-test it! Just because it worked for me on my VM with the specific set of packages does not mean this is applicable for other Linux or even Solaris/AIX!

Site Maintenance Complete!

It looks like the site maintenance is complete and from my perspective the DNS changes have gone through.

If you go to the homepage and see a message called “Site Maintenance” in the “Site News” section, it means you are being directed to the new server. If you don’t see that it means you are still being directed to the old server and you won’t be able to read this. :)

I guess it will take a few hours for the DNS changes to propagate. Last time I moved the site it took a couple of days to complete for everyone.

Cheers

Tim…


Site Maintenance Complete! was first posted on July 19, 2014 at 11:40 am.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

Database Consolidation (PDBaaS) in EM 12.1.0.4 – Setup

Introduction

This is an almighty long post, because it walks you step-by-step through the setup of Pluggable Database as a Service (PDBaaS) in Enterprise Manager 12.1.0.4 so there are a lot of screenshots. The actual setup doesn’t take long at all, so don’t be put off by the length of the post. :) Just as a bit of enticement, there is also a way this can be done without going through the process I outline below, but you will need to understand this process first. So persevere with this, and I’ll reward you with another separate post of how this can done more simply! ;)

With the introduction of Oracle Database 12c multitenant architecture, the latest consolidation model to look at is PDBaaS. A pluggable database (PDB), which can be described as portable sets of schemas, schema objects, and related structures that appear logically to an application as a separate database, provides enhanced database consolidation. Users can perform day-to-day actions like provisioning, monitoring and backup, all from a single self-service console. This is complemented by metering and showback / chargeback capabilities that provide visibility and transparency into resource consumption by each user.

The steps to set up PDBaaS can be broadly outlined as:

  1. Enable DBaaS, including setting up the Software Library, privileges and users.
  2. Set up one or more PaaS Infrastructure Zones.
  3. Create a database pool for PDBaaS
  4. Configure request settings.
  5. Define quotas for each self service user.
  6. Create a database provisioning profile. This step is optional and is not required if you are creating an empty pluggable database.
  7. Create a service template, either for an empty pluggable database (i.e. created with an empty schema), or a pluggable database from a profile: (where you can import schemas from a database provisioning profile, including applications with data such as eBusiness applications).
  8. Configure Chargeback.
  9. While deploying a database, select the service template that you have created.

Let’s look at the details.

PDBaaS Setup

Cloud administrators configure the PDBaaS cloud, define cloud governance with policies and quotas, expose it to certain users, and decide the total amount of resources each user can reserve before the Self Service Portal can be used.

The first step in the process requires the Software Library to be configured. The Software Library is a repository that stores software patches, virtual appliance images, reference gold images, application software, and their associated directive scripts. It allows different versions, maturity levels, and states of entities. The software entities can be automatically mass-deployed to provision software, software updates, and servers using Enterprise Manager in a reliable and repeatable manner. These provisioning operations, which are unattended and can be scheduled, lead to substantial cost savings.

Besides acting as a repository for certified software entities, the Software Library is a logical interface between the deployment models and the automation framework required to perform a large number of patching and provisioning tasks. To configure the storage location for the Software Library, follow the path Setup → Provisioning and Patching → Software Library and set a location for the OMS Shared File System, as shown below:

pdbaas01

Defining Roles and Assigning Users

Roles are named groups of related system and object privileges. You can create roles and then assign them to users and to other roles. You can assign any of the existing roles and the associated privileges to a new role.

When creating Database Zones and Service Templates, selective access can only be granted to custom roles. The Self Service Portal is intended for end-users to be able to provision and manage their own cloud services. As such, end-users need only access to the Self Service Portal and the resources they are assigned. Such capabilities are inherent in the pre-defined EM_SSA_USER role. Since pre-defined roles cannot be assigned to Database Zones and Service Templates, you need to create a custom Cloud User role based on the standard EM_SSA_USER role. To do this, follow the path Setup → Security → Roles:

pdbaas03

As of the date I am writing this (mid-July 2014), don’t use the “Create Like” button in EM12.1.0.4 to create a role like the EM_SSA_USER role. It maps the wrong role to the role you are creating. I’ve logged a bug on this but don’t have a FixBy date yet. Click the Create button instead to workaround this:

pdbaas04

Provide a name such as “DBaaS_Cloud_Users” and description and click “Next”:

pdbaas05

Select the EM_SSA_USER role, then click the “Move” button to move the role to the list of Selected Roles, and then click “Review” (we don’t need the remaining wizard steps so they can be skipped):

pdbaas06

Check all the details are correct and click “Finish” to create the role:

pdbaas07

You should then see a confirmation that the role has been created successfully:

pdbaas08

It is highly recommended that each end-user should have its own Cloud User credentials, to allow for effective monitoring of services and resource utilization, so the next step is to create an end-user and grant it the DBaaS_Cloud_Users role. To do this, follow the path Setup → Security → Administrators:

pdbaas09

You can either select another administrator and click “Create Like” or just click “Create”. I find it easier to do the latter as you don’t need to remove any extraneous privileges that “Create Like” can bring with it:

pdbaas10

Provide a username (I used DBAAS in this example) and password, and click “Next”:

pdbaas11

Select the “DBAAS_CLOUD_USERS” role you just created, click the “Move” button to add it to the Selected Roles list and click “Review” (again, the remaining wizard steps are not needed):

pdbaas12

Review the details to make sure they are correct, then click “Finish” to create the DBAAS administrator:

pdbaas13

You should see a confirmation message that the administrator was created successfully:

pdbaas14

Creating a PaaS Infrastructure Zone

Before you enable or setup DBaaS or MWaaS, you must create a PaaS Infrastructure Zone that allows you to define the placement policy constraints for a specified set of targets and the users to whom this zone will be available. To create a PaaS Infrastructure Zone, you must login using an account that has been granted the EM_CLOUD_ADMINISTRATOR role. Once you’ve done this, follow the path To do this, follow the path Setup → Cloud → PaaS Infrastructure Zone:

pdbaas15

On the PaaS Infrastructure Zone page, click the “Create” button:

pdbaas16

Provide a name and optionally a description for the zone, determine placement policy constraints per host (i.e. maximum CPU utilization and maximum memory allocation allowed) and click “Next”:

pdbaas17

If you already have a named credential defined for the hosts you are about to add, select it from the “Named Credential” drop-down list or click the “+” sign to create a new named credential:

pdbaas18

Enter the username and password for the named credential, optionally provide Run Privilege (such as sudo), give the named credential a meaningful name and click “OK”:

pdbaas19

Now add the host(s) you will be putting in this PaaS infrastructure zone by clicking “Add”:

pdbaas20

You can either search for the host name(s) or just select them from the list. This is a multi-select screen so you can select multiple rows by holding down the Shift key as you select, then click “Select”:

pdbaas21

Now that you’ve added one or more hosts, you can select the named credential you defined earlier and click “Test Credential” to check it works (actually, I think this screen should be redesigned and the Credentials part put AFTER the Targets part, but let’s just work with what we have!):

pdbaas22

You should get an informational message that the credential test succeeded (if not, you will need to exit the Create PaaS Infrastructure Zone wizard and follow the path Setup → Security → Named Credentials to fix it, so it’s best to get it right the first time!). Click “OK” to acknowledge the message:

pdbaas23

Click “Next” to move to step 3 of the wizard:

pdbaas24

The zone can be made available to a restricted set of users by selecting the role(s) that can access it. We need to add the role we created earlier, so click the “Add” button:

pdbaas25

Select the DBAAS_CLOUD_USERS role, and then click “Select”:

pdbaas26

Click the “Next” button:

pdbaas27

Finally, review the details and click “Submit” to create the PaaS infrastructure zone:

pdbaas28

You should now see a message that the PaaS Infrastructure Zone was created successfully:

pdbaas29

Creating a Database Pool

A database pool is a collection of servers or clusters with pre-installed database software. Each server in a database zone has identical platform and database versions. For servers that support multiple ORACLE_HOME’s with different versions, a separate database zone must be created for each database version.

To create a database pool, follow the path Setup → Cloud → Database to go to the Database Cloud Self Service Portal Setup page:

pdbaas30

From here, select “For Pluggable Database” from the “Create…” dropdown:

pdbaas31

Provide a name and optionally a description for the new pool. If you already have named credentials defined for this environment, you can simply select them from the dropdown lists to the right, but if not, click the “+” sign to create a named credential for the host:

pdbaas32

Enter a username and password for the credential, optionally provide the run privilege (e.g. sudo), give the credential a meaningful name and click “OK”:

pdbaas33

Likewise you can provide a named credential for the Grid Infrastructure (if you are going to use it) and for the database. You can also specify a container database wallet password if you want to support encryption for the pluggable database that will be created (I normally provide this regardless because it saves coming back to redo this later if you change your mind). Next, we need to add one or more container databases to the pool from a single PaaS Infrastructure Zone. The filters we select here cannot be changed once the pool is created, so select these carefully. I’ve chosen the East Coast Zone we just created, a target type of database instance (the other choice is a RAC environment but this example is being built in non-clustered configurations), and set the platform and database version correctly. Then click “Add”:

pdbaas34

The “Select and Add Targets” pop-up provides a list of already existing databases, so I simply select the cdb1 container database and click “Select”:

pdbaas35

Click the “Next” button:

pdbaas36

If you want to set maximum ceilings for resource utilization, you can do it on this screen. I’m just going to leave it at the defaults in this example and select “Submit”:

pdbaas37

You should see a message saying the pool has been created successfully. Next I want to add some settings to restrict the scope for database requests, so I click “Request Settings”:

pdbaas38

In this case I want to change the request purging duration (the period of time after which completed creation requests will be purged from the repository) to 3 days, so I change that and click “Apply”:

pdbaas39

Again, you should see a confirmation message. Next, I want to add quota – the aggregate amount of resources that can be granted to each self service user belonging to a certain role. This quota only applies to databases provisioned through the Self Service Portal. To do this, click “Quotas”:

pdbaas40

Click on the “Create…” button to create a new quota (or use an already existing one):

pdbaas41

Provide a role name and quota for the amount of memory and storage, as well as the number of database requests, schema service requests and pluggable database service requests, then click “OK”:

pdbaas42

Next we want to configure profile and service template definitions that can be used by self service users to provision databases in selected zones. Click “Profiles and Service Templates”:

pdbaas43

A database provisioning profile is not needed when creating an empty pluggable database, but I’ll walk you quickly through the steps anyway. Click the “Create…” button to create a database provisioning profile:

pdbaas44

Click the magnifying glass to search for a reference target:

pdbaas45

Select the cdb1 container database and click “Select”:

pdbaas46

Provide the relevant named credentials and click “Next”:

pdbaas47

Since we are creating a profile with structure only, step 2 of the wizard is skipped. On step 3, you can change the profile name, path and the scheduling, but for now I’m just going to click “Next”:

pdbaas48

Review the information and click “Submit” to create the profile:

pdbaas49

You can change the page to refresh every 30 seconds on the right, and you should fairly quickly see that the profile has been created successfully:

pdbaas50

Now we need to create a service template so follow the path Setup → Cloud → Database to go to the Database Cloud Self Service Portal Setup page again, then click “Profiles and Service Templates” and then click “For Pluggable Database” from the “Create…” dropdown:

pdbaas51

We’re going to create a new service temple to provision an empty pluggable database, so provide a name and optionally a description, select “Create Empty Pluggable Database” if not already selected then click “Add”:

pdbaas52

In this case I want to use the East Coast PaaS Infrastructure Zone I created earlier so select that row then click “Select”:

pdbaas53

Now that the zone is added I also want to assign a pool to the template by clicking “Assign Pool”:

pdbaas54

Select the “DB12c Pool” created earlier, then click “Select”:

pdbaas55

Now I want to add a prefix to the pluggable database name, so I choose something deeply meaningful like “ORCL” ;) and click “Next”:

pdbaas56

On the “Configurations” page, you can set up the following parameters for the PDB service template:

  • Workload – Here you can setup different workload sizes (e.g. small, medium and large) based on the CPU, memory, number of sessions and storage requirements of a particular PDB service. These workload sizes can be chosen by the Self Service user at request time
  • Role – This is the database role that will be associated with the PDB for the service that will give it control over the service
  • Storage – A number of tablespaces can be created for each PDB. This is where you set up the storage requirements for those tablespaces such as initial size

Firstly let’s create a workload. Click “Create”:

pdbaas57

In this case, I’m creating a small workload, so name the workload appropriately, optionally add a description, set values for the number of CPU cores, amount of memory, number of sessions and amount of storage allowed for the workload and click “Create”:

pdbaas58

Back on the “Configurations” page, provide a more meaningful name for the role that will be created, leave the other values at their default and click “Next”:

pdbaas59

On the next screen, I can set specific initialization parameters for databases created using this template. For now, I’ll leave that alone and click “Next”:

pdbaas60

On the next page, I can specify custom scripts that will be executed either before and after creating the PDB, or before and after deleting the PDB. Again, I’ll leave that at the defaults and click “Next”:

pdbaas61

On the “Roles” step, click the “Add” button:

pdbaas62

Select the “DBAAS_CLOUD_USERS” role, then click “Select”:

pdbaas63

Click “Next”:

pdbaas64

Review the template details, and click “Create”:

pdbaas65

You should get a message confirming the template was create successfully:

pdbaas66

We’ll leave chargeback for another time.

So there you have it. I warned you it would be a long post! Now we have PDBaaS set up. In another post, I’ll take you through using all of this in the Self Service Portal.

Loading XML documents into an Oracle database (2)

If only I could do the following…(but it seems that it isn’t supported yet)… That is…based on the first “Loading XML documents into an Oracle...
class="readmore">Read More

Loading XML documents into an Oracle database (1)

Very often people want to load XML documents into an Oracle database so, for example, they can shred the needed values from those XML documents...
class="readmore">Read More

When Storage is REALLY Fast Even Zero-Second Wait Events are Top 5. Disk File Operations I/O: The Mystery Wait Event.

The SLOB code that is generally available here differs significantly from what I often test with in labs. Recently I was contorting SLOB to hammer an EMC XtremIO All-Flash Array in a rather interesting way. Those of you in the ranks of the hundreds of SLOB experts out there will notice two things quickly in the following AWR snippet:

1)   Physical single block reads are being timed by the Oracle wait interface at 601 microseconds (3604/5995141 == .000601) and this is, naturally for SLOB, the top wait event.

2)   Disk file operations I/O is ranking as a top 5 timed event. This is not typical for SLOB.

 

file-io-operations

The 601us latencies for XtremIO are certainly no surprise. After all, this particular EMC storage array is an All-Flash Array so there’s no opportunity for latency to suffer as is the case with alternatives such as flash-cache approaches. So what is this blog post about? It’s about Disk file operations I/O.

I needed to refresh my memory on what the Disk file operations I/O event was all about. So, I naturally went to consult the Statistics Description documentation. Unfortunately there was no mention of the wait even there so I dug further to find it documented in the Description of Wait Events section of the Oracle Database 11g documentation which states:

This event is used to wait for disk file operations (for example, open, close, seek, and resize). It is also used for miscellaneous I/O operations such as block dumps and password file accesses.

Egad. A wait is a blocking system call. Since open(2)/close(2) and seek(2) are non-blocking on normal files I suppose I could have suffered a resize operation–but wait, this tablespace doesn’t allow autoextend.  I suppose I really shouldn’t care that much given the fact that the sum total of wait time was zero seconds. But I wanted to understand more so I sought information from the user community–a search that landed me happily at Kyle Hailey’s post on oaktableworld.com here. Kyle’s post had some scripts that looked promising for providing more information about these waits but unfortunately in my case the scripts returned no rows found.

So, at this point, I’ll have to say that the sole value of this blog post is to point out the fact that a) the Oracle documentation specifically covering statistics descriptions is not as complete as the Description of Wait Events section and b) the elusive Disk file operations I/O wait event remains, well, elusive and that this is now part I in a multi-part blog series until I learn more. I’ll set up some traces and see what’s going on. Perhaps Kyle will chime in.

 

 

 

Filed under: oracle

Planned Maintenance This Weekend!

Just a quick heads-up to you folks…

On Saturday I’ll be transferring my website and blog to a new server. It’s with the same hosting company, so hopefully the DNS changes will not be too drastic.

The site is already in place on the new box and it seems to be working fine, so on Saturday morning I will do a backup of the MySQL databases and transfer them to the new machine, then initiate the DNS change.

The last time I did this, which was between different hosting companies, there were some issues in some geographical regions for a couple of days, but most people were seeing the site as normal within about an hour.

Note. If you happen to post on the forum or comment on a blog post during Saturday, it might be lost depending on the timing. Also, my email address my go wonky for a little while…

Fingers crossed… :)

Cheers

Tim…

 


Planned Maintenance This Weekend! was first posted on July 18, 2014 at 9:14 am.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

AWR Warehouse Webinar from ODTUG

The webinar is over, but you haven’t missed out on everything I presented on the console feature, under the hood and behind the scenes!

You can access the slides from today’s presentation, as I’ve uploaded them to slideshare and the scripts are easy to locate on the scripts page here on DBAKevlar.

Thanks to everyone who attended and a big thanks to GP for doing the introduction and ODTUG for hosting us! :)



Tags:  


Del.icio.us



Facebook

TweetThis

Digg

StumbleUpon



#003399;padding-bottom:4px;">You might be interested in this:  


Copyright © DBA Kevlar [AWR Warehouse Webinar from ODTUG], All Right Reserved. 2014.