на 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;
#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`
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;
Комментариев нет:
Отправить комментарий