Category Archives: Uncategorized

db_common.ksh


#!/bin/ksh
############################################################################
# Script Name..: db_common.ksh
# Description..: common db functions was derived from ddl_common.ksh
# Author.......: Michael Culp
# Date.........: 11/30/2016
# Version......: .09
# Modified By..:
# Date Modified:
# Comments.....:
# Schema owner.:
# Functions....:
#              : lst_objs_all
#              :
# Login User...:
# Run Order....:
# Dependent on.:
# Script type..:
############################################################################


db_comm_init()
{

echo "Init parameters for db_common....."
echo
echo

}

db_comm_dbrd()
{

echo "DB Common Dashboard......."

}


db_comm_sho_cmds()
{

echo "Show commands from the db_common.ksh library......"



}

chg_profile()
# Change a users profile 
# 2 parameters username 
# profile
{

usr=$1
prfl=$2

echo
echo "Changing the profile....."
echo
sqlplus -s "/ as sysdba" <<EOF
set lines 150
set pages 150
set feedback off
-- spool <some file name>

alter user ${usr} profile ${prfl};

EOF
}

qry_template_function()
{
echo
echo "    "
echo
sqlplus -s "/ as sysdba" <<EOF
set lines 150
set pages 150
set feedback off
-- spool <some file name>

-- select username from dba_users;

EOF
}

lst_users()
{

usrs=$1

echo
echo
echo

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

PROMPT
PROMPT
PROMPT Schemas / Users like ${usrs}

select username from dba_users
 where username like '${usrs}%'
 order by username;

EOF
}

drp_app_users()
{
#########################################################################
# App users that need to be dropped and should not be in the database
#########################################################################

schm=$1

echo
echo "Drop the application user "${schm}
echo

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

drop user ${schm} cascade;

EOF
}

db_cr_svcs_script()
{

####################################################
# Create the services script
####################################################

db_nm=$1
dir=/oracle/admin/scripts/rspfiles
# nl=lcdre5wbpdbd1,lcdre5wcpdbd1
instlst=PIPERD011,PIPERD012

echo "....${db_nm} database services creation script...."

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

echo "....Creating database services creation script......."
# echo > ${db_nm}.ksh
cat << EOS > ${dir}/cr_${db_nm}_svcs.ksh
#!/bin/ksh

###########################################################################
# This will create services needed for a database
###########################################################################

srvctl add service -d ${db_nm} -s ${db_nm}_SVC_01 -r ${instlst}
srvctl start service -d ${db_nm} -s ${db_nm}_SVC_01
srvctl status service -d ${db_nm}

EOS

chmod +x ${dir}/cr_${db_nm}_svcs.ksh

}

srvctl_cfg()
# Shows the config of all databases on the cluster
{

srvctl config

}

srvctl_stat_db()
{

srvctl status database -d DBFSXT01

}

gen_srv_lst ()
#######################################################
# Create a server list to dblst file
# Filename is dblst
#######################################################
{
srvctl config > dblst
clear
echo
echo "Databases listed abbreviated in clusterware"
echo
cat dblst
}


gen_srv_lst_verbose ()
{
#######################################################
# Create a verbose server list to dblst file
#######################################################
srvctl config database -v > dblst
clear
echo
echo "Databases listed verbose in clusterware"
echo
# cat the file
cat dblst
}


lst_type_hm ()
{
#######################################################
# call gen_srv_lst first
#######################################################

file="dblst"
while read line
do
   echo
   db=`echo $line | cut -d ' ' -f1`
   echo "####################################################################"
   echo " Database Name...: "$db
   dr=`echo $line | cut -d ' ' -f2`
   echo
   echo " Directory   "$dr
   echo
   ${dr}/bin/srvctl status database -d $db
   ${dr}/bin/srvctl config database -d $db |grep Type
   ${dr}/bin/srvctl config database -d $db |grep home
   ${dr}/bin/srvctl status service -d $db
   sho_env
   echo
   echo "####################################################################"
done <"$file"
}

lst_type_dir ()
{
################################################################
# you must generate dblst first
# Cycle through the dblst file and show the directory sizes
################################################################
file="dblst"
while read line
do
   echo
   echo "Database Name...: "$line
   echo
#   srvctl config database -d $line |grep "Oracle home"|cut -d' ' -f3
   db_dir=`srvctl config database -d $line |grep "Oracle home"|cut -d' ' -f3`
#   echo $db_dir
   db_dir_sz=`df $db_dir|tail -n 1|sed -e 's/^[ \t]*//'|cut -d' ' -f1`
   db_dir_sz_rem=`df $db_dir|tail -n 1|sed -e 's/^[ \t]*//'|cut -d' ' -f5`

   db_gi_sz=`df /oracle_crs|tail -n 1|sed -e 's/^[ \t]*//'|cut -d' ' -f1`
   db_gi_sz_rem=`df /oracle_crs|tail -n 1|sed -e 's/^[ \t]*//'|cut -d' ' -f5`

#    db_ora01_sz=`df /ora01|tail -n 1|sed -e 's/^[ \t]*//'|cut -d' ' -f3`
   db_ora01_sz=`df /ora01|tail -n 1|sed -e 's/^[ \t]*//'|awk '{print $2}'`
   db_ora01_sz_rem=`df /ora01|tail -n 1|sed -e 's/^[ \t]*//'|awk '{print $4}'`

   echo "########### Filesystem Space ###########"
   echo
   echo "GI Allocated........: "$db_gi_sz
   echo "GI Remaining........: "$db_gi_sz_rem
   echo
   echo "Database Allocated..: "$db_dir_sz
   echo "Database Remaining..: "$db_dir_sz_rem
   echo
   echo "ORA01 Allocated.....: "$db_ora01_sz
   echo "ORA01 Remaining.....: "$db_ora01_sz_rem
   echo
   find_db
done <"$file"
}

db_rec_parm()
{
###############################################################################
# Show the parameters for recovery
###############################################################################
${ORACLE_HOME}/bin/sqlplus -s "/ as sysdba" <<EOF
--   select name from v\$database;

   show parameter recov

EOF
}

db_psu ()
{
###############################################################################
# Show the DB PSU patches for all on the current server
# designed to replace the stand alone script db_psu.ksh
###############################################################################
# use gen_srv_lst to refresh the file on the next line
###############################################################################

gen_srv_lst

file="dblst"
hostnm=`hostname`
log_file=db_psu.log

echo "Database PSU Logfile...: ">$log_file

while read line
do
   echo "#########################################################################"
   echo "$line"
   srvctl status database -d $line |grep $hostnm|grep "is running"
   ###############################################################################
   # Check to see if instance is running
   ###############################################################################
   db_run=`srvctl status database -d $line |grep $hostnm|grep "is running"|wc -l`
   sho_env
   ############################################################
   # If instance not running loop back up with continue command
   ############################################################
   if [ $db_run -eq 0 ]; then
      echo
      echo "No DB instance found running looping to the list"
      echo
      continue
   fi
   db_inst=`srvctl status database -d $line |grep $hostnm|grep "is running"|cut -d' ' -f2`
   echo "This is the running instance...: "$db_inst
   export NEW_ORACLE_SID=$db_inst; . ~/.profile > /dev/null
   echo
   echo "After environment change..."
   sho_env

   ${ORACLE_HOME}/bin/sqlplus -s "/ as sysdba" <<EOF >>$log_file
   select name from v\$database;
EOF

   ${ORACLE_HOME}/bin/sqlplus -s "/ as sysdba" <<EOF >>$log_file
   set lines 150
   column action        format a8
   column action_time   format a30
   column namespace     format a10
   column version       format a10
   column id            format 999
   column bundle_series format a10
   column comments      format a50
   select action_time, action, namespace, version, id, bundle_series, comments
     from dba_registry_history
    order by action_time desc;
EOF
   opatch lsinventory >>$log_file
   echo
done <"$file"
}

sho_env()
{

###############################################################
# Show the current environment
# Prereqs..:
###############################################################

echo "Show Environment"
echo
echo "Oracle Base....: "$ORACLE_BASE
echo "Oracle Home....: "$ORACLE_HOME
echo "Library Path...: "$ORACLE_HOME/lib
echo "Oracle SID.....: "$ORACLE_SID
echo "Current environment is set to...: "$ORACLE_SID
echo "Currently logged in as.: "`whoami`
echo
}

crs_srv_lst_cfg()
{
##############################################################
# Loops through the database list checks the Oracle home size
# pre-req
#       gen_srv_lst
##############################################################

# Create the file to use for databases
gen_srv_lst

file="dblst"
while read line
do
   echo "===================================================================="
   echo "Database Name.......: "$line
   srvctl config database -d $line
   echo "....Oracle home is..."
   srvctl config database -d $line |grep "Oracle home"|awk '{print $3}'

#   find_db

done <"$file"
}

crs_sho_srv_cfg_home()
{
#######################################################################
# This use the file generated by gen_srv_lst_verbose
# And print the correct config information from the correct srvctl
# from the correct directory
#######################################################################
gen_srv_lst

file="dblst"
while read line
do
   echo
   echo
   # echo "Database...: "$line
   echo $line
   echo "Database name..: "$line|awk '{print $1}'
   db_nm=`echo $line|awk '{print $1}'`
   echo "Home directory.: "$line|awk '{print $2}'
   db_hm_dir=`echo $line|awk '{print $2}'`
   db_hm_dir=$db_hm_dir/bin
   echo $db_hm_dir
   cd $db_hm_dir
   echo "SRVCTl config database listing>>>>>>>>"
   srvctl config database -d $db_nm
   echo $line|awk '{print $3}'
   echo "<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<"
#   find_db

done <"$file"
}



crs_sho_srvctl_cfg()
#######################################################################
# This uses the file generated by gen_srv_lst_verbose
# And print the correct config information from the correct srvctl
# from the correct directory
#######################################################################
{
# Generate a fresh list
gen_srv_lst_verbose

file="dblst"
while read line
do
   echo
   echo
   # echo "Database...: "$line
   echo $line
   # echo "Database name..: "$line|awk '{print $1}'
   db_nm=`echo $line|awk '{print $1}'`
   # Get the home directory for this database
   echo "Home directory.: "$line|awk '{print $2}'
   db_hm_dir=`echo $line|awk '{print $2}'`
   db_hm_dir=$db_hm_dir/bin
   echo $db_hm_dir

   # change to the home directory
   cd $db_hm_dir
   # Next line works because the directory its executing from is different
   # ./srvctl config database -d $db_nm
   echo $line|awk '{print $3}'
   sho_hm=`./srvctl config database -d $db_nm |grep "Oracle home"|cut -d' ' -f3`
   echo $sho_hm
   echo "<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<"
#   echo "$0"
#   find_db

done <"$file"
}

crs_sho_srvctl_stat()
{

#######################################################################
# This use the file generated by gen_srv_lst_verbose
# And print the correct config information from the correct srvctl
# from the correct directory
#######################################################################

#######################################################################
# Generate a fresh list
#######################################################################
gen_srv_lst_verbose

file="dblst"
while read line
do
   echo
   echo
   # echo "Database...: "$line
   echo $line
   # echo "Database name..: "$line|awk '{print $1}'
   db_nm=`echo $line|awk '{print $1}'`
   echo "Home directory.: "$line|awk '{print $2}'
   db_hm_dir=`echo $line|awk '{print $2}'`
   db_hm_dir=$db_hm_dir/bin
   echo $db_hm_dir
   cd $db_hm_dir

   # Next line works because the directory its executing from is different
   ./srvctl status database -d $db_nm

   echo $line|awk '{print $3}'
   sho_hm=`./srvctl config database -d $db_nm |grep "Oracle home"|cut -d' ' -f3`
   echo $sho_hm
   echo "<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<"
#   find_db

done <"$file"
}



Meeting link

Meeting link

Oracle ACS Priority Support Webinar

Oracle Database release 18c New features

 

 

 

AUDIENCE
Exclusively, Oracle ACS Priority Support customers are invited to attend.

 

Upcoming Priority Support Webinar
On Friday June 29th, 2018 the Priority Support program office will host a Priority Support webinar on Oracle Database release 18c New features.

 

Conference details
Date: June 29, 2018
Time: 7:00 AM PT /10:00 AM ET
Audio dial in: +1-866-682-4770
Conference Code:  3547314
Pass code: 12345

 

If you need Global access numbers, please contact your TAM at least 24 hours in advance.

 

Use the above call details for the webinar session.  Please ignore any pop-ups you get with call details when you join the webinar.

 

——————————————————-
To join the online meeting click following link:
——————————————————-

 

 

 

When it’s time, join the meeting

 

 

 

 

 

Meeting number:  592 086 160

 

Accessing DBFS Using Oracle Wallet Fails With Input/output error (Doc ID 1358194.1)

After configuring DBFS to use Oracle Wallet, to store the password, via “Configuring DBFS on Oracle Database Machine (Doc ID 1054431.1)” or by following DBFS documentation in Oracle® Database SecureFiles and Large Objects Developer’s Guide 11g Release 2 (11.2), Part Number E18294-01 , the /dbfs directory mounts but when trying to access directory in any way you get “Input/output error”, i.e.

# df -k /dbfs
df: `/dbfs’: Input/output error

# ls -dl /dbfs
ls: /dbfs: Input/output error

Bringing dbfs_mount online, which will mount the file system on all nodes, via “crsctl start resource dbfs_mount” from any cluster node shows no errors.

(oracle)$ crsctl start resource dbfs_mount
CRS-2672: Attempting to start ‘dbfs_mount’ on ‘host05’
CRS-2672: Attempting to start ‘dbfs_mount’ on ‘host06’
CRS-2676: Start of ‘dbfs_mount’ on ‘host06’ succeeded
CRS-2676: Start of ‘dbfs_mount’ on ‘host05’ succeeded

The “crsctl stat res dbfs_mount -t” confirms that dbfs is online

# oracle)$ crsctl stat res dbfs_mount -t
——————————————————————————–
NAME TARGET STATE SERVER STATE_DETAILS
——————————————————————————–
Local Resources
——————————————————————————–
dbfs_mount
ONLINE ONLINE host05
ONLINE ONLINE host06

By setting trace parameter as part of the mount command we find that accessing /dbfs directory results in “ORA-01017: invalid username/password; logon denied”.

To turn on tracing, modify /etc/fstab to have following mount command and remount /dbfs via “crsctl stop/start resource dbfs_mount” command

/sbin/mount.dbfs#/@dbfs_mount.local /dbfs fuse rw,user,direct_io,allow_other,wallet,trace_file=/tmp/dbfs_mount.trc,trace_level=1,trace_size=0,noauto 0 0

After running “df -k /dbfs” command and getting error you will see following in /tmp/dbfs_mount.trc

[5bbf7940 09/09/11 11:24:42.818800 LcdfLock.cpp:230 ] DEBUG: Global Lock – unlock
[5bbf7940 09/09/11 11:24:42.818806 LcdfLock.cpp:242 ] DEBUG: Global Lock (R) rdwr:0
[45bec940 09/09/11 11:24:42.857258 LcdfDBPool.cpp:398 ] ERROR: ERROR 1017 – ORA-01017: invalid username/password; logon denied

[45bec940 09/09/11 11:24:42.857287 LcdfJob.cpp:304 ] DEBUG: Exit LcdfJob::DoJobInt job:0x2aaaac0bc0e0 op(1)

Oracle Highwater Mark

“high watermark”

The database doesn’t know what is on a block unless and until……

It goes to the block.

So, when you delete the information, the block is still “a block”, it is just a block that once had active rows – but no longer does.

And when you full scan the table – we have to read all blocks that at any time contained data – because – they could contain data now. We won’t know what is there until we read it.

Suppose you deleted all but ONE row – we have to look in every block (unless of course we use an index) to see if that row is on a given block.

<quote src=expert oracle database architecture>

High-water Mark

This is a term used with table segments stored in the database. If you envision a table, for example, as a ‘flat’ structure or as a series of blocks laid one after the other in a line from left to right, the high-water mark (HWM) would be the rightmost block that ever contained data, as illustrated in Figure 10-1.

+---- high water mark of newly created table
|
V
+--------------------------------------------------------+
|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+

      high water mark after inserting 10,000 rows
                                    |
                                    v
+--------------------------------------------------------+
|x |x |x |x |x |x |x |x |x |x |x |x |  |  |  |  |  |  |  |
|x |x |x |x |x |x |x |x |x |x |x |x |  |  |  |  |  |  |  |
+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+


      high water mark after inserting 10,000 rows
                                    |
                                    v
+--------------------------------------------------------+
|x |x |x |x |x |x |x |  |  |  |  |  |  |  |  |  |  |  |  |
|x |x |x |x |x |x |x |  |  |  |  |  |  |  |  |  |  |  |  |
+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+

Figure 10-1. Depiction of an HWM

Figure 10-1 shows that the HWM starts at the first block of a newly created table. As data is placed into the table over time and more blocks get used, the HWM rises. If we delete some (or even all) of the rows in the table, we might have many blocks that no longer contain data, but they are still under the HWM, and they will remain under the HWM until the object is rebuilt, truncated, or shrunk (shrinking of a segment is a new Oracle 10g feature that is supported only if the segment is in an ASSM tablespace).

The HWM is relevant since Oracle will scan all blocks under the HWM, even when they contain no data, during a full scan. This will impact the performance of a full scan¿especially if most of the blocks under the HWM are empty. To see this, just create a table with 1,000,000 rows (or create any table with a large number of rows), and then execute a SELECT COUNT(*) from this table. Now, DELETE every row in it and you will find that the SELECT COUNT(*) takes just as long (or longer, if you need to clean out the block! Refer to the ‘Block Cleanout’ section of Chapter 9) to count 0 rows as it did to count 1,000,000. This is because Oracle is busy reading all of the blocks below the HWM to see if they contain data. You should compare this to what happens if you used TRUNCATE on the table instead of deleting each individual row. TRUNCATE will reset the HWM of a table back to ‘zero’ and will truncate the associated indexes on the table as well. If you plan on deleting every row in a table, TRUNCATE¿if it can be used¿would be the method of choice for this reason.

In an MSSM tablespace, segments have a definite HWM. In an ASSM tablespace, however, there is an HWM and a low HWM. In MSSM, when the HWM is advanced (e.g., as rows are inserted), all of the blocks are formatted and valid, and Oracle can read them safely. With ASSM, however, when the HWM is advanced Oracle doesn¿t format all of the blocks immediately¿they are only formatted and made safe to read upon their first use. So, when full scanning a segment, we have to know if the blocks to be read are ‘safe’ or unformatted (meaning they contain nothing of interest and we do not process them). To make it so that not every block in the table need go through this safe/not safe check, Oracle maintains a low HWM and a HWM. Oracle will full scan the table up to the HWM¿and for all of the blocks below the low HWM, it will just read and process them. For blocks between the ‘low HWM’ and the HWM, it must be more careful and refer to the ASSM bitmap information used to manage these blocks in order to see which of them it should read and which it should just ignore.

GoldenGate Hub Architecture Performance

The GoldenGate Hub Architecture is an implementation of GoldenGate  that removes the  extracts and replicats from a traditional deployment of running on the same server as the database, to running them on a separate server.

The processing of GoldenGate replications is offloaded from the database servers.

Only the internal database portion of GoldenGate processing is running on the database servers.

Those of you who have been using GoldenGate for a long time might be skeptical of the performance of this model, since the processing is now not being done close to the server.

Well, there is some network processing that is involved but it is overshadowed by the benefit you gain from offloading GoldenGate processing.

Let’s look at how the GoldenGate extract works.  The extract mines the Oracle redo log, maintaining transactions in memory.  When the commit record is reached the extract pulls together (from its memory) all the operations involved in the transaction and writes them to the trail file.  Much of the processing involves maintaining the transactions in memory and writing out the trail file.  By offloading this processing, you not only save a lot of memory on the database server, but significant processing as well.

In fact, keep in mind that by default, the maximum amount of memory an extract can use is 64GB.  This can be significant and cause problems on a database server.  Thus, there are several benefits to offloading the GoldenGate extract from the database server an on to the GoldenGate Hub.

This method of configuring GoldenGate is proven to deliver high performance and to offload much of the overhead from the database server.  I would recommend this method.

Remote IT Services