Oracle Management Pack usages

#######################################################
#
# Oracle Management Pack usages
# =============================
#
# http://www.dbaexpert.com/blog/determining-usage-of-oracle-management-packs/
# https://petesdbablog.wordpress.com/2013/04/06/disable-oracle-diagnostic-pack-tuning-pack/
#
#######################################################


# If you leverage any of the SQL scripts from the $ORACLE_HOME/rdbms/admin directory, you will be liable for the diagnostic pack:
• aawrload.sql
• addmrpti.sql
• addmrtp.sql
• ashrpt.sql
• ashrpti.sql
• awrddrpi.sql
• awrddrpt.sql
• awrextr.sql
• awrextr.sql
• awrinfo.sql
• awrload.sql
• awrrpt.sql
• awrrpti.sql
• awrsqrpi.sql
• awrsqrpt.


col name format A30
col detected format 9999
col samples format 9999
col used format A5
col interval format 9999999
SELECT name,
       detected_usages detected,
                 total_samples   samples,
                 currently_used  used,
                 to_char(last_sample_date,'MMDDYYYY:HH24:MI') last_sample,
                 sample_interval interval
FROM dba_feature_usage_statistics
WHERE detected_usages > 0
order by 1;


col name format A30
col detected format 9999
col samples format 9999
col used format A5
col interval format 9999999
SELECT name,
       detected_usages detected,
                 total_samples   samples,
                 currently_used  used,
                 to_char(last_sample_date,'MMDDYYYY:HH24:MI') last_sample,
                 sample_interval interval
FROM dba_feature_usage_statistics
WHERE name = 'Automatic Workload Repository';
show parameter control_management_pack_access
ALTER SYSTEM SET control_management_pack_access=NONE;




cd ${ORACLE_HOME}/rdbms/admin
mv awrgrpt.sql _donotuse_awrgrpt.sql
mv awrrpt.sql _donotuse_awrrpt.sql

RAC/HAS checking

################################
#
# Check for RAC or HAS env - run as root user
#
#
################################


for z in SED
do
GH=`cat /etc/oratab | grep "^+" | cut -f2 -d":"`
export GRID_HOME=${GH}
export PATH=${GRID_HOME}/bin:${PATH}
echo -e "\nChecking type of installation:"
A=`ps -ef | grep -i "orarootagent" | grep -v "grep" | awk '{print $1}' | uniq`
if [ -z ${A} ]
then
  echo -e "\nThis is NOT a RAC host:"
  crsctl check has
else
  CLUN=`cemutlo -n | tr [:lower:] [:upper:]`
  echo -e "\nThis is a RAC host for cluster: ${CLUN}"
  crsctl check crs
  echo "Hostnames:"
  olsnodes
  MMH=`crsctl status resource ora.mgmtdb | grep STATE | awk '{print $NF}'`
  echo -e "Management database host: ${MMH}"
fi
echo -e "\n"
done

PL/SQL - Commit in batches test

for z in SED
do
sqlplus /nolog<<END
connect JUSTIN/JUSTIN
set pages 100 lines 220
drop table sed_flip_1;
create table sed_flip_1 ( x int );
set serveroutput on
declare
 CNT number := 0;
begin
    for i in 1 .. 500001
    loop
        insert into sed_flip_1 values (i);
        CNT := CNT + 1;
        if CNT = 1000 then
        commit work write immediate wait;
 dbms_output.put_line('Committing now...'||CNT||' '||i);
        CNT := 0;
        end if;
    end loop;
end;
/
-- truncate table justin.sed_flip_1;
delete from justin.sed_flip_1;
disconnect
END
done

Opatch version check

################################
#
#
# OPATCH Version check
#
#
#
#
################################

for z in SED
do
USR=`echo $USER`
if [ ${USR} = 'oracle' ]
 then
  ORACLE_HOME=`cat /etc/oratab | grep "oracle/product" | cut -f2 -d":"`
elif [ ${USR} = 'grid' ]
  then
  ORACLE_HOME=`cat /etc/oratab | grep "grid/product" | grep -v "^#" | cut -f2 -d":"`
else
  echo -e "\nERROR - run as either oracle or grid user\n\n"
  RSP=Abort
fi
if [ -z ${RSP} ]
  then
cd $ORACLE_HOME
echo -e "\nChecking Opatch version:"
OPatch/opatch version
echo -e "\n"
fi
done

Restoring Oracle binaries

# Restoring the Oracle inventory

cd /data1/oraInventory
ls -altr
mv oraInventory oraInventory_good
mkdir oraInventory
cp ~/dbinventory_bigley_20141106_bkp.tar ^C
cd oraInventory
cp ~/dbinventory_bigley_20141106_bkp.tar .
tar -zxvf dbinventory_bigley_20141106_bkp.tar

# Restoring the Oracle Binaries - as root user
cd $ORACLE_HOME && cd ..
mv dbhome_1 dbhome_1_good
mkdir dbhome_1
cd dbhome_1
cp /home/oracle/dbhome_bigley_20141106_bkp.tar .
tar -zxvf dbhome_bigley_20141106_bkp.tar

Export data pump - schema export

#########################
# Create the ORACLE user
#########################

check values of
 SHOW PARAMETER os_authent_prefix



export ORACLE_OWNER=${LOGNAME}

sqlplus /nolog<<END
connect / as sysdba
set pages 100 lines 220
create user ops\$$ORACLE_OWNER identified externally
default tablespace USERS
temporary tablespace TEMP
profile admin
/
connect / as sysdba
grant create table to ops\$$ORACLE_OWNER;
GRANT UNLIMITED TABLESPACE TO ops\$$ORACLE_OWNER ;
GRANT DATAPUMP_EXP_FULL_DATABASE TO ops\$$ORACLE_OWNER ;
grant EXP_FULL_DATABASE to ops\$$ORACLE_OWNER ;
grant IMP_FULL_DATABASE to ops\$$ORACLE_OWNER ;
GRANT DATAPUMP_IMP_FULL_DATABASE TO ops\$$ORACLE_OWNER ;
ALTER USER ops\$$ORACLE_OWNER DEFAULT ROLE ALL ;
GRANT FLASHBACK ANY TABLE TO EXP_FULL_DATABASE ;
-- GRANT READ ON DIRECTORY EXPDP TO IMP_FULL_DATABASE;
-- GRANT WRITE ON DIRECTORY EXPDP TO IMP_FULL_DATABASE;
GRANT READ ON DIRECTORY EXPDP TO ops\$$ORACLE_OWNER;
GRANT WRITE ON DIRECTORY EXPDP TO ops\$$ORACLE_OWNER;
END

# If os_authent_prefix is null

export ORACLE_OWNER=${LOGNAME}

sqlplus /nolog<<END
connect / as sysdba
set pages 100 lines 220
create user $ORACLE_OWNER identified externally
default tablespace USERS
temporary tablespace TEMP
profile admin
/
connect / as sysdba
alter user ORACLE quota unlimited on USERS;
grant create table to $ORACLE_OWNER;
-- GRANT UNLIMITED TABLESPACE TO $ORACLE_OWNER ;
GRANT DATAPUMP_EXP_FULL_DATABASE TO $ORACLE_OWNER ;
GRANT DATAPUMP_IMP_FULL_DATABASE TO $ORACLE_OWNER ;
grant EXP_FULL_DATABASE to $ORACLE_OWNER ;
grant IMP_FULL_DATABASE to $ORACLE_OWNER ;
ALTER USER $ORACLE_OWNER DEFAULT ROLE ALL ;
GRANT FLASHBACK ANY TABLE TO EXP_FULL_DATABASE ;
grant EXECUTE ON DBMS_DATAPUMP to $ORACLE_OWNER;
END

#########################
# Generate the export script
#########################

# Flashback Time value should look like this
FLASHBACK_TIME=\"TO_TIMESTAMP\(\'21-05-2013 17:00:59\',\'DD-MM-YYYY HH24:MI:SS\'\)\" \

for i in 1
do
echo "Enter schema: "
read SCHEMA

PWD=`pwd`
DPAREX=`sqlplus -s /nolog<<END
connect / as sysdba
set pages 100 lines 220 heading off feedback off
select DIRECTORY_NAME
 from dba_directories
where DIRECTORY_PATH like '${PWD}%'
/
END`
echo -e "The datapump directory is called: = \t${DPARE}\n\n"

if [ -z ${DPAREX} ]
  then echo -e "Incorrect directory - change\n\tABORTING!!!"
else
  echo "Correct directory"
  DPARE=`echo ${DPAREX}`
  export DPARE
  echo ${DPARE}
  export ORACLE_OWNER=${LOGNAME}
  # DT=`date '+%Y%d%m%H%M'`
  DT=`date '+%Y%m%d_%H%M'`
  # FBDT=`date "+\\"TO_TIMESTAMP\(\'%d/%m/%Y %H:%M:%S\',\'DD-MM-YYYY HH24:MI:SS\'\)\\""`
  FBDT=`date "+\\"TO_TIMESTAMP\(\'%d/%m/%Y %H:%M:%S\',\'DD-MM-YYYY HH24:MI:SS\'\)"`
  #
  # Test Script
  #
  echo "nohup expdp \"/\" schemas=${SCHEMA} \\
  directory=${DPARE} \\
  dumpfile=expdp${SCHEMA}_${DT}_%U.dmp \\
  logfile=expdp${SCHEMA}_${DT}.log \\
  JOB_NAME=EXP_${SCHEMA}_${DT} \\
  COMPRESSION=ALL \\
  FILESIZE=500M \\
  CLUSTER=N \\ <<-- remove if directory is on a shared filesystem
  FLASHBACK_TIME="\\"${FBDT}"\\"\" \\
  PARALLEL=15 &"
  #
fi
done

RMAN - delete archivelogs/backup sets from RECO area

###############################################
#
# RMAN - delete archivelogs/backup sets from RECO area
#
#
#
#
###############################################

# Find what is available
for z in SED
do
NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'
export NLS_DATE_FORMAT
rman target / <<END
list  backup of database summary completed before 'SYSDATE -14' device type ='DISK';
END
done

# Delete backups
for z in SED
do
NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'
export NLS_DATE_FORMAT
rman target / <<END
list  backup of database summary completed before 'SYSDATE -14' device type ='DISK';
run {
allocate channel D1 type disk;
delete noprompt backupset completed before 'SYSDATE-14';
}
END
done


# Alternate versions to delete - NOTE - define BA first
for z in SED
do
export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'
rman target / debug trace=name.trc <<END
run {
   allocate channel c1 type disk;
   backup format '${BA}/${ORACLE_SID}_arch_%c:%s:%t_.arc'
   archivelog all not backed up 2 times;
   delete noprompt archivelog all completed before 'sysdate - 12/24';
   release channel c1;
}
END
done

for z in SED
do
export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'
rman target / debug trace=name.trc <<END
run {
   allocate channel c1 type disk;
   backup format '${BA}/${ORACLE_SID}_arch_%c:%s:%t_.arc'
   archivelog all not backed up 2 times delete input;
   release channel c1;
}
END
done