Donghua's Blog - DBAGlobe

Sunday, July 17, 2022

Install PostgreSQL 15 beta version on RHEL8/CentOS8

Step 1: Modify existing `/etc/yum.repos.d/pgdg-redhat-all.repo` to include beta release for v15. Replace the `enable=0` to `enabled=1`.

[pgdg15-updates-testing]
name=PostgreSQL 15 for RHEL / Rocky $releasever - $basearch - Updates testing
baseurl=https://download.postgresql.org/pub/repos/yum/testing/15/redhat/rhel-$releasever-$basearch
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
repo_gpgcheck = 1

If you haven't installed the PostgreSQL Yum Repo yet, execute following command to install it for your OS version. Choose a different link if your are not using RHEL 8/CentOS 8. (https://www.postgresql.org/download/linux/redhat/)

yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm

Step 2: Install PostgreSQL Server and Client

Get list of enabled YUM repositories.

yum repolist

[root@9cced9bedc1c ~]# yum repolist
repo id                                       repo name
appstream                                     CentOS Linux 8 - AppStream
baseos                                        CentOS Linux 8 - BaseOS
extras                                        CentOS Linux 8 - Extras
pgdg-common                                   PostgreSQL common RPMs for RHEL / Rocky 8 - x86_64
pgdg14                                        PostgreSQL 14 for RHEL / Rocky 8 - x86_64
pgdg15-updates-testing                        PostgreSQL 15 for RHEL / Rocky 8 - x86_64 - Updates testing

List available packages in pgdg15-updates-testing Repo.

# yum --disablerepo="*" --enablerepo="pgdg15-updates-testing" list available

[root@9cced9bedc1c ~]# yum --disablerepo="*" --enablerepo="pgdg15-updates-testing" list available
Last metadata expiration check: 0:02:57 ago on Sun 17 Jul 2022 07:10:56 AM UTC.
Available Packages
postgis33_15.x86_64                        3.3.0-beta2_1.rhel8                 pgdg15-updates-testing
postgis33_15-client.x86_64                 3.3.0-beta2_1.rhel8                 pgdg15-updates-testing
postgis33_15-devel.x86_64                  3.3.0-beta2_1.rhel8                 pgdg15-updates-testing
postgis33_15-docs.x86_64                   3.3.0-beta2_1.rhel8                 pgdg15-updates-testing
postgis33_15-gui.x86_64                    3.3.0-beta2_1.rhel8                 pgdg15-updates-testing
postgis33_15-utils.x86_64                  3.3.0-beta2_1.rhel8                 pgdg15-updates-testing
postgresql15.x86_64                        15.0-beta2_3PGDG.rhel8              pgdg15-updates-testing
postgresql15-contrib.x86_64                15.0-beta2_3PGDG.rhel8              pgdg15-updates-testing
postgresql15-devel.x86_64                  15.0-beta2_3PGDG.rhel8              pgdg15-updates-testing
postgresql15-docs.x86_64                   15.0-beta2_3PGDG.rhel8              pgdg15-updates-testing
postgresql15-libs.x86_64                   15.0-beta2_3PGDG.rhel8              pgdg15-updates-testing
postgresql15-llvmjit.x86_64                15.0-beta2_3PGDG.rhel8              pgdg15-updates-testing
postgresql15-plperl.x86_64                 15.0-beta2_3PGDG.rhel8              pgdg15-updates-testing
postgresql15-plpython3.x86_64              15.0-beta2_3PGDG.rhel8              pgdg15-updates-testing
postgresql15-pltcl.x86_64                  15.0-beta2_3PGDG.rhel8              pgdg15-updates-testing
postgresql15-server.x86_64                 15.0-beta2_3PGDG.rhel8              pgdg15-updates-testing
postgresql15-test.x86_64                   15.0-beta2_3PGDG.rhel8              pgdg15-updates-testing

Install PostgreSQL Server and Client

yum --disablerepo="*" --enablerepo="pgdg15-updates-testing" -y install postgresql15-server.x86_64 postgresql15.x86_64 postgresql15-contrib.x86_64

[root@9cced9bedc1c ~]# yum --disablerepo="*" --enablerepo="pgdg15-updates-testing" -y install postgresql15-server.x86_64 postgresql15.x86_64 postgresql15-contrib.x86_64
Last metadata expiration check: 0:03:57 ago on Sun 17 Jul 2022 07:10:56 AM UTC.
Dependencies resolved.
=====================================================================================================
 Package                   Arch        Version                     Repository                   Size
=====================================================================================================
Installing:
 postgresql15              x86_64      15.0-beta2_3PGDG.rhel8      pgdg15-updates-testing      1.8 M
 postgresql15-contrib      x86_64      15.0-beta2_3PGDG.rhel8      pgdg15-updates-testing      771 k
 postgresql15-server       x86_64      15.0-beta2_3PGDG.rhel8      pgdg15-updates-testing      6.8 M
Installing dependencies:
 postgresql15-libs         x86_64      15.0-beta2_3PGDG.rhel8      pgdg15-updates-testing      311 k

Transaction Summary
=====================================================================================================
Install  4 Packages

Total download size: 9.7 M
Installed size: 39 M
Downloading Packages:
(1/4): postgresql15-libs-15.0-beta2_3PGDG.rhel8.x86_64.rpm           169 kB/s | 311 kB     00:01
(2/4): postgresql15-contrib-15.0-beta2_3PGDG.rhel8.x86_64.rpm        354 kB/s | 771 kB     00:02
(3/4): postgresql15-15.0-beta2_3PGDG.rhel8.x86_64.rpm                706 kB/s | 1.8 MB     00:02
(4/4): postgresql15-server-15.0-beta2_3PGDG.rhel8.x86_64.rpm         4.2 MB/s | 6.8 MB     00:01
-----------------------------------------------------------------------------------------------------
Total                                                                2.8 MB/s | 9.7 MB     00:03
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                                             1/1
  Installing       : postgresql15-libs-15.0-beta2_3PGDG.rhel8.x86_64                             1/4
  Running scriptlet: postgresql15-libs-15.0-beta2_3PGDG.rhel8.x86_64                             1/4
  Installing       : postgresql15-15.0-beta2_3PGDG.rhel8.x86_64                                  2/4
  Running scriptlet: postgresql15-15.0-beta2_3PGDG.rhel8.x86_64                                  2/4
  Running scriptlet: postgresql15-server-15.0-beta2_3PGDG.rhel8.x86_64                           3/4
  Installing       : postgresql15-server-15.0-beta2_3PGDG.rhel8.x86_64                           3/4
  Running scriptlet: postgresql15-server-15.0-beta2_3PGDG.rhel8.x86_64                           3/4
  Installing       : postgresql15-contrib-15.0-beta2_3PGDG.rhel8.x86_64                          4/4
  Running scriptlet: postgresql15-contrib-15.0-beta2_3PGDG.rhel8.x86_64                          4/4
  Verifying        : postgresql15-15.0-beta2_3PGDG.rhel8.x86_64                                  1/4
  Verifying        : postgresql15-contrib-15.0-beta2_3PGDG.rhel8.x86_64                          2/4
  Verifying        : postgresql15-libs-15.0-beta2_3PGDG.rhel8.x86_64                             3/4
  Verifying        : postgresql15-server-15.0-beta2_3PGDG.rhel8.x86_64                           4/4

Installed:
  postgresql15-15.0-beta2_3PGDG.rhel8.x86_64      postgresql15-contrib-15.0-beta2_3PGDG.rhel8.x86_64
  postgresql15-libs-15.0-beta2_3PGDG.rhel8.x86_64 postgresql15-server-15.0-beta2_3PGDG.rhel8.x86_64

Complete!

Alternatively, you can manually install PostgreSQL RPM files used for beta testing here: https://download.postgresql.org/pub/repos/yum/testing/15/redhat/rhel-8-x86_64/

Step 3: Create Database

/usr/pgsql-15/bin/postgresql-15-setup initdb

[root@9cced9bedc1c ~]# /usr/pgsql-15/bin/postgresql-15-setup initdb
Initializing database ... OK

Step 4: Enable Autostart

systemctl enable postgresql-15
systemctl start postgresql-15

[root@9cced9bedc1c ~]# systemctl status postgresql-15.service
● postgresql-15.service - PostgreSQL 15 database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql-15.service; enabled; vendor preset: disabled)
   Active: active (running) since Sun 2022-07-17 07:22:40 UTC; 1min 20s ago
     Docs: https://www.postgresql.org/docs/15/static/
  Process: 3901 ExecStartPre=/usr/pgsql-15/bin/postgresql-15-check-db-dir ${PGDATA} (code=exited, st>
 Main PID: 3906 (postmaster)
    Tasks: 7 (limit: 48480)
   Memory: 19.4M
   CGroup: /system.slice/postgresql-15.service
           ├─3906 /usr/pgsql-15/bin/postmaster -D /var/lib/pgsql/15/data/
           ├─3909 postgres: logger
           ├─3910 postgres: checkpointer
           ├─3911 postgres: background writer
           ├─3913 postgres: walwriter
           ├─3914 postgres: autovacuum launcher
           └─3915 postgres: logical replication launcher

Jul 17 07:22:39 9cced9bedc1c.mylabserver.com systemd[1]: Starting PostgreSQL 15 database server...
Jul 17 07:22:40 9cced9bedc1c.mylabserver.com postmaster[3906]: 2022-07-17 07:22:40.054 UTC [3906] LO>
Jul 17 07:22:40 9cced9bedc1c.mylabserver.com postmaster[3906]: 2022-07-17 07:22:40.054 UTC [3906] HI>
Jul 17 07:22:40 9cced9bedc1c.mylabserver.com systemd[1]: Started PostgreSQL 15 database server.

Step 5: Verify Server via `psql` client.

[postgres@9cced9bedc1c log]$ psql
psql (15beta2)
Type "help" for help.

postgres=# select version();
                                                  version

-----------------------------------------------------------------------------------------------------
-------
 PostgreSQL 15beta2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10),
64-bit
(1 row)

Wednesday, July 13, 2022

Use Java Stored Procedure in RDS for Oracle 19c

1. Prepare Oracle database, make sure JVM option installed

2. Prepare Testing Data

create table t (id int,last_update timestamp);
insert into t values(1,CURRENT_TIMESTAMP(0));
commit;

3. Prepare Java Code Example

import java.sql.*;
import java.io.*;
import oracle.jdbc.*;

public class DemoJava
{
    public static void PrintTable () throws SQLException 
    {
      String sql = "SELECT TO_CHAR(id)||', '||TO_CHAR(last_update) FROM t";
      try
      {
        Connection conn = DriverManager.getConnection("jdbc:default:connection:");
        PreparedStatement pstmt = conn.prepareStatement(sql);
        ResultSet rset = pstmt.executeQuery();
        rset.next();
        System.out.println(rset.getString(1));
        rset.close();
        pstmt.close();
      }
      catch (SQLException e)
      {
        System.err.println(e.getMessage());
      }
    }

    public static void ListFile(String myfolder) {
      File dir = new File(myfolder);
      String[] children = dir.list();
      
      if (children == null) {
         System.out.println( "Either dir does not exist or is not a directory");
      } else { 
         for (int i = 0; i< children.length; i++) {
            String filename = children[i];
            System.out.println(filename);
         }
      }
   }
}

4. Load Java Code into Oracle Database

loadjava -u admin/yourcomplexpassword@oracle-se2.cesu9tljidgs.us-east-1.rds.amazonaws.com/ORCL -v -r -t DemoJava.java

[oracle@OraceClient ~]$ loadjava -u admin/yourcomplexpassword@oracle-se2.cesu9tljidgs.us-east-1.rds.amazonaws.com/ORCL -v -r -t DemoJava.java
arguments: '-u' 'admin/***@oracle-se2.cesu9tljidgs.us-east-1.rds.amazonaws.com/ORCL' '-v' '-r' '-t' 'DemoJava.java' 
dropped  : JAVA$CLASS$MD5$TABLE
creating : source DemoJava
loading  : source DemoJava
resolving: source DemoJava
Classes Loaded: 0
Resources Loaded: 0
Sources Loaded: 1
Published Interfaces: 0
Classes generated: 0
Classes skipped: 0
Synonyms Created: 0
Errors: 0

5. Test 1: Use Java to query table

CREATE OR REPLACE PROCEDURE DemoJavaPrintTable
AS LANGUAGE JAVA
NAME 'DemoJava.PrintTable()';
/

SET SERVEROUTPUT ON
CALL dbms_java.set_output(2000);
CALL DemoJavaPrintTable();

SQL> SQL> SET SERVEROUTPUT ON
SQL> 
SQL> CALL dbms_java.set_output(2000);

Call completed.

SQL> CALL DemoJavaPrintTable();
1, 13-JUL-22 10.20.14.000000 AM

Call completed.

6. Test 2: Use Java to to access file system

CREATE OR REPLACE PROCEDURE DemoJavaListFile (myfolder varchar2)
AS LANGUAGE JAVA
NAME 'DemoJava.ListFile(java.lang.String)';
/

SET SERVEROUTPUT ON
CALL dbms_java.set_output(2000);
CALL DemoJavaListFile('/rdsdbdata/log/diag/rdbms/');

SQL> CALL DemoJavaListFile('/rdsdbdata/log/diag/rdbms/');
Exception in thread "Root Thread" java.security.AccessControlException: the
Permission ("java.io.FilePermission" "/rdsdbdata/log/diag/rdbms" "read") has not
been granted to ADMIN. The PL/SQL to grant this is dbms_java.grant_permission(
'ADMIN', 'SYS:java.io.FilePermission', '/rdsdbdata/log/diag/rdbms', 'read' )
        at
java.security.AccessControlContext.checkPermission(AccessControlContext.java)
        at java.security.AccessController.checkPermission(AccessController.java:928)
        at java.lang.SecurityManager.checkPermission(SecurityManager.java:551)
        at
oracle.aurora.rdbms.SecurityManagerImpl.checkPermission(SecurityManagerImpl.java
:210)
        at java.lang.SecurityManager.checkRead(SecurityManager.java:890)
        at java.io.File.normalizedList(File.java:1110)
        at java.io.File.list(File.java:1159)
        at DemoJava.ListFile(DemoJava:29)
CALL DemoJavaListFile('/rdsdbdata/log/diag/rdbms/')
     *
ERROR at line 1:
ORA-29532: Java call terminated by uncaught Java exception:
java.security.AccessControlException: the Permission ("java.io.FilePermission"
"/rdsdbdata/log/diag/rdbms" "read") has not been granted to ADMIN. The PL/SQL
to grant this is dbms_java.grant_permission( 'ADMIN',
'SYS:java.io.FilePermission', '/rdsdbdata/log/diag/rdbms', 'read' )

7. Troubleshooting

To find out what java permissions you have been granted in RDS for Oracle for Java, use following query:

SELECT * FROM dba_java_policy
  WHERE grantee IN ('RDS_JAVA_ADMIN', 'PUBLIC')
  AND enabled = 'ENABLED'
  ORDER BY type_name, name, grantee;

Make sure you have installed/added JVM option to the database, it supports both Oracle EE and SE2.

[oracle@OraceClient ~]$ loadjava -u admin/yourcomplexpassword@oracle-se2.cesu9tljidgs.us-east-1.rds.amazonaws.com/ORCL -v -r -t DemoJava.java
arguments: '-u' 'admin/***@oracle-se2.cesu9tljidgs.us-east-1.rds.amazonaws.com/ORCL' '-v' '-r' '-t' 'DemoJava.java' 
created  : JAVA$CLASS$MD5$TABLE
creating : source DemoJava
loading  : source DemoJava
created  : CREATE$JAVA$LOB$TABLE
Error while creating source DemoJava
    ORA-06550: line 1, column 75:
PLS-00201: identifier 'DBMS_JAVA.SET_COMPILER_OPTION' must be declared
ORA-06550: line 1, column 75:
PL/SQL: Statement ignored
ORA-06550: line 1, column 239:
PLS-00201: identifier 'NameFromLastDDL' must be declared
ORA-06550: line 1, column 231:
PL/SQL: Statement ignored

Error while computing shortname of DemoJava
    ORA-00942: table or view does not exist

The following operations failed
    source DemoJava: creation (createFailed)
exiting  : Failures occurred during processing

Sunday, July 3, 2022

B-Tree index deduplication enabled by default since PostgreSQL 13

PostgreSQL B-Tree indexes are multi-level tree structures, where each level of the tree can be used as a doubly-linked list of pages. Each leaf page contains tuples that point to table rows.

A new feature introduced in PostgreSQL 13 called "deduplication". A duplicate is a leaf page tuple (a tuple that points to a table row) where all indexed key columns have values that match corresponding column values from at least one other leaf page tuple in the same index. More information refers to official documentation: https://www.postgresql.org/docs/13/btree-implementation.html#BTREE-DEDUPLICATION

Use "deduplicate_items" to explicitly disable deduplicate for write-heavy workload with no duplication to avoid performance overheads associated with de-duplication logic, which is default to enabled.

Demo Setup

create table salesorder (
    id          integer, 
    salerep     varchar(10), 
    order_date  date,
    amount numeric(10,2)
);
insert into salesorder 
select generate_series, 
       'salesrep'||mod(generate_series,100),
        concat(generate_series, ' day')::interval+current_date,
        round((random()*100)::numeric,2)
from generate_series(1, 1000000);
vacuum analyze salesorder;

Create indexes

create index id_dedup_on on salesorder(id);
create index id_dedup_off on salesorder(id)  WITH (deduplicate_items = off);
create index salerep_dedup_on on salesorder(salerep);
create index salerep_dedup_off on salesorder(salerep)  WITH (deduplicate_items = off);

Compare table and index sizes

mytest=> \d+
                                     List of relations
 Schema |    Name    | Type  |  Owner   | Persistence | Access method | Size  | Description
--------+------------+-------+----------+-------------+---------------+-------+-------------
 public | salesorder | table | postgres | permanent   | heap          | 57 MB |
(1 row)

mytest=> \di+
                                                List of relations
 Schema |       Name        | Type  |  Owner   |   Table    | Persistence | Access method |  Size   | Description
--------+-------------------+-------+----------+------------+-------------+---------------+---------+-------------
 public | id_dedup_off      | index | postgres | salesorder | permanent   | btree         | 21 MB   |
 public | id_dedup_on       | index | postgres | salesorder | permanent   | btree         | 21 MB   |
 public | salerep_dedup_off | index | postgres | salesorder | permanent   | btree         | 30 MB   |
 public | salerep_dedup_on  | index | postgres | salesorder | permanent   | btree         | 6912 kB |
(4 rows)

Tuesday, June 21, 2022

Automatic start observer during instance startup/reboot

Observer plays critical role during automatic failover, but sometimes routine maintenance would restart the Linux instance running observer and causes observer in stopped after instance reboot. Here are scripts to start observer automatically during Linux OS startup.

Running observer in background requires Oracle wallet setup, refer to below 2 links for more details:

/home/oracle/start_observer.sh

#!/bin/bash

export PATH

export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/client
export LD_LIBRARY_PATH=/u01/lib
export PATH=/u01/sqlcl/bin:$ORACLE_HOME/bin:$PATH

ObserverRunning=`ps -ef|grep dgmgrl|grep -v grep|wc -l`
# echo $ObserverRunning
if [[ $ObserverRunning -eq 1 ]]
then
         echo "Observer is already running"
else
dgmgrl  <<EOD
start observer in background logfile is '/tmp/observer.log' connect identifier is rds_custom_orcl_a;
EOD
fi

/etc/rc.local

#!/bin/bash
# THIS FILE IS ADDED FOR COMPATIBILITY PURPOSES
#
# It is highly advisable to create own systemd services or udev rules
# to run scripts during boot instead of using this file.
#
# In contrast to previous versions due to parallel execution during boot
# this script will NOT be run after all other services.
#
# Please note that you must run 'chmod +x /etc/rc.d/rc.local' to ensure
# that this script will be executed during boot.

touch /var/lock/subsys/local

su - oracle -c "nohup /home/oracle/start_observer.sh &"

Make sure change these scripts to be *executable".

chmod +x /home/oracle/start_observer.sh
chmod +x /etc/rc.local

pyexpat.cpython-36m-x86_64-linux-gnu.so: undefined symbol: XML_SetHashSalt

Symptom:

Command line "aws cli" failed with error /usr/lib64/python3.6/lib-dynload/pyexpat.cpython-36m-x86_64-linux-gnu.so: undefined symbol: XML_SetHashSalt.

-bash-4.2$ aws s3 ls
Traceback (most recent call last):
  File "/usr/local/bin/aws", line 27, in <module>
    sys.exit(main())
  File "/usr/local/bin/aws", line 23, in main
    return awscli.clidriver.main()
  File "/usr/local/lib/python3.6/site-packages/awscli/clidriver.py", line 69, in main
    driver = create_clidriver()
  File "/usr/local/lib/python3.6/site-packages/awscli/clidriver.py", line 79, in create_clidriver
    event_hooks=session.get_component('event_emitter'))
  File "/usr/local/lib/python3.6/site-packages/awscli/plugin.py", line 44, in load_plugins
    modules = _import_plugins(plugin_mapping)
  File "/usr/local/lib/python3.6/site-packages/awscli/plugin.py", line 61, in _import_plugins
    module = __import__(path, fromlist=[module])
  File "/usr/local/lib/python3.6/site-packages/awscli/handlers.py", line 43, in <module>
    from awscli.customizations.history import register_history_mode
  File "/usr/local/lib/python3.6/site-packages/awscli/customizations/history/__init__.py", line 28, in <module>
    from awscli.customizations.history.show import ShowCommand
  File "/usr/local/lib/python3.6/site-packages/awscli/customizations/history/show.py", line 16, in <module>
    import xml.parsers.expat
  File "/usr/lib64/python3.6/xml/parsers/expat.py", line 4, in <module>
    from pyexpat import *
ImportError: /usr/lib64/python3.6/lib-dynload/pyexpat.cpython-36m-x86_64-linux-gnu.so: undefined symbol: XML_SetHashSalt

Troubleshooting:

Check the dependencies for pyexpat.cpython-36m-x86_64-linux-gnu.so:

-bash-4.2$ ldd /usr/lib64/python3.6/lib-dynload/pyexpat.cpython-36m-x86_64-linux-gnu.so
        linux-vdso.so.1 =>  (0x00007fffa67fc000)
        libexpat.so.1 => /lib64/libexpat.so.1 (0x00007f16058bb000)
        libpython3.6m.so.1.0 => /lib64/libpython3.6m.so.1.0 (0x00007f1605394000)
        libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f1605178000)
        libc.so.6 => /lib64/libc.so.6 (0x00007f1604daa000)
        libdl.so.2 => /lib64/libdl.so.2 (0x00007f1604ba6000)
        libutil.so.1 => /lib64/libutil.so.1 (0x00007f16049a3000)
        libm.so.6 => /lib64/libm.so.6 (0x00007f16046a1000)
        /lib64/ld-linux-x86-64.so.2 (0x00007f1605cf4000)

Check the suspicious file, found multiple of it in the LD_LIBRARY_PATH:

$ find / -name libexpat.so.1 2>/dev/null
/usr/lib64/libexpat.so.1
/rdsdbbin/oracle.12.1.custom.r1.EE.1/lib/libexpat.so.1

Solution:

Add /usr/lib64/ to the beginning of LD_LIBRARY_PATH:

export LD_LIBRARY_PATH=/usr/lib64/:$LD_LIBRARY_PATH

Saturday, May 28, 2022

DBCA failed with error "ORA-04062: signature of package "SYS.DBMS_BACKUP_RESTORE" has been changed"

Symptom during DBCA on OACLE_HOME with RU/PSU installed

46% complete
100% complete
[FATAL] Error while restoring PDB backup piece

-- from $ORACLE_BASE/cfgtoollogs/dbca/ORCLCDB/trace.log_xxxxx
[Thread-244] [ 2022-05-28 08:31:49.022 UTC ] [RMANUtil$RMANUtilErrorListener.handleError:1386]  ERROR=RMAN-03002: failure of restore command at 05/28/2022 08:31:48
[Thread-244] [ 2022-05-28 08:31:49.022 UTC ] [RMANUtil$RMANUtilErrorListener.handleError:1386]  ERROR=ORA-04062: signature of package "SYS.DBMS_BACKUP_RESTORE" has been changed
[Thread-244] [ 2022-05-28 08:31:49.022 UTC ] [RMANUtil$RMANUtilErrorListener.handleError:1386]  ERROR=RMAN>
[Thread-244] [ 2022-05-28 08:31:49.022 UTC ] [RMANUtil$RMANUtilErrorListener.handleError:1386]  ERROR=echo set on

Root Cause

ORA-04062 on "SYS.DBMS_BACKUP_RESTORE" because datapatch failed, which supposes to recreate RMAN packages based on installed RU/PSU patches.

[progressPage.flowWorker] [ 2022-05-28 08:57:16.357 UTC ] [CloneDBCreationStep.executeImpl:836]  executing datapatch /u01/db/OPatch/datapatch
[progressPage.flowWorker] [ 2022-05-28 08:57:16.357 UTC ] [CloneDBCreationStep.executeImpl:836]  executing datapatch /u01/db/OPatch/datapatch
[progressPage.flowWorker] [ 2022-05-28 08:57:25.369 UTC ] [CloneDBCreationStep.executeImpl:839]  datapatch returned with exit status 1

The cause for data patch failure is NOT able to query OPATCH

SQL> select * from OPATCH_XML_INV ;
ERROR:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-29400: data cartridge error
KUP-04095: preprocessor command /u01/db193/QOpatch/qopiprep.bat encountered
error "locale: Cannot set LC_CTYPE to default locale: No such file or directory"

Solution for my case

export LANG=en_US.utf-8
export LC_ALL=en_US.utf-8

Add these 2 lines into /etc/environment for permanently change:

LANG=en_US.utf-8
LC_ALL=en_US.utf-8

Donghua's Blog - DBAGlobe

Sunday, July 17, 2022

Install PostgreSQL 15 beta version on RHEL8/CentOS8

Step 1: Modify existing `/etc/yum.repos.d/pgdg-redhat-all.repo` to include beta release for v15. Replace the `enable=0` to `enabled=1`.

Step 2: Install PostgreSQL Server and Client

Step 3: Create Database

Step 4: Enable Autostart

Step 5: Verify Server via `psql` client.

Wednesday, July 13, 2022

Use Java Stored Procedure in RDS for Oracle 19c

1. Prepare Oracle database, make sure JVM option installed

2. Prepare Testing Data

3. Prepare Java Code Example

4. Load Java Code into Oracle Database

5. Test 1: Use Java to query table

6. Test 2: Use Java to to access file system

7. Troubleshooting

Sunday, July 3, 2022

B-Tree index deduplication enabled by default since PostgreSQL 13

Demo Setup

Create indexes

Compare table and index sizes

Tuesday, June 21, 2022

Automatic start observer during instance startup/reboot

pyexpat.cpython-36m-x86_64-linux-gnu.so: undefined symbol: XML_SetHashSalt

Symptom:

Troubleshooting:

Solution:

Saturday, May 28, 2022

DBCA failed with error "ORA-04062: signature of package "SYS.DBMS_BACKUP_RESTORE" has been changed"

Symptom during DBCA on OACLE_HOME with RU/PSU installed

Root Cause

ORA-04062 on "SYS.DBMS_BACKUP_RESTORE" because datapatch failed, which supposes to recreate RMAN packages based on installed RU/PSU patches.

The cause for data patch failure is NOT able to query OPATCH

Solution for my case

Disclaimer

Labels

Blog Archive

Search This Website

Tweets by Me

Donghua's Blog - DBAGlobe

Sunday, July 17, 2022

Install PostgreSQL 15 beta version on RHEL8/CentOS8

Step 1: Modify existing /etc/yum.repos.d/pgdg-redhat-all.repo to include beta release for v15. Replace the enable=0 to enabled=1.

Step 2: Install PostgreSQL Server and Client

Step 3: Create Database

Step 4: Enable Autostart

Step 5: Verify Server via psql client.

Wednesday, July 13, 2022

Use Java Stored Procedure in RDS for Oracle 19c

1. Prepare Oracle database, make sure JVM option installed

2. Prepare Testing Data

3. Prepare Java Code Example

4. Load Java Code into Oracle Database

5. Test 1: Use Java to query table

6. Test 2: Use Java to to access file system

7. Troubleshooting

Sunday, July 3, 2022

B-Tree index deduplication enabled by default since PostgreSQL 13

Demo Setup

Create indexes

Compare table and index sizes

Tuesday, June 21, 2022

Automatic start observer during instance startup/reboot

pyexpat.cpython-36m-x86_64-linux-gnu.so: undefined symbol: XML_SetHashSalt

Symptom:

Troubleshooting:

Solution:

Saturday, May 28, 2022

DBCA failed with error "ORA-04062: signature of package "SYS.DBMS_BACKUP_RESTORE" has been changed"

Symptom during DBCA on OACLE_HOME with RU/PSU installed

Root Cause

ORA-04062 on "SYS.DBMS_BACKUP_RESTORE" because datapatch failed, which supposes to recreate RMAN packages based on installed RU/PSU patches.

The cause for data patch failure is NOT able to query OPATCH

Solution for my case

Disclaimer

Labels

Blog Archive

Search This Website

Tweets by Me

Step 1: Modify existing `/etc/yum.repos.d/pgdg-redhat-all.repo` to include beta release for v15. Replace the `enable=0` to `enabled=1`.

Step 5: Verify Server via `psql` client.