pre-post check script

 #######################################################
#!/bin/ksh
# set the DB env and run
# Script to check PRE & Post checks Manually
#
# CB - <Date>   - version 1 - Initial Draft
#set -x

echo -e "\n*************************************"
echo  "DATE = `date`"
echo  "HOSTNAME = " `hostname`
echo -e "*************************************\n"

if [ "$1" = "" ]; then
  echo -e "\n please Provide pre or post "
  echo -e "Syntax: $0 <pre>|<post> \n"
  exit 1
elif [[ "${chk_type}" == "pre" || "${chk_type}" == "post" ]]; then
  echo -e "\n Entered ${chk_type}   ..........  \n"
else
  echo -e "\n Please enter either " PRE " or " POST " Only. \n"
  exit 1
fi

chk_type=`echo $1 | tr "[:upper:]" "[:lower:]"`
export logfile=/home/oracle/CB/${chk_type}_shell_logfile.log
export home=<ORACLE_HOME>
export oem_agent_home=<Agent_home>
export psu_location=<Patch_path>/<patch#>

#exit 1
precheck(){
echo -e "\n   -----[ Gather OS Info ]------\n"
date
uname -a
cat /etc/oratab
ps -ef |grep pmon
ps -ef |grep tns
crsctl stat res -t
df -h
echo -e "\n   -----[ OEM Agent Info ]------\n"
${oem_agent_home}/bin/emctl status agent
${oem_agent_home}/bin/emctl status blackout <name>
}


home_check () {
echo -e "\n     -----[ Gather PATCH Info ]------\n"
$home/OPatch/opatch version
$home/OPatch/opatch lsinventory
 if [[ "$chk_type" == "pre" ]]; then
  echo -e "\n Checking patch Conflicts ........."
  cd ${psu_location}
  ${home}/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./
 fi
}

db_check () {
echo -e "\n   -----[ Gather DB Info ]------\n"
crsctl stat res -t | grep ".db$" |tr "." " " | awk -F" " '{print $2}'| while read LINE2
do
 # case $LINE2 in
 #   *)
echo -e "\n Running for : $LINE2  "
srvctl status database -d $LINE2 -v
srvctl status service -d $LINE2 -v
echo "-------------------------"
#esac
done
}

home_check | tee -a ${logfile}
precheck | tee ${logfile}
db_check  | tee -a ${logfile}

sql_info (){
cat /etc/oratab | grep 112|tr ":" " "  | awk -F" " '{print $1}' |egrep -v ''\#'|ASM' | while read LINE3
do
export ORACLE_SID=${LINE3}
export ORACLE_HOME=${home}
export LD_LIBRARY_PATH=$ORACLE_HOME/bin:$PATH
#echo "$LINE3"
echo -e "\n    --------[ Running for database : $LINE3 ]---------"
$ORACLE_HOME/bin/sqlplus -s / as sysdba << eof
set pages 999 lines 280 feedback off
col COMMENTS for a33
col pdb_name format a15
col dp_action format a15
col dp_status format a20
col dp_action_time format a20
col dp_description format a30
select comments, action, to_char(action_time,'DD/MM/RR HH24:MI:SS') action_date, version  from sys.registry\$history  order by action_date;
 select name,action_time dp_action_time,id dp_patch_id,action dp_action,comments dp_description from v\$database,sys.registry\$history  where action_time=(select max(action_time)   from sys.registry\$history);
eof
done
}

sql_info

cat  ${logfile} | mailx -s "`hostname` : precheck info " Your_email

 

Postgres DB Fundamentals

 

vacuum --> Defrag in Oracle

 

In postgres Tables gets Bloated due to DML's

A tuple is an internal representation of a row

 

Vacuum	Vacuum FULL
Free up dead rows for reuse, Selects and DMLs Allowed No Exclusive Locks, OS Space NOT released	Rewrite the table with no dead rows (Tuple), No DML’s Including select Allowed, Puts EXCLUSIVE Lock, OS Space will be released

Tune AutoVacuum:

set Vacuum_cost_page IO limit parameters
No. of Workers (I/O Intensive)
 

Autovacuum_vacuum_threshold :

min number of updates/deletes tuples needed to trigger a VACUUM in any table. The default is 50 tuples.

 

Autovacuum_vacuum_scale_factor:

Fraction of the table size (in terms of no. of rows) to decide whether vacuum should be triggered. Default is 0.2

 

Transaction wraparound

·        TXID's can go up till ~4 billion (32 bit ID)

·        Postgres will stop accepting commands when there are fewer than one million transactions left before the maximum XID value is reached.

 

   Why Happens this ?

·        Auto-vacuum is set to turned off

·        Heavy DML Operation

·        Many session or connection’s holding lock’s for very long time

 

   What happens in this Situation

·        PostgreSQL will stop accepting DML statements and switches to READONLY mode.

 

   FIX:

·        Stop and Bring up DB into single user mode

·        Run Vacumm FULL on entire DB (vacuumdb --all)

·        Once done stop ad restart postgres normally

OEM Modifying-em-metric-threshold sizeOfOSAuditFiles

 

In order to modify “sizeOfOSAuditFiles” metric for all the targets in OEM when you have several of them use below script to do all in one shot

Check the current values/setting for your targets 

select  * from sysman.MGMT$METRIC_COLLECTION A
where A.METRIC_NAME='sizeOfOSAuditFiles'
and WARNING_THRESHOLD <'2000';
--and target_name='<target_name>

 
Generate emcli command using below
 

select
'emcli modify_threshold -target_name="'||A.target_name||'" -target_type="oracle_database" -metric="sizeOfOSAuditFiles" -column="FILE_SIZE" -warning_threshold="2000" -critical_threshold="5000" -force'
from sysman.MGMT$METRIC_COLLECTION A
where A.METRIC_NAME='sizeOfOSAuditFiles'
and WARNING_THRESHOLD <='2000';

 
Place the above emcli commands in shell script and run from OMS repo server

$ emcli login -username=sysman 

vi modify_audit.sh
 
[oracle@omshost CB]$ ll
total 20
-rw-r-----. 1 oracle dba 18471 Apr 22 08:42 modify_audit.sh
 
[oracle@ omshost CB]$ chmod 755 modify_audit.sh
total 20
-rwxr-xr-x. 1 oracle dba 18471 Apr 22 08:42 modify_audit.sh
 
[oracle@ omshost CB]$ sh modify_audit.sh
 

 

 and if you want to disable this metric itself then follow below oracle Doc, thanks 

13c EM: How To Disable noOfOSAuditFiles.pl Running On Agent Side (Doc ID 2442647.1)

 

Postgres Backup/Restore

 

pg_dump    à Creates a backup of ONE database at a time

pg_dumpall  à Can back up ALL of your databases simultaneously,

 

pg_dump -U username -W -F t database_name > c:\backup_file.tar

 
-F : specifies the output file format that can be one of the following:
    ·        c: custom-format archive file format
    ·        d: directory-format archive
    ·        t: tar
    ·        p: plain-text SQL script file (Default)
-h Specify database server host
 
-p   Specify database server port
-U  Specify the user which is used to connect to the PostgreSQL database server
-W Used to prompt for a password before connecting to the PostgreSQL server
-d   Specify the database to dump
 
 
 

SCHEMA backup
 

pg_dump --username=user --password --schema-only [schema_name] > database_schema.sql

pg_dump --schema-only DATABASE > schema.sql

 
 
EX:
pg_dump --clean --create --file /tmp/DB-$(date +%Y%m%d).pgdump --format=custom --no-unlogged-table-data  <dbname>
 
 
psql --dbname=DBNAME --command="select pg_start_backup('CurBuild');"
 

Restore

To import a single database testdb from the tar dumpfile

pg_restore -c -U username -W -F t -d testdb dump.tar

 

To import ALL databases from tar dumpfile

pg_restore -c -U username -W -F t dump.tar

To import 1 database from .sql backup

postgres=# create database

psql   -d <New_DBNAME>    < dump.sql 

Validate :

-bash-4.2$ psql

psql (15.4)

Type "help" for help.

postgres=#  \l

postgres=# \c   <new_dbname>

postgres=#  SELECT pg_size_pretty( pg_database_size(‘NEW_DBNAME’));