![Page 1: PDB Provisioning with Oracle Multitenant Self Service Application](https://reader031.vdocuments.us/reader031/viewer/2022012400/55524fb3b4c905954f8b5023/html5/thumbnails/1.jpg)
Databases for the Masses:Self-Service Oracle Multitenant with APEX
Leighton L. Nelson
![Page 2: PDB Provisioning with Oracle Multitenant Self Service Application](https://reader031.vdocuments.us/reader031/viewer/2022012400/55524fb3b4c905954f8b5023/html5/thumbnails/2.jpg)
2
Who am I?
• Oracle DBA• Oracle ACE• Oracle Certified Expert RAC and Grid
Infrastructure Administrator 11gR2• Co-Author Expert Oracle Enterprise
Manager Cloud Control 12c (Apress 2013)• Speaker at RMOUG, IOUG Collaborate,
ODTUG KScope, Oracle OpenWorld• Blogs, Social Media• RAC Attack Ninja
@leight0nn http://pdbss.oracledb12c.com:8080/ords/fp?=pdbss6/24/14
![Page 3: PDB Provisioning with Oracle Multitenant Self Service Application](https://reader031.vdocuments.us/reader031/viewer/2022012400/55524fb3b4c905954f8b5023/html5/thumbnails/3.jpg)
3
Agenda
• Oracle Multitenant Overview• Manual Provisioning• Oracle Multitenant Self Service Apex Setup• Self-Service Provisioning Demo• Summary
@leight0nn http://pdbss.oracledb12c.com:8080/ords/fp?=pdbss6/24/14
![Page 4: PDB Provisioning with Oracle Multitenant Self Service Application](https://reader031.vdocuments.us/reader031/viewer/2022012400/55524fb3b4c905954f8b5023/html5/thumbnails/4.jpg)
4
Oracle Multitenant Overview
@leight0nn http://pdbss.oracledb12c.com:8080/ords/fp?=pdbss6/24/14
![Page 5: PDB Provisioning with Oracle Multitenant Self Service Application](https://reader031.vdocuments.us/reader031/viewer/2022012400/55524fb3b4c905954f8b5023/html5/thumbnails/5.jpg)
5
Oracle Multitenant Overview
@leight0nn http://pdbss.oracledb12c.com:8080/ords/fp?=pdbss6/24/14
• High consolidation density• Rapid provisioning and cloning• Rapid patching and upgrades• Manage many databases as one• Dynamic resource management between
pluggable database
![Page 6: PDB Provisioning with Oracle Multitenant Self Service Application](https://reader031.vdocuments.us/reader031/viewer/2022012400/55524fb3b4c905954f8b5023/html5/thumbnails/6.jpg)
6
Manual Provisioning
@leight0nn http://pdbss.oracledb12c.com:8080/ords/fp?=pdbss6/24/14
![Page 7: PDB Provisioning with Oracle Multitenant Self Service Application](https://reader031.vdocuments.us/reader031/viewer/2022012400/55524fb3b4c905954f8b5023/html5/thumbnails/7.jpg)
7
Manual Provisioning
• Create from PDB seed
CREATE PLUGGABLE DATABASE pdbname ADMIN USER pdbadmin IDENTIFIED BY password
• Cloning an existing PDB
CREATE PLUGGABLE DATABASE pdbclone FROM pdbsource
• Plugging in an unplugged PDB
CREATE PLUGGABLE DATABASE USING ‘pdb.xml’• Migrate non-CDB to a PDB
– DBMS_PDB– Data Pump @leight0nn
http://pdbss.oracledb12c.com:8080/ords/fp?=pdbss6/24/14
![Page 8: PDB Provisioning with Oracle Multitenant Self Service Application](https://reader031.vdocuments.us/reader031/viewer/2022012400/55524fb3b4c905954f8b5023/html5/thumbnails/8.jpg)
8
Manual Provisioning
• Drop PDB
DROP PLUGGABLE DATABASE pdbname• Unplug PDB
ALTER PLUGGABLE DATABASE pdbname UNPLUG INTO ‘pdb.xml’
• Open/Close
ALTER PLUGGABLE DATABASE pdbname OPEN/CLOSE
@leight0nn http://pdbss.oracledb12c.com:8080/ords/fp?=pdbss6/24/14
![Page 9: PDB Provisioning with Oracle Multitenant Self Service Application](https://reader031.vdocuments.us/reader031/viewer/2022012400/55524fb3b4c905954f8b5023/html5/thumbnails/9.jpg)
9
Manual Provisioning
@leight0nn http://pdbss.oracledb12c.com:8080/ords/fp?=pdbss6/24/14
![Page 10: PDB Provisioning with Oracle Multitenant Self Service Application](https://reader031.vdocuments.us/reader031/viewer/2022012400/55524fb3b4c905954f8b5023/html5/thumbnails/10.jpg)
10
Oracle Multitenant Self-Service Provisioning – BETA 2
• APEX App available on OTN• Provisions PDBs using PL/SQL API on top of
APEX.• Customizable
– Quotas– Security– DB Size
@leight0nn http://pdbss.oracledb12c.com:8080/ords/fp?=pdbss6/24/14
![Page 11: PDB Provisioning with Oracle Multitenant Self Service Application](https://reader031.vdocuments.us/reader031/viewer/2022012400/55524fb3b4c905954f8b5023/html5/thumbnails/11.jpg)
11
Oracle Multitenant Self Service APEX Setup
• Install Oracle Database 12c• Create new container database• Upgrade APEX to 4.2.3+• Configure RESTful Web Services in CDB• Unlock APEX_PUBLIC_USER• Grant permissions on containers to APEX users• Download and Configure APEX Listener 2.0.4+
(Oracle REST Data Services)• Start APEX Listener in Standalone Mode@leight0nn
http://pdbss.oracledb12c.com:8080/ords/fp?=pdbss6/24/14
![Page 12: PDB Provisioning with Oracle Multitenant Self Service Application](https://reader031.vdocuments.us/reader031/viewer/2022012400/55524fb3b4c905954f8b5023/html5/thumbnails/12.jpg)
12
Oracle Multitenant Self Service APEX Setup
• Create a new 12c container database
@leight0nn http://pdbss.oracledb12c.com:8080/ords/fp?=pdbss6/24/14
![Page 13: PDB Provisioning with Oracle Multitenant Self Service Application](https://reader031.vdocuments.us/reader031/viewer/2022012400/55524fb3b4c905954f8b5023/html5/thumbnails/13.jpg)
13
Oracle Multitenant Self Service APEX Setup
• Upgrade APEX to 4.2.3+– Download and apply Oracle Application Express
patch 17347169 from My Oracle Support. This patch will upgrade Application Express to release 4.2.3.00.08.
– Recommended to apply latest patch 4.2.5.00.08(patch no. 17966818)-For APEX 4.1 and below Install APEX 4.2.5 Full
installer (OTN)
@leight0nn http://pdbss.oracledb12c.com:8080/ords/fp?=pdbss6/24/14
![Page 14: PDB Provisioning with Oracle Multitenant Self Service Application](https://reader031.vdocuments.us/reader031/viewer/2022012400/55524fb3b4c905954f8b5023/html5/thumbnails/14.jpg)
14
Oracle Multitenant Self Service APEX Setup
• Configure RESTful Web Services in CDB– cd patch (the directory from Step 3 above)– Connect as SYS as SYSDBA to CDB$ROOT– @apex_rest_config_con.sql– When prompted, enter passwords for the
APEX_LISTENER and APEX_REST_PUBLIC_USER database users that will be created.
@leight0nn http://pdbss.oracledb12c.com:8080/ords/fp?=pdbss6/24/14
![Page 15: PDB Provisioning with Oracle Multitenant Self Service Application](https://reader031.vdocuments.us/reader031/viewer/2022012400/55524fb3b4c905954f8b5023/html5/thumbnails/15.jpg)
15
Oracle Multitenant Self Service APEX Setup
• Unlock APEX_PUBLIC_USER– Connect as SYS as SYSDBA to CDB$ROOT– alter user APEX_PUBLIC_USER identified by
"<replace_with_password>" account unlock;
@leight0nn http://pdbss.oracledb12c.com:8080/ords/fp?=pdbss6/24/14
![Page 16: PDB Provisioning with Oracle Multitenant Self Service Application](https://reader031.vdocuments.us/reader031/viewer/2022012400/55524fb3b4c905954f8b5023/html5/thumbnails/16.jpg)
16
Oracle Multitenant Self Service APEX Setup
• Grant permissions on containers to APEX users– Connect as SYS as SYSDBA to CDB$ROOT– grant set container to APEX_PUBLIC_USER
container=all;– grant set container to APEX_REST_PUBLIC_USER
container=all;– grant set container to APEX_LISTENER
container=all;
@leight0nn http://pdbss.oracledb12c.com:8080/ords/fp?=pdbss6/24/14
![Page 17: PDB Provisioning with Oracle Multitenant Self Service Application](https://reader031.vdocuments.us/reader031/viewer/2022012400/55524fb3b4c905954f8b5023/html5/thumbnails/17.jpg)
17
Oracle Multitenant Self Service APEX Setup
• Download and Configure APEX Listener 2.0.4+– unzip apex_listener.<version>.zip (ords.zip)– Change your working directory to where you unzipped the
software– java -jar apex.war
or– java –jar ords.war
@leight0nn http://pdbss.oracledb12c.com:8080/ords/fp?=pdbss6/24/14
![Page 18: PDB Provisioning with Oracle Multitenant Self Service Application](https://reader031.vdocuments.us/reader031/viewer/2022012400/55524fb3b4c905954f8b5023/html5/thumbnails/18.jpg)
18
Oracle Multitenant Self Service APEX Setup
• Start APEX Listener in Standalone Mode– java -jar apex.war– Supply the full file system path to the Application
Express images directory from the patch set from Step 3
– Choose an available network port (for example, 8080)
– Confirm that you can access the URL listed at the end of the apex.war console output using a web browser
@leight0nn http://pdbss.oracledb12c.com:8080/ords/fp?=pdbss6/24/14
![Page 19: PDB Provisioning with Oracle Multitenant Self Service Application](https://reader031.vdocuments.us/reader031/viewer/2022012400/55524fb3b4c905954f8b5023/html5/thumbnails/19.jpg)
19
Oracle Multitenant Self Service APEX Setup
• Install PDBSS applicationSQL> @pdbss_ins.sql
. Oracle Multitenant Self-Service Provisioning
..............................................
@leight0nn http://pdbss.oracledb12c.com:8080/ords/fp?=pdbss6/24/14
![Page 20: PDB Provisioning with Oracle Multitenant Self Service Application](https://reader031.vdocuments.us/reader031/viewer/2022012400/55524fb3b4c905954f8b5023/html5/thumbnails/20.jpg)
20
Oracle Multitenant Self Service APEX Setup
• Login to APEX app as ADMIN
@leight0nn http://pdbss.oracledb12c.com:8080/ords/fp?=pdbss6/24/14
![Page 21: PDB Provisioning with Oracle Multitenant Self Service Application](https://reader031.vdocuments.us/reader031/viewer/2022012400/55524fb3b4c905954f8b5023/html5/thumbnails/21.jpg)
21
Self-Service Provisioning Demo
DEMO TIME
@leight0nn http://pdbss.oracledb12c.com:8080/ords/fp?=pdbss6/24/14
![Page 22: PDB Provisioning with Oracle Multitenant Self Service Application](https://reader031.vdocuments.us/reader031/viewer/2022012400/55524fb3b4c905954f8b5023/html5/thumbnails/22.jpg)
22
Self-Service Provisioning Demo
@leight0nn http://pdbss.oracledb12c.com:8080/ords/fp?=pdbss6/24/14
![Page 23: PDB Provisioning with Oracle Multitenant Self Service Application](https://reader031.vdocuments.us/reader031/viewer/2022012400/55524fb3b4c905954f8b5023/html5/thumbnails/23.jpg)
23
Self-Service Provisioning Demo
• Administration
@leight0nn http://pdbss.oracledb12c.com:8080/ords/fp?=pdbss6/24/14
![Page 24: PDB Provisioning with Oracle Multitenant Self Service Application](https://reader031.vdocuments.us/reader031/viewer/2022012400/55524fb3b4c905954f8b5023/html5/thumbnails/24.jpg)
24
Self-Service Provisioning Demo
• Configure SMTP settings
@leight0nn http://pdbss.oracledb12c.com:8080/ords/fp?=pdbss6/24/14
![Page 25: PDB Provisioning with Oracle Multitenant Self Service Application](https://reader031.vdocuments.us/reader031/viewer/2022012400/55524fb3b4c905954f8b5023/html5/thumbnails/25.jpg)
25
Self-Service Provisioning Demo
• Update Admin Email Address
@leight0nn http://pdbss.oracledb12c.com:8080/ords/fp?=pdbss6/24/14
![Page 26: PDB Provisioning with Oracle Multitenant Self Service Application](https://reader031.vdocuments.us/reader031/viewer/2022012400/55524fb3b4c905954f8b5023/html5/thumbnails/26.jpg)
26
Self-Service Provisioning Demo
• Request Access
@leight0nn http://pdbss.oracledb12c.com:8080/ords/fp?=pdbss6/24/14
![Page 27: PDB Provisioning with Oracle Multitenant Self Service Application](https://reader031.vdocuments.us/reader031/viewer/2022012400/55524fb3b4c905954f8b5023/html5/thumbnails/27.jpg)
27
Self-Service Provisioning Demo Mode
@leight0nn http://pdbss.oracledb12c.com:8080/ords/fp?=pdbss6/24/14
![Page 28: PDB Provisioning with Oracle Multitenant Self Service Application](https://reader031.vdocuments.us/reader031/viewer/2022012400/55524fb3b4c905954f8b5023/html5/thumbnails/28.jpg)
28
Self-Service Provisioning Demo
@leight0nn http://pdbss.oracledb12c.com:8080/ords/fp?=pdbss6/24/14
![Page 29: PDB Provisioning with Oracle Multitenant Self Service Application](https://reader031.vdocuments.us/reader031/viewer/2022012400/55524fb3b4c905954f8b5023/html5/thumbnails/29.jpg)
29
Self-Service Provisioning Demo
@leight0nn http://pdbss.oracledb12c.com:8080/ords/fp?=pdbss6/24/14
![Page 30: PDB Provisioning with Oracle Multitenant Self Service Application](https://reader031.vdocuments.us/reader031/viewer/2022012400/55524fb3b4c905954f8b5023/html5/thumbnails/30.jpg)
30
Self-Service Provisioning Demo
@leight0nn http://pdbss.oracledb12c.com:8080/ords/fp?=pdbss6/24/14
![Page 31: PDB Provisioning with Oracle Multitenant Self Service Application](https://reader031.vdocuments.us/reader031/viewer/2022012400/55524fb3b4c905954f8b5023/html5/thumbnails/31.jpg)
31
Self-Service Provisioning Demo
@leight0nn http://pdbss.oracledb12c.com:8080/ords/fp?=pdbss6/24/14
![Page 32: PDB Provisioning with Oracle Multitenant Self Service Application](https://reader031.vdocuments.us/reader031/viewer/2022012400/55524fb3b4c905954f8b5023/html5/thumbnails/32.jpg)
32
Self-Service Provisioning Demo
@leight0nn http://pdbss.oracledb12c.com:8080/ords/fp?=pdbss6/24/14
![Page 33: PDB Provisioning with Oracle Multitenant Self Service Application](https://reader031.vdocuments.us/reader031/viewer/2022012400/55524fb3b4c905954f8b5023/html5/thumbnails/33.jpg)
33
Database Management
• PDBSS ONLY does provisioning– No monitoring– No DBA management
• SQL Developer 4.0• Manually configure EM12c Express for PDBs
– Requires EM_EXPRESS_ALL or EM_EXPRESS_BASIC
@leight0nn http://pdbss.oracledb12c.com:8080/ords/fp?=pdbss6/24/14
![Page 34: PDB Provisioning with Oracle Multitenant Self Service Application](https://reader031.vdocuments.us/reader031/viewer/2022012400/55524fb3b4c905954f8b5023/html5/thumbnails/34.jpg)
34
Database Management
@leight0nn http://pdbss.oracledb12c.com:8080/ords/fp?=pdbss
6/24/14
![Page 35: PDB Provisioning with Oracle Multitenant Self Service Application](https://reader031.vdocuments.us/reader031/viewer/2022012400/55524fb3b4c905954f8b5023/html5/thumbnails/35.jpg)
35
Summary
• Consolidated database environment• Fast database provisioning • Self-Service• Ideal for Test/Development Environments• No need for complex tools• Does not manage or monitor PDBs
– configure EM12c Express
@leight0nn http://pdbss.oracledb12c.com:8080/ords/fp?=pdbss6/24/14
![Page 36: PDB Provisioning with Oracle Multitenant Self Service Application](https://reader031.vdocuments.us/reader031/viewer/2022012400/55524fb3b4c905954f8b5023/html5/thumbnails/36.jpg)
36
Summary
@leight0nn http://pdbss.oracledb12c.com:8080/ords/fp?=pdbss6/24/14
![Page 37: PDB Provisioning with Oracle Multitenant Self Service Application](https://reader031.vdocuments.us/reader031/viewer/2022012400/55524fb3b4c905954f8b5023/html5/thumbnails/37.jpg)
37
Thank you
Questions?
@leight0nn http://pdbss.oracledb12c.com:8080/ords/fp?=pdbss6/24/14
![Page 38: PDB Provisioning with Oracle Multitenant Self Service Application](https://reader031.vdocuments.us/reader031/viewer/2022012400/55524fb3b4c905954f8b5023/html5/thumbnails/38.jpg)
38
Contact
@leight0nn http://pdbss.oracledb12c.com:8080/ords/fp?=pdbss6/24/14
http://blogs.griddba.com
linkedin.com/pub/leighton-nelson/4/6b8/a7a
@leight0nn
![Page 39: PDB Provisioning with Oracle Multitenant Self Service Application](https://reader031.vdocuments.us/reader031/viewer/2022012400/55524fb3b4c905954f8b5023/html5/thumbnails/39.jpg)
39
References
• Oracle Multitenant Option - 12c : Frequently Asked Questions (Doc ID 1511619.1)
• Oracle® Database Administrator's Guide 12c Release 1 (12.1)
@leight0nn http://pdbss.oracledb12c.com:8080/ords/fp?=pdbss6/24/14