Differences

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

--- oracle_multitenant_overview [2021/11/11 20:54] – andonovj
+++ oracle_multitenant_overview [2021/11/13 21:51] (current) – andonovj
@@ Line 13: / Line 13: @@
   * 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.
-=====Creation=====
+===== 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:
-==== Create Normal DB ===
+==== Creation ===
 Actually, the creation of a normal database isn't that hard, you can just type:
@@ Line 94: / Line 94: @@
 Furthermore, from 12.2, we have also applicational containers, which are containers within the CDB, but we will speak about them in a second.
-===== Management =====
+===== Using DBCA =====
-We can perform several actions with a PDB:
+We can use DBCA to create and delete CDB and PDBs as follows:
-  * OPEN
+<Code:bash|Examples>
-  * Plug & Unplig
+# Local container (cdb1).
-  * Close
+dbca -silent -createDatabase \
-  * Restore / Recover
+ -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
-==== Open ====
+# Remote container (cdb3) with PDB (pdb5).
-After we create a CDB, we need to open it, as follows:
+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
-<Code:bash|Open PDB>
+# Non-CDB instance (db12c).
-SQL> ALTER PLUGGABLE DATABASE pdb2 OPEN;
+dbca -silent -createDatabase \
-</Code>
+ -templateName General_Purpose.dbc \
+ -gdbname db12c -sid db12c -responseFile NO_VALUE \
-That command, will open us a database and it will become available to the users.
+ -characterSet AL32UTF8 \
-We can also close a pluggable database as follows:
+ -sysPassword SysPassword1 \
+ -systemPassword SysPassword1 \
-<Code:bash|Close PDB>
+ -createAsContainerDatabase false \
-ALTER PLUGGABLE DATABASE pdb2 CLOSE IMMEDIATE;
+ -databaseType MULTIPURPOSE \
-</Code>
+ -memoryMgmtType auto_sga \
+ -totalMemory 2048 \
+ -storageType FS \
+ -datafileDestination "/u02/oradata/" \
+ -redoLogFileSize 50 \
+ -initParams encrypt_new_tablespaces=DDL \
+ -emConfiguration NONE \
+ -ignorePreReqs
-==== Unplug a PDB ====
+# Delete the instances.
-In 12.1 we could unplug a PDB into .xml file and the migration of the PDB, would be the migration of that XML file AND the relevant database files.
+#dbca -silent -deleteDatabase -sourceDB cdb1 -sysDBAUserName sys -sysDBAPassword SysPassword1
-From 12.2 we can also unplug the PDB into a .pdb archive file, which contains information about the PDB AND the relevant database files. That will ease our migration.
+dbca -silent -deleteDatabase -sourceDB cdb3 -sysDBAUserName sys -sysDBAPassword SysPassword1
+dbca -silent -deleteDatabase -sourceDB db12c -sysDBAUserName sys -sysDBAPassword SysPassword1
-<Code:bash|Unplug to .pdb file>
+#Configure OMF
 export ORAENV_ASK=NO
 export ORACLE_SID=cdb3
@@ Line 126: / Line 169: @@
 export ORAENV_ASK=YES
-sqlplus / as sysdba
+sqlplus / as sysdba <<EOF
-ALTER PLUGGABLE DATABASE pdb5 CLOSE;
-ALTER PLUGGABLE DATABASE pdb5 UNPLUG INTO '/u01/pdb5.pdb';
-</Code>
-That assumes, you are using OMF file. The resulting file contains all the necessary data:
+ALTER SYSTEM SET db_create_file_dest = '/u02/oradata';
-<Code:bash|List .pdb file>
+SHUTDOWN IMMEDIATE;
-HOST ls -al /u01/pdb5.pdb
+STARTUP MOUNT;
--rw-r--r--. 1 oracle oinstall 161702502 Jan  7 21:01 /u01/pdb5.pdb
+ALTER DATABASE ARCHIVELOG;
+ALTER DATABASE OPEN;
-SQL>
+ALTER PLUGGABLE DATABASE pdb5 OPEN;
+ALTER PLUGGABLE DATABASE pdb5 SAVE STATE;
+EXIT;
+EOF
 </Code>
-====Plug a PDB====
+===== Automation =====
-To plug a database, we need the file from above and we need to verify the compatibility as follows:
+Using the awesome power, vested to me by Vagrant and Bash, I summon the automation of:
-<Code:bash|Plug a PDB>
-SET SERVEROUTPUT ON
-DECLARE
-  l_result BOOLEAN;
-BEGIN
-  l_result := DBMS_PDB.check_plug_compatibility(
-                pdb_descr_file => '/u01/pdb5.pdb',
-                pdb_name       => 'pdb5');
-  IF l_result THEN
+  * Virtual Machine
-    DBMS_OUTPUT.PUT_LINE('compatible');
+  * Oracle 19c database
-  ELSE
+  * 1 CDB
-    DBMS_OUTPUT.PUT_LINE('incompatible');
+  * 1 PDB
-  END IF;
-END;
-/
-compatible
-PL/SQL procedure successfully completed.
+All that, twice:
-SQL>
-</Code>
-=== Create a PDB using the source file ===
+<Code:bash|Automation>
-Then we can create a PDB using the .pdb file as follows:
+$allow_shell = <<SCRIPT
+sed -i 's/PasswordAuthentication no/PasswordAuthentication yes/g' /etc/ssh/sshd_config
+systemctl restart sshd.service
+SCRIPT
-<Code:bash|Plug-in PDB>
+$edit_hosts_file = <<SCRIPT
-CREATE PLUGGABLE DATABASE pdb5 USING '/u01/pdb5.pdb';
+cat > /etc/hosts <<EOF
+.0.0.1 localhost
+.168.0.l03 lpar1.example.com
+.168.0.l04 lpar2.example.com
+EOF
+SCRIPT
-ALTER PLUGGABLE DATABASE pdb5 OPEN READ WRITE;
+$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
-SELECT name, open_mode
+$install_oracle_19c = <<SCRIPT
-FROM   v$pdbs
+cd /vagrant
-ORDER BY name;
+yum -y localinstall oracle-database-ee-19c-1.0-1.x86_64.rpm
-NAME			       OPEN_MODE
+SCRIPT
------------------------------- ----------
-PDB$SEED		       READ ONLY
-PDB5			       READ WRITE
-SQL>
+$install_19c_db = <<SCRIPT
-</Code>
+runuser -l oracle -c '/opt/oracle/product/19c/dbhome_1/bin/dbca -silent -createDatabase -responseFile  /vagrant/simple_db_cdb.rsp'
+SCRIPT
-====Clone a PDB====
+$install_vnc_server = <<SCRIPT
-We can clone a PDB as follows:
+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
-<Code:bash|Cloning a PDB>
--- Setting the source to read-only is not necessary for Oracle 12cR2 with local undo, if you use 12.1 or 12.2 without local undo, it is needed:
-ALTER PLUGGABLE DATABASE pdb3 CLOSE;
-ALTER PLUGGABLE DATABASE pdb3 OPEN READ ONLY;
+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>
-CREATE PLUGGABLE DATABASE pdb4 FROM pdb3
+Just make sure, you have the Oracle 19c RPM in folder called: "lpar_binaries" in your Vagrant main folder and the response file below:
-  FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdb3/','/u01/app/oracle/oradata/cdb1/pdb4/');
-ALTER PLUGGABLE DATABASE pdb4 OPEN READ WRITE;
+<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 :)
--- Switch the source PDB back to read/write if you made it read-only.
-ALTER PLUGGABLE DATABASE pdb3 CLOSE;
-ALTER PLUGGABLE DATABASE pdb3 OPEN READ WRITE;
-</Code>