Tuesday, March 25, 2014

Get on the Fast Track to DBaaS with EM12c - Part I

Database-as-a-Service (DBaaS) has now become a buzzword in the IT industry. Organizations are starting to realize the benefits of database as a service thanks to the ubiquity of "The Cloud".
Companies relying on Oracle databases for managing their data can also get on the DBaaS fast track thanks to Oracle Enterprise Manager Cloud Control 12c. Using EM12c you can enable self-service and rapid provisioning of Oracle databases and enjoy all the benefits (well most) of Cloud computing.  EM12c supports both IaaS and PaaS models. These are also tightly integrated with Oracle VM Server along with their engineered systems including Oracle Exadata Database Machine.

Setting up and configuring DBaaS requires several steps. While these are well-documented, it seems like a daunting task for those who are unfamiliar with process. A list of steps required to setup DBaaS include the following:

■Create Roles and Users
■Setup Privilege Delegation
■Add Hosts
■Configure Software Library
■Create PaaS Infrastructure Zone
■Setup Software Pools
■Setup Quotas
■Create Provisioning Profiles
■Create Service Templates

Thanks to the Rapid Start Kit we can setup a fully configured DBaaS environment in minutes.

Rapid Start Kit

The Rapid Start Kit consists of a set of Python scripts which call EMCLI commands and a setup of XML config files. The RSK is located in MW_HOME/plugins/oracle.sysman.ssa.oms.plug_12.1.0.7.0/dbaas/setup

cd /u01/app/oracle/Middleware/plugins/oracle.sysman.ssa.oms.plugin_12.1.0.7.0/dbaas/setup
$ ls
 config  database_cloud_setup.py  exadata_cloud_setup.py

The exadata_cloud_setup.py script is the one that  is executed to setup DBaaS. Don't worry, it doesn't require an Exadata machine.

You can view the command line options for the scripts by running emcli in script mode.

[oracle@gridoms01 setup]$ emcli @exadata_cloud_setup.py -help
====================================================================================================
Cloud Setup Tool
====================================================================================================
Usage: emcli @exadata_cloud_setup.py
                     -pdbaas
                         This flag is used to setup Cloud for Pluggable Database
                     -dbaas
                         This flag is used to setup Cloud for Database as service
                     -db_list
                         comma separated list of 'Target Name'(s) for 'Cluster Database' targets discovered in Enterprise Manager.
                         Choose Container Databases for PDbaaS.
                        Make sure all the Container Databases have same Database credentials and same Host Credentials and are on same platform
                         e.g -db_list=CDB1,CDB2,CDB3
                     -host_list
                         comma separated list of 'Target Name'(s) for 'Host' targets discovered in Enterprise Manager.
                     -db_home_location
                         Database Home location
                         e.g -db_home_location=/u01/app/oracle/11.2.0/dbhome1
                     -cloud_input=
                         Cloud input XML defines Enterprise Manager entities created as part of Cloud Setup.
                         Default file config/dbaas_cloud_input.xml will be used for database setup
                         Default file config/pdbaas_cloud_input.xml will be used for pluggable database setup.
                         Pass custom input xml file to override default input file.
                    -help
                         Displays Cloud Setup help

     For example:
        Setup Cloud on Exadata Machine: Run script on first node of Exadata Machine:
             emcli @exadata_cloud_setup.py : Sets up Cloud for PDBaaS if CDB is available in Enterprise Manager. Setups up DBaaS otherwise.
             emcli @exadata_cloud_setup.py -pdbaas : Sets up Cloud for PDBaaS
            emcli @exadata_cloud_setup.py -dbaas  : Sets up Cloud for DBaaS

       Setup Cloud on Non-Exadata Cluster Machine: Run script on cluster node or any standalone Machine:
             emcli @exadata_cloud_setup.py -pdbaas -db_list=CDB1,CDB2,CDB3   : Sets up Cloud for PDBaaS using  given list of Pluggable databases
             emcli @exadata_cloud_setup.py -dbaas -host_list=host1,host2 -db_home_location= : Sets up Cloud for DBaaS using  given list of hosts and oracle home

You should edit the dbaas_cloud_input.xml configuration file to change the default role and credential names as well as default passwords.
[oracle@gridoms01 config]$ vi dbaas_cloud_input.xml

To setup a Pluggable database as a service (PDBaaS) environment, you need to have an existing Oracle 12c RAC or single-instance database using ASM for database storage. To find the the list of container databases we can use the emcli get_targets command as shown below.
$ emcli get_targets -target="oracle_pdb"
Status  Status           Target Type           Target Name
 ID
1       Up               oracle_pdb            racdb_CDBROOT
0       Down             oracle_pdb            racdb_RACPDB
0       Down             oracle_pdb            racdb_RAC_PDB1
0       Down             oracle_pdb            racdb_RAC_PDB2

The screenshot below is an example of setting up a PDBaaS on a RAC 12c cluster.

After completing the setup simply login as one of the SSA users.

Part II will show how SSA users access the Self-Service Portal to request databases.

Sunday, March 2, 2014

Useful EMCLI commands for Pluggable Database-As-A-Service

This is a quick note on some useful EMCLI commands for enabling and managing Pluggable Database-as-a-Service (PDBaaS).

Get Paas Zone details
emcli get_paas_zone_detail -name="DBA_Test_Zone"

  Name                            DBA_Test_Zone
  Named Credentials               NC_HOST_2012-10-20-160312
  Number of Hosts                 5
  Roles                           CLOUD_DBA
  Maximum Memory Allocation (%)   90
  Maximum CPU Utilization (%)     80

Update PaaS zone placement policy constraints
emcli update_paas_zone -name="DBA_Test_Zone" -memory_utilization=99 -cpu_utilization=99
PaaS Infrastructure Zone "DBA_Test_Zone" updated successfully. 

Add host to PaaS Zone
emcli update_paas_zone -name="DBA_Test_Zone" -add_hosts="linuxhost3.example.com"
PaaS Infrastructure Zone "DBA_Test_Zone" updated successfully.

Find the members of a PaaS Zone
emcli get_system_members -name="DBA_Test_Zone" -type="self_service_zone"
Source Target Name  Member Target Name         Member Target Type  Level  Key Member
DBA_Test_Zone       Schema_Pool_11203          schaas_pool         1
DBA_Test_Zone       Schema_Pool_11202          schaas_pool         1
DBA_Test_Zone       aixhost1.example.com       host                1
DBA_Test_Zone       linuxhost1.example.com     host                1
DBA_Test_Zone       aixhost2.example.com       host                1
DBA_Test_Zone       aixhost3.example.com       host                1
DBA_Test_Zone       linuxhost2.example.com     host                1
DBA_Test_Zone       linuxhost3.example.com     host                1
DBA_Test_Zone       Single Instance AIX Pool   oracle_cloud_zone   1
                                                                                                                               
Create a PDB Pool
emcli create_pool -name="Pluggable DB Pool" -target_type="pdbaas_pool" -paas_zone="DBA_Test_Zone" -members="orclcdb" -description="Oracle Database 12c Pool for Pluggable Databases" -member_constraints="PLATFORM_CDB=226,TARGET_TYPE_CDB=oracle_database,VERSION_CDB=12.1.0.1.0"
Software Pool "Pluggable DB Pool" created successfully.

View the constraints for a PDBaaS pool
emcli get_pool_allowed_member_constraints -target_type=pdbaas_pool
Name                  Value                 Description

PLATFORM_CDB          197                   HP-UX Itanium
                      59                    HP-UX PA-RISC (64-bit)
                      212                   IBM AIX on POWER Systems (64-bit)
                      209                   IBM: Linux on System z
                      46                    Linux x86
                      226                   Linux x86-64
                      912                   Microsoft Windows (32-bit)
                      208                   Microsoft Windows Itanium (64-bit)
                      233                   Microsoft Windows x64 (64-bit)
                      23                    Oracle Solaris on SPARC (64-bit)
                      267                   Oracle Solaris on x86-64 (64-bit)

TARGET_TYPE_CDB       rac_database          Cluster Database
                      oracle_database       Database Instance

VERSION_CDB           12.1.0.1.0            12.1.0.1.0
                                                                                                                                                
Display details about the Pool
emcli get_pool_detail -name="Pluggable DB Pool" -target_type=pdbaas_pool

  Name                            Pluggable DB Pool
  Target Type                     pdbaas_pool
  Description                     Oracle Database 12c Pool for Pluggable Databases
  Paas Infrastructure Zone        DBA_Test_Zone
  Number of Members               1
  Placement Constraints
  Member Constraints              PLATFORM_CDB : Linux x86-64 , TARGET_TYPE_CDB : Database Instance
                                   , VERSION_CDB : 12.1.0.1.0
Find all pluggable databases
emcli get_targets -target="oracle_pdb"
Status  Status           Target Type           Target Name
 ID
1       Up               oracle_pdb            racdb_CDBROOT
0       Down             oracle_pdb            racdb_RACPDB
0       Down             oracle_pdb            racdb_RAC_PDB1
0       Down             oracle_pdb            racdb_RAC_PDB2

Get a list of all Pluggable databases, pools, database clouds and PaaS zones
emcli get_targets -target="oracle_pdb;%pool;cloud;%zone"
Status  Status           Target Type           Target Name
 ID
-9      n/a              cloud                 Middleware and Database Cloud
-9      n/a              oracle_cloud_zone     DBAAS_Pool_AIX
-9      n/a              oracle_cloud_zone     DB12cPool
-9      n/a              oracle_cloud_zone     Dev 11203 Linux VM
1       Up               oracle_pdb            racdb_CDBROOT
0       Down             oracle_pdb            racdb_RACPDB
0       Down             oracle_pdb            racdb_RAC_PDB1
0       Down             oracle_pdb            racdb_RAC_PDB2
-9      n/a              pdbaas_pool           PDB Pool
-9      n/a              schaas_pool           DBAAS_SCHAAS
-9      n/a              schaas_pool           Dev 11203 AIX
-9      n/a              self_service_zone     Lab Zone (Linux)
-9      n/a              self_service_zone     Lab Zone (AIX)


Here is a list of more useful PDBaaS/DBaaS verbs 

PaaS Zone verbs
create_paas_zone      -- To Create a PaaS Infrastructure Zone
delete_paas_zone      -- To Delete a PaaS Infrastructure Zone
get_paas_zone_detail  -- To get PaaS Infrastructure Zone details
update_paas_zone      -- To Update a PaaS Infrastructure Zone

Software and Database Pool verbs
create_pool           -- To Create a Software Pool
delete_pool           -- To Delete a Software Pool
get_pool_allowed_member_constraints           -- To get possible member constraint values of a Software Pool based on its target type
get_pool_allowed_placement_constraints        -- To get the placement constraints of a Software Pool based on its target type
get_pool_capacity     -- To get Software Pool capacity
get_pool_detail       -- To get Software Pool details
get_pool_filtered_targets     -- To get the filtered targets for a Software Pool based on the criteria passed
update_pool           -- To Update a Software Pool

Quota verbs
create_dbaas_quota    -- To Create Database Quota for a SSA User Role
delete_dbaas_quota    -- To Delete the Database Quota setup for a SSA User Role
get_dbaas_quota       -- To List the Database Quota setup for all SSA User Roles
update_dbaas_quota    -- To Update the Database Quota for a SSA User Role

Profile verbs
create_dbprofile    -- Create Database Profile
delete_dbprofile    -- Delete Database Profile
describe_dbprofile_input    -- Describe Database Profile Input
list_dbprofiles     -- List Database Profiles