Configure Oracle DBFS or ACFS on Exadata
Create the dbs_group file for dcli
As the user root, create a text file in the home directory of the root user called dbs_group. This file will contain the names of both the X5-2 compute nodes.
We will be using the DCLI utility to run commands on all compute nodes in the Exadata box and this file will be used for that purpose when we run the ‘dcli –g’ command.
[root@exdb1db01 ~]# dcli -g dbs_group -l root hostname
exdb1db01: exdb1db01.gavin.com.au
exdb1db02: exdb1db02.gavin.com.au
1.1 Add the oracle user to the fuse group
[root@exdb1db01 ~]# dcli -g ~/dbs_group -l root usermod -a -G fuse oracle
1.2 Add the user_allow_other option to the fuse.conf file
root@exdb1db01 ~]# dcli -g ~/dbs_group -l root "echo user_allow_other > /etc/fuse.conf"
[root@exdb1db01 ~]# dcli -g ~/dbs_group -l root chmod 644 /etc/fuse.conf
Note – on the Exadata servers, the required fuse RPM packages are installed by default.
1.3 Create the mount points and give appropriate permissions
On both compute nodes we will create mount points which will be used to mount the DBFS file system.
Since the objective is to have multiple mount points where each mount point is dedicated to separate database or environment, we will create the mount point with the naming convention /dbfs/
Change the ownership of the mount points to the oracle user
dcli -g ~/dbs_group -l root mkdir /dbfs/dev2
dcli -g ~/dbs_group -l root chown oracle:oinstall /dbfs/dev2
1.4 Create tablespace and users
As the user SYS, we will create two tablespaces which will be used to store the LOB objects associated with the DBFS file system.
We will create the dbfs_gg_dirtmp tablespace with the recommended NOLOGGING attribute as it will be used to store the contents of the GoldenGate dirtmp directory.
Note: The size of the tablespace will depend on the amount of trail files which are expected to be generated as well as the required retention period for those trail files.
While the example shows the DBFS_DG ASM disk group being used for the hosting the DBFS related tablespaces, any ASM disk group with the required amount of free disk space can be used.
The DBFS_USER Oracle database user will be the owner of the DBFS related database objects and we create the user and grant the appropriate privileges especially the DBFS_ROLE database role.
create bigfile tablespace dbfs_gg_dirsrc
datafile '+DBFS_DG' size 32g autoextend on next 2g
LOGGING EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;
create bigfile tablespace dbfs_gg_dirtmp
datafile '+DBFS_DG' size 10g autoextend on next 2g
NOLOGGING EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;
create user dbfs_user identified by Oracle#123
default tablespace dbfs_gg_dirsrc
temporary tablespace temp
quota unlimited on dbfs_gg_dirsrc
quota unlimited on dbfs_gg_dirtmp;
GRANT create session, create table, create view,create procedure, dbfs_role TO dbfs_user;
1.5 Create the DBFS file system
We will next connect as the DBFS_USER and run the dbfs_create_filesystem.sql script to create the necessary DBFS related database objects.
The dbfs_create_filesystem.sql takes two parameters – the tablespace_name and the DBFS file system name.
SQL> conn dbfs_user/
Connected.
SQL> @?/rdbms/admin/dbfs_create_filesystem dbfs_gg_dirsrc ogg_dev2
No errors.
--------
CREATE STORE:
begin dbms_dbfs_sfs.createFilesystem(store_name => 'FS_OGG_DEV2', tbl_name =>
'T_OGG_DEV2', tbl_tbs => 'dbfs_gg_dirsrc', lob_tbs => 'dbfs_gg_dirsrc',
do_partition => false, partition_key => 1, do_compress => false, compression =>
'', do_dedup => false, do_encrypt => false); end;
--------
REGISTER STORE:
begin dbms_dbfs_content.registerStore(store_name=> 'FS_OGG_DEV2',
provider_name => 'sample1', provider_package => 'dbms_dbfs_sfs'); end;
--------
MOUNT STORE:
begin dbms_dbfs_content.mountStore(store_name=>'FS_OGG_DEV2',
store_mount=>'ogg_dev2'); end;
--------
CHMOD STORE:
declare m integer; begin m := dbms_fuse.fs_chmod('/ogg_dev2', 16895); end;
No errors.
SQL>@?/rdbms/admin/dbfs_create_filesystem dbfs_gg_dirtmp ogg_dirtmp_dev2
No errors.
--------
CREATE STORE:
begin dbms_dbfs_sfs.createFilesystem(store_name => 'FS_OGG_DIRTMP_DEV2',
tbl_name => 'T_OGG_DIRTMP_DEV2', tbl_tbs => 'dbfs_gg_dirtmp', lob_tbs =>
'dbfs_gg_dirtmp', do_partition => false, partition_key => 1, do_compress =>
false, compression => '', do_dedup => false, do_encrypt => false); end;
--------
REGISTER STORE:
begin dbms_dbfs_content.registerStore(store_name=> 'FS_OGG_DIRTMP_DEV2',
provider_name => 'sample1', provider_package => 'dbms_dbfs_sfs'); end;
--------
MOUNT STORE:
begin dbms_dbfs_content.mountStore(store_name=>'FS_OGG_DIRTMP_DEV2',
store_mount=>'ogg_dirtmp_dev2'); end;
--------
CHMOD STORE:
declare m integer; begin m := dbms_fuse.fs_chmod('/ogg_dirtmp_dev2', 16895);
end;
No errors.
1.6 Verify the DBFS LOB segment attributes
SQL> SELECT table_name, segment_name, logging, cache
2 FROM dba_lobs WHERE tablespace_name like 'DBFS%';
TABLE_NAME SEGMENT_NAME LOGGING CACHE
------------------------------ ------------------------------ ------- ----------
T_OGG_DEV2 LOB_SFS$_FST_1 YES NO
T_OGG_DIRTMP_DEV2 LOB_SFS$_FST_11 NO NO
1.7 Edit and customize the Oracle supplied mount-dbfs.sh script
Download the file mount-dbfs-20160215.zip from the MOS note 1054431.1 (Configuring DBFS on Oracle Exadata Database Machine).
Copy the file to a temporary directory on one of the database compute nodes and as the user root, extract the file.
We will now have two files – mount-dbfs.conf and mount-dbfs.sh.
Copy the mount-dbfs.sh to mount-dbfs_
[root@exdb1db01 ~]# cd /tmp
[root@exdb1db01 tmp]# cp mount-dbfs.sh mount-dbfs_dev2.sh
[root@exdb1db01 tmp]# cp mount-dbfs.conf mount-dbfs_dev2.conf
Edit the mount-dbfs_
[root@exdb1db01 tmp]# vi mount-dbfs_dev2.sh
### Ensure that when multiple mounts are used, there are separate copies
### of mount-dbfs.sh that reference separate CONFIG file pathnames
CONFIG=/etc/oracle/mount-dbfs_dev2.conf
1.8 Edit and customize the Oracle supplied mount-dbfs.conf script
Change the values for :
• DBNAME
• MOUNT_POINT
• DBFS_USER
• ORACLE_HOME
• GRID_HOME
• DBFS_PASSWORD
### Database name for the DBFS repository as used in "srvctl status database -d $DBNAME"
### If using PDB/CDB, this should be set to the CDB name
### Database name for the DBFS repository as used in "srvctl status database -d $DBNAME"
### If using PDB/CDB, this should be set to the CDB name
DBNAME=DEV2
### Mount point where DBFS should be mounted
MOUNT_POINT=/dbfs/dev2
### Username of the DBFS repository owner in database $DBNAME
DBFS_USER=dbfs_user
### RDBMS ORACLE_HOME directory path
ORACLE_HOME=/u01/app/oracle/product/11.2.0/shieldnp_1
### GRID HOME directory path
GRID_HOME=/u01/app/12.1.0/grid_1
###########################################
### If using password-based authentication, set these
###########################################
### This is the plain text password for the DBFS_USER user
DBFS_PASSWD=Oracle#123
1.9 Copy the modified files to $GRID_HOME/crs/script as well as /etc/oracle and grant appropriate privileges
dcli -g ~/dbs_group -l root -d /u01/app/12.1.0/grid_1/crs/script -f /tmp/mount-dbfs_dev2.sh
dcli -g ~/dbs_group -l root chown oracle:oinstall /u01/app/12.1.0/grid_1/crs/script/mount-dbfs_dev2.sh
dcli -g ~/dbs_group -l root chmod 750 /u01/app/12.1.0/grid_1/crs/script/mount-dbfs_dev2.sh
dcli -g ~/dbs_group -l root -d /etc/oracle -f /tmp/mount-dbfs_dev2.conf
dcli -g ~/dbs_group -l root chown oracle:oinstall /etc/oracle/mount-dbfs_dev2.conf
dcli -g ~/dbs_group -l root chmod 640 /etc/oracle/mount-dbfs_dev2.conf
1.10 Create the script for mounting the DBFS File System
We will create the add-dbfs-resource _
Note that the add-dbfs-resource script will be sourcing the customized mount-dbfs_
[root@exdb1db01 tmp]# cd /u01/app/12.1.0/grid_1/crs/script
[root@exdb1db01 script]# vi add-dbfs-resource_dev2.sh
##### start script add-dbfs-resource_dev2.sh
#!/bin/bash
ACTION_SCRIPT=/u01/app/12.1.0/grid_1/crs/script/mount-dbfs_dev2.sh
RESNAME=dbfs_mount_dev2
DBNAME=DEV2
DBNAMEL=`echo $DBNAME | tr A-Z a-z`
ORACLE_HOME=/u01/app/oracle/product/11.2.0/shieldnp_1
PATH=$ORACLE_HOME/bin:$PATH
export PATH ORACLE_HOME
/u01/app/12.1.0/grid_1/bin/crsctl add resource $RESNAME \
-type local_resource \
-attr "ACTION_SCRIPT=$ACTION_SCRIPT, \
CHECK_INTERVAL=30,RESTART_ATTEMPTS=10, \
START_DEPENDENCIES='hard(ora.$DBNAMEL.db)pullup(ora.$DBNAMEL.db)',\
STOP_DEPENDENCIES='hard(ora.$DBNAMEL.db)',\
SCRIPT_TIMEOUT=300"
##### end script add-dbfs-resource_dev2.sh
Change the ownership of the script to oracle
[root@exdb1db01 script]# chown oracle:oinstall add-dbfs-resource_dev2.sh
1.11 As the OS user oracle run the add-dbfs-resource script to create the resource
[root@exdb1db01 script]# su - oracle
[oracle@exdb1db01 ~]$ cd /u01/app/12.1.0/grid_1/crs/script
[oracle@exdb1db01 script]$ ./add-dbfs-resource_dev2.sh
1.12 As oracle start the resource using crsctl – this will mount the DBFS file system
[oracle@exdb1db01 ~]$ cd /u01/app/12.1.0/grid_1/bin
[oracle@exdb1db01 bin]$ ./crsctl start resource dbfs_mount_dev2
CRS-2672: Attempting to start 'dbfs_mount_dev2' on 'exdb1db01'
CRS-2672: Attempting to start 'ora.dev2.db' on 'exdb1db02'
CRS-2676: Start of 'dbfs_mount_dev2' on 'exdb1db01' succeeded
CRS-2676: Start of 'dbfs_mount_dev2' on 'exdb1db02' succeeded
1.13 Check the status of the resource
[oracle@exdb1db01 bin]$ ./crsctl stat res dbfs_mount_dev2
NAME=dbfs_mount_dev2
TYPE=local_resource
TARGET=ONLINE , ONLINE
STATE=ONLINE on exdb1db01, ONLINE on exdb1db02
[oracle@exdb1db01 bin]$ exit
logout
1.14 As root create the Application VIP
[root@exdb1db01 script]# cd /u01/app/12.1.0/grid_1/bin
[root@exdb1db01 bin]# ./appvipcfg create -network=1 -ip=10.100.24.28 -vipname=ogg_vip_dev2 -user=root
[root@exdb1db01 bin]# ./crsctl setperm resource ogg_vip_dev2 -u user:oracle:r-x
[root@exdb1db01 bin]# ./crsctl setperm resource ogg_vip_dev2 -u user:grid:r-x
[root@exdb1db01 bin]# ./crsctl start resource ogg_vip_dev2
CRS-2672: Attempting to start 'ogg_vip_dev2' on 'exdb1db02'
CRS-2676: Start of 'ogg_vip_dev2' on 'exdb1db02' succeeded
We can see that the VIP is running on exdb1db02 -we can relocate it to exdb1db01
[root@exdb1db01 bin]# ./crsctl relocate resource ogg_vip_dev2
CRS-2673: Attempting to stop 'ogg_vip_dev2' on 'exdb1db02'
CRS-2677: Stop of 'ogg_vip_dev2' on 'exdb1db02' succeeded
CRS-2672: Attempting to start 'ogg_vip_dev2' on 'exdb1db01'
CRS-2676: Start of 'ogg_vip_dev2' on 'exdb1db01' succeeded
Now check the status of the resource – we can see it running on exdb1db01
[root@exdb1db01 bin]# ./crsctl status resource ogg_vip_dev2
NAME=ogg_vip_dev2
TYPE=app.appvipx.type
TARGET=ONLINE
STATE=ONLINE on exdb1db01
1.15 Check if the DBFS file systems for each database environment are mounted and directories are present
[root@exdb1db01 bin]# df -k |grep dbfs
dbfs-dbfs_user@:/ 56559616 232 56559384 1% /dbfs_dev2
[root@exdb1db01 bin]# cd /dbfs_dev2/
[root@exdb1db01 dbfs_dev2]# ls -l
total 0
drwxrwxrwx 3 root root 0 Feb 25 11:56 ogg_dev2
drwxrwxrwx 3 root root 0 Feb 25 11:57 ogg_dirtmp_dev2
2 Configure Grid Infrastructure Agent
2.1 Create the directories on the DBFS file system
[oracle@exdb1db01 ogg_dev2pd]$ pwd
/dbfs/dev2pd/ogg_dev2pd
[oracle@exdb1db01 ]$ mkdir dirpcs
[oracle@exdb1db01 ]$ mkdir dirchk
[oracle@exdb1db01 ]$ mkdir dirdat
[oracle@exdb1db01 ]$ mkdir dirprm
[oracle@exdb1db01 ]$ mkdir dircrd
[oracle@exdb1db01 ]$ mkdir BR
[oracle@exdb1db01 dev2pd]$ cd ogg_dirtmp_dev2pd
[oracle@exdb1db01 ogg_dirtmp_dev2pd]$ pwd
/dbfs/dev2pd/ogg_dirtmp_dev2pd
[oracle@exdb1db01 ogg_dirtmp]$ mkdir dirtmp
2.2 On each compute node rename the existing directories in the GoldenGate software home
[oracle@exdb1db01 dev2]$ mkdir BR
[oracle@exdb1db01 dev2]$ mv dirchk dirchk.bkp
[oracle@exdb1db01 dev2]$ mv dirdat dirdat.bkp
[oracle@exdb1db01 dev2]$ mv dirpcs dirpcs.bkp
[oracle@exdb1db01 dev2]$ mv dirprm dirprm.bkp
[oracle@exdb1db01 dev2]$ mv dircrd dircrd.bkp
[oracle@exdb1db01 dev2]$ mv dirtmp dirtmp.bkp
2.3 Create the symbolic links
[oracle@exdb1db01 dev2]$ ln -s /dbfs/dev2pd/ogg_dev2pd/dirdat dirdat
[oracle@exdb1db01 dev2]$ ln -s /dbfs/dev2pd/ogg_dev2pd/dirchk dirchk
[oracle@exdb1db01 dev2]$ ln -s /dbfs/dev2pd/ogg_dev2pd/dirpcs dirpcs
[oracle@exdb1db01 dev2]$ ln -s /dbfs/dev2pd/ogg_dev2pd/dirprm dirprm
[oracle@exdb1db01 dev2]$ ln -s /dbfs/dev2pd/ogg_dev2pd/BR BR
[oracle@exdb1db01 dev2]$ ln -s /dbfs/dev2pd/ogg_dev2pd/dircrd dircrd
[oracle@exdb1db01 dev2]$ ln -s /dbfs/dev2pd/ogg_dirtmp_dev2pd /dirtmp dirtmp
2.4 Download Oracle Grid Infrastructure Agent
From the URL below download the file: xagpack_7b.zip
http://www.oracle.com/technetwork/database/database-technologies/clusterware/downloads/index.html
2.5 Copy the downloaded xagpack_7b.zip file to Grid user $HOME and unzip
[grid@exdb1db01 ~]$ ls
xagpack_7b.zip
[grid@exdb1db01 ~]$ unzip xagpack_7b.zip
Archive: xagpack_7b.zip
creating: xag/
inflating: xag/xagsetup.bat
creating: xag/lib/
inflating: xag/lib/facility.lis
inflating: xag/agcommon.pm
inflating: xag/agjdeas.pm
creating: xag/bin/
inflating: xag/bin/oerr.pl
inflating: xag/xagsetup.sh
…
…
…
inflating: xag/mesg/xagus.be
inflating: xag/mesg/xagus.msg
inflating: xag/mesg/xagus.msb
inflating: xag/agmysqlmonas.pm
inflating: xag/readme.txt
inflating: xag/agwl.pm
2.6 Two directories will be created - xag and xagent
[grid@exdb1db01 xag]$ pwd
/home/grid/xag
[grid@exdb1db01 xag]$ cd ..
[grid@exdb1db01 ~]$ ls
xag xagent xagpack_7b.zip
2.7 Run the xagsetup.sh script (as the Grid Infrastructure owner)
Note – this will install the Grid Infrastructure Agent files in the xagent directory (on both compute nodes)
[grid@exdb1db01 xag]$ ./xagsetup.sh --install --directory /u01/app/grid/xagent --all_nodes
Installing Oracle Grid Infrastructure Agents on: exdb1db01
Installing Oracle Grid Infrastructure Agents on: exdb1db02
If we try and install the Grid Infrastructure Agents under the $GRID_HOME we will see an error as shown below:
[grid@exdb1db01 xag]$ ./xagsetup.sh --install --directory /u01/app/12.1.0/grid_1/xagent --all_nodes
Installation directory cannot be under Clusterware home.
2.8 As oracle we run the AGCTL command to create the GoldenGate resource
[root@exdb1db01 bin]# su - oracle
[oracle@exdb1db01 ~]$ cd /u01/app/grid/xagent/bin
[oracle@exdb1db01 bin]$ ./agctl add goldengate ogg_dev2 --gg_home /u01/app/oracle/product/gg12.2/dev2 --instance_type source --nodes exdb1db01,exdb1db02 --vip_name ogg_vip_dev2 --filesystems dbfs_mount_dev2pd --databases ora.dev2pd.db --oracle_home /u01/app/oracle/product/11.2.0/shieldnp_1
2.9 Start and Stop Goldengate using AGCTL
[oracle@exdb1db01 bin]$ ./agctl status goldengate ogg_dev2
Goldengate instance 'ogg_dev2' is not running
[oracle@exdb1db01 bin]$ ./agctl start goldengate ogg_dev2
[oracle@exdb1db01 bin]$ ./agctl status goldengate ogg_dev2
Goldengate instance 'ogg_dev2' is running on exdb1db01
If we check via GGSCI, we can see the manager process is now up and running on compute node exdb1db01
[oracle@exdb1db01 bin]$ cd -
/u01/app/oracle/product/gg12.2/dev2
[oracle@exdb1db01 dev2]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Dec 12 2015 00:54:38
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.
GGSCI (exdb1db01.gavin.com.au) 1> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
Note that manager is stopped on compute node exdb1db02
GGSCI (exdb1db02.gavin.com.au) 3> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER STOPPED
2.10 Relocate GoldenGate using AGCTL
[oracle@exdb1db01 bin]$ ./agctl relocate goldengate ogg_dev2
[oracle@exdb1db01 bin]$ ./agctl status goldengate ogg_dev2
Goldengate instance 'ogg_dev2' is running on exdb1db02
Now manager is running on exdb1db02
GGSCI (exdb1db02.gavin.com.au) 3> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
#!/bin/ksh
############################################
# Name: ggs_lag.ksh #
# PURPOSE: TO MONITOR LAG OF GOLDEN GATE #
# NOTE: THIS SCRIPT CALLS ggs.ksh #
# THIS SCRIPT NOTIFY IF LAG IS MORE THEN 30 MIN #
# ONLY FOR FOR EXT AND PMP PROCESS GROUP #
###########################################
export GGATE=/opt/oracle/u01/app/oracle/ggs
alias gate='clear;cd $GGATE;./ggsci'
export PATH=/opt/oracle/u01/app/oracle/ggs:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib:/opt/oracle/u01/app/oracle/ggs
LOGDIR=/export/home/oracle/dba_scripts/ggs/logs
EMAILFile=$LOGDIR/ggs_email.log
BOX=$(uname -a | awk '{print $2}')
##########################################################################
# RUNNING SCRIPT TO GET GOLDEN GATE INFORMATION #
##########################################################################
/export/home/oracle/dba_scripts/ggs/ggs.ksh > $LOGDIR/ggs_1.log
#to check when script was running
echo "script ggsksh completed from ggs_lag at `date`" >> /tmp/ggs_check.log
##################################################################################
## FORMATTING INFORMATION: change cut -d":" -f 1,4 TO cut -d":" -f 1,2 ##
## to get information about lag instead of checkpoint ##
## this command grep only EXT_ and PMP_ if you need more pattern ##
## if you need more pattern to be grep'd please add to '(EXT_|PMP_|pattern)' ##
##################################################################################
cat $LOGDIR/ggs_1.log|egrep -i '(EXT_|PMP_)'|cut -d":" -f 1,2,4| tr ":" " "|tr -s '[:space:]'|cut -d" " -f1,2,3,4,5,6 > $LOGDIR/ggs_2.log
# uncomment below command if you want to get lag and checkpoint both information #
#cat $LOGDIR/ggs_1.log|egrep -i '(EXT_|PMP_|DART)'|cut -d":" -f 1,2,4| tr ":" " "|tr -s '[:space:]'|cut -d" " -f1,2,3,4,5,6 > $LOGDIR/ggs_2.log
# uncomment below command if you want to get lag information about running process #
#cat $LOGDIR/ggs_1.log|grep RUNNING|cut -d":" -f 1,2,4| tr ":" " "|tr -s '[:space:]'|cut -d" " -f1,2,3,4,5,6 >
$LOGDIR/ggs_2.log
##########################################################################
## CHECKING FOR LAG MORE THEN 30 MIN FOR ABENDED PROCESS ##
##########################################################################
##########################################################################
## CHECKING FOR LAG MORE THEN 30 MIN FOR ABENDED PROCESS ##
##########################################################################
awk '{if ( $4 > 00 || $5 >=30 ) {print $1 " " $3 " HAS LAG of " $4" hour " $5 " min -- at -- " d "\n"} else {print "NO LAG FOR " $3 " " d >> "/tmp/ggs_lag_fine.log" }}' d="$(date)" $LOGDIR/ggs_2.log > $LOGDIR/ggs_email.log
# uncomment below command if you want to get lag and checkpoint both information #
#awk '{if ($4 >=30 || $5>=30 ) {print $1 " " $3 " has lag of "$4" min with checkpoint of "$5" min -- at -- " d "\n"} else {print "NO LAG FOR " $3 " "d > "/tmp/ggs_lag_fine.log" }}' d="$(date)" $LOGDIR/ggs_2.log > $LOGDIR/ggs_email.log
##########################################################
## SENDING EMAIL IF ERRORS ARE IN LOGFILE ###
##########################################################
if [ -s $EMAILFile ]; then
#echo "ERRORS FOUND"
mailx -s "GG LAG FOUND ON: $BOX" your.email@gmail.com team.email@gmail.com < $EMAILFile else cat /dev/null > $EMAILFile
#echo "ERRORS NOT FOUND"
fi
################# SCRIPT END ######################
#!/bin/ksh
#########################################
#Name: ggs.ksh #
#THIS SCRIPT WILL CALLED BY ggs_lag.ksh #
#########################################
#alias gate='clear;cd $GGATE;./ggsci'
echo "ggsksh started `date`" >> /tmp/ggs_check.log
export GGATE=/opt/oracle/u01/app/oracle/ggs
alias gate='clear;cd $GGATE;ggsci'
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib:/opt/oracle/u01/app/oracle/ggs
export PATH=/opt/oracle/u01/app/oracle/ggs:$PATH
#cd $GGATE
/opt/oracle/u01/app/oracle/ggs/ggsci <<>
info all
exit
EOF
echo "ggsksh completed at `date` " >> /tmp/ggs_check.log
Here is a great resource as the A-Team within Oracle is the top-level technical resources as it relates to GoldenGate. This is a link to many of their published articles. A-Team Index of articles
Oracle GoldenGate Extract Recovery
Oracle GoldenGate Best Practices: Configuring Downstream Integrated Extract
GoldenGate Cloud Service (GGCS) Replication from Amazon RDS Oracle Database to Oracle DBCS
Here is a sample GoldenGate environment file, feel free to comment about changes or additions
# This should be a .ggora12_env file
# This variable could be used to identify a site
export GG_SITE=01
# This could be used to identify a location
export GG_LOC=CO
# if there is only one DB related to this GG home,
# set NEW_ORACLE_SID to avoid constant switch between a DB env and its GG env
export NEW_ORACLE_SID=MRC01D011; . ~/.std_profile
# otherwise, set NEW_ORACLE_SID to dummy if there are multiple replicated databases related to same GG home
#export NEW_ORACLE_SID=dummy; . ~/.std_profile
# uncomment next three lines if ORACLE_SID is dummy
# export ORACLE_HOME=/oracle/product/11.2.0/db_1
export LD_LIBRARY_PATH=$ORACLE_HOME/lib/$JAVA_HOME/lib/amd64/server:$LD_LIBRARY_PATH
export PATH=$ORACLE_HOME/bin:$PATH
export GGS_HOME=/oracle/product/gg12.1
export PATH=$GGS_HOME:$PATH
# For OEM12c GG monitoring
export JAVA_HOME=/oracle/product/12.1.0/oem_1/agent/core/12.1.0.2.0/jdk/jre
export PATH=$JAVA_HOME/bin:$PATH
# export LD_LIBRARY_PATH=$JAVA_HOME/lib/amd64/server:$LD_LIBRARY_PATH
#############################################
## For GI Agent (XAG)
##############################################
export XAG_HOME=/oracle/product/xag71
export PATH=$XAG_HOME/bin:$PATH
PS1="\\
\${PWD} \\
\${SNAME} [\${ORACLE_SID}] [GG12_site$GG_SITE"_"$GG_LOC]-> "
export PS1
alias ggsora12='. $HOME/.ggsora12_env;cd $GGS_HOME'
alias xag='. $HOME/.ggsora12_env;cd $XAG_HOME'
alias ggstatus='$XAG_HOME/bin/agctl status goldengate testdb01_oggapp'
The heartbeat function in 12.2 is more automated than in previous versions.
Heartbeat Table Issue 12.2 Resolved
GOLDENGATE HA MAA RAC ACFS XAG
#!/bin/bash
###############################
# determine the OS type
###############################
OSNAME=`uname`
case "$OSNAME" in
"SunOS")
echo "OSNAME = $OSNAME"
;;
Linux")
echo "OSNAME = $OSNAME"
;;
"*")
echo "This script has not been verified on $OSNAME"
exit 1
;;
esac
<div class="line number20 index19 alt1"></div>
<div class="line number21 index20 alt2">###############################</code></div>
<div class="line number22 index21 alt1"><code class="plain plain"># set the temp file</code></div>
<div class="line number23 index22 alt2"><code class="plain plain">###############################</code></div>
<div class="line number24 index23 alt1"><code class="plain plain">TMPFILE=/tmp/pmem.tmp</code></div>
<div class="line number25 index24 alt2"><code class="plain plain">if [ -f $TMPFILE ]</code></div>
<div class="line number26 index25 alt1"><code class="plain plain">then</code></div>
<div class="line number27 index26 alt2"><code class="plain spaces"> </code><code class="plain plain">rm -f $TMPFILE</code></div>
<div class="line number28 index27 alt1"><code class="plain plain">fi</code></div>
<div class="line number29 index28 alt2"></div>
<div class="line number30 index29 alt1"><code class="plain plain">################################</code></div>
<div class="line number31 index30 alt2"><code class="plain plain"># loop over the gg process types</code></div>
<div class="line number32 index31 alt1"><code class="plain plain">################################</code></div>
<div class="line number33 index32 alt2"><code class="plain plain">PROCESSES="extract replicat"</code></div>
<div class="line number34 index33 alt1"></div>
<div class="line number35 index34 alt2"><code class="plain plain">for PROCESS in $PROCESSES</code></div>
<div class="line number36 index35 alt1"><code class="plain plain">do</code></div>
<div class="line number37 index36 alt2"><code class="plain spaces"> </code><code class="plain plain">FLAG=""</code></div>
<div class="line number38 index37 alt1"><code class="plain spaces"> </code><code class="plain plain">FLAG=`ps -ef | grep $PROCESS`</code></div>
<div class="line number39 index38 alt2"><code class="plain spaces"> </code><code class="plain plain">if [ -z "FLAG" ]</code></div>
<div class="line number40 index39 alt1"><code class="plain spaces"> </code><code class="plain plain">then</code></div>
<div class="line number41 index40 alt2"><code class="plain spaces"> </code><code class="plain plain">echo "No $PROCESS processes found"</code></div>
<div class="line number42 index41 alt1"><code class="plain spaces"> </code><code class="plain plain">else</code></div>
<div class="line number43 index42 alt2"><code class="plain spaces"> </code><code class="plain plain">echo</code></div>
<div class="line number44 index43 alt1"><code class="plain spaces"> </code><code class="plain plain">echo "#####################################"</code></div>
<div class="line number45 index44 alt2"><code class="plain spaces"> </code><code class="plain plain">echo "# Individual $PROCESS Process Usage #"</code></div>
<div class="line number46 index45 alt1"><code class="plain spaces"> </code><code class="plain plain">echo "#####################################"</code></div>
<div class="line number47 index46 alt2"><code class="plain spaces"> </code><code class="plain plain">case "$OSNAME" in</code></div>
<div class="line number48 index47 alt1"><code class="plain spaces"> </code><code class="plain plain">"Linux")</code></div>
<div class="line number49 index48 alt2"><code class="plain spaces"> </code><code class="plain plain">ps -C $PROCESS -O rss > $TMPFILE</code></div>
<div class="line number50 index49 alt1"><code class="plain spaces"> </code><code class="plain plain">cat $TMPFILE | grep $PROCESS | awk '{print $2/1024, "MB", $12}' | sort -k 2</code></div>
<div class="line number51 index50 alt2"><code class="plain spaces"> </code><code class="plain plain">;;</code></div>
<div class="line number52 index51 alt1"><code class="plain spaces"> </code><code class="plain plain">"SunOS")</code></div>
<div class="line number53 index52 alt2"><code class="plain spaces"> </code><code class="plain plain">ps -efo vsz,uid,pid,ppid,pcpu,args | grep -v grep | grep $PROCESS > $TMPFILE</code></div>
<div class="line number54 index53 alt1"><code class="plain spaces"> </code><code class="plain plain">cat $TMPFILE | grep $PROCESS | awk '{print $1/1024, "MB", $8}' | sort -k 2</code></div>
<div class="line number55 index54 alt2"><code class="plain spaces"> </code><code class="plain plain">;;</code></div>
<div class="line number56 index55 alt1"><code class="plain spaces"> </code><code class="plain plain">"*")</code></div>
<div class="line number57 index56 alt2"><code class="plain spaces"> </code><code class="plain plain">echo "This script has not been verified on $OSNAME"</code></div>
<div class="line number58 index57 alt1"><code class="plain spaces"> </code><code class="plain plain">exit 1</code></div>
<div class="line number59 index58 alt2"><code class="plain spaces"> </code><code class="plain plain">;;</code></div>
<div class="line number60 index59 alt1"><code class="plain spaces"> </code><code class="plain plain">esac</code></div>
<div class="line number61 index60 alt2"><code class="plain spaces"> </code><code class="plain plain">rm -f $TMPFILE</code></div>
<div class="line number62 index61 alt1"></div>
<div class="line number63 index62 alt2"><code class="plain spaces"> </code><code class="plain plain">echo</code></div>
echo "#####################################"</code></div>
echo "# Total $PROCESS Process Usage #"</code></div>
echo "#####################################"</code></div>
<div class="line number67 index66 alt2"><code class="plain spaces"> </code><code class="plain plain">case "$OSNAME" in</code></div>
<div class="line number68 index67 alt1"><code class="plain spaces"> </code><code class="plain plain">"Linux")</code></div>
<div class="line number69 index68 alt2"><code class="plain spaces"> </code><code class="plain plain">ps -C $PROCESS -O rss > $TMPFILE</code></div>
<div class="line number70 index69 alt1"><code class="plain spaces"> </code><code class="plain plain">cat $TMPFILE | grep $PROCESS | awk '{count ++; sum=sum+$2; } END \</code></div>
<div class="line number71 index70 alt2"><code class="plain spaces"> </code><code class="plain plain">{ print "Number of processes =",count; \</code></div>
<div class="line number72 index71 alt1"><code class="plain spaces"> </code><code class="plain plain">print "AVG Memory usage/process =",sum/1024/count, "MB"; \</code></div>
<div class="line number73 index72 alt2"><code class="plain spaces"> </code><code class="plain plain">print "Total memory usage =", sum/1024, " MB"}'</code></div>
<div class="line number74 index73 alt1"><code class="plain spaces"> </code><code class="plain plain">;;</code></div>
<div class="line number75 index74 alt2"><code class="plain spaces"> </code><code class="plain plain">"SunOS")</code></div>
<div class="line number76 index75 alt1"><code class="plain spaces"> </code><code class="plain plain">ps -efo vsz,uid,pid,ppid,pcpu,comm | grep -v grep | grep $PROCESS > $TMPFILE</code></div>
<div class="line number77 index76 alt2"><code class="plain spaces"> </code><code class="plain plain">cat $TMPFILE | awk '{count ++; sum=sum+$1; } END \</code></div>
<div class="line number78 index77 alt1"><code class="plain spaces"> </code><code class="plain plain">{ print "Number of processes =",count; \</code></div>
<div class="line number79 index78 alt2"><code class="plain spaces"> </code><code class="plain plain">print "AVG Memory usage/process =",sum/1024/count, "MB"; \</code></div>
<div class="line number80 index79 alt1"><code class="plain spaces"> </code><code class="plain plain">print "Total memory usage =", sum/1024, " MB"}'</code></div>
<div class="line number81 index80 alt2"><code class="plain spaces"> </code><code class="plain plain">;;</code></div>
<div class="line number82 index81 alt1"><code class="plain spaces"> </code><code class="plain plain">"*")</code></div>
<div class="line number83 index82 alt2"><code class="plain spaces"> </code><code class="plain plain">echo "This script has not been verified on $OSNAME"</code></div>
<div class="line number84 index83 alt1"><code class="plain spaces"> </code><code class="plain plain">exit 1</code></div>
<div class="line number85 index84 alt2"><code class="plain spaces"> </code><code class="plain plain">;;</code></div>
<div class="line number86 index85 alt1"><code class="plain spaces"> </code><code class="plain plain">esac</code></div>
<div class="line number87 index86 alt2"><code class="plain spaces"> </code><code class="plain plain">rm -f $TMPFILE</code></div>
fi
done
exit 0
When working with Oracle GoldenGate, you might need to refer to the checkpoints that are made by a process. Checkpoints save the state of the process for recovery purposes. Extracts and Replicats use checkpoints.
Extract checkpoint positions are composed of read checkpoints in the data source and write checkpoints in the trail. The following is a sampling of checkpoint information displayed with the INFO EXTRACT command with the SHOWCH option. In this case, the data source is an Oracle RAC database cluster, so there is thread information included in the output. You can view past checkpoints by specifying the number of them that you want to view after the SHOWCH argument.