Friday, June 20, 2025

SSL Connection Fails with SSL routines:final_renegotiate:unsafe legacy renegotiation disabled

Problem

SSL connection fails with and error: 

SSL routines:final_renegotiate:unsafe legacy renegotiation disabled:ssl/statem/extensions.c:948

openssl is used

Solution

Save the below script as a file and use it when making an SSL connection:

openssl_conf = default_conf
[ default_conf ]
ssl_conf = ssl_sect
[ssl_sect]
system_default = ssl_default_sect
[ssl_default_sect]
Options = UnsafeLegacyRenegotiation

OPENSSL_CONF=/path/to/the/config/file/above <command>

 

Saturday, May 24, 2025

Oracle Database 19c -- Enable Tracing for an impdp Job

Problem

When an impdp job fails with an error, you want to identify the failing statement

Solution

Enable tracing for the impdp job with: 

SQL> ALTER SYSTEM SET EVENTS '14401 trace name errorstack level 3';
SQL> ALTER SYSTEM set MAX_DUMP_FILE_SIZE= unlimited ;
SQL> ALTER SYSTEM set TIMED_STATISTICS = true; 

Re-run the impdp job.

To disable tracing:

SQL> ALTER SYSTEM SET EVENTS '14401 trace name errorstack off';

 

Oracle Database 19c -- impdp fails with ORA-14401 inserted partition key is outside specified partition

Problem

Importing data into an Oracle Database 19c with impdp fails with the errors like:

ORA-31693: Table data object <table> failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-14401: inserted partition key is outside specified partition 

The table is an interval-range-partitioned table

Solution

Use the  DATA_OPTIONS=DISABLE_APPEND_HINT option of impdp

Friday, May 23, 2025

Oracle Restart 19 -- roothas.sh fails with CLSRSC-752: incorrect invocation of script

Problem

Deconfiguring (roothas.sh -deconfig -force) or configuring (roothas.sh) Oracle Restart might fails with the error like:

CLSRSC-752: incorrect invocation of script roothas.pl called on a Grid Infrastructure cluster node

Died at <GI HOME>/crs/install/crsutils.pm line <line_no>

Solution

A probable cause of the issue might be the leftover checkpoint file from the previous operations: 

$ORACLE_BASE/crsdata/<HOSTNAME>/crsconfig/ckptGridHA_<HOSTNAME>.xml

Remove, if exists, that file and try to deconfigure/configure again

Oracle Restart 19 -- Managing a Database Fails with PRCR-1055 : Cluster membership check failed

Problem

When using Oracle Restart, and trying to add, start, stop, or check status of an Oracle Database, you get the errors below:

$ srvctl status database -db <db_unique_name>
PRCD-1024 : Failed to retrieve instance list for database <db_unique_name>
PRCR-1055 : Cluster membership check failed for node <hostname>
 

Solution

Most probably the cause of the error is that the HOSTING_MEMBERS attribute is set:

# $GRID_HOME/grid/bin/crsctl stat res ora.<db_unique_name>.db -f | grep HOSTING_MEMBERS

HOSTING_MEMBERS=<some_hostname>

To fix the issue, reset the attribute:

# $GRID_HOME/grid/bin/crsctl modify resource "ora.<db_unique_name>.db" -attr "HOSTING_MEMBERS=" -unsupported

 

Oracle Restart 19 -- roothas.sh fails with CLSRSC-762: Empty site GUID for the local site name

Problem

After cloning Oracle Restart to another host, reconfiguration might fail with the errors: 

# $GRID_HOME/grid/crs/install/roothas.sh

Using configuration parameter file: /u01/app/19.0.0.0/grid/crs/install/crsconfig_params
The log of current session can be found at:
  /u01/app/grid/crsdata/<host>/crsconfig/roothas_2025-05-23_10-47-04AM.log
2025/05/23 10:47:06 CLSRSC-363: User ignored prerequisites during installation
2025/05/23 10:47:07 CLSRSC-762: Empty site GUID for the local site name <host>.
Died at /u01/app/19.0.0.0/grid/crs/install/crsutils.pm line 16443.
The command '/u01/app/19.0.0.0/grid/perl/bin/perl -I/u01/app/19.0.0.0/grid/perl/lib -I/u01/app/19.0.0.0/grid/crs/install -I/u01/app/19.0.0.0/grid/xag /u01/app/19.0.0.0/grid/crs/install/roothas.pl ' execution failed

Solution

The common reason for this error, that file 

$GRID_HOME/grid/crs/install/crsgenconfig_params

stores references for the older host, and reconfiguration is happening on the newer host

As a solution, remove the file and try configuration again:

# rm -f $GRID_HOME/grid/crs/install/crsgenconfig_params

 

Monday, October 14, 2024

Oracle Database 11g -- How to Quickly Reset RMAN Configuraiton

Problem

You want to reset RMAN configuration to its default state, quickly with one step

Solution

Use the below procedure:

SQL> exec dbms_backup_restore.resetConfig;

This will reset all RMAN configuration to their default values

 

Saturday, October 12, 2024

Oracle GolgenGate 21c -- Configure a CDB Database for GoldenGate Extract

Problem

You need to configure a CDB database for enabling a GoldenGate extract for replication

Solution

Perform the following actions on the source CDB database to prepare for a GoldenGate extract:

At CDB$ROOT:

CREATE USER c##ggadmin IDENTIFIED BY "<password>"
PROFILE <profile>
DEFAULT TABLESPACE SYSAUX
TEMPORARY TABLESPACE TEMP;

GRANT DBA to c##ggadmin;

EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE(GRANTEE => 'C##GGADMIN', CONTAINER => 'ALL' );

alter system set ENABLE_GOLDENGATE_REPLICATION=true scope=both;

alter system set STREAMS_POOL_SIZE=<#_of_extracts * 1>G scope=both; 

ALTER DATABASE FORCE LOGGING; 

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

At PDB level:

ALTER SESSION SET CONTAINER=<pdb>;

CREATE USER ggadmin IDENTIFIED BY "<password>"
PROFILE <profile>
DEFAULT TABLESPACE SYSAUX
TEMPORARY TABLESPACE TEMP;

GRANT DBA TO ggadmin;

EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE('ggadmin');

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

 

Thursday, April 25, 2024

Oracle GoldenGate 21c -- How Triggers Fired during Replication

Problem

You need a better understanding on how Oracle database triggers work with Oracle GoldenGate replication

Solution

By default in an Oracle database, triggers do fire only when a statement executed from a user process, and do not fire in case of Oracle-maintained processes, like Logical standby SQL Apply or Oracle Streams replication processes 

The property can be changed by using the procedure:

DBMS_DDL.SET_TRIGGER_FIRING_PROPERTY

When set to FALSE, triggers will fire during replication, that is, the statements issued by Replicat processes


Wednesday, April 10, 2024

OCI Autonomous Database -- How to Create a DB Link

Problem

You want to create a database link on an Autonomous Database in OCI (Oracle Cloud Infrastructure)

Solution

Use commands below to create a db link:

If db link being created in a schema other than ADMIN, grant required privileges:

SQL> grant execute on DBMS_CLOUD_ADMIN to <schema>;
SQL> grant CREATE DATABASE LINK to <schema>;

Create credential;

BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => '<credential_name>',
    username        => '<username>',
    password        => '<password>'
  );
END;

Create database link:

BEGIN
     DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK(
          db_link_name       => '<db_link_name>',
          hostname           => 'target_db_host_name',
          port               => '<port>',
          service_name       => '<service_name>',
          ssl_server_cert_dn => NULL,
          credential_name    => '<credential_name>',
          directory_name     => NULL,
          private_target     => true);
END;