#!/bin/sh
#******************************************************************************************#
#      Name of the Script : cms_direct_upgrade.sh                                          #
#      Purpose            : This script restores the data from 1.2/2.0 version of CMS      #
#                           and upgrade the database to latest CMS vesion.                 #
#      Created by         : Suresh Neravati                                                #
#      Created on         : 03/28/2013                                                     #
#                                                                                          #
#******************************************************************************************#
ORASCRIPTS_DIR="/home/oracle/scripts"
UPGRADE_DIR="${ORASCRIPTS_DIR}/cms_direct_upgrade"
DBRESTORE_DIR="${ORASCRIPTS_DIR}/db_restore"
INSTALLCMS_DIR="${ORASCRIPTS_DIR}/install/installcms"
INSTALLRCS_DIR="${ORASCRIPTS_DIR}/install/installrcs"
INSTALLEPG_DIR="${ORASCRIPTS_DIR}/install/installepgmgr"
INSTALLDW_DIR="${ORASCRIPTS_DIR}/install/installdw"
INSTALL_DIR="/home/upgrade/db_upgrade"
ORA_SID="ttv"
CMS_LATEST_DB_VERSION="$1"
RUN_MODE="$2"
function drawline
{
   echo -e "********************************************************************************\n"
}
function db_restore 
{
if [ -d "/opt/oracle/backup/ttv/full" ]; 
then
   chown -R oracle:oinstall /opt/oracle/backup/ttv/full/backup*
   RMAN_DIR="/opt/oracle/backup/ttv/full/backup_"`ls -ldt /opt/oracle/backup/ttv/full/backup* | awk -F / '{print }' | cut -d'_' -f2 | head -1`
   if [ "${RMAN_DIR}" = "" ]; 
   then
      echo "RMan Backup not found to restore the database, pelase verify "
      echo "the folder /opt/oracle/backup/ttv/full"
      drawline
      exit 1
   else
      echo -e "\nRMan Backup is restoring from ${RMAN_DIR} \n"
      if [ "X${RUN_MODE}" = "XS" ];
      then
         echo "Running with silent option"
      else
         echo -n "You want to continue (Y/N): "
         read WANT_TO_CONTINUE
         case "${WANT_TO_CONTINUE}" in
	   Y | y ) echo -e "\n"
                   ;;
	   N | n ) echo -e "\nExiting .......\n"
                   exit 1
                   ;;
	   * ) echo -e "\nPlease try again....\n"
                   exit 1
         esac
      fi
   fi
else
   echo -e "\nDatabase RMan Backup not copied on to this server, please verify..!\n" 
   exit 1
fi

cd ${DBRESTORE_DIR}
${DBRESTORE_DIR}/db_restore.sh 

DB_STATUS=$(sqlplus -S /nolog<<PQR
  connect / as sysdba
  set pages 0
  set feedback off
  select trim(STATUS) from v\$instance;
  exit
PQR
)
if [ "${DB_STATUS}" = "OPEN" ]; then
   echo -e "Database restore completed successfully....\n"
else
   echo -e "\nDatabase restore failed, Please check the log file"
   echo " ${DBRESTORE_DIR}/upgrade.log for any errors."
   drawline 
   exit 1
fi
}
function db_inst_verify
{
if  grep -q "ORA-\|SP2-\|Permission denied\|SQL-" "$2"
then
   echo -e "\n$1 DB install/upgrade script probably failed! "
   echo "    Check $2 file for any errors."
   drawline
   exit 1
fi
}
function createoradir
{
   if [ "$1" = "" ]; then
      echo -e "Can not create a directory with out a name.\n"
   else
      #echo -e "Creating a directory $1...\n"
      mkdir -p "$1"
      chown -R oracle:oinstall "$1"
   fi
}
function oracle_restart
{
sqlplus -S /nolog<<AAA
  connect / as sysdba
  set serveroutput on
  set echo off
  set head on
  spool ${INSTALLCMS_DIR}/recompile_invalid_objects.log
  @${ORACLE_HOME}/rdbms/admin/utlprp.sql 1 
  spool off
  shutdown immediate
  startup
  exit
AAA
}
function cms_version_before_upg
{
   PREVER="$1"
sqlplus -S /nolog<<ABC
  connect / as sysdba
  set pages 0
  set feedback off
MERGE INTO wfs.system_settings ss
   USING (SELECT 'CMS_VERSION_BEFORE_UPG' as setting_type,'${PREVER}' as setting_value, 
                 systimestamp as createdate FROM dual) d ON (d.setting_type = ss.setting_type)
   WHEN MATCHED THEN UPDATE SET ss.setting_value = d.setting_value
   WHEN NOT MATCHED THEN INSERT (ss.setting_type, ss.setting_value) values ('CMS_VERSION_BEFORE_UPG','${PREVER}');
  exit
ABC
}
function db_upgrade
{
DB_VERSION=$(sqlplus -S /nolog<<SQL1
  connect / as sysdba
  set pages 0
  set feedback off
  ALTER TABLESPACE WFS_DATA ADD DATAFILE size 1G AUTOEXTEND ON NEXT 200M MAXSIZE unlimited;
  ALTER TABLESPACE WFS_DATA ADD DATAFILE size 1G AUTOEXTEND ON NEXT 200M MAXSIZE unlimited;
  ALTER TABLESPACE UNDOTBS1 ADD DATAFILE size 100m AUTOEXTEND ON NEXT 100m MAXSIZE unlimited;
  ALTER TABLESPACE UNDOTBS1 ADD DATAFILE size 100m AUTOEXTEND ON NEXT 100m MAXSIZE unlimited;
  SELECT setting_value FROM WFS.SYSTEM_SETTINGS WHERE setting_type = 'RELEASE';
  exit
SQL1
)
case "${DB_VERSION}" in 
   "3.0" | "3.2.4" ) 
       echo
       echo -e "Upgrading CMS 1.2 or 1.5 database to CMS 3.1, please wait...!\n"
       cd ${INSTALLCMS_DIR}
       ${INSTALLCMS_DIR}/installCMS.sh 
       db_inst_verify "CMS" "${INSTALLCMS_DIR}/cmsdbinstall.log"
       cd ${UPGRADE_DIR}
       ${UPGRADE_DIR}/increment_db_version.sh >> ${UPGRADE_DIR}/installcms.log  
       db_inst_verify "CMS" "${UPGRADE_DIR}/installcms.log"
       cd ${INSTALLCMS_DIR} 
       ${INSTALLCMS_DIR}/installCMS.sh 
       db_inst_verify "CMS" "${INSTALLCMS_DIR}/cmsdbinstall.log"
       cd ${INSTALLRCS_DIR}
       ${INSTALLRCS_DIR}/installRCS.sh
       db_inst_verify "RCS" "${INSTALLRCS_DIR}/rcsdbinstall.log"
       cd ${INSTALLEPG_DIR}
       ${INSTALLEPG_DIR}/installepgmgr.sh
       db_inst_verify "EPG Manager" "${INSTALLEPG_DIR}/epgmanager.log"
       cd ${INSTALLDW_DIR}
       ${INSTALLDW_DIR}/installDW.sh
       db_inst_verify "CMS DW" "${INSTALLDW_DIR}/cmsdwinstall.log"
       if [ "${DB_VERSION}" = "3.0" ]; then
          cms_version_before_upg "1.2.030.14"
       elif [ "${DB_VERSION}" = "3.2.4" ]; then
          cms_version_before_upg "1.5.0.26"
       fi
       ;;
   "3.2.17"  ) 
       echo
       echo -e "Upgrading CMS 2.0 database to CMS 3.1, please wait...!\n"
       cd ${INSTALLCMS_DIR}
       sh ${INSTALLCMS_DIR}/copy_all_xml.sh ttv
       if  grep -q "ORA-\|SP2-\|Permission denied\|SQL-" "/home/oracle/scripts/copy_all_xml.log"
       then
        echo "Copy all xml script got failed! Check /home/oracle/scripts/copy_all_xml.log file for any errors."
        echo -e "**************************************************************************************************\n"
        exit 1
       else
        echo "COPY ALL XML SCRIPT SUCCEEDED!"
        sleep 15
       fi
       cd ${UPGRADE_DIR}
       ${UPGRADE_DIR}/increment_db_version.sh >> ${UPGRADE_DIR}/installcms.log
       db_inst_verify "CMS" "${UPGRADE_DIR}/installcms.log"
       cd ${INSTALLCMS_DIR}
       ${INSTALLCMS_DIR}/installCMS.sh  
       db_inst_verify "CMS" "${INSTALLCMS_DIR}/cmsdbinstall.log"
       sleep 15
       cd ${INSTALLRCS_DIR}
       ${INSTALLRCS_DIR}/installRCS.sh
       db_inst_verify "RCS" "${INSTALLRCS_DIR}/rcsdbinstall.log"
       sleep 15
       cd ${INSTALLEPG_DIR}
       ${INSTALLEPG_DIR}/installepgmgr.sh
       db_inst_verify "EPG Manager" "${INSTALLEPG_DIR}/epgmanager.log"
       sleep 15
       cd ${INSTALLDW_DIR}
       ${INSTALLDW_DIR}/installDW.sh
       db_inst_verify "CMS DW" "${INSTALLDW_DIR}/cmsdwinstall.log"
       sleep 15
       if [ "${DB_VERSION}" = "3.2.17" ]; then
          cms_version_before_upg "2.0.040.26"
       fi
       ;;
   #"3.2.22"  ) 
   #    echo
   #    echo -e "Upgrading CMS 2.5 database to CMS 3.1, please wait...!\n"
   #    ;;
   #"3.3.20"  ) 
   #    echo
   #    echo "Upgrading CMS 3.0 database to CMS 3.1, please wait...!"
   #    ;;
   * ) 
       echo
       echo -e "Check your database....\n" 
       exit 1
esac
}
function new_db_vesion
{
NEW_DB_VERSION=$(sqlplus -S /nolog<<SQL1
  connect / as sysdba
  set pages 0
  set feedback off
  SELECT setting_value FROM WFS.SYSTEM_SETTINGS WHERE setting_type = 'RELEASE';
  exit
SQL1
)

if [ "${NEW_DB_VERSION}" = "${CMS_LATEST_DB_VERSION}" ]; then 
   oracle_restart
   echo -e "\nCMS Database has been upgraded to 3.1 successfully.\n"
else
   echo -e "\nCMS database upgrade not completed, contact DBA for futher assistance.\n"
fi
}
#echo $ORACLE_HOME
DB_ROLE=$(sqlplus -S /nolog<<PQR
  connect / as sysdba
  set pages 0
  set feedback off
  select trim(database_role) from v\$database;
  exit
PQR
)
#echo "DB_ROLE=${DB_ROLE}"
if [ "${DB_ROLE}" = "PRIMARY" ]; then
   drawline
   echo -e "Script started at $(date)"
   db_restore
   db_upgrade
   new_db_vesion 
   echo -e "Script ended at $(date)"
   drawline
else
   echo -e "\nThis is not a Primary database, this script can run only on primary database."
   drawline
fi

exit 0
