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.

    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.

    Also popular now: