Category Archives: Scripts

longops.sql


-- -----------------------------------------------------------------------------------
-- File Name    : https://oracle-base.com/dba/monitoring/longops.sql
-- Author       : Tim Hall
-- Description  : Displays information on all long operations.
-- Requirements : Access to the V$ views.
-- Call Syntax  : @longops
-- Last Modified: 03/07/2003
-- -----------------------------------------------------------------------------------

COLUMN sid FORMAT 999
COLUMN serial# FORMAT 9999999
COLUMN machine FORMAT A30
COLUMN progress_pct FORMAT 99999999.00
COLUMN elapsed FORMAT A10
COLUMN remaining FORMAT A10

SELECT s.sid
,      s.serial#
,      s.machine
,      ROUND(sl.elapsed_seconds/60) || ':' || MOD(sl.elapsed_seconds,60) elapsed
,      ROUND(sl.time_remaining/60) || ':' || MOD(sl.time_remaining,60) remaining
,      ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct
  FROM v\$session s,
       v\$session_longops sl
 WHERE s.sid = sl.sid
   AND s.serial# = sl.serial#;

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 <<EOF

sh date

send ${ext}, status

send ${ext}, showtrans

send ${ext} getlag

stats ${ext} totalsonly *.*, reportrate sec

info ${ext}, showch

info ${ext}, showch debug

send ${ext}, CACHEMGR CACHESTATS

send ${ext}, CACHEMGR CACHEQUEUES

send ${ext}, CACHEMGR CACHEPOOL

send ${ext}, LOGSTATS

send ${ext}, report

EOF

}

par_proc()
{

# Show the parallel processes on this box
# Sorted

ps -ef|grep ora_p|sort -k8

}


lm_proc()
{

# Show the logminer processes for GoldenGate on this box
# Sorted

ps -ef|grep ora_m|sort -k8

}


cls_lst_svc()
{

db=$1
srvctl status service -d ${db}

}


cls_cfg_svc()
{

db=$1
srvctl config service -d ${db}

}





cell_iops.ksh Cell Node IOPS script

#!/bin/ksh
#
# cell_iops.sh - a "sort of" end to end Exadata IO monitoring script
#     * inspired by http://glennfawcett.wordpress.com/2013/06/18/analyzing-io-at-the-exadata-cell-level-a-simple-tool-for-iops/
#       and modified to show end to end breakdown of IOPS, inter-database, consumer groups, and latency across Exadata storage cells
#     * you must use this script together with "iostat -xmd" on storage cells on both flash and spinning disk and database IO latency on 
#       system level (AWR) and session level (Tanel Poder's snapper) for a "real" end to end IO troubleshooting and monitoring
#     * the inter-database and consumer groups data is very useful for overall resource management and IORM configuration and troubleshooting 
#     * check out the sample viz that can be done by mining the data here goo.gl/0Q1Oeo
#
# Karl Arao, Oracle ACE (bit.ly/karlarao), OCP-DBA, RHCE, OakTable
# http://karlarao.wordpress.com
#
# on any Exadata storage cell node you can run this one time
#     ./cell_iops.sh
#
# OR on loop spooling to a file and consume later with Tableau for visualization
#     while :; do ./cell_iops.sh >> cell_iops.csv ; egrep "CS,ALL|DB,_OTHER_DATABASE_" cell_iops.csv ; sleep 60; echo "--"; done
#
# Here are the 19 column headers:
#
#     TM             - the time on each snap
#     CATEGORY       - CS (cell server - includes IOPS, MBs, R+W breakdown, latency), DB (database - IOPS, MBs), CG (consumer group - IOPS, MBs)
#     GROUP          - grouping per CATEGORY, it could be databases or consumer groups.. a pretty useful dimension in Tableau to drill down on IO
#     DISK_IOPS      - (applies to CS, DB, CG) high level spinning disk IOPS
#     FLASH_IOPS     - (applies to CS, DB, CG) high level flash disk IOPS
#     DISK_MBS       - (applies to CS, DB, CG) high level spinning disk MB/s (bandwidth)
#     FLASH_MBS      - (applies to CS, DB, CG) high level flash disk MB/s (bandwidth)
#     DISK_IOPS_R    - (applies to CS only) IOPS breakdown, spinning disk IOPS read
#     FLASH_IOPS_R   - (applies to CS only) IOPS breakdown, flash disk IOPS read
#     DISK_IOPS_W    - (applies to CS only) IOPS breakdown, spinning disk IOPS write
#     FLASH_IOPS_W   - (applies to CS only) IOPS breakdown, flash disk IOPS write
#     DLAT_RLG       - (applies to CS only) average latency breakdown, spinning disk large reads
#     FLAT_RLG       - (applies to CS only) average latency breakdown, flash disk large reads
#     DLAT_RSM       - (applies to CS only) average latency breakdown, spinning disk small reads
#     FLAT_RSM       - (applies to CS only) average latency breakdown, flash disk small reads
#     DLAT_WLG       - (applies to CS only) average latency breakdown, spinning disk large writes
#     FLAT_WLG       - (applies to CS only) average latency breakdown, flash disk large writes
#     DLAT_WSM       - (applies to CS only) average latency breakdown, spinning disk small writes
#     FLAT_WSM       - (applies to CS only) average latency breakdown, flash disk small writes
#
 
 
datafile=`echo /tmp/metriccurrentall.txt`
/usr/local/bin/dcli -l root -g /root/cell_group "cellcli -e list metriccurrent" > $datafile
export TM=$(date +%m/%d/%y" "%H:%M:%S)
 
 
# Header
print "TM,CATEGORY,GROUP,DISK_IOPS,FLASH_IOPS,DISK_MBS,FLASH_MBS,DISK_IOPS_R,FLASH_IOPS_R,DISK_IOPS_W,FLASH_IOPS_W,DLAT_RLG,FLAT_RLG,DLAT_RSM,FLAT_RSM,DLAT_WLG,FLAT_WLG,DLAT_WSM,FLAT_WSM"
 
#######################################
# extract IOPS for cells
#######################################
export DRW=`cat $datafile | egrep  'CD_IO_RQ_R_LG_SEC|CD_IO_RQ_R_SM_SEC|CD_IO_RQ_W_LG_SEC|CD_IO_RQ_W_SM_SEC' |grep  -v FD_ |sed 's/,//g'|awk 'BEGIN {w=0} {w=$4+w;} END {printf("%d\n",w);}'`
export FRW=`cat $datafile | egrep  'CD_IO_RQ_R_LG_SEC|CD_IO_RQ_R_SM_SEC|CD_IO_RQ_W_LG_SEC|CD_IO_RQ_W_SM_SEC' |grep  FD_ |sed 's/,//g'|awk 'BEGIN {w=0} {w=$4+w;} END {printf("%d\n",w);}'`
 
export DRWM=`cat $datafile | egrep  'CD_IO_BY_R_LG_SEC|CD_IO_BY_R_SM_SEC|CD_IO_BY_W_LG_SEC|CD_IO_BY_W_SM_SEC' |grep  -v FD_ |sed 's/,//g'|awk 'BEGIN {w=0} {w=$4+w;} END {printf("%d\n",w);}'`
export FRWM=`cat $datafile | egrep  'CD_IO_BY_R_LG_SEC|CD_IO_BY_R_SM_SEC|CD_IO_BY_W_LG_SEC|CD_IO_BY_W_SM_SEC' |grep  FD_ |sed 's/,//g'|awk 'BEGIN {w=0} {w=$4+w;} END {printf("%d\n",w);}'`
 
export DR=`cat $datafile | egrep  'CD_IO_RQ_R_LG_SEC|CD_IO_RQ_R_SM_SEC' |grep  -v FD_ |sed 's/,//g'|awk 'BEGIN {w=0} {w=$4+w;} END {printf("%d\n",w);}'`
export FR=`cat $datafile | egrep  'CD_IO_RQ_R_LG_SEC|CD_IO_RQ_R_SM_SEC' |grep  FD_ |sed 's/,//g'|awk 'BEGIN {w=0} {w=$4+w;} END {printf("%d\n",w);}'`
 
export DW=`cat $datafile | egrep  'CD_IO_RQ_W_LG_SEC|CD_IO_RQ_W_SM_SEC' |grep  -v FD_ |sed 's/,//g'|awk 'BEGIN {w=0} {w=$4+w;} END {printf("%d\n",w);}'`
export FW=`cat $datafile | egrep  'CD_IO_RQ_W_LG_SEC|CD_IO_RQ_W_SM_SEC' |grep  FD_ |sed 's/,//g'|awk 'BEGIN {w=0} {w=$4+w;} END {printf("%d\n",w);}'`
 
export DLATRLG=`cat $datafile | egrep  'CD_IO_TM_R_LG_RQ' |grep  -v FD_ |sed 's/,//g'|awk 'BEGIN {sum=0;count=0} {sum+=$4;++count} END {printf("%.2f",(sum/count)/1000);}'`
export FLATRLG=`cat $datafile | egrep  'CD_IO_TM_R_LG_RQ' |grep  FD_ |sed 's/,//g'|awk 'BEGIN {sum=0;count=0} {sum+=$4;++count} END {printf("%.2f",(sum/count)/1000);}'`
 
export DLATRSM=`cat $datafile | egrep  'CD_IO_TM_R_SM_RQ' |grep  -v FD_ |sed 's/,//g'|awk 'BEGIN {sum=0;count=0} {sum+=$4;++count} END {printf("%.2f",(sum/count)/1000);}'`
export FLATRSM=`cat $datafile | egrep  'CD_IO_TM_R_SM_RQ' |grep  FD_ |sed 's/,//g'|awk 'BEGIN {sum=0;count=0} {sum+=$4;++count} END {printf("%.2f",(sum/count)/1000);}'`
 
export DLATWLG=`cat $datafile | egrep  'CD_IO_TM_W_LG_RQ' |grep  -v FD_ |sed 's/,//g'|awk 'BEGIN {sum=0;count=0} {sum+=$4;++count} END {printf("%.2f",(sum/count)/1000);}'`
export FLATWLG=`cat $datafile | egrep  'CD_IO_TM_W_LG_RQ' |grep  FD_ |sed 's/,//g'|awk 'BEGIN {sum=0;count=0} {sum+=$4;++count} END {printf("%.2f",(sum/count)/1000);}'`
 
export DLATWSM=`cat $datafile | egrep  'CD_IO_TM_W_SM_RQ' |grep  -v FD_ |sed 's/,//g'|awk 'BEGIN {sum=0;count=0} {sum+=$4;++count} END {printf("%.2f",(sum/count)/1000);}'`
export FLATWSM=`cat $datafile | egrep  'CD_IO_TM_W_SM_RQ' |grep  FD_ |sed 's/,//g'|awk 'BEGIN {sum=0;count=0} {sum+=$4;++count} END {printf("%.2f",(sum/count)/1000);}'`
 
print "$TM,CS,ALL,$DRW,$FRW,$DRWM,$FRWM,$DR,$FR,$DW,$FW,$DLATRLG,$FLATRLG,$DLATRSM,$FLATRSM,$DLATWLG,$FLATWLG,$DLATWSM,$FLATWSM"
 
 
#######################################
# extract IOPS for database
#######################################
export db_str=`cat $datafile | egrep 'DB_FD_IO_RQ_LG_SEC' | grep -v DBUA | awk '{ print $3}' | sort | uniq`
 
for db_name in `echo $db_str`
do
  # Calculate Total IOPS of harddisk
  # DB_IO_RQ_LG_SEC
  # DB_IO_RQ_SM_SEC
  db_drw=`cat $datafile | egrep 'DB_IO_RQ_LG_SEC|DB_IO_RQ_SM_SEC' |grep $db_name |sed 's/,//g'|awk 'BEGIN {w=0} {w=$4+w;} END {printf("%d\n",w);}'`
 
  # Calculate Total IOPS of flashdisk
  # DB_FD_IO_RQ_LG_SEC
  # DB_FD_IO_RQ_SM_SEC
  db_frw=`cat $datafile | egrep 'DB_FD_IO_RQ_LG_SEC|DB_FD_IO_RQ_SM_SEC' |grep $db_name |sed 's/,//g'|awk 'BEGIN {w=0} {w=$4+w;} END {printf("%d\n",w);}'`
 
  # Calculate Total MB/s of harddisk
  # DB_IO_BY_SEC
  db_drwm=`cat $datafile | egrep 'DB_IO_BY_SEC' |grep $db_name |sed 's/,//g'|awk 'BEGIN {w=0} {w=$4+w;} END {printf("%d\n",w);}'`
 
  # Calculate Total MB/s of flashdisk
  # DB_FC_IO_BY_SEC
  # DB_FD_IO_BY_SEC
  # DB_FL_IO_BY_SEC
  db_frwm=`cat $datafile | egrep 'DB_FC_IO_BY_SEC|DB_FD_IO_BY_SEC|DB_FL_IO_BY_SEC' |grep $db_name |sed 's/,//g'|awk 'BEGIN {w=0} {w=$4+w;} END {printf("%d\n",w);}'`
 
  print "$TM,DB,$db_name,$db_drw,$db_frw,$db_drwm,$db_frwm,0,0,0,0,0,0,0,0,0,0,0,0"
 
done
 
 
#######################################
# extract IOPS for DBRM consumer groups
#######################################
export cg_str=`cat $datafile | egrep 'CG_FD_IO_RQ_LG_SEC' | grep -v DBUA | awk '{ print $3}' | sort | uniq`
 
for cg_name in `echo $cg_str`
do
 
  # Calculate Total IOPS of harddisk
  # CG_IO_RQ_LG_SEC
  # CG_IO_RQ_SM_SEC
  cg_drw=`cat $datafile | egrep 'CG_IO_RQ_LG_SEC|CG_IO_RQ_SM_SEC' |grep $cg_name |sed 's/,//g'|awk 'BEGIN {w=0} {w=$4+w;} END {printf("%d\n",w);}'`
 
  # Calculate Total IOPS of flashdisk
  # CG_FD_IO_RQ_LG_SEC
  # CG_FD_IO_RQ_SM_SEC
  cg_frw=`cat $datafile | egrep 'CG_FD_IO_RQ_LG_SEC|CG_FD_IO_RQ_SM_SEC' |grep $cg_name |sed 's/,//g'|awk 'BEGIN {w=0} {w=$4+w;} END {printf("%d\n",w);}'`
 
 
  # Calculate Total MB/s of harddisk
  # CG_IO_BY_SEC
  cg_drwm=`cat $datafile | egrep 'CG_IO_BY_SEC' |grep $cg_name |sed 's/,//g'|awk 'BEGIN {w=0} {w=$4+w;} END {printf("%d\n",w);}'`
 
  # Calculate Total MB/s of flashdisk
  # CG_FC_IO_BY_SEC
  # CG_FD_IO_BY_SEC
  cg_frwm=`cat $datafile | egrep 'CG_FC_IO_BY_SEC|CG_FD_IO_BY_SEC' |grep $cg_name |sed 's/,//g'|awk 'BEGIN {w=0} {w=$4+w;} END {printf("%d\n",w);}'`
 
  print "$TM,CG,$cg_name,$cg_drw,$cg_frw,$cg_drwm,$cg_frwm,0,0,0,0,0,0,0,0,0,0,0,0"
 
done

Scripts Directories Master Doc

120 server

copied to the local \users\nbkgyqp\documents\scripts\120 server on 10/12/2017

directories include

apex – This is my directory of apex related scripts

asm

ASM_Create_Scripts

asm_disk

ASM_scripts

awr.d – DMV directory of scripts

clscreate – my cluster create library

clsqa – Cluster QA directory

cluster

cmnfunc

dataguard – Dataguard library

dbadmin –

dbbuild –

 

 

GoldenGate sample environment file ggsora12_env

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)
###########################