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

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 12.1.0.4.

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”:

usingsaas01

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

usingsaas02

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

usingsaas03

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

usingsaas04

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:

usingsaas05

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

usingsaas06

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

usingsaas07

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:

usingsaas08

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

usingsaas09

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! ๐Ÿ™‚

Pete

After 22 years of working at Oracle in just about every role except Marketing and Support, I am now working as a Senior Managed Services Consultant with Data Intensity, specializing in Oracle Database technology, High Availability and Disaster Recovery solutions. I am also a member of the OakTable Network, and have presented at RMOUG Training Days, Hotsos Symposia, Oracle OpenWorld conferences, and other user group events. I have co-authored the Expert Oracle Enterprise Manager 12c and Practical Oracle Database Appliance books published by Apress, and am one of the authors of the Building Database Clouds in Oracle Database 12c book published by Addison Wesley.

6 Comments:

  1. Pete, I’ve implemented SchaaS as test in EM12cR4 and it’s working fine with schemas with data deployed to a RAC DB. Perhaps tied into a couple of your caveat notes at the end of the blog I can’t seem to reuse a tablespace name on subsequent requests even though the original schema and tablespace is deleted from EM and the DB fine. Just wondered if you had seen this?

  2. Perhaps a bug Pete. I should be able to reuse a TS name again if the original TS/schema combo has been deleted from the SSA Portal (no longer in the target DB)…. but I can’t.

    • Matt, I’ve done just that so there must be something else screwy here. Just to be 100% clear, you did delete the schema with the prefix from the target database, correct? So in my example, I needed to delete a schema called DBAAS_HR, not just HR. I’d really need to see the error (send a screenshot to my email address, pete.sharman@oracle.com), along with the details from the procedure activity (Enterprise -> Provisioning and Patching -> Procedure activity) to debug it further. Let’s take it offline from the website to do that.

  3. Hi Pete ,
    I came across your Blog for Oracle 12c Schema as a Service and would first of all like to thank you for such an informative blog. I am working as a DBA in Infosys (Mysore , India ) . I tried out Schema as a Service and came across the bugs that you have mentioned in the blog. First of all I got error unable to find targets with sufficient resources , later second time I got the error tablespace null does not exist. The third time when I tried the import failed . But there is no error message associated with it. When I click to check the status of import it says “No log available “. Would be of great help if you could suggest me how to resolve this or am I going wrong some where .The user is getting created in the other database but there no objects in it .

    I followed whatever you had mentioned in the below two posts

    1) https://petewhodidnottweet.com/2014/07/schema-consolidation-setup/
    2) https://petewhodidnottweet.com/2014/07/using-the-self-service-portal-with-schema-as-a-service-in-em-12-1-0-4/

    In Anticipation of help ๐Ÿ™‚

    Thanks & Regards,
    Suvidha

    • Sorry, Suvidha, I’ve been off sick for a few days and hadn’t seen your question.

      The lack of log message is actually quite misleading. There WILL be a log somewhere under the GC_INST directory. I don’t have a Schema as a Service setup right now to tell you the exact path, but if you do a grep -iR under GC_INST for the service name you should find the log fairly quickly. The error will probably be either a privileges issue or possibly the account is locked still. You could also try logging on as the user and creating a test object to see if that returns an error as well.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.