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
$

Make dataguard

на primary:

#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=
      (SID_NAME = testdb)
      (GLOBAL_DBNAME=testdb_p_DGMGRL.sun.com)
      (ORACLE_HOME=/u01/app/oradb/product/11.2.0.3/dbhome_1)
    )
  )


LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = primary.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
#
# TESTDB:
#

TESTDB_P.SUN.COM =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = m5k3.sun.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (UR = A)
      (SERVICE_NAME = testdb_p_DGMGRL.sun.com)
    )
  )

TESTDB_S.SUN.COM =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = m5k4.sun.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (UR = A)
      (SERVICE_NAME = testdb_s_DGMGRL.sun.com)
    )
  )

TESTDB.SUN.COM =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = m5k3.sun.com)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = m5k4.sun.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = testdb_app.sun.com)
      (SERVER = DEDICATED)
      (FAILOVER_MODE =
        (TYPE = SESSION)
        (METHOD = BASIC)
        (RETRIES = 180)
        (DELAY = 1)
      )
    )
  )


$



alter system set DB_UNIQUE_NAME='testdb_p' SCOPE=SPFILE;

alter system set log_archive_format='%t_%s_%r.arc' SCOPE=SPFILE;
             

archive log list

shutdown immediate
startup mount
alter database archivelog;
alter database open;
archive log list


alter database force logging;
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(testdb_p, testdb_s1)';
alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES, ALL_ROLES)';
alter system set LOG_ARCHIVE_DEST_2='SERVICE=testdb_s1.sun.com LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=testdb_s1';
alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE;
alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE;
alter system set remote_login_passwordfile='EXCLUSIVE' scope=spfile;
alter system set FAL_SERVER=testdb_s1;
alter system set FAL_CLIENT=testdb_p;

SELECT a.group#, a.member, b.bytes FROM v$logfile a, v$log b WHERE a.group# = b.group#;

ALTER DATABASE ADD STANDBY LOGFILE GROUP  7 SIZE 314572800;
ALTER DATABASE ADD STANDBY LOGFILE GROUP  8 SIZE 314572800;
ALTER DATABASE ADD STANDBY LOGFILE GROUP  9 SIZE 314572800;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 10 SIZE 314572800;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 11 SIZE 314572800;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 12 SIZE 314572800;

select status, type, member, group# from v$logfile;
select * from v$standby_log;




на standby:
#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=
      (SID_NAME = testdb)
      (GLOBAL_DBNAME=testdb_s1_DGMGRL.sun.com)
      (ORACLE_HOME=/u01/app/oradb/product/11.2.0.3/dbhome_1)
    )
  )


LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = standby.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

#
# TESTDB:
#

TESTDB_P.SUN.COM =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = m5k3.sun.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (UR = A)
      (SERVICE_NAME = testdb_p_DGMGRL.sun.com)
    )
  )

TESTDB_S.SUN.COM =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = m5k4.sun.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (UR = A)
      (SERVICE_NAME = testdb_s_DGMGRL.sun.com)
    )
  )

TESTDB.SUN.COM =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = m5k3.sun.com)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = m5k4.sun.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = testdb_app.sun.com)
      (SERVER = DEDICATED)
      (FAILOVER_MODE =
        (TYPE = SESSION)
        (METHOD = BASIC)
        (RETRIES = 180)
        (DELAY = 1)
      )
    )
  )



$



# Make dataguard:
00 23 * * * ${HOME}/bin/pkg/make_dataguard/_run.sh   testdb     > /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 ORACLE_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_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

# 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}:Make dataguard"
log_file=${TMP_DIR}/${pkg_name}_${ORACLE_SID}.log

after_recreate_sql=/export/home/oradb/bin/_sql/dataguard_after_recreate_${ORACLE_SID}.sql
after_recreate_log=/export/home/oradb/bin/_sql/dataguard_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_physical_standby_${ORACLE_SID}.rman"

echo "Start RMAN Make Dataguard: `date +%Y.%m.%d" "%H:%M:%S`"
echo ''

echo "MASTER_HOST=${MASTER_HOST}"
echo "ORACLE_SID=${ORACLE_SID}"
echo "ORACLE_HOME=${ORACLE_HOME}"
echo "UTL_SQLPLUS=${UTL_SQLPLUS}"
echo "UTL_ORAPWD=${UTL_ORAPWD}"
echo "UTL_RMAN=${UTL_RMAN}"
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
##############################################
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 ''


#####################################
# Task 1: 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 ''


############################################################################
# Task 2: Create an Initialization Parameter File for the Auxiliary Instance
############################################################################
echo "`date +%Y.%m.%d" "%H:%M:%S` Task 2: Create an Initialization Parameter File for the Auxiliary Instance"

echo -n " Trying to remove spfile: ${initfile_ora}"

if [ -r ${initfile_ora} ]; then
    rm -f ${initfile_ora}
    echo " - Done."
else
    echo " - Skipped. File not exist."
fi

echo  "db_name=${ORACLE_SID}" >  ${initfile_ora}


echo "`date +%Y.%m.%d" "%H:%M:%S` Task 2: Create an Initialization Parameter File for the Auxiliary Instance"
echo ''
echo ''


############################################################################
# Task 3: Remove SPFILE  for the Auxiliary Instance
############################################################################
echo "`date +%Y.%m.%d" "%H:%M:%S` Task 3: Remove SPFILE  for the Auxiliary Instance"

echo -n " Trying to remove spfile: ${spfile_ora}"

if [ -r ${spfile_ora} ]; then
    rm -f ${spfile_ora}
    echo " - Done."
else
    echo " - Skipped. File not exist."
fi

echo "`date +%Y.%m.%d" "%H:%M:%S` Task 3: Remove SPFILE  for the Auxiliary Instance"
echo ''
echo ''



###################################################################
# Task 4: Create an Oracle Password File for the Auxiliary Instance
###################################################################
echo "`date +%Y.%m.%d" "%H:%M:%S` Task 4: Create an Oracle Password File for the Auxiliary Instance - Start"

echo -n " Trying to remove spfile: ${orapwd_filename}"

if [ -r ${orapwd_filename} ]; then
    rm -f ${orapwd_filename}
    echo " - Done."
else
    echo " - Skipped. File not exist."
fi

${UTL_ORAPWD} force=y file=${orapwd_filename} password=${orapwd_temp_passwd}

echo "`date +%Y.%m.%d" "%H:%M:%S` Task 4: Create an Oracle Password File for the Auxiliary Instance - Finish"
echo ''
echo ''



######################################
# Task 5: Start the Auxiliary Instance
######################################
echo "`date +%Y.%m.%d" "%H:%M:%S` Task 5: Start the Auxiliary Instance - Start"

# Start up the auxiliary instance nomount
${UTL_SQLPLUS}  -s '/ as sysdba' << EOF
startup force nomount;
EOF

echo "`date +%Y.%m.%d" "%H:%M:%S` Task 5: Start the Auxiliary Instance - Finish"
echo ''
echo ''


######################################
# Task 6: Start Make Dataguard script
######################################
echo "`date +%Y.%m.%d" "%H:%M:%S` Task 6: Start Make Dataguard script"

${UTL_RMAN} cmdfile ${rman_cmdfile}

echo "`date +%Y.%m.%d" "%H:%M:%S` Task 6: Start Make Dataguard script"
echo ''
echo ''


############################################################################
# Task 7: Remove an Initialization Parameter File for the Auxiliary Instance
############################################################################
echo "`date +%Y.%m.%d" "%H:%M:%S` Task 7: Remove an Initialization Parameter File for the Auxiliary Instance"

echo -n " Trying to remove spfile: ${initfile_ora}"

if [ -r ${initfile_ora} ]; then
    rm -f ${initfile_ora}
    echo " - Done."
else
    echo " - Skipped. File not exist."
fi

echo "`date +%Y.%m.%d" "%H:%M:%S` Task 7: Remove an Initialization Parameter File for the Auxiliary Instance"
echo ''
echo ''


#######################################################
# Task 8: Copy PRYMARY Password File to STANDBY server
#######################################################
echo "`date +%Y.%m.%d" "%H:%M:%S` Task 8: Copy PRYMARY Password File to STANDBY server"

echo -n " Trying to remove spfile: ${orapwd_filename}"

if [ -r ${orapwd_filename} ]; then
    rm -f ${orapwd_filename}
    echo " - Done."
else
    echo " - Skipped. File not exist."
fi

scp -pr ${MASTER_HOST}:${orapwd_filename} ${orapwd_filename}

echo "`date +%Y.%m.%d" "%H:%M:%S` Task 8: Copy PRYMARY Password File to STANDBY server"
echo ''
echo ''


echo "End make Dataguard: `date +%Y.%m.%d" "%H:%M:%S`"

cat  ${log_file}  | ${BIN_DIR}/_lib/utl_send.sh "${mess_theme}" "${mail_recipients}"
$




$more make_physical_standby_testdb.rman
connect auxiliary sys/oracle@TESTDB_S1.SUN.COM;
run {
DUPLICATE TARGET DATABASE
   FOR STANDBY
   BACKUP LOCATION '/mnt/primary_backup/backups/oradb/rman/testdb/'
    spfile
     set control_files='+DATA','+DATA','+DATA'
     set db_unique_name='testdb_s1'
     set fal_client='testdb_s1'
     set fal_server='testdb_p'
     set standby_file_management='AUTO'
     set log_archive_config='DG_CONFIG=(testdb_p, testdb_s1)'
     set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES, ALL_ROLES)'
     set log_archive_dest_2='SERVICE=testdb_p.sun.com LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=testdb_p'
     set log_archive_dest_state_1='ENABLE'
     nofilenamecheck
;
}

exit 0

$


на primary:

SQL> show parameter DG_BROKER_START
SQL> alter system set DG_BROKER_START=true scope=both;
SQL> show parameter DG_BROKER_START

на standby:

SQL> show parameter DG_BROKER_START
SQL> alter system set DG_BROKER_START=true scope=both;
SQL> show parameter DG_BROKER_START


на primary:

DGMGRL> connect sys/pwd@testdb_p.sun.com;

create configuration 'testdb_bdg' as primary database is testdb_p connect identifier is testdb_p.sun.com;

add database testdb_s1 as connect identifier is testdb_s1.sun.com maintained as physical;

show configuration

enable configuration;


на standby:

#On the standby database, start the MRP process.
$alter database recover managed standby database disconnect from session;
$alter database recover managed standby database cancel;