##############################################################################
##                                                                          ##
## Script to create db instances/alter existing DB instance Memory settings ##
##                                                                          ##
##############################################################################
#!/bin/sh
DBCA_LOG=db_create.log
INSTALL_DIR=/home/oracle/scripts/rac/createdb
ORACLE_HOME="/opt/app/oracle/product/11.2.0/db_1"
GRID_HOME="/opt/app/11.2.0/grid"
RAC_CONF_DIR="/home/oracle/scripts/rac/racconfig"
NODE1_HOSTNAME=`sh ${RAC_CONF_DIR}/get_var_value.sh "DB_NODE1"`
NODE2_HOSTNAME=`sh ${RAC_CONF_DIR}/get_var_value.sh "DB_NODE2"`
CLUSFILES_VOLUME_SIZE=`sh ${RAC_CONF_DIR}/get_var_value.sh "DB_CLUSFILES_VOLUME_SIZE_MB"`
CURRENTHOST='hostname'
DBCLUSTERNAME=`sh ${RAC_CONF_DIR}/get_var_value.sh "DB_CLUSTER_NAME"`
DB_NAME="ttv"
DBSIZE="48G"

if [ `whoami` != "root" ]; then
   echo "************************************************************************************"
   echo " Only root user can run this script. Please log in as a root user and try again...!" 
   echo "************************************************************************************"
   exit 1
fi
echo "*************************************************************************"
echo " Script started at $(date)"
echo ""
if [ "$DB_NAME" = "" ]; then
   echo "Please enter the database Name:"
   echo "(Suggestion: When creating a new instance, use six characters name"
   echo "with NO special characters, blank spaces and upper case letters)"
   read DB_NAME
fi
if [ "$DB_NAME" = "" ]; then
   echo "*****************************************************************"
   echo "DB_NAME is manditory to create the Database instance"
   echo "Please try again!"
   echo "*****************************************************************"
   exit 1
fi

if [ "$DBSIZE" = "" ]; then
   echo "Enter the memory size (Suggestion: 400M for DEV/ 8G for PROD):"
   read DBSIZE
fi
if [ "$DBSIZE" = "" ]; then
   echo "*****************************************************************"
   echo "DBSIZE is manditory to create the Database instance"
   echo "Please try again!"
   echo "*****************************************************************"
   exit 1
fi
DB_SIZE=$(echo $DBSIZE | tr '[:lower:]' '[:upper:]')

length=$(expr $(expr length $DB_SIZE) - 1 )
DB_SIZE_UNIT=${DB_SIZE:$length:1}
DB_SIZE_NUM=`expr substr $DB_SIZE 1 $length`

if [ $DB_SIZE_UNIT = "G" ]; then
   DB_SIZE_NUM=$(echo "scale=0;$DB_SIZE_NUM * 1024"|bc )
   for bash_rnum in $(printf %.0f $DB_SIZE_NUM); do
      DB_SIZE_NUM=$bash_rnum
   done
   DB_SIZE_UNIT="M"
   DB_SIZE=$DB_SIZE_NUM$DB_SIZE_UNIT
fi

PGASIZE=$(echo "scale=0;$DB_SIZE_NUM / 8"|bc )
for bash_rounded_number in $(printf %.0f $PGASIZE); do
  PGA_SIZE=$bash_rounded_number
done

SGASIZE=$(echo "scale=0;$DB_SIZE_NUM * 7/8"|bc )
for bash_rounded_number in $(printf %.0f $SGASIZE); do
  SGA_SIZE=$bash_rounded_number
done

TOTAL_MEMORY=$(echo "scale=0;$(cat /proc/meminfo | grep MemTotal | awk '{print $2}' ) /1024"|bc)
ORA_MEMORY=$(printf %.0f $(echo "scale=0;${TOTAL_MEMORY}*2/3"|bc))

echo "*************************************************************************"
echo "  Your DB_NAME is ${DB_NAME} and DB_SIZE is ${DBSIZE}" 
echo "*************************************************************************"

echo " ${DB_NAME} DB instance creation/ modification started for ${DBSIZE} foot print" > ${INSTALL_DIR}/${DBCA_LOG}
ORACLE_SID=$DB_NAME
grep -q ${ORACLE_SID} /etc/oratab
if [ $? -ne 0 ]; then
   echo "in the install loop.... "
   cp -f ${INSTALL_DIR}/ttv.dbt  ${ORACLE_HOME}/assistants/dbca/templates/ttv.dbt
   chown oracle:oinstall ${ORACLE_HOME}/assistants/dbca/templates/ttv.dbt

   if [ -f ${ORACLE_HOME}/assistants/dbca/templates/ttv.dbt ]; then
     echo "Creating new instance $DB_NAME will take several minutes.  Please wait.....!"
     echo " pga_size=${PGA_SIZE}   sga_size=${SGA_SIZE}"
     sed -e "s/NODE1/${NODE1_HOSTNAME}/g" -e "s/NODE2/${NODE2_HOSTNAME}/g" -e "s/DB_NAME/$DB_NAME/g" -e "s/PGA_SIZE/$PGA_SIZE/g" -e "s/SGA_SIZE/$SGA_SIZE/g" < ${INSTALL_DIR}/dbca_template.rsp > ${INSTALL_DIR}/dbca.rsp
     su - oracle -c "${ORACLE_HOME}/bin/dbca -silent -createDatabase -responseFile ${INSTALL_DIR}/dbca.rsp -continueOnNonFatalErrors true" 2>&1 | tee ${INSTALL_DIR}/${DBCA_LOG}
     #echo "${ORACLE_SID}:${ORACLE_HOME}:N" >> /etc/oratab
     cp /etc/oratab ${INSTALL_DIR}/oratab_old
     ORATAB_ENTRY=`sed -e "s/:N/:Y/" < /etc/oratab | grep $DB_NAME`
     sed "s/^.*$DB_NAME.*//" < /etc/oratab > ${INSTALL_DIR}/oratab_local
     echo "${ORATAB_ENTRY}" >> ${INSTALL_DIR}/oratab_local
     sed '/^$/d' ${INSTALL_DIR}/oratab_local > /etc/oratab

     check_db_stat=`ps -ef|grep ${ORACLE_SID}|grep pmon|wc -l`;
     instance_found=`expr $check_db_stat`
     if [ $instance_found -gt 0 ];
     then
        echo "Changing user profile........."
        su - oracle -c "sh ${INSTALL_DIR}/chg_prof.sh ${ORACLE_SID}" 2>&1 | tee -a ${INSTALL_DIR}/${DBCA_LOG} 
        echo ""
        echo "Setting up Oracle Enterprise manager, pelase wait......."
        su - oracle -c "${ORACLE_HOME}/bin/emca -config dbcontrol db -silent -cluster -ASM_USER_ROLE SYSDBA -ASM_USER_NAME ASMSNMP -ASM_USER_PWD n2bbsys -SYS_PWD n2bbsys -CLUSTER_NAME ${DBCLUSTERNAME} -LOG_FILE ${INSTALL_DIR}/emConfig.log -SID ttv -ASM_SID +ASM1 -DB_UNIQUE_NAME ttv -EM_HOME ${ORACLE_HOME}  -SERVICE_NAME ttv -ASM_PORT 1521 -PORT 1521 -LISTENER_OH ${GRID_HOME} -LISTENER LISTENER -ORACLE_HOME ${ORACLE_HOME} -HOST ${CURRENTHOST} -ASM_OH ${GRID_HOME} -DBSNMP_PWD n2bbsys -SYSMAN_PWD n2bbsys"

        if [ "${CURRENTHOST}"="${NODE1_HOSTNAME}" ]; then
           OTHERHOST=${NODE2_HOSTNAME}
        elif [ "${CURRENTHOST}"="${NODE2_HOSTNAME}" ]; then
           OTHERHOST=${NODE1_HOSTNAME}
        fi

        su - oracle -c "${INSTALL_DIR}/set_sid.sh"
        ssh "oracle@${OTHERHOST}" "${INSTALL_DIR}/set_sid.sh"  

        /opt/app/11.2.0/grid/bin/acfsload start
        /bin/mkdir -p /clusfiles
        /bin/chmod 777 /clusfiles
        ssh "root@${OTHERHOST}" "/opt/app/11.2.0/grid/bin/acfsload start;mkdir -p /clusfiles;chmod 777 /clusfiles"

        su - oracle -c "export ORACLE_SID='+ASM1';export ORACLE_HOME=/opt/app/11.2.0/grid;/opt/app/11.2.0/grid/bin/asmcmd volcreate -G CLUSFILES -s ${CLUSFILES_VOLUME_SIZE}M clusfiles"
        CLUSFILES_VOL=$(su - oracle -c "export ORACLE_SID='+ASM1';export ORACLE_HOME=/opt/app/11.2.0/grid;/opt/app/11.2.0/grid/bin/asmcmd volinfo -G CLUSFILES clusfiles| grep 'Volume Device'"| awk '{print($3)}')
        #echo "CLUSFILES_VOL=${CLUSFILES_VOL}"
        if [ ! "${CLUSFILES_VOL}" = ""  ]; then
           echo -e "Adding cluster files acfs volumne ${CLUSFILES_VOL} ....\n"
           /sbin/mkfs -t acfs -b 4k "${CLUSFILES_VOL}"
           /sbin/acfsutil registry -f -a "${CLUSFILES_VOL}" "/clusfiles"
           /bin/mount -t acfs "${CLUSFILES_VOL}" "/clusfiles"
           ssh "root@${OTHERHOST}" "/bin/mount -t acfs '${CLUSFILES_VOL}' '/clusfiles'"
           /opt/app/11.2.0/grid/bin/acfsroot enable
           ssh "root@${OTHERHOST}" "/opt/app/11.2.0/grid/bin/acfsroot enable"
           /bin/mkdir -p /clusfiles/epgpublish
           /bin/chmod 755 /clusfiles/epgpublish
           /bin/chown oracle:oinstall /clusfiles/epgpublish
           /bin/mkdir -p /clusfiles/backup
           /bin/chmod 755 /clusfiles/backup
           /bin/chown oracle:oinstall /clusfiles/backup
           /bin/mkdir -p /clusfiles/DWT1/log
           /bin/chmod -R 755 /clusfiles/DWT1
           /bin/chown -R oracle:oinstall /clusfiles/DWT1
        fi

        /bin/mkdir -p /cmsappmq
        /bin/chmod 777 /cmsappmq
        ssh "root@${OTHERHOST}" "/bin/mkdir -p /cmsappmq;/bin/chmod 777 /cmsappmq"
        #NFS volume for CMS App
        su - oracle -c "export ORACLE_SID='+ASM1';export ORACLE_HOME=/opt/app/11.2.0/grid;/opt/app/11.2.0/grid/bin/asmcmd volcreate -G CLUSFILES -s 100G cmsappmq"
        CLUSFILES_VOL=$(su - oracle -c "export ORACLE_SID='+ASM1';export ORACLE_HOME=/opt/app/11.2.0/grid;/opt/app/11.2.0/grid/bin/asmcmd volinfo -G CLUSFILES cmsappmq| grep 'Volume Device'"| awk '{print($3)}')
        #echo "CLUSFILES_VOL=${CLUSFILES_VOL}"
        if [ ! "${CLUSFILES_VOL}" = ""  ]; then
           echo -e "Adding cluster files acfs volumne ${CLUSFILES_VOL} ....\n"
           /sbin/mkfs -t acfs -b 4k "${CLUSFILES_VOL}"
           /sbin/acfsutil registry -f -a "${CLUSFILES_VOL}" "/cmsappmq"
           /bin/mount -t acfs "${CLUSFILES_VOL}" "/cmsappmq"
           ssh "root@${OTHERHOST}" "/bin/mount -t acfs '${CLUSFILES_VOL}' '/cmsappmq'"
           ${GRID_HOME}/bin/crsctl delete resource nfsservice > /dev/null 2>&1
           ${GRID_HOME}/bin/crsctl delete type nfstype > /dev/null 2>&1
           ${GRID_HOME}/bin/appvipcfg delete -vipname=cmsappmqvip > /dev/null 2>&1
           ${GRID_HOME}/bin/crsctl add type nfstype -basetype cluster_resource -attr "ATTRIBUTE=ACTION_SCRIPT,TYPE=string,DEFAULT_VALUE=/home/oracle/cmsappnfs/cmsappnfs"
           ${GRID_HOME}/bin/crsctl add resource nfsservice -type nfstype -attr "AUTO_START=always,CARDINALITY=2,DEGREE=1,CHECK_INTERVAL=3600,DESCRIPTION='NFS Service over ACFS volumes',START_TIMEOUT=120,RESTART_ATTEMPTS=1,HOSTING_MEMBERS='$NODE1_HOSTNAME,$NODE2_HOSTNAME',START_DEPENDENCIES=hard(ora.registry.acfs)pullup(ora.registry.acfs),STOP_DEPENDENCIES=hard(ora.registry.acfs)"
           #Enable NFS sharing for App server of /cmsappmq folder
           echo "/cmsappmq *(rw,fsid=1802)" > /etc/exports
           ${GRID_HOME}/bin/crsctl stop resource nfsservice
           ${GRID_HOME}/bin/crsctl start resource nfsservice
           /bin/chmod 777 /cmsappmq
           ssh "root@${OTHERHOST}" "echo '/cmsappmq *(rw,fsid=1802)' > /etc/exports;${GRID_HOME}/bin/crsctl stop resource nfsservice > /dev/null 2>&1;${GRID_HOME}/bin/crsctl start resource nfsservice;/bin/chmod 777 /cmsappmq"
           /bin/mkdir -p /cmsappmq/stats /cmsappmq/history /cmsappmq/mq
           /bin/chmod -R 777 /cmsappmq
           /bin/chown -R nobody.nobody /cmsappmq
        fi

        echo -e "\nDatabase Backup setup is in progress, please wait...!\n"
        ORA_SID=`ps -ef | grep ora_pmon | grep -v "grep ora_pmon" | awk '{print($8)}' | sed 's/ora\_pmon\_//g'`
        mkdir -p /opt/app/oracle/backup/scripts
        cp -f ${INSTALL_DIR}/rman_backup.sh /opt/app/oracle/backup/scripts/rman_backup.sh
        cp -f ${INSTALL_DIR}/rman_backup_arch.sh /opt/app/oracle/backup/scripts/rman_backup_arch.sh
        chown -R oracle:oinstall /opt/app/oracle/backup
        chmod 755 /opt/app/oracle/backup/scripts/rman_backup*.sh

        ssh "oracle@${OTHERHOST}" "mkdir -p /opt/app/oracle/backup/scripts"
        ssh "oracle@${OTHERHOST}" "cp -f ${INSTALL_DIR}/rman_backup.sh /opt/app/oracle/backup/scripts/rman_backup.sh"
        ssh "oracle@${OTHERHOST}" "cp -f ${INSTALL_DIR}/rman_backup_arch.sh /opt/app/oracle/backup/scripts/rman_backup_arch.sh"
        ssh "oracle@${OTHERHOST}" "chown -R oracle:oinstall /opt/app/oracle/backup;chmod 744 /opt/app/oracle/backup/scripts/rman_backup*.sh"

        echo -e "\nChanging database to archive mode...\n"
        su - oracle -c "export ORACLE_SID=${ORA_SID};${ORACLE_HOME}/bin/sqlplus -s /nolog<<SQL1
           connect / as sysdba
           alter system set db_recovery_file_dest_size = '1024G' scope=both sid='*';
           alter system set db_recovery_file_dest = '+FRA' scope=both sid='*';
           alter system set db_flashback_retention_target = 4320 scope=both sid='*';
SQL1"
        su - oracle -c "srvctl stop database -d ttv"
        su - oracle -c "export ORACLE_SID=${ORA_SID};${ORACLE_HOME}/bin/sqlplus -s /nolog<<EOF
           connect / as sysdba
           startup mount
           alter database archivelog;
           alter database open;
           shut immediate
EOF"
        echo -e "\nStarting the database, please wait..."
        su - oracle -c "srvctl start database -d ttv"
        echo -e "\nSetting up database backup jobs on the database...\n"
        su - oracle -c "export ORACLE_SID=${ORA_SID};${ORACLE_HOME}/bin/sqlplus -s /nolog<<EOF
           connect / as sysdba
           begin
             dbms_scheduler.create_program(
                     program_name =>   'sys.rman_full_backup_prog',
                     program_type =>   'executable',
                     program_action => '/opt/app/oracle/backup/scripts/rman_backup.sh',
                     enabled =>        TRUE);
             dbms_scheduler.create_job(
                     job_name =>        'sys.rman_full_backup_job',
                     program_name =>    'sys.rman_full_backup_prog',
                     repeat_interval => 'FREQ=WEEKLY; BYDAY=SUN; BYHOUR=2');
             dbms_scheduler.enable('sys.rman_full_backup_job');
             dbms_scheduler.set_attribute('sys.rman_full_backup_job','logging_level',DBMS_SCHEDULER.LOGGING_FULL);
           end;
           /
           begin
             dbms_scheduler.create_program(
                     program_name =>   'sys.rman_arch_backup_prog',
                     program_type =>   'executable',
                     program_action => '/opt/app/oracle/backup/scripts/rman_backup_arch.sh',
                     enabled =>        TRUE);
             dbms_scheduler.create_job(
                     job_name =>        'sys.rman_arch_backup_job',
                     program_name =>    'sys.rman_arch_backup_prog',
                     repeat_interval => 'FREQ=DAILY; BYHOUR=0,6,12,18');
             dbms_scheduler.enable('sys.rman_arch_backup_job');
             dbms_scheduler.set_attribute('sys.rman_arch_backup_job','logging_level',DBMS_SCHEDULER.LOGGING_FULL);
           end;
           /
EOF"
        echo -e "\nDatabase Backup set-up is completed successfully.\n"
     fi

     echo "*************************************************************************"
     if  grep -q "ORA-\|SP2-\|Permission denied\|SQL-" "${INSTALL_DIR}/${DBCA_LOG}"
     then
         echo "Database instance ${DB_NAME} creation got Failed!"
         echo "  Please check ${INSTALL_DIR}/${DBCA_LOG} file for errors.....!"
         echo "*************************************************************************"
         exit 1
     elif grep -q "100\% complete" "${INSTALL_DIR}/${DBCA_LOG}" 
     then
         echo "  Script completed at $(date)"
         echo "  Database instance ${DB_NAME} created SUCCESSFULLY..."
         echo "*************************************************************************"
         exit 0
     else
         echo "Database instance ${DB_NAME} creation got Failed!"
         echo "  Please check ${INSTALL_DIR}/${DBCA_LOG} file for errors.....!"
         echo "*************************************************************************"
         exit 1 
     fi
   else
     echo "Database template file is missing. Please contact DBA for further assistance ....!" 
     exit 1
   fi
else
echo "${ORACLE_SID} database instance is already exists."
exit 1
   check_stat=`ps -ef|grep ${ORACLE_SID}|grep pmon|wc -l`;
   oracle_num=`expr $check_stat`
   if [ $oracle_num -lt 1 ];
   then
      sh ${INSTALL_DIR}/init_ora_build.sh ${DB_NAME} ${PGA_SIZE} ${SGA_SIZE} ${INSTALL_DIR} 2>&1 | tee ${INSTALL_DIR}/${DBCA_LOG}
      #echo "Parameter file created successfully and database will be started using this pfile, please WAIT..."
      su - oracle -c "sh ${INSTALL_DIR}/db_strt_use_pfile.sh ${DB_NAME}" 2>&1 | tee ${INSTALL_DIR}/${DBCA_LOG}
   else
     
      DB_ROLE=$(su - oracle -c "sqlplus -s /nolog <<EOF2
          conn / as sysdba
          set pages 0
          set feedback off
          select trim(database_role) from v\\\$database;
          exit
EOF2")
      if [ -n "${DB_ROLE}" ]
      then
         case ${DB_ROLE} in
            "PHYSICAL STANDBY" )
               DB_OPEN_MODE="mount"
         ;;
         * )
              DB_OPEN_MODE=""
         esac
      fi

      #echo "$ORACLE_SID Database instance memory variables changing is in progress...."
      su - oracle -c "sh ${INSTALL_DIR}/db_alt_mem.sh ${DB_NAME} ${PGA_SIZE} ${SGA_SIZE} ${DB_OPEN_MODE}" 2>&1 | tee ${INSTALL_DIR}/${DBCA_LOG}	
      if [ "${DB_OPEN_MODE}" = "" ] 
      then
         su - oracle -c "sh ${INSTALL_DIR}/chg_prof.sh ${ORACLE_SID}" 2>&1 | tee -a ${INSTALL_DIR}/${DBCA_LOG} 
      fi
   fi
   sed -i "/ORA-01109[.]*/d" ${INSTALL_DIR}/${DBCA_LOG}
   echo "*************************************************************************"
   if  grep -q "ORA-\|SP2-\|Permission denied\|SQL-" "${INSTALL_DIR}/${DBCA_LOG}" 
   then
       echo "Database ${DB_NAME} instance memory changing got Failed!"
       echo "Please check ${INSTALL_DIR}/${DBCA_LOG} file for any errors"
       echo "*************************************************************************"
       exit 1
    else
       echo "Script completed at $(date)"
       echo "Database ${DB_NAME} instance memory modification completed SUCCESSFULLY..."
       echo "*************************************************************************"
       exit 0
   fi
fi
