KTEXPERTS – Page 16 – KNOWLEDGE SHARING PLATFORM

How to change remote trail file location in Oracle GoldenGate.

4 years ago by Binnary Ajay KumarGolden Gate

Hello Everyone, This article will teach you how to change remote trail file location in Oracle GoldenGate. Current location : /u01/GG_AWS_P1/GG_trails/aws_trails/ New Location : /dbbackup/GG_trails/aws_trails GG Process Details : Source :

GGSCI (gg.oracle.com) 2> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING DPAWSP1 00:00:00 00:00:06
EXTRACT RUNNING EXAWSP1 00:00:03 00:00:05

GGSCI (gg.oracle.com) 2> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING DPAWSP1 00:00:00 00:00:06

EXTRACT RUNNING EXAWSP1 00:00:03 00:00:05

Target :

GGSCI (gg2.oracle.com) 6> info all
Program Status Group Lag at Chkpt Time Since Chkpt
REPLICAT RUNNING REPAWSP1 00:00:08 00:00:04

GGSCI (gg2.oracle.com) 6> info all

Program Status Group Lag at Chkpt Time Since Chkpt

REPLICAT RUNNING REPAWSP1 00:00:08 00:00:04

Check Replicat trail file location

GGSCI (gg2.oracle.com) 2> info REPAWSP1 detail 

REPLICAT   REPAWSP1  Last Started 2021-12-10 01:01   Status RUNNING
INTEGRATED
Checkpoint Lag       00:00:00 (updated 00:00:07 ago)
Process ID           45824
Log Read Checkpoint  File /u01/GG_AWS_P1/GG_trails/aws_trails/R1000004321
                     2022-01-22 06:44:24.997155  RBA 202887709
INTEGRATED Replicat
No DBLOGIN Provided, constructed inbound server name is OGG$REPAWSP1
Current Log BSN value: (requires database login)
Low Watermark CSN value: (requires database login)
High Watermark CSN value: (requires database login)
  Extract Source                          Begin             End             
  /u01/GG_AWS_P1/GG_trails/aws_trails/R1000004321  2021-12-10 00:38  2022-01-22 06:44
  /u01/GG_AWS_P1/GG_trails/aws_trails/R1000003658  2021-12-10 00:14  2021-12-10 00:38
  /u01/GG_AWS_P1/GG_trails/aws_trails/R1000003658  2021-11-12 04:02  2021-12-10 00:14
  /u01/GG_AWS_P1/GG_trails/aws_trails/R1000000000  * Initialized *   First Record    
  /u01/GG_AWS_P1/GG_trails/aws_trails/R1000000000  * Initialized *   First Record    
Current directory    /u01/app/oracle/product/ogg_src
Report file          /u01/app/oracle/product/ogg_src/dirrpt/REPAWSP1.rpt
Parameter file       /u01/app/oracle/product/ogg_src/dirprm/REPAWSP1.prm
Checkpoint file      /u01/app/oracle/product/ogg_src/dirchk/REPAWSP1.cpr
Process file         
Error log            /u01/app/oracle/product/ogg_src/ggserr.log

GGSCI (gg2.oracle.com) 2> info REPAWSP1 detail

REPLICAT REPAWSP1 Last Started 2021-12-10 01:01 Status RUNNING

INTEGRATED

Checkpoint Lag 00:00:00 (updated 00:00:07 ago)

Process ID 45824

Log Read Checkpoint File /u01/GG_AWS_P1/GG_trails/aws_trails/R1000004321

2022-01-22 06:44:24.997155 RBA 202887709

INTEGRATED Replicat

No DBLOGIN Provided, constructed inbound server name is OGG$REPAWSP1

Current Log BSN value: (requires database login)

Low Watermark CSN value: (requires database login)

High Watermark CSN value: (requires database login)

Extract Source Begin End

/u01/GG_AWS_P1/GG_trails/aws_trails/R1000004321 2021-12-10 00:38 2022-01-22 06:44

/u01/GG_AWS_P1/GG_trails/aws_trails/R1000003658 2021-12-10 00:14 2021-12-10 00:38

/u01/GG_AWS_P1/GG_trails/aws_trails/R1000003658 2021-11-12 04:02 2021-12-10 00:14

/u01/GG_AWS_P1/GG_trails/aws_trails/R1000000000 * Initialized * First Record

Current directory /u01/app/oracle/product/ogg_src

Report file /u01/app/oracle/product/ogg_src/dirrpt/REPAWSP1.rpt

Parameter file /u01/app/oracle/product/ogg_src/dirprm/REPAWSP1.prm

Checkpoint file /u01/app/oracle/product/ogg_src/dirchk/REPAWSP1.cpr

Process file

Error log /u01/app/oracle/product/ogg_src/ggserr.log

Remote trail file location… Read More

WAL Internals in PostgreSQL

4 years ago by Ankit GoyalPostgreSQL

WAL Internals in PostgreSQL Write-Ahead Log (WAL) is a very important term in transaction processing. In PostgreSQL, it is also known as a transaction log. A log is a record of all the events or changes and WAL data is just a description of changes… Read More

Data Fragmentation in PostgreSQL

4 years ago by Ankit GoyalPostgreSQL

Data Fragmentation in PostgreSQL PostgreSQL is a relational database management system with a client-server architecture. At the server-side, PostgreSQL’s processes and shared memory work together and build an instance, which handles the access to the data. Client programs connect to the instance and request read… Read More

Ask Doubts & Learn ! (Oracle Snapshot Standby Database)

4 years ago by Shoaib AnsariUncategorized

Ask Doubts & Learn! (Oracle Snapshot Standby Database) In the below article, you can find all questions and answers regarding our upcoming session Oracle Snapshot Standby Database on (🕘28th Nov 2021 09:00 PM IST / 07:30 AM PST). Note: New Q&A will be updated… Read More

OGG-08223 Error when registering Extract for a container database.

4 years ago by Binnary Ajay KumarGolden Gate

OGG-08223 Error when registering Extract for a container database. Hi Experts. In this article, we will see the OGG-08223 Error when registering Extract for a container database. I do have a plans to setup replication between the pluggable database in different containers. When registering… Read More

Replication to GCP PostgreSQL Using Oracle GoldenGate

4 years ago by Ankit GoyalGolden Gate

Replication to GCP PostgreSQL Using Oracle GoldenGate Use Case: Real-time replication of transactional data from an on-premises database to GCP Managed PostgreSQL instance using GoldenGate. Architecture: Source Database (Source Database can be any GoldenGate supported databases) Oracle GoldenGate (OGG) 19c/18c/12c for extraction from Source DB… Read More

Oracle : Shell script for Crontab Monitoring alert

4 years ago by Vinod SiramOracle DB Admin

Hi Readers, In this article, we will see the Oracle : Shell script for Crontab Monitoring alert THIS SCRIPT WILL IDENTIFY changes with the crontab file made comparing with original file.

[oracle@nposqa1 ~]$ cat /home/oracle/DBA/bin/crontab_compare.bash
#!/bin/bash
# set -x
# Get the current crontab entries
# Compare with the expected crontab entries
# Notify if any changes found
#########################################################################################
# Control file locations separately.
# create following variables in the file
# ORIGFILE = Original file to compare the current crontab entries
# EMAILRECIPIENTS='example@example.com'
# EMAILBODY=Location of email body file.

VARIABLES=$1
. $VARIABLES

HOST=$(hostname)
CURRFILE='/tmp/currcron.file'
DIFFILE='/tmp/crondif.file'

crontab -l > $CURRFILE
diff $ORIGFILE $CURRFILE > $DIFFILE

#Function to push email to users on alert
function pushmail
{
SUBJ=$1
MAILBODY=$2
RECIPIENTS=$3
mail -s "$SUBJ" $RECIPIENTS < $MAILBODY
}

if [ -s $DIFFILE ]
then
echo "" > $EMAILBODY
echo "Attention: Found changes in the current crontab entry" >>$EMAILBODY
echo "" >> $EMAILBODY
echo "---- Below are the changes from DIFF command ----" >> $EMAILBODY
echo "" >> $EMAILBODY
cat $DIFFILE >> $EMAILBODY
pushmail "ALERT: Found changes in crontab entry on server $HOST" $EMAILBODY $EMAILRECIPIENTS
fi

[oracle@nposqa1 ~]$ cat /home/oracle/DBA/bin/crontab_compare.bash

#!/bin/bash

# set -x

# Get the current crontab entries

# Compare with the expected crontab entries

# Notify if any changes found

#########################################################################################

# Control file locations separately.

# create following variables in the file

# ORIGFILE = Original file to compare the current crontab entries

# EMAILRECIPIENTS='example@example.com'

# EMAILBODY=Location of email body file.

VARIABLES=$1

. $VARIABLES

HOST=$(hostname)

CURRFILE='/tmp/currcron.file'

DIFFILE='/tmp/crondif.file'

crontab -l > $CURRFILE

diff $ORIGFILE $CURRFILE > $DIFFILE

#Function to push email to users on alert

function pushmail

{

SUBJ=$1

MAILBODY=$2

RECIPIENTS=$3

mail -s "$SUBJ" $RECIPIENTS < $MAILBODY

}

if [ -s $DIFFILE ]

then

echo "" > $EMAILBODY

echo "Attention: Found changes in the current crontab entry" >>$EMAILBODY

echo "" >> $EMAILBODY

echo "---- Below are the changes from DIFF command ----" >> $EMAILBODY

echo "" >> $EMAILBODY

cat $DIFFILE >> $EMAILBODY

pushmail "ALERT: Found changes in crontab entry on server $HOST" $EMAILBODY $EMAILRECIPIENTS

Config file :

[oracle@nposqa1 ~]$ cat /home/oracle/DBA/cfg/crontab_compare.cfg
# Control file locations separately.
# create following variables in the file
# ORIGFILE = Original file to compare the current crontab entries
# EMAILRECIPIENTS='example@example.com'
# EMAILBODY=Location of email body file.

export DIR='/home/oracle/DBA'
export DBA_BIN=${DIR}/bin
export DBA_LOG=${DIR}/log
export DBA_CFG=${DIR}/cfg
export DBA_SQL=${DIR}/sql
export ORIGFILE="${DBA_CFG}/crontab_orig.cron"
export EMAILRECIPIENTS="admin@ktexperts.com"
export EMAILBODY="${DBA_CFG}/crontab_emailbody.file"

[oracle@nposqa1 ~]$ cat /home/oracle/DBA/cfg/crontab_compare.cfg

# Control file locations separately.

# create following variables in the file

# ORIGFILE = Original file to compare the current crontab entries

# EMAILRECIPIENTS='example@example.com'

# EMAILBODY=Location of email body file.

export DIR='/home/oracle/DBA'

export DBA_BIN=${DIR}/bin

export DBA_LOG=${DIR}/log

export DBA_CFG=${DIR}/cfg

export DBA_SQL=${DIR}/sql

export ORIGFILE="${DBA_CFG}/crontab_orig.cron"

export EMAILRECIPIENTS="admin@ktexperts.com"

export EMAILBODY="${DBA_CFG}/crontab_emailbody.file"

Create original crontab file :

cd /home/oracle/DBA/cfg\

1	cd /home/oracle/DBA/cfg\

crontab -l >crontab_orig.cron

1	crontab -l >crontab_orig.cron

Add below… Read More

Oracle : RAC Important Commands (SecureCRT)

4 years ago by Vinod SiramRAC

RAC Important Commands (SecureCRT) Dear Readers, In this article, we will see the Oracle : Important Commands for RAC (SecureCRT) Menu Bar : RAC Button : VD_Location

crsctl query css votedisk

1	crsctl query css votedisk

## STATE File Universal Id File Name Disk group
-- ----- ----------------- --------- ---------
1. ONLINE 970d29b0fdca4fb3bfa2210f71ebf828 (VIN:SSD_E0_S00_XXYZ41896P1) [DATA]
2. ONLINE 1422365a8a334f2fbfda2a8e9a348d78 (VIN:SSD_E0_S02_XXYZ41868P1) [DATA]
3. ONLINE 2752bc1b291a4f97bf904ff37b43c19b (VIN:SSD_E0_S04_XXYZ41948P1) [DATA]

## STATE File Universal Id File Name Disk group

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

1. ONLINE 970d29b0fdca4fb3bfa2210f71ebf828 (VIN:SSD_E0_S00_XXYZ41896P1) [DATA]

2. ONLINE 1422365a8a334f2fbfda2a8e9a348d78 (VIN:SSD_E0_S02_XXYZ41868P1) [DATA]

3. ONLINE 2752bc1b291a4f97bf904ff37b43c19b (VIN:SSD_E0_S04_XXYZ41948P1) [DATA]

Button : ocrcheck

ocrcheck

ocrcheck

Status of Oracle Cluster Registry is as follows :
Version : 4
Total space (kbytes) : 491684
Used space (kbytes) : 85328
Available space (kbytes) : 406356
ID : 496404425
Device/File Name : +DATA
Device/File integrity check succeeded
Device/File not configured
Device/File not configured
Device/File not configured
Device/File not configured
Cluster registry integrity check succeeded
Logical corruption check bypassed due to non-privileged user

Status of Oracle Cluster Registry is as follows :

Version : 4

Total space (kbytes) : 491684

Used space (kbytes) : 85328

Available space (kbytes) : 406356

ID : 496404425

Device/File Name : +DATA

Device/File integrity check succeeded

Device/File not configured

Cluster registry integrity check succeeded

Logical corruption check bypassed due to non-privileged user

Button : ConfigDB

srvctl config database -d ORCL

1	srvctl config database -d ORCL

Database unique name: ORCL
Database name: ORCL
Oracle home: /u01/app/oracle/product/12.1.0.2/dbhome_1
Oracle user: oracle
Spfile: +DATA/ORCL/PARAMETERFILE/spfile.266.1005927959
Password file: +DATA/ORCL/PASSWORD/pwdorcl.260.1005927685
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups: DATA,REDO
Mount point paths:
Services: orcl_app_svc,orcl_int_svc
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group: dbaoper
Database instances: orcl11,orcl12
Configured nodes: server03,server04
Database is administrator managed

Database unique name: ORCL

Database name: ORCL

Oracle home: /u01/app/oracle/product/12.1.0.2/dbhome_1

Oracle user: oracle

Spfile: +DATA/ORCL/PARAMETERFILE/spfile.266.1005927959

Password file: +DATA/ORCL/PASSWORD/pwdorcl.260.1005927685

Domain:

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Server pools:

Disk Groups: DATA,REDO

Mount point paths:

Services: orcl_app_svc,orcl_int_svc

Type: RAC

Start concurrency:

Stop concurrency:

OSDBA group: dba

OSOPER group: dbaoper

Database instances: orcl11,orcl12

Configured nodes: server03,server04

Database is administrator managed

Button : config service

srvctl config service -d ORCL

1	srvctl config service -d ORCL

Service name: orcl_app_svc
Server pool:
Cardinality: 2
Disconnect: false
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: false
Global: false
Commit Outcome: false
Failover type:
Failover method:
TAF failover retries:
TAF failover delay:
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: NONE
TAF policy specification: NONE
Edition:
Pluggable database name:
Maximum lag time: ANY
SQL Translation Profile:
Retention: 86400 seconds
Replay Initiation Time: 300 seconds
Session State Consistency:
GSM Flags: 0
Service is enabled
Preferred instances: orcl1,orcl2
Available instances:

Service name: orcl_int_svc
Server pool:
Cardinality: 2
Disconnect: false
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: false
Global: false
Commit Outcome: false
Failover type:
Failover method:
TAF failover retries:
TAF failover delay:
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: NONE
TAF policy specification: NONE
Edition:
Pluggable database name:
Maximum lag time: ANY
SQL Translation Profile:
Retention: 86400 seconds
Replay Initiation Time: 300 seconds
Session State Consistency:
GSM Flags: 0
Service is enabled
Preferred instances: orcl1,orcl2
Available instances:

Service name: orcl_app_svc

Server pool:

Cardinality: 2

Disconnect: false

Service role: PRIMARY

Management policy: AUTOMATIC

DTP transaction: false

AQ HA notifications: false

Global: false

Commit Outcome: false

Failover type:

Failover method:

TAF failover retries:

TAF failover delay:

Connection Load Balancing Goal: LONG

Runtime Load Balancing Goal: NONE

TAF policy specification: NONE

Edition:

Pluggable database name:

Maximum lag time: ANY

SQL Translation Profile:

Retention: 86400 seconds

Replay Initiation Time: 300 seconds

Session State Consistency:

GSM Flags: 0

Service is enabled

Preferred instances: orcl1,orcl2

Available instances:

Service name: orcl_int_svc

Server pool:

Cardinality: 2

Disconnect: false

Service role: PRIMARY

Management policy: AUTOMATIC

DTP transaction: false

AQ HA notifications: false

Global: false

Commit Outcome: false

Failover type:

Failover method:

TAF failover retries:

TAF failover delay:

Connection Load Balancing Goal: LONG

Runtime Load Balancing Goal: NONE

TAF policy specification: NONE

Edition:

Pluggable database name:

Maximum lag time: ANY

SQL Translation Profile:

Retention: 86400 seconds

Replay Initiation Time: 300 seconds

Session State Consistency:

GSM Flags: 0

Service is enabled

Preferred instances: orcl1,orcl2

Available instances:

Button… Read More

Oracle : Adding new table to existing GoldenGate Replication without downtime

4 years ago by Binnary Ajay KumarAdd new table to existing GoldenGate Replication

Adding new table to existing GoldenGate Replication In this article, we will add a new table in existing GG replication setup without downtime using filter (@GETENV (‘TRANSACTION’,’CSN’) > scn number); Source : GG11 Target : GG12 Source : Connect and Login to the ggsci

[oracle@GG11 19c12]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 19.1.0.0.1 OGGCORE_19.1.0.0.0_PLATFORMS_190524.2201_FBO
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved.

[oracle@GG11 19c12]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 19.1.0.0.1 OGGCORE_19.1.0.0.0_PLATFORMS_190524.2201_FBO

Operating system character set identified as UTF-8.

Oracle : Shell script for RMAN backup of Standby Database

4 years ago by Binnary Ajay KumarOracle DB Admin

Oracle : Shell script for RMAN backup of Standby Database In this article, we wrote an automation job for Shell script for RMAN backup of Standby Database The below script will be initiating the RMAN backup on the standby database: RMAN Script : [oracle@tewnposqa1 scripts]$… Read More