Oracle 12c Multitenant Architecture. New features for development and testing
The biggest innovation of the recently released Oracle 12c is certainly 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 multiple independent databases (pluggable database, PDB) within one instance (container database, CDB). Each base has its own set of schemas 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 a single container, and between containers. It is this opportunity that we will use to create copies of test databases and save resources.
Task - we have a large system, with a large base. It is necessary to carry out testing of changes, and the destructive changes - removal / modification of the tables. How it is done now - we create a new database, fill it with the minimum possible set of schemes and data and conduct testing. The process itself is not fast, time consuming and there is always the possibility of a mistake. Yes, and the "minimum data set" may not be so small.
In the documentation for the CREATE PLUGGABLE DATABASE command , in the cloning section there is a mention of 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
created copy on write snapshots and only modified blocks of the cloned database will take up disk space. Clone snapshots can be created either on ACFS or on specialized NAS .
')
The experiment was conducted in Oracle Virtualbox 4.2.14. I will not describe in detail the installation, it is well covered in the documentation, I will dwell only on important points.
Install Oracle linux 6.4 with updates, dependencies for oracle and ASM support:
configure ASM and create an ASM disk:
Install Oracle Database 12c Release 1 Grid Infrastructure (12.1.0.1.0) for Linux x86-64 in singl node mode. In the discovery path -> / dev / oracleasm / disks.
Using the asmcmd commands or using asmca, we create disk group (DATA) volume (DATAVOL) and ASM cluster filesystem with a mount point (/ data). From under root we mount ACFS and make sure that everything is fine:
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 software installation (enterprise). After successful installation, run dbca and create a database - container: Create database -> Advanced mode -> Custom Database -> Create As Container Database (Create an Empty Container Database) .
As the Storage Type we specify ASM (+ DATA). All Database Components will be selected without editing. Character set you need to choose suitable for all databases that will be created in this container. We start the creation of the database and look forward to a successful completion.
As a result, we obtain a container base and a seed database (model database for creating a pluggable database).
We check that everything turned 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 represent the test base:
A directory with an alphanumeric name (random) containing our PDB should appear on ACFS:
PDB after creating or restarting the container you need to open:
We simulate the creation of a test database - create a tablespace test of 2G size:
Make sure that the data file is there and the size of its 2 gigabytes:
We have a large test base and want to test a particularly large change affecting the structure of the tables and requiring all data to be checked.
Clone the test database in snapshot mode:
And look what happened on the file system:
The data files of all tablespaces except TEMP are references to ACFS snapshot, and they do not take up disk space. You can find out how much snapshot really took:
What and sought - 286MB against more than 3GB
Naturally, if you actively begin to change clone blocks with snapshots, their place will grow.
After testing, delete the unnecessary clone:
We are convinced that the place is vacant:
As a result, we managed 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 claim to be a complete description of the Oracle Multitenant Architecture , only a special case is considered with reference to a specific task.
The essence of the technology is the ability to run multiple independent databases (pluggable database, PDB) within one instance (container database, CDB). Each base has its own set of schemas 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 a single container, and between containers. It is this opportunity that we will use to create copies of test databases and save resources.
Task - we have a large system, with a large base. It is necessary to carry out testing of changes, and the destructive changes - removal / modification of the tables. How it is done now - we create a new database, fill it with the minimum possible set of schemes and data and conduct testing. The process itself is not fast, time consuming and there is always the possibility of a mistake. Yes, and the "minimum data set" may not be so small.
In the documentation for the CREATE PLUGGABLE DATABASE command , in the cloning section there is a mention of 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
created copy on write snapshots and only modified blocks of the cloned database will take up disk space. Clone snapshots can be created either on ACFS or on specialized NAS .
')
The experiment was conducted in Oracle Virtualbox 4.2.14. I will not describe in detail the installation, it is well covered in the documentation, I will dwell only on important points.
Installation
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 an 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 the discovery path -> / dev / oracleasm / disks.
Using the asmcmd commands or using asmca, we create disk group (DATA) volume (DATAVOL) and ASM cluster filesystem with a mount point (/ data). From under root we mount ACFS and make sure that everything is fine:
$ mount | grep data /dev/asm/datavol-326 on /data type acfs (rw) 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 software installation (enterprise). After successful installation, run dbca and create a database - container: Create database -> Advanced mode -> Custom Database -> Create As Container Database (Create an Empty Container Database) .
As the Storage Type we specify ASM (+ DATA). All Database Components will be selected without editing. Character set you need to choose suitable for all databases that will be created in this container. We start the creation of the database and look forward to a successful completion.
As a result, we obtain a container base and a seed database (model database for creating a pluggable database).
We check that everything turned 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 represent 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) containing our PDB should appear on ACFS:
$ ls /data/oradata/ORCL/E0A1FFE9FFCF393FE0430F02000A6D7E/datafile/ o1_mf_sysaux_8x9131gt_.dbf o1_mf_system_8x912hvo_.dbf o1_mf_temp_8x914mbg_.dbf PDB after creating or restarting the container you need to open:
alter pluggable database all open; We simulate the creation of a test database - create a tablespace test of 2G size:
alter session set container=pdb001; -- create tablespace test datafile size 2g; Make sure that the data file is there and the size of its 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 What was it all for?
We have a large test base and want to test a particularly large change affecting the structure of the tables and requiring all data to be checked.
Clone the test database 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 The data files of all tablespaces except TEMP are references to ACFS snapshot, and they do not take up disk space. You can find out how much 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 and sought - 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 grow.
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 are convinced that the place is vacant:
$ acfsutil snap info /data number of snapshots: 0 snapshot space usage: 0 Result
As a result, we managed 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 claim to be a complete description of the Oracle Multitenant Architecture , only a special case is considered with reference to a specific task.
Source: https://habr.com/ru/post/185564/
All Articles