Category Archives: GoldenGate

Configure Oracle DBFS or ACFS on Exadata

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_.sh
[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_.sh script to reference the customized CONFIG file

[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 _.sh script. This script will be used to create the clusterware resource for mounting the DBFS file system.
Note that the add-dbfs-resource script will be sourcing the customized mount-dbfs_.sh script which we had created earlier.

[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

GoldenGate Lag Script


#!/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



##################################################################################
## FORMATING INFORMATION: change cut -d":" -f 1,4 TO cut -d":" -f 1,2 ##
## to getinformation about lag instead of checkpoint ##
## this command grep only EXT_ and PMP_ if you need more pattern ##
## if you need more pattern to be greped 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

&nbsp;

################# SCRIPT END ######################

&nbsp;

&nbsp;

&nbsp;

&nbsp;

#!/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

&nbsp;

&nbsp;

&nbsp;

GoldenGate gg_common.ksh script library

This is the script library for GoldenGate

Current development is taking place on

Function List
gg_cr_env – Create the environment file for a particular gg server, this will create an environment file to setup GG
gg_status


#!/bin/ksh
############################################################################
# Script Name..: gg_common.ksh
# Description..: Common GG functions for inclusion to all scripts
# Author.......: Michael Culp
# Date.........: 10/25/2017
# Version......: .81
# Modified By..: Michael Culp
# Date Modified: 05/18/2017
# Comments.....: Change this library to make mods to all functions
# Schema owner.:
# Dependent on.: N/A
# Script type..: function library
# Function list:
############################################################################

db_fixes_ver ()
{

clear
echo
echo "------------------------------"
echo "  Databases fixes by version  "
echo "------------------------------"
echo

sqlplus -s "/ as sysdba" <<EOF
set lines 150
set pages 150

select optimizer_feature_enable,
       count(*)
  from v\$system_fix_control
 group by optimizer_feature_enable
 order by 1 asc;

EOF

}


test01()
{

###############################################################################################
# This is a test script designed to extract some values but shows the date and time properly
###############################################################################################
sqlplus -s "/ as sysdba" <<EOF
set lines 150
set pages 150
alter session set nls_date_format="YYYY MM DD hh24:mi:ss"

select thread#, sequence#, first_scn, to_char(first_time, 'YYYY MM DD HH24:MI:SS') from dba_registered_archived_log where thread#=1 and sequence# in (510830,510977) order by thread#;

select thread#, sequence#, first_scn, to_char(first_time, 'YYYY MM DD HH24:MI:SS') from dba_registered_archived_log where thread#=2 and sequence# in (594911,595018) order by thread#;

select thread#, sequence#, first_scn, to_char(first_time, 'YYYY MM DD HH24:MI:SS') from dba_registered_archived_log where thread#=3 and sequence# in (306761,306822) order by thread#;

select thread#, sequence#, first_scn, to_char(first_time, 'YYYY MM DD HH24:MI:SS') from dba_registered_archived_log where thread#=4 and sequence# in (309620,309681) order by thread#;

EOF
}

###################################################
# GoldenGate create function set
###################################################
sho_gg_cmd()
{

clear

echo ""
echo ""
echo "chk_gg_status                    - info all and abended processes                   "
echo ""
echo "cp_gg_comm                       - copies the gg_common.ksh to a specific directory "
echo ""
echo "gg_cap_procs / gg_cap_procs.ksh  - shows the internal capture processes "
echo ""
echo "gg_version   / gg_version_fn.ksh - shows the version                    "
echo ""
echo "par_proc                         - Show the parallel processes                      "
echo ""

}


chk_gg_status()
{

###########################################################################
# All this is doing is pulling the info all, then looking for
# ABENDED processes
###########################################################################



# constants
# -------------------------------------------------------------------------
GGS_HOME=/oracle/product/12.1/gg_1
ORACLE_HOME=/oracle/product/11.2.0/db_1
GGSMON=$GGS_HOME/ggsmon
MAILTO=mike.culp@gmail.com

# no more editting past this point
# -------------------------------------------------------------------------
LD_LIBRARY_PATH=${ORACLE_HOME}/lib
export LD_LIBRARY_PATH

###########################################################################
# for some utter reason you need to cd to Golden Gate home or else i got
# Could not load program /ora/product/goldengate/112101/ggsci:
#         Dependent module libgglog.a could not be loaded.
# Could not load module libgglog.a.
# System error: No such file or directory
###########################################################################

cd ${GGS_HOME}

###########################################################################
# start golden gate with eof and execute 'info all' and
###########################################################################

gg_1=`${GGS_HOME}/ggsci << EOF
info all
EOF`

echo "info all output: " $gg_1

# grep for ABENDED string
# golden_gate_infoall=`echo $golden_gate_infoall | grep ABENDED`

gg_2=`cat $gg_1|grep APPENDED|cut -d":" -f 1,2,4| tr ":" " "|tr -s '[:space:]'|cut -d" " -f1,2,3,4,5,6 `


# echo "Abended proc :" $golden_gate_infoall

if [[ "" = ${gg_2} ]] ;
then
   exit 0
else
   # found 'ABENDED' - send email
   # echo $golden_gate_infoall | mailx -s "One or more Golden Gate processes abended at $(hostname)" ${MAILTO}
   echo "Abended proc :" `echo $gg_2`

   exit 2
fi
}

cp_gg_comm()
{

################################################################
# This will copy the gg_common.ksh file to the correct directory
# after editing to get it in the right place
################################################################

cp gg_common.ksh /oracle/product/12.1/gg_1

}


gg_cap_procs()
{

################################################################
# Show the capture processes for a particular database
################################################################


sqlplus -s "/ as sysdba" <<EOF

select capture_name
  from dba_capture;

EOF
}



gg_version()
{

/oracle/product/12.1/gg_1/ggsci <<EOF

versions

EOF

}


gg_chk_ddl()
{

#################################################################################
# This procedure will check the schema to ensure
# there are adequate GoldenGate
# features added to the DDL
#################################################################################

echo
echo "Executing GoldenGate DDL check process....."
echo

sqlplus -s "/ as sysdba" <<EOF
set lines 150
set pagesize 300

PROMPT
PROMPT Shows tables missing primary/unique keys
PROMPT

SELECT owner, table_name
  FROM all_tables
WHERE owner in ('PIPER_D11_OWNER', 'CST_D11_OWNER','MISITE')
  and table_name in ( select table_name
                        from OGGUSER.OGG_REPLICATE
                       where owner in  ('PIPER_D11_OWNER','CST_D11_OWNER','MISITE'))
MINUS
(SELECT user1.name,obj1.name
   FROM SYS.user$ user1,
        SYS.user$ user2,
        SYS.cdef$ cdef,
        SYS.con$ con1,
        SYS.con$ con2,
        SYS.obj$ obj1,
        SYS.obj$ obj2
  WHERE user1.name in ('PIPER_D11_OWNER', 'CST_D11_OWNER','MISITE')
    AND cdef.type# = 2
    AND con2.owner# = user2.user#(+)
    AND cdef.robj# = obj2.obj#(+)
    AND cdef.rcon# = con2.con#(+)
    AND obj1.owner# = user1.user#
    AND cdef.con# = con1.con#
    AND cdef.obj# = obj1.obj#
 UNION ALL
  SELECT idx.owner,
         idx.table_name
    FROM all_indexes idx
   where idx.owner in ('PIPER_D11_OWNER','CST_D11_OWNER','MISITE')
     and idx.uniqueness = 'UNIQUE')
   order by 1;

--PROMPT
--PROMPT Shows tables missing primary/unique keys script #2
--PROMPT

--select owner,
--       table_name
--  from dba_tables
-- where owner in ('PIPER_D11_OWNER', 'CST_D11_OWNER','MISITE')
--minus
-- (select owner, table_name
--    from dba_indexes
--   where uniqueness = 'UNIQUE'
--     and owner in ('PIPER_D11_OWNER', 'CST_D11_OWNER','MISITE')
--  union all
--  select owner,
--         table_name
--    from dba_constraints
--   where constraint_type = 'P'
--     and owner in ('PIPER_D11_OWNER', 'CST_D11_OWNER','MISITE')
--     and table_name in (select table_name
--                          from ogguser.ogg_replicate
--                         where owner in ('PIPER_D11_OWNER', 'CST_D11_OWNER','MISITE')
-- ));


PROMPT
PROMPT Tables with relationships across schemas.....
PROMPT

select owner,
       constraint_name,
       table_name,
       r_owner,
       R_constraint_name
  from dba_constraints
 where owner <> R_owner
   and owner in ('PIPER_D11_OWNER','CST_D11_OWNER','MISITE')
   and status='ENABLED';

PROMPT
PROMPT Shows disabled primary/unique keys for specific schemas
PROMPT

select owner,
       table_name,
       constraint_name,
       status
  from dba_constraints
 where owner in ('PIPER_D11_OWNER','CST_D11_OWNER','MISITE')
   and constraint_type in ('R','P','U')
   and status='DISABLED';

PROMPT
PROMPT Shows primary keys not validated
PROMPT

select CONSTRAINT_NAME,
       VALIDATED
  from dba_constraints
 where owner in ('PIPER_D11_OWNER', 'CST_D11_OWNER','MISITE')
   and constraint_type in ('P', 'R','U')
   and validated = 'NOT VALIDATED';

PROMPT
PROMPT Shows cascade delete constraints DEFERRABLE
PROMPT

select owner,
       constraint_name,
       deferrable,
       deferred,
       delete_rule
  from dba_constraints
 Where owner in ('PIPER_D11_OWNER','CST_D11_OWNER','MISITE')
   and constraint_type ='R'
   and (deferrable='DEFERRABLE' or delete_rule='CASCADE');

PROMPT
PROMPT Shows tables without CREATEDON_ Timestamp col
PROMPT

select distinct t.owner,
       t.table_name
  from OGGUSER.OGG_REPLICATE t,
       dba_tab_cols c
 where t.table_name = c.table_name
   and t.owner in ('PIPER_D11_OWNER','CST_D11_OWNER','MISITE')
minus
select distinct t.owner,
       t.table_name
  from dba_tables  t,
       dba_tab_cols c
 where t.table_name = c.table_name
   and c.COLUMN_NAME ='CREATEDON_'
   and t.owner in ('PIPER_D11_OWNER','CST_D11_OWNER','MISITE');

PROMPT
PROMPT Shows tables without MODIFIEDON_ Timestamp col
PROMPT

select distinct t.owner,
       t.table_name
  from OGGUSER.OGG_REPLICATE t,
       dba_tab_cols c
 where t.table_name = c.table_name
   and t.owner in ('PIPER_D11_OWNER','CST_D11_OWNER','MISITE')
minus
select distinct t.owner,
       t.table_name
  from dba_tables  t,
       dba_tab_cols c
 where t.table_name = c.table_name
   and c.COLUMN_NAME ='MODIFIEDON_'
   and t.owner in ('PIPER_D11_OWNER','CST_D11_OWNER','MISITE');

PROMPT
PROMPT Shows tables with CREATEDON_ nullable
PROMPT

select owner,
       table_name,
       column_name,
       data_type,
       NULLABLE
  from dba_tab_columns
 where column_name ='CREATEDON_'
   and owner in ('PIPER_D11_OWNER', 'CST_D11_OWNER','MISITE')
   and NULLABLE ='Y'
   and table_name in (select table_name from OGGUSER.OGG_REPLICATE);

PROMPT
PROMPT Shows tables with MODIFIEDON_ nullable
PROMPT

select owner,
       table_name,
       column_name,
       data_type,
       NULLABLE
  from dba_tab_columns
 where column_name ='MODIFIEDON_'
   and owner in ('PIPER_D11_OWNER', 'CST_D11_OWNER','MISITE')
   and NULLABLE ='Y'
   and table_name in (select table_name from OGGUSER.OGG_REPLICATE);

PROMPT
PROMPT Shows tables with logging off
PROMPT

Select Owner,
       Table_NAMe
  from dba_tables
 where owner in ('PIPER_D11_OWNER', 'CST_D11_OWNER','MISITE')
   and LOGGING='N';

PROMPT
PROMPT Shows the primary/unique key length
PROMPT


select owner,
       table_name,
       constraint_name,
       count(*)
  from dba_cons_columns
 where owner in  ('PIPER_D11_OWNER', 'CST_D11_OWNER','MISITE')
   and constraint_name in
      (select constraint_name
         from dba_constraints
        where owner in ('PIPER_D11_OWNER', 'CST_D11_OWNER','MISITE')
          and constraint_type in ('U','P'))
        Group by owner,
                 table_name,
                 constraint_name
        Order by count(*) asc;



PROMPT
PROMPT Shows the primary/unique key data type
PROMPT


select count (*),
       a.data_type
  from all_tab_columns a,
(select all_constraints.owner,
        all_constraints.table_name,
        all_constraints.constraint_name,
        all_cons_columns.column_name,
        all_constraints.constraint_type
   from all_constraints,all_cons_columns
  where all_constraints.constraint_name = all_cons_columns.constraint_name
    and all_constraints.owner in ('PIPER_D11_OWNER', 'CST_D11_OWNER','MISITE')
    and all_constraints.constraint_type in ('P','U')) b
  where b.column_name=a.column_name
    and a.owner=b.owner
    and a.table_name=b.table_name
  group by a.data_type;

PROMPT
PROMPT Shows the without triggers for GG
PROMPT

select owner,table_name
  from OGGUSER.OGG_REPLICATE
 where owner in ('PIPER_D11_OWNER','CST_D11_OWNER','MISITE')
 minus
select owner,
       table_name
  from dba_triggers
 where status='ENABLED';

EOF
}








###################################################
# GoldenGate test function set
###################################################
#
# This consists of several functions
#
# cr_dba_gg_tst_usr



cr_dba_gg_tst_usr()
{
######################################################################
# function Name: cr_dba_gg_tst_usr
# Description..: Create schema/user for golden gate testing
# Author.......: Michael Culp
# Date.........: 05/09/2016
# Version......: 1.0
# Modified By..:
# Date Modified:
# Comments.....:
# Schema owner.: DBA_GG_TST
#    alter session set current
# Login User...:
# Run Order....:
# Dependent on.:
######################################################################

sqlplus -s "/ as sysdba" <<EOF

set echo on
set feed on
set time on
set timing on

spool logs/cr_dba_gg_tst_usr.log

drop user DBA_GG_TST cascade;

create user DBA_GG_TST identified by dba88tst
  default tablespace piper_data
  temporary tablespace temp
  profile exempt_app_profile;

grant create session to DBA_GG_TST;
grant dba            to DBA_GG_TST;
alter user DBA_GG_TST quota unlimited on piper_data;

commit;
spool off
exit

EOF

}


cr_dba_gg_tst_objs()
{
######################################################################
# Func Name....: cr_dba_gg_tst_objs.ksh
# Description..: Create base objects for DBA_GG_TEST schema
# Author.......:
# Date.........: 07/13/2016
# Version......: 1.0
# Modified By..: Michael Culp
# Date Modified:
# Comments.....: Modified into a function, needs to check to see
#                if user exists first, if not drop out or create user
#                also need to have several values replaced in order
#                to work on different environments
#
# Schema owner.: dba_gg_tst
# Login User...:
# Run Order....:
# Dependent on.:
# Script type..:
#
######################################################################

sqlplus -s "/ as sysdba" <<EOF

set echo on
set feed on
set time on
set timing on

spool logs/110_cr_objs_dba_gg_tst.log

set pagesize 144
set linesize 132

REM
REM dba_gg_tst objects
REM

create table DBA_GG_TST.GGSITE (
   site_id      number(1)     not null,
   dbname       varchar2(30)  not null,
   gghost       varchar2(30)  not null,
   constraint   ggsite_pk primary key (SITE_ID)
using index );

----- value given here is for DEV. Adjust values properly.
-- These can be replaced by variables

insert into DBA_GG_TST.ggsite values (1,'PIPERD01','piperd01-ggvip');
insert into DBA_GG_TST.ggsite values (2,'PIPERD02','piperd02-ggvip');

commit;

CREATE TABLE DBA_GG_TST.GGTEST
(
  ID            NUMBER                   NOT NULL,
  HOST_NAME     VARCHAR2(100 BYTE)       NOT NULL,
  SITE_ID       NUMBER(1),
  TEST_MESSAGE  VARCHAR2(80 BYTE),
  CREATEDON_    TIMESTAMP(6)             DEFAULT SYS_EXTRACT_UTC(SYSTIMESTAMP) NOT NULL,
  MODIFIEDON_   TIMESTAMP(6)             DEFAULT SYS_EXTRACT_UTC(SYSTIMESTAMP) NOT NULL,
  SUPPLEMENTAL LOG DATA (ALL) COLUMNS
)
TABLESPACE PIPER_DATA
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL ;

-- These grants should be revoked afterward to comply
-- with audit
grant select on v_\$instance to DBA_GG_TST;
grant select on v_\$database to DBA_GG_TST;


CREATE SEQUENCE DBA_GG_TST.GGTEST_SEQ
  START WITH 1
  MAXVALUE 9999999999999999999999999999
  MINVALUE 1
  NOCYCLE
  NOCACHE
  NOORDER;


CREATE OR REPLACE TRIGGER DBA_GG_TST.GGTEST_TRG
   BEFORE INSERT OR UPDATE ON DBA_GG_TST.GGTEST
   FOR EACH ROW
WHEN (
USER !='OGGUSER'
     )
DECLARE

BEGIN
IF INSERTING THEN
   :NEW.CREATEDON_  := SYS_EXTRACT_UTC(SYSTIMESTAMP);
   :NEW.MODIFIEDON_ := SYS_EXTRACT_UTC(SYSTIMESTAMP);
END IF;

IF UPDATING THEN
   :NEW.MODIFIEDON_ := SYS_EXTRACT_UTC(SYSTIMESTAMP);
END IF;
END;
/

CREATE OR REPLACE TRIGGER DBA_GG_TST.GGTEST_TRG_HOST_SITEID
   BEFORE INSERT OR UPDATE
   ON DBA_GG_TST.ggtest
   FOR EACH ROW
begin
   select host_name into :new.host_name from v$instance;
   select a.site_id into :new.site_id
     from DBA_GG_TST.ggsite a,
          v$database b
    where a.dbname = b.name;
end;
/

grant select, insert, update, delete
   on DBA_GG_TST.ggtest to ogguser;

spool off

EOF

}

sho_gg_test_tbl()
{
############################################################################
# Function Name: sho_gg_test_tbl
# Description..: show the ggtest table
# Author.......: Michael Culp
# Date.........:
# Version......:
# Modified By..: Michael Culp
# Date Modified:
# Comments.....:
# Schema owner.: dba_gg_tst
#              : alter session set current
# Login User...:
# Run Order....:
# Dependent on.:
# Script type..:
############################################################################

echo
echo
sqlplus -s "/ as sysdba" <<EOF
set lines 150
set pages 150
set feedback off
column object_name format a25
-- spool <some file name>

desc dba_gg_tst.ggtest;

select count(*)
  from dba_gg_tst.ggtest;

EOF

}



test_gg_upd()
{
############################################################################
# Func Name....: test_gg_upd
# Description..: test goldengate update
# Author.......: Michael Culp
# Date.........: 05/2016
# Version......:
# Modified By..:
# Date Modified:
# Comments.....:
# Schema owner.: CST_L01_OWNER
#              : PIPER
#              : MISITE
#              : alter session set current
# Login User...:
# Run Order....:
# Dependent on.:
# Script type..:
############################################################################

echo
echo "Testing GoldenGate Update"
echo

sqlplus -s "/ as sysdba" <<EOF
set lines 150
set pages 150
set feedback off
column object_name format a25
-- spool <some file name>

conn dba_gg_tst/gg5d01_o

update dba_gg_tst.ggtest
   set TEST_MESSAGE='Updating a test record' where id=1;

commit;

select *
  from dba_gg_tst.ggtest;

EOF

}



gg_stop()
{
####################################################################
# This function will check the GG status and then stop GG using XAG
# Needs parameter
####################################################################
echo
echo "checking GG status........................................................................................."
echo
/oracle/product/xag71/bin/agctl status goldengate piperd01_oggapp
echo
echo "stopping GG................................................................................................"
echo
/oracle/product/xag71/bin/agctl stop goldengate piperd01_oggapp
echo
echo "checking GG status........................................................................................."
echo
/oracle/product/xag71/bin/agctl status goldengate piperd01_oggapp
echo

}


db_start()
{
########################################################################
# Function to Start Database
# Needs parameter
#######################################################################

srvctl start database -d PIPERD01

}



db_stop()
{
########################################################################
# Function to Stop Database
# Needs parameter
#######################################################################

srvctl stop database -d PIPERD01

}



gg_start()

{

##########################################################################
# 
# This function will checK GG status and then try to start GG using XAG
##########################################################################

echo
echo "checking GG status........................................................................................."
echo
/oracle/product/xag71/bin/agctl status goldengate piperd01_oggapp
echo
echo "stopping GG................................................................................................"
echo
/oracle/product/xag71/bin/agctl start goldengate piperd01_oggapp
echo
echo "checking GG status........................................................................................."
echo
/oracle/product/xag71/bin/agctl status goldengate piperd01_oggapp
echo

}



###################################################
# GoldenGate mgmt function set
# Many of these were created during the issue
# we had over Xmas holiday 2016
###################################################

gg_status()
{

# read the environment file for gg here
# . /var/opt/oracle/.ggsora12_env
#
# info extract <extract name>

echo
echo "GGSCI status....................................."
echo


/oracle/product/12.1/gg_1/ggsci <<EOF

info all

-- info mgr

EOF

}


gg_ext_det()
{

##########################################################
# read the environment file for gg here
# . /var/opt/oracle/.ggsora12_env
#
# info extract <extract name>
# send EP1ALSDM, report
##########################################################

echo
echo "Extract detail................."
echo

/oracle/product/12.1/gg_1/ggsci <<EOF

sh date

send EP1ALSDM, status

send EP1ALSDM, showtrans

send EP1ALSDM getlag

stats EP1ALSDM totalsonly *.*, reportrate sec

info EP1ALSDM, showch

info EP1ALSDM, showch debug

send <name>, CACHEMGR CACHESTATS

send <name>, CACHEMGR CACHEQUEUES

send <name>, CACHEMGR CACHEPOOL

send <name>, LOGSTATS

send <name>, report

EOF

}


gg_mgr_inf()
###########################################
# Manager info
###########################################
{

/oracle/product/12.1/gg_1/ggsci <<EOF

info manager

EOF
}


cp_gg_dba_cls()
{
#####################################################################################
# Function Name: cp_dba_cls
# Description..: Copies the ddl_common.ksh to piper dev clusters
# Author.......: Michael Culp
# Date.........: 8/12/2014
# Version......:
# Modified By..:
# Date Modified:
# Comments.....:
# Schema owner.:
# Login User...:
# Run Order....:
# Dependent on.:
############################################################################
#
echo "MDX Prod Cluster Node 2"
scp -rp /oracle/product/12.1/gg_1/*ksh oracle@<server name>:/oracle/admin/scripts/mrc/prod_node02
### scp -rp /oracle/product/gg12.1/dirprm/* oracle@<server name>:/oracle/admin/scripts/mrc/piper_dev/ggdirprm

}


cp_rpt_ext()
{

# Copy a report file to the tmp directory

extrpt=$1

cd /oracle/product/12.1/gg_1/dirrpt
cp ${extrpt} /tmp

}

cp_ggserr()
{

# Copy the error log to the tmp directory

cd /oracle/product/12.1/gg_1
cp ggserr.log /tmp

}

linux_prc_chk()
{

ps -eo pid,stat,args,wchan |grep D

}

ora_prc_chk()
{

echo
echo "Checking for oracle processes......"
echo

ps -ef|grep ora

echo
echo "Checking for oracle processes sorted......"
echo

ps -ef|grep ora|sort

echo
echo "Checking for oracle processes sorted......"
echo

ps -ef|grep crs_|sort

}


gg_ext_stat()
{

ext=$1

# read the environment file for gg here
# . /var/opt/oracle/.ggsora12_env
#
# info extract <extract name>
# send EP1ALSDM, report

echo
echo "Extract status................."
echo

/oracle/product/12.1/gg_1/ggsci <<EOF

sh date

send ${ext}, status

EOF

}


gg_ext_det_p()
{
#####################################################
# read the environment file for gg here
# . /var/opt/oracle/.ggsora12_env
#
# info extract <extract name>
# send EP1ALSDM, report
#####################################################

ext=$1

echo
echo "Extract detail................."
echo

/oracle/product/12.1/gg_1/ggsci &