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

ORA-01110: data file 201

#########################################
#
# ORA-01110: data file 201
#
##########################################

-- Problem
DDE: Problem Key 'ORA 1110' was flood controlled (0x1) (no incident)
ORA-01110: data file 201: '+DATA01'
ORA-01186: file 201 failed verification tests
ORA-01157: cannot identify/lock data file 201 - see DBWR trace file

-- Gather status of files
col name format a76
col MEMBER format a76
select file#,status,name from v$datafile;
select file#,status,name from v$tempfile;
select GROUP# , MEMBER from v$logfile
where member like '%DATA01%';

-- TEMP datafile showed a problem

     FILE# STATUS  NAME
---------- ------- ----------------------------------------------------------------------------
         1 ONLINE  +DATA01

       
-- The example below shoed that the ASM space was exhausted due to old files being resident
SQL> alter tablespace TEMP add tempfile size 10G;
alter tablespace TEMP add tempfile size 10G
*
ERROR at line 1:
ORA-01119: error in creating database file '+DATA01'
ORA-17502: ksfdcre:4 Failed to create file +DATA01
ORA-15041: diskgroup "DATA01" space exhausted

-- Workaround
create temporary tablespace temp_sed ;
alter database default temporary tablespace temp_sed;
drop tablespace temp;
select file#,status,name from v$tempfile;
create temporary tablespace TEMP;
alter database default temporary tablespace temp;
drop tablespace temp_sed;
select file#,status,name from v$tempfile;

-- Post check
select file#,status,name from v$tempfile;

FILE# STATUS  NAME
----- ------- ----------------------------------------------------------------------------
         1 ONLINE  +DATA01/qt01dda_tss2l592/tempfile/temp.265.852292851

-- Thanks to the following for advice.

Sameer Shaik

http://www.shaiksameer.com/2011/07/ora-01157-cannot-identifylock-data-file.html

Database sanity checker

##################################
#
# Database sanity checker
#
##################################

for i in `srvctl config | grep ${ORACLE_SID%[12]}`
do
echo -e "\nChecking database status:"
srvctl status database -d ${i} -v
echo -e "\nChecking service status:"
srvctl status service -d ${i} -s ${ORACLE_SID%[12]}_OCI
DIR1=`adrci<<END | grep -E "ADR base" | awk '{print $NF}' | sed 's/\"//g'
END`
DIR2=`adrci<<END | grep ${ORACLE_SID%[12]} | grep ${HOSTNAME%n1} | awk '{print $NF}' | tr '[:upper:]' '[:lower:]'
show alert
Q
END`
DIR2=`echo $DIR2 | sed -r 's/(.*)\/.*/\1/'`
DIR3=${ORACLE_SID}
TRACE_DIR="${DIR1}/${DIR2}/${DIR3}/trace/"
echo -e "\nChecking alert log for errors:"
tail -200 ${DIR1}/${DIR2}/${DIR3}/trace/alert*.log | grep "ORA-"
sqlplus -s /nolog<<END
connect / as sysdba
set heading on pages 100 feedback off
set serveroutput on
prompt
prompt Is the database clustered?
prompt
SET SERVEROUTPUT ON
BEGIN
  IF DBMS_UTILITY.is_cluster_database THEN
    DBMS_OUTPUT.put_line(chr(09)||' This database is Clustered'||chr(10));
  ELSE
    DBMS_OUTPUT.put_line(chr(09)||' This database is Not Clustered'||chr(10));
  END IF;
END;
/
declare
   RN      number(9);
   SED     varchar2(100);
BEGIN
        select DEST_NAME||' '||TARGET||' '||DESTINATION||' '||DATAGUARD_BROKER||' '||DATABASE_ROLE into SED from v\$archive_dest a, v\$database b where TARGET(+) != 'PRIMARY' and DEST_NAME = 'LOG_ARCHIVE_DEST_2';
dbms_output.put_line(chr(09)||'Dataguard Status: --> '||SED);
EXCEPTION
   WHEN NO_DATA_FOUND THEN
   dbms_output.put_line (chr(09)||' THIS IS NOT A DATAGUARDED DATABASE.'||chr(10));
end;
/
col "Database Status" format a50
SELECT
'HOST:= '||SYS_CONTEXT('USERENV','SERVER_HOST')||chr(10)||
'Dataguard Role:= '||SYS_CONTEXT('USERENV','DATABASE_ROLE')||chr(10)||
'Service:= '||SYS_CONTEXT('USERENV','SERVICE_NAME') "Database Status"
FROM dual
/
col name format a25
col "DB Status" format a50
col OM format a10
select name||decode(substr(name,4,1),'H',' - HDS Database','T',' - PDS Database',null) "DB Status"
, open_mode OM
from v\$database
/
END
echo -e "\n\n"
done

Oracle and AMM /dev/shm

###############################################
#
# What is /dev/shm
# http://blog.oracle48.nl/oracle-11g-amm-memory_target-memory_max_target-and-dev_shm/
#
###############################################
for k in y
do
MV=0
echo -e "\n"
# for x in `ps -ef | grep smon | grep -Ev 'ASM|grep|bin' | cut -f3 -d "_"`
for x in `ps -ef | grep smon | grep -Ev 'grep|bin' | cut -f3 -d "_"`
do
MC=0
for i in `ls -al /dev/shm | grep ${x} | awk '{print $5}'`
do
MC=`expr ${MC} + ${i}`
done
echo "Checking memory for: ${x} - ${MC}"
A=`echo "scale=2;"\`echo ${MC}\`/1048576/1024 | bc -l`
echo -e "\t${A}G"
MV=`expr ${MC} + ${MV}`
done
echo "Total usage: "
B=`echo "scale=2;"\`echo ${MV}\`/1048576/1024 | bc -l`
echo -e "\t${B}G"
# echo "Total usage: ${MV}"
df -h /dev/shm
echo -e "\n"
done

Empty a schema(s) without dropping

#############################################
#
# Empty schema(s) script generation script
#
#
#############################################

# Check listof users on the database
sqlplus -s /nolog<<END
connect / as sysdba
set pages 100 lines 220 heading off feedback off
select ''''||username||''','
 from dba_users
where username NOT in
('ANONYMOUS','APEX_PUBLIC_USER','APPQOSSYS','CTXSYS','DBA_TOOLS','DBSNMP','DIP','DMSYS', 'ESMDBA','ESMDBA1','EXFSYS','HGIOSD','JPMCEPV','MDDATA','MDSYS','MGMT_VIEW','OLAPSYS','OPS$ORACLE','ORACLE', 'ORACLE_COM','ORDDATA','ORDPLUGINS','ORDSYS','OSDAUD','OUTLN','SCOTT','SECURITY','SI_INFORMTN_SCHEMA','SRDF_DBA', 'SYS','SYSMAN','SYSTEM','TSMSYS','WMSYS','XDB')
order by 1
/
END

# Generate the drop commands
for z in SED
do
echo -e "Enter list of names as in 'TEST','ONE','TWO'  :"
read USERS

for i in 1
do
echo -e "
spool sed_user_drop.log

select object_type,count(*) from dba_objects where owner in (${USERS});

"

echo -e "
SET SERVEROUTPUT ON SIZE 1000000
BEGIN
  FOR cur_rec IN (SELECT owner,object_name, object_type
                  FROM   dba_objects
                  WHERE owner in (${USERS}) and  object_type IN ('TABLE', 'VIEW', 'PACKAGE', 'PROCEDURE', 'FUNCTION', 'SEQUENCE','SYNONYM','TYPE','TYPE BODY'))
LOOP
    BEGIN
     IF cur_rec.object_type = 'TABLE' THEN
        EXECUTE IMMEDIATE 'DROP ' || cur_rec.object_type ||' ' || cur_rec.owner||'.' || cur_rec.object_name || ' CASCADE CONSTRAINTS';
      ELSE
        EXECUTE IMMEDIATE 'DROP ' || cur_rec.object_type ||' ' || cur_rec.owner||'.' || cur_rec.object_name || '';
      END IF;

    EXCEPTION
        WHEN OTHERS THEN
        DBMS_OUTPUT.put_line('FAILED: DROP ' || cur_rec.object_type || ' \"' || cur_rec.object_name || '\"');
    END;
  END LOOP;
END;
/



BEGIN
  FOR cur_rec IN (SELECT owner,object_name, object_type
                  FROM   dba_objects
                  WHERE owner in (${USERS}) and  object_type IN ('MATERIALIZED VIEW'))
LOOP
    BEGIN
     IF cur_rec.object_type = 'TABLE' THEN
        EXECUTE IMMEDIATE 'DROP ' || cur_rec.object_type ||' ' || cur_rec.owner||'.' || cur_rec.object_name || ' CASCADE CONSTRAINTS';
      ELSE
        EXECUTE IMMEDIATE 'DROP ' || cur_rec.object_type ||' ' || cur_rec.owner||'.' || cur_rec.object_name || '';
      END IF;

    EXCEPTION
        WHEN OTHERS THEN
        DBMS_OUTPUT.put_line('FAILED: DROP ' || cur_rec.object_type || ' \"' || cur_rec.object_name || '\"');
    END;
  END LOOP;
END;
/


prompt If required purge recycle_bin

purge dba_recycle_bin;

select object_type,count(*) from dba_objects where owner in (${USERS});

prompt confirm that all objects are dropped apart from DB LINK

"
done > sed_drop_user.info
echo -e "\nCheck the details in: sed_drop_user.info
done

Exclude Schema when exporting full database

######################
#
# Full export data pump excluding 'Oracle Schemas'

# To export whole database except one or more schemas
#
######################


for i in 1
do
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"
SCHEMLIST=`sqlplus -s /nolog<<END
connect / as sysdba
set pages 100 lines 220 heading off feedback off
select ''''||username||''','
 from dba_users
where username in
('ANONYMOUS','APEX_PUBLIC_USER','APPQOSSYS','CTXSYS','DBA_TOOLS','DBSNMP','DIP','DMSYS', \\  'ESMDBA','ESMDBA1','EXFSYS','HGIOSD','JPMCEPV','MDDATA','MDSYS','MGMT_VIEW','OLAPSYS','OPS$ORACLE','ORACLE', \\
'ORACLE_COM','ORDDATA','ORDPLUGINS','ORDSYS','OSDAUD','OUTLN','SCOTT','SECURITY','SI_INFORMTN_SCHEMA','SRDF_DBA', \\
'SYS','SYSMAN','SYSTEM','TSMSYS','WMSYS','XDB')
order by 1
/
END`
SL=`echo $SCHEMLIST | sed 's/ //g' | sed 's/,$//g'`
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 \"/\" \\
  directory=${DPARE} \\
  dumpfile=expdp${SCHEMA}_${DT}_%U.dmp \\
  logfile=expdp${SCHEMA}_${DT}.log \\
  JOB_NAME=EXP_${SCHEMA}_${DT} \\
  COMPRESSION=ALL \\
  FLASHBACK_TIME="\\"${FBDT}"\\"\" \\
  PARALLEL=15 \\
  ESTIMATE_ONLY=Y \\
  FULL=Y \\
  EXCLUDE=SCHEMA:\"IN\(${SL}\)\" &"
  #
  # If schema list too long, then break using backslashes
  echo -e "\n\n"
fi
done

Import Data Pump SQLFILE

###############################
#
# Import Data Pump create SQLFILE script
#
#
#
###############################

for i in 1
do

PWD=`pwd`
DPAREX=`sqlplus -s /nolog<<END
connect / as sysdba
set pages 100 lines 220 heading off feedback off
select * from
(select DIRECTORY_NAME
 from dba_directories
where DIRECTORY_PATH like '${PWD}%')
where rownum <2
/
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}
  echo "Enter schema: "
  read SCHEMA
  export ORACLE_OWNER=${LOGNAME}
  # DT=`date '+%Y%d%m%H%M'`
  DT=`date '+%Y%m%d_%H%M'`
  EXPDF=`ls expdp*.dmp | cut -f-4 -d"_" | uniq`
  TRUNCF=`ls expdp*.dmp | cut -f-4 -d"_" | uniq | cut -f1-3 -d"_" | uniq`
  FBDT=`date "+\"TO_TIMESTAMP('%d/%m/%Y %H:%M:%S','DD-MM-YYYY HH24:MI:SS')\""`
  PCNT=`ls expdp*.dmp | cut -f-4 -d"_" | uniq | wc -l`
  #
  # Test Script
  #
  echo -e "  nohup impdp \"/\" \\"
  echo -e "  directory=${DPARE} \\
  dumpfile=${TRUNCF}_%U.dmp \\
  logfile=impdp${SCHEMA}_${DT}.log \\
  JOB_NAME=IMP_${SCHEMA}_${DT} \\
  SQLFILE=IMP_${SCHEMA}_${DT}.sql \\
  PARALLEL=${PCNT} &"
  #
fi
echo -e "\n\n"
done

Linux Red Hat version check

####################
#
# Linux Red Hat release/version
#
####################

for z in SED
do
echo -e "\nChecking Linux version: \n"
A=`cat /etc/redhat-release`
B=`uname -r`
C=`dmesg | head -10 | grep "Linux version"`
echo -e "\nThe version is: ${A}"
echo -e "The release is: ${B}"
echo -e "In detail: ${C}"
echo -e "\n"
done