Oracle Database 19c Blockchain Tables

First, to explore blockchain table in 19c release, you will need to apply the latest patches and change compatibility parameter value (which is unusual), the blockchain feature was introduced initially in 21c and back-ported to 19c with 19.10 RU (release update) in January 2021 CPU. Here I am going to explore it after patching the database to 19.11 (April 2021 CPU).

To raise the compatibility parameter:

I have shutdown the database

SQL> shutdown immediate;

Then, edited the init.ora file and changed compatibility parameter value:

compatible=’19.11.0′

Then, I started up the database:

SQL> startup pfile=’D:\WINDOWS.X64_193000_db_home\dbs\init.ora’

Then, checking the compatibility parameter value:

SQL> SELECT name, value, description FROM v$parameter WHERE name = ‘compatible’;

Let us explore it:

In pluggable database PDB1

CREATE TABLESPACE ledgertbs;

CREATE USER auditor identified by audit_123 DEFAULT TABLESPACE ledgertbs;

GRANT create session, create table, unlimited tablespace TO auditor;

GRANT execute ON sys.dbms_blockchain_table TO auditor;

Connecting to PDB1 database as user “auditor”:

I will create a blockchain table named AUDITOR.LEDGER_EMP that will maintain a tamper-resistant ledger of current and historical transactions in PDB1. Rows can never be deleted in the AUDITOR.LEDGER_EMP blockchain table. The blockchain table can be dropped only after 3 days of inactivity.

SQL> CREATE BLOCKCHAIN TABLE ledger_emp (employee_id NUMBER, employee_name varchar2(15), salary NUMBER)

                     NO DROP UNTIL 3 DAYS IDLE

                     NO DELETE LOCKED

                     HASHING USING “SHA2_512” VERSION “v1”;

Checking user_blockchain_tables to list blockchain table information:

SQL> SELECT *  FROM   user_blockchain_tables ;

I will insert 1 record:

SQL> INSERT INTO ledger_emp VALUES (106,’EMAD’,3000);

SQL> COMMIT;

If I attempt to drop the table:

SQL> drop table ledger_emp;

ORA-05723: drop blockchain or immutable table LEDGER_EMP not allowed

If I attempt to delete all records from the table:

SQL> delete from auditor.ledger_emp;

ORA-05715: operation not allowed on the blockchain or immutable table

To inspect the row hash value:

SELECT ORABCTAB_CHAIN_ID$ “Chain ID”, ORABCTAB_SEQ_NUM$ “Seq Num”,

               to_char(ORABCTAB_CREATION_TIME$,’dd-Mon-YYYY hh-mi’) “Chain date”,

               ORABCTAB_USER_NUMBER$ “User Num”, ORABCTAB_HASH$ “Chain HASH”,

               employee_id, salary

        FROM   ledger_emp;

There is a parameter called BLOCKCHAIN_TABLE_MAX_NO_DROP

basically this parameter controls the amount of idle time the table must be inactive before

it can be dropped, this parameter is useful if you want to protect from anyone by mistake setting the nodrop option to very high value.

the default value is NONE, based on documentation:

https://docs.oracle.com/en/database/oracle/oracle-database/21/refrn/BLOCKCHAIN_TABLE_MAX_NO_DROP.html#GUID-26AF15B2-5621-4602-AA6E-D92842E4285C

from PDB level:

sqlplus / as sysdba

SQL> alter session set container=PDB1;

SQL> alter system set blockchain_table_max_no_drop=0;

alter system set blockchain_table_max_no_drop=0

*

ERROR at line 1:

ORA-01031: insufficient privileges

from CDB level:

sqlplus / as sysdba

SQL> alter system set blockchain_table_max_no_drop=0;

System altered.

Even though, its stated in the documentation that you can modify it from PDB level ?! (is this a documentation bug)

select

    name,type,value,ISPDB_MODIFIABLE,ISINSTANCE_MODIFIABLE,ISMODIFIED,ISSYS_MODIFIABLE,DESCRIPTION

from

    v$parameter

where

name=’blockchain_table_max_no_drop’;

Now, after setting the parameter to 0 if you attempt to create the block-chain table with number of days more than zero:

SQL> CREATE BLOCKCHAIN TABLE auditor.ledger_emp_n (employee_id NUMBER, employee_name varchar2(15), salary NUMBER)

                         NO DROP UNTIL 4 DAYS IDLE

                         NO DELETE LOCKED

                         HASHING USING “SHA2_512” VERSION “v1”;

CREATE BLOCKCHAIN TABLE auditor.ledger_emp_n (employee_id NUMBER, employee_name varchar2(15), salary NUMBER)

*

ERROR at line 1:

ORA-05747: maximum retention time too high, should be less than or equal to 0

INS-08102 DBCA error

ERROR

———————–

[WARNING] [INS-08102] Unexpected error occurred while transitioning from state ‘PrimaryDatabase’

When running dbca with -createDuplicateDB, dbca is failed by INS-8102.

Solution:

I have dropped the tablespace that is configured for lost write protection (shadow tablespace)….to find the tablespace name:

SQL> select tablespace_name from dba_tablespaces where CONTENTS=’LOST WRITE PROTECTION’;

SQL> drop tablespace XXXX including contents and data files;

To find more information about lost write protection you can refer to my article: Oracle Database Lost Write Detection & Recovery Options – Geodata Master

Oracle Spatial Error ORA-29903: error in executing ODCIIndexFetch() routine

After upgrading an Oracle database from 12cR1 to 19c release, this database using Oracle Spatial component. The developers noticed many of their spatial queries are failing and the following error is thrown:

ORA-29903: error in executing ODCIIndexFetch() routine

What is the cause of this issue ?

check the value of this parameter ” SPATIAL_VECTOR_ACCELERATION ” in your database

SQL> show parameter SPATIAL_VECTOR_ACCELERATION

if its set to “FALSE” , then enable it to “TRUE”

SQL> ALTER SYSTEM SET SPATIAL_VECTOR_ACCELERATION = TRUE;

And this will fix the problem. I was under the impression that ” SPATIAL_VECTOR_ACCELERATION” parameter is “optional” parameter that will improve your queries against GIS data, but it seems starting with Oracle 12cR2 and beyond this parameter should be set to TRUE !

Oracle 21c in Oracle Cloud ATP – Default Tablespace Encryption Algorithm

Tablespace encryption is very important database security feature where data at rest is encrypted for protection against any attempt of physical access to copy data file levels. Of course Oracle cloud provides security protection and encrypts tablespaces. In Oracle 21c a new security parameter feature called “ Default Tablespace Encryption Algorithm” this parameter will set the default/required encryption algorithm for newly created tablespaces…..let us briefly explore it:

The following query will list encrypted tablespaces:

SQL> select * from v$encrypted_tablespaces;

The following is the new parameter and its default value

SQL> SHOW PARAMETER TABLESPACE_ENCRYPTION_DEFAULT_ALGORITHM

its worth stating that in Oracle ATP you can’t execute create tablespace sql command: https://docs.oracle.com/en/cloud/paas/autonomous-database/adbsa/appendix-restrictions-sql.html#GUID-791E7112-07F7-46F0-BD81-777C8FAD83A0

Oracle datapatch error PLS-00201: identifier ‘DBMS_LOB’

The following errors were generated in the “datapatch” part of applying January CPU 2021 on Oracle 19c database:

Validating logfiles…done

Patch 32218454 apply: WITH ERRORS

logfile: /orcl/db22/cfgtoollogs/sqlpatch/32218454/24018797/32218454_apply_db22_2021Feb12_01_13_32.log (errors)

-> Error at line 28715: script rdbms/admin/prvtgwmpl.plb

  - Warning: Package Body created with compilation errors.

-> Error at line 28722: script rdbms/admin/prvtgwmpl.plb

  - 3422/8   PL/SQL: Statement ignored

-> Error at line 28723: script rdbms/admin/prvtgwmpl.plb

  - 3422/50  PLS-00201: identifier 'DBMS_RANDOM' must be declared

-> Error at line 28724: script rdbms/admin/prvtgwmpl.plb

  - 5631/9   PL/SQL: Statement ignored

-> Error at line 28725: script rdbms/admin/prvtgwmpl.plb

  - 5631/9   PLS-00201: identifier 'DBMS_LOB' must be declared

-> Error at line 28726: script rdbms/admin/prvtgwmpl.plb

  - 5688/9   PL/SQL: Statement ignored

-> Error at line 28727: script rdbms/admin/prvtgwmpl.plb

  - 5688/9   PLS-00201: identifier 'DBMS_LOB' must be declared

-> Error at line 28728: script rdbms/admin/prvtgwmpl.plb

  - 6477/4   PL/SQL: Statement ignored

-> Error at line 28732: script rdbms/admin/prvtgwmpl.plb

  - 6477/4   PLS-00201: identifier 'DBMS_LOB' must be declared

-> Error at line 28733: script rdbms/admin/prvtgwmpl.plb

  - 6534/4   PL/SQL: Statement ignored

-> Error at line 28734: script rdbms/admin/prvtgwmpl.plb

  - 6534/4   PLS-00201: identifier 'DBMS_LOB' must be declared

-> Error at line 28735: script rdbms/admin/prvtgwmpl.plb

  - 6625/6   PL/SQL: Statement ignored

-> Error at line 28736: script rdbms/admin/prvtgwmpl.plb

  - 6625/17  PLS-00201: identifier 'DBMS_LOB' must be declared

-> Error at line 28737: script rdbms/admin/prvtgwmpl.plb

  - 6895/14  PL/SQL: Statement ignored

-> Error at line 28738: script rdbms/admin/prvtgwmpl.plb

  - 6895/14  PLS-00201: identifier 'DBMS_LOB' must be declared

-> Error at line 28742: script rdbms/admin/prvtgwmpl.plb

  - 6930/14  PL/SQL: Statement ignored

-> Error at line 28743: script rdbms/admin/prvtgwmpl.plb

  - 6930/14  PLS-00201: identifier 'DBMS_LOB' must be declared

-> Error at line 28744: script rdbms/admin/prvtgwmpl.plb

  - 6932/14  PL/SQL: Statement ignored

-> Error at line 28745: script rdbms/admin/prvtgwmpl.plb

  - 6932/14  PLS-00201: identifier 'DBMS_LOB' must be declared

-> Error at line 28746: script rdbms/admin/prvtgwmpl.plb

  - 7048/6   PL/SQL: Statement ignored

-> Error at line 28747: script rdbms/admin/prvtgwmpl.plb

  - 7048/6   PLS-00201: identifier 'DBMS_LOB' must be declared

-> Error at line 28769: script rdbms/admin/prvtgwmpl.plb

  - 3422/8   PL/SQL: Statement ignored

-> Error at line 28770: script rdbms/admin/prvtgwmpl.plb

  - 3422/50  PLS-00201: identifier 'DBMS_RANDOM' must be declared

-> Error at line 28771: script rdbms/admin/prvtgwmpl.plb

  - 5631/9   PL/SQL: Statement ignored

-> Error at line 28772: script rdbms/admin/prvtgwmpl.plb

  - 5631/9   PLS-00201: identifier 'DBMS_LOB' must be declared

-> Error at line 28773: script rdbms/admin/prvtgwmpl.plb

  - 5688/9   PL/SQL: Statement ignored

-> Error at line 28774: script rdbms/admin/prvtgwmpl.plb

  - 5688/9   PLS-00201: identifier 'DBMS_LOB' must be declared

-> Error at line 28775: script rdbms/admin/prvtgwmpl.plb

  - 6477/4   PL/SQL: Statement ignored

-> Error at line 28779: script rdbms/admin/prvtgwmpl.plb

  - 6477/4   PLS-00201: identifier 'DBMS_LOB' must be declared

-> Error at line 28780: script rdbms/admin/prvtgwmpl.plb

  - 6534/4   PL/SQL: Statement ignored

-> Error at line 28781: script rdbms/admin/prvtgwmpl.plb

  - 6534/4   PLS-00201: identifier 'DBMS_LOB' must be declared

-> Error at line 28782: script rdbms/admin/prvtgwmpl.plb

  - 6625/6   PL/SQL: Statement ignored

-> Error at line 28783: script rdbms/admin/prvtgwmpl.plb

  - 6625/17  PLS-00201: identifier 'DBMS_LOB' must be declared

-> Error at line 28784: script rdbms/admin/prvtgwmpl.plb

  - 6895/14  PL/SQL: Statement ignored

-> Error at line 28785: script rdbms/admin/prvtgwmpl.plb

  - 6895/14  PLS-00201: identifier 'DBMS_LOB' must be declared

-> Error at line 28789: script rdbms/admin/prvtgwmpl.plb

  - 6930/14  PL/SQL: Statement ignored

-> Error at line 28790: script rdbms/admin/prvtgwmpl.plb

  - 6930/14  PLS-00201: identifier 'DBMS_LOB' must be declared

-> Error at line 28791: script rdbms/admin/prvtgwmpl.plb

  - 6932/14  PL/SQL: Statement ignored

-> Error at line 28792: script rdbms/admin/prvtgwmpl.plb

  - 6932/14  PLS-00201: identifier 'DBMS_LOB' must be declared

-> Error at line 28793: script rdbms/admin/prvtgwmpl.plb

  - 7048/6   PL/SQL: Statement ignored

-> Error at line 28794: script rdbms/admin/prvtgwmpl.plb

  - 7048/6   PLS-00201: identifier 'DBMS_LOB' must be declared

Please refer to MOS Note 1609718.1 and/or the invocation log

/orcl/db22/cfgtoollogs/sqlpatch/sqlpatch_76633_2021_02_12_01_13_10/sqlpatch_invocation.log

for information on how to resolve the above errors.

To fix this problem:

the above errors generated because multiple built-in oracle pacakges are revoked from “PUBLIC” as best security practice
if you query dba_registry_sqlpatch you will find that the patch is applied with ERRORS ….to overcome this and esnure success
patches being applied…grant these packages temporarly to public:

sqlplus / as sysdba

SQL> grant execute on DBMS_RANDOM to PUBLIC;

SQL> grant execute on UTL_FILE to PUBLIC;

SQL> grant execute on UTL_HTTP to PUBLIC;

SQL> grant execute on dbms_lob to PUBLIC;

SQL> grant execute on dbms_sql to PUBLIC;

SQL> grant execute on dbms_job to PUBLIC;

SQL> exit;

then, re-run datapatch again:

cd $ORACLE_HOME/OPatch

./datapatch -verbose

after that revoke the permissions:

SQL> revoke execute on DBMS_RANDOM from PUBLIC;

SQL> revoke execute on UTL_FILE from PUBLIC;

SQL> revoke execute on UTL_HTTP from PUBLIC;

SQL> revoke execute on dbms_lob from PUBLIC;

SQL> revoke execute on dbms_sql from PUBLIC;

SQL> revoke execute on dbms_job from PUBLIC;

then perform re-compilation:

SQL> ?@/rdbms/admin/utlrp.sql

SQL> select comp_name,status from dba_registry;

Oracle 19c upgrade error ORA-40362: invalid object SYS.ODCIPARTINFOLIST specified in the statement

As its known by now Oracle 19c database release is the long-term release for Oracle database system and by now your Infrastructure should be based on it.

One of the database systems while upgrading it from Oracle 12cR1 to 19c, the below errors generated (this was the first upgrade failure I have ever faced since my journey with 19c upgrade for the last 1 year).

Serial   Phase #:105  [orcl] Files:1    Time: 3s

Serial   Phase #:106  [orcl] Files:1    Time: 0s

Serial   Phase #:107  [orcl] Files:1     Time: 34s

——————————————————

Phases [0-107]         End Time:[2021_01_22 11:17:33]

——————————————————

Grand Total Time: 1359s

*** WARNING: ERRORS FOUND DURING UPGRADE ***

1. Evaluate the errors found in the upgrade logs

    and determine the proper action.

2. Rerun the upgrade when the problem is resolved

REASON:

      ERRORS FOUND: During Upgrade

         FILENAME: /orcl/export/upgrade_19c_log//catupgrd0.log AT LINE NUMBER: 802204

——————————————————

Identifier CATPROC 21-01-22 10:59:28

SCRIPT    = [/orcl/ora/product/19.3/rdbms/admin/catodci.sql]

ERROR    = [ORA-40362: invalid object SYS.ODCIPARTINFOLIST specified in the statement]

STATEMENT = [GRANT EXECUTE ON ODCIPartInfoList TO PUBLIC]

——————————————————

——————————————————

Identifier CONTEXT 21-01-22 11:08:54

SCRIPT    = [/orcl/ora/product/19.3/ctx/admin/t1202000.sql]

ERROR    = [ORA-06550: line 4, column 22: PLS-00905: object CTXSYS.TEXTINDEXMETHODS is invalid

ORA-06550: line 4, column 6:

PL/SQL: Statement ignored

]

STATEMENT = [alter indextype context using TextIndexMethods]

——————————————————

——————————————————

Identifier CONTEXT 21-01-22 11:08:54

SCRIPT    = [/orcl/ora/product/19.3/ctx/admin/t1202000.sql]

ERROR    = [PLS-00905: object CTXSYS.TEXTINDEXMETHODS is invalid ORA-06550: line 4, column 6:

PL/SQL: Statement ignored

]

STATEMENT = [as above]

——————————————————

——————————————————

Identifier CONTEXT 21-01-22 11:08:54

SCRIPT    = [/orcl/ora/product/19.3/ctx/admin/t1202000.sql]

ERROR    = [ORA-06550: line 4, column 6: PL/SQL: Statement ignored

]

STATEMENT = [as above]

——————————————————

——————————————————

Identifier CONTEXT 21-01-22 11:08:54

SCRIPT    = [/orcl/ora/product/19.3/ctx/admin/t1202000.sql]

ERROR    = [PL/SQL: Statement ignored]

STATEMENT = [as above]

——————————————————

——————————————————

Identifier CONTEXT 21-01-22 11:08:54

SCRIPT    = [/orcl/ora/product/19.3/ctx/admin/dr0itype.sql]

ERROR    = [ORA-06552: PL/SQL: Declaration ignored ORA-06553: PLS-905: object CTXSYS.CTX_CONTAINS is invalid

ORA-06552: PL/SQL: Declaration ignored

ORA-06553: PLS-905: object CTXSYS.CTX_CONTAINS is invalid

ORA-06552: PL/SQL: Declaration ignored

ORA-06553: PLS-905: object CTXSYS.CTX_CONTAINS is invalid

ORA-06552: PL/SQL: Declaration ignored

ORA-06553: PLS-905: object CTXSYS.CTX_CONTAINS is invalid

ORA-06552: PL/SQL: Declaration ignored

ORA-06553: PLS-905: object CTXSYS.CTX_CONTAINS is invalid

ORA-06552: PL/SQL: Declaration ignored

ORA-06553: PLS-905: object CTXSYS.CTX_CONTAINS is invalid

ORA-06552: PL/SQL: Declaration ignored

ORA-06553: PLS-905: object CTXSYS.CTX_CONTAINS is invalid

ORA-06552: PL/SQL: Declaration ignored

ORA-06553: PLS-905: object CTXSYS.CTX_CONTAINS is invalid

ORA-06552: PL/SQL: Declaration ignored

ORA-06553: PLS-905: object CTXSYS.CTX_CONTAINS is invalid

ORA-06552: PL/SQL: Declaration ignored

ORA-06553: PLS-905: object CTXSYS.CTX_CONTAINS is invalid

ORA-06552: PL/SQL: Declaration ignored

ORA-06553: PLS-905: object CTXSYS.CTX_CONTAINS is invalid

ORA-06552: PL/SQL: Declaration ignored

ORA-06553: PLS-905: object CTXSYS.CTX_CONTAINS is invalid

ORA-06552: PL/SQL: Declaration ignored

ORA-06553: PLS-905: object CTXSYS.CTX_CONTAINS is invalid

ORA-06552: PL/SQL: Declaration ignored

ORA-06553: PLS-905: object CTXSYS.CTX_CONTAINS is invalid

ORA-06552: PL/SQL: Declaration ignored

ORA-06553: PLS-905: object CTXSYS.CTX_CONTAINS is invalid

ORA-06552: PL/SQL: Declaration ignored

ORA-06553: PLS-905: object CTXSYS.CTX_CONTAINS is invalid

]

STATEMENT = [create or replace operator contains binding

  (varchar2, varchar2) return number

     with index context, scan context TextIndexMethods

     compute ancillary data without column data using ctx_contains.Textcontains

The solution of this error is the following:

modify/edit the following file:

vi $ORACLE_HOME/rdbms/admin/catodci.sql

replace this statement: GRANT EXECUTE ON ODCIPartInfoList TO PUBLIC

with

begin

  execute immediate ‘alter type ODCIPartInfo compile’;

  execute immediate ‘alter type ODCIPartInfoList compile’;

  begin

  execute immediate ‘GRANT EXECUTE ON ODCIPartInfo TO PUBLIC’;

  exception

  when others then

  null;

  end;

  begin

  execute immediate ‘GRANT EXECUTE ON ODCIPartInfoList TO PUBLIC’;

  exception

  when others then

  null;

  end;

  begin

  execute immediate ‘GRANT EXECUTE ON ODCIPartInfoList TO PUBLIC’;

  exception

  when others then

  null;

  end;

  begin

  execute immediate ‘GRANT EXECUTE ON ODCIPartInfoList TO PUBLIC’;

  exception

  when others then

  null;

  end;

exception

  when others then

  null;

end;

/

After that, re-run the upgrade command

Oracle PDB and when is DAPTATCH required to be executed

Here I am going to show three scenarios to illustrate when datapatch utility is required to be executed.

In my simulation the source CDB environment version is 19.3 , the destination CDB environment is 19.9 (patched with October 2020 RU).

Scenario 1: moving PDB from a lower version (source CDB) to a higher destination CDB

SQL> ALTER SESSION SET CONTAINER=CDB$ROOT;

SQL> ALTER PLUGGABLE DATABASE PDB2 CLOSE;

SQL> ALTER PLUGGABLE DATABASE PDB2 UNPLUG INTO ‘D:\19c_vm\OracleDatabase\19.3.0\PDB2_UNLPLUG.xml’;

Then I will drop the database from the source CDB:

SQL> DROP PLUGGABLE DATABASE PDB2;

After that copy the datafiles and .XML file to the destination server hosting the destination CDB:

SQL> CREATE PLUGGABLE DATABASE NEW_PDB

  USING ‘/opt/oracle/product/19c/dbhome_1/dbs/PDB2_UNLPLUG.xml’ MOVE

 SOURCE_FILE_NAME_CONVERT=  (‘D:\19C_BASE\ORADATA\ORACLE19C\PDB2\’, ‘/opt/oracle/oradata/ORCLCDB/NEW_PDB/’);

// open the database and check the dictionary:

alter pluggable database NEW_PDB open;

alter session set container=NEW_PDB;

select * from dba_registry_sqlpatch;

There are NO rows resulted from dba_registry_sqlpatch , this means we will need to run datapatch to update the dictionary of the newly plugged database (always download the latest OPatch utility version):

cd $ORACLE_HOME/OPatch

[oracle@oracle-19c]$ ./datapatch -verbose

SQL Patching tool version 19.9.0.0.0 Production on Mon Nov 30 15:40:32 2020

Copyright (c) 2012, 2020, Oracle.  All rights reserved.

Log file for this invocation: /opt/oracle/cfgtoollogs/sqlpatch/sqlpatch_9900_2020_11_30_15_40_32/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 31668882 (OJVM RELEASE UPDATE: 19.9.0.0.201020 (31668882)):

  Binary registry: Installed

  PDB CDB$ROOT: Applied successfully on 06-NOV-20 08.17.57.037468 PM

  PDB NEW_PDB: Not installed

  PDB PDB$SEED: Applied successfully on 06-NOV-20 08.17.58.718557 PM

  PDB PDB1: Applied successfully on 06-NOV-20 08.18.00.702663 PM

  PDB PDB2: Applied successfully on 06-NOV-20 08.18.02.342712 PM

Current state of release update SQL patches:

  Binary registry:

    19.9.0.0.0 Release_Update 200930183249: Installed

  PDB CDB$ROOT:

    Applied 19.9.0.0.0 Release_Update 200930183249 successfully on 06-NOV-20 08.59.54.481954 PM

  PDB NEW_PDB:

    No release update patches installed

After that check dba_registry_sqlpatch view ……the metadata is updated reflecting the patch version and SQL code modifications.

Scenario 2: creating a new pluggable database in a patched CDB

 SQL> CREATE PLUGGABLE DATABASE “PDB33” ADMIN USER “admin” IDENTIFIED BY “admin_123”

  FILE_NAME_CONVERT=( ‘/opt/oracle/oradata/ORCLCDB/’, ‘/opt/oracle/oradata/ORCLCDB/PDB33/’ )

  STORAGE UNLIMITED TEMPFILE REUSE;

So, NO need to run datapatch for any “newly” created PDB in CDB container !

The pluggable database created is based on the template database PDB$SEED

Scenario 3: migrating a PDB to a lower version CDB

ALTER SESSION SET CONTAINER=CDB$ROOT;

ALTER PLUGGABLE DATABASE NEW_PDB CLOSE;

ALTER PLUGGABLE DATABASE NEW_PDB UNPLUG INTO ‘/vagrant/NEW_PDB_UNLPLUG.xml’;

Then I will drop the database from the source CDB:

DROP PLUGGABLE DATABASE NEW_PDB;

// I will plug the database now in the destination CDB

alter session set container=CDB$ROOT;

CREATE PLUGGABLE DATABASE NEW_PDB

  USING ‘D:\19c_base\oradata\ORACLE19C\NEW_PDB\NEW_PDB_UNLPLUG.xml’ MOVE

 SOURCE_FILE_NAME_CONVERT=  (‘/opt/oracle/oradata/ORCLCDB/ORCLCDB/1D59E472CA974170BFC1A471E133E3C9/datafile/’, ‘D:\19c_base\oradata\ORACLE19C\NEW_PDB\’);

After successfully plugging the database….now checking the registry:

alter session set container=NEW_PDB;

select PATCH_ID,DESCRIPTION from dba_registry_sqlpatch;

Dictionary is reflecting that the PDB is patched with 19.9 patches

What will happen if I run “datapatch -verbose” command ?

The patches will be rolled back (as expected) !

The datapatch perl script executes the package DBMS_QOPATCH that compares the patch level between the binaries and the database system metadata.

select action,action_time,description from dba_registry_sqlpatch;

This blog post was inspired from youtube video by Connor McDonald titled “Do I need to run DataPatch” ?

Oracle 19c New Feature: Automatic Database Diagnostic Monitor (ADDM) in Pluggable Database Level

Oracle 19c has introduced new feature (capability) which is generating ADDM report from a PDB-level, this was not possible in previous releases, as ADDM was generated from a CDB (Container Level).

The following are the steps to enable ADDM in a PDB:

SQL> alter session set container=PDB1;

Session altered.

SQL> ALTER SYSTEM SET AWR_PDB_AUTOFLUSH_ENABLED=TRUE;

System altered.

//configure snapshot interval for AWR

SQL> EXEC dbms_workload_repository.modify_snapshot_settings(interval=>60);

PL/SQL procedure successfully completed.

// take manual snapshot

SQL> BEGIN

   DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();

END;

/

// to check snapshsot details for PDB1 database

SQL> select * from DBA_HIST_SNAPSHOT where CON_ID=3;

As HR user I will create a new table and insert dummy data:

sqlplus hr/hr_123@PDB1

CREATE  TABLE DUMMY_SEPT (sales  number(10));

BEGIN

    FOR v_LoopCounter IN 1..3000 LOOP

 insert into DUMMY_SEPT values (‘4000’);   

COMMIT;

    END LOOP;

END;

/

commit;

select *  from DUMMY_SEPT;

// as SYS or SYSTEM account execute the following to take manual snapshot:

SQL> BEGIN

   DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();

END;

/

Now, I will generate ADDM Report for PDB1 database:

SQL> alter session set container=PDB1;

Session altered.

SQL> @?/rdbms/admin/addmrpt.sql

Current Instance

~~~~~~~~~~~~~~~~

   DB Id    DB Name      Inst Num Instance

———– ———— ——– ————

 2381533563 ORCLCDB             1 ORCLCDB

Instances in this Workload Repository schema

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

  DB Id      Inst Num   DB Name      Instance     Host

———— ———- ———    ———-   ——

  2381533563     1      ORCLCDB      ORCLCDB      oracle-19c-v

Using 2381533563 for database Id

Using          1 for instance number

Specify the number of days of snapshots to choose from

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Entering the number of days (n) will result in the most recent

(n) days of snapshots being listed.  Pressing <return> without

specifying a number lists all completed snapshots.

Listing the last 3 days of Completed Snapshots

Instance     DB Name      Snap Id       Snap Started    Snap Level

———— ———— ———- —————— ———-

ORCLCDB      ORCLCDB              1  24 Sep 2020 12:38    1

                                  2  24 Sep 2020 14:00    1

                                  3  24 Sep 2020 14:25    1

                                  4  24 Sep 2020 14:30    1

                                  5  24 Sep 2020 14:50    1

Specify the Begin and End Snapshot Ids

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Enter value for begin_snap: 4

Begin Snapshot Id specified: 4

Enter value for end_snap: 5

End   Snapshot Id specified: 5

Specify the Report Name

~~~~~~~~~~~~~~~~~~~~~~~

The default report file name is addmrpt_1_4_5.txt.  To use this name,

press <return> to continue, otherwise enter an alternative.

Enter value for report_name: ADDM_PDB1_SEPT24.txt

Using the report name ADDM_PDB1_SEPT24.txt

Running the ADDM analysis on the specified pair of snapshots …

Generating the ADDM report for this analysis …

          ADDM Report for Task ‘TASK_15’

          ——————————

Analysis Period

—————

AWR snapshot range from 4 to 5.

Time period starts at 24-SEP-20 02.30.46 PM

Time period ends at 24-SEP-20 02.50.05 PM

Analysis Target

—————

Database ‘ORCLCDB’ with DB ID 2381533563.

Database version 19.0.0.0.0.

ADDM performed an analysis of instance ORCLCDB, numbered 1 and hosted at

oracle-19c-vagrant.

ADDM detected that the system is a PDB.

Activity During the Analysis Period

———————————–

Total database time was 6 seconds.

The average number of active sessions was .01.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

There are no findings to report.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

          Additional Information

          ———————-

Miscellaneous Information

————————-

There was no significant database activity to run the ADDM.

End of Report

Report written to ADDM_PDB1_SEPT24.txt