Oracle ACE Director Award - Deiby Gómez

Thanks #OracleACE Program for this awesome certificate recognizing the work I have done in the community for the last year. Looking forward to learn and share more content about Oracle Technology! 

 

 

 

 

 

 

Oracle 21c New Feature: INCLUDE and EXCLUDE parameters in expdp for the same job

 Starting in Oracle 21c, we can use the parameters INCLUDE and EXCLUDE in the same job for expdp. Before this you were able to use only one of these parameters for the same job.

 In the following example, I will export the table DGOMEZ.TABLE2 and excluding the table  DGOMEZ.TABLE1:

[oracle@localhost ~]$ expdp system/system@localhost:1521/pdb11 exclude=table:"IN('TABLE1')" include=table:"IN('TABLE2')" schemas='DGOMEZ'

Export: Release 21.0.0.0.0 - Production on Mon May 02 03:39:07 2022
Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/********@localhost:1521/pdb11 exclude=table:IN('TABLE1') include=table:IN('TABLE2') schemas=DGOMEZ
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . exported "DGOMEZ"."TABLE2"                           5.054 KB       1 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  /u01/app/oracle/product/19.0.0/db_base/admin/orcl/dpdump/E8D8634299033724E055000000000001/expdat.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Mon May 02 03:39:29 2022 elapsed 0 00:00:19

[oracle@localhost ~]$

 

  In the following example, I will export the table DGOMEZ.TABLE1 and excluding the table  DGOMEZ.TABLE2:

 [oracle@localhost ~]$ expdp system/****@localhost:1521/pdb11 exclude=table:"IN('TABLE2')" include=table:"IN('TABLE1')" schemas='DGOMEZ'

Export: Release 21.0.0.0.0 - Production on Mon May 02 03:40:00 2022
Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/********@localhost:1521/pdb11 exclude=table:IN('TABLE2') include=table:IN('TABLE1') schemas=DGOMEZ
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . exported "DGOMEZ"."TABLE1"                           5.054 KB       1 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  /u01/app/oracle/product/19.0.0/db_base/admin/orcl/dpdump/E8D8634299033724E055000000000001/expdat.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Mon May 02 03:40:27 2022 elapsed 0 00:00:25

[oracle@localhost ~]$

New parameter CPU_MIN_COUNT in Oracle Database 19c

Oracle Database 19c has introduced several new features for Multitenant among them we can mention the parameter  CPU_MIN_COUNT that specify a minimum number of CPUs for the PDB. Oracle documentation says the following about this parameter:

CPU_MIN_COUNT specifies the minimum number of CPUs required by a pluggable database (PDB) at any given time.

This parameter specifies the minimum number of CPUs required by a PDB at any given time. For multi-threaded CPUs, this number corresponds to CPU threads, not CPU cores.

You can set this parameter at the CDB level, and for each individual PDB. This enables you to control each PDBs minimum share of CPU utilization within a CDB. If the sum of the CPU_MIN_COUNT values across all open PDBs in a CDB is equal to the value of CPU_MIN_COUNT for the CDB, then the CDB instance is considered full. If the sum exceeds the value of CPU_MIN_COUNT for the CDB, then the CDB instance is over-provisioned. Oracle does not prevent you from over-provisioning a CDB.

Resource Manager is enabled at the CDB level by setting the RESOURCE_MANAGER_PLAN at the root level to the name of a CDB resource plan. If the CDB resource plan has no configured CPU directives, that is, the SHARES and UTILIZATION_LIMIT directives are unset, then Resource Manager uses the CPU_COUNT and CPU_MIN_COUNT settings for the PDB to manage CPU utilization.

 

Reference: https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/CPU_MIN_COUNT.html#GUID-C0EFFDF8-951A-4C36-B2A9-925AA899647D

 

 

 

 How to change it:

At PDB Level:

SQL> show con_name

CON_NAME
------------------------------
PDB11

SQL> alter system set cpu_min_count=2 scope=both;

System altered.

SQL>

 
SQL> show parameters cpu_min_count

NAME               TYPE        VALUE
------------------ ----------- ------------------------------
cpu_min_count      string      2
SQL>

 At CDB Level: 

SQL>  show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> alter system set cpu_min_count=2 scope=both;

System altered.

SQL>  show parameters cpu_min_count

NAME               TYPE        VALUE
------------------ ----------- ------------------------------
cpu_min_count      string      1
SQL>

 

 

How to Clone a PDB into another CDB in Oracle 21c

 For this article I will use 2 Container Databases:

• orcl
• orcl2

[oracle@localhost ~]$ ps -ef |grep pmon
oracle     17189       1  0 Sep16 ?        00:00:00 ora_pmon_orcl2
oracle     22514       1  0 00:40 ?        00:00:00 ora_pmon_orcl
[oracle@localhost ~]$

The "orcl" container database has two Pluggable databases:

 SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB11                          READ WRITE NO
         4 PDB12                          READ WRITE NO

The "orcl2" container database has 0 Pluggable databases. 

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
SQL>

The first step is to grant "create pluggable database" privilege to the user that we will use to clone the database, this privilege has to be granted inside the pdb that we want to clone, in this case is ORCL:PDB11.

In CDB "orcl":

SQL> alter session set container=pdb11;

Session altered.

SQL> grant create pluggable database to system;

Grant succeeded.

SQL>


In CDB "orcl2":

While connected to the CDB$ROOT:

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
SQL>

Creating a Database Link in "orcl2" poiting to the pdb "pdb11" in cdb "orcl":

SQL> create database link cdb_orcl1 connect to system identified by system using '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=pdb11)))';

Database link created.


Clonning the PDB into "orcl" CDB:

SQL> create pluggable database pdb11_clone from pdb11@cdb_orcl1 file_name_convert=('pdb11','pdb11_clone') parallel 3;

Pluggable database created.

SQL>

Opening the PDB in "orcl" CDB:

SQL> alter pluggable database pdb11_clone open;

Pluggable database altered.

Checking that the PDB was successfully cloned:

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB11_CLONE                    READ WRITE NO
 

How to use the parameters max_idle_blocker_time in Oracle 21c

 

 In Oracle 21c we have two new parameters.

• MAX_IDLE_TIME
• MAX_IDLE_BLOCKER_TIME

For the parameter MAX_IDLE_BLOCKER_TIME, oracle documentation provides the following definition:

MAX_IDLE_BLOCKER_TIME: Sets limits only for idle sessions consuming resources. It specifies the maximum number of minutes that a blocking session can be idle. After that point, the session is automatically terminated.

More info: https://docs.oracle.com/en/database/oracle/oracle-database/21/refrn/MAX_IDLE_BLOCKER_TIME.html

 

Let's test how it works:

I will Logging into a PDB with the user "SYS".

SQL> show con_name

CON_NAME
------------------------------
PDB11

Setting the parameter: 

SQL> alter system set  set max_idle_blocker_time=1 scope=both;

System altered.

Session 1: I will create a session with the user "DGOMEZ" and I will perform one update against the table dgomez.table1 so that this sessions is "consuming resources" and is blocking the other session.

22:42:05 SQL> update dgomez.table1 set col1=2 where col1=1;

1 row updated.

 Session 2: In another session, I will create another session with the user "DGOMEZ" and I will execute the same update, so that this session will be blocked by the session 1.

 SQL> update dgomez.table1 set col1=2 where col1=1;

The session 2 was waiting for 1 minutes and the I got the output:

1 row updated.

SQL>

Session 1: I saw that status of the session 1 by executing a SELECT statement and I confirmed it was killed:

22:42:39 SQL> select 1 from dual;
select 1 from dual
              *
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 18351
Session ID: 100 Serial number: 55963

 

As expected, the session 1 was killed after 1 minutes of being using resources and being blocking another session.