Cloning Oracle Databases from Non-CDB to PDB

Introduction

In a previous article we explored how to use the Oracle REMOTE CLONE PDB and the REFRESHABLE PDB functionalities to do a migration from Oracle Databases in Windows (on-prem or from other cloud providers) to Linux-based OCI Oracle Databases.

In that article we briefly discussed that these features can also be used when cloning a Non-CDB database into a PDB one.

Now we are going to look at the process of how to clone a Non-CDB into a PDB in more detail.

Prerequisites for CLONE

The prerequisites that apply are the same as the ones in the previous article : endianness, database options, database version -12.1.0.2 or higher for “Cold” Clone (Source DB open READ ONLY) or 12.2 or higher for Hot Clone-, character sets, open mode for the Source DB.

As we did in that article, we will be describing the HOT CLONE method since it does not interrupt the source database transactional activities during the process.

REMOTE HOT CLONE from Non-CDB to a PDB process

We are going to clone a non-CDB Oracle Database (Oracle 19.14 Standard Edition on Oracle Enterprise Linux Server 7.9) to a multitenant (CDB) Database system (Oracle 19.18 Enterprise Edition on Oracle Linux Server release 7.9). Note that the cloning can be done between Standard and Enterprise editions.

We check the Source DB system (non-CDB, Archive Log, Local Undo, Character Sets):

[oracle@calinstancedb19st ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Oct 18 14:22:27 2023
Version 19.14.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production
Version 19.14.0.0.0

-- Check if Multitenant:
SQL> SELECT CDB FROM V$DATABASE;

CDB
---
NO

-- Archive Log:
SQL> ARCHIVE LOG LIST;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/product/19c/dbhome_1/dbs/arch
Oldest online log sequence     49
Next log sequence to archive   51
Current log sequence           51


-- Local Undo
SQL> SELECT * FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME='LOCAL_UNDO_ENABLED';

PROPERTY_NAME       PROPERTY_VALUE  DESCRIPTION
------------------  --------------  -----------------------------
LOCAL_UNDO_ENABLED  TRUE            true if local undo is enabled


-- Character sets:
SQL> SELECT VALUE FROM NLS_DATABASE_PARAMETERS
  2  WHERE PARAMETER = 'NLS_CHARACTERSET';

VALUE
--------
AL32UTF8

SQL> SELECT VALUE FROM NLS_DATABASE_PARAMETERS
  2  WHERE PARAMETER = 'NLS_NCHAR_CHARACTERSET';

VALUE
---------
AL16UTF16

SQL>

If any of the prerequisites is not met, we must change them (E.g: ALTER DATABASE ARCHIVELOG) or consider to do a COLD CLONE instead (using ALTER DATABASE OPEN READ ONLY).

Then we create the clone user in Source DB for the cloning process and GRANT CREATE SESSION, CREATE PLUGGABLE DATABASE to it.

-- Clone User.
SQL> CREATE USER CLONE_USER IDENTIFIED BY Oracle_123456;

User created.

SQL> GRANT CREATE SESSION, CREATE PLUGGABLE DATABASE TO CLONE_USER;

Grant succeeded.

SQL>

Now, we change to Target DB to check (Multitenant, Character Sets, Existing PDB’s):

[oracle@caldbcs3 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Oct 18 14:31:33 2023
Version 19.18.0.0.0

Copyright (c) 1982, 2022, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c EE High Perf Release 19.0.0.0.0 - Production
Version 19.18.0.0.0

-- Check if Multitenant:
SYS@CALDBCS3> SELECT CDB FROM V$DATABASE;

CDB
---
YES

-- Character sets:
SYS@CALDBCS3> SELECT VALUE FROM NLS_DATABASE_PARAMETERS 
   2  WHERE PARAMETER = 'NLS_CHARACTERSET';

VALUE
--------
AL32UTF8

SYS@CALDBCS3> SELECT VALUE FROM NLS_DATABASE_PARAMETERS
   2  WHERE PARAMETER = 'NLS_NCHAR_CHARACTERSET';

VALUE
---------
AL16UTF16

-- PDBs:
SYS@CALDBCS3> SHOW PDBS;

CON_ID CON_NAME                       OPEN MODE  RESTRICTED
------ ------------------------------ ---------- ----------
     2 PDB$SEED                       READ ONLY  NO
     4 CALDBCS3_PDB1                  READ WRITE NO

SYS@CALDBCS3>

Now we create the DBLink to the Source DB and connect with the clone user (don’t forget GLOBAL_NAMES).

-- DBLink:
SYS@CALDBCS3> SHOW PARAMETER GLOBAL_NAMES;

NAME         TYPE        VALUE
------------ ----------- -----
global_names boolean     TRUE

SYS@CALDBCS3> alter session set global_names=FALSE;

Session altered.

SYS@CALDBCS3> CREATE DATABASE LINK ORADB19ST_LINK 
  2  CONNECT TO CLONE_USER IDENTIFIED BY Oracle_123456
  3  USING 'calinstancedb19st:1521/ORCL';

Database link created.

SYS@CALDBCS3>  SELECT * FROM DUAL@ORADB19ST_LINK;

D
-
X

SYS@CALDBCS3>

We can now proceed with the CLONE PDB process. We must provide the password for the target TDE Wallet since Target DB is using TDE.

-- CREATE PDB (CLONE)
SYS@CALDBCS3> SHOW PDBS;

CON_ID CON_NAME      OPEN MODE  RESTRICTED
------ ------------- ---------- ----------
     2 PDB$SEED      READ ONLY  NO
     4 CALDBCS3_PDB1 READ WRITE NO

SYS@CALDBCS3> CREATE PLUGGABLE DATABASE CALDBCS3_PDB2 
  2  FROM ORCL@ORADB19ST_LINK 
  3  KEYSTORE IDENTIFIED BY ***************;

Pluggable database created.

SYS@CALDBCS3> SHOW PDBS;

CON_ID CON_NAME      OPEN MODE  RESTRICTED
------ ------------- ---------- ----------
     2 PDB$SEED      READ ONLY  NO
     3 CALDBCS3_PDB2 MOUNTED              <-- MOUNTED
     4 CALDBCS3_PDB1 READ WRITE NO
SYS@CALDBCS3>

The new PDB appears as MOUNTED.

Now is when we must convert it to a proper PDB executing the script noncdb_to_pdb.sql. This step is done with the PDB in MOUNTED state.

-- noncdb_to_pdb.sql (in MOUNTED state)
SYS@CALDBCS3> ALTER SESSION SET CONTAINER=CALDBCS3_PDB2;

Session altered.

SYS@CALDBCS3> @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql
SYS@CALDBCS3> SET FEEDBACK 1
SYS@CALDBCS3> SET NUMWIDTH 10
SYS@CALDBCS3> SET LINESIZE 80
SYS@CALDBCS3> SET TRIMSPOOL ON
SYS@CALDBCS3> SET TAB OFF
SYS@CALDBCS3> SET PAGESIZE 100
SYS@CALDBCS3> SET VERIFY OFF
SYS@CALDBCS3>
SYS@CALDBCS3> WHENEVER SQLERROR EXIT;
SYS@CALDBCS3>
SYS@CALDBCS3> DOC
DOC>#######################################################################
DOC>#######################################################################
DOC>   The following statement will cause an "ORA-01403: no data found"
DOC>   error if we're not in a PDB.
DOC>   This script is intended to be run right after plugin of a PDB,
DOC>   while inside the PDB.
DOC>#######################################################################
DOC>#######################################################################
DOC>#
SYS@CALDBCS3>

...<snipped>

SYS@CALDBCS3> alter session set "_enable_view_pdb"=false;

Session altered.

SYS@CALDBCS3>
SYS@CALDBCS3> -- leave the PDB in the same state it was when we started
SYS@CALDBCS3> BEGIN
  2    execute immediate '&open_sql &restricted_state';
  3  EXCEPTION
  4    WHEN OTHERS THEN
  5    BEGIN
  6      IF (sqlcode <> -900) THEN
  7        RAISE;
  8      END IF;
  9    END;
 10  END;
 11  /

PL/SQL procedure successfully completed.

SYS@CALDBCS3> SHOW PDBS;

CON_ID CON_NAME      OPEN MODE  RESTRICTED
------ ------------- ---------- ----------
     2 PDB$SEED      READ ONLY  NO
     3 CALDBCS3_PDB2 MOUNTED              <-- MOUNTED
     4 CALDBCS3_PDB1 READ WRITE NO
SYS@CALDBCS3>

The output is quite long (I’ve snipped it), but after a while we get the new PDB almost ready (and still in MOUNTED state).

If we try to open it now we get the «Warning: PDB altered with errors." message and the RESTRICTED open mode because of the different patch level between Source DB and Target DB:

-- OPEN PDB:
SYS@CALDBCS3> SHOW PDBS;

CON_ID CON_NAME      OPEN MODE  RESTRICTED
------ ------------- ---------- ----------
     2 PDB$SEED      READ ONLY  NO
     3 CALDBCS3_PDB2 MOUNTED
     4 CALDBCS3_PDB1 READ WRITE NO

SYS@CALDBCS3> ALTER PLUGGABLE DATABASE CALDBCS3_PDB2 OPEN;

Warning: PDB altered with errors.

SYS@CALDBCS3>  SHOW PDBS;

CON_ID CON_NAME      OPEN MODE  RESTRICTED
------ ------------- ---------- ----------
     2 PDB$SEED      READ ONLY  NO
     3 CALDBCS3_PDB2 READ WRITE YES       <-- RESTRICTED
     4 CALDBCS3_PDB1 READ WRITE NO

SYS@CALDBCS3>

So, we must follow the usual path: check violations in PDB_PLUG_IN_VIOLATIONS and run datapatch if required:

SYS@CALDBCS3> ALTER SESSION SET CONTAINER=CALDBCS3_PDB2;

Session altered.

SYS@CALDBCS3> SELECT status, type, cause, message 
  2  FROM pdb_plug_in_violations;

STATUS    TYPE      CAUSE
--------- --------- ---------
MESSAGE
---------------------------------------------------------
RESOLVED  WARNING   OPTION
Database option APS mismatch: PDB installed version NULL. CDB installed version 19.0.0.0.0.

RESOLVED  WARNING   OPTION
Database option DV mismatch: PDB installed version NULL. CDB installed version 19.0.0.0.0.

RESOLVED  WARNING   OPTION
Database option OLS mismatch: PDB installed version NULL. CDB installed version 19.0.0.0.0.

RESOLVED  WARNING   OPTION
Database option RAC mismatch: PDB installed version NULL. CDB installed version 19.0.0.0.0.

RESOLVED  WARNING   OPTION
Database option SDO mismatch: PDB installed version NULL. CDB installed version 19.0.0.0.0.

RESOLVED  WARNING   OPTION
Database option XOQ mismatch: PDB installed version NULL. CDB installed version 19.0.0.0.0.

RESOLVED  ERROR     Non-CDB to PDB
PDB plugged in is a non-CDB, requires noncdb_to_pdb.sql be run.

PENDING   ERROR     SQL Patch
Interim patch 34786990/25032666 (OJVM RELEASE UPDATE: 19.18.0.0.230117 (34786990)): Installed in the CDB but not in the PDB

PENDING   ERROR     SQL Patch
Interim patch 33561310/24538862 (OJVM RELEASE UPDATE: 19.14.0.0.220118 (33561310)): Not installed in the CDB but installed in the PDB

PENDING   ERROR     SQL Patch
'19.18.0.0.0 Release_Update 2301270055' is installed in the CDB but '19.14.0.0.0 Release_Update 2112251221' is installed in the PDB

PENDING   WARNING   is encrypted tablespace?
Tablespace SYSTEM is not encrypted. Oracle Cloud mandates all tablespaces should be encrypted.

PENDING   WARNING   is encrypted tablespace?
Tablespace SYSAUX is not encrypted. Oracle Cloud mandates all tablespaces should be encrypted.

PENDING   WARNING   is encrypted tablespace?
Tablespace USERS is not encrypted. Oracle Cloud mandates all tablespaces should be encrypted.


13 rows selected.

SYS@CALDBCS3> select cause, type, action 
  2  from pdb_plug_in_violations;

CAUSE
---------------------------------------------
TYPE
---------------------------------------------
ACTION
---------------------------------------------
OPTION
WARNING
Fix the database option in the PDB or the CDB

OPTION
WARNING
Fix the database option in the PDB or the CDB

OPTION
WARNING
Fix the database option in the PDB or the CDB

OPTION
WARNING
Fix the database option in the PDB or the CDB

OPTION
WARNING
Fix the database option in the PDB or the CDB

OPTION
WARNING
Fix the database option in the PDB or the CDB

Non-CDB to PDB
ERROR
Run noncdb_to_pdb.sql.

SQL Patch
ERROR
Call datapatch to install in the PDB or the CDB

SQL Patch
ERROR
Call datapatch to install in the PDB or the CDB

SQL Patch
ERROR
Call datapatch to install in the PDB or the CDB

is encrypted tablespace?
WARNING
Encrypt the tablespace.

is encrypted tablespace?
WARNING
Encrypt the tablespace.

is encrypted tablespace?
WARNING
Encrypt the tablespace.


13 rows selected.

SYS@CALDBCS3>

SYS@CALDBCS3> EXIT
Disconnected from Oracle Database 19c EE High Perf Release 19.0.0.0.0 - Production
Version 19.18.0.0.0

[oracle@caldbcs3 ~]$ cd $ORACLE_HOME/OPatch
[oracle@caldbcs3 OPatch]$ ./datapatch -verbose
SQL Patching tool version 19.18.0.0.0 Production on Wed Oct 18 15:18:25 2023
Copyright (c) 2012, 2023, Oracle.  All rights reserved.

Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_77286_2023_10_18_15_18_25/sqlpatch_invocation.log

Connecting to database...OK
Gathering database info...done

Note:  Datapatch will only apply or rollback SQL fixes for PDBs
       that are in an open state, no patches will be applied to closed PDBs.
       Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation
       (Doc ID 1585822.1)

Bootstrapping registry and package to current versions...done
Determining current state...done

Current state of interim SQL patches:
Interim patch 33561310 (OJVM RELEASE UPDATE: 19.14.0.0.220118 (33561310)):
  Binary registry: Not installed
  PDB CALDBCS1_PDB1: Not installed
  PDB CALDBCS3_PDB2: Applied successfully on 03-OCT-22 11.56.18.315748 AM
  PDB CDB$ROOT: Not installed
  PDB PDB$SEED: Not installed
Interim patch 33808367 (OJVM RELEASE UPDATE: 19.15.0.0.220419 (33808367)):
  Binary registry: Not installed
  PDB CALDBCS1_PDB1: Rolled back successfully on 25-JUL-23 10.30.05.591534 AM
  PDB CALDBCS3_PDB2: Not installed
  PDB CDB$ROOT: Rolled back successfully on 25-JUL-23 10.29.56.052091 AM
  PDB PDB$SEED: Rolled back successfully on 25-JUL-23 10.30.00.836619 AM
Interim patch 34786990 (OJVM RELEASE UPDATE: 19.18.0.0.230117 (34786990)):
  Binary registry: Installed
  PDB CALDBCS1_PDB1: Applied successfully on 25-JUL-23 10.30.10.320358 AM
  PDB CALDBCS3_PDB2: Not installed
  PDB CDB$ROOT: Applied successfully on 25-JUL-23 10.30.00.815902 AM
  PDB PDB$SEED: Applied successfully on 25-JUL-23 10.30.05.575750 AM

Current state of release update SQL patches:
  Binary registry:
    19.18.0.0.0 Release_Update 230127005551: Installed
  PDB CALDBCS3_PDB1:
    Applied 19.18.0.0.0 Release_Update 230127005551 successfully on 25-JUL-23 10.30.10.316032 AM
  PDB CALDBCS3_PDB2:
    Applied 19.14.0.0.0 Release_Update 211225122123 successfully on 03-OCT-22 11.56.18.309998 AM
  PDB CDB$ROOT:
    Applied 19.18.0.0.0 Release_Update 230127005551 successfully on 25-JUL-23 10.30.00.810350 AM
  PDB PDB$SEED:
    Applied 19.18.0.0.0 Release_Update 230127005551 successfully on 25-JUL-23 10.30.05.570170 AM

Adding patches to installation queue and performing prereq checks...done
Installation queue:
  For the following PDBs: CDB$ROOT PDB$SEED CALDBCS1_PDB1
    No interim patches need to be rolled back
    No release update patches need to be installed
    No interim patches need to be applied
  For the following PDBs: CALDBCS3_PDB2
    The following interim patches will be rolled back:
      33561310 (OJVM RELEASE UPDATE: 19.14.0.0.220118 (33561310))
    Patch 34765931 (DATABASE RELEASE UPDATE : 19.18.0.0.230117 (REL-JAN230131) (34765931)):
      Apply from 19.14.0.0.0 Release_Update 211225122123 to 19.18.0.0.0 Release_Update 230127005551
    The following interim patches will be applied:
      34786990 (OJVM RELEASE UPDATE: 19.18.0.0.230117 (34786990))


WARNING: Following components are NOT in a valid state.
         This could cause patching failure. If it does, consider
         running utlrp.sql to bring components to VALID state.
         Then, re-run datapatch.

         CALDBCS3_PDB2 : CATPROC [INVALID], SDO [INVALID]

Installing patches...
Patch installation complete.  Total patches installed: 3

Validating logfiles...done
Patch 33561310 rollback (pdb CALDBCS3_PDB2): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/33561310/24538862/33561310_rollback_CALDBCS1_CALDBCS3_PDB2_2023Oct18_15_18_54.log (no errors)
Patch 34765931 apply (pdb CALDBCS3_PDB2): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/34765931/25098466/34765931_apply_CALDBCS1_CALDBCS3_PDB2_2023Oct18_15_19_01.log (no errors)
Patch 34786990 apply (pdb CALDBCS3_PDB2): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/34786990/25032666/34786990_apply_CALDBCS1_CALDBCS3_PDB2_2023Oct18_15_19_01.log (no errors)
SQL Patching tool complete on Wed Oct 18 15:21:22 2023
[oracle@caldbcs3 OPatch]$

If required, we recompile the objects running the sql script utlrp.sql

SYS@CALDBCS3> ALTER SESSION SET CONTAINER=CALDBCS3_PDB2;

Session altered.

SYS@CALDBCS3> @$ORACLE_HOME/rdbms/admin/utlrp.sql

Session altered.


TIMESTAMP
----------------------------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN              2023-10-18 15:22:58

DOC>   The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC>   objects in the database. Recompilation time is proportional to the
DOC>   number of invalid objects in the database, so this command may take
DOC>   a long time to execute on a database with a large number of invalid
DOC>   objects.
DOC>
DOC>   Use the following queries to track recompilation progress:
DOC>
DOC>   1. Query returning the number of invalid objects remaining. This
DOC>      number should decrease with time.
DOC>         SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC>   2. Query returning the number of objects compiled so far. This number
DOC>      should increase with time.
DOC>         SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC>   This script automatically chooses serial or parallel recompilation
DOC>   based on the number of CPUs available (parameter cpu_count) multiplied
DOC>   by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC>   On RAC, this number is added across all RAC nodes.
DOC>
DOC>   UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC>   recompilation. Jobs are created without instance affinity so that they
DOC>   can migrate across RAC nodes. Use the following queries to verify
DOC>   whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC>   1. Query showing jobs created by UTL_RECOMP
DOC>         SELECT job_name FROM dba_scheduler_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC>   2. Query showing UTL_RECOMP jobs that are running
DOC>         SELECT job_name FROM dba_scheduler_running_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#

PL/SQL procedure successfully completed.


TIMESTAMP
----------------------------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END              2023-10-18 15:22:58

DOC> The following query reports the number of invalid objects.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#

OBJECTS WITH ERRORS
-------------------
                  0

DOC> The following query reports the number of exceptions caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC> Note: Typical compilation errors (due to coding errors) are not
DOC>       logged into this table: they go into DBA_ERRORS instead.
DOC>#

ERRORS DURING RECOMPILATION
---------------------------
                          0


Function created.


PL/SQL procedure successfully completed.


Function dropped.


PL/SQL procedure successfully completed.

SYS@CALDBCS3>

Finally, we check, CLOSE and OPEN the new PDB and SAVE the STATE:

-- Check, CLOSE & OPEN:
SYS@CALDBCS3> ALTER SESSION SET CONTAINER=CDB$ROOT;

Session altered.

SYS@CALDBCS3> ALTER PLUGGABLE DATABASE CALDBCS3_PDB2 CLOSE IMMEDIATE;

Pluggable database altered.

SYS@CALDBCS3> ALTER PLUGGABLE DATABASE CALDBCS3_PDB2 OPEN;

Pluggable database altered.

SYS@CALDBCS3> SHOW PDBS;

CON_ID CON_NAME                       OPEN MODE  RESTRICTED
------ ------------------------------ ---------- ----------
     2 PDB$SEED                       READ ONLY  NO
     3 CALDBCS3_PDB2                  READ WRITE NO
     4 CALDBCS3_PDB1                  READ WRITE NO
SYS@CALDBCS3>


-- SAVE STATE:
SYS@CALDBCS3> ALTER PLUGGABLE DATABASE CALDBCS3_PDB2 SAVE STATE;

Pluggable database altered.

SYS@CALDBCS3>

At this point we have the Non-CDB Source DB cloned into a full operational PDB (CALDBCS3_PDB2) in the Target CDB.

Cheers.

Carlos.

CLONE PDB options as a Migration Method for Windows Databases to Oracle Cloud Infrastructure

Introduction

When migrating on-prem Oracle databases (or databases in other cloud providers) to OCI there are several methods available, like Zero Downtime Migration (ZDM) and Database Migration (DMS) tools, Data Pump Export / Import, Data Guard, etc…

Oracle recommends using ZDM / DMS as the preferred migration methods because they are automated and simplified processes that provide other benefits like performance, built-in security, and minimal downtime.

But ZDM and DMS don’t support Oracle databases on Windows systems as migration sources. Oracle databases on Windows are not the most frequent ones, but still there are customers that have their databases on these platforms.

The Oracle REMOTE CLONE PDB and the REFRESHABLE PDB functionalities can be an alternative in these cases.

Here we’ll explore how to do it.

Prerequisites for CLONE

These methods work only if the on-premises platform is little endian and the on-premises database release is 12.1.0.2 or higher for “Cold” Clone (Source DB open READ ONLY) or 12.2 or higher for Hot Clone / Refreshable PDB (Source DB open READ WRITE).

The source and target platforms must meet the following requirements:

• They must have the same endianness (note that this is not “same OS”).
• The database options installed on the source platform must be the same as, or a subset of, the database options installed on the target platform.

Prerequisites for Database Versions:

• Starting from 12.2 the source and target versions can be different.

Prerequisites for Character Sets:

• If the character set of the CDB to which the PDB is being cloned is not AL32UTF8, then the source and target must have compatible character sets and national character sets.
• If the character set of the CDB to which the PDB is being cloned is AL32UTF8, then this requirement does not apply.

Prerequisites for the Open Mode of the Source PDB:

• The source PDB must not be closed (can be open READ ONLY or READ WRITE).
• If the remote CDB is not in LOCAL UNDO mode, then the source PDB must be open in READ ONLY mode (COLD CLONE).
• If the remote CDB is not in ARCHIVELOG mode, then the source PDB must be open in READ ONLY mode (COLD CLONE).
• To do a HOT CLONE (the source database open READ WRITE and accessible for ongoing transactions) the Source DB must be 12.2 or later, PDB must be in LOCAL UNDO mode and ARCHIVELOG mode (archived logs on source database must be retained on disk until the clone of the PDB/non-CDB has completed. The archived logs are needed to roll forward and make data files consistent). This also applies for REFRESHABLE PDBs.

We will be describing the HOT CLONE method since it does not interrupt the source database transactional activities during the process.

REMOTE HOT CLONE process

We are going to migrate PDB1 PDB from Windows Oracle Database (Oracle 19.3 on Windows Server 2019) to an OCI Base Database system (Oracle 19.18 on Oracle Linux Server release 7.9).

We check the PDB is open in Source system:

SYS@calwindows1_cdb> SHOW PDBS;
CON_ID CON_NAME                  OPEN MODE  RESTRICTED
------ ------------------------- ---------- ----------
     2 PDB$SEED                  READ ONLY  NO
     3 PDB1                      READ WRITE NO
SYS@calwindows1_cdb>

We check prerequisites in Windows Source DB (we are assuming right endianness, character set AL32UTF8 and Oracle version):

SYS@calwindows1_cdb> SELECT log_mode FROM v$database;
LOG_MODE
------------
ARCHIVELOG

SYS@calwindows1_cdb> SELECT * FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME='LOCAL_UNDO_ENABLED';

PROPERTY_NAME       PROPERTY_VALUE  DESCRIPTION
------------------  --------------  -----------------------------
LOCAL_UNDO_ENABLED  TRUE            true if local undo is enabled

SYS@calwindows1_cdb> SELECT C.CON_ID, P.NAME , C.TABLESPACE_NAME, C.STATUS FROM CDB_TABLESPACES C,V$CONTAINERS P WHERE C.TABLESPACE_NAME LIKE 'UNDO%' AND  C.CON_ID=P.CON_ID ORDER BY C.CON_ID;

CON_ID NAME       TABLESPACE_NAME STATUS
------ --------   --------------- ------
     1 CDB$ROOT   UNDOTBS1        ONLINE
     3 PDB1       UNDOTBS1        ONLINE

SYS@calwindows1_cdb>

If any of the prerequisites is not met, we must change them (E.g: ALTER DATABASE ARCHIVELOG and/or ALTER DATABASE LOCAL UNDO ON) or do a COLD CLONE instead (put PDB1 in READ ONLY MODE during the cloning process).

Now we must create the clone user in PDB1 for the cloning process and GRANT CREATE SESSION, CREATE PLUGGABLE DATABASE to it:

SYS@calwindows1_cdb> ALTER SESSION SET CONTAINER=PDB1;

Session altered.

SYS@calwindows1_cdb> CREATE USER CLONE_USER IDENTIFIED BY Oracle_123456;

User created.

SYS@calwindows1_cdb> GRANT CREATE SESSION, CREATE PLUGGABLE DATABASE TO CLONE_USER;

Grant succeeded.

SYS@calwindows1_cdb>

In the OCI Target DB we must create a DBLink to the Windows Database and connect with the clone user that we have just created and then check if it works (here we are assuming that the communications between Source DB and Target DB are working OK):

SYS@caldbcs3_cdb> create database link ORCLWIN_PDB1_LINK connect to clone_user identified by Oracle_123456 using '<Windows host>:1521/PDB1';

Database link created.

SYS@caldbcs3_cdb> alter session set global_names=FALSE;                                             

Session altered.

SYS@caldbcs3_cdb> SELECT * FROM DUAL@ORCLWIN_PDB1_LINK;

D
-
X

SYS@calwindows1_cdb>

Now, in the target DB we can execute the CLONE PDB:

SYS@caldbcs3_cdb> SHOW PDBS;

CON_ID CON_NAME                  OPEN MODE  RESTRICTED
------ ------------------------- ---------- ----------
     2 PDB$SEED                  READ ONLY  NO
     4 CALDBCS1_PDB1             READ WRITE NO

SYS@caldbcs3_cdb> CREATE PLUGGABLE DATABASE CALDBCS1_PDB2 FROM PDB1@ORCLWIN_PDB1_LINK;

Pluggable database created.

SYS@caldbcs3_cdb> SHOW PDBS;

CON_ID CON_NAME                  OPEN MODE  RESTRICTED
------ ------------------------- ---------- ----------
     2 PDB$SEED                  READ ONLY  NO
     3 CALDBCS1_PDB2             MOUNTED
     4 CALDBCS1_PDB1             READ WRITE NO

SYS@caldbcs3_cdb>

After some time (the time required to copy the datafiles) we will have the new cloned PDB from Windows into our OCI Linux CDB Database.

The PDB will appear as MOUNTED. If we open it READ WRITE we can find some issues (PDB is open as RESTRICTED) probably related to the different version / patch level between Source DB and Target DB:

SYS@caldbcs3_cdb> ALTER PLUGGABLE DATABASE CALDBCS1_PDB2 OPEN;

Warning: PDB altered with errors.

SYS@caldbcs3_cdb> SHOW PDBS;

CON_ID CON_NAME                  OPEN MODE  RESTRICTED
------ ------------------------- ---------- ----------
     2 PDB$SEED                  READ ONLY  NO
     3 CALDBCS1_PDB2             READ WRITE YES
     4 CALDBCS1_PDB1             READ WRITE NO

SYS@caldbcs3_cdb>

To fix these issues we check PDB_PLUG_IN_VIOLATIONS for instructions with:

SELECT status, type, cause, message FROM pdb_plug_in_violations;

And:

SELECT status, type, cause, action FROM pdb_plug_in_violations;

These queries will show some violations as “ERROR” and will instruct us about running datapatch to fix them:

[oracle@caldbcs3 ~]$ cd $ORACLE_HOME/OPatch
[oracle@caldbcs3 OPatch]$ ./datapatch -verbose
SQL Patching tool version 19.18.0.0.0 Production on Tue Jun 13 09:57:54 2023
Copyright (c) 2012, 2023, Oracle.  All rights reserved.

Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_98301_2023_06_13_09_57_54/sqlpatch_invocation.log

Connecting to database...OK
Gathering database info...done


. . . <snipped>


Automatic recompilation incomplete; run utlrp.sql to revalidate.
  PDBs: CALDBCS1_PDB2

SQL Patching tool complete on Tue Jun 13 10:04:46 2023
[oracle@caldbcs3 OPatch]$

We then run utlrp.sql in the cloned PDB to recompile all objects:

SYS@caldbcs3_cdb> show con_name

CON_NAME
------------------------------
CALDBCS1_PDB2
SYS@caldbcs3_cdb> @$ORACLE_HOME/rdbms/admin/utlrp.sql

Session altered.


TIMESTAMP
------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN              2023-06-13 10:07:19

DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.


. . . <snipped>


ERRORS DURING RECOMPILATION
---------------------------
                          0


Function created.


PL/SQL procedure successfully completed.


Function dropped.


PL/SQL procedure successfully completed.

SYS@caldbcs3_cdb>

Now we can CLOSE & OPEN the cloned PDB:

SYS@caldbcs3_cdb> ALTER SESSION SET CONTAINER=CDB$ROOT;

Session altered.

SYS@caldbcs3_cdb> ALTER PLUGGABLE DATABASE CALDBCS1_PDB2 CLOSE IMMEDIATE;

Pluggable database altered.

SYS@caldbcs3_cdb> ALTER PLUGGABLE DATABASE CALDBCS1_PDB2 OPEN;

Pluggable database altered.

SYS@caldbcs3_cdb> SHOW PDBS;

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

SYS@caldbcs3_cdb>

At this point we will have migrated the PDB with all its data from the Windows DB to OCI.

Note: This method can also be used to clone a Non-CDB (standalone) Database as a PDB in a CDB Database Target. In this case the script noncdb_to_pdb.sql must be executed prior to OPENing the Target cloned PDB. noncdb_to_pdb.sql is run with the cloned PDB in MOUNTED state. There is a detailed description of the process in this blog article: Cloning Oracle Databases from Non-CDB to PDB.

Getting close to Online Migration: Using REFRESHABLE PDBs.

The REMOTE HOT CLONE process explained above is somewhat similar to a ZDM / DMS Offline migration: the transactional activities that occurred in the Source DB during the cloning process are not transferred to the Target DB (like in a Data Pump export / import).
We can get close to the “Online objective” if we use another Oracle functionality: REFRESHABLE PLUGGABLE Databases (Refreshable PDBs).
Refreshable PDBs allows the creation of PDBs that can be easily refreshed from source PDBs. The Refreshable PDBs act as synchronized copies of the source PDBs.
When a Refreshable PDB is created an initial data copy from the source PDB is performed. Then, the subsequent refresh operations synchronize the data in the Refreshable PDB with the changes that have occurred in the source PDB since the last refresh.
Taking advantage of this feature, we can clone the PDB as REFRESHABLE and then we can REFRESH the cloned PDB to synchronize the transactional operations that may have occurred during the initial data copy (somehow like the switchover phases for ZDM / DMS Online Logical migrations with GoldenGate). Finally, after checking everything is OK (the Refreshable PDB can be OPEN READ ONLY to do the checks) we will remove the “Refreshable status” with the command ALTER PLUGGABLE DATABASE <PDB name> REFRESH MODE NONE. Note that there is no way back from here (we cannot convert the PDB in Refreshable again).

The whole process could be summarized as:
1. Create the REFRESHABLE PDB through a DBLink
2. REFRESH the cloned PDB (apply redo) from Source PDB (this can be done more than once).
3. Stop the activities in the Source PDB and REFRESH for the last time.
4. Convert the REFRESHABLE CLONE PDB into a regular PDB.

With the same prerequisites as in the previous HOT CLONE PDB (ARCHIVELOG mode and archived logs retained, LOCAL UNDO mode, clone user, DBLink …) we can proceed with the creation of the REFRESHABLE PDB:

SYS@caldbcs3_cdb> ALTER SESSION SET CONTAINER=CDB$ROOT;

Session altered.

SYS@caldbcs3_cdb> CREATE PLUGGABLE DATABASE CALDBCS1_PDB3 FROM PDB1@ORCLWIN_PDB1_LINK REFRESH MODE MANUAL;

Pluggable database created.

SYS@caldbcs3_cdb> SHOW PDBS;

CON_ID CON_NAME                  OPEN MODE  RESTRICTED
------ ------------------------- ---------- ----------
     2 PDB$SEED                  READ ONLY  NO
     3 CALDBCS1_PDB2             READ WRITE NO
     4 CALDBCS1_PDB1             READ WRITE NO
     5 CALDBCS1_PDB3             MOUNTED

SYS@caldbcs3_cdb> ALTER PLUGGABLE DATABASE CALDBCS1_PDB3 OPEN READ ONLY;

Warning: PDB altered with errors.

SYS@caldbcs3_cdb>  SHOW PDBS;

CON_ID CON_NAME                  OPEN MODE  RESTRICTED
------ ------------------------- ---------- ----------
     2 PDB$SEED                  READ ONLY  NO
     3 CALDBCS1_PDB2             READ WRITE NO
     4 CALDBCS1_PDB1             READ WRITE NO
     5 CALDBCS1_PDB3             READ ONLY  YES

SYS@caldbcs3_cdb> SELECT status, refresh_mode FROM dba_pdbs WHERE pdb_name = 'CALDBCS1_PDB3';

STATUS     REFRESH_MODE
---------- ------------
REFRESHING MANUAL

SYS@caldbcs3_cdb>

Now we have a new cloned PDB which is also REFRESHABLE. The same issues related to the different version / patch level between Source DB and Target DB in the previous section appear again (PDB is open as RESTRICTED).

But this time we cannot run datapatch now, because datapatch needs the PDB to be OPEN READ WRITE (or at least OPEN UPGRADE). Nonetheless, we can open the PDB as READ ONLY to check the data that have been transferred.

For example:

SYS@caldbcs3_cdb> ALTER SESSION SET CONTAINER=CALDBCS1_PDB3;

Session altered.

SYS@caldbcs3_cdb> SELECT * FROM CARLOS.PRUEBA_01;

ID_N C_TXT                     TS_TIMESTAMP
---- ------------------------- -------------------
   1 UNO                       2023-06-13 09:43:26
   2 DOS                       2023-06-13 09:43:26
   3 TRES                      2023-06-13 09:43:26
   4 CUATRO                    2023-06-13 09:45:01
   5 CINCO                     2023-06-13 09:45:01

SYS@caldbcs3_cdb>

The patch level differences shouldn’t stop the REFRESH process (that is: apply redo).

So, if we insert some rows in this test table in the Windows PDB and do a MANUAL REFRESH:

SYS@caldbcs3_cdb> ALTER PLUGGABLE DATABASE CALDBCS1_PDB3 CLOSE IMMEDIATE;

Pluggable database altered.

SYS@caldbcs3_cdb> ALTER PLUGGABLE DATABASE CALDBCS1_PDB3 REFRESH;

Pluggable database altered.

SYS@caldbcs3_cdb> ALTER PLUGGABLE DATABASE CALDBCS1_PDB3 OPEN READ ONLY;

Warning: PDB altered with errors.

SYS@caldbcs3_cdb> SELECT * FROM CARLOS.PRUEBA_01;

ID_N C_TXT                     TS_TIMESTAMP
---- ------------------------- -------------------
   1 UNO                       2023-06-13 09:43:26
   2 DOS                       2023-06-13 09:43:26
   3 TRES                      2023-06-13 09:43:26
   4 CUATRO                    2023-06-13 09:45:01
   5 CINCO                     2023-06-13 09:45:01
   6 SEIS                      2023-06-13 11:06:44
   7 SIETE                     2023-06-13 11:06:44
   8 OCHO                      2023-06-13 11:06:44
   9 NUEVE                     2023-06-13 11:06:44
  10 DIEZ                      2023-06-13 11:06:44

10 rows selected.

SYS@caldbcs3_cdb>

We can see that the modifications in the Windows Source DB are propagated to the Linux REFRESHABLE PDB. The redo propagation (foreign archive logs are used to roll forward the clone) will typically take much less time than the initial data load, and the activity in the Windows Source DB does not need to be interrupted.

This REFRESH / CHECK method can be executed more than one time, allowing to rise -if any- issues in the process and to assure the migration works OK.

Once the tests have been OK, we can do the switchover. This switchover will need a stop in the Windows Source DB activities because we need to disconnect the REFRESHABLE PDB in the Target (ALTER PLUGGABLE DATABASE … REFRESH MODE NONE) from the Source PDB, OPEN it READ WRITE and run the datapatch. If we don’t stop the activities in the Source DB (E. g.: put it in READ ONLY mode), then all these transactions will not be propagated and will be lost.

This switchover can be compared to the phases ZDM_SWITCHOVER_APP / ODMS_SWITCHOVER in ZDM / DMS LOGICAL ONLINE migrations: somewhere along the process we’ll have to stop the transactions in the Source DB, synchronize Source DB with Target DB (apply redo) and point the applications to the migrated DB.

This way, after stopping the transactional activities in Windows Source PDB and executing the final REFRESH in Target PDB we change it to normal, non-refreshable PDB with REFRESH MODE NONE:

SYS@caldbcs3_cdb> ALTER PLUGGABLE DATABASE CALDBCS1_PDB3 REFRESH MODE NONE;

Pluggable database altered.

SYS@caldbcs3_cdb> ALTER PLUGGABLE DATABASE CALDBCS1_PDB3 OPEN;

Warning: PDB altered with errors.

SYS@caldbcs3_cdb> SHOW PDBS;

CON_ID CON_NAME                  OPEN MODE  RESTRICTED
------ ------------------------- ---------- ----------
     2 PDB$SEED                  READ ONLY  NO
     4 CALDBCS1_PDB1             READ WRITE NO
     3 CALDBCS1_PDB2             READ WRITE NO
     5 CALDBCS1_PDB3             READ WRITE YES

SYS@caldbcs3_cdb>

Now we can go on with the datapatch execution:

[oracle@caldbcs3 OPatch]$ ./datapatch -verbose
SQL Patching tool version 19.18.0.0.0 Production on Tue Jun 13 11:33:01 2023
Copyright (c) 2012, 2023, Oracle.  All rights reserved.

Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_82948_2023_06_13_11_33_01/sqlpatch_invocation.log

Connecting to database...OK
Gathering database info...done

. . . <snipped>

Installing patches...
Patch installation complete.  Total patches installed: 2

Validating logfiles...done
Patch 34765931 apply (pdb CALDBCS1_PDB3): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/34765931/25098466/34765931_apply_CALDBCS1_CALDBCS1_PDB3_2023Jun13_11_33_44.log (no errors)
Patch 34786990 apply (pdb CALDBCS1_PDB3): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/34786990/25032666/34786990_apply_CALDBCS1_CALDBCS1_PDB3_2023Jun13_11_33_33.log (no errors)

Automatic recompilation incomplete; run utlrp.sql to revalidate.
  PDBs: CALDBCS1_PDB3

SQL Patching tool complete on Tue Jun 13 11:39:19 2023
[oracle@caldbcs3 OPatch]$

Then run utlrp.sql:

[oracle@caldbcs3 OPatch]$ ./datapatch -verbose
SYS@caldbcs3_cdb> @$ORACLE_HOME/rdbms/admin/utlrp.sql

Session altered.


. . . <snipped>


ERRORS DURING RECOMPILATION
---------------------------
                          0

Function created.


PL/SQL procedure successfully completed.


Function dropped.


PL/SQL procedure successfully completed.

SYS@caldbcs3_cdb>

Finally, we can CLOSE and OPEN READ WRITE the former REFRESHABLE PDB and we’ll have a migrated PDB with exactly the same information as the Windows Source PDB one, and ready to go and ready to accept all the activity from the client applications (Note that “RESTRICTED” has changed to “NO”):

SYS@caldbcs3_cdb> ALTER SESSION SET CONTAINER=CDB$ROOT;

Session altered.

SYS@caldbcs3_cdb> ALTER PLUGGABLE DATABASE CALDBCS1_PDB2 CLOSE IMMEDIATE;

Pluggable database altered.

SYS@caldbcs3_cdb> ALTER PLUGGABLE DATABASE CALDBCS1_PDB2 OPEN;

Pluggable database altered.

SYS@caldbcs3_cdb> SHOW PDBS;

CON_ID CON_NAME                  OPEN MODE  RESTRICTED
------ ------------------------- ---------- ----------
     2 PDB$SEED                  READ ONLY  NO
     4 CALDBCS1_PDB1             READ WRITE NO
     3 CALDBCS1_PDB2             READ WRITE NO
     5 CALDBCS1_PDB3             READ WRITE NO

SYS@caldbcs3_cdb>

Note: This method can also be used to clone a Non-CDB (standalone) Database as a PDB in a CDB Database Target. In this case the script noncdb_to_pdb.sql must be executed prior to OPENing the Target cloned PDB. noncdb_to_pdb.sql is run with the cloned PDB in MOUNTED state.

Conclusion

Here we have explored two methods for doing a migration from Windows DB sources to Linux OCI DB targets as an alternative to using ZDM / DMS, which do not support this kind of sources.

We have taken advantage of the fact that the prerequisite for doing a CLONE/REFRESH is that Source DB system and Target DB system must be the same endianness, not the same OS. Windows and Linux are both little endian.

Other things to keep in mind.

There may be an impact in the Source DB during the migration using these methods, since they use system resources (CPU, I/O, etc…).

There may be an impact in network: copying the data from Source DB to Target DB will take network resources and may affect the overall performance.

The time needed for the process can be significant and depends on the size of the database and the network (bandwidth, throughput, latency…).

Just for the sake of simplicity, we have left out some aspects (like Transparent Data Encryption and PARALLELism) that must be kept into account in a real-world migration:

• If TDE is enabled in Source DB then the clause KEYSTORE IDENTIFIED BY <target_wallet_pwd> must be added to the CREATE PLUGGABLE DATABASE … FROM …
• The PARALLEL clause of the CREATE PLUGGABLE DATABASE statement can be used to control the resource usage of the copying of data files. The default is to use as much parallel as deemed suitable on the target database. This might put an additional load on the Source DB as noted above. To minimise the impact of the data file copy, the parallel degree can be lowered with CREATE PLUGGABLE DATABASE … PARALLEL n.

Saludos.

Carlos.

Note to self…

Note to self: Never, ever, ever use btrfs filesystem again. Not even on SuSE.

Saludos.

Carlos.

Hasta luego, Teradata. Hola… ¡Oracle!

Hace mucho, mucho tiempo, publicaba esto:

Casi trece años han pasado y ahora, después de que Teradata decidiera que no quería seguir contando con mis servicios, recorro el camino inverso y comienzo a trabajar en Oracle.

Así que, lleno de ilusión, comienzo una nueva etapa (¿o retomo una vieja? no sé…) llena de cosas que aprender (¿o reaprender?) y con el «Cloud» como horizonte.

Feels like coming home…

Saludos.

Carlos.

Windows 10 is still Windows… as always.

En uno de mis ordenadores con Windows 10 el audio ha dejado de funcionar después de una de esas actualizaciones que se instalan «porque sí». Sin ningún motivo aparente, sin ninguna explicación.

Un día arrancas el sistema y te das cuenta de que no suena. Y ves que el altavoz de la barra de tareas aparece con una X roja. Al pasar el ratón por encima aparece el mensaje «No Audio Output Device is enabled.»

A partir de ahí intentas todo tipo de soluciones. Desinstalar/instalar el «device», actualizar el controlador, cambiar el controlador, usar el «troubleshooter», editar el registry(!!)… Nada. Pero nada de nada.

El «device manager» dice que todo está OK: «This device is working properly.» Pero no. Ni «properly» ni mucho menos «working». Ni un ruido. Muerto.

Buceando en la web me encuentro un artículo que habla de actualizaciones corruptas o incompletas. Así que me voy al «Windows Update» y me doy cuenta que ¡tampoco funciona! Las actualizaciones fallan con error 0x800f0900 una y otra vez:

También pruebo de todo según los artículos de la base de conocimiento de Microsoft, pero lo mismo: nada de nada.

Así que me estoy replanteando una reinstalación desde cero, con todo lo que ello supone… 😡😡😡

Y luego me llaman «raro» y «friki» porque uso Linux.

Windows 10 is still Windows…

Saludos.

Carlos.

Teradata 17.00

Actualmente estoy probando Teradata 17.00 en SLES 12 SP3. (Es un pre-release).

 

 

Saludos.

Carlos.

Larry Higa’s Ratio, Product Join Indicator and Unnecessary I/O Indicator.

En Teradata (y en la mayoría de los RDBMS), el consumo de CPU y las actividades de I/O son métricas básicas cuando se trata de analizar el rendimiento de un sistema.
En todos los sistemas Teradata hay tareas que tienden a ser «CPU-bound» y otras tienden a ser «I/O-bound«. Es una buena idea determinar qué tareas encajan en cada categoría.
Las métricas de CPU e I/O se utilizan para hacerse una idea acerca de la eficiencia de las «queries» a la hora de consumir recursos de CPU e I/O. Estas métricas se pueden calcular fácilmente a partir de las tablas y vistas DBQL / PDCR utilizando las columnas AMPCpuTime y TotalIOCount.

Larry Higa’s Ratio (LHR).

Larry Higa era un especialista en temas de rendimiento de Teradata que encontró muy útil relacionar las métricas de CPU e I/O de una manera simple.
El Ratio de Larry Higa (Larry Higa’s Ratio – LHR) es un índice empírico que muestra la tasa de CPU frente a I/O.
La experiencia muestra que la mayoría de las solicitudes de SQL consumen alrededor de 1 segundo de CPU por cada 1000 operaciones de I/O. La desviación de esta constante indica predominio de CPU o I/O, lo que significa un consumo de recursos desequilibrado.

Product Join Indicator (PJI).

El «Product Join Indicator» es la aplicación del principio LHR desde el punto de vista del consumo de CPU:

PJI = CPU Seconds * 1000 dividido por I/O.

Esto se traduce a las columnas DBQLogTbl como: PJI = (1000 * AmpCPUTime) / NullIfZero (TotalIOCount)

Un PJI relativamente alto para una consulta significa que la consulta está usando mucho CPU para las operaciones de I/O dadas.
El valor umbral para considerar PJI «alto» es algo difuso. Algunos dicen 3, algunos dicen 6, algunos dicen 10 … Pero en general, cuanto mayor es PJI menor es la eficacia en el uso de los recursos de CPU (se consume demasiada CPU).

No obstante, podemos encontrar «falsos positivos» en los PJI.
Las «queries» pueden arrojar falsos positivos PJI (alto consumo de CPU por I/O) cuando hay en ellas:
· Agregaciones grandes o muchos grupos de agregación (GROUP BY).
· Numerosos SUBSTRINGS.
· Cláusulas CASE.
· Uso de INDEX / POSITION en cadenas de caracteres.
· Operaciones aritmeticas.
· Comprobaciones de filas duplicadas (INSERTAR filas con el mismo «rowhash» en tablas SET con NUPI).
· Tareas de compresión / descompresión de bloque de datos para actividades de compresión a nivel de bloque (BLC – «block level compression»).

Unnecessary I/O Indicator (UII).

El «Unnecessary I/O Indicator» es la aplicación del principio LHR desde el punto de vista de I/O:

UII (Unnecessary I/O Indicator) = I/O dividida por (CPU Seconds * 1000).

Esto se traduce a las columnas DBQLogTbl como: UII = TotalIOCount / (1000 * NullIfZero (AMPCpuTime))

Cuando una «query» presenta un UII alto generalmente significa que se leen muchos bloques de I/O, pero en realidad se procesa un número relativamente pequeño de filas. Un ejemplo podría ser un «full table scan» que devuelva un resultado de unas pocas filas. En este caso el uso de un índice (secondary, join index…) podría reducir el consumo de I/O. igual que con el PJI, el valor umbral para considerar un UII «alto» también es difuso pero, en general, cuanto mayor es el UII, mayor es la sobrecarga de I/O provocada por una «query».

Algunas acciones pueden ayudar a reducir el UII:
· Agregar índices secundarios, join indexes, hash indexes, sparse indexes.
· Recopilación / actualización de estadísticas en todas las columnas de «JOIN» y de selección (WHERE).
· Elegir los tipos de datos (y los juegos de caracteres) correctos para limitar el almacenamiento necesario para las columnas y filas.
· Usar MVC («multi value compression») para reducir el tamaño de fila y obtener más filas por bloque.
· Usar PPI (y column-PPI).
· Aumentar el tamaño de los «datablocks».
· Usar el modelo de datos en tercera forma normal (3NF) para obtener filas más pequeñas, más filas por bloque, menos I/O y desnormalizar después si es necesario.
· Aumentar la memoria del nodo, para ampliar el tamaño de la caché FSG.
· BLC también puede ayudar a reducir I/O y aumentar la utilización de FSG.

Saludos.

Carlos.

Teradata available for Google Cloud

La oferta de Teradata «on cloud»  se amplía. Hace ya tiempo que Teradata Vantage está disponible para AWS y  Microsoft Azure, y ahora lo va a estar también en Google Cloud.

Más información en la nota de prensa.

Saludos.

Carlos.

Teradata New Developer Site

Teradata tiene un nuevo «Developer Site».

Se accede por aquí: https://www.teradata.com/Developer o por aquí: https://developer.teradata.com/

… ¡y en «dark mode»!

Saludos.

Carlos.

 

Hewlett Packard compra MapR

Hewlett Packard acaba de comprar MapR.

Después de la fusión de Cloudera con HortonWorks parece que el mundo del Hadoop tal y como fue pensado (open-source, commodity hardware, community mantained…) ha tenido mejores épocas, si no es que ha pasado a mejor vida.

Saludos.

Carlos.