oracle_multitenant_overview

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
Next revision		 Previous revision
oracle_multitenant_overview [2021/11/11 17:58] – andonovjoracle_multitenant_overview [2021/11/13 21:51] (current) – andonovj
Line 6: Line 6:
  
 {{:screen_shot_2021-11-11_at_6.52.49_pm.png?1200|}} {{:screen_shot_2021-11-11_at_6.52.49_pm.png?1200|}}
 +
 +
 +With the multi-tenancy, we have a:
 +
 +  * root container - Where the system data resides. Also you can choose if you want the UNDO tablespace in the root to be used for all PDBs, or each PDB to have its own undo, SYSTEM and SYSAUX tablespace, making it completely independent from the root container.
 +  * Application Container - Container which consist of 1 or many PDBs which host a specific application. We are able to assign versions and name to such container, furthermore we can initiate installation and upgrade modes.
 +
 +===== Using SQL =====
 +Firstly, we need to create our container. From 12c, we can create a database to be either as it always has been, a normal database, OR we can create it to allow other databases as well. So it will be a container, let's see how we can do both:
 +
 +
 +==== Creation ===
 +Actually, the creation of a normal database isn't that hard, you can just type:
 +
 +<Code:bash|Create simple database>
 +SQL> Create database;
 +</Code>
 +
 +Simply that, will create us a normal database, well after that you will have to run the catproc and the catalog scripts, but you get the gist.
 +
 +
 +==== Create container DB =====
 +To create a container, database we NEED to allow it to have PLUGGABLE databases, as follows:
 +
 +<Code:bash|Create CDB>
 +SQL> CREATE DATABASE CDB1 ENABLE PLUGGABLE DATABASE SEED FILE_NAME_CONVERT = ('/loc1/data','/loc2/data');
 +</Code>
 +
 +That will create us a container database called CDB1 and a SEED, after that you will have to run script:
 +
 +@$ORACLE_HOME/rdbms/admin/catcdb.sql
 +
 +But other than that, all will be ok.
 +So we have a container, big deal, what then.
 +Well, now we can create PLUGGABLE databases as follows:
 +
 +
 +==== Create Pluggable DB (PDB) ====
 +As I said before, pluggable databases, are totally separate databases from one another. Imagine it having many databases (PDBs) within one big database (CDB). These PDBs, can have separate user totally individual from each other. So let's see how it is done:
 +
 +
 +<Code:bash|Create PDB>
 +CREATE PLUGGABLE DATABASE pdb2 ADMIN USER pdb_adm IDENTIFIED BY Password1
 +  FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdbseed/','/u01/app/oracle/oradata/cdb1/pdb2/');
 +</Code>
 +
 +You can use either:
 +
 +  * FILE_NAME_CONVERT, or
 +  * CREATE_FILE_DEST, or
 +  * PDB_FILE_NAME_CONVERT
 +
 +Either in the session level or on the CREATE statement, up to you, the result will be the same.
 +
 +Once created, you can list all PDBs in a certain CDB as follows:
 +
 +<Code:bash|List PDBs>
 +COLUMN pdb_name FORMAT A20
 +
 +SELECT pdb_name, status
 +FROM   dba_pdbs
 +ORDER BY pdb_name;
 +
 +PDB_NAME      STATUS
 +-------------------- -------------
 +PDB$SEED      NORMAL
 +PDB1      NORMAL
 +PDB2      NEW
 +PDB3      NEW
 +
 +COLUMN name FORMAT A20
 +
 +SELECT name, open_mode
 +FROM   v$pdbs
 +ORDER BY name;
 +
 +NAME        OPEN_MODE
 +------------------------------ ----------
 +PDB$SEED        READ ONLY
 +PDB1        MOUNTED
 +PDB2        MOUNTED
 +PDB3        MOUNTED
 +
 +</Code>
 +
 +Remember, 1 PDB can be in only 1 ROOT$CDB, but 1 CDB can have many PDBs.
 +Furthermore, from 12.2, we have also applicational containers, which are containers within the CDB, but we will speak about them in a second.
 +
 +===== Using DBCA =====
 +We can use DBCA to create and delete CDB and PDBs as follows:
 +
 +<Code:bash|Examples>
 +# Local container (cdb1).
 +dbca -silent -createDatabase \
 + -templateName General_Purpose.dbc \
 + -gdbname cdb1 -sid cdb1 -responseFile NO_VALUE \
 + -characterSet AL32UTF8 \
 + -sysPassword SysPassword1 \
 + -systemPassword SysPassword1 \
 + -createAsContainerDatabase true \
 + -numberOfPDBs 1 \
 + -pdbName pdb1 \
 + -pdbAdminPassword SysPassword1 \
 + -databaseType MULTIPURPOSE \
 + -memoryMgmtType auto_sga \
 + -totalMemory 2048 \
 + -storageType FS \
 + -datafileDestination "/u02/oradata/" \
 + -redoLogFileSize 50 \
 + -initParams encrypt_new_tablespaces=DDL \
 + -emConfiguration NONE \
 + -ignorePreReqs
 +
 +# Remote container (cdb3) with PDB (pdb5).
 +dbca -silent -createDatabase \
 + -templateName General_Purpose.dbc \
 + -gdbname cdb3 -sid cdb3 -responseFile NO_VALUE \
 + -characterSet AL32UTF8 \
 + -sysPassword SysPassword1 \
 + -systemPassword SysPassword1 \
 + -createAsContainerDatabase true \
 + -numberOfPDBs 1 \
 + -pdbName pdb5 \
 + -pdbAdminPassword SysPassword1 \
 + -databaseType MULTIPURPOSE \
 + -memoryMgmtType auto_sga \
 + -totalMemory 2048 \
 + -storageType FS \
 + -datafileDestination "/u02/oradata/" \
 + -redoLogFileSize 50 \
 + -initParams encrypt_new_tablespaces=DDL \
 + -emConfiguration NONE \
 + -ignorePreReqs
 +
 +# Non-CDB instance (db12c).
 +dbca -silent -createDatabase \
 + -templateName General_Purpose.dbc \
 + -gdbname db12c -sid db12c -responseFile NO_VALUE \
 + -characterSet AL32UTF8 \
 + -sysPassword SysPassword1 \
 + -systemPassword SysPassword1 \
 + -createAsContainerDatabase false \
 + -databaseType MULTIPURPOSE \
 + -memoryMgmtType auto_sga \
 + -totalMemory 2048 \
 + -storageType FS \
 + -datafileDestination "/u02/oradata/" \
 + -redoLogFileSize 50 \
 + -initParams encrypt_new_tablespaces=DDL \
 + -emConfiguration NONE \
 + -ignorePreReqs
 +
 +# Delete the instances.
 +#dbca -silent -deleteDatabase -sourceDB cdb1 -sysDBAUserName sys -sysDBAPassword SysPassword1
 +dbca -silent -deleteDatabase -sourceDB cdb3 -sysDBAUserName sys -sysDBAPassword SysPassword1
 +dbca -silent -deleteDatabase -sourceDB db12c -sysDBAUserName sys -sysDBAPassword SysPassword1
 +
 +#Configure OMF
 +export ORAENV_ASK=NO
 +export ORACLE_SID=cdb3
 +. oraenv
 +export ORAENV_ASK=YES
 +
 +sqlplus / as sysdba <<EOF
 +
 +ALTER SYSTEM SET db_create_file_dest = '/u02/oradata';
 +
 +SHUTDOWN IMMEDIATE;
 +STARTUP MOUNT;
 +ALTER DATABASE ARCHIVELOG;
 +ALTER DATABASE OPEN;
 +
 +ALTER PLUGGABLE DATABASE pdb5 OPEN;
 +ALTER PLUGGABLE DATABASE pdb5 SAVE STATE;
 +
 +EXIT;
 +EOF
 +</Code>
 +
 +===== Automation =====
 +Using the awesome power, vested to me by Vagrant and Bash, I summon the automation of:
 +
 +  * Virtual Machine
 +  * Oracle 19c database
 +  * 1 CDB
 +  * 1 PDB
 +
 +All that, twice:
 +
 +
 +<Code:bash|Automation>
 +$allow_shell = <<SCRIPT
 +sed -i 's/PasswordAuthentication no/PasswordAuthentication yes/g' /etc/ssh/sshd_config    
 +systemctl restart sshd.service
 +SCRIPT
 +
 +$edit_hosts_file = <<SCRIPT
 +cat > /etc/hosts <<EOF
 +127.0.0.1 localhost
 +192.168.0.l03 lpar1.example.com 
 +192.168.0.l04 lpar2.example.com 
 +EOF
 +SCRIPT
 +
 +$install_oracle_preinstall = <<SCRIPT
 +curl -o oracle-database-preinstall-19c-1.0-1.el7.x86_64.rpm https://yum.oracle.com/repo/OracleLinux/OL7/latest/x86_64/getPackage/oracle-database-preinstall-19c-1.0-1.el7.x86_64.rpm
 +yum -y localinstall oracle-database-preinstall-19c-1.0-1.el7.x86_64.rpm
 +SCRIPT
 +
 +$install_oracle_19c = <<SCRIPT
 +cd /vagrant
 +yum -y localinstall oracle-database-ee-19c-1.0-1.x86_64.rpm
 +
 +SCRIPT
 +
 +$install_19c_db = <<SCRIPT
 +runuser -l oracle -c '/opt/oracle/product/19c/dbhome_1/bin/dbca -silent -createDatabase -responseFile  /vagrant/simple_db_cdb.rsp'
 +SCRIPT
 +
 +$install_vnc_server = <<SCRIPT
 +yum -y install vnc-server
 +yum -y install tigervnc-server
 +yum -y install xterm
 +yum -y install net-tools
 +yum -y install unzip
 +yum -y install curl
 +SCRIPT
 +
 +
 +Vagrant.configure("2") do |config|
 + (1..2).each do |i|
 +  config.vm.provision :shell, inline: "setenforce 0", run: "always"
 +  config.vm.define "lpar#{i}" do |lpar|
 +    lpar.vm.box = "centos/7"
 +    lpar.vm.hostname = "lpar#{i}.example.com"
 +    lpar.vm.synced_folder "./lpar_binaries", "/vagrant", type: "virtualbox"
 + lpar.vm.network "public_network", ip: "192.168.0.10#{2+i}"
 + lpar.vm.network "private_network", ip: "10.100.0.10#{2+i}"
 + lpar.vm.provision "shell", inline: $install_vnc_server, privileged: true
 + lpar.vm.provision "shell", inline: $allow_shell, privileged: true
 + lpar.vm.provision "shell", inline: $edit_hosts_file, privileged: true
 + lpar.vm.provision "shell", inline: $install_oracle_preinstall, privileged: true
 + lpar.vm.provision "shell", inline: $install_oracle_19c, privileged: true
 + lpar.vm.provision "shell", inline: $install_19c_db, privileged: true
 +    lpar.vm.provider :virtualbox do |v|
 +      v.customize ["modifyvm", :id, "--memory", 2560]
 +      v.customize ["modifyvm", :id, "--name", "lpar#{2+i}"]
 +   v.customize ["storageattach", :id, 
 +                "--storagectl", "IDE", 
 +                "--port", "0", "--device", "1", 
 +                "--type", "dvddrive", 
 +                "--medium", "emptydrive"]    
 +     end
 +    end
 +  end
 +end
 +</Code>
 +
 +
 +Just make sure, you have the Oracle 19c RPM in folder called: "lpar_binaries" in your Vagrant main folder and the response file below:
 +
 +<Code:bash|Response file>
 +responseFileVersion=/oracle/assistants/rspfmt_dbca_response_schema_v12.2.0
 +gdbName=orcl.example.com
 +sid=orcl
 +databaseConfigType=SI
 +policyManaged=false
 +createServerPool=false
 +force=false
 +createAsContainerDatabase=true
 +numberOfPDBs=1
 +pdbName=pdb1
 +useLocalUndoForPDBs=true
 +pdbAdminPassword=Oracle123
 +templateName=/opt/oracle/product/19c/dbhome_1/assistants/dbca/templates/General_Purpose.dbc
 +sysPassword=Oracle123
 +systemPassword= Oracle123
 +emExpressPort=0
 +runCVUChecks=FALSE
 +dvConfiguration=false
 +olsConfiguration=false
 +datafileJarLocation={ORACLE_HOME}/assistants/dbca/templates/
 +datafileDestination={ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/
 +recoveryAreaDestination={ORACLE_BASE}/fast_recovery_area/{DB_UNIQUE_NAME}
 +storageType=FS
 +characterSet=AL32UTF8
 +registerWithDirService=false
 +variables=ORACLE_BASE_HOME=/opt/oracle/product/19c/dbhome_1,DB_UNIQUE_NAME=orcl,ORACLE_BASE=/opt/oracle,PDB_NAME=,DB_NAME=orcl,ORACLE_HOME=/opt/oracle/product/19c/dbhome_1,SID=orcl
 +initParams=undo_tablespace=UNDOTBS1,db_block_size=8KB,dispatchers=(PROTOCOL=TCP) (SERVICE={SID}XDB),diagnostic_dest={ORACLE_BASE},remote_login_passwordfile=EXCLUSIVE,db_create_file_dest={ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/,audit_file_dest={ORACLE_BASE}/admin/{DB_UNIQUE_NAME}/adump,processes=300,memory_target=936MB,db_recovery_file_dest_size=12732MB,open_cursors=300,db_domain=example.com,compatible=19.0.0,db_name=orcl,db_recovery_file_dest={ORACLE_BASE}/fast_recovery_area/{DB_UNIQUE_NAME},audit_trail=db
 +sampleSchema=false
 +memoryPercentage=40
 +databaseType=MULTIPURPOSE
 +automaticMemoryManagement=true
 +totalMemory=0
 +</Code>
 +
 +Of course feel free to add / remove or modify any parameter which you wish. Just bare in mind that some are mandatory :)
  • oracle_multitenant_overview.1636653512.txt.gz
  • Last modified: 2021/11/11 17:58
  • by andonovj