Script - Check LOBSEGMENT Storage Usage of Oracle table BLOB column stored as SECUREFILE or BASICFILE

NAME

  CkSpaceBLOB.sql

DESCRIPTION

  Check the space usage of BLOB column which is stored as LOBSEGMENT type segment  

USAGE

     CkSapceBLOB <table_owner> <table_name> <lob_column_name>

Note

  1. This script is run as DBA using SQL*Plus or SQLcl.

  2. Case sensitive object name has to be enclosed with double quota then single quota.

     For example

     Case insensitive:  

           SQL>@CkSapceBLOB user01 table01 column01

     Case sensitive:

           SQL@CkSapceBLOB '"user01"' '"table01"' '"column01"'

Source code of CkSpaceBLOB.sql,

set echo off
set feed off
set verify off
set serveroutput on
declare
  f_table_owner     varchar2(255):='&1';
  f_table_name      varchar2(255):='&2';
  f_column_name     varchar2(255):='&3';
  f_segment_name    varchar2(255);
  f_data_type       varchar2(128);
  f_securefile      varchar2(3);
  f_full_col_name   varchar2(255):=f_table_owner||'.'||f_table_name||'('||f_column_name||')';
  f_sql             varchar2(255):='select nvl(sum(nvl(dbms_lob.getlength('||f_column_name||'),0)),0) from '||f_table_owner||'.'||f_table_name;
  l_segment_size_blocks number;
  l_segment_size_bytes  number;
  l_used_blocks         number;
  l_used_bytes          number;
  l_expired_blocks      number;
  l_expired_bytes       number;
  l_unexpired_blocks    number;
  l_unexpired_bytes     number;
  l_unused_blocks       number;
  l_unused_bytes        number;
  l_non_data_blocks     number;
  l_non_data_bytes      number;
begin
  ------------------------------------------------------------------------------------
  -- Convert object name to uppercase if not case sensitive (enclosed by double quota)
  ------------------------------------------------------------------------------------
  if instr(f_table_owner,'"')<>1 or instr(f_table_owner,'"',-1)<>length(f_table_owner) then
     f_table_owner:=upper(f_table_owner);
  else	 
     f_table_owner:=replace(f_table_owner,'"','');
  end if;
  if instr(f_table_name,'"')<>1 or instr(f_table_name,'"',-1)<>length(f_table_name) then
     f_table_name:=upper(f_table_name);
  else	 
     f_table_name:=replace(f_table_name,'"','');
  end if;
  if instr(f_column_name,'"')<>1 or instr(f_column_name,'"',-1)<>length(f_column_name) then
     f_column_name:=upper(f_column_name);
  else	 
     f_column_name:=replace(f_column_name,'"','');
  end if;
  ------------------------------------------------------------------------------------
  -- Get LOB segment name by column name
  ------------------------------------------------------------------------------------
  begin
    select a.segment_name, a.securefile, b.data_type
      into f_segment_name,f_securefile,f_data_type
      from dba_lobs a, dba_tab_cols b
     where a.owner=f_table_owner and a.table_name=f_table_name
           and a.column_name=f_column_name and b.owner=a.owner
		   and b.table_name=a.table_name and b.column_name=a.column_name;
    if f_data_type<>'BLOB' then
       dbms_output.put_line('ERROR: Column '||f_full_col_name||' is '||f_data_type||', not BLOB');
	   return;
	end if;
  exception
    when others then	
      if SQLCODE=100 then
  	     dbms_output.put_line('ERROR: BLOB column '||f_full_col_name||') not found!');
  	     return;
      else	
         raise;   
  	  end if;
  end;
  if f_securefile='YES' then
     -- Check space usage of SECUREFILE segment
     begin
       dbms_space.space_usage( segment_owner =>f_table_owner,
                               segment_name => f_segment_name,
                               segment_type => 'lob',
                               segment_size_blocks => l_segment_size_blocks,
                               segment_size_bytes => l_segment_size_bytes,
                               used_blocks => l_used_blocks,
                               used_bytes => l_used_bytes,
                               expired_blocks => l_expired_blocks,
                               expired_bytes => l_expired_bytes,
                               unexpired_blocks => l_unexpired_blocks,
                               unexpired_bytes => l_unexpired_bytes);
       l_unused_blocks := l_segment_size_blocks - (l_used_blocks + l_expired_blocks + l_unexpired_blocks);
       l_unused_bytes := l_segment_size_bytes - (l_used_bytes + l_expired_bytes + l_unexpired_bytes);
       l_non_data_blocks := l_unused_blocks + l_expired_blocks + l_unexpired_blocks;
       l_non_data_bytes :=  l_unused_bytes + l_expired_bytes + l_unexpired_bytes;
       dbms_output.put_line('');
       dbms_output.put_line('SECUREFILE BLOB column ['||f_full_col_name||']');
       dbms_output.put_line('=======================================================');
       dbms_output.put_line(' segment name           '||f_segment_name);
       dbms_output.put_line(' ------------------------------------------------');
       dbms_output.put_line(' segment blocks/bytes   = '||l_segment_size_blocks||' / '||l_segment_size_bytes);
       dbms_output.put_line(' unused blocks/bytes    = '||l_unused_blocks||' / '||l_unused_bytes);
       dbms_output.put_line(' used blocks/bytes      = '||l_used_blocks||' / '||l_used_bytes);
       dbms_output.put_line(' expired blocks/bytes   = '||l_expired_blocks||' / '||l_expired_bytes);
       dbms_output.put_line(' unexpired blocks/bytes = '||l_unexpired_blocks||' / '||l_unexpired_bytes);
       dbms_output.put_line(' ------------------------------------------------');
       dbms_output.put_line(' non data blocks/bytes  = '||l_non_data_blocks||' / '||l_non_data_bytes);
     end;
  else
     -- Check space usage of BASICFILE segment
     begin
       select sum(blocks),sum(bytes)
         into l_segment_size_blocks,l_segment_size_bytes
		 from dba_extents
        where segment_name=f_segment_name;
	   execute immediate f_sql into l_used_bytes;
       l_unused_bytes := l_segment_size_bytes - l_used_bytes;
       dbms_output.put_line('');
       dbms_output.put_line('BASICFILE BLOB column ['||f_full_col_name||']');
       dbms_output.put_line('=======================================================');
       dbms_output.put_line(' segment name           '||f_segment_name);
       dbms_output.put_line(' ------------------------------------------------');
       dbms_output.put_line(' segment blocks/bytes   = '||l_segment_size_blocks||' / '||l_segment_size_bytes);
       dbms_output.put_line(' lob data using bytes   = '||l_used_bytes);
       dbms_output.put_line(' non lob data bytes     = '||l_unused_bytes);
       dbms_output.put_line(' ------------------------------------------------');
       dbms_output.put_line(' The segment can be shrinked/reorganized if following SQL return records');
       dbms_output.put_line(' ');
       dbms_output.put_line('     select bytes, count(*)');
       dbms_output.put_line('       from dba_extents');
       dbms_output.put_line('      where owner = '''||f_table_owner||'''');
       dbms_output.put_line('            and segment_name = '''||f_segment_name||'''');
       dbms_output.put_line('            and bytes < '||l_unused_bytes);
       dbms_output.put_line('      group by bytes order by 2;');
	 end;
  end if;
end;
/
undefine 1
undefine 2
undefine 3

Oracle Database 12.2/18c/19c ORA-00600: [qosdExpStatRead: expcnt mismatch]

Oracle database 12.2, 18c or 19c database alert log file reports ORA-00600: [qosdExpStatRead: expcnt mismatch]. The error message is something like following

2023-01-29T15:55:03.286965-05:00
Errors in file /u01/app/oracle/diag/rdbms/cdb01/cdb01/trace/cdb01_mz00_513756.trc  (incident=97764) (PDBNAME=PDB1):
ORA-00600: internal error code, arguments: [qosdExpStatRead: expcnt mismatch], [65561], [25], [3125688], [1], [], [], [], [], [], [], []
PDB1(3):Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
2023-01-29T15:55:03.291141-05:00
Errors in file /u01/app/oracle/diag/rdbms/cdb01/cdb01/trace/cdb01_mz00_513756.trc:
ORA-00600: internal error code, arguments: [qosdExpStatRead: expcnt mismatch], [65561], [25], [3125688], [1], [], [], [], [], [], [], []

The error is due to mismatch between expression count (EXP_CNT) in SYS table SYS.EXP_OBJ$, and number of matching rows in table SYS.EXP_STAT$. For example, in the database which is reporting previous errors

sys@pdb1> show con_name
CON_NAME
---------
PDB1

sys@PDB1> select count(*) from sys.exp_stat$ where snapshot_id=1 and objn=3125688;

   COUNT(*)
___________
      65561

sys@PDB1> select exp_cnt from sys.exp_obj$ a where a.snapshot_id = 1 and a.objn = 3125688;

   EXP_CNT
__________
        25

The object (object number 3125688) has 65561 records with snapshot_id 1 in table sys.exp_stat$, but the value of EXP_CNT column in table sys.exp_obj$ for same object is 25. They are different, which trigger the ORA-600.

Solution

1. To stop the mismatch happening in the future, apply one-off patch 28681153. The fix is included in Database Release Update (DBRU) starting from 12.2.0.1.201020, 18.15, 19.7. The patch only fixes the issue with future occurrence, does not eliminate existing occurrence.

2. To eliminate the existing orrurrence (reporting ORA-600 against existing data in sys.exp_stat$ and sys.exp_obj$), apply patch 31143146 and set implicit parameter "_FIX_CONTROL" with follwoing command

ALTER SYSTEM SET "_FIX_CONTROL"='31143146:ON'

The fix for 31143146 is included in 19c DBRU starting from 19.11. The fix, no mattter installed by one-off patch or DBUR, is disabled by default. It has to be enabled with previous command to set parameter "_FIX_CONTROL".

If the fix is not applied or you do not want to set the parameter. You can manually reset existing data recordes to eliminate occuring errors with following steps,

Note: Run following in the container in which the error occures, it is pluggable database PDB1 in the previous example.

1). Find out objn and snapshot_id of mismatch records with sql

with b as (
   select count(*) cnt, objn, snapshot_id
     from sys.exp_stat$
     group by objn,snapshot_id)
select *
  from sys.exp_obj$ a, b
 where a.objn=b.objn
       and a.snapshot_id=b.snapshot_id
       and a.EXP_CNT<>b.CNT;

Example output

sys@PDB1> With b as (
  2      select count(*) cnt, objn, snapshot_id
  3        from sys.exp_stat$
  4        group by objn,snapshot_id)
  5  select *
  6    from sys.exp_obj$ a, b
  7   where a.objn=b.objn
  8         and a.snapshot_id=b.snapshot_id
  9*        and a.EXP_CNT<>b.CNT;

      OBJN    SNAPSHOT_ID    EXP_CNT      CNT       OBJN    SNAPSHOT_ID
__________ ______________ __________ ________ __________ ______________
   3125688              1         25    65561    3125688              1

2). Delete all records, which groups of objn and snapshot_id are listed in step 1, from sys.exp_stat$

  delete from sys.exp_stat$ where snapshot_id=<snapshot_id> and objn=<objn>;

Replace <snapshot_id> and <objn> with the values listed in step 1, for example

sys@PDB1> delete from sys.exp_stat$ where snapshot_id=1 and objn=3125688;

65,561 rows deleted.

3). Reset the column EXP_CNT of sys.exp_obj$ with sql

  update sys.exp_obj$ set exp_cnt=0 where snapshot_id=<snapshot_ip> and objn =<objn>;

Replace <snapshot_id> and <objn> with the values listed in step 1, for example

sys@PDB1> update sys.exp_obj$ set EXP_CNT=0 where snapshot_id=1 and objn=3125688;

1 row updated.

Rember to commit the changes.

19c database createtion getting ORA-46385 On AUDSYS.AUD$UNIFIED

When creating Oracle 19c databases, got errors as following,

comment on table AUDSYS.AUD$UNIFIED is
                        *
ERROR at line 1:

ORA-46385: DML and DDL operations are not allowed on table

"AUDSYS"."AUD$UNIFIED".

It was reported by Oracle support on 19.5. It is still there when creating non-cdb on 19.16. 

When the Unified Auditing is enabled on Oracle database home, the database creation tries to create a comment on internal Unified Auditing table and gets this error.

It is completely harmless and can be safely ignored.

Upgrade/patch JDK 8 used by Oracle Enterprise Manager 13.5 OMS and Agent

This document is for upgrading JDK used by Enterprise Manager (EM) 13.5 in both OMS and Agent home on Linux, Solaris and Windows. 

By default, JDK 8 version 1.8.0_261 is shipped with EM 13.5, and can be upgraded to certified higher version of JDK 8 (e.g. 1.8.0_341)

Important notes:
1. Only certified version can be used by EM. Therefore, latest version may not be applicable. Never upgrade JDK to the version other than JDK 8 (e.g. JDK 9).
2. Different versions of JDK have different requirements of patch level in OMS and Agent home. Make sure all required patches are applied before JDK is upgraded.
3. Before Upgrading the JDK on Agents, Ensure that OMS's JDK has been Upgraded already
4. Starting from JDK 1.8 update 291, TLS 1.0 and TLS 1.1 are disabled and not supported. If any targets, LDAP or Load Balancer are still using TLS 1.0 or TLS 1.1, the communication will fail. They have to be configured with TLS 1.2 before upgrading to this JDK update.
5. If EM repository database is configured with TLS 1.2 for secure communication [Check for SSL_VERSION=1.2 in sqlnet.ora file] , then after the JDK is upgraded on OMS, re-import the repository database certificate into the Oracle Management Service JDK TrustStore as following
* Backup keystore "$ORACLE_HOME/oracle_common/jdk/jre/lib/security/cacerts"
* Import repository database certificate with command
  $ORACLE_HOME/oracle_common/jdk/bin/keytool -importcert -file trustCert.pem -alias emreprootca -keystore $ORACLE_HOME/oracle_common/jdk/jre/lib/security/cacerts -storepass changeit

Pre-requisites for JDK 1.8 Update 341 (1.8.0_341) in OMS home
1. Upgrade Opatch to version 13.9.4.2.10
2. Rollback Patch 32880070 if it is already applied on OMS home
3. Apply following patches on OMS home, all the patches are mandatory
   Patch 34003602 : EM 13.5 RU 07 or later
   Patch 34236279 WLS PATCH SET UPDATE 12.2.1.4.220602  [WLS - WebLogic Server]
   Patch 34341032 OWSM BUNDLE PATCH 12.2.1.4.220701 [OWSM - ORACLE WEB SERVICES MANAGER]
   Patch 34248976 Coherence 12.2.1.4 Cumulative Patch 14 (12.2.1.4.14)
   Patch 33639718 ADR FOR WEBLOGIC SERVER 12.2.1.4.0 CPU JUL 2022
   Patch 33093748 FMW PLATFORM 12.2.1.4.0 SPU FOR APRCPU2021
   Patch 34257860 OHS (NATIVE) BUNDLE PATCH 12.2.1.4.220608  [OHS - Oracle HTTP Server]
   Patch 33950717 OPSS BUNDLE PATCH 12.2.1.4.220311  [OPSS - Oracle Platform Security Services]
   Patch 33877829 OSS BUNDLE PATCH 12.2.1.4.220219  [OSS - Oracle Security Service]
   Patch 34247006 ADF BUNDLE PATCH 12.2.1.4.220606
   Patch 34287807 FMW Thirdparty Bundle Patch 12.2.1.4.220616
   Patch 33903365 OAM CONSOLE LOGIN FAILS AFTER APPLYING 1.80.331 JDK ( APRIL JAVA CPU )
   Patch 32720458 Fix for JDBC Bug
   Patch 34237409 WEBCENTER CORE BUNDLE PATCH 12.2.1.4.220601
Pre-requisites for JDK 1.8 Update 341 (1.8.0_341) in Agent home
1. Before Upgrading the JDK on Agents, Ensure that OMS's JDK has been Updated already.
2. Patch 34129921 : RU 07 or later is applied on Agent home

1. Check OPatch version with command

$ORACLE_HOME/OPatch/opatch version

For example,

$ $ORACLE_HOME/OPatch/opatch version
OPatch Version: 13.9.4.2.10
OPatch succeeded.

2. List/check patches applied on OMS/Agent home by running command

$ORACLE_HOME/OPatch/opatch lspatches

For example,

$ $ORACLE_HOME/OPatch/opatch lspatches
34237409;WebCenter Core Bundle Patch 12.2.1.4.220601
33903365;One-off
34287807;FMW Thirdparty Bundle Patch 12.2.1.4.220616
34247006;ADF BUNDLE PATCH 12.2.1.4.220606
33877829;OSS BUNDLE PATCH 12.2.1.4.220219
33950717;OPSS Bundle Patch 12.2.1.4.220311
34257860;OHS (NATIVE) BUNDLE PATCH 12.2.1.4.220608
34341032;OWSM BUNDLE PATCH 12.2.1.4.220701
34126771;Oracle Enterprise Manager for Siebel 13c Release 5 Plug-in Update 8 (13.5.1.8) for Oracle Management Service
34126767;Oracle Enterprise Manager for Cloud Framework 13c Release 5 Plug-in Update 8 (13.5.1.8) for Oracle Management Service
34126761;Oracle Enterprise Manager for Systems Infrastructure 13c Release 5 Plug-in Update 8 (13.5.1.8) for Oracle Management Service
34126751;Oracle Enterprise Manager for Cloud 13c Release 5 Plug-in Update 8 (13.5.1.8) for Oracle Management Service
34126747;Oracle Enterprise Manager for Exadata 13c Release 5 Plug-in Update 8 (13.5.1.8) for Oracle Management Service
34126738;Oracle Enterprise Manager for Chargeback and Capacity Planning 13c Release 5 Plug-in Update 8 (13.5.1.8) for Oracle Management Service
34126726;Oracle Enterprise Manager for Zero Data Loss Recovery Appliance 13c Release 5 Plug-in Update 8 (13.5.1.8) for Oracle Management Service
34126720;Oracle Enterprise Manager for Fusion Applications 13c Release 5 Plug-in Update 8 (13.5.1.8) for Oracle Management Service
34126716;Oracle Enterprise Manager for Oracle Database 13c Release 5 Plug-in Update 8 (13.5.1.8) for Oracle Management Service
34126711;Oracle Enterprise Manager for Fusion Middleware 13c Release 5 Plug-in Update 8 (13.5.1.8) for Oracle Management Service
34126708;Oracle Enterprise Manager 13c Release 5 Platform Update 8 (13.5.0.8) for Oracle Management Service
34373589;WLS STACK PATCH BUNDLE 12.2.1.4.220711 (Patch 34373563)
34236279;WLS PATCH SET UPDATE 12.2.1.4.220602
34212770;RDA release 22.3-20220719 for OFM 12.2.1.4 SPB
33639718;33639718 - ADR FOR WEBLOGIC SERVER 12.2.1.4.0 JUL CPU 2022
1221414;Bundle patch for Oracle Coherence Version 12.2.1.4.14
33093748;One-off
32720458;JDBC 19.3.0.0 FOR CPUJAN2022 (WLS 12.2.1.4, WLS 14.1.1)
34003672;Oracle Enterprise Manager for Virtualization 13c Release 5 Plug-in Update 7 (13.5.1.7) for Oracle Management Service
33873406;Oracle Enterprise Manager for Oracle Virtual Infrastructure 13c Release 5 Plug-in Update 6 (13.5.1.6) for Oracle Management Service
33873314;Oracle Enterprise Manager for Big Data Appliance 13c Release 5 Plug-in Update 6 (13.5.1.6) for Oracle Management Service
33424187;Oracle Enterprise Manager for Storage Management 13c Release 5 Plug-in Update 3 (13.5.1.3) for Oracle Management Service
30152128;One-off
26626168;One-off

OPatch succeeded.

3. Download required/wanted version of JDK 8 Update

The patch number of correct JDK update and its download URL can be found from My Oracle Support (MOS) document Supported Java SE Downloads on MOS (Doc ID 1439822.1). 

For example, Patch 34170400 is Oracle JDK 8 Update 341. Following the URL, following file will be downloaded,

  p34170400_180341_Linux-x86-64.zip - For Linux x86-64

  p34170400_180341_MSWIN-x86-64.zip - For Windows x64

  p34170400_180341_SOLARIS64.zip    - For Solaris SPARC 64-bit

4. Upgrade JDK in OMS and Agent home

Note: $ORACLE_HOME refers to OMS or Agent home and <AGENT_INST_HOME> refers to Agent Instance home. All patch operations are only applicable on $ORACLE_HOME. <AGENT_INST_HOME> is only used to stop/start agent.

* Stop OMS/Agent

  For OMS:  $ORACLE_HOME/bin/emctl stop oms -all

  For Agent: <AGENT_INST_HOME>/bin/emctl stop agent

* Rename JDK directory $ORACLE_HOME/oracle_common/jdk 

  mv $ORACLE_HOME/oracle_common/jdk $ORACLE_HOME/oracle_common/jdk_bak

* Extract files from downloaded zip file to JDK directory $ORACLE_HOME/oracle_common/jdk

The extraction method may be different for different version of JDK Update on different platform. Following example is for JDK 8 Update 341 (1.8.0_341).

Unzip the downloaded zip file

  On Linux:   unzip p34170400_180341_Linux-x86-64.zip

  On Solaris: unzip p34170400_180341_SOLARIS64.zip

  On Windows: unzip p34170400_180341_MSWIN-x86-64.zip

It will create compressed/archive file

  On Linux:   jdk-8u341-linux-x64.tar.gz

  On Solaris: jdk-8u341-solaris-sparcv9.tar.gz

  On Windows: jdk-8u341-windows-x64.exe

Extract the file

  On Linux:

    cd $ORACLE_HOME/oracle_common

    tar xf jdk-8u341-linux-x64.tar.gz  - Directory jdk1.8.0_341 will be created

    mv jdk1.8.0_341 jdk

  On Solaris:

    cd $ORACLE_HOME/oracle_common

    tar xf jdk-8u341-solaris-sparcv9.tar.gz  - Directory jdk1.8.0_341 will be created

    mv jdk1.8.0_341 jdk

  On Windows:

     Launch file jdk-8u341-windows-x64.exe, enter the folder name as <ORACLE_HOME>/oracle_common/jdk/ and complete the JDK installation.

* Confirm the version of the JDK by running

  $ORACLE_HOME/oracle_common/jdk/bin/java -version

* Start OMS/Agent

  For OMS:  $ORACLE_HOME/bin/emctl start oms

  For Agent: <AGENT_INST_HOME>/bin/emctl start agent

How to Apply Patch on Oracle Enterprise Manger 13c (13.4/13.5) Agent in online / offline mode

Oracle Enterprise Manager (EM) 13.4/13.5 agent patch is released as one-off patch, Release Update, or bundle patches. These patches can be applied manually or through EM Cloud Control console.

Patch Oracle WebLogic Server 12c Release 2 12.2.1.x (12.2.1.3, 12.2.1.4) including WLS, OHS, Coherence, ADR and RDA

This instruction applies to Oracle WebLogic Server (WLS) 12.2.1.x standalone (Lite, Slim, Generic), Oracle Fusion Middleware (FMW) 12.2.1.x Infrastructure and WLS 12.2.1.x installed with Oracle Enterprise Manager (OEM) 13c.

The installation of these WLS releases includes following components,

  OPatch

  WebLogic Server

  Coherence Server

  Oracle HTTP Server (OHS)

  Automatic Diagnostic Repository (ADR) 

Oracle currently delivers the latest Critical Patch Updates for these components on a quarterly basis: January, April, July, and October of each year. Oracle highly recommends to apply these updates to secure your environment. 

Technically, the patches (one-off or bundle) for these components are patched independently. The patches are downloaded and installed separately for different components. It is frustrating for the administrator to figure out which component needs which patch and where to download different component patches. To simplify the download and patching process, starting with April 2022, a Stack Patch Bundle (SPB) is made available for Oracle WebLogic Server versions 12.2.1.x (12.2.1.3 and 12.2.1.4), it includes the WebLogic Server PSU and other required updates (for other components) in a single bundle.

1. Upgrade OPatch

OPatch has to be upgraded before other patches are applied. 

* Download patch 28186730, which is the latest version of OPatch for Enterprise Manager (EM) and Fusion Middle Ware (FMW) / WebLogic Server (WLS). Version 13.9.4.2.8 is current OPatch version for EM 13.5 ans WLS 12.2.1.4 and default file name is p28186730_139428_Generic.zip.

Note: WLS Stack Patch Bundle (SPB) already includes required OPatch version and It does not need to download OPatch separately if you patch WebLogic Server with SPB.

* Unzip downloaded OPatch to a temporary location outside of your Oracle home, fox example

$ unzip p28186730_139428_Generic.zip -d /stage/opatch
Archive:  p28186730_139428_Generic.zip
   creating: /stage/opatch/6880880/
  inflating: /stage/opatch/6880880/README.txt
  inflating: /stage/opatch/6880880/opatch_generic.jar
  inflating: /stage/opatch/6880880/version.txt

* If using OPatch shipped with SPB, unzip OPatch from unzipped SPB, fox example

$ unzip /stage/WLS_SPB_12.2.1.4.220418/tools/opatch/generic/p28186730_139428_Generic.zip -d /stage/opatch
Archive:  /stage/WLS_SPB_12.2.1.4.220418/tools/opatch/generic/p28186730_139428_Generic.zip
   creating: /stage/opatch/6880880/
  inflating: /stage/opatch/6880880/README.txt
  inflating: /stage/opatch/6880880/opatch_generic.jar
  inflating: /stage/opatch/6880880/version.txt

* Stop all processes which are using files from Oracle home

If Oracle Enterprise Manager OMS home, run following command to stop all processes

   $ORACLE_HOME/bin/emctl stop oms -all

* Backup Oracle home and Central Inventory, there is no mechanism to rollback to the older OPatch version without backup

* Check environment for OPatch

Log in and run commands as the OS user that installed Oracle WebLogic Server.

Unset the JAVA_HOME and ORACLE_HOME environment variables.

Verify that the JAVA_HOME setting in the <ORACLE_HOME>/oui/.globalEnv.properties is where you have installed your Java SE (JDK/JRE).

Example of OEM 13.5

$ cat /oracle/em13.5/middleware/oui/.globalEnv.properties
#This file is automatically generated
#Wed Jun 22 17:10:45 EDT 2022
COMMON_HOME=/oracle/em13.5/middleware/oracle_common
EMD_ROOT=/oracle/em13.5/agent/agent_13.5.0.0.0
JAVA_HOME=/oracle/em13.5/middleware/oracle_common/jdk
JAVA_HOME_1_8=/oracle/em13.5/middleware/oracle_common/jdk
JVM_64=
OMS_HOME=/oracle/em13.5/middleware/
ORACLE_HOME=/oracle/em13.5/agent/agent_13.5.0.0.0
PERL_HOME=/oracle/em13.5/agent/agent_13.5.0.0.0/perl

Example of standalone WebLogic Server 12.2.1.4

$ cat /oracle/Middleware/oui/.globalEnv.properties
#This file is automatically generated
#Sun Dec 12 10:59:49 EST 2021
JAVA_HOME=/oracle/jdk1.8.0_latest
JAVA_HOME_1_8=/oracle/jdk1.8.0_latest
JVM_64=

Note: To prevent OPatch from entering into self-patching mode, and to avoid inventory corruption issues, ensure that no OPatch operations are consuming a JDK/JRE located inside the ORACLE_HOME.  More details about OPatch self-patching mode, please check following document from Oracle Support

OPatch : Following Messages during Patching - "Start OOP by Prereq process. Launch OOP..." (Doc ID 2624030.1)

* Install OPatch with following command

  java [-Djava.io.tmpdir=<TEMP_DIR>] -jar <PATCH_HOME>/6880880/opatch_generic.jar -silent oracle_home=<ORACLE_HOME> [-invPtrLoc <INVENTORY_LOCATION>]

  Where 

     "-Djava.io.tmpdir=<TEMP_DIR>" is optional, it sets a custom location for the logs, and <TEMP_DIR> is an absolute path where the logs will be created. By default, /tmp is used for log location.

     "<PATCH_HOME>" is the location where OPatch file is unzipped, it is "/u01/opatch" in my example.

     "<ORACLE_HOME>" is the absolute path where you have installed FMW/WLS products. In my example, it is "/u01/app/oracle/em13.5/middleware"

     "-invPtrLoc <INVENTORY_LOCATION>" is optional and used to specify a custom Inventory location, <INVENTORY_LOCATION> is the absolute path to the oraInst.loc file

     The console output will show location of log files, it should be under "/tmp/OraInstall<TIMESTAMP>" or custom location if you have specified with "-Djava.io.tmpdir=<TEMP_DIR>". If OPatch upgrade session succeeds, logs will be copied under <CENTRAL_INVENTORY>/logs.

Example output

$ echo $ORACLE_HOME
/oracle/em13.5/middleware

$ $ORACLE_HOME/oracle_common/jdk/bin/java -jar /u01/opatch/6880880/opatch_generic.jar -silent oracle_home=$ORACLE_HOME
Launcher log file is /tmp/OraInstall2022-06-22_05-10-45PM/launcher2022-06-22_05-10-45PM.log.
Extracting the installer . . . . Done
Checking if CPU speed is above 300 MHz.   Actual 886.505 MHz    Passed
Checking swap space: must be greater than 512 MB.   Actual 20479 MB    Passed
Checking if this platform requires a 64-bit JVM.   Actual 64    Passed (-d64 flag is not required)
Checking temp space: must be greater than 300 MB.   Actual 20201 MB    Passed
Preparing to launch the Oracle Universal Installer from /tmp/OraInstall2022-06-22_05-10-45PM
Installation Summary


Disk Space : Required 41 MB, Available 97,832 MB
Feature Sets to Install:
        Next Generation Install Core 13.9.4.0.1
        OPatch 13.9.4.2.8
        OPatch Auto OPlan 13.9.4.2.8
Session log file is /tmp/OraInstall2022-06-22_05-10-45PM/install2022-06-22_05-10-45PM.log

Loading products list. Please wait.
  '''
The install operation completed successfully.

Logs successfully copied to /u01/app/oraInventory/logs.

2. Apply patches on WLS components

2.1 Option 1  Patch WLS with SPB, a new way starting in April 2022

If installing quarterly Patch Set Updates released in April 2022 or later, the SPB can be used to download and patch all WLS components with a single bundle.

* Downloaded SPB from Oracle Support site. For example, the April 2022 SPB for WebLogic 12.2.1.4 is Patch 34080315. The default patch file name is

 p34080315_122140_Generic.zip

* Unzip the file to stage/temporary directory

After unzipping the patch, a directory WLS_SPB_12.2.1.x.<VERSION> should be created with the following contents:

Note: Do not make any changes to this directory structure.

    WLS_SPB_12.2.1.x.<VERSION>/

        -|binary_patches (dir)

        -|README.txt (file)

        -|README.html (file)

        -|spbat-bundle.properties (file)

        -|tools (dir)

Under sub-directory tools/opatch/generic, OPatch installation file can be found, this is the version required by current SPB. If OPatch is older than required, it can upgraded with this OPatch file.

Under sub-directory binary_patches, following files (patch list file) can be found

    WLS_SPB_12.2.1.x.<VERSION>/binary_patches/

        -|aix64_patchlist.txt

        -|generic_patchlist.txt

        -|hpuxia64_patchlist.txt

        -|linux64_patchlist.txt

        -|linux_zser_patchlist.txt

        -|rollback_patchlist.txt

        -|solaris64_patchlist.txt

        -|solaris_sparc64_patchlist.txt

        -|windows64_patchlist.txt

Depending on the platform of your WebLogic is running on, the corresponding file is required by OPatch to apply the patches.

Unzipped file example,

$ unzip -q p34080315_122140_Generic.zip
$ 
$ ls
WLS_SPB_12.2.1.4.220418
$
$ ls -l WLS_SPB_12.2.1.4.220418
total 52
drwxr-xr-x 9 oracle oinstall  4096 Jun 22 17:28 binary_patches
-rw-r--r-- 1 oracle oinstall 38531 Apr 19 09:20 README.html
-rw-r--r-- 1 oracle oinstall    21 Apr 19 09:20 README.txt
-rwxr-xr-x 1 oracle oinstall   181 Apr 18 03:24 spbat-bundle.properties
drwxr-xr-x 5 oracle oinstall    44 Apr 18 03:23 tools
$
$ ls -l WLS_SPB_12.2.1.4.220418/tools/opatch/generic
total 51320
-rwxr-xr-x 1 oracle oinstall 52547596 Apr 18 03:23 p28186730_139428_Generic.zip
$
$ ls -l WLS_SPB_12.2.1.4.220418/binary_patches/*txt
-rwxr-xr-x 1 oracle oinstall 109 Apr 18 03:23 WLS_SPB_12.2.1.4.220418/binary_patches/aix64_patchlist.txt
-rwxr-xr-x 1 oracle oinstall  98 Apr 18 03:23 WLS_SPB_12.2.1.4.220418/binary_patches/generic_patchlist.txt
-rwxr-xr-x 1 oracle oinstall 112 Apr 18 03:23 WLS_SPB_12.2.1.4.220418/binary_patches/hpuxia64_patchlist.txt
-rwxr-xr-x 1 oracle oinstall 111 Apr 18 03:23 WLS_SPB_12.2.1.4.220418/binary_patches/linux64_patchlist.txt
-rwxr-xr-x 1 oracle oinstall 114 Apr 18 03:23 WLS_SPB_12.2.1.4.220418/binary_patches/linux_zser_patchlist.txt
-rwxr-xr-x 1 oracle oinstall  71 Apr 18 03:24 WLS_SPB_12.2.1.4.220418/binary_patches/rollback_patchlist.txt
-rwxr-xr-x 1 oracle oinstall 113 Apr 18 03:23 WLS_SPB_12.2.1.4.220418/binary_patches/solaris64_patchlist.txt
-rwxr-xr-x 1 oracle oinstall 119 Apr 18 03:23 WLS_SPB_12.2.1.4.220418/binary_patches/solaris_sparc64_patchlist.txt
-rwxr-xr-x 1 oracle oinstall 113 Apr 18 03:23 WLS_SPB_12.2.1.4.220418/binary_patches/windows64_patchlist.txt

* (Optional) Run OPatch with the -report flag to produce a log of patches that will be applied, but does not actually update anything

Navigate to the binary patches directory

  WLS_SPB_12.2.1.x.<VERSION>/binary_patches

Run OPatch with the -report flag to produce a log to review:

  <ORACLE_HOME>/OPatch/opatch napply -report -oh <ORACLE_HOME> -phBaseFile <patch_list_file>

Make sure the -phBaseFile value corresponds to your specific UNIX operating platform. For example, Oracle Linux 64 bit needs file linux64_patchlist.txt.

Note: Use generic_patchlist.txt to apply generic patches only in case their install platform specific file is not provided.

Once the opatch napply -report command is executed, review the output of the command and the logs it produces to be aware of any possible issues. No changes have been made to your Oracle home even though the command output may say that patches were successfully applied.

Example on Oracle Linux 8

$ cd WLS_SPB_12.2.1.4.220418/binary_patches/

$ /u01/app/oracle/em13.5/middleware/OPatch /opatch napply -report -oh /u01/app/oracle/em13.5/middleware -phBaseFile linux64_patchlist.txt
Oracle Interim Patch Installer version 13.9.4.2.8
Copyright (c) 2022, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/em13.5/middleware
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/em13.5/middleware/oraInst.loc
OPatch version    : 13.9.4.2.8
OUI version       : 13.9.4.0.0
Log file location : /u01/app/oracle/em13.5/middleware/cfgtoollogs/opatch/opatch2022-06-22_17-20-11PM_1.log


OPatch detects the Middleware Home as "/u01/app/oracle/em13.5/middleware"

Verifying environment and performing prerequisite checks...
Skip patch 33868012 from list of patches to apply: This patch is not needed.

  ... <<output truncated>> ...

ApplySession skipping inventory update.
Patches 1221413,32647448,32720458,33093748,34012040,34077658,34080360 successfully applied.
OPatch Session completed with warnings.
Log file location: /u01/app/oracle/em13.5/middleware/cfgtoollogs/opatch/opatch2022-06-22_17-20-11PM_1.log

OPatch completed with warnings.

* Run OPatch to install the patches included in the SPB

Navigate to the binary_patches directory

  WLS_SPB_12.2.1.x.<VERSION>/binary_patches

Run opatch napply:

  <ORACLE_HOME>/OPatch/opatch napply -oh <ORACLE_HOME> -phBaseFile <patch_list_file>

Make sure the -phBaseFile value corresponds to your specific UNIX operating platform.

The patchlist files are bundled into the SPB, so you do not need to provide a path to the file.

Once the opatch napply command is executed, review the output of the command and the logs it produces to be aware of any possible issues.

Changes have been made to your Oracle home if this command was successful.

Example on Oracle Linux

$ cd WLS_SPB_12.2.1.4.220418/binary_patches/

[oracle@saxon]$ /u01/app/oracle/em13.5/middleware/OPatch/opatch napply -oh /u01/app/oracle/em13.5/middleware -phBaseFile linux64_patchlist.txt
Oracle Interim Patch Installer version 13.9.4.2.8
Copyright (c) 2022, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/em13.5/middleware
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/em13.5/middleware/oraInst.loc
OPatch version    : 13.9.4.2.8
OUI version       : 13.9.4.0.0
Log file location : /u01/app/oracle/em13.5/middleware/cfgtoollogs/opatch/opatch2022-06-22_17-27-45PM_1.log


OPatch detects the Middleware Home as "/u01/app/oracle/em13.5/middleware"

Verifying environment and performing prerequisite checks...
Skip patch 33868012 from list of patches to apply: This patch is not needed.

Conflicts/Supersets for each patch are:

Patch : 32647448

Better duplicate of 31544353

Patch : 34012040

Bug Superset of 32698246
Super set bugs are:
31498405, 30510407, 32235275, 30295025, 31232471, 30362086, 32307656, 30558254, 32214441, 32016868, 30670689, 30729141, 31510290, 30771358, 30961904, 30589563, 30342923, 30469093, 31234666, 31765567, 32068710, 30468443, 29940841, 31316252, 30068341, 29660156, 32371861, 30838007, 30465861, 31567049, 30734182, 26547727, 30884852, 31142740, 31526201, 30230430, 31401659, 32108759, 30866037, 31118905, 30568713, 30155056, 30885114, 31724565, 31234573, 30885237, 30718589, 31765550, 30153412, 30633620, 30652362, 30326976, 32415913, 25219796, 30885128, 31011293, 31441174, 29247835, 31657139, 31671559, 30740009, 32248716, 32412974, 31332368, 30478451, 30067299, 30885217, 31332264, 18183882, 30563848, 30459026, 29769772, 30692988, 32244262, 30964331, 30814590, 32373306, 29878681, 32312961, 30341541, 32228228, 29971088, 31770512, 32528774, 31975423, 31113242, 25973136, 32425607, 29449188, 30284059, 32069620, 30958807, 31353368, 30362026, 32054481, 30837932, 31247235, 29630055, 30801769, 31564423, 31160218, 26444945, 31297042, 31913015, 30624882, 30285053, 30469341, 31380363, 31157988, 29671344, 31047981

Patch : 1221413

Bug Superset of 122146
Super set bugs are:
31470730, 31806259, 30689686, 30564187, 30729380, 31030896


Patches [   31544353 ] will be rolled back.

OPatch continues with these patches:   1221413  32647448  32720458  33093748  34012040  34077658  34080360  

Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/u01/app/oracle/em13.5/middleware')


Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...
Applying interim patch '1221413' to OH '/u01/app/oracle/em13.5/middleware'
Rolling back interim patch '31544353' from OH '/u01/app/oracle/em13.5/middleware'

...<<output truncated>>...
Patching component oracle.com.fasterxml.jackson.dataformat.jackson.dataformat.xml, 2.9.9.0.0...
Applying interim patch '34077658' to OH '/u01/app/oracle/em13.5/middleware'
ApplySession: Optional component(s) [ oracle.rda, 8.15.17.03.14 ] , [ oracle.rda, 20.1.20.1.21 ] , [ oracle.rda, 20.2.20.4.21 ]  not present in the Oracle Home or a higher version is found.

Patching component oracle.rda, 19.3.19.8.2...
Applying interim patch '34080360' to OH '/u01/app/oracle/em13.5/middleware'

Patching component oracle.wls.core.app.server, 12.2.1.4.0...
Patches 1221413,32647448,32720458,33093748,34012040,34077658,34080360 successfully applied.
Sub-set patch [122146] has become inactive due to the application of a super-set patch [1221413].
Sub-set patch [32698246] has become inactive due to the application of a super-set patch [34012040].
Please refer to Doc ID 2161861.1 for any possible further required actions.
OPatch Session completed with warnings.
Log file location: /u01/app/oracle/em13.5/middleware/cfgtoollogs/opatch/opatch2022-06-22_17-27-45PM_1.log

OPatch completed with warnings.

* Start all WebLogic Servers.

2.2 Option 2  Patch one component at one time, a popular way before April 2022 and still an alternative method when SPB is not applicable

When applying patches released before April 2022 or only for single component (e.g. OHS, Coherence, etc.), the normal (old traditional) way has to be used.

* Download required patch for the specific component

  WebLogic Server Patch Set Update (PSU), such as Patch 33727616 - January 2022 Patch Set Update (PSU) for WebLogic Server 12.2.1.4  (PSU 12.2.1.4.220105)

  Oracle HTTP Server (OHS) Bundle Patch, such as Patch 32673423 - Oracle OHS (Native) Bundle Patch 12.2.1.4.210324

  Oracle Coherence cumulative patches, such as Patch 33591019 - Coherence 12.2.1.4 Cumulative Patch 12 (12.2.1.4.12)

* Unzip the downloaded file, and run "opatch apply" to install the patch

Oracle Data Pump Export/Import SODA Collection Data

Simple Oracle Document Access (SODA ) collections can be exported and imported using Oracle Data Pump Utilities starting with version 19.6.

In Oracle database, a SODA collection is analogous to database table. A SODA collection is mapped to /backed by a table. A SODA document is analogous to, and is in fact backed by, a row of a database table. The row has one column for each document component: key, content, version, and so on.

When a SODA colleciton is created, a database table (mapped table) is created under current user and related collection metadata is saved under schema XDB. When inserting a document into a collection, it actually insert a record into the collection mapped table.

Therefore, export/import SODA include two parts,

  * Export/import the mapped table with Data Pump utility. Utility has to be 19.6 or higher

  * Copy collection metadata. Currently, Data Pump does not have specific export path for SODA Collection Metadata, it means there is no database object type for it used in export/import parameters INCLUDE & EXCLUDE. As workaround, the metadata can be 'copied' by generating creation script in source database and running generated script in destination database.

Step 1. Export the mapped table (contains SODA documents) with Data Pump

The SODA Collection mapped tables can be exported individually or together with other tables/objects in SCHEMA mode or FULL mode.

For example, user soda_user logs into database and list his SODA collections and tables as following

soda_user@db01> select uri_name,object_type,object_schema,object_name from user_soda_collections;

URI_NAME          OBJECT_TYPE  OBJECT_SCHEMA   OBJECT_NAME
----------------- ------------ --------------- ------------------------------
SodaCollection1   TABLE        SODA_USER       SodaCollection1
SodaCollection2   TABLE        SODA_USER       SodaCollection2

soda_user@db01> select table_name from user_tables;

TABLE_NAME
------------------------------
SodaCollection1
SodaCollection2

The first SQL list all SODA Collections for current user 'SODA_USER', the two collections' names are 'SodaCollection1' and 'SodaCollection2' (column URI_NAME of view USER_SODA_COLLECITONS) and their respective mapped tables are created under current schema 'SODA_USER' with same names as collection names. 

Note: the collection name and table name are case sensitive.

The sencod SQL list all tables owned by current user, the output confirms that the two mapped tables listed in first SQL do be there.

The mapped tables can be exported with command

   expdp username/password schemas=<schema_name> include=<table_list> dumpfile=<dump_file> logfile=<log_file>

or

   expdp username/password schemas=<schema_name> dumpfile=<dump_file> logfile=<log_file>

If possible, I prefer the second command because I do not have to list all tables if many, especially the headache to escape the quota (double & single) characters in the INCLUDE parameter.

For example, export specified tables as following

expdp soda_user/password schemas=soda_user include=TABLE:\"in (\'SodaCollection1\',\'SodaCollection2\')\"  dumpfile=soda_collection.dmp logfile=soda_collection_exp.log

Step 2. Unload SODA collection metadata by generatting collection-creating script from source database

Log into source database as user who created the collection (soda_user in my example database), run following PL/SQL to generate scripts

set serveroutput on
set feedback off
set linesize 32767
begin
  dbms_output.put_line('set serveroutput on');
  dbms_output.put_line('declare');
  dbms_output.put_line('  soda_c soda_collection_t;');
  dbms_output.put_line('begin');
  for c1 in (select uri_name, json_descriptor from user_soda_collections) loop
    dbms_output.put_line('  -- Collection '''||c1.uri_name||'''');
    dbms_output.put_line('  begin');
    dbms_output.put_line('    soda_c:=dbms_soda.create_collection(collection_Name=>'''||c1.uri_name||''', metadata=>'''||c1.json_descriptor||''');');
    dbms_output.put_line('    dbms_output.put_line(''  [INFO]  SODA Collection ['||c1.uri_name||'] created successfully'');');
    dbms_output.put_line('  exception');
    dbms_output.put_line('    when others then');
    dbms_output.put_line('      dbms_output.put_line(''  [ERROR] SODA Collection ['||c1.uri_name||'] creation failed with errors:'');');
    dbms_output.put_line('      dbms_output.put_line(SQLERRM);');
    dbms_output.put_line('  end;');
  end loop;
  dbms_output.put_line('end;');
  dbms_output.put_line('/');
end;
/

The script reads SODA collections' names (column URI_NAME of view USER_SODA_COLLECTIONS) and respective collections' metadata (column JSON_DESCRIPTION of view USER_SODA_COLLECTION), and generates creation statements for each collection.

Save the output as script file. If it is run with SQL*Plus or SQLcl, the output can be saved with SPOOL command.

Sample output as following

set serveroutput on
declare
  soda_c soda_collection_t;
begin
  -- Collection 'SodaCollection1'
  begin
    soda_c:=dbms_soda.create_collection(collection_Name=>'SodaCollection1', metadata=>'{"schemaName":"SODA_USER","tableName":"SodaCollection1","keyColumn":{"name":"ID","sqlType":"VARCHAR2","maxLength":255,"assignmentMethod":"CLIENT"},"contentColumn":{"name":"DOCUMENT","sqlType":"BLOB","compress":"NONE","cache":true,"encrypt":"NONE","validation":"STANDARD"},"lastModifiedColumn":{"name":"MODIFIEDON"},"mediaTypeColumn":{"name":"MEDIATYPE"},"creationTimeColumn":{"name":"CREATEDON"},"readOnly":false}');
    dbms_output.put_line('  [INFO]  SODA Collection [SodaCollection1] created successfully');
  exception
    when others then
      dbms_output.put_line('  [ERROR] SODA Collection [SodaCollection1] creation failed with errors:');
      dbms_output.put_line(SQLERRM);
  end;
  -- Collection 'SodaCollection2'
  begin
    soda_c:=dbms_soda.create_collection(collection_Name=>'SodaCollection2', metadata=>'{"schemaName":"SODA_USER","tableName":"SodaCollection2","keyColumn":{"name":"ID","sqlType":"VARCHAR2","maxLength":255,"assignmentMethod":"CLIENT"},"contentColumn":{"name":"DOCUMENT","sqlType":"BLOB","compress":"NONE","cache":true,"encrypt":"NONE","validation":"STANDARD"},"lastModifiedColumn":{"name":"MODIFIEDON"},"versionColumn":{"name":"VERSION","method":"MD5"},"creationTimeColumn":{"name":"CREATEDON"},"readOnly":false}');
    dbms_output.put_line('  [INFO]  SODA Collection [SodaCollection2] created successfully');
  exception
    when others then
      dbms_output.put_line('  [ERROR] SODA Collection [SodaCollection2] creation failed with errors:');
      dbms_output.put_line(SQLERRM);
  end;
end;
/

In the example, the database user SODA_USER has two SODA collections 'SodaColleciton01' and 'SodaColleciton02'. 

Note: the value of "schemaName" in metadata section is "SODA_USER" which owns the SODA data, it is hard-coded in the script. Thereore, the generated script has to be run under same user in destination database.

Step 3. Import the mapped table (contains SODA documents) into destination database with Data Pump

The mapped tables can be imported into destination database with command

   impdp username/password schemas=<schema_name> dumpfile=<dump_file> logfile=<log_file>

Here, <dump_file> is dump file created in step 1.

Step 4. Load SODA collection metadata into destination database by running collection-creating script created in step 2

Since the script hard-coded the schema name in metadata section, it has to be run by same user in source database. In my example, the hard-coded schema name is SODA_USER, and the user SODA_USER has to be created in my source database and run the script as user SODA_USER.

If the mapped tables are imported to different schema in step 3 (with parameter REMAP_SCHEMA), you have to manually edit the script to replace the old value (SODA_USER in my example) of "schemaName" with the new schema name, then run it as new user.

ORA-02291: integrity constraint (WWV_FLOW_FND_GU_INT_G_FK) violated - parent key not found - When Importing Workspace In APEX configured with ORDS & WebLogic

When creating users during importing workspace in APEX, got ORA-02291 error like following

ORA-02291: integrity constraint (APEX_200100.WWV_FLOW_FND_GU_INT_G_FK) violated - parent key not found
ORA-06512: at "APEX_200100.WWV_FLOW_FND_USER_INT", line 2067
ORA-06512: at "APEX_200100.WWV_FLOW_FND_USER_API", line 339
ORA-06512: at line 2
02291. 00000 - "integrity constraint (%s.%s) violated - parent key not found"
*Cause:    A foreign key value has no matching primary key value.
*Action:   Delete the foreign key or add a matching primary key.

This error occurs because pre-created user groups (SQL Developer, etc) do not have the same IDs in two APEX instances. It was reported as bug of APEX 4.2.1 when ORDS is used and deployed with WebLogic. But it also reported on APEX 18.1 and I got it on my APEX  20.1.

As a workaround, it can fixed by remove group assignment from the "create_fnd_user" statement in the workspace import SQL script as following,

Original SQL

begin
wwv_flow_fnd_user_api.create_fnd_user (
  p_user_id                      => '2471724602488540',
  p_user_name                    => 'DEVELOPER01',
  p_first_name                   => 'Tom',
  p_last_name                    => 'Scott',
  p_description                  => '',
  p_email_address                => 'user01@dbaplus.ca',
  p_web_password                 => '1CE93BEEE43FF616144AC66B076A57D7729B7F53E08230A1D344DF88CF52CC2B1D2D7383BB874B794EAB3841BA9FB466B759DC33E3C2C6E8C8C2A5D7C611F5D7',
  p_web_password_format          => '5;5;10000',
  p_group_ids                    => '1794918882609493:1795050459609497:1795182004609497:',
  p_developer_privs              => 'ADMIN:CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL',
  p_default_schema               => 'APP_SCHEMA',
  p_account_locked               => 'N',
  p_account_expiry               => to_date('202002030936','YYYYMMDDHH24MI'),
  p_failed_access_attempts       => 0,
  p_change_password_on_first_use => 'Y',
  p_first_password_use_occurred  => 'Y',
  p_allow_app_building_yn        => 'Y',
  p_allow_sql_workshop_yn        => 'Y',
  p_allow_websheet_dev_yn        => 'Y',
  p_allow_team_development_yn    => 'Y',
  p_allow_access_to_schemas      => '');
end;
/

New statement

begin
wwv_flow_fnd_user_api.create_fnd_user (
  p_user_id                      => '2471724602488540',
  p_user_name                    => 'DEVELOPER01',
  p_first_name                   => 'Tom',
  p_last_name                    => 'Scott',
  p_description                  => '',
  p_email_address                => 'user01@dbaplus.ca',
  p_web_password                 => '1CE93BEEE43FF616144AC66B076A57D7729B7F53E08230A1D344DF88CF52CC2B1D2D7383BB874B794EAB3841BA9FB466B759DC33E3C2C6E8C8C2A5D7C611F5D7',
  p_web_password_format          => '5;5;10000',
  p_group_ids                    => '',
  p_developer_privs              => 'ADMIN:CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL',
  p_default_schema               => 'APP_SCHEMA',
  p_account_locked               => 'N',
  p_account_expiry               => to_date('202002030936','YYYYMMDDHH24MI'),
  p_failed_access_attempts       => 0,
  p_change_password_on_first_use => 'Y',
  p_first_password_use_occurred  => 'Y',
  p_allow_app_building_yn        => 'Y',
  p_allow_sql_workshop_yn        => 'Y',
  p_allow_websheet_dev_yn        => 'Y',
  p_allow_team_development_yn    => 'Y',
  p_allow_access_to_schemas      => '');
end;
/

EM 12c/13c Configure Enterprise Manager with custom or third-party SSL Certificates

When deciding to configure Oracle Enterprise Manager with custom or third-party SSL certificates,  you usually need to complete configuration tasks with following EM components

  *  EM Cloud Control Console

  *  Oracle Management Service (OMS) and EM Agent

  *  WebLogic Server (WLS)

The configuration can be done in following steps.

Script - Enable Customized Password Complexity in Oracle non-cdb or cdb environment

The script creates a password complexity verification function, and enable the function with database profile. It also sets password policy with profile.

Script notes:

1. The script is executed by SYS in single instance database or RAC database

2. The script enable password complexity and policy by setting database profile, by default, it will change DEFAULT profile, it can be changed by changing the value of SQL*Plus substitution variable S_PROFILE in the script.

3. The verification function name (default name is dbaplus_verify_function) and all password complexity and password policy options can be changed by yourself with the substitution variables (define statements in the script)

4. The script can be executed in non-cdb or pluggable database (pdb) which is open in READ WRITE mode

5. If script is executed in root container (CDB$ROOT) of container database (CDB), it will apply the changes in all pdbs which are open in READ WRITE mode. If seed pdb (PDB$SEED) is open in READ ONLY mode, it will be re-open in READ WRITE mode to apply the change, then re-open in READ ONLY mode. Any pdbs which is not open will be skipped.

--------------------------------------------------
define S_VERIFY_FUNCTION='dbaplus_verify_function'
define S_PROFILE='DEFAULT'
define S_PASSWORD_LIFE_TIME='unlimited'
define S_PASSWORD_GRACE_TIME='7'
define S_PASSWORD_REUSE_TIME='1'
define S_PASSWORD_REUSE_MAX='10'
define S_PASSWORD_LOCK_TIME='.0104'
define S_FAILED_LOGIN_ATTEMPTS='5'
define S_NO_OF_CHAR='12'
define S_NO_OF_DIFF='1'
define S_NO_OF_UPPERCASE='1'
define S_NO_OF_LOWERCASE='1'
define S_NO_OF_DIGIT='1'
define S_NO_OF_SPECIAL='1'
--------------------------------------------------
set echo off
set verify off
set feedback off
set trimspool on
set serveroutput on
declare
  isRAC varchar2(5);
  isContainer varchar2(5);
  i_return integer;
  i_handle integer;
  s_container varchar2(255);
  s_open_mode varchar2(255);
  -- SQL to grant execute privilege on the function to public
  s_grant varchar2(100) := 'grant execute on &S_VERIFY_FUNCTION to public';
  s_close_seed varchar2(255) := 'alter pluggable database PDB$SEED close immediate';
  s_open_seed varchar2(255) := 'alter pluggable database PDB$SEED open read only';
  s_open_rw_seed varchar2(255) := 'alter pluggable database PDB$SEED open read write';
  -- SQL to alter profile
  s_modify_profile varchar2(2000):=q'[
    alter profile &S_PROFILE limit
      password_life_time &S_PASSWORD_LIFE_TIME
      password_grace_time &S_PASSWORD_GRACE_TIME
      password_reuse_time &S_PASSWORD_REUSE_TIME
      password_reuse_max &S_PASSWORD_REUSE_MAX
      password_lock_time &S_PASSWORD_LOCK_TIME
      failed_login_attempts &S_FAILED_LOGIN_ATTEMPTS
      password_verify_function &S_VERIFY_FUNCTION]';
  -- PL/SQL block to create custom password verify function
  s_create_func varchar2(2000):=q'[
    create or replace function &S_VERIFY_FUNCTION( username varchar2, password varchar2, old_password varchar2)
     return boolean is
       p_diff    integer;
       p_lang    varchar2(512);
       p_message varchar2(512);
       p_return  number;
    begin
       -- Get the context p_lang and use utl_lms for p_messages
       p_lang := sys_context('userenv','lang');
       p_lang := substr(p_lang,1,instr(p_lang,'_')-1);
       --19c ora_complexity_check(password varchar2, chars number, letter number, uppercase number, lowercase number, digit number, special number)
       --12c ora_complexity_check(password varchar2, chars number, letter number, upper number, lower number, digit number, special number)
       if not ora_complexity_check(password, &S_NO_OF_CHAR,null,&S_NO_OF_UPPERCASE,&S_NO_OF_LOWERCASE,&S_NO_OF_DIGIT,&S_NO_OF_SPECIAL) then
          return(false);
       end if;
       -- Check if the password differs from the previous password by at least S_NO_OF_DIFF characters
       if old_password is not null then
          p_diff := ora_string_distance(old_password, password);
          if p_diff < &S_NO_OF_DIFF then
             p_return := utl_lms.get_message(28211, 'RDBMS', 'ORA', p_lang, p_message);
             raise_application_error(-20000, utl_lms.format_message(p_message, '&S_NO_OF_DIFF'));
          end if;
       end if;
       return(true);
    end;]';
begin
  -- CDB
  select value into isContainer from v$parameter where name='enable_pluggable_database';
  -- RAC database
  select value into isRAC from v$parameter where name='cluster_database';
  if isRAC='TRUE' then
     s_close_seed := s_close_seed||' instances=all';
     s_open_seed := s_open_seed||' instances=all';
  end if;
  s_container := sys_context('userenv','con_name');
  if s_container<>'CDB$ROOT' then -- In Non-CDB or PDB
     execute immediate s_create_func;
     execute immediate s_grant;
     execute immediate s_modify_profile;
     dbms_output.new_line;
     if isContainer='TRUE' then
        dbms_output.put_line('Succeeded in pluggable database '''||s_container||'''');
     else
        dbms_output.put_line('Succeeded in non-cdb database '''||s_container||'''');
     end if;
  else -- In root container CDB$ROOT
     execute immediate s_create_func;
     execute immediate s_grant;
     execute immediate s_modify_profile;
     dbms_output.new_line;
     dbms_output.put_line('Succeeded in root container '''||s_container||'''.');
     begin
       i_handle := dbms_sql.open_cursor;
       --for c1 in (select name,open_mode from v$pdbs where open_mode='READ WRITE' or (name='PDB$SEED' and open_mode='READ ONLY'))
       for c1 in (select name,open_mode from v$pdbs)
       loop
         s_open_mode := c1.open_mode;
         if c1.name='PDB$SEED' and c1.open_mode='READ ONLY' then
            execute immediate s_close_seed;
            execute immediate s_open_rw_seed;
            s_open_mode := 'READ WRITE';
         end if;
         if s_open_mode='READ WRITE' then
            dbms_sql.parse(c=>i_handle, statement=>s_create_func, language_flag=>DBMS_SQL.NATIVE, container=>c1.name);
            i_return := dbms_sql.execute(i_handle);
            dbms_sql.parse(c=>i_handle, statement=>s_grant, language_flag=>DBMS_SQL.NATIVE, container=>c1.name);
            i_return := dbms_sql.execute(i_handle);
            dbms_sql.parse(c=>i_handle, statement=>s_modify_profile, language_flag=>DBMS_SQL.NATIVE, container=>c1.name);
            i_return := dbms_sql.execute(i_handle);
            dbms_output.new_line;
            dbms_output.put_line('Succeeded in pluggable database '''||c1.name||'''.');
            if c1.name='PDB$SEED' then
               execute immediate s_close_seed;
               execute immediate s_open_seed;
            end if;
         else
            dbms_output.new_line;
            dbms_output.put_line('Skipped pluggable database '''||c1.name||''' which is in '''||s_open_mode||''' mode.');
         end if;
       end loop;
       dbms_sql.close_cursor(i_handle);
     exception
       when others then
         dbms_sql.close_cursor(i_handle);
         raise;
     end;
  end if;
  dbms_output.new_line;
end;
/

Export/import APEX workspace and application with SQLcl

One of the reason why I prefer SQLcl to SQL*Plus is that SQLcl has built-in "apex" commands. APEX workspace and application can be easily export / import with SQLcl.

Note: If you have multiple versions of APEX schemas created in the database, you have to log into database as current version of APEX schema to run the commands. If you log in as other privileged users (i.e. SYS, SYSTEM), you have to set current schema before running apex command with following SQL,

  alter session set current_schema=<current-apex-schema>;

For example, if APEX 19.1 is installed

alter session set current_schema=APEX_190100;

1. Export / Import APEX workspace

Workspace can be exported with following command

  apex -workspaceid <workspace_id> <-expWorkspace | -expMinimal | -expFiles>

The command will create a SQL script file under current directory with name w<workspace_id>.sql, it can be run with one of following options

      -expWorkspace:  Export workspace identified by -workspaceid

      -expMinimal:    Only export workspace definition, users, and groups

      -expFiles:      Export all workspace files identified by -workspaceid

And <workspace id> can be listed/found with following SQL

  select workspace_id,workspace from apex_workspaces;

For example

apex_190100@ddb01> select workspace_id,workspace from apex_workspaces;

      WORKSPACE_ID WORKSPACE
  ---------------- --------------------------
                11 COM.ORACLE.APEX.REPOSITORY
                12 COM.ORACLE.CUST.REPOSITORY
                10 INTERNAL
  4482715458638925 SALES
  2104419987831886 FINANCE
  6822807081190901 HR

If export workspace SALES,

apex_190100@ddb01> apex export -workspaceid 4482715458638925 -expWorkspace
Exporting Workspace 4482715458638925:'SALES'

  Completed at Thu Mar 10 19:56:28 EST 2022

It creates a SQL script file named w4482715458638925.sql. This script file is the workspace SALES export file. To import this workspace to new APEX instance, just run this script under APEX schema in that database.

For example, import SALES workspace to database db02

apex_190100@db02> @w4482715458638925.sql
API Last Extended:20190331
Your Current Version:20190331
This import is compatible with version: 20190331
COMPATIBLE (You should be able to run this import without issues.)

WORKSPACE 4482715458638925
Creating workspace SALES...
Creating Groups...
Creating group grants...
Creating Users...
...workspace objects
...RESTful Services
SCHEMA SALESDATA - User Interface Defaults, Table Defaults
SCHEMA HRDATA - User Interface Defaults, Table Defaults
User Interface Defaults, Attribute Dictionary
... elapsed: 14.1 sec
...done

2. Export / import application

Export single application with following command

  apex export -applicationid <application_id>

Export all applications of specified workspace

  apex export -workspaceid <workspace_id>

Both commands will create one SQL script file for each application under current directory. The script file name is f<application_id>.sql.

For example, export all applications of workspace SALES

apex_190100@db01> apex export -workspaceid 4482715458638925
Exporting Application 108:'Promotion Channel'

  Completed at Fri Mar 11 21:23:27 EST 2022

Exporting Application 126:'Sales History'

  Completed at Fri Mar 11 21:23:27 EST 2022

Exporting Application 128:'Customers'

  Completed at Fri Mar 11 21:23:27 EST 2022

Totally, three applications are exported as three SQL script files. The files' name are f108.sql, f126.sql and f128.sql.

To import application, run the exported SQL with APEX schema in new database. For example, import application "Sales History" (application id 126) into database db02

apex_190100@db02> @f126.sql
API Last Extended:20190331
Your Current Version:20190331
This import is compatible with version: 20190331
COMPATIBLE (You should be able to run this import without issues.)

APPLICATION 126 - Sales History
--application/delete_application
--application/create_application
--application/shared_components/navigation/lists/data_load_process_train_load_customers
--application/shared_components/navigation/lists/admin_reports
--application/shared_components/navigation/lists/order_page
--application/shared_components/navigation/lists/mobile_options
--application/shared_components/navigation/lists/footer_controls
--application/shared_components/navigation/lists/charts
--application/shared_components/navigation/lists/data_load_process_train_load_customers_002
--application/shared_components/navigation/lists/reports
   ...<output truncated> ...
--application/deployment/checks
--application/deployment/buildoptions
--application/end_environment
... elapsed: 8.32 sec
...done

Database failed to start with errors ORA-01565 ORA-17503 ORA-27300 ORA-27301 ORA-27302

 When starting database, get errors ORA-01565 ORA-17503 ORA-27300 ORA-27301 ORA-27302. For example,

$ srvctl start database -db db01
PRCR-1079 : Failed to start resource ora.db01.db
CRS-5017: The resource action "ora.db01.db start" encountered the following error:
ORA-01078: failure in processing system parameters
ORA-01565: error in identifying file '+DATA/db01/parameterfile/spfiledb01.ora'
ORA-17503: ksfdopn:10 Failed to open file +DATA/db01/parameterfile/spfiledb01.ora
ORA-27300: OS system dependent operation:open failed with status: 13
ORA-27301: OS failure message: Permission denied
ORA-27302: failure occurred at: sskgmsmr_7
. For details refer to "(:CLSN00107:)" in "/u01/app/grid/diag/crs/host01/crs/trace/ohasd_oraagent_grid.trc".

CRS-2674: Start of 'ora.db01.db' on 'host01' failed

The ORA-2730x errors could be seen in many different scenarios, they are usually caused by OS system call error or OS configuration issue and are in the form of

ORA-27300: OS system dependent operation:%s failed with status: %s
ORA-27301: OS failure message: %s
ORA-27302: failure occurred at: %s

One of them is as in previous example, it shows error ORA-27300 with 'status 13' and ORA-27302 with 'sskgmsmr_7'. This issue could happen when Oracle ASM is running as an account (usually grid) which is different from Oracle database home owner (usually oracle). For example, Oracle Grid Infrastructure (GI) installation or role separate Oracle Restart (standalone GI) installation. In these environments, if you got following errors

ORA-27300: OS system dependent operation:open failed with status: 13
ORA-27301: OS failure message: Permission denied
ORA-27302: failure occurred at: sskgmsmr_7

The culprit should be the permission of the directory "<GRID_HOME>/dbs" or file "<GRID_HOME>/dbs/ab_+ASM.dat". The permissions in the previous example system as following

$ ls -ld /u01/app/grid/product/19.0.0/grid_1/dbs
drwxr-xr-x    2 grid     oinstall        256 Feb 09 14:39 /u01/app/grid/product/19.0.0/grid_1/dbs
$ cd /u01/app/grid/product/19.0.0/grid_1/dbs
$ ls -l
total 56
-rw-rw----    1 grid     oinstall       2304 Feb 09 14:39 ab_+ASM.dat
-rw-rw----    1 grid     oinstall       1544 Feb 09 15:00 hc_+ASM.dat
-rw-r-----    1 grid     oinstall         57 Sep 25 2020  init+ASM.ora
-rw-r--r--    1 grid     oinstall       3079 May 16 2015  init.ora
-rw-r-----    1 grid     oinstall       9728 Sep 25 2020  orapw+ASM

Everyone has read/execute (r-x) permission on directory "dbs", but only owner (grid) and group (oinstall) have read/write permission on file "ab_+ASM.dat" and other users do not have access to the file.

The issues can be fixed by granting read permission to other users as following

$ chmod o+r ab_+ASM.dat
$ ls -l
total 56
-rw-rw-r--    1 grid     oinstall       2304 Feb 09 14:39 ab_+ASM.dat
-rw-rw----    1 grid     oinstall       1544 Feb 09 15:09 hc_+ASM.dat
-rw-r-----    1 grid     oinstall         57 Sep 25 2020  init+ASM.ora
-rw-r--r--    1 grid     oinstall       3079 May 16 2015  init.ora
-rw-r-----    1 grid     oinstall       9728 Sep 25 2020  orapw+ASM

Oracle SQLcl installation and configuration

Oracle SQL Developer Command Line (SQLcl) is a free command-line interface for Oracle Database that combines the power of SQL*Plus and SQL Developer. It allows you to interactively or batch execute SQL and PL/SQL. SQLcl provides in-line editing, statement completion, and command recall for a feature-rich experience, as well as supporting your previously written SQL*Plus scripts.

Script Find the Regular Source File of the Multi-level Symbolic Link on Linux / Unix

 A bash script is used to find out the final regular source file or directory of a multi-level symbolic on Linux and Unix (AIX, Solaris) platform.

Suppose there are following file and symbolic links on current system

-rw-r--r--   1 user01   group01      12 Feb 26 14:08 /dir1/file1.txt
lrwxrwxrwx   1 user01   group01      17 Feb 26 14:13 /dir2/file2.lnk -> ../dir1/file1.txt
lrwxrwxrwx   1 user01   group01      30 Feb 26 14:14 /dir3/file3.lnk -> /dir2/file2.lnk
lrwxrwxrwx   2 user01   group01      17 Feb 26 14:18 /dir4/file4_1.lnk -> ../dir3/file3.lnk
lrwxrwxrwx   1 user01   group01      11 Feb 26 14:21 /dir4/file4_2.lnk -> file4_1.lnk

And run the script getSrc.sh against symbolic link 'file4_2.lnk', the script should return regular file 'file1.txt' with proper path,

$ findSrc.sh /dir4/file4_2.lnk
/dir1/file1.txt

The script code as following

#!/bin/bash
srcFile="$1"
while [ -h "$srcFile" ]
do
  tmpStr=`ls -ld "$srcFile"`
  tmpSrc=`expr "$tmpStr" : '.*-> \(.*\)$'`
  if expr "$tmpSrc" : '/.*' > /dev/null; then
     srcFile="$tmpSrc"
  else
     srcFile=`dirname "$srcFile"`"/$tmpSrc"
  fi
done
if [[ $srcFile == *".."* ]] || [[ $srcFile == *"."* ]] ; then
   workDir=`pwd`
   tmpDir=`dirname "$srcFile"`
   tmpFile=`basename "$srcFile"`
   tmpDir=`cd "$tmpDir" > /dev/null && pwd`
   if [ x"$tmpDir" != x ]; then
      srcFile="$tmpDir""/$tmpFile"
   fi
   cd "$workDir"
fi
echo $srcFile

Line 4. The logical operator '-h' return TRUE if the following file "$srcFile" is a symbolic link.

Line 5. Gets the output of command 'ls -ld' against the symbolic link given by the first parameter of the script run and assign the output as string to variable 'tmpStr'. For example, running the command as following

$ ls -ld /dir4/file4
lrwxrwxrwx   1 oracle   oinstall       1 Feb 26 08:16 /dir4/file4 -> file3

The value of the variable tmpStr will be the string printed in red including the SPACE characters inside the string.

Line 6. The command 'expr' is run with string matching operator ':', it is doing anchored pattern match of regular expression in the string value of variable 'tmpStr', the match starts from beginning of the string. The regular expression '.*-> \(.*\)$' including following parts

  '.*'  represents any number of any character, it occurs twice in the expression. The first occurrence is followed by three characters '-> ' (hyphen, greater-than and space), they are together to match the string value from the beginning letter 'l' until the SPACE character before string 'file3' in the previous example output of command 'ls -ld'.

  

  '\(' and '\)' the back slash '\' is escape character and telles command 'expr' that the following characters '(' and ')' are parenthesis asking to return the string matching the expression inside the parenthesis. Here, the expression in the parenthesis is second occurrence of '.*', which will match all the characters after the three-character-group '-> ', it is 'file3' in the previous output example of command 'ls -ld'.

Eventually, the output of command 'expr' will be assigned to variable 'tmpSrc'.

Line 7. Test if the path of file saved in variable 'tmpSrc' is absolute path (start with '/').

Line 10. If the test is FALSE in line 7, it means the path of linked (source) file is relative path related to symbolic link file. Therefore, the full path of the source file has to be prefixed with the path of symbolic link.

Line 13-22. It is optional block, it makes the path of source file friendly. It will change path like following

    /dir1/dir2/../../dir3/file.txt

    /dir1/dir2/./../dir3/file.txt

    

to following more friendly format

    /dir3/file.txt

    /dir1/dir3/file.txt

Oracle RAC 19c pdb open failed with ORA-12963: A read-only instance cannot be the first instance to open a database

When opening pluggable database (pdb) on one node of two-node 19c RAC, got following errors

sys@rac01> alter pluggable database pdb1 open;
alter pluggable database pdb1 open
*
ERROR at line 1:
ORA-12963: A read-only instance cannot be the first instance to open a database.

Open the pdb on the other node. It is, by default, in read/write mode

sys@rac02> show pdbs

    CON_ID CON_NAME           OPEN MODE  RESTRICTED
---------- ------------------ ---------- ----------
         2 PDB$SEED           READ ONLY  NO
         3 pdb1               MOUNTED
sys@rac02> alter pluggable database pdb1 open;

Pluggable database altered.

sys@rac02> show pdbs

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

And, try to open pdb on local node again, it will be opened successfully, but will be READ ONLY mode

sys@rac01> alter pluggable database pdb1 open;

Pluggable database altered.

sys@rac01> show pdbs

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

Check the instances' status with command 'srvctl status database'

[oracle@rac01]$ srvctl status database -db orcl -verbose
Instance orcl_1 is running on node rac02. Instance status: Open.
Instance orcl_2 is running on node rac01. Instance status: Open,Readonly.

The instance is started in READ ONLY mode on node rac01, it usually happens when the node is assigned to a parallel query server pool (PQPOOL). A node assigned to PQPOOL is treated as a leaf node and database instance running on leaf node will be READ ONLY. 

Check the server pool configuration of the database

[oracle@rac01]$ srvctl config database -db orcl
Database unique name: orcl
Database name: orcl
Oracle home: /u01/app/oracle/product/19.0.0/dbhome_1
Oracle user: oracle
Spfile: +DATA/ORCL/PARAMETERFILE/spfile.299.1096020569
Password file: +DATA/ORCL/PASSWORD/pwdorcl.289.1096019487
Domain: 
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: dbpool,pool2
PQ Helper Server Pools: pool2
Disk Groups: DATA,FRA
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group: oper
Database instances:
Configured nodes:
CSS critical: no
CPU count: 0
Memory target: 0
Maximum memory: 0
Default network number for database services:
Database is policy managed

The database is configured with two pools: dbpool and pool2, and pool2 is a PQPOOL (PQ Helper Server Pools, also called parallel query server pool).

Check server pools' assignment with command 'srvctl status srvpool'

[oracle@rac01]$ srvctl status srvpool
Server pool name: Free
Active servers count: 0
Server pool name: Generic
Active servers count: 0
Server pool name: dbpool
Active servers count: 1
Server pool name: pool2
Active servers count: 1

The cluster is two-node RAC, both server pool dbpool and PQPOOL pool2 have one server assigned. It means orcl has one instance, which server node is in dbpool, will be normal (read/write) mode, and another instance, which server node is in pool2, will be read only mode.

Since it is a two-node RAC, and both nodes are hub nodes, PQPOOL is not necessary. The PQPOOL can be removed to fix the issue as following

1. Remove PQPOOL configuration from database with command 'srvctl modify database'

[oracle@rac01]$ srvctl stop database -db orcl

[oracle@rac01]$ srvctl modify database -db orcl -pqpool

[oracle@rac01]$ srvctl config database -db orcl
Database unique name: orcl
Database name: orcl
Oracle home: /u01/app/oracle/product/19.0.0/dbhome_1
Oracle user: oracle
Spfile: +DATA/ORCL/PARAMETERFILE/spfile.299.1096020569
Password file: +DATA/ORCL/PASSWORD/pwdorcl.289.1096019487
Domain: 
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: dbpool
Disk Groups: DATA,FRA
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group: oper
Database instances:
Configured nodes:
CSS critical: no
CPU count: 0
Memory target: 0
Maximum memory: 0
Default network number for database services:
Database is policy managed

2. Remove PQPOOL with command 'srvcl remove srvpool'

[oracle@rac01]$ srvctl remove srvpool -serverpool pool2
[oracle@rac01]$
[oracle@rac01]$ srvctl status srvpool
Server pool name: Free
Active servers count: 0
Server pool name: Generic
Active servers count: 0
Server pool name: dbpool
Active servers count: 2

The server pool dbpool gets two servers assigned, and database orcl will get two instances running in normal mode.

Oracle 12.2 EXPDP Fails Exporting Full PDB with ORA-39126 On KUPW$WORKER.FETCH_XML_OBJECTS

This issue happens in Oracle database 12.2 or higher version, the example in this post happened in 19.13.

When running Oracle Data Pump Export utility expdp to export PDB in Full Mode, the job fails with ORA-39126 and ORA-00942. Following is an example

ORA-39126: Worker unexpected fatal error in KUPW$WORKER.FETCH_XML_OBJECTS [ORA-00942: table or view does not exist

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.KUPW$WORKER", line 12630
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 9571
ORA-06512: at "SYS.DBMS_METADATA", line 2976
ORA-06512: at "SYS.DBMS_METADATA", line 3608
ORA-06512: at "SYS.DBMS_METADATA", line 5008
ORA-06512: at "SYS.DBMS_METADATA", line 5327
ORA-06512: at "SYS.DBMS_METADATA", line 9552
ORA-06512: at "SYS.KUPW$WORKER", line 15119

----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
900000045fb46b00     33543  package body SYS.KUPW$WORKER.WRITE_ERROR_INFORMATION
900000045fb46b00     12651  package body SYS.KUPW$WORKER.DETERMINE_FATAL_ERROR
900000045fb46b00     15452  package body SYS.KUPW$WORKER.FETCH_XML_OBJECTS
900000045fb46b00      3917  package body SYS.KUPW$WORKER.UNLOAD_METADATA
900000045fb46b00     13746  package body SYS.KUPW$WORKER.DISPATCH_WORK_ITEMS
900000045fb46b00      2439  package body SYS.KUPW$WORKER.MAIN
b00000055ff65380         2  anonymous block

DBMS_METADATA.SET_FILTER
DBMS_METADATA.SET_FILTER
DBMS_METADATA.SET_FILTER
KUPW: In FETCH_XML_OBJECTS
KUPW: End seqno is: 24
KUPF$FILE.OPEN_CONTEXT
KUPF$FILE.OPEN_CONTEXT
DBMS_METADATA.FETCH_XML_CLOB
DBMS_METADATA.FETCH_XML_CLOB
KUPW: In procedure DETERMINE_FATAL_ERROR with ORA-00942: table or view does not exist

The issues is usually seen when components installed in pdb do not match (less than) components installed in root container. For example, in root container, components installed as following

sys@orcl> alter session set container=cdb$root;

Session altered.

sys@orcl> show pdbs

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

sys@orcl> select comp_id,comp_name from dba_registry;

COMP_ID                        COMP_NAME
------------------------------ --------------------------------------------------
CATALOG                        Oracle Database Catalog Views
CATPROC                        Oracle Database Packages and Types
JAVAVM                         JServer JAVA Virtual Machine
XML                            Oracle XDK
CATJAVA                        Oracle Database Java Packages
APS                            OLAP Analytic Workspace
RAC                            Oracle Real Application Clusters
XDB                            Oracle XML Database
OWM                            Oracle Workspace Manager
CONTEXT                        Oracle Text
ORDIM                          Oracle Multimedia
SDO                            Spatial
XOQ                            Oracle OLAP API
OLS                            Oracle Label Security
APEX                           Oracle Application Express
DV                             Oracle Database Vault

sys@orcl> alter session set container=PDB$SEED;

Session altered.

sys@orcl> select comp_id,comp_name from dba_registry;

COMP_ID                        COMP_NAME
------------------------------ --------------------------------------------------
CATALOG                        Oracle Database Catalog Views
CATPROC                        Oracle Database Packages and Types
JAVAVM                         JServer JAVA Virtual Machine
XML                            Oracle XDK
CATJAVA                        Oracle Database Java Packages
APS                            OLAP Analytic Workspace
RAC                            Oracle Real Application Clusters
XDB                            Oracle XML Database
OWM                            Oracle Workspace Manager
CONTEXT                        Oracle Text
ORDIM                          Oracle Multimedia
SDO                            Spatial
XOQ                            Oracle OLAP API
OLS                            Oracle Label Security
APEX                           Oracle Application Express
DV                             Oracle Database Vault

Components installed in pdb

sys@orcl> alter session set container=pdb01;

Session altered.

sys@orcl> select comp_id,comp_name from dba_registry;

COMP_ID                        COMP_NAME
------------------------------ --------------------------------------------------
CATALOG                        Oracle Database Catalog Views
CATPROC                        Oracle Database Packages and Types
RAC                            Oracle Real Application Clusters
OWM                            Oracle Workspace Manager
XDB                            Oracle XML Database

Both root container and seed pdb have 16 components installed, but pdb01 only have 5 components installed. It usually happends when pdb is created from remote clone. If you got the issue in this scenario with database 12.2 or higher, it can be fixed by reloading Data Pump as following

1. Open all pluggable databases

alter pluggable database all open;

2. Run following script as SYS

@?/rdbms/admin/dpload.sql

3. Recompile invalid objects as SYS

@?/rdbms/admin/utlrp.sql

If reloading Data Pump is not applicable for you, schema mode can be used instead of full mode.

Configure Reverse Path Filter parameter "rp_filter" on Oracle Linux and Red Hat Enterprise Linux

When installing Oracle Grid Infrastructure (GI) 12c or higher version (18c, 19c, 21c), multiple network interfaces (NIC) can be used as private interconnection to implement inter-communication load balance and failover. However, if this configuration is adopted on Oracle Linux (OL) 6 or Red Hat Enterprise Linux (RHEL) 6 or higher, the GI installation prerequists checking shows error 

Oracle Cloud Infrastructure Plugin xxx not present for instance ocid1.instance.oc1.*

When you enable or configure moniter or managerment features on compute instance in Oracle Cloud Infrastructure (OCI), you have to enable the relavant Oracle Cloud Agent plugin. 

For example, if you want to use Bastion to connect to your compute instance residing in private subnet, you have to enable Bastion plugin on the instance. When enabling the plugin, you may see following error messsages,

Plugin Bastion not present for instance <ocid of the instance>

It happens because Oracle Cloud Agent running on the instance cannot access OCI services. The OCI services are out of the network where the instance resides. Instance's network is private subnet and does not have access to outside. Therefore a Service Gateway or NAT Gateway and proper route table rules are needed in the private subnet.

The issue can be fixed as following,

1. Create a Service Gateway for "All Services in Oracle Services Network" if it does not exist in the VCN in which the subnet resides. 

* In the Console, confirm you're viewing the instance on which you want to enable the plugin. 

* In the "Instance information" tab, click the name link beside "Virtual cloud network" under section "Instance details"

* On the left side of the page, click Service Gateways.

* Click Create Service Gateway.

* Enter the following values:

  Name: A descriptive name for the service gateway. 

  Create in compartment: Select same compartment where the VCN is created.

  Services: Select "All Services in Oracle Services Network". Currently, only two options for selection. The other "OCI Object Storage" is not applicable.

  Click "Create Service Gateway".

2. Update routing for the subnet

* Back to the instance home and in "nstance information" tab, click the name link beside "Subnet" under section "Primary VNIC"

* In "Subnet Information" tab, click the name link beside "Route Table"

* Under "Route Rules", click "Add Route Rules" and enter the following values:

  Target Type: Service Gateway.

  Destination Service: Select "All Services in Oracle Services Network"

  Target Service Gateway: Select the gateway which is created earlier. If it does not show in the list, click "Change Comparment" to choose the compartment where the Service Gateway is created.

  Click "Save".

Wait for a few minutes, the issue should be gone and the plugin status shows "Running".

Oracle 19c GI installation failed with PRVG-13605 : Query of chrony daemon for its time sources could not be performed

The issue happens on Oracle Linux / Red Hat Enterprise Linux 7/8 on which chronyd is adopted as the network time protocol daemon instead of ntpd. When installing Oracle Grid Infrastructure 19c on these platform, the installation fails during prerequisit check, the message shows that

Verifying Network Time Protocol (NTP) ...FAILED
  Verifying chrony daemon is synchronized with at least one external time
  source ...FAILED
  rac01: PRVG-13605 : Query of chrony daemon for its time sources could not
         be performed on node "rac01".
         PRVG-2043 : Command "/usr/bin/chronyc sources " failed on node
         "rac01" and produced the following output:
         506 Cannot talk to daemon

  rac02: PRVG-13605 : Query of chrony daemon for its time sources could not
            be performed on node "rac02".
            PRVG-2043 : Command "/usr/bin/chronyc sources " failed on node
            "rac02" and produced the following output:
            506 Cannot talk to daemon

Manually test the status of service chronyd,

[root@rac01]# systemctl status chronyd
● chronyd.service - NTP client/server
   Loaded: loaded (/usr/lib/systemd/system/chronyd.service; enabled; vendor preset: enabled)
   Active: active (running) since Wed 2022-02-02 17:10:33 EST; 22s ago
     Docs: man:chronyd(8)
           man:chrony.conf(5)
  Process: 43769 ExecStartPost=/usr/libexec/chrony-helper update-daemon (code=exited, status=0/SUCCESS)
  Process: 43764 ExecStart=/usr/sbin/chronyd $OPTIONS (code=exited, status=0/SUCCESS)
 Main PID: 43768 (chronyd)
    Tasks: 1 (limit: 3355442)
   Memory: 376.0K
   CGroup: /system.slice/chronyd.service
           └─43768 /usr/sbin/chronyd

[root@rac01 ~]# chronyc sources
210 Number of sources = 4
MS Name/IP address         Stratum Poll Reach LastRx Last sample
===============================================================================
^? ts1.lab.dbaplus.ca            2   6     3    18   -166us[ -166us] +/-   17ms
^? ts2.lab.dbaplus.ca            2   6     3    17   +427us[ +427us] +/-   25ms

[root@rac01 ~]# su - grid
Last login: Wed Feb  2 20:11:48 EST 2022 on pts/1

[grid@rac01]$ chronyc sources
506 Cannot talk to daemon

The service "chronyd" is running and user root can run command "chronyc sources" successfully, and the command list all the time sources that chronyd is accessing. The sources are servers ts1.lab.dbaplus.ca and ts2.lab.dbaplus.ca.

However Grid Infrastructure (GI) owner grid cannot run the command, failed with message "506 Cannot talk to daemon".

The reason is that the system is stopping chrony daemon from listening on the UDP port 323 for management connections made by chronyc. This option is set by adding following line in chrony configuration file "/etc/chrony.conf"

cmdport 0

This line can be commented out (add # at the beginging of the line) to enable chronyc management connctions. The line as following

# cmdport 0

After saving the file "/etc/chrony.conf", restart chronyd service

systemctl restart chronyd

Now, normal user (non root) grid can run "chronyc sources" successfully.