Datapump is not starting. WARNING OGG-01221 Connect failed to 10.10.51.38:7819, error 111:Connection refused.

Datapump is not starting

OGG Report showing:
WARNING OGG-01221  Connect failed to 10.20.30.40:7819, error 111:Connection refused.

What to check
=====================
Site A

=====================
oracle@qa-1-dbs-1b>% lsof -i TCP | grep 7819
extract    6917 oracle   17u  IPv4 4553207      0t0  TCP qa-1-dbs-1b:30163->qa-2-dbs-01:7819 (ESTABLISHED)
server    19087 oracle    6u  IPv4 4535342      0t0  TCP *:7819 (LISTEN)
server    19087 oracle   14u  IPv4 4533293      0t0  TCP qa-1-ora-01:7819->qa-2-dbs-01:51406 (ESTABLISHED)

oracle@qa-1-dbs-1b>% lsof -i TCP | grep 51406
server    19087 oracle   14u  IPv4 4533293      0t0  TCP qa-1-ora-01:7819->qa-2-dbs-01:51406 (ESTABLISHED)

oracle@qa-1-dbs-1b>% ps -ef | grep 19087
oracle   19087 15558  0 19:46 ?        00:00:01 ./server -w 300 -p 7819-12818 -m 7809 -k -l /software/ogg/191/ggserr.log

oracle@qa-1-dbs-1b>% ps -ef | grep 6917
oracle    6917 15558  0 19:52 ?        00:00:00 /software/ogg/191/extract PARAMFILE /software/ogg/191/dirprm/dpm_i_01.prm REPORTFILE /software/ogg/191/dirrpt/DPM_I_01.rpt PROCESSID DPM_I_01
oracle    7124  6917  0 19:52 ?        00:00:00 oracleigt (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

=====================
Site B

=====================
oracle@qa-2-dbs-01>% lsof -i TCP | grep 7819
extract   70353 oracle   17u  IPv4 1204012      0t0  TCP qa-2-dbs-01:51406->qa-1-ora-01:7819 (ESTABLISHED)
server    70832 oracle    6u  IPv4 1208335      0t0  TCP *:7819 (LISTEN)
server    70832 oracle   14u  IPv4 1208369      0t0  TCP qa-2-dbs-01:7819->qa-1-dbs-1b:30163 (ESTABLISHED)

oracle@qa-2-dbs-01>% lsof -i TCP | grep 30163
server    70832 oracle   14u  IPv4 1208369      0t0  TCP qa-2-dbs-01:7819->qa-1-dbs-1b:30163 (ESTABLISHED)

oracle@qa-2-dbs-01>% ps -ef | grep 70832
oracle     70832    2703  0 19:52 ?        00:00:00 ./server -w 300 -p 7819-12818 -m 7809 -k -l /software/ogg/191/ggserr.log

oracle@qa-2-dbs-01>% ps -ef | grep 70353
oracle     70353    2703  0 19:46 ?        00:00:01 /software/ogg/191/extract PARAMFILE /software/ogg/191/dirprm/dpm_i_01.prm REPORTFILE /softwar/ogg/191/dirrpt/DPM_I_01.rpt PROCESSID DPM_I_01
oracle     70358   70353  0 19:46 ?        00:00:00 oracleigt (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

What to check

The server process is running with parameters:

./server -w 300 -p 7819-12818 -m 7809 -k -l /software/ogg/191/ggserr.log

oracle@qa-2-dbs-01>% ls -l /software/ogg/191/ggserr.log

-rw-r----- 1 oracle dba 3337659 May 19 20:01 /software/ogg/191/ggserr.log

File /software/ogg/191/ggserr.log must be owned by oracle, else the server process would not start.

Oracle Encryption - Encrypt data on storage with wallet

 --Specify location for wallet root

ALTER SYSTEM SET WALLET_ROOT='$ORACLE_BASE/admin/igt/wallet' SCOPE=SPFILE;

Restart Oracle is required here

SHUTDOWN IMMEDIATE;

STARTUP;

SHOW PARAMETER WALLET_ROOT

--Specify keystore type

ALTER SYSTEM SET TDE_CONFIGURATION="KEYSTORE_CONFIGURATION=FILE" SCOPE=BOTH;

SHOW PARAMETER TDE_CONFIGURATION

SELECT con_id, keystore_mode FROM V$ENCTYPTION_WALLET;

Auto-Login Keystore         - can be opened from a remote servers

Local Auto-Login Keystore   - can be opened from a local server

Password-Protected Keystore - can be opened only after providing a password

ADMINISTER KEY MANAGEMENT CREATE KEYSTORE IDENTIFIED BY Abcd@2026;

This will create an ewallet.p12 file under $ORACLE_BASE/admin/igt/wallet/TDE

SELECT wrl_type, wrl_parameter, status, con_id
FROM V$ENCTYPTION_WALLET;

At this point, password walled is closed

Need manually to open password protected keystore.

ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY Abcd@2026;

SELECT con_id, keystore_mode, status FROM V$ENCTYPTION_WALLET;

Status should be OPEN_NO_MASTER_KEY

Now, create Master Key

ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY Abcd@2026 WITH BACKUP;

SELECT masterkey_activated FROM V$DATABASE_KEY_INFO

Should be YES

Now, Alter key to be auatologin

ADMINISTER KEY MANAGEMENT CREATE auto_login keystore FROM KEYSTORE '$ORACLE_BASE/admin/igt/wallet/tde' IDENTIFIED BY Abcd@2026;

Configuration completed!

====================

How to use:

====================

====================

Encrypt Tablespace

====================

CREATE TABLESPACE IGT_ENC_TBS DATAFILE '/some/patn/to/datafile/encypted_tbs.dbf' SIZE 10M AUTOEXTEND ON MAXSIZE 10000M;

ALTER TABLESPACE IGT_ENC_TBS ENCRYPTIONONLINE ENCRYPT;

SELECT tablespace_name, status, encrypted FROM DBA_TABLESPACES;

Deafult Encryption is AES192. Salt and MAC are added by default

Salt - random string added before encryption. More strength to encryption. required additional 16 bytes for each encrypted data value. Cannot be used on indexed columns.

MAC  - Message Authentication Code. Used for Data Integrity checking. Adds additional 20 bytes for each encrypted data value.

Encrypt a column

CREATE TABLE TOKEN_USAGE 

(id          NUMBER,

 usage_name  VARCHAR2(100),

 usage_value NUMBER ENCRYPT;

);

ALTER TABLE TOKEN_USAGE ADD (CONSTRAINT TOKEN_USAGE_PK PRIMARY KEY  (id) USING INDEX TABLESPACE IGT_INDEX;

INSERT INTO TOKEN_USAGE (id, usage_name, usage_value) VALUES (1,'Usage Name',20);

Oracle Encryption - encrypt data

Permissions on Oracle package DBMS_CRYPTO
========================================

GRANT EXECUTE ON DBMS_CRYPTO TO TEST_USER;

========================================

Create a wrapper package.
p_text - is the encrypted value

p_key - a key passed from application
The encrypted value - is stored in database instead of the real value

The key - should be stored outside of the database.
========================================

CREATE OR REPLACE PACKAGE DBMS_CRYPT AS

  FUNCTION encrypt_text (p_text IN VARCHAR2, 

                         p_key  IN VARCHAR2) RETURN VARCHAR2;

 

  FUNCTION decrypt_text (p_text IN VARCHAR2, 

                         p_key  IN VARCHAR2) RETURN VARCHAR2;

END DBMS_CRYPT;

/

========================================

CREATE OR REPLACE PACKAGE BODY DBMS_CRYPT AS

FUNCTION encrypt_text (p_text IN VARCHAR2,

                       p_key  IN VARCHAR2) RETURN VARCHAR2 IS

 v_return_value  VARCHAR2(1000);

BEGIN

  SELECT DBMS_CRYPTO.encrypt(UTL_RAW.cast_to_raw(p_text), 4353, UTL_RAW.cast_to_raw(p_key))

  INTO v_return_value

  FROM DUAL;

  RETURN v_return_value;

END;

FUNCTION decrypt_text (p_text IN VARCHAR2,

                       p_key  IN VARCHAR2) RETURN VARCHAR2 IS

 v_return_value  VARCHAR2(1000);

BEGIN

  SELECT UTL_RAW.cast_to_varchar2(SYS.DBMS_CRYPTO.decrypt(p_text, 4353, UTL_RAW.cast_to_raw(p_key)))

  INTO v_return_value

  FROM DUAL;

  RETURN v_return_value;

END;

END DBMS_CRYPT;

/

Usage 
========================================

SELECT DBMS_CRYPT.encrypt_text(p_text => TO_CHAR(1234), p_key => 'Apr2026@Tomia') FROM DUAL;

SELECT DBMS_CRYPT.decrypt_text(p_text => 'B067B800BC18271C', p_key => 'Apr2026@Tomia') FROM DUAL;

Usage with table
========================================

CREATE TABLE TOKEN_USAGE 

(id               NUMBER,

 usage_name       VARCHAR2(100),

 usage_value      VARCHAR2(1000),

 ts_last_modified DATE DEFAULT SYSDATE

) TABLESPACE IGT_TABLE;

ALTER TABLE TOKEN_USAGE ADD CONSTRAINT TOKEN_USAGE_PK PRIMARY KEY  (id) USING INDEX TABLESPACE IGT_INDEX;

INSERT INTO TOKEN_USAGE (id, usage_name, usage_value) VALUES (1,'Tomia Chat Bot',DBMS_CRYPT.encrypt_text(p_text => TO_CHAR(1234), p_key => 'Apr2026@Tomia'));

SELECT id, usage_name, usage_value FROM TOKEN_USAGE;

        ID USAGE_NAME     USAGE_VALUE

---------- -------------- -----------------

         1 Tomia Chat Bot B067B800BC18271C

SELECT id, usage_name, DBMS_CRYPT.decrypt_text(p_text => usage_value, p_key => 'Apr2026@Tomia') as usage_value, ts_last_modified FROM TOKEN_USAGE

WHERE id=1;S

        ID USAGE_NAME     USAGE_VALUE

---------- -------------- -----------------

         1 Tomia Chat Bot 1234