Oracle DB Admin – Page 3

Protected: Oracle : Shell Script to Send email if RAC services not running

4 years ago by Rakesh SahuOracle Production Support Scripts

There is no excerpt because this is a protected post.

CONFIGURING Object Storage & Archive Storage

4 years ago by Venkata Sai PavanOracle Cloud

CONFIGURING Object Storage & Archive Storage In this article we will learn about “CONFIGURING Object Storage & Archive Storage” Object Storage provides unlimited, high-performance, durable, and secure data storage. Data is uploaded as objects that are stored in buckets. It can store an unlimited amount… Read More

Autonomous Transaction Processing

4 years ago by Venkata Sai PavanOracle Cloud

Autonomous Transaction Processing In this article, we will know about “ATP” Oracle Autonomous Transaction Processing delivers a self-driving, self-securing, self-repairing database service that can instantly scale to meet the demands of a variety of applications: mission-critical transaction processing, mixed transactions and analytics, IoT, JSON… Read More

HOW TO CONNECT AND ACCESS ATP DATABASE

4 years ago by Venkata Sai PavanOracle DB Admin

HOW TO CONNECT AND ACCESS ATP DATABASE In this article, we will know “HOW TO CONNECT AND ACCESS ATP DATABASE” We can access ATP database through SQL developer for that we have to download wallet file of this particular database 1. Click on the ATP… Read More

How to Install Oracle VM Virtual Box On Windows

5 years ago by Ramesh ReddyOracle DB Admin

Hi Dears, In this article, we will see Install Oracle VM Virtual Box On Windows. Oracle VirtualBox Oracle VM VirtualBox is a free, open-source, cross-platform application for creating, managing and running virtual machines (VMs). Virtual machines are computers whose hardware components are emulated by the… Read More

How to drop a Oracle RAC database manually

5 years ago by Binnary Ajay KumarOracle Architecture

How to drop a two-node Oracle RAC database database manually. In this article, we wrote an Clear steps for dropping a two node oracle database with Pre-requisite steps. Server: sewnpossqa01/02 Database : npossqa1 DB Unique name: sewnpossqa1 Step 1: Take consistent export of NPOSSQA1 database… Read More

Oracle : Shell Script to gather stats for Stale tables

5 years ago by Rakesh SahuOracle Production Support Scripts

Dear Readers, In this article, we wrote an automation job for Shell Script to gather stats for Stale tables. Note : 1) Please test the Shell script in Non-Prod before implementing it in Production. Script for gather stats for Stale tables

#!/bin/bash

function PreChks
{
ORACLE_HOME=/u01/app/oracle/product/12.1.0.2
if [[ ! -e $ORACLE_HOME ]]; then
        echo " "
        echo "Error :::  Given ORACLE_HOME :: $ORACLE_HOME does not exist!!! "
        echo " "
        exit;
fi

ORACLE_SID=mvmrp1
export PATH=$ORACLE_HOME/bin:$PATH:.
export ORACLE_BASE ORACLE_HOME PATH ORACLE_SID
LOGFILE=/tmp/main.log
echo "Task Start at `date` " > $LOGFILE
export MAIL_RECIPIENT_INFO='ktexperts@admin.com'
}

function get_objects
{
echo "#CHECK FOR DATABASE and Tables  STATUS##">>"$LOGFILE"
DB_OPEN_MODE=`sqlplus -s / as sysdba <<EOF
set pagesize 0 feedback off verify off heading off echo off
select open_mode from v\\$database;
EOF`
echo "$DB_OPEN_MODE">>"$LOGFILE"
if [[ $DB_OPEN_MODE = "READ WRITE" ]]
then
echo "Fetch Stale_Stats Data"
sqlplus -s / as sysdba <<EOF > /tmp/a.log
set pagesize 0 feedback off verify off heading off echo off
set linesize 300
select table_name, owner from dba_tab_statistics where stale_stats='YES' and owner in ('MVM1') order by table_name;
EOF
else
##DATABASE IS NOT OPEN##
  mail_subject="Database $INSTANCE is not open hence Gather Stats cant not Start...."
        echo " "
        echo "Database $INSTANCE is not open hence Gather Stats cant not Start...."
        echo " "
   mailx -s "${mail_subject}" ${MAIL_RECIPIENT_INFO}<<EOF
 " Database $INSTANCE is not open hence Gather Stats cant not Start...."
EOF
exit 0
fi
cat /tmp/a.log >> /tmp/main.log
}


function gather_stats
{
cat /tmp/a.log | while read p
do
tbl=`echo $p | awk '{ print $1 }'`
owner=`echo $p | awk '{ print $2 }'`
SQL=`echo "Exec dbms_stats.gather_table_stats('$owner','$tbl',ESTIMATE_PERCENT=>dbms_stats.auto_sample_size,METHOD_OPT=>'for all indexed columns size 1',CASCADE=>True,degree=> 4);"`
echo $SQL >> /tmp/main.log
sqlplus -s / as sysdba <<EOF >> /tmp/main.log
Exec dbms_stats.gather_table_stats('$owner','$tbl',ESTIMATE_PERCENT=>dbms_stats.auto_sample_size,METHOD_OPT=>'for all indexed columns size 1',CASCADE=>True,degree=> 4);
EOF
done
}


PreChks
get_objects
gather_stats
get_objects
mail_subject="mvmrp1 Stale Stats Report"
cat "$LOGFILE" | mailx -s "${mail_subject}" ${MAIL_RECIPIENT_INFO}

#!/bin/bash

function PreChks

{

ORACLE_HOME=/u01/app/oracle/product/12.1.0.2

if [[ ! -e $ORACLE_HOME ]]; then

echo " "

echo "Error ::: Given ORACLE_HOME :: $ORACLE_HOME does not exist!!! "

echo " "

exit;

ORACLE_SID=mvmrp1

export PATH=$ORACLE_HOME/bin:$PATH:.

export ORACLE_BASE ORACLE_HOME PATH ORACLE_SID

LOGFILE=/tmp/main.log

echo "Task Start at `date` " > $LOGFILE

export MAIL_RECIPIENT_INFO='ktexperts@admin.com'

}

function get_objects

{

echo "#CHECK FOR DATABASE and Tables STATUS##">>"$LOGFILE"

DB_OPEN_MODE=`sqlplus -s / as sysdba <<EOF

set pagesize 0 feedback off verify off heading off echo off

select open_mode from v\\$database;

EOF`

echo "$DB_OPEN_MODE">>"$LOGFILE"

if [[ $DB_OPEN_MODE = "READ WRITE" ]]

then

echo "Fetch Stale_Stats Data"

sqlplus -s / as sysdba <<EOF > /tmp/a.log

set pagesize 0 feedback off verify off heading off echo off

set linesize 300

select table_name, owner from dba_tab_statistics where stale_stats='YES' and owner in ('MVM1') order by table_name;

EOF

else

##DATABASE IS NOT OPEN##

mail_subject="Database $INSTANCE is not open hence Gather Stats cant not Start...."

echo " "

echo "Database $INSTANCE is not open hence Gather Stats cant not Start...."

echo " "

mailx -s "${mail_subject}" ${MAIL_RECIPIENT_INFO}<<EOF

" Database $INSTANCE is not open hence Gather Stats cant not Start...."

EOF

exit 0

cat /tmp/a.log >> /tmp/main.log

}

function gather_stats

{

cat /tmp/a.log | while read p

tbl=`echo $p | awk '{ print $1 }'`

owner=`echo $p | awk '{ print $2 }'`

SQL=`echo "Exec dbms_stats.gather_table_stats('$owner','$tbl',ESTIMATE_PERCENT=>dbms_stats.auto_sample_size,METHOD_OPT=>'for all indexed columns size 1',CASCADE=>True,degree=> 4);"`

echo $SQL >> /tmp/main.log

sqlplus -s / as sysdba <<EOF >> /tmp/main.log

Exec dbms_stats.gather_table_stats('$owner','$tbl',ESTIMATE_PERCENT=>dbms_stats.auto_sample_size,METHOD_OPT=>'for all indexed columns size 1',CASCADE=>True,degree=> 4);

EOF

done

}

PreChks

get_objects

gather_stats

get_objects

mail_subject="mvmrp1 Stale Stats Report"

cat "$LOGFILE" | mailx -s "${mail_subject}" ${MAIL_RECIPIENT_INFO}

Output :

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

Task Start at Mon Jun 14 21:00:01 PDT 2021 #CHECK FOR DATABASE and Tables  STATUS## READ WRITE READ WRITE #CHECK FOR DATABASE and Tables  STATUS## READ WRITE READ WRITE
EMP					MVM1
DEPT				MVM1
SALGRADE			MVM1
Exec dbms_stats.gather_table_stats('MVM1','EMP',ESTIMATE_PERCENT=>dbms_stats.auto_sample_size,METHOD_OPT=>'for all indexed columns size 1',CASCADE=>True,degree=> 4);

PL/SQL procedure successfully completed.

Exec dbms_stats.gather_table_stats('MVM1','DEPT',ESTIMATE_PERCENT=>dbms_stats.auto_sample_size,METHOD_OPT=>'for all indexed columns size 1',CASCADE=>True,degree=> 4);

PL/SQL procedure successfully completed.

Exec dbms_stats.gather_table_stats('MVM1','SALGRADE',ESTIMATE_PERCENT=>dbms_stats.auto_sample_size,METHOD_OPT=>'for all indexed columns size 1',CASCADE=>True,degree=> 4);

PL/SQL procedure successfully completed.

#CHECK FOR DATABASE and Tables  STATUS## READ WRITE

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

Task Start at Mon Jun 14 21:00:01 PDT 2021 #CHECK FOR DATABASE and Tables STATUS## READ WRITE READ WRITE #CHECK FOR DATABASE and Tables STATUS## READ WRITE READ WRITE

EMP MVM1

DEPT MVM1

SALGRADE MVM1

Exec dbms_stats.gather_table_stats('MVM1','EMP',ESTIMATE_PERCENT=>dbms_stats.auto_sample_size,METHOD_OPT=>'for all indexed columns size 1',CASCADE=>True,degree=> 4);

PL/SQL procedure successfully completed.

Exec dbms_stats.gather_table_stats('MVM1','DEPT',ESTIMATE_PERCENT=>dbms_stats.auto_sample_size,METHOD_OPT=>'for all indexed columns size 1',CASCADE=>True,degree=> 4);

PL/SQL procedure successfully completed.

Exec dbms_stats.gather_table_stats('MVM1','SALGRADE',ESTIMATE_PERCENT=>dbms_stats.auto_sample_size,METHOD_OPT=>'for all indexed columns size 1',CASCADE=>True,degree=> 4);

PL/SQL procedure successfully completed.

#CHECK FOR DATABASE and Tables STATUS## READ WRITE

Oracle Architecture Articles

5 years ago by KTEXPERTS TEAMOracle Architecture

Dear Readers, Please check the below articles to get good knowledge on Oracle Architecture. SNO Article Name Article Link 1 Startup and Shutdown modes in Oracle https://www.ktexperts.com/startup-and-shutdown-modes-in-oracle/ 2 ORACLE ARCHITECTURE -4(PHYSICAL REPRESENTATION OF DATABASE) https://www.ktexperts.com/oracle-architecture-4physical-representation-of-database/ 3 ORACLE OPTIONAL BACKGROUND PROCESSES https://www.ktexperts.com/oracle-optional-background-processes/ 4 ORACLE ARCHITECTURE -2… Read More

Oracle Partitioning Articles

5 years ago by KTEXPERTS TEAMOracle Partitioning

Dear Readers, Please check the below articles to get good knowledge on Oracle Partitioning. SNO Article Name Article Link 1 Partition https://www.ktexperts.com/partition/ 2 Oracle : Partitioning using online Redefinition method which requires minimal downtime https://www.ktexperts.com/oracle-partitioning-using-online-redefinition-method-which-requires-minimal-downtime/ 3 Oracle : High Level steps for Partitioning a non… Read More

Oracle ASM Articles

5 years ago by KTEXPERTS TEAMORACLE ASM

Dear Readers, Please check the below articles to get good knowledge on Oracle ASM. SNO Article Name Article Link 1 Oracle : Rename ASM Disk Label in Oracle 12c (RAC) https://www.ktexperts.com/oracle-rename-disk-label-rac/ 2 Oracle : Add ASM Disks to existing Diskgroup (RAC) https://www.ktexperts.com/oracle-add-asm-disks-to-existing-diskgroup/ 3 Oracle :… Read More