Oracle 12c Multitenant Architecture. New development and testing opportunities
The biggest innovation of the recently released Oracle 12c is by far the Multitenant Architecture . Oracle itself presents this opportunity mainly as a means of consolidation and cost reduction.
The essence of the technology is the ability to run several independent databases (pluggable database, PDB) within one instance (container database, CDB). Each database has its own set of schemes and table spaces, but at the same time they have a common SGA and one set of server processes. It is possible to clone a pluggable database, both within the same container, and between containers. We will use this opportunity to create copies of test databases and save resources.
The task - we have a large system with a large base. It is necessary to test changes, and destructive changes - deleting / modifying tables. How this is done now - we create a new database, fill it with the minimum possible set of circuits and data and conduct testing. The process itself is not fast, time-consuming and there is always the opportunity to make mistakes. And the “minimum data set” may not be so small.
The documentation for the CREATE PLUGGABLE DATABASE command , in the cloning section , mentions the SNAPSHOT COPY option. Judging by the description, when creating a clone with the SNAPSHOT COPY option, the data files of the cloned database will not be copied. For them will be
snapshots are created on copy and only the changed blocks of the cloned base will occupy disk space. Creating clones with snapshots is possible either on ACFS or on specialized NAS .
The experiment was conducted in the environment of Oracle Virtualbox 4.2.14. I will not describe the installation in detail, it is well covered in the documentation, I will dwell only on important points.
We install Oracle linux 6.4 with updates, dependencies for oracle and ASM support:
configure ASM and create ASM disk:
Install Oracle Database 12c Release 1 Grid Infrastructure (12.1.0.1.0) for Linux x86-64 in singl node mode. In discovery path -> / dev / oracleasm / disks.
Using asmcmd commands or using asmca create disk group (DATA) volume (DATAVOL) and ASM cluster filesystem with mount point (/ data). From root, mount ACFS and make sure everything is fine:
We connect to the ASM instance and change the compatibility mode:
We put Oracle Database 12c Release 1 (12.1.0.1.0) for Linux x86-64, during installation we select only the software installation (enterprise). After a successful installation, run dbca and create a container database : Create database -> Advanced mode -> Custom Database -> Create As Container Database (Create an Empty Container Database) .
Specify ASM (+ DATA) as Storage Type. All Database Components will be selected without editing. Character set needs to be selected suitable for all bases which we will create in this container. We start the creation of the base and wait for the successful completion.
As a result, we get a container database with seed database (model base for creating a pluggable database).
Check that everything worked out:
Create a directory on ACFS / data / oradata, and change the owner to oracle. Change the db_create_file_dest parameter to / data / oradata. Create a clone that will depict the test base:
A directory with an alphanumeric name (random) should appear on ACFS containing our PDB:
After creating or restarting the container, you need to open the PDB:
We simulate the creation of a test base - create a tablespace test of 2G size:
We make sure that there is a data file and its size is 2 gigabytes:
We have a large test base and we want to test a particularly large change that affects the structure of tables and requires all data to be verified.
We clone the test base in snapshot mode:
And look what happened on the file system:
Data files of all table spaces except TEMP are ACFS snapshot links, and they do not take up disk space. You can find out how much the snapshot really took:
What they achieved - 286MB against more than 3GB
Naturally, if you actively begin to change clone blocks with snapshots, their place will increase.
After testing, delete the unnecessary clone:
We make sure that the place is vacant:
As a result, we were able to save time and resources. And not only disk, I remind you that all PDBs use one set of processes and one SGA.
PS. The article does not pretend to be a full description of Oracle Multitenant Architecture , only a special case is considered in relation to a specific task.
The essence of the technology is the ability to run several independent databases (pluggable database, PDB) within one instance (container database, CDB). Each database has its own set of schemes and table spaces, but at the same time they have a common SGA and one set of server processes. It is possible to clone a pluggable database, both within the same container, and between containers. We will use this opportunity to create copies of test databases and save resources.
The task - we have a large system with a large base. It is necessary to test changes, and destructive changes - deleting / modifying tables. How this is done now - we create a new database, fill it with the minimum possible set of circuits and data and conduct testing. The process itself is not fast, time-consuming and there is always the opportunity to make mistakes. And the “minimum data set” may not be so small.
The documentation for the CREATE PLUGGABLE DATABASE command , in the cloning section , mentions the SNAPSHOT COPY option. Judging by the description, when creating a clone with the SNAPSHOT COPY option, the data files of the cloned database will not be copied. For them will be
snapshots are created on copy and only the changed blocks of the cloned base will occupy disk space. Creating clones with snapshots is possible either on ACFS or on specialized NAS .
The experiment was conducted in the environment of Oracle Virtualbox 4.2.14. I will not describe the installation in detail, it is well covered in the documentation, I will dwell only on important points.
Installation
We install Oracle linux 6.4 with updates, dependencies for oracle and ASM support:
$ uname -a
Linux ora12.local 2.6.39-400.109.1.el6uek.x86_64 #1 SMP Tue Jun 4 23:21:51 PDT 2013 x86_64 x86_64 x86_64 GNU/Linux
$ yum install oracle-rdbms-server-11gR2-preinstall
$ yum install oracleasm-support
configure ASM and create ASM disk:
$ oracleasm configure -i
$ oracleasm createdisk -v data /dev/sdb1
Install Oracle Database 12c Release 1 Grid Infrastructure (12.1.0.1.0) for Linux x86-64 in singl node mode. In discovery path -> / dev / oracleasm / disks.
Using asmcmd commands or using asmca create disk group (DATA) volume (DATAVOL) and ASM cluster filesystem with mount point (/ data). From root, mount ACFS and make sure everything is fine:
$ mount | grep data
/dev/asm/datavol-326 on /data type acfs (rw)
We connect to the ASM instance and change the compatibility mode:
$ sqlplus "/ AS SYSASM"
ALTER DISKGROUP data SET ATTRIBUTE 'compatible.rdbms' = '12.1.0.0.0';
ALTER DISKGROUP data SET ATTRIBUTE 'compatible.advm' = '12.1.0.0.0';
We put Oracle Database 12c Release 1 (12.1.0.1.0) for Linux x86-64, during installation we select only the software installation (enterprise). After a successful installation, run dbca and create a container database : Create database -> Advanced mode -> Custom Database -> Create As Container Database (Create an Empty Container Database) .
Specify ASM (+ DATA) as Storage Type. All Database Components will be selected without editing. Character set needs to be selected suitable for all bases which we will create in this container. We start the creation of the base and wait for the successful completion.
As a result, we get a container database with seed database (model base for creating a pluggable database).
Check that everything worked out:
Hidden text
$ sqlplus "/ AS SYSDBA"
SQL*Plus: Release 12.1.0.1.0 Production on Thu Jul 4 13:48:26 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
SQL> SELECT NAME,OPEN_MODE FROM V$PDBS ;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
Work with clones
Create a directory on ACFS / data / oradata, and change the owner to oracle. Change the db_create_file_dest parameter to / data / oradata. Create a clone that will depict the test base:
CREATE PLUGGABLE DATABASE PDB001 ADMIN USER admin IDENTIFIED BY qwerty123
STORAGE (MAXSIZE 100G MAX_SHARED_TEMP_SIZE 100M)
PATH_PREFIX = '/data/oradata/PDB001';
A directory with an alphanumeric name (random) should appear on ACFS containing our PDB:
$ ls /data/oradata/ORCL/E0A1FFE9FFCF393FE0430F02000A6D7E/datafile/
o1_mf_sysaux_8x9131gt_.dbf o1_mf_system_8x912hvo_.dbf o1_mf_temp_8x914mbg_.dbf
After creating or restarting the container, you need to open the PDB:
alter pluggable database all open;
We simulate the creation of a test base - create a tablespace test of 2G size:
alter session set container=pdb001; -- выбираем активным наш клон
create tablespace test datafile size 2g;
We make sure that there is a data file and its size is 2 gigabytes:
$ ls -l /data/oradata/ORCL/E0A1FFE9FFCF393FE0430F02000A6D7E/datafile/*test*
-rw-r----- 1 oracle grid 2147491840 Jul 4 10:55 /data/oradata/ORCL/E0A1FFE9FFCF393FE0430F02000A6D7E/datafile/o1_mf_test_8x97xv5k_.dbf
For what all this was up to
We have a large test base and we want to test a particularly large change that affects the structure of tables and requires all data to be verified.
We clone the test base in snapshot mode:
$ sqlplus "/ AS SYSDBA"
SQL> alter pluggable database pdb001 close immediate;
Pluggable database altered.
SQL> alter pluggable database pdb001 open read only;
Pluggable database altered.
SQL> create pluggable database pdb003 from pdb001 SNAPSHOT COPY;
Pluggable database created.
SQL> alter pluggable database pdb001 close immediate;
Pluggable database altered.
SQL> alter pluggable database all open;
Pluggable database altered.
And look what happened on the file system:
$ ls -l /data/oradata/ORCL/E0AB0DD4BA9D2C11E0430F02000AED35/datafile/
total 20500
lrwxrwxrwx 1 oracle grid 132 Jul 4 10:54 o1_mf_sysaux_8xb71r49_.dbf -> /data/.ACFS/snaps/E0AB0DD4BA9D2C11E0430F02000AED35/oradata/ORCL/E0A1FFE9FFCF393FE0430F02000A6D7E/datafile/o1_mf_sysaux_8x9131gt_.dbf
lrwxrwxrwx 1 oracle grid 132 Jul 4 10:54 o1_mf_system_8xb71r49_.dbf -> /data/.ACFS/snaps/E0AB0DD4BA9D2C11E0430F02000AED35/oradata/ORCL/E0A1FFE9FFCF393FE0430F02000A6D7E/datafile/o1_mf_system_8x912hvo_.dbf
-rw-r----- 1 oracle grid 20979712 Jul 4 10:55 o1_mf_temp_8xb71tn1_.dbf
lrwxrwxrwx 1 oracle grid 130 Jul 4 10:54 o1_mf_test_8xb71r49_.dbf -> /data/.ACFS/snaps/E0AB0DD4BA9D2C11E0430F02000AED35/oradata/ORCL/E0A1FFE9FFCF393FE0430F02000A6D7E/datafile/o1_mf_test_8x97xv5k_.dbf
Data files of all table spaces except TEMP are ACFS snapshot links, and they do not take up disk space. You can find out how much the snapshot really took:
$ acfsutil snap info /data
snapshot name: E0AB0DD4BA9D2C11E0430F02000AED35
RO snapshot or RW snapshot: RW
parent name: /data
snapshot creation time: Thu Jul 4 10:54:48 2013
number of snapshots: 1
snapshot space usage: 286388224
What they achieved - 286MB against more than 3GB
$ du -k /data/oradata/ORCL/E0A1FFE9FFCF393FE0430F02000A6D7E/datafile
3105828 /data/oradata/ORCL/E0A1FFE9FFCF393FE0430F02000A6D7E/datafile
Naturally, if you actively begin to change clone blocks with snapshots, their place will increase.
After testing, delete the unnecessary clone:
SQL> alter pluggable database PDB003 close immediate;
Pluggable database altered.
SQL> drop pluggable database pdb003 including datafiles;
Pluggable database dropped.
We make sure that the place is vacant:
$ acfsutil snap info /data
number of snapshots: 0
snapshot space usage: 0
Result
As a result, we were able to save time and resources. And not only disk, I remind you that all PDBs use one set of processes and one SGA.
PS. The article does not pretend to be a full description of Oracle Multitenant Architecture , only a special case is considered in relation to a specific task.