#! /bin/ksh
export TOD_DATE=`date +%Y%m%d`
echo "Specify ORACLE_HOME path : "
read ORACLE_HOME
echo "specify GRID_HOME path :"
read GRID_HOME
echo "Specify ORACLE_BASE path :"
read ORACLE_BASE
echo "Specify DBNAME : "
read DBNAME
echo "Specify SID name :"
read SID
echo "Specify ASM SID name :"
read ASM
echo "Specify backup location for prechecks:"
read PRECHKS
export ORACLE_HOME=$ORACLE_HOME
export GRID_HOME=$GRID_HOME
export PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_SID=$SID
echo "###################### sqlplus begin #####################"
sqlplus -s / as sysdba << EOF
set lines 1000
set pages 500
set serverout on
prompt
prompt ################################################################
prompt
spool '$PRECHKS/spool_ver_iv_reg.log'
prompt SQL> SELECT * FROM V\$VERSION;
SELECT * FROM V\$VERSION;
prompt
col COMP_NAME for a50
col VERSION for a30
col STATUS for a30
prompt SQL>select COMP_NAME,VERSION,STATUS from dba_registry
select COMP_NAME,VERSION,STATUS from dba_registry;
prompt SQL>select status,count(*) from dba_objects group by status
select status,count(*) from dba_objects group by status;
prompt SQL>select owner,OBJECT_NAME,CREATED,OBJECT_TYPE,LAST_DDL_TIME,status from dba_invalid_objects
col object_name for a40
select owner,OBJECT_NAME,CREATED,OBJECT_TYPE,LAST_DDL_TIME,status from dba_invalid_objects;
prompt SQL>select owner,count(*) from dba_objects where status='INVALID' group by owner;
select owner,count(*) from dba_objects where status='INVALID' group by owner;
prompt SQL> select owner,object_name,object_type,status from dba_objects where status='INVALID' order by object_type;
select owner,object_name,object_type,status from dba_objects where status='INVALID' order by object_type;
prompt SQL>select sum(bytes)/1024/1024/1024 from dba_data_files;
select sum(bytes)/1024/1024/1024 from dba_data_files;
prompt SQL>select sum(bytes)/1024/1024/1024 from dba_segments;
select sum(bytes)/1024/1024/1024 from dba_segments;
prompt SQL>Show parameter spfile
Show parameter spfile
prompt SQL>show parameter cluster_database
show parameter cluster_database
prompt SQL>show parameter listener
show parameter listener
prompt SQL> show parameter background
show parameter background
prompt SQL>show parameter service_names
show parameter service_names
prompt SQL>CREATE TABLE dbaobj_bkp_bef_upgrd_$TOD_DATE AS (select * from dba_objects);
CREATE TABLE dbaobj_bkp_bef_upgrd_$TOD_DATE AS (select * from dba_objects);
prompt SQL>SELECT * FROM V\$OPTION WHERE PARAMETER in ('Oracle Database Vault','Oracle Label Security');
SELECT * FROM V\$OPTION WHERE PARAMETER in ('Oracle Database Vault','Oracle Label Security');
spool off
spool '$PRECHKS/spool_dblnks.log'
prompt SQL>select count(0) from dba_db_links;
select count(0) from dba_db_links;
prompt generating dynamic sql for checking status of db links
select 'select count(0) from user_tables@'||db_link from dba_db_links;
prompt ###Create db link statement ####
select d.owner, 'create database link "'||d.db_link||'" connect to "'||d.username||'" identified by "'||l.password||'" using '|| '''' ||d.host|| '''' ||';'
from dba_db_links d, sys.link$ l
where d.db_link = l.name
and d.host = l.host
and d.username = l.userid
and d.created = l.ctime
and d.owner <> 'PUBLIC'
order by d.owner, d.db_link, d.host;
spool off
prompt ################ Creating pfile from spfile in $PRECHKS/pfile_init$TOD_DATE.ora #####
create pfile='$PRECHKS/pfile_init$TOD_DATE.ora' from spfile;
spool '$PRECHKS/spool_show_dbparms.log'
set lines 1000
show parameters
exit
EOF
echo "#############################sqlplus end########"
echo "############################ crc check start #####" >> $PRECHKS/spool_crschk.log
echo "### crsctl query crs softwareversion#####" >> $PRECHKS/spool_crschk.log
$GRID_HOME/bin/crsctl query crs softwareversion >> $PRECHKS/spool_crschk.log
echo "##### crsctl query crs releaseversion#####" >> $PRECHKS/spool_crschk.log
$GRID_HOME/bin/crsctl query crs releaseversion >> $PRECHKS/spool_crschk.log
echo "##### crsctl query crs activeversion#####" >> $PRECHKS/spool_crschk.log
$GRID_HOME/bin/crsctl query crs activeversion >> $PRECHKS/spool_crschk.log
echo "###### crs_stat -t #######" >> $PRECHKS/spool_crschk.log
$GRID_HOME/bin/crs_stat -t >> $PRECHKS/spool_crschk.log
echo "#### crs_stat -t | grep -i OFFLINE ###" >> $PRECHKS/spool_crschk.log
$GRID_HOME/bin/crs_stat -t | grep -i OFFLINE >> $PRECHKS/spool_crschk.log
echo "#### crs_stat -t | grep -i ONLINE ###" >> $PRECHKS/spool_crschk.log
$GRID_HOME/bin/crs_stat -t | grep -i ONLINE >> $PRECHKS/spool_crschk.log
echo "##### crsctl status resource -t ####" >> $PRECHKS/spool_crschk.log
$GRID_HOME/bin/crsctl status resource -t >> $PRECHKS/spool_crschk.log
echo "##### crsctl stat res -t ####" >> $PRECHKS/spool_crschk.log
$GRID_HOME/bin/crsctl stat res -t >> $PRECHKS/spool_crschk.log
echo " #### crsctl stat res -p ###### " >> $PRECHKS/spool_crschk.log
$GRID_HOME/bin/crsctl stat res -p >> $PRECHKS/spool_crschk.log
echo " #### crsctl get css diagwait ###### " >> $PRECHKS/spool_crschk.log
$GRID_HOME/bin/crsctl get css diagwait >> $PRECHKS/spool_crschk.log
echo " #### oifcfg iflist -p -n ###### " >> $PRECHKS/spool_crschk.log
$GRID_HOME/bin/oifcfg iflist -p -n >> $PRECHKS/spool_crschk.log
echo " #### oifcfg getif ###### " >> $PRECHKS/spool_crschk.log
$GRID_HOME/bin/oifcfg getif >> $PRECHKS/spool_crschk.log
echo " #### ocrcheck ###### " >> $PRECHKS/spool_crschk.log
$GRID_HOME/bin/ocrcheck >> $PRECHKS/spool_crschk.log
echo " #### olsnodes ###### " >> $PRECHKS/spool_crschk.log
$GRID_HOME/bin/olsnodes >> $PRECHKS/spool_crschk.log
echo "##### srvctl config database -d $DBNAME ###" >> $PRECHKS/spool_crschk.log
$GRID_HOME/bin/srvctl config database -d $DBNAME >> $PRECHKS/spool_crschk.log
echo "##### srvctl config scan #####" >> $PRECHKS/spool_crschk.log
$GRID_HOME/bin/srvctl config scan >> $PRECHKS/spool_crschk.log
echo "#### srvctl config listener ####" >> $PRECHKS/spool_crschk.log
$GRID_HOME/bin/srvctl config listener >> $PRECHKS/spool_crschk.log
echo "#### srvctl config scan_listener ####" >> $PRECHKS/spool_crschk.log
$GRID_HOME/bin/srvctl config scan_listener >> $PRECHKS/spool_crschk.log
echo "#### srvctl config service -d $DBNAME ####" >> $PRECHKS/spool_crschk.log
$GRID_HOME/bin/srvctl config service -d $DBNAME >> $PRECHKS/spool_crschk.log
echo "#### srvctl config nodeapps ####" >> $PRECHKS/spool_crschk.log
$GRID_HOME/bin/srvctl config nodeapps >> $PRECHKS/spool_crschk.log
echo "############################ crc check end #####"
echo "###### Checking PSU and CPU patches applied on oracle home ####" >> $PRECHKS/spool_opatch.log
echo "###### opatch lsinventory #######" >> $PRECHKS/spool_opatch.log
$ORACLE_HOME/OPatch/opatch lsinventory >> $PRECHKS/spool_opatch.log
echo "###### opatch lsinventory -bugs_fixed | grep -i psu #######" >> $PRECHKS/spool_opatch.log
$ORACLE_HOME/OPatch/opatch lsinventory -bugs_fixed | grep -i psu >> $PRECHKS/spool_opatch.log
echo "####### opatch lsinventory -bugs_fixed | grep -i cpu ######" >> $PRECHKS/spool_opatch.log
$ORACLE_HOME/OPatch/opatch lsinventory -bugs_fixed | grep -i cpu >> $PRECHKS/spool_opatch.log
echo "################### checking for ORA errors in alertlog $ORACLE_BASE/diag/rdbms/$DBNAME/$SID/trace/alert_$SID.log ####" >> $PRECHKS/spool_ORAerrors.log
tail -n 200000 $ORACLE_BASE/diag/rdbms/$DBNAME/$SID/trace/alert_$SID.log |grep -B 2 -A 3 "ORA-" >> $PRECHKS/spool_ORAerrors.log
echo "################### checking for ORA errors in alertlog /u01/app/oragrid/diag/asm/+asm/$ASM/trace/alert_$ASM.log ####" >> $PRECHKS/spool_ORAerrors.log
tail -n 20000 /u01/app/oragrid/diag/asm/+asm/$ASM/trace/alert_$ASM.log |grep -B 2 -A 3 "ORA-" >> $PRECHKS/spool_ORAerrors.log
echo "####### df -h /tmp ###### " >> $PRECHKS/spool_os_space.log
df -h /tmp >> $PRECHKS/spool_os_space.log
echo "####### df -h /u01#####" >> $PRECHKS/spool_os_space.log
df -h /u01 >> $PRECHKS/spool_os_space.log
echo "#### free -mto ####" >> $PRECHKS/spool_os_space.log
free -mto >> $PRECHKS/spool_os_space.log
echo "#### grep MemTotal /proc/meminfo ###" >> $PRECHKS/spool_os_space.log
grep MemTotal /proc/meminfo >> $PRECHKS/spool_os_space.log
echo "#### grep SwapTotal /proc/meminfo ####" >> $PRECHKS/spool_os_space.log
grep SwapTotal /proc/meminfo >> $PRECHKS/spool_os_space.log
echo "#### df -h /dev/shm ####" >> $PRECHKS/spool_os_space.log
df -h /dev/shm >> $PRECHKS/spool_os_space.log
echo "#### db processes ###" >> $PRECHKS/spool_os_space.log
ps -ef | egrep -i "crs|tns|emagent|cpmgr|pmon|fndlibr" | egrep -v "root|grep|ssh" >> $PRECHKS/spool_os_space.log
echo "#### all process ####" >> $PRECHKS/spool_os_space.log
ps -ef >> $PRECHKS/spool_os_space.log
echo "#### mount points #### " >> $PRECHKS/spool_os_space.log
df -h >> $PRECHKS/spool_os_space.log
ps -ef | egrep -i "crs|tns|emagent|cpmgr|pmon|fndlibr" | egrep -v "root|grep|ssh" > $PRECHKS/dbprocess.`date +%Y%m%d`.$$
ps -ef > $PRECHKS/allprocess.`date +%Y%m%d`.$$
df -h > $PRECHKS/mountpoints.`date +%Y%m%d`.$$
crontab -l > $PRECHKS/crontab.`date +%Y%m%d`.$$
cat /etc/hosts > $PRECHKS/hosts.`date +%Y%m%d`.$$
cat /etc/oratab > $PRECHKS/oratab.`date +%Y%m%d`.$$
echo "### /etc/init.d/oracleasm listdisks -d #### " >> $PRECHKS/spool_oracleasm.log
/etc/init.d/oracleasm listdisks -d >> $PRECHKS/spool_oracleasm.log
echo "##### kernal parameters ##### " >> $PRECHKS/spool_kernal_limits.log
cat /etc/sysctl.conf |grep 'sem\|shm\|file-max\|ip_local_port_range\|rmem_default\|rmem_max\|wmem_default\|wmem_max\|aio-max-nr' >> $PRECHKS/spool_kernal_limits.log
echo "##### security limits ##### " >> $PRECHKS/spool_kernal_limits.log
cat /etc/security/limits.conf |grep -v "#" >> $PRECHKS/spool_kernal_limits.log
echo "## scripts running on server `hostname` #### " >> $PRECHKS/spool_lsnrctl.log
echo "##### ps -ef|grep tns ####" >> $PRECHKS/spool_lsnrctl.log
ps -ef|grep tns >> $PRECHKS/spool_lsnrctl.log
for lsnr in `ps -ef|grep tns |grep product|cut -d" " -f23`
do
export TNS_ADMIN=$ORACLE_HOME/network/admin
echo "#### lsnrctl status $lsnr #### " >> $PRECHKS/spool_lsnrctl.log
$ORACLE_HOME/bin/lsnrctl status $lsnr >> $PRECHKS/spool_lsnrctl.log
echo "#### lsnrctl services $lsnr #### " >> $PRECHKS/spool_lsnrctl.log
$ORACLE_HOME/bin/lsnrctl services $lsnr >> $PRECHKS/spool_lsnrctl.log
done
for lsnr2 in `ps -ef|grep tns |grep oragrid|cut -d" " -f22`
do
export TNS_ADMIN=$GRID_HOME/network/admin
echo "#### lsnrctl status $lsnr2 #### " >> $PRECHKS/spool_lsnrctl.log
$GRID_HOME/bin/lsnrctl status $lsnr2 >> $PRECHKS/spool_lsnrctl.log
echo "#### lsnrctl services $lsnr2 #### " >> $PRECHKS/spool_lsnrctl.log
$GRID_HOME/bin/lsnrctl services $lsnr2 >> $PRECHKS/spool_lsnrctl.log
done
No comments:
Post a Comment