Add Oracle Physical Standby to Existing Data guard BROKER Configuration

 

 
PRIMARY  : oradb_prim
STANDBY  :  oradb_std
ORACLE Version : 19c
 

Assuming tnsping using TNSNAME is working from both sides.

On Standby :

sho parameter broker

 alter system set dg_broker_config_file1='+DATAC1/ORADB_STD/DATAGUARDCONFIG/oradb_primdg1.dat' scope=both sid='*'; 

 alter system set dg_broker_config_file2='+DATAC1/ORADB_STD/DATAGUARDCONFIG/oradb_primdg2.dat' scope=both sid='*';

 alter system set dg_broker_start=TRUE scope=both sid='*'; 

sho parameter broker

 

From primary

$ dgmgrl /

show configuration;            

    --- Add Standby database into Broker Config                                  
add database oradb_std  as connect identifier is oradb_std maintained as physical;
 

enable configuration;

show configuration;

        --- Add Redo Routes for Primary

show database oradb  redoroutes;

edit database oradb_prim set property RedoRoutes='(local:oradb_std ASYNC)';   -- Change as per requirements

  -- As soon as Redo property is added oracle dest_ parameter will set in database Automatically

show database oradb_prim redoroutes;

 

edit database 'oradb_std' set state='apply-off';

edit database 'oradb_std' set state='apply-on';

Enable configuration

            --- Optionally adjust some properties as you like

DGMGRL> edit database oradb_std set property TransportLagThreshold='900';

Property "transportlagthreshold" updated

 

DGMGRL> edit database oradb_std set property ApplyLagThreshold='900';

Property "applylagthreshold" updated

Enable Physical standby BROKER Configuration for oracle Primary

Primary : PRIMDB  
Standby  : primdb_std 
 
On primary :
 
12:14:55 SYSTEM@PRIMDB >sho parameter broker
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
connection_brokers                   string      ((TYPE=DEDICATED)(BROKERS=1)),
                                                  ((TYPE=EMON)(BROKERS=1))
dg_broker_config_file1               string      /opt/oracle/product/19.3.0/dbs
                                                 /dr1primdb.dat
dg_broker_config_file2               string      /opt/oracle/product/19.3.0/dbs
                                                 /dr2primdb.dat
dg_broker_start                      boolean     FALSE
use_dedicated_broker                 boolean     FALSE
 
12:15:05 SYSTEM@PRIMDB > show parameter log_archive_config
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_config                   string
 
oraclehost:PRIMDB:/home/oracle $ dgmgrl /
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Thu Jul 27 11:45:14 2023
Version 19.17.0.0.0
 
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
 
Welcome to DGMGRL, type "help" for information.
Connected to "PRIMDB"
Connected as SYSDG.
DGMGRL> show configuration
ORA-16525: The Oracle Data Guard broker is not yet available.
 
Configuration details cannot be determined by DGMGRL
 
 
 
SYNTAX:
DGMGRL> CREATE CONFIGURATION '<configuration name>' AS PRIMARY DATABASE IS '<primary db_unique_name>' CONNECT IDENTIFIER IS <primary connect string>;
 
 
DGMGRL>  create configuration Primdb_dg as primary database is 'PRIMDB' connect identifier is PRIMDB ;
Error:
ORA-16525: The Oracle Data Guard broker is not yet available.
 
 
Fix : set “dg_broker_start” to TRUE
 
11:50:11 SYSTEM@PRIMDB >   alter system set dg_broker_start=true sid='*';
 
System altered.
 

Also you can set desired broker config file path using below

alter system set dg_broker_config_file1='+<path>/DATAGUARDCONFIG/primdb_dg1.dat' scope=both sid='*'; 

alter system set dg_broker_config_file2='+<path>/DATAGUARDCONFIG/primdb_dg2.dat' scope=both sid='*'; 

11:51:53 SYSTEM@PRIMDB >sho parameter broker
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
connection_brokers                   string      ((TYPE=DEDICATED)(BROKERS=1)),
                                                  ((TYPE=EMON)(BROKERS=1))
dg_broker_config_file1               string      /opt/oracle/product/19.3.0/dbs/dr1PRIMDB.dat
dg_broker_config_file2               string      /opt/oracle/product/19.3.0/dbs/dr2PRIMDB.dat                                            
dg_broker_start                      boolean     TRUE
use_dedicated_broker                 boolean     FALSE
 
  
 
DGMGRL> create configuration primdb_dg as primary database is 'PRIMDB' connect identifier is primdb ;
Configuration "primdb_dg" created with primary database "PRIMDB"
 
DGMGRL>  show configuration
 
Configuration - Primdb_dg
 
  Protection Mode: MaxPerformance
  Members:
  PRIMDB - Primary database
 
Fast-Start Failover:  Disabled
 
Configuration Status:
DISABLED
 
Files will be created automatically on server
 
oraclehost:PRIMDB:/home/oracle $ ll /opt/oracle/product/19.3.0/dbs/dr1PRIMDB.dat
-rw-r----- 1 oracle oinstall 8192 Jul 27 11:58 /opt/oracle/product/19.3.0/dbs/dr1PRIMDB.dat
 
oraclehost:PRIMDB:/home/oracle $ ll /opt/oracle/product/19.3.0/dbs/dr2PRIMDB.dat
-rw-r----- 1 oracle oinstall  8192 Jul 27 11:58 /opt/oracle/product/19.3.0/dbs/dr2PRIMDB.dat
 
 
DGMGRL> enable configuration;
Enabled.
 
DGMGRL> show configuration
 
Configuration - Primdb_dg
 
  Protection Mode: MaxPerformance
  Members:
  PRIMDB - Primary database
    Warning: ORA-16905: The member was not enabled yet.
 
Fast-Start Failover:  Disabled
 
Configuration Status:
WARNING   (status updated 246 seconds ago)

 Doc:
12c Create Dataguard Broker Configuration - DGMGRL (Doc ID 1583588.1)
 

Add Standby to primary can be found here :  Add standby to Broker 

Check oracle OEM agent from AWS RDS database

PING OMS

 

From RDS database

 

SQL> SELECT rdsadmin.rdsadmin_oem_agent_tasks.ping_oms_oem_agent() as TASK_ID from DUAL;
 
TASK_ID
--------------------------------------------------------------------------------
16968905670056-4470
 
 

View the status output

 
SQL> SELECT text FROM table(rdsadmin.rds_file_util.read_text_file('BDUMP','dbtask-16968905670056-4470.log'));
 
TEXT
--------------------------------------------------------------------------------
2023-10-09 22:29:32.557 UTC [INFO ] Oracle Enterprise Manager Cloud Control 13c
Release 4
 
Copyright (c) 1996, 2020 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
EMD pingOMS completed successfully
 
2023-10-09 22:29:32.558 UTC [INFO ] The task finished successfully.
 
6 rows selected.

 

 

Check Agent Status

 

SQL> SELECT rdsadmin.rdsadmin_oem_agent_tasks.get_status_oem_agent() as TASK_ID from DUAL;

 
TASK_ID
--------------------------------------------------------------------------------
1696890801000-4470
 
 

SQL> SELECT text FROM table(rdsadmin.rds_file_util.read_text_file('BDUMP','dbtask-1696890801000-4470.log'));

 
TEXT
--------------------------------------------------------------------------------
2023-10-09 22:33:23.486 UTC [INFO ] Oracle Enterprise Manager Cloud Control 13c
Release 4
 
Copyright (c) 1996, 2020 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
Agent Version          : 13.4.0.0.0
OMS Version            : 13.5.0.0.0
Protocol Version       : 12.1.0.1.0
Agent Home             : /home/rdsdb/agent/app/agent_inst
Agent Log Directory    : /home/rdsdb/agent/app/agent_inst/sysman/log
Agent Binaries         : /home/rdsdb/agent/app/agent_13.4.0.0.0
Core JAR Location      : /home/rdsdb/agent/app/agent_13.4.0.0.0/jlib
Agent Process ID       : 1076
Parent Process ID      : 1032
Agent URL              : https://mydb.ckpij9egh4g.us-west-2.rds.amazonaws.com
:3872/emd/main/
 
Local Agent URL in NAT : https://mydb.ckpij9egh4g.us-west-2.rds.amazonaws.com
:3872/emd/main/
 
Repository URL         : https://omsserver.domain.com:4904/empbs/upload
Started at             : 2023-09-19 11:20:48
Started by user        : rdsdb
Operating System       : Linux version 4.1.12-124.77.2.el7uek.x86_64 (amd64)
Number of Targets      : 2
Last Reload            : (none)
Last successful upload                       : 2023-10-09 22:32:08
Last attempted upload                        : 2023-10-09 22:32:08
Total Megabytes of XML files uploaded so far : 26.03
Number of XML files pending upload           : 0
Size of XML files pending upload(MB)         : 0
Available disk space on upload filesystem    : 53.44%
Collection Status                            : Collections enabled
Heartbeat Status                             : Ok
Last attempted heartbeat to OMS              : 2023-10-09 22:32:55
Last successful heartbeat to OMS             : 2023-10-09 22:32:55
Next scheduled heartbeat to OMS              : 2023-10-09 22:33:56
 
---------------------------------------------------------------
Agent is Running and Ready
 
2023-10-09 22:33:23.487 UTC [INFO ] The task finished successfully.
 
36 rows selected.
 
 

 

Refer more :

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Oracle.Options.OEMAgent.html#Oracle.Options.OEMAgent.limitations

 

x

Recover Standby DB when standby_file_management is MANUAL when new files added in primary 19c

 
 
When datafiles are added on primary I see 2 datafiles are in WRONG Location in standby database after the new standby build
 
On Standby :
09:52:55 SYSTEM@devdb_std >select file_name from dba_data_files order by 1;
 
FILE_NAME
--------------------------------------------------------------------------------
/acfs_data/devdb_std/datafile/DEVDB_STD/datafile/o1_mf_it2_lbtw9k_.dbf
/acfs_data/devdb_std/datafile/DEVDB_STD/datafile/o1_mf_it2_lbtymt_.dbf
/acfs_data/devdb_std/datafile/data_D-DEVDB_TS-A_FNO-8
/acfs_data/devdb_std/datafile/data_D-DEVDB_TS-AA_FNO-9
/acfs_data/devdb_std/datafile/data_D-DEVDB_TS-AD_FNO-10
…… Trimmed ….
 
09:54:26 SYSTEM@devdb_std >sho parameter convert
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert                 string      +DATAC1, /acfs_data/devdb_std/datafile
log_file_name_convert                string      +RECOC1, /acfs_reco/devdb_std/onlinelog
pdb_file_name_convert                string
 
09:53:19 SYSTEM@devdb_std >sho parameter standby
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
enabled_PDBs_on_standby              string      *
standby_db_preserve_states           string      NONE
standby_file_management              string      AUTO
standby_pdb_source_file_dblink       string
standby_pdb_source_file_directory    string
 
Reason :
 
The reason being by default when “standby_file_management” is AUTO OMF creates files in “db_file_name_convert” parameter in below location
 
<db_file_name_convert PATH>/<CAPS DBNAME>/datafile/
 
FIX:
 
Check the file number and status
 
10:31:33 SYSTEM@devdb_std > select file#, error, name from v$datafile_header where ERROR='FILE MISSING';
 
     FILE# ERROR
---------- -----------------------------------------------------------------
NAME
------------------------------------------------------------------------------------------------------------------------------------------
       781 FILE MISSING
 
 
 
10:34:39 SYSTEM@devdb_std >select name from v$datafile where file#=781 ;
 
NAME
-------------------------------------------------------------------------------------------------------------------
/u02/app/oracle/product/19.0.0.0/dbhome_1/dbs/UNNAMED00781
 
10:34:53 SYSTEM@devdb_std >alter database create datafile '/u02/app/oracle/product/19.0.0.0/dbhome_1/dbs/UNNAMED00781' as NEW;
alter database create datafile '/u02/app/oracle/product/19.0.0.0/dbhome_1/dbs/UNNAMED00781' as NEW
*
ERROR at line 1:
ORA-01275: Operation CREATE DATAFILE is not allowed if standby file management is automatic.
 
Cannot run when standby_file_management is AUTO so disable and rerun
 
10:36:13 SYSTEM@devdb_std >alter system set standby_file_management=MANUAL scope=both;
 
System altered.
 
10:36:25 SYSTEM@devdb_std >alter database create datafile '/u02/app/oracle/product/19.0.0.0/dbhome_1/dbs/UNNAMED00781' as NEW;
 
Database altered.
 
10:36:31 SYSTEM@devdb_std >alter system set standby_file_management=AUTO scope=both;
 
System altered.
 
 
Check the paths again
 
SQL >   select file_name from dba_data_files order by 1;
 
SQL >   select name from v$datafile where file#=779;
 
set the path correctly based on your location so that we cannot ran into this issue again
 
10:17:19 SYSTEM@devdb_std >alter system set db_create_file_dest='/acfs_data';
 
System altered.
 
10:23:41 SYSTEM@devdb_std >sho parameter file_dest
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /u02/app/oracle/product/19.0.0.0/dbhome_1/rdbms/audit
db_create_file_dest                  string      /acfs_data
db_recovery_file_dest                string      /acfs_reco/devdb_std/archivelog
db_recovery_file_dest_size           big integer 3500G
remote_recovery_file_dest            string
 
 
Now add couple of datafiles in primary and verify.
 
10:52:44 SYSTEM@devdb_std >select name from v$datafile where file#=779;
 
NAME
------------------------------------------------------------------------------------------------------------------------------------------
/acfs_data/DEVDB_STD/datafile/o1_mf_imm2_lc2vtbcd_.dbf
 
 

More like this

http://chandu208.blogspot.com/2017/05/ora-01111-name-for-data-file-xx-is.html

Goldengate PMSRVR - Monitoring is not enabled in the GLOBALS parameter file

 
 
ENV :  OGG  19c (Classic Architecture)
 
On my new server I wanted to setup PMSRVR
Port should be open and listening  (default is 9004)
 
cd $GG_HOME
vi GLOBALS and add below entries
 
dev01.domain.com:NOTSET:/goldengate/home $ cat GLOBALS
GGSCHEMA ggadmin
ENABLEMONITORING UDP
 
 
dev01.domain.com:NOTSET:/goldengate/home $ GG
 
Oracle GoldenGate Command Interpreter for Oracle
Version 19.1.0.0.4 OGGCORE_19.1.0.0.0_PLATFORMS_191017.1054_FBO
Linux, x64, 64bit (optimized), Oracle 19c on Oct 17 2019 21:16:29
Operating system character set identified as UTF-8.
 
Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved.
 
 
 
GGSCI (dev01.domain.com) 1> info all
 
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
 
MANAGER     RUNNING                                          
EXTRACT     RUNNING     EDEV01     00:00:00      00:00:08   
EXTRACT     RUNNING     PDEV01     00:00:00      00:00:10   
REPLICAT    RUNNING     RDEV01     00:00:00      00:00:04   
 
 
GGSCI (dev01.domain.com) 2> start pmsrvr
ERROR: Monitoring is not enabled in the GLOBALS parameter file..
 
But GLOBALS file has entries already.
 
FIX :
 
dev01.domain.com:NOTSET:/goldengate/home $ GG
 
Oracle GoldenGate Command Interpreter for Oracle
Version 19.1.0.0.4 OGGCORE_19.1.0.0.0_PLATFORMS_191017.1054_FBO
Linux, x64, 64bit (optimized), Oracle 19c on Oct 17 2019 21:16:29
Operating system character set identified as UTF-8.
 
Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved.
 
 
 
GGSCI (dev01.domain.com) 1> edit params ./GLOBALS  à This was EMPTY
GGSCHEMA ggadmin
ENABLEMONITORING UDP
 
 
-        Added below entries and save
 
GGSCI (dev01.domain.com) 3> exit
 
dev01.domain.com:NOTSET:/goldengate/home $ GG
 
Oracle GoldenGate Command Interpreter for Oracle
Version 19.1.0.0.4 OGGCORE_19.1.0.0.0_PLATFORMS_191017.1054_FBO
Linux, x64, 64bit (optimized), Oracle 19c on Oct 17 2019 21:16:29
Operating system character set identified as UTF-8.
 
Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved.
 
 
 
GGSCI (dev01.domain.com) 1> info all
 
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
 
MANAGER     STOPPED                                          
JAGENT      STOPPED                                          
PMSRVR      STOPPED                                          
EXTRACT     RUNNING     EDEV01     00:00:00      00:00:05    
EXTRACT     RUNNING     PDEV01     00:00:00      00:00:11    
REPLICAT    RUNNING     RDEV01     00:00:00      00:00:04   
 
 
GGSCI (dev01.domain.com) 2> start PMSRVR
PMSRVR started
 
 
Refer more on PMSRVR here :
http://chandu208.blogspot.com/search?q=pmsrvr
 
OMC: Discovery of Oracle GoldenGate in Oracle Management Cloud Fails with Error: Monitoring is not enabled in the GLOBALS parameter file (Doc ID 2514856.1)
 
   

Refresh 19c physical standby database using service

 
Primary DB (19c) : TESTDB_PRIM (standby)
Standby DB (19c) : TESTDB_STD (cascade standby)
 
Standby has a GAP and waiting on logs which are deleted from primary
 
NOTE : here my primary is also a STANDBY from where I setup another standby (cascade)
 
SQL> select inst_id,process, thread#, sequence#, status from gv$managed_standby where process='MRP0';
 
   INST_ID PROCESS      THREAD#  SEQUENCE# STATUS
---------- --------- ---------- ---------- ------------
         1 MRP0               1     973244 WAIT_FOR_GAP
 
 
 
SQL> SELECT name "Database name", status, ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (select name from v$database) name, (select status from v$instance) status,(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH, (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;
 
Database  STATUS           Thread Last Sequence Received Last Sequence Applied Difference
--------- ------------ ---------- ---------------------- --------------------- ----------
TESTDB    MOUNTED               1                 975607                973243       2364
TESTDB    MOUNTED               3                 945126                942906       2220
TESTDB    MOUNTED               2                 995543                993282       2261
 
 ***** Switch some logfile on PRIMARY *****
 
SQL>   /
 
Database  STATUS           Thread Last Sequence Received Last Sequence Applied Difference
--------- ------------ ---------- ---------------------- --------------------- ----------
TESTDB    MOUNTED               1                 975607                973243       2364
TESTDB    MOUNTED               3                 945126                942906       2220
TESTDB    MOUNTED               2                 995545                993282       2263
 
 
I see logfiles are flowing to standby but MRP is waiting on GAP
 
Standby Alert log gives the GAP Message
 
2023-06-13T11:48:36.422200-05:00
PR00 (PID:397954): FAL: Failed to request gap sequence
PR00 (PID:397954):  GAP - thread 1 sequence 973244-973343
PR00 (PID:397954):  DBID 3401882687 branch 943793862
PR00 (PID:397954): FAL: All defined FAL servers have been attempted
PR00 (PID:397954): -------------------------------------------------------------------------
PR00 (PID:397954): Check that the CONTROL_FILE_RECORD_KEEP_TIME initialization
PR00 (PID:397954): parameter is defined to a value that's sufficiently large
PR00 (PID:397954): enough to maintain adequate log switch information to resolve
PR00 (PID:397954): archived redo log gaps.
PR00 (PID:397954): -------------------------------------------------------------
 
 
Solution :
 
OCI - Roll Forward A Standby Database Using Recover Database From Service (Doc ID 2931070.1)
How to Roll Forward a Standby Database Using Recover Database From Service (Doc ID 2850185.1)
 
From Oracle 18c and higher. Single command (RED) will do all the work
 
1.      STOP MRP
                     SQL>  recover managed standby database cancel;   ( or )
 
                     DGMGRL>  EDIT DATABASE '<standby>' SET STATE='APPLY-OFF';
 
 
2.      Stop all standby instances (if RAC) and mount only 1 node
3.       Run the shell script
HOSTNAME:NOTSET:/export/scripts $ cat TESTDB_INC_RECOVER_CB.sh
 
export NLS_DATE_FORMAT='DD-MON-YY HH24:MI:SS'
rman target / trace /var/logs/RMAN_INC_recover_01jun2023.log << EOF
alter session set NLS_DATE_FORMAT='DD-MON-YY HH24:MI:SS';
select sysdate from dual;
 
run
{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
allocate channel c5 type disk;
allocate channel c6 type disk;
allocate channel c7 type disk;
allocate channel c8 type disk;
allocate channel c9 type disk;
allocate channel c10 type disk;
recover database from service TESTDB_PRIM section size 5g;
}
 
alter session set NLS_DATE_FORMAT='DD-MON-YY HH24:MI:SS';
select sysdate from dual;
 
EOF
 
 
4.      Once recovery is done then start MRP
 
 
 
 

Then logs will start apply normally.
 
 
 
 
 
 
 
 
 
 
 

ORA-38777: database must not be started in any other instance

 

I have 19c DB converted to SNAPSHOT STANDBY and now i wanted to revert back to Physical standby

SQL> select NAME, OPEN_MODE, GUARD_STATUS, DATABASE_ROLE from v$database;

NAME      OPEN_MODE            GUARD_S DATABASE_ROLE
--------- -------------------- ------- ----------------
MYPROD    READ WRITE           NONE    SNAPSHOT STANDBY

While Converting Snapshot standby back to Physical standby while both RAC Nodes are up and running we get this error

SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

ALTER DATABASE CONVERT TO PHYSICAL STANDBY

*

ERROR at line 1:

ORA-38777: database must not be started in any other instance

srvctl status database -d myprod_std -v
Instance myprod_std1 is running on node hostname1. Instance status: Mounted (Closed).
Instance myprod_std2 is running on node hostname2. Instance status: Mounted (Closed).

NOTE : 

   Instance should open in only 1 node to Convert back

 $ srvctl stop instance -i myprod2 -d myprod_std
 
hostname:myprod1:/export/scripts $ sqlplus / as sysdba
 
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jun 9 13:11:19 2023
Version 19.17.0.0.0
 
Copyright (c) 1982, 2022, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Version 19.17.0.0.0
 
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
 
Database altered.
 
SQL> shut immediate
ORA-01109: database not open
 
 
Database dismounted.
ORACLE instance shut down.
SQL> exit
 
$ srvctl start database -d myprod_std -v
 
$ srvctl status database -d myprod_std -v

Instance myprod_std1 is running on node hostname1. Instance status: Mounted (Closed).

Instance myprod_std2 is running on node hostname2. Instance status: Mounted (Closed).

 
hostname:myprod_std:/export/scripts $ sqlplus / as sysdba
 
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jun 9 13:13:41 2023
Version 19.17.0.0.0
 
Copyright (c) 1982, 2022, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Version 19.17.0.0.0
 
SQL> alter database open;
 
Database altered.
 
SQL> select NAME, OPEN_MODE, GUARD_STATUS, DATABASE_ROLE from v$database;
 
NAME      OPEN_MODE            GUARD_S  DATABASE_ROLE
--------- -------------------- ------- ----------------
MYPROD    READ ONLY            NONE     PHYSICAL STANDBY
 
 Start MRP .......

SQL> alter database recover managed standby database disconnect from session;
 
Database altered.

Oracle goldengate Encrypt TRAIL File using ENCRYPTTRAIL parameter

 

https://docs.oracle.com/en/middleware/goldengate/core/21.3/reference/encrypttrail-noencrypttrail.html#GUID-F9C77C5E-500A-4B1D-9326-8385EECE531D

 ENCRYPTTRAIL and NOENCRYPTTRAIL parameters are used to enable/disable encryption on OGG TRAIL files

      1.      Wallet (or)  master key Method

      2.     ENCKEY Method

Extract param: (wallet method)

For wallet method you need to create wallets before using the ENCRYPTTRAIL in extract

 GGSCI (oracledev) 5> view params EXT

EXTRACT ext

INCLUDE /tmp/GG_ENV.mac

#E_GG_env()

GETUPDATEBEFORES

NOCOMPRESSDELETES

NOCOMPRESSUPDATES

REPORTCOUNT EVERY 1 HOUR, RATE

WARNLONGTRANS 1D, CHECKINTERVAL 10M, SKIPEMPTYTRANS

EXTTRAIL <path>/aa

TABLE SCOTT.EMPL GETBEFORECOLS(ON UPDATE ALL, ON DELETE ALL) ;

 

$ cat /goldengate/home/GG_ENV.mac

MACRO #E_GG_env

BEGIN
SETENV (ORACLE_HOME='/u01/app/oracle/product/19.0.0.0/dbhome_1')
SETENV (ORACLE_SID='oradb1')
USERIDALIAS ggadmin_dev
ENCRYPTTRAIL
END;

Reference:

How to Encrypt/Decrypt Oracle GoldenGate Trail File (Doc ID 1287578.1)
How to Use ENCRYPTTRAIL and DECRYPTTRAIL Parameters in Extract, Pump and Replicat (Doc ID 1397104.1)