Martins Blog

Trying to explain complex things in simple terms

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.

Series Overview

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!

New Options

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.

Group Creation

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/12.1.0.1/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.

Conclusion

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.

About these ads

One Response to “Your first steps with the multi-tenancy option”

  1. […] preparation of the OUGN Spring Seminar and to finally fulfill at least a part of my promise from July I was getting ready to research RAC, PDBs and services for my demos. It turned out to become a lot […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 2,367 other followers

%d bloggers like this: