Top 60 Oracle Blogs

Recent comments

Using the Self Service Portal with Schema as a Service in EM

In a previous blog post, I covered setting up Schema as a Service for schema level consolidation. In this post I’m going to cover how to use the Self Service Portal with Schema as a Service in EM

Just as it was in my posting on using the Self Service Portal with DBaaS, our first step here is to login as the Self Service user, so I provide the right username and password and click “Login”:


By default, this will take me to the Infrastructure Self Service Portal page. I need to select “Databases” from the “Manage” dropdown list:


Next, I need to request a schema from the Database Service Instances region:


On the “Select Service Template” pop-up, I select the DBaaS Schema Service with Data template I created earlier and click “Select”:


On the “Create Schema” page, there are three regions that I need to provide information for:

  1. General – in this region you provide a request name, select the zone the schema will be created in, provide a database service name, and choose a workload size from the workloads we created earlier.
  2. Schedule Request – this is where you define when the request will start and an end date after which the service instance will be removed. You also have the option to keep the service instance indefinitely.
  3. Schema Details – here you define schema details, the master account and the tablespace that will be defined as part of the service instance. While most of the other information you provide is self-explanatory, some of this region may be a bit unclear, so let’s look at these fields in more detail:
    • Schema details – The schemas that will be created as part of this Self Service request which is dependent upon the the Service Template chosen. You can choose to create multiple schemas at once if your template was based on that, but in this example I only selected the HR schema. Each schema will be remapped to another name, based on the provided prefix, so in the example here I will end up with a new schema called “DBAAS_HR”. Note also that you can either choose to have different passwords for each schema if your request has multiple schemas, or alternatively, if you’re lazy like me you could keep the same password for each schema. Obviously it’s better from a security perspective to not be lazy. :)
    • Master account – the master account is the account which has privileges over all the schemas created as part of this service request.
    • Tablespace – This is the name of a tablespace that will be created to contain the schema objects as part of the service request.

The fields on this page that are marked with an asterisk (*) are mandatory fields, so you need to make sure you provide values for those fields at least. Once I’ve filled those in I just click “Submit” to start the service request processing:


Back on the Database Cloud Self Service portal page, I can swap the refresh rate from manual to every 30 seconds:


You should also notice the “Usage” region has been updated to reflect the newly submitted request:


After a short period of time, you will notice the “HR2_Service” instance has now been added to the list of Database Service Instances. If you want to see more details, you can click on the link in the “Status” column for the “Requests” region (depending on the screen refresh timing, you will either see the word “Running” or “Success” there – the fact that we now have a new instance in the Database Service Instances list is your real indication that the service instance was created successfully). You should also notice that we actually added TWO requests in this case – one was to create the service instance and the other is to remove it as I had specified a duration of 7 days for the service instance lifetime:


If I click on either the word “Running” or “Success”, I can see the Request Details pop-up:


Selecting any of the execution steps will show more details in the “Execution Details” region for that particular step. You can also see that some steps weren’t needed (like the custom scripts) so they will show a status of “Skipped”. You can click “OK” to close that window.

Tips and Tricks

As with any software there can be little idiosyncrasies with Oracle software – I know, I know, it’s hard to believe, right? ;) So here are a few tips and tricks to point you to ways around these:

  • There is a known bug, where the Create Schema request is failing with the error “Tablespace ‘NULL’ does not exist”. This only occurs in fairly obscure configurations where the default tablespace for the schema being exported doesn’t contain any objects. If you hit this, you can probably get around it by setting the default tablespace for the schema you want to export to something like the “EXAMPLE” tablespace that contains the Sample Schema. Alternatively, the bug is fixed in Patch 19176910.
  • On the Create Schema page, you are asked to provide (among other values) a database service name and a new tablespace name for the new service instance. If something goes wrong, when you attempt to enter the same values again you are not allowed to. Sometimes the new tablespace has been created, but in other situations it is not, so I’m not sure why this problem occurs then. I wasn’t able to locate where the database services were kept to delete those, but for the tablespace issue you can of course just drop the newly created tablespace including contents and datafiles
  • In another example, the processing of the request may end up creating the new schema name and then failing. If you then try the operation again, the operation fails because the new schema already exists. However, the error message you get is:

    Placement Failure: Unable to find targets with sufficient resources.

    This is a completely incorrect red herring message.

  • I have logged a bug for these last two cleanup issues. You can workaround them but it would be nicer if the system cleaned up after itself! :)