Clone database

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
$