Data Guard Redo Transport Encryption

Data Guard Redo Transport Encryption

There are cases when redo encryption is enabled and encryption must be enabled between primary and far sync instance as well as the far sync and other standby terminal databases, This is due to the redo being unencrypted when taken off the wire at the Far Sync instance.

Advanced Security Option network encryption has been available since Oracle version 7,  For example, enabling Advanced Encryption Standard (AES) encryption algorithm requires only a few parameter changes in sqlnet.ora file. No certificate or directory setup is required and only requires restart of the database.  With Oracle Database Version 11g Release 2, network encryption (native network encryption and SSL/TLS) and strong authentication services (Kerberos, PKI, and RADIUS) are no longer part of the Oracle Advanced Security Option and are available in all licensed editions of all supported releases of the Oracle database. See My Oracle Support Note 749947.1 for details on enabling transport encryption.

In order to enable encryption of the redo information, follow these requirements:

# Install Oracle Advanced Security option at both the primary and standby database.

# Set up the appropriate sqlnet.ora parameters as documented in the Oracle Advanced Security manual to allow Oracle Net to encrypt and integrity checksum the redo traffic shipped to the standby.

Oracle Advanced Security Transparent Data Encryption

ENCRYPTION_WALLET_LOCATION = (SOURCE =
                                  (METHOD = FILE)
                                  (METHOD_DATA =
                                  (DIRECTORY =
                                   /oracle/dbsid/admin/pdcs11/wallet)))

Oracle Advanced Security Network Encryption

#ASO Encryption

sqlnet.encryption_server=accepted
sqlnet.encryption_client=requested
sqlnet.encryption_types_server=(RC4_40)
sqlnet.encryption_types_client=(RC4_40)

Oracle Advanced Security Network Data Integrity

#ASO Checksum

sqlnet.crypto_checksum_server=requested
sqlnet.crypto_checksum_client=requested
sqlnet.crypto_checksum_types_server = (MD5)
sqlnet.crypto_checksum_types_client = (MD5)

SSL

#SSL

WALLET_LOCATION = (SOURCE=
                          (METHOD = FILE)
                          (METHOD_DATA =
                           DIRECTORY=/wallet)

SSL_CIPHER_SUITES=(SSL_DH_anon_WITH_RC4_128_MD5)
SSL_VERSION= 3
SSL_CLIENT_AUTHENTICATION=FALSE 

12C Data guard Broker ORA-01017: invalid username/password; logon denied

## While doing switchover and failover with 12C Broker i kept on investigating for a long time why invalid username password error is coming even though the password and password file is same for primary and standby, i later realized that the password was not complex and that was the reason i was getting this error. Let us see now for a switchover and failover example.

DGMGRL> switchover to prd ;
Performing switchover NOW, please wait...
Operation requires a connection to instance "prd" on database "prd"
Connecting to instance "prd"...
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

        connect to instance "prd" of database "prd"

DGMGRL> switchover to prd ;
not logged on
DGMGRL> connect sys@drs
Password:
ORA-01017: invalid username/password; logon denied

## Now i made sure my password is complex

DGMGRL> connect sys@drs
Password:
Connected as SYSDBA.
DGMGRL> switchover to prd ;
Performing switchover NOW, please wait...
Operation requires a connection to instance "prd" on database "prd"
Connecting to instance "prd"...
Connected as SYSDBA.
New primary database "prd" is opening...
Operation requires startup of instance "drs" on database "drs"
Starting instance "drs"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "prd"
DGMGRL> show configuration

Configuration - myconfig1

  Protection Mode: MaxPerformance
  Databases:
  prd - Primary database
    drs - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

Playing with DBMS_COMPARISON package in 11G

One of a good feature in 11G is the ability to compare and converge the data with the DBMS_COMPARISON package, The DBMS_COMPARISON package provides interfaces to compare and converge database objects at different databases. The best part is we can compare tables with different names and columns also provided the data type is same, also it is possible to compare a table with a materialzied view.

Some prerequisites are there in order to use this package:

1. The source database must be an Oracle 11g Release 1 or later.

2. The destination database must be an Oracle database.

3. The character set must be the same.

  1  BEGIN
  2    DBMS_COMPARISON.CREATE_COMPARISON
  3    ( comparison_name => 'compare_t1_and_t22'
  4    , schema_name     => 'scott'
  5    , object_name     => 'T1'
  6    , dblink_name     => null
  7    , remote_schema_name=> 'SCOTT'
  8    , remote_object_name=>'T2'
  9    );
 10* END;
SQL> /
BEGIN
*
ERROR at line 1:
ORA-23626: No eligible index on table SCOTT.T1
ORA-06512: at "SYS.DBMS_COMPARISON", line 4793
ORA-06512: at "SYS.DBMS_COMPARISON", line 448
ORA-06512: at line 2

## Lets see what error explains :-

[oracle@node4 ~]$ oerr ora 23626
23626, 00000, "No eligible index on table %s.%s"
// *Cause:  Table did not have an eligible index to use for comparison.
// *Action: For a table to be compared using DBMS_COMPARISON, the table must
//          have an appropriate index on a column of certain data types.
//          Check the documentation for supported data types.


## So we need an index on the column but it should be the primary key one not the non unique one

  1  BEGIN
  2    DBMS_COMPARISON.CREATE_COMPARISON
  3    ( comparison_name => 'compare_t1_and_t22'
  4    , schema_name     => 'scott'
  5    , object_name     => 'T1'
  6    , dblink_name     => null
  7    , remote_schema_name=> 'SCOTT'
  8    , remote_object_name=>'T2'
  9    );
 10* END;
SQL> /

PL/SQL procedure successfully completed.

## Let us now compare both the objects


  1  DECLARE
  2    consistent   BOOLEAN;
  3    scan_info    DBMS_COMPARISON.COMPARISON_TYPE;
  4  BEGIN
  5    consistent := DBMS_COMPARISON.COMPARE
  6                  ( comparison_name =>'compare_t1_and_t22'
  7                  , scan_info       => scan_info , perform_row_dif => TRUE);
  8    DBMS_OUTPUT.PUT_LINE('Scan ID: '||scan_info.scan_id);
  9    IF consistent=TRUE THEN
 10      DBMS_OUTPUT.PUT_LINE('No differences were found.');
 11    ELSE
 12      DBMS_OUTPUT.PUT_LINE('Differences were found.');
 13    END IF;
 14* END;
SQL> /
Scan ID: 1
Differences were found.

PL/SQL procedure successfully completed.

## So there were differences found, let us see them in below query


  1  SELECT s.scan_id
  2  ,      c.COMPARISON_NAME
  3  ,      c.SCHEMA_NAME
  4  ,      c.OBJECT_NAME
  5  ,      s.CURRENT_DIF_COUNT
  6  FROM   USER_COMPARISON c
  7  ,      USER_COMPARISON_SCAN_SUMMARY s
  8  WHERE  c.COMPARISON_NAME = s.COMPARISON_NAME
  9  AND    s.scan_id =
 10* 1
SQL> /

   SCAN_ID COMPARISON_NAME                SCHEMA_NAME                    OBJECT_NAME                    CURRENT_DIF_COUNT
---------- ------------------------------ ------------------------------ ------------------------------ -----------------
         1 COMPARE_T1_AND_T22             SCOTT                          T1                                             2

## And lets see the values


  1  SELECT c.COLUMN_NAME
  2  ,      r.INDEX_VALUE
  3  ,      case
  4         when r.LOCAL_ROWID is null
  5         then 'No'
  6         else 'Yes'
  7         end  LOCAL_ROWID
  8  ,      case
  9         when r.REMOTE_ROWID is null
 10         then 'No'
 11         else 'Yes'
 12         end  REMOTE_ROWID
 13  FROM   USER_COMPARISON_COLUMNS c
 14  ,      USER_COMPARISON_ROW_DIF r
 15  ,      USER_COMPARISON_SCAN s
 16  WHERE  c.COMPARISON_NAME = 'COMPARE_T1_AND_T22'
 17  AND    r.SCAN_ID         = s.SCAN_ID
 18  AND    r.STATUS          = 'DIF'
 19  --AND    c.INDEX_COLUMN    = 'Y'
 20  AND    c.COMPARISON_NAME = r.COMPARISON_NAME
 21  ORDER
 22* BY     r.INDEX_VALUE
SQL> /

COLUMN_NAME                    INDEX_VALUE                    LOC REM
------------------------------ ------------------------------ --- ---
C2                             1                              Yes No
ID                             1                              Yes No
C2                             2                              No  Yes
ID                             2                              No  Yes


## Lets now do the converging part by making sure local wins


  1  DECLARE
  2    scan_info    DBMS_COMPARISON.COMPARISON_TYPE;
  3  BEGIN
  4    DBMS_COMPARISON.CONVERGE
  5    ( comparison_name  => 'compare_t1_and_t22'
  6    , scan_id          => 1
  7    , scan_info        => scan_info
  8    , converge_options => DBMS_COMPARISON.CMP_CONVERGE_LOCAL_WINS
  9    );
 10    DBMS_OUTPUT.PUT_LINE('Local Rows Merged: '||scan_info.loc_rows_merged);
 11    DBMS_OUTPUT.PUT_LINE('Remote Rows Merged: '||scan_info.rmt_rows_merged);
 12    DBMS_OUTPUT.PUT_LINE('Local Rows Deleted: '||scan_info.loc_rows_deleted);
 13    DBMS_OUTPUT.PUT_LINE('Remote Rows Deleted: '||scan_info.rmt_rows_deleted);
 14* END;
 15  /
Local Rows Merged: 0
Remote Rows Merged: 1
Local Rows Deleted: 0
Remote Rows Deleted: 1

PL/SQL procedure successfully completed.

Integrated Replicat in Goldengate

Integrated Replicat or also known as Inetgrated Deliver has been introduced from 11.2.0.4 onwards that means it is also available in 12C, In integrated mode, the Replicat process leverages the apply processing functionality that is available within the Oracle database. In this mode, Replicat operates as follows:

# Reads the Oracle GoldenGate trail.

# Performs data filtering, mapping, and conversion.

# Constructs logical change records (LCR) that represent source database DML transactions (in committed order). DDL is applied directly by Replicat.

# Attaches to a background process in the target database known as a database inbound server by means of a lightweight streaming interface.

# Transmits the LCRs to the inbound server, which applies the data to the target database.


Below is an image which lists the example of an integrated apply with 2 Apply Servers

Errors in Goldengate Scenario 4

Scenario 4


ERROR   OGG-00396  Command 'MAP' not terminated by semi-colon.

ERROR   OGG-01668  PROCESS ABENDING.

## This error message clearly specifies MAP command not finished with semi- colon, so below is how we fixed it :-

## Now you will see that semicolon is clearly mentioned :-

GGSCI (node4.example.com as ogguser@euro) 61> edit params rep1

GGSCI (node4.example.com as ogguser@euro) 62> view params rep1

REPLICAT REP1
ASSUMETARGETDEFS
USERID ogguser, PASSWORD o
MAP scott.emp4, TARGET scott.emp4;


GGSCI (node4.example.com as ogguser@euro) 63> start replicat rep1

Sending START request to MANAGER ...
REPLICAT REP1 starting


GGSCI (node4.example.com as ogguser@euro) 64> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                        
REPLICAT    RUNNING     REP1        00:00:00      00:05:43


__________________

Errors in Goldengate Scenario 3

Scenario 3

Error Message :- ERROR   OGG-01044  The trail '/u01/app/oracle/product/ogg_trg/dirdat/rg' is not assigned to extract 'EXT1'. Assign the trail to the extract with the command "ADD EXTTRAIL/RMTTRAIL /u01/app/oracle/product/ogg_trg/dirdat/rg, EXTRACT EXT1".

2015-09-26 17:34:57  ERROR   OGG-01668  PROCESS ABENDING.


GGSCI (node4.example.com as ogguser@amer) 49> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                        
EXTRACT     ABENDED     EXT1        00:00:00      00:07:30

## After checking we found that remote trail file name was not correct so we fixed it solve it as per below details :-

## Let check what exact remote should the extract send to, so its the rt file not rg which was observed in the error log

GGSCI (node4.example.com as ogguser@amer) 51> info extract ext1, detail

EXTRACT    EXT1      Last Started 2015-09-26 17:34   Status ABENDED
Checkpoint Lag       00:00:00 (updated 00:09:14 ago)
Log Read Checkpoint  Oracle Redo Logs
                     2015-09-26 17:30:07  Seqno 20, RBA 25548800
                     SCN 0.2082218 (2082218)

  Target Extract Trails:

  Trail Name                                       Seqno        RBA     Max MB Trail Type

  /u01/app/oracle/product/ogg_trg/dirdat/rt            0       1964        100 RMTTRAIL


## We can also check from below command :-


GGSCI (node4.example.com as ogguser@amer) 52> info rmttrail *

       Extract Trail: /u01/app/oracle/product/ogg_trg/dirdat/rt
             Extract: EXT1
               Seqno: 0
                 RBA: 1964
           File Size: 100M

## Below are details of how we have fixed it :-

GGSCI (node4.example.com as ogguser@amer) 54> edit params ext1



GGSCI (node4.example.com as ogguser@amer) 55> view params ext1

EXTRACT ext1
userid ogguser, password o
RMTHOST node4.example.com , mgrport 7909
rmttrail /u01/app/oracle/product/ogg_trg/dirdat/rt
TABLE SCOTT.EMP4;



GGSCI (node4.example.com as ogguser@amer) 56> start extract ext1

Sending START request to MANAGER ...
EXTRACT EXT1 starting


GGSCI (node4.example.com as ogguser@amer) 57> info all    

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                        
EXTRACT     RUNNING     EXT1        00:10:54      00:00:05

Errors in Goldengate Scenario 2

# Scenario 2

WARNING OGG-01223  TCP/IP error 111 (Connection refused), endpoint: node4.example.com:7907.

INFO    OGG-01971  The previous message, 'WARNING OGG-01223', repeated 2 times.


Whenever there is problem with extract always check whether it is able to capture the records or not, this will be done by stats extract command from GGSCI prompt

GGSCI (node4.example.com as ogguser@amer) 42> stats extract ext1

Sending STATS request to EXTRACT EXT1 ...

No active extraction maps.

## this means that extract is not able to capture the records as of now

We check the extract report file in location

/u01/app/oracle/product/ogg_src/dirrpt

and the report file name is EXT1.rpt

Below is the error details :-

2015-09-26 17:31:20  WARNING OGG-01223  TCP/IP error 111 (Connection refused), endpoint: node4.example.com:7907.

2015-09-26 17:31:50  INFO    OGG-01971  The previous message, 'WARNING OGG-01223', repeated 2 times.

## After reading the above error message, we came to know that the MGR port number was not correct in extract parameter file, so after correcting it we are able to solve it, lets check how

GGSCI (node4.example.com as ogguser@amer) 37> view params ext1


EXTRACT ext1
userid ogguser, password o
RMTHOST node4.example.com , mgrport 7907
rmttrail /u01/app/oracle/product/ogg_trg/dirdat/rt
TABLE SCOTT.EMP4;

## After correcting the port number to 7909


GGSCI (node4.example.com as ogguser@amer) 47> start extract ext1

Sending START request to MANAGER ...
EXTRACT EXT1 starting



GGSCI (node4.example.com as ogguser@amer) 44> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                        
EXTRACT     RUNNING     EXT1        00:00:00      00:02:10