TECHNOLOGY: Database Cloud
Many in OneBy Arup Nanda
Create many databases in one database instance with the Oracle Database 12c multitenant architecture.
John, the lead database architect at Acme Bank, has some important visitors today: the chief information officer and her senior IT leaders.
Acme has several divisions, all of which use a third-party application called MortEngage to manage the mortgage loan process. Over the past several years, all of these divisions have deployed and maintained separate installations of the product in their independent databases. The company understands the value of consolidating multiple databases and machines, and as part of its current consolidation project, the CIO wants to put all the separate installations into a single database running on one powerful machine. All the different instances of the application would be stored as schemas in the same database, and that would eliminate a lot of overhead. There would be one Oracle Database instance instead of hundreds, there would be only one set of Oracle Database metadata, fewer DBAs would be needed to manage the one database, and so on. The idea is great, but unfortunately, as the CIO has learned, the application needs a specific schema name—MORTENGAGE—in the database and it is hard-coded in the application and cannot be changed. Obviously, as the DBAs correctly informed her, it is not possible to create two different schemas with the same name in a database. Therefore, the only way to run multiple installations of the application is to create the required schema in multiple separate databases.
Consolidation? Impossible was the general verdict of Acme’s DBAs.
But the smart CIO isn’t ready to give up just yet. She reaches out to John in search of a solution, and she isn’t disappointed. Indeed it is possible to consolidate the database—she learns from a smiling John—with the new multitenant architecture in Oracle Database 12c. In the rest of this article, you will see how John provides the solution.
The problem, John tells the CIO and the senior IT leaders, has to do with the namespace. Each Oracle Database user is uniquely named, so if the application needs a database user named MORTENGAGE, only one instance of that application can run against that database. Each additional deployment of the same application would need to connect as the MORTENGAGE user on a different database.
But that changes in Oracle Database 12c, John explains. Instead of creating multiple databases, one can create multiple pluggable databases in a multitenant container database. The database instance—a set of memory areas, such as the buffer cache and shared pool and processes such as pmon and smon—is associated with the multitenant container database; the individual pluggable databases do not have their own database instances. The Oracle Database instance processes exist only for the multitenant container database—not the pluggable databases—saving a lot of resources on the host server.
To illustrate the concept, John points the CIO and the IT leaders in his office to Figure 1 and shows the various databases; the memory, CPU, and storage they consume; and the savings after they have been consolidated as pluggable databases in a single multitenant container database. In Figure 1, the red databases are database instances—three before consolidation and one multitenant container database after consolidation. The green databases—after consolidation—are pluggable databases.
Figure 1: From multiple database instances to pluggable databases in a multitenant container database
The CIO chews on the information a bit and muses, “So, John, you are saying there is just one actual database, and therefore there is just one set each of memory areas such as SGA and background processes such as smon, regardless of the number of pluggable databases. Well, if there is just one actual database, how can there be multiple users with the same name—MORTENGAGE—in the database?”
This is where the beauty of the multitenant architecture in Oracle Database 12c comes in, John explains. To a user, the pluggable databases behave just like regular databases. In fact, a typical user may not even know the difference. If 50 instances of the application need to run, John continues, the Acme DBAs create 50 pluggable databases in a single multitenant container database. Each pluggable database will have one MORTENGAGE user and will support one installation of the application. The audience, now visibly enthused, urges John to demonstrate how it all works.
To create the databases, John kicks off the Oracle Database Configuration Assistant that came with Oracle Database 12c. After a few clicks, he comes to the Database Identification screen, shown in Figure 2. John selects Create a Container Database with one or more PDBs as shown and chooses 2 as the number of pluggable databases. He enters CONT as the multitenant container database name (in the Global Database Name field) and PLUG as the pluggable database name prefix (in the PDB Name Prefix field). This will create a multitenant container database named CONT and two pluggable databases named PLUG1 and PLUG2.
Figure 2: Oracle Database Configuration Assistant screen for creating pluggable databases
After the multitenant container database (CDB) is created, John wants to confirm that two pluggable databases were created. Oracle Database 12c introduces a new view called V$PDBS that shows the pluggable databases. John logs in to SQL*Plus as a SYSDBA user and selects two columns from this view:
SQL> select con_id, name 2 from v$pdbs; CON_ID NAME —————————— —————————— 2 PDB$SEED 3 PLUG1 4 PLUG2
The pluggable databases do not have their own background processes and shared memory areas. They do, however, take up some space in the multitenant container database’s Oracle metadata, redo logfile, controlfile, and some tablespaces such as undo. Each of the pluggable databases has its own SYSTEM, SYSAUX, TEMP, and USERS tablespaces. There is a common location for the Automatic Diagnostic Repository feature of Oracle Database for the multitenant container database; the pluggable databases do not have independent Automatic Diagnostic Repository locations. Therefore, John explains, if there were 50 independent databases as mentioned earlier, after the consolidation into 1 multitenant container database, the DBAs would need to manage only the multitenant container database. There is just 1 instance and 1 pmon process instead of 50, reducing the amount of CPU and memory required. All of this, John points out, dramatically reduces the cost of both infrastructure and operation.
Next, John moves on to creating the users required for the application. The application needs a user named MORTENGAGE. John creates that user in each of the pluggable databases. To create the user in the PLUG1 pluggable database, he first sets the CONTAINER session parameter to the pluggable database name and then creates the user.
SQL> alter session set container = plug1; Session altered. SQL> create user mortengage identified by plug1pass; User created.
SQL> alter session set container = plug2; Session altered. SQL> create user mortengage identified by plug2pass; User created.
SQL> select con_id, username, common 2 from cdb_users; CON_ID USERNAME COMMON ————————— ————————————— ———————— 3 MORTENGAGE NO 4 MORTENGAGE NO 1 SYSTEM YES 2 SYSTEM YES 3 SYSTEM YES 4 SYSTEM YES
“I see that there is a MORTENGAGE user in each of the pluggable databases,” offers one DBA, “but how does an application connect to a specific pluggable database?”
“Exactly as it used to connect in the past,” replies John. “By using the appropriate TNS connect string.” He puts the entries in the TNSNAMES.ORA file, located in the network\admin directory under Oracle Home on the client machines where the applications run; Listing 1 shows the TNSNAMES.ORA entries.
Code Listing 1: TNS entries for pluggable databases
PLUG1 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = prohost1)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = PLUG1) ) ) PLUG2 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = prohost1)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = PLUG2) ) )
To identify which pluggable database a user is connected to, John demonstrates a new user environment variable called CON_NAME in the SYS_CONTEXT function:
SQL> select sys_context('userenv', 'con_name') 2 from dual; SYS_CONTEXT('USERENV','CON_NAME') ——————————————————————————————————— PLUG1
It’s a valid concern, John concedes, but he announces that fortunately it is possible to set different values for different pluggable databases. He demonstrates this by setting the value of the parameter in the PLUG2 pluggable database to false.
$ sqlplus sys/oracle@plug2 as sysdba SQL> alter system set optimizer_use_sql_plan_baselines = false scope=memory;
$ sqlplus sys/oracle@plug1 as sysdba SQL> alter system set optimizer_use_sql_plan_baselines = true scope=memory;
First, connecting to PLUG2, he checks for the value:
SQL> connect mortengage/plug2@plug2 SQL> show parameter optimizer_use_sql_plan_baselines NAME TYPE VALUE —————————————————————— ——————— ——————— optimizer_use_sql... boolean FALSE
SQL> connect mortengage/plug4@plug1 SQL> show parameter optimizer_use_sql_plan_baselines NAME TYPE VALUE —————————————————————— ——————— —————— optimizer_use_sql... boolean TRUE
DOWNLOAD Oracle Database 12c
Note that the red triangle icon shown at the top of the mobile screen at runtime (see Figure 2) indicates that the deployed application is in debug mode. This can be changed by a configuration setting in the ViewController project, and the change also shortens deployment time.
Pluggable databases running in the multitenant architecture of Oracle Database 12c offer the simplicity and familiarity of traditional databases while providing the flexibility to run multiple pluggable databases within one multitenant container database. The multitenant architecture enables many schemas with the same name to be created without the need to create many disparate databases. Because there is just one multixtenant container database, there is just one database instance, eliminating the Oracle Database background process and memory areas such as SGA for separate databases. And running pluggable databases in the multitenant architecture of Oracle Database 12c requires no changes to applications.
Acme’s IT leaders are all nods and smiles, and there are no more questions about Oracle Database 12c, multitenant container architecture, pluggable databases, provisioning, cloning, or backups. The meeting is adjourned.
Arup Nanda (firstname.lastname@example.org) has been an Oracle DBA since 1993, handling all aspects of database administration, from performance tuning to security and disaster recovery. He was Oracle Magazine’s DBA of the Year in 2003 and received an Oracle Excellence Award for Technologist of the Year in 2012.