Your first steps with the multi-tenancy option
Posted by Martin Bach on July 5, 2013
You can’t possibly have avoided all the buzz around the multi-tenancy option until now so it is probably in order to give you a bit more information about it besides the marketing slides Oracle has shown you so far.
IMPORTANT:This article is a technical article and leaves the pros and cons for the multi-tenancy option at one side. It does the same with the decision to make the CDB a cost option. And it doesn’t advise you on licensing either, so make sure you are appropriately licensed to make use of the features demonstrated in the series.
Since it is nearly impossible to give an overview over Pluggable and Container Databases in one article this is going to be a series of multiple articles. In this part you can read about creating PDBs and where to look for information about them.
- Part 1 is what you are reading right now, it covers the initial steps with the new database type
- Part 2 will deal with the various options of creating, plugging and unplugging databases and the like
- Part 3 will deal with backup and recovery of Container Databases
- Part 4 will be all about Data Guard and how the new type of operation affects
- And finally in part 5 you can read about how PDBs, CDBs work in a Real Application Cluster.
Prior to part 5 all articles will deal with single instance databases, part 5 obviously requires a RAC build.
If you are interested in more in-depth information about Oracle 12c and Consolidation techniques I would like to ask you to wait a little longer until my new book is released. It covers all the above in much more detail and with more examples while at the same time extending the focus to management of your new 12c estate built from the ground up for the new database generation and its lifecyle from migration to production use.
PART 1 Getting started with Multi-Tenancy Option
Many other members of the community have already blogged about the installation of Oracle 12c on Linux, this is why you are not going to find the tenth article about the same subject here. I am actually surprised to see more than Tim Hall‘s article on the subject since his site is the non-official but better understandable reference to most things Oracle :)
More options for separation of duties
Previously users could opt to separate the ownership of Grid Infrastructure from the oracle account. This was initially a little painful, especially when it came to patching but the process is relatively smooth now. The separation of grid home and Oracle home ownership does not really make sense for environments where one group is managing the stack though-you are just making your life a bit more difficult switching back and forth from oracle to grid and vice versa!
I’d recommend making use of the new roles Oracle has introduced with 12c, even if you are not planning on using them. Putting them into the standard and creating them on the O/S level now gives you more flexibility later! The below example is relevant for the installation of Grid Infrastructure and the “separation of duties model”, if you are using a plain-old-file system then just skip the creation of the ASM-related groups and benefit from role separation on the RDBMS layer!
Prior to the new database version we had the following groups defined in for Oracle databases for operating system authentication:
- OSDBA (typically mapped to the OS group “dba”), allows SYSDBA connection to the database
- OSOPER (typically mapped to “oper”) – not often used, allows SYSOPER access to the database.
With 11.1 Oracle introduced a new privilege for ASM, named SYSASM which replaced SYSDBA as the super user. This leaves us with the following groups for ASM:
- OSASM which typically maps to asmadmin and allows you to connect as SYSASM
- OSDBA for ASM which often maps to asmdba and finally the little used
- OSOPER for ASM which by default maps to asmoper on the O/S.
The well established model has now changed in 12c, and the RDBMS has been given new roles in addition to OSDBA and OSOPER. The idea is to give the least needed privileges to other groups in the team: why should the backup team need SYSDBA access to the database and be able to view/modify all data in the system? The new structure is defined using groups as shown here:
- OSBACKUPDBA maps to backupdba by default and is used to allow users to backup the database. Users in this group can connect as SYSBACKUP and have their privileges limited to what is needed for backup and recovery operations.
- OSDGDBA typically maps to dgdba and is used to administer and monitor Data Guard configurations. Members of the group can connect as SYSDBG)
- OSKMDBA typically maps to kmdba and allows the user connecting with the SYSKM role to perform key management and encryption tasks.
Just to show you the difference in an already installed system, consider this:
SQL> connect / as sysdba Connected. SQL> select count(1) from session_privs; COUNT(1) ---------- 233 SQL> conn / as sysbackup Connected. SQL> select count(1) from session_privs; COUNT(1) ---------- 14 SQL> select * from session_privs; PRIVILEGE ---------------------------------------- SYSBACKUP SELECT ANY TRANSACTION SELECT ANY DICTIONARY RESUMABLE CREATE ANY DIRECTORY ALTER DATABASE AUDIT ANY CREATE ANY CLUSTER CREATE ANY TABLE UNLIMITED TABLESPACE DROP TABLESPACE ALTER TABLESPACE ALTER SESSION ALTER SYSTEM 14 rows selected.
So there is a lot less granted by default, making the IT security department happy! Oh one more thing before moving on-check the contents of v$pwfile_users:
SQL> select * from v$pwfile_users; USERNAME SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM CON_ID ------------------------------ ----- ----- ----- ----- ----- ----- ---------- SYS TRUE TRUE FALSE FALSE FALSE FALSE 0 SYSDG FALSE FALSE FALSE FALSE TRUE FALSE 1 SYSBACKUP FALSE FALSE FALSE TRUE FALSE FALSE 1 SYSKM FALSE FALSE FALSE FALSE FALSE TRUE 1
You didn’t see these before in 11.2 ;) And these users are actually created as well:
SQL> select username,account_status 2 from dba_users 3 where username like 'SYS%'; USERNAME ACCOUNT_STATUS -------------------- -------------------------------- SYS OPEN SYSTEM OPEN SYSBACKUP EXPIRED SYSKM EXPIRED & LOCKED SYSDG EXPIRED & LOCKED
As you can see the account is locked initially (well done Oracle!) and when unlocked needs to be reinitialised as you can see in the case of sysbackup. Once that’s done you can actually log in:
SQL> alter user sysbackup identified by xxx; User altered. SQL> conn sysbackup/xxx@server1/cdb1 as sysbackup; Connected. SQL> select * from session_privs; PRIVILEGE ---------------------------------------- SYSBACKUP SELECT ANY TRANSACTION SELECT ANY DICTIONARY RESUMABLE CREATE ANY DIRECTORY ALTER DATABASE AUDIT ANY CREATE ANY CLUSTER CREATE ANY TABLE UNLIMITED TABLESPACE DROP TABLESPACE ALTER TABLESPACE ALTER SESSION ALTER SYSTEM 14 rows selected.
So once the backup agents are updated for 12c you can actually benefit from this, and the loss of the password isn’t as much a problem as it was before.
To create the new groups in one go, you can run the following command:
for i in asmdba asmadmin oinstall dba backupdba kmdba dgdba; do groupadd $i done
If I remember writing it up I will show you how to change the oracle 11gR2 preinstall RPM in a later blog post to perform this task automatically. That reminds me-if you previously installed the standard oracle 11gR2 preinstall RPM then the creation of some of these groups will cause and error which can normally be ignored. I haven’t checked the new preinstall command though.
With the groups defined you can create the grid and oracle accounts if you opt for separation of duties:
# useradd -g oinstall -G asmdba,dba,backupdba,dgdba,kmdba oracle # passwd oracle # useradd -g oinstall -G asmdba,asmadmin grid # passwd grid
And you are done! When you are running the installer for Grid Infrastructure and the database perform the mapping of Oracle to O/S groups. Even if you are currently not planning to add other users than oracle or grid to the groups.
Creating a Container Database
After the successful installation (again please refer to oracle-base.com for the details) you run the Database Configuration Assistant. For those who want to script the process, here is the command line for creating a database on a file system:
[oracle@server1 bin]$ ./dbca -silent -createDatabase -templateName General_Purpose.dbc \ -gdbName CDB1 -createAsContainerDatabase true -numberOfPDBs 1 -pdbName MASTER \ -sysPassword sys -systemPassword sys -dvConfiguration false -olsConfiguration false \ -datafileDestination /u01/oradata -recoveryAreaDestination /u01/fra -listeners LISTENER \ -totalMemory 1024 Enter PDBADMIN User Password: Copying database files 1% complete 2% complete 27% complete Creating and starting Oracle instance 29% complete 32% complete 33% complete 34% complete 38% complete 42% complete 43% complete 45% complete Completing Database Creation 48% complete 51% complete 53% complete 62% complete 70% complete 72% complete Creating Pluggable Databases 78% complete 100% complete Look at the log file "/u01/app/oracle/product/220.127.116.11/dbhome_1/assistants/dbca/templates/cfgtoollogs/dbca/CDB1/CDB1.log" for further details.
The new keywords for dbca are the -createAsContainerDatabase plus the additional options to allow for the configuration of Data Vault and Oracle Label Security. If you want to use ASM for the creation of the database you need to supply the -storageType ASM plus related configuration parameters.
The configuration assistant will next start the creation of a database. I found during tests that changing the character set to UTF8 (AL32UTF8) caused the process to take a very long time so I left it at its default.
Getting started with the multi-tenancy option is rather simple as you just saw. The next step is to actually do something with the new PDBs. This will be covered in the next article in the series.