su - oragrid
$more listener.ora
# listener.ora Network Configuration File: /u01/app/11.2.0.3/grid/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = testdbt1.sun.com)
(ORACLE_HOME = /u01/app/oradb/product/11.2.0.3/dbhome_1)
(SID_NAME = testdbt1)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = test.sun.com)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /u01/app/oragrid
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent
$
su - oradb
$more tnsnames.ora
TESTDBT1.SUN.COM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = testdbt1.sun.com)
)
)
$
# Rman CloneDB_asm:
#00 22 * * * ${HOME}/bin/pkg/clonedb_asm/_run.sh testdbt1 > /dev/null 2>> /tmp/crontab_oracle.err
$more _run.sh
#!/bin/bash
#
# Package Name:
#
tmp_string="`dirname $0`"
pkg_name="`basename ${tmp_string}`"
if [ "${pkg_name}" = "." ]; then
tmp_string="`pwd`"
pkg_name="`basename ${tmp_string}`"
fi
# Check Parameters:
if [ $1 ]
then
oracle_sid=`echo ${1} | tr "[A-Z]" "[a-z]"`
else
echo "`date +%Y.%m.%d" "%H:%M:%S` - ${pkg_name}: Error! (Parameter=1). Call `basename $0`" >&2
exit 2
fi
#
# Setup Environment:
#
pkg_env="${HOME}/bin/_conf/.env"
if [ ! -r "${pkg_env}" ]; then
echo "`date +%Y.%m.%d" "%H:%M:%S` - ${pkg_name}: ERROR!!! Execution Aborted. Not Found Environment file: ${pkg_env}" >&2
exit 2
fi
if [ -s "${pkg_env}" ]; then
. ${pkg_env}
else
echo "`date +%Y.%m.%d" "%H:%M:%S` - ${pkg_name}: ERROR!!! Execution Aborted. Environment file is empty: ${pkg_env}" >&2
exit 2
fi
#
# Check Environment:
#
# Check ORA_USER:
if [ -z "${ORA_USER}" ]; then
echo "`date +%Y.%m.%d" "%H:%M:%S` - ${pkg_name}: ERROR!!! Execution Aborted. Environment variable ORA_USER not defined" >&2
exit 2
else
ora_user=${ORA_USER}
fi
# Check ORACLE_BASE:
if [ -z "${ORACLE_BASE}" ]; then
echo "`date +%Y.%m.%d" "%H:%M:%S` - ${pkg_name}: ERROR!!! Execution Aborted. Environment variable ORACLE_BASE not defined" >&2
exit 2
elif [ ! -d "${ORACLE_BASE}" ]; then
echo "`date +%Y.%m.%d" "%H:%M:%S` - ${pkg_name}: ERROR!!! Execution Aborted. Directory ORACLE_BASE not found (${ORACLE_BASE})" >&2
exit 2
fi
# Check ORACLE_HOME:
if [ -z "${ORACLE_HOME}" ]; then
echo "`date +%Y.%m.%d" "%H:%M:%S` - ${pkg_name}: ERROR!!! Execution Aborted. Environment variable ORACLE_HOME not defined" >&2
exit 2
elif [ ! -d "${ORACLE_HOME}" ]; then
echo "`date +%Y.%m.%d" "%H:%M:%S` - ${pkg_name}: ERROR!!! Execution Aborted. Directory ORACLE_HOME not found (${ORACLE_HOME})" >&2
exit 2
fi
# Check TMP_DIR:
if [ -z "${TMP_DIR}" ]; then
echo "`date +%Y.%m.%d" "%H:%M:%S` - ${pkg_name}: ERROR!!! Execution Aborted. Environment variable TMP_DIR not defined" >&2
exit 2
elif [ ! -d "${TMP_DIR}" ]; then
echo "`date +%Y.%m.%d" "%H:%M:%S` - ${pkg_name}: ERROR!!! Execution Aborted. Directory TMP_DIR not found (${TMP_DIR})" >&2
exit 2
elif [ ! -w "${TMP_DIR}" ]; then
echo "`date +%Y.%m.%d" "%H:%M:%S` - ${pkg_name}: ERROR!!! Execution Aborted. Directory TMP_DIR not writeable (${TMP_DIR})" >&2
exit 2
fi
# Check BIN_DIR:
if [ -z "${BIN_DIR}" ]; then
echo "`date +%Y.%m.%d" "%H:%M:%S` - ${pkg_name}: ERROR!!! Execution Aborted. Environment variable BIN_DIR not defined" >&2
exit 2
elif [ ! -d "${BIN_DIR}" ]; then
echo "`date +%Y.%m.%d" "%H:%M:%S` - ${pkg_name}: ERROR!!! Execution Aborted. Directory BIN_DIR not found (${BIN_DIR})" >&2
exit 2
fi
# Check COMPANY_NAME:
if [ -z "${COMPANY_NAME}" ]; then
echo "`date +%Y.%m.%d" "%H:%M:%S` - ${pkg_name}: ERROR!!! Execution Aborted. Environment variable COMPANY_NAME not defined" >&2
exit 2
fi
# Check HOST_NAME:
if [ -z "${HOST_NAME}" ]; then
echo "`date +%Y.%m.%d" "%H:%M:%S` - ${pkg_name}: ERROR!!! Execution Aborted. Environment variable HOST_NAME not defined" >&2
exit 2
fi
# Check MAIL_RECIPIENTS:
if [ -z "${MAIL_DBA}" ]; then
echo "`date +%Y.%m.%d" "%H:%M:%S` - ${pkg_name}: ERROR!!! Execution Aborted. Environment variable MAIL_RECIPIENTS not defined" >&2
exit 2
fi
# Check UTL_SEND:
if [ -z "${UTL_SEND}" ]; then
echo "`date +%Y.%m.%d" "%H:%M:%S` - ${pkg_name}: ERROR!!! Execution Aborted. Environment variable UTL_SEND not defined" >&2
exit 2
elif [ ! -f "${UTL_SEND}" ]; then
echo "`date +%Y.%m.%d" "%H:%M:%S` - ${pkg_name}: ERROR!!! Execution Aborted. File UTL_SEND not found (${UTL_SEND})" >&2
exit 2
elif [ ! -x "${UTL_SEND}" ]; then
echo "`date +%Y.%m.%d" "%H:%M:%S` - ${pkg_name}: ERROR!!! Execution Aborted. File UTL_SEND not executable (${UTL_SEND})" >&2
exit 2
fi
# Check UTL_SQLPLUS:
if [ -z "${UTL_SQLPLUS}" ]; then
echo "`date +%Y.%m.%d" "%H:%M:%S` - ${pkg_name}: ERROR!!! Execution Aborted. Environment variable UTL_SQLPLUS not defined" >&2
exit 2
elif [ ! -f "${UTL_SQLPLUS}" ]; then
echo "`date +%Y.%m.%d" "%H:%M:%S` - ${pkg_name}: ERROR!!! Execution Aborted. File UTL_SQLPLUS not found (${UTL_SQLPLUS})" >&2
exit 2
elif [ ! -x "${UTL_SQLPLUS}" ]; then
echo "`date +%Y.%m.%d" "%H:%M:%S` - ${pkg_name}: ERROR!!! Execution Aborted. File UTL_SQLPLUS not executable (${UTL_SQLPLUS})" >&2
exit 2
fi
# Check UTL_ORAPWD:
if [ -z "${UTL_ORAPWD}" ]; then
echo "`date +%Y.%m.%d" "%H:%M:%S` - ${pkg_name}: ERROR!!! Execution Aborted. Environment variable UTL_ORAPWD not defined" >&2
exit 2
elif [ ! -f "${UTL_ORAPWD}" ]; then
echo "`date +%Y.%m.%d" "%H:%M:%S` - ${pkg_name}: ERROR!!! Execution Aborted. File UTL_ORAPWD not found (${UTL_ORAPWD})" >&2
exit 2
elif [ ! -x "${UTL_ORAPWD}" ]; then
echo "`date +%Y.%m.%d" "%H:%M:%S` - ${pkg_name}: ERROR!!! Execution Aborted. File UTL_ORAPWD not executable (${UTL_ORAPWD})" >&2
exit 2
fi
# Check UTL_RMAN:
if [ -z "${UTL_RMAN}" ]; then
echo "`date +%Y.%m.%d" "%H:%M:%S` - ${pkg_name}: ERROR!!! Execution Aborted. Environment variable UTL_RMAN not defined" >&2
exit 2
elif [ ! -f "${UTL_RMAN}" ]; then
echo "`date +%Y.%m.%d" "%H:%M:%S` - ${pkg_name}: ERROR!!! Execution Aborted. File UTL_RMAN not found (${UTL_RMAN})" >&2
exit 2
elif [ ! -x "${UTL_RMAN}" ]; then
echo "`date +%Y.%m.%d" "%H:%M:%S` - ${pkg_name}: ERROR!!! Execution Aborted. File UTL_RMAN not executable (${UTL_RMAN})" >&2
exit 2
fi
# Initialize Package:
pkg_flag="`basename $0 .sh`"
run_in_progress=${TMP_DIR}/${pkg_name}_${oracle_sid}.lock
log_file=${TMP_DIR}/${pkg_name}_${oracle_sid}.log
prefix="${COMPANY_NAME}#${HOST_NAME}(${oracle_sid}) ${pkg_name}"
mess_theme="${prefix}: Error! Another process running (${run_in_progress})"
mail_recipients="$MAIL_DBA"
echo $mail_recipients
# Check Another process for running:
if [ -f ${run_in_progress} ]; then
echo "`date +%Y.%m.%d" "%H:%M:%S` - ${pkg_name}: Error! Another process running. Remove a file ${run_in_progress}" >&2
echo "Remove a file ${run_in_progress}" | ${BIN_DIR}/_lib/utl_send.sh "${mess_theme}" "${mail_recipients}"
exit 2
fi
# Prevent Parallel Execution:
touch ${run_in_progress}
# Run Implementation:
${BIN_DIR}/pkg/${pkg_name}/body.sh "${pkg_flag}" "${oracle_sid}" | tee ${log_file}
# Cleanup Stop Flag:
test -r ${run_in_progress} && rm ${run_in_progress}
$
$more body.sh
#!/bin/bash
#
# Check Parameters:
#
# Package Name:
tmp_string="`dirname $0`"
pkg_name="`basename ${tmp_string}`"
if [ "${pkg_name}" = "." ]; then
tmp_string="`pwd`"
pkg_name="`basename ${tmp_string}`"
fi
# Check for internal call:
if [ ! $1 = '_run' ]; then
echo "`date +%Y.%m.%d" "%H:%M:%S` - ${pkg_name}: Error! (Parameter=1) Call `basename $0` from ./_run.sh" >&2
exit 2
fi
# Check For:
if [ $2 ]
then
##echo " 2=${2} (ORACLE_SID)"
ORACLE_SID=`echo ${2} | tr "[A-Z]" "[a-z]"`; export ORACLE_SID
else
echo "`date +%Y.%m.%d" "%H:%M:%S` - ${pkg_name}: Error! (Parameter=2) Call `basename $0` With 2-d parameter" >&2
exit 2
fi
#
# Define local variables:
#
# Variables:
prefix="${COMPANY_NAME}#${HOST_NAME}(${ORACLE_SID}) ${pkg_name}"
mess_theme="${prefix}:Duplicate database"
log_file=${TMP_DIR}/${pkg_name}_${ORACLE_SID}.log
after_recreate_sql=/export/home/oradb/bin/_sql/testdb_after_recreate_${ORACLE_SID}.sql
after_recreate_log=/export/home/oradb/bin/_sql/testdb_after_recreate_${ORACLE_SID}.log
mail_recipients="$MAIL_DBA"
orapwd_filename="${ORACLE_HOME}/dbs/orapw${ORACLE_SID}"
orapwd_temp_passwd='oracle'
tnsnames_name="${ORACLE_SID}"
initfile_ora="${ORACLE_HOME}/dbs/init${ORACLE_SID}.ora"
spfile_ora="${ORACLE_HOME}/dbs/spfile${ORACLE_SID}.ora"
rman_cmdfile="${BIN_DIR}/_sql/make_${ORACLE_SID}.rman"
echo "Start RMAN Duplicate Database: `date +%Y.%m.%d" "%H:%M:%S`"
echo ''
echo "ORACLE_HOME=${ORACLE_HOME}"
echo "UTL_SQLPLUS=${UTL_SQLPLUS}"
echo "UTL_ORAPWD=${UTL_ORAPWD}"
echo "UTL_RMAN=${UTL_RMAN}"
echo "ORACLE_SID=${ORACLE_SID}"
echo "orapwd_filename=${orapwd_filename}"
echo "orapwd_temp_passwd=${orapwd_temp_passwd}"
echo "tnsnames_name=${tnsnames_name}"
echo "initfile_ora=${initfile_ora}"
echo "spfile_ora=${spfile_ora}"
echo "rman_cmdfile=${rman_cmdfile}"
echo ''
echo ''
#
# Task 0: Drop Database
# (2DO: First time - DB does`t not exist!!!)
#
echo "`date +%Y.%m.%d" "%H:%M:%S` Task 0: DROP DATABASE - Start"
${UTL_SQLPLUS} -s '/ as sysdba' << EOF
shutdown abort;
STARTUP MOUNT RESTRICT;
DROP DATABASE;
EOF
echo "`date +%Y.%m.%d" "%H:%M:%S` Task 0: DROP DATABASE - Finish"
echo ''
echo ''
#
# Step 0.A: Trying shutdown Instance
#
echo " Trying shutdown Instance: ${ORACLE_SID} - Start"
${UTL_SQLPLUS} /nolog << EOF
connect / as sysdba
shutdown abort;
EOF
echo " Trying shutdown Instance: ${ORACLE_SID} - Finish"
echo ''
echo ''
#
# Step 0.B: Trying to remove spfile
#
echo -n " Trying to remove spfile: ${spfile_ora}"
if [ -r ${spfile_ora} ]; then
rm ${spfile_ora}
echo " - Done."
else
echo " - Skipped. File not exist."
fi
echo ''
echo ''
#
# Task 1: Create an Oracle Password File for the Auxiliary Instance
#
echo "`date +%Y.%m.%d" "%H:%M:%S` Task 1: Create an Oracle Password File for the Auxiliary Instance - Start"
${UTL_ORAPWD} force=y file=${orapwd_filename} password=${orapwd_temp_passwd}
echo "`date +%Y.%m.%d" "%H:%M:%S` Task 1: Create an Oracle Password File for the Auxiliary Instance - Finish"
echo ''
echo ''
#
# Task 3: Create an Initialization Parameter File for the Auxiliary Instance
#
echo "`date +%Y.%m.%d" "%H:%M:%S` Task 3: Establish Oracle Net Connectivity to the Auxiliary Instance - Start"
rm -f ${initfile_ora}
echo "db_name=${ORACLE_SID}" > ${initfile_ora}
echo "shared_pool_size=1g" >> ${initfile_ora}
echo "`date +%Y.%m.%d" "%H:%M:%S` Task 3: Establish Oracle Net Connectivity to the Auxiliary Instance - Finish"
echo ''
echo ''
#
# Task 4: Start the Auxiliary Instance
#
echo "`date +%Y.%m.%d" "%H:%M:%S` Task 4: Start the Auxiliary Instance - Start"
${UTL_SQLPLUS} -s '/ as sysdba' << EOF
startup force nomount;
EOF
echo "`date +%Y.%m.%d" "%H:%M:%S` Task 4: Start the Auxiliary Instance - Finish"
echo ''
echo ''
#
# Task 5: Start DUPLICATE
#
echo "`date +%Y.%m.%d" "%H:%M:%S` Task 5: - Start"
${UTL_RMAN} cmdfile ${rman_cmdfile}
rm -f ${initfile_ora}
echo "`date +%Y.%m.%d" "%H:%M:%S` Task 5: - Finish"
echo ''
echo ''
#
# Task 6: Apply After Recreate Script
#
echo -n " Trying apply commands from file: ${after_recreate_sql}"
if [ -r ${after_recreate_sql} ]; then
export ORACLE_SID=${ORACLE_SID}
sqlplus /nolog @${after_recreate_sql} ${ORACLE_SID} > ${after_recreate_log}
echo " - Done."
else
echo " - Skipped. File not exist."
fi
echo "End RMAN Duplicate Database: `date +%Y.%m.%d" "%H:%M:%S`"
cat ${log_file} | ${UTL_SEND} "${mess_theme}" "${mail_recipients}"
$
$more make_testdbt1.rman
connect auxiliary sys/oracle@TESTDBT1.SUN.COM;
run {
duplicate target database to 'testdbt1'
SPFILE
SET CONTROL_FILES '+TEST','+TEST'
set db_create_file_dest='+TEST'
SET CLUSTER_DATABASE 'false'
set sga_max_size '10g'
set db_cache_size '5g'
set pga_aggregate_target '2g'
set shared_pool_size '2g'
set dispatchers '(PROTOCOL=TCP) (SERVICE=testdbt1XDB)'
reset audit_file_dest
reset dg_broker_start
reset fal_server
reset fal_client
reset log_archive_config
reset log_archive_dest_state_2
reset log_archive_dest_2
BACKUP LOCATION '/mnt/primary_backup/backups/oradb/rman/testdb/'
NOFILENAMECHECK
;
}
exit 0
$more testdb_after_recreate_testdbt1.sql
connect / as sysdba
alter system reset "__db_cache_size" scope=spfile sid='testdb';
alter system reset "__java_pool_size" scope=spfile sid='testdb';
alter system reset "__large_pool_size" scope=spfile sid='testdb';
alter system reset "__oracle_base" scope=spfile sid='testdb';
alter system reset "__pga_aggregate_target" scope=spfile sid='testdb';
alter system reset "__sga_target" scope=spfile sid='testdb';
alter system reset "__shared_io_pool_size" scope=spfile sid='testdb';
alter system reset "__shared_pool_size" scope=spfile sid='testdb';
alter system reset "__streams_pool_size" scope=spfile sid='testdb';
alter system reset "log_archive_format" scope=spfile sid='testdb';
alter system reset "log_archive_trace" scope=spfile sid='testdb';
-- Call procedure... :
begin
null;
end;
/
exit
$
$more listener.ora
# listener.ora Network Configuration File: /u01/app/11.2.0.3/grid/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = testdbt1.sun.com)
(ORACLE_HOME = /u01/app/oradb/product/11.2.0.3/dbhome_1)
(SID_NAME = testdbt1)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = test.sun.com)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /u01/app/oragrid
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent
$
su - oradb
$more tnsnames.ora
TESTDBT1.SUN.COM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = testdbt1.sun.com)
)
)
$
# Rman CloneDB_asm:
#00 22 * * * ${HOME}/bin/pkg/clonedb_asm/_run.sh testdbt1 > /dev/null 2>> /tmp/crontab_oracle.err
$more _run.sh
#!/bin/bash
#
# Package Name:
#
tmp_string="`dirname $0`"
pkg_name="`basename ${tmp_string}`"
if [ "${pkg_name}" = "." ]; then
tmp_string="`pwd`"
pkg_name="`basename ${tmp_string}`"
fi
# Check Parameters:
if [ $1 ]
then
oracle_sid=`echo ${1} | tr "[A-Z]" "[a-z]"`
else
echo "`date +%Y.%m.%d" "%H:%M:%S` - ${pkg_name}: Error! (Parameter=1). Call `basename $0`
exit 2
fi
#
# Setup Environment:
#
pkg_env="${HOME}/bin/_conf/.env"
if [ ! -r "${pkg_env}" ]; then
echo "`date +%Y.%m.%d" "%H:%M:%S` - ${pkg_name}: ERROR!!! Execution Aborted. Not Found Environment file: ${pkg_env}" >&2
exit 2
fi
if [ -s "${pkg_env}" ]; then
. ${pkg_env}
else
echo "`date +%Y.%m.%d" "%H:%M:%S` - ${pkg_name}: ERROR!!! Execution Aborted. Environment file is empty: ${pkg_env}" >&2
exit 2
fi
#
# Check Environment:
#
# Check ORA_USER:
if [ -z "${ORA_USER}" ]; then
echo "`date +%Y.%m.%d" "%H:%M:%S` - ${pkg_name}: ERROR!!! Execution Aborted. Environment variable ORA_USER not defined" >&2
exit 2
else
ora_user=${ORA_USER}
fi
# Check ORACLE_BASE:
if [ -z "${ORACLE_BASE}" ]; then
echo "`date +%Y.%m.%d" "%H:%M:%S` - ${pkg_name}: ERROR!!! Execution Aborted. Environment variable ORACLE_BASE not defined" >&2
exit 2
elif [ ! -d "${ORACLE_BASE}" ]; then
echo "`date +%Y.%m.%d" "%H:%M:%S` - ${pkg_name}: ERROR!!! Execution Aborted. Directory ORACLE_BASE not found (${ORACLE_BASE})" >&2
exit 2
fi
# Check ORACLE_HOME:
if [ -z "${ORACLE_HOME}" ]; then
echo "`date +%Y.%m.%d" "%H:%M:%S` - ${pkg_name}: ERROR!!! Execution Aborted. Environment variable ORACLE_HOME not defined" >&2
exit 2
elif [ ! -d "${ORACLE_HOME}" ]; then
echo "`date +%Y.%m.%d" "%H:%M:%S` - ${pkg_name}: ERROR!!! Execution Aborted. Directory ORACLE_HOME not found (${ORACLE_HOME})" >&2
exit 2
fi
# Check TMP_DIR:
if [ -z "${TMP_DIR}" ]; then
echo "`date +%Y.%m.%d" "%H:%M:%S` - ${pkg_name}: ERROR!!! Execution Aborted. Environment variable TMP_DIR not defined" >&2
exit 2
elif [ ! -d "${TMP_DIR}" ]; then
echo "`date +%Y.%m.%d" "%H:%M:%S` - ${pkg_name}: ERROR!!! Execution Aborted. Directory TMP_DIR not found (${TMP_DIR})" >&2
exit 2
elif [ ! -w "${TMP_DIR}" ]; then
echo "`date +%Y.%m.%d" "%H:%M:%S` - ${pkg_name}: ERROR!!! Execution Aborted. Directory TMP_DIR not writeable (${TMP_DIR})" >&2
exit 2
fi
# Check BIN_DIR:
if [ -z "${BIN_DIR}" ]; then
echo "`date +%Y.%m.%d" "%H:%M:%S` - ${pkg_name}: ERROR!!! Execution Aborted. Environment variable BIN_DIR not defined" >&2
exit 2
elif [ ! -d "${BIN_DIR}" ]; then
echo "`date +%Y.%m.%d" "%H:%M:%S` - ${pkg_name}: ERROR!!! Execution Aborted. Directory BIN_DIR not found (${BIN_DIR})" >&2
exit 2
fi
# Check COMPANY_NAME:
if [ -z "${COMPANY_NAME}" ]; then
echo "`date +%Y.%m.%d" "%H:%M:%S` - ${pkg_name}: ERROR!!! Execution Aborted. Environment variable COMPANY_NAME not defined" >&2
exit 2
fi
# Check HOST_NAME:
if [ -z "${HOST_NAME}" ]; then
echo "`date +%Y.%m.%d" "%H:%M:%S` - ${pkg_name}: ERROR!!! Execution Aborted. Environment variable HOST_NAME not defined" >&2
exit 2
fi
# Check MAIL_RECIPIENTS:
if [ -z "${MAIL_DBA}" ]; then
echo "`date +%Y.%m.%d" "%H:%M:%S` - ${pkg_name}: ERROR!!! Execution Aborted. Environment variable MAIL_RECIPIENTS not defined" >&2
exit 2
fi
# Check UTL_SEND:
if [ -z "${UTL_SEND}" ]; then
echo "`date +%Y.%m.%d" "%H:%M:%S` - ${pkg_name}: ERROR!!! Execution Aborted. Environment variable UTL_SEND not defined" >&2
exit 2
elif [ ! -f "${UTL_SEND}" ]; then
echo "`date +%Y.%m.%d" "%H:%M:%S` - ${pkg_name}: ERROR!!! Execution Aborted. File UTL_SEND not found (${UTL_SEND})" >&2
exit 2
elif [ ! -x "${UTL_SEND}" ]; then
echo "`date +%Y.%m.%d" "%H:%M:%S` - ${pkg_name}: ERROR!!! Execution Aborted. File UTL_SEND not executable (${UTL_SEND})" >&2
exit 2
fi
# Check UTL_SQLPLUS:
if [ -z "${UTL_SQLPLUS}" ]; then
echo "`date +%Y.%m.%d" "%H:%M:%S` - ${pkg_name}: ERROR!!! Execution Aborted. Environment variable UTL_SQLPLUS not defined" >&2
exit 2
elif [ ! -f "${UTL_SQLPLUS}" ]; then
echo "`date +%Y.%m.%d" "%H:%M:%S` - ${pkg_name}: ERROR!!! Execution Aborted. File UTL_SQLPLUS not found (${UTL_SQLPLUS})" >&2
exit 2
elif [ ! -x "${UTL_SQLPLUS}" ]; then
echo "`date +%Y.%m.%d" "%H:%M:%S` - ${pkg_name}: ERROR!!! Execution Aborted. File UTL_SQLPLUS not executable (${UTL_SQLPLUS})" >&2
exit 2
fi
# Check UTL_ORAPWD:
if [ -z "${UTL_ORAPWD}" ]; then
echo "`date +%Y.%m.%d" "%H:%M:%S` - ${pkg_name}: ERROR!!! Execution Aborted. Environment variable UTL_ORAPWD not defined" >&2
exit 2
elif [ ! -f "${UTL_ORAPWD}" ]; then
echo "`date +%Y.%m.%d" "%H:%M:%S` - ${pkg_name}: ERROR!!! Execution Aborted. File UTL_ORAPWD not found (${UTL_ORAPWD})" >&2
exit 2
elif [ ! -x "${UTL_ORAPWD}" ]; then
echo "`date +%Y.%m.%d" "%H:%M:%S` - ${pkg_name}: ERROR!!! Execution Aborted. File UTL_ORAPWD not executable (${UTL_ORAPWD})" >&2
exit 2
fi
# Check UTL_RMAN:
if [ -z "${UTL_RMAN}" ]; then
echo "`date +%Y.%m.%d" "%H:%M:%S` - ${pkg_name}: ERROR!!! Execution Aborted. Environment variable UTL_RMAN not defined" >&2
exit 2
elif [ ! -f "${UTL_RMAN}" ]; then
echo "`date +%Y.%m.%d" "%H:%M:%S` - ${pkg_name}: ERROR!!! Execution Aborted. File UTL_RMAN not found (${UTL_RMAN})" >&2
exit 2
elif [ ! -x "${UTL_RMAN}" ]; then
echo "`date +%Y.%m.%d" "%H:%M:%S` - ${pkg_name}: ERROR!!! Execution Aborted. File UTL_RMAN not executable (${UTL_RMAN})" >&2
exit 2
fi
# Initialize Package:
pkg_flag="`basename $0 .sh`"
run_in_progress=${TMP_DIR}/${pkg_name}_${oracle_sid}.lock
log_file=${TMP_DIR}/${pkg_name}_${oracle_sid}.log
prefix="${COMPANY_NAME}#${HOST_NAME}(${oracle_sid}) ${pkg_name}"
mess_theme="${prefix}: Error! Another process running (${run_in_progress})"
mail_recipients="$MAIL_DBA"
echo $mail_recipients
# Check Another process for running:
if [ -f ${run_in_progress} ]; then
echo "`date +%Y.%m.%d" "%H:%M:%S` - ${pkg_name}: Error! Another process running. Remove a file ${run_in_progress}" >&2
echo "Remove a file ${run_in_progress}" | ${BIN_DIR}/_lib/utl_send.sh "${mess_theme}" "${mail_recipients}"
exit 2
fi
# Prevent Parallel Execution:
touch ${run_in_progress}
# Run Implementation:
${BIN_DIR}/pkg/${pkg_name}/body.sh "${pkg_flag}" "${oracle_sid}" | tee ${log_file}
# Cleanup Stop Flag:
test -r ${run_in_progress} && rm ${run_in_progress}
$
$more body.sh
#!/bin/bash
#
# Check Parameters:
#
# Package Name:
tmp_string="`dirname $0`"
pkg_name="`basename ${tmp_string}`"
if [ "${pkg_name}" = "." ]; then
tmp_string="`pwd`"
pkg_name="`basename ${tmp_string}`"
fi
# Check for internal call:
if [ ! $1 = '_run' ]; then
echo "`date +%Y.%m.%d" "%H:%M:%S` - ${pkg_name}: Error! (Parameter=1) Call `basename $0` from ./_run.sh" >&2
exit 2
fi
# Check For
if [ $2 ]
then
##echo " 2=${2} (ORACLE_SID)"
ORACLE_SID=`echo ${2} | tr "[A-Z]" "[a-z]"`; export ORACLE_SID
else
echo "`date +%Y.%m.%d" "%H:%M:%S` - ${pkg_name}: Error! (Parameter=2) Call `basename $0` With 2-d parameter" >&2
exit 2
fi
#
# Define local variables:
#
# Variables:
prefix="${COMPANY_NAME}#${HOST_NAME}(${ORACLE_SID}) ${pkg_name}"
mess_theme="${prefix}:Duplicate database"
log_file=${TMP_DIR}/${pkg_name}_${ORACLE_SID}.log
after_recreate_sql=/export/home/oradb/bin/_sql/testdb_after_recreate_${ORACLE_SID}.sql
after_recreate_log=/export/home/oradb/bin/_sql/testdb_after_recreate_${ORACLE_SID}.log
mail_recipients="$MAIL_DBA"
orapwd_filename="${ORACLE_HOME}/dbs/orapw${ORACLE_SID}"
orapwd_temp_passwd='oracle'
tnsnames_name="${ORACLE_SID}"
initfile_ora="${ORACLE_HOME}/dbs/init${ORACLE_SID}.ora"
spfile_ora="${ORACLE_HOME}/dbs/spfile${ORACLE_SID}.ora"
rman_cmdfile="${BIN_DIR}/_sql/make_${ORACLE_SID}.rman"
echo "Start RMAN Duplicate Database: `date +%Y.%m.%d" "%H:%M:%S`"
echo ''
echo "ORACLE_HOME=${ORACLE_HOME}"
echo "UTL_SQLPLUS=${UTL_SQLPLUS}"
echo "UTL_ORAPWD=${UTL_ORAPWD}"
echo "UTL_RMAN=${UTL_RMAN}"
echo "ORACLE_SID=${ORACLE_SID}"
echo "orapwd_filename=${orapwd_filename}"
echo "orapwd_temp_passwd=${orapwd_temp_passwd}"
echo "tnsnames_name=${tnsnames_name}"
echo "initfile_ora=${initfile_ora}"
echo "spfile_ora=${spfile_ora}"
echo "rman_cmdfile=${rman_cmdfile}"
echo ''
echo ''
#
# Task 0: Drop Database
# (2DO: First time - DB does`t not exist!!!)
#
echo "`date +%Y.%m.%d" "%H:%M:%S` Task 0: DROP DATABASE - Start"
${UTL_SQLPLUS} -s '/ as sysdba' << EOF
shutdown abort;
STARTUP MOUNT RESTRICT;
DROP DATABASE;
EOF
echo "`date +%Y.%m.%d" "%H:%M:%S` Task 0: DROP DATABASE - Finish"
echo ''
echo ''
#
# Step 0.A: Trying shutdown Instance
#
echo " Trying shutdown Instance: ${ORACLE_SID} - Start"
${UTL_SQLPLUS} /nolog << EOF
connect / as sysdba
shutdown abort;
EOF
echo " Trying shutdown Instance: ${ORACLE_SID} - Finish"
echo ''
echo ''
#
# Step 0.B: Trying to remove spfile
#
echo -n " Trying to remove spfile: ${spfile_ora}"
if [ -r ${spfile_ora} ]; then
rm ${spfile_ora}
echo " - Done."
else
echo " - Skipped. File not exist."
fi
echo ''
echo ''
#
# Task 1: Create an Oracle Password File for the Auxiliary Instance
#
echo "`date +%Y.%m.%d" "%H:%M:%S` Task 1: Create an Oracle Password File for the Auxiliary Instance - Start"
${UTL_ORAPWD} force=y file=${orapwd_filename} password=${orapwd_temp_passwd}
echo "`date +%Y.%m.%d" "%H:%M:%S` Task 1: Create an Oracle Password File for the Auxiliary Instance - Finish"
echo ''
echo ''
#
# Task 3: Create an Initialization Parameter File for the Auxiliary Instance
#
echo "`date +%Y.%m.%d" "%H:%M:%S` Task 3: Establish Oracle Net Connectivity to the Auxiliary Instance - Start"
rm -f ${initfile_ora}
echo "db_name=${ORACLE_SID}" > ${initfile_ora}
echo "shared_pool_size=1g" >> ${initfile_ora}
echo "`date +%Y.%m.%d" "%H:%M:%S` Task 3: Establish Oracle Net Connectivity to the Auxiliary Instance - Finish"
echo ''
echo ''
#
# Task 4: Start the Auxiliary Instance
#
echo "`date +%Y.%m.%d" "%H:%M:%S` Task 4: Start the Auxiliary Instance - Start"
${UTL_SQLPLUS} -s '/ as sysdba' << EOF
startup force nomount;
EOF
echo "`date +%Y.%m.%d" "%H:%M:%S` Task 4: Start the Auxiliary Instance - Finish"
echo ''
echo ''
#
# Task 5: Start DUPLICATE
#
echo "`date +%Y.%m.%d" "%H:%M:%S` Task 5: - Start"
${UTL_RMAN} cmdfile ${rman_cmdfile}
rm -f ${initfile_ora}
echo "`date +%Y.%m.%d" "%H:%M:%S` Task 5: - Finish"
echo ''
echo ''
#
# Task 6: Apply After Recreate Script
#
echo -n " Trying apply commands from file: ${after_recreate_sql}"
if [ -r ${after_recreate_sql} ]; then
export ORACLE_SID=${ORACLE_SID}
sqlplus /nolog @${after_recreate_sql} ${ORACLE_SID} > ${after_recreate_log}
echo " - Done."
else
echo " - Skipped. File not exist."
fi
echo "End RMAN Duplicate Database: `date +%Y.%m.%d" "%H:%M:%S`"
cat ${log_file} | ${UTL_SEND} "${mess_theme}" "${mail_recipients}"
$
connect auxiliary sys/oracle@TESTDBT1.SUN.COM;
run {
duplicate target database to 'testdbt1'
SPFILE
SET CONTROL_FILES '+TEST','+TEST'
set db_create_file_dest='+TEST'
SET CLUSTER_DATABASE 'false'
set sga_max_size '10g'
set db_cache_size '5g'
set pga_aggregate_target '2g'
set shared_pool_size '2g'
set dispatchers '(PROTOCOL=TCP) (SERVICE=testdbt1XDB)'
reset audit_file_dest
reset dg_broker_start
reset fal_server
reset fal_client
reset log_archive_config
reset log_archive_dest_state_2
reset log_archive_dest_2
BACKUP LOCATION '/mnt/primary_backup/backups/oradb/rman/testdb/'
NOFILENAMECHECK
;
}
exit 0
$more testdb_after_recreate_testdbt1.sql
connect / as sysdba
alter system reset "__db_cache_size" scope=spfile sid='testdb';
alter system reset "__java_pool_size" scope=spfile sid='testdb';
alter system reset "__large_pool_size" scope=spfile sid='testdb';
alter system reset "__oracle_base" scope=spfile sid='testdb';
alter system reset "__pga_aggregate_target" scope=spfile sid='testdb';
alter system reset "__sga_target" scope=spfile sid='testdb';
alter system reset "__shared_io_pool_size" scope=spfile sid='testdb';
alter system reset "__shared_pool_size" scope=spfile sid='testdb';
alter system reset "__streams_pool_size" scope=spfile sid='testdb';
alter system reset "log_archive_format" scope=spfile sid='testdb';
alter system reset "log_archive_trace" scope=spfile sid='testdb';
-- Call procedure... :
begin
null;
end;
/
exit
$
Комментариев нет:
Отправить комментарий