Oracle DB Admin

How to drop a Oracle RAC database manually

3 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

4 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

4 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

4 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

4 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

Oracle Tablespaces Articles

4 years ago by KTEXPERTS TEAMOracle Tablespaces

Dear Readers, Please check the below articles to get good knowledge on Oracle Tablespaces. SNO Article Name Article Link 1 Tablespace Management in Oracle. https://www.ktexperts.com/tablespace-management-in-oracle/ 2 Oracle : Storage Management https://www.ktexperts.com/oracle-storage-management/ 3 Transportable Tablespace between two pluggable database(PDB’s) https://www.ktexperts.com/transportable-tablespace-between-two-pluggable-databasepdbs/ 4 ORACLE: Cross Platform Transportable Tablespace… Read More

RMAN (Recovery Manager) Articles

4 years ago by KTEXPERTS TEAMRMAN (Recovery Manager)

Dear Readers, Please check the below articles to get good knowledge on RMAN (Recovery Manager). SNO Article Name Article Link 1 How to add Container database to Existing RMAN Recovery catalog https://www.ktexperts.com/how-to-add-container-database-to-existing-rman-recovery-catalog/ 2 How to Create an RMAN Recovery Catalog https://www.ktexperts.com/how-to-create-an-rman-recovery-catalog/ 3 Cloning a Standalone… Read More

Oracle 18c Articles

4 years ago by KTEXPERTS TEAMOracle 18c

Dear Readers, Please check the below articles to get good knowledge on Oracle 18c. SNO Article Name Article Link 1 Oracle 18c New Features for DBA’s https://www.ktexperts.com/oracle-18c-new-features-for-dbas/ 2 Oracle 18C new feature: Private Temporary Tables https://www.ktexperts.com/oracle-18c-new-feature-private-temporary-tables/ 3 Oracle 18C new feature: Cancel SQL instead of… Read More

Data Pump (Export & Import) Articles

4 years ago by KTEXPERTS TEAMOracle Datapump (Export & Imports)

Dear Readers, Please check the below articles to get good knowledge on Data Pump (Export & Import). SNO Article Name Article Link 1 How to Kill & Trace Datapunp Job https://www.ktexperts.com/how-to-kill-trace-datapunp-job/ 2 How to Generate DDL from existing dumpfile https://www.ktexperts.com/how-to-generate-ddl-from-existing-dumpfile/ 3 How to utilize PARFILE… Read More

Oracle DataGuard Articles

4 years ago by KTEXPERTS TEAMOracle Dataguard

Dear Readers, Please check the below articles to get good knowledge on Oracle DataGuard Articles. SNO Article Name Article Link 1 Step By Step Configuration of Oracle DGMGRL Utility https://www.ktexperts.com/step-by-step-configuration-of-oracle-dgmgrl-utility/ 2 Oracle Physical Standby Data guard in 11g https://www.ktexperts.com/oracle-physical-standby-data-guard-in-11g/ 3 Switchover and Switchback in Oracle… Read More