DATAGURAD SETUP IN ORACLE11G
In Primary
1) Change the spfile in primary database
and restart
ALTER
SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(TESTPMS,TESTPMSstdb)';
ALTER
SYSTEM SET LOG_ARCHIVE_DEST_2='service="TESTPMSstdb"','LGWR ASYNC
NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1
reopen=300
db_unique_name="TESTPMS_STBY"
net_timeout=30','valid_for=(all_logfiles,primary_role)'
ALTER
SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
ALTER
SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE;
ALTER
SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE
ALTER
SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
ALTER
SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=4;
ALTER
SYSTEM SET FAL_SERVER='TESTPMSSTdb';
ALTER
SYSTEM SET FAL_CLIENT='TESTPMS';
2) Genrate pfile in Primary DB
create
pfile='/app/oracle/admin/TESTPMS/pfile/initTESTPMS.ora' from spfile;
3) Run backup script in primary
4) copied the control files, backup, onlinelogs
and archive logs, pfile
scp
-r -i /root/TESTcloud.pem -P 22 /backup/flash_recovery_area/
root@10.0.1.39:/backup/flash_recovery_area/TESTPMS/
scp
-r -i /root/TESTcloud.pem -P 22 /backup/fast_recovery_area/TESTPMS/control02.ctl
root@10.0.1.39:/backup/fast_recovery_area/TESTPMS/control02.ctl
scp
-r -i /root/TESTcloud.pem -P 22 /data/controlfile/TESTPMS/control01.ctl
root@10.0.1.39:/data/controlfile/TESTPMS/control01.ctl
scp
-r -i /root/TESTcloud.pem -P 22 /backup/flash_recovery_area/TESTPMS/autobackup/
root@10.0.1.39:/backup/flash_recovery_area/TESTPMS/autobackup/
scp
-i /root/TESTcloud.pem -P 22 /app/oracle/admin/TESTPMS/pfile/initTESTPMS.ora
root@10.0.1.39:/app/oracle/admin/TESTPMS/pfile/initTESTPMS.ora
5) Enable DG broker
ALTER
SYSTEM SET DG_BROKER_START=TRUE SCOPE=BOTH;
6) Remote login password file.
orapwd file=orapwTESTPMSstdb
password=STANDARD IGNORECASE=Y
7) Configure listener
#####################
#tnsnames.ora########
#####################
#
tnsnames.ora Network Configuration File:
/app/oracle/11.3.0/network/admin/tnsnames.ora
#
Generated by Oracle configuration tools.
TESTPMSprdb
=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =
devprimary)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TESTPMS.psc.com)
)
)
TESTPMSstdb
=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =
devstandby)(PORT = 1521))
(CONNECT_DATA = (UR=A)
(SERVER = DEDICATED)
(SERVICE_NAME = TESTPMSstdb.psc.com)
)
)
#####################
#listener.ora########
#####################
#
listener.ora Network Configuration File:
/app/oracle/11.3.0/network/admin/listener.ora
#
Generated by Oracle configuration tools.
ADR_BASE_LISTENER_TESTPMS
= /app/oracle
LISTENER_TESTPMS
=
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =
devprimary)(PORT = 1521))
)
)
SID_LIST_LISTENER
=
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = TESTPMS)
(ORACLE_HOME = /app/oracle/11.3.0)
(SID_NAME = TESTPMS)
)
)
8) Create DG config
CREATE
CONFIGURATION 'TESTR' AS PRIMARY DATABASE IS 'TESTPMS' CONNECT IDENTIFIER IS TESTPMSprdb
ADD
DATABASE 'TESTPMSstdb' AS CONNECT IDENTIFIER IS TESTPMSstdb MAINTAINED AS
PHYSICAL;
enable
configuration;
9) Connect to RMAN ( after step 7 in Standby)
rman target sys/STANDARD@TESTPMSprdb auxiliary
sys/STANDARD@TESTPMSstdb
10) Duplicate DB connect to auxiliary db from
primary ( after step 7 in Standby)
DUPLICATE
TARGET DATABASE
FOR
STANDBY
FROM
ACTIVE DATABASE
DORECOVER
NOFILENAMECHECK;
In Standby
1) Modify the bash_profile (
ORACLE_HOSTNAME,ORACLE_UNQNAME,ORACLE_SID). Changed the ORACLE_SID FROM TESTpms
to TESTpmsstdb in bash_profile
TMP=/tmp;
export TMP
TMPDIR=$TMP;
export TMPDIR
ORACLE_HOSTNAME=devstandby;
export ORACLE_HOSTNAME
ORACLE_BASE=/app/oracle;
export ORACLE_BASE
ORACLE_UNQNAME=TESTPMSstdb;
export ORACLE_UNQNAME
ORACLE_HOME=$ORACLE_BASE/11.3.0;
export ORACLE_HOME
PATH=/usr/sbin:$PATH;
export PATH
export
ORACLE_SID=TESTPMSstdb
PATH=$ORACLE_HOME/bin:$PATH;
export PATH
PATH=$PATH:$ORACLE_HOME/OPatch;
export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib;
export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib;
export CLASSPATH
if [
$USER = "oracle" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi
2) Created a template form the old DB in standby
DB
3) Create new database from the template
4) Changed the below in the pfile copied
from primary ( after step 4 in primary)
*.db_unique_name='TESTPMSstdb'
*.fal_client='TESTPMSSTDB'
*.fal_server='TESTPMS'
*.log_archive_dest_2='SERVICE=TESTPMSprdb
ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=TESTPMS'
5) Rename the file from initTESTPMS.ora
to initTESTPMSstdb.ora
6) Create spfile from the modified pfile
from the above steps
create
spfile from pfile='/app/oracle/admin/TESTPMS/pfile/initTESTPMSstdb.ora';
7) Configure listener
#####################
#tnsnames.ora########
#####################
#
tnsnames.ora Network Configuration File:
/app/oracle/11.3.0/network/admin/tnsnames.ora
#
Generated by Oracle configuration tools.
TESTPMSprdb
=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = devprimary)(PORT
= 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TESTPMS.psc.com)
)
)
TESTPMSstdb
=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =
devstandby)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TESTPMSstdb)
)
)
#####################
#listener.ora########
#####################
#
listener.ora Network Configuration File:
/app/oracle/11.3.0/network/admin/listener.ora
#
Generated by Oracle configuration tools.
ADR_BASE_LISTENER_TESTPMS
= /app/oracle
LISTENER_TESTPMSSTDB
=
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =
devstandby)(PORT = 1521))
)
)
SID_LIST_LISTENER
=
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = TESTPMS)
(ORACLE_HOME = /app/oracle/11.3.0)
(SID_NAME = TESTPMSstdb)
)
)
8) Duplicate DB from standby ( This is optional)
rman
target sys/STANDARD
startup
mount
restore
database
9) Enable DG broker
ALTER
SYSTEM SET DG_BROKER_START=TRUE SCOPE=BOTH;
10) Remote login password file.
orapwd
file=orapwTESTPMSstdb password=STANDARD IGNORECASE=Y
11) Once duplicate DB recovery completed
after the step in 10 (Primary)
shutdown
immediate
startup
alter
database recover managed standby database disconnect from session;
13. Any error from the above step, then execute
the below commands
alter
database recover managed standby database CANCEL;
alter database recover managed standby
database disconnect from session;
14. Check DB
mode
select
OPEN_MODE, DATABASE_ROLE from v$database;
OPEN_MODE
DATABASE_ROLE
--------------------
----------------
READ
ONLY WITH APPLY PHYSICAL STANDBY
15. Add
standby redo log files
ALTER
SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;;
alter
database add standby logfile '/backup/flash_recovery_area/TESTPMSSTDB/onlinelog/stdby_o1_mf_3_9qqbp03n_.log'
size 209715200;
alter
database add standby logfile '/backup/flash_recovery_area/TESTPMSSTDB/onlinelog/stdby_o1_mf_2_9qqbot0k_.log'
size 209715200;
alter
database add standby logfile '/backup/flash_recovery_area/TESTPMSSTDB/onlinelog/stdby_o1_mf_1_9qqbomkn_.log'
size 209715200;
ALTER
SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO scope=both;
16. If redo
online log location is in the flash_recovery location then change the location
of the redo online logs to match the primary
17. Install
script to clean up the archive log
#!/bin/bash
##.
~/.bash_profile
export
ORATAB=/etc/oratab
export
PATH=/usr/local/bin:/usr/sbin:$PATH
export
SQLDIR=/home/oracle/scripts/
export
DATE=`date +%y%m%d`
ps
-ef|grep -v grep|grep pmon|awk '{printf(substr($8,10)"\n")}' | while
read SID
do
ORACLE_SID=$SID
export ORACLE_SID=$SID
if [[ "$ORACLE_SID" =
"+ASM"* ]]; then
echo
"ASM instance"
else
export ORAENV_ASK=NO; . oraenv
echo Delete archivelog for ${ORACLE_SID} database.
${ORACLE_HOME}/bin/rman target /
<<EOF
delete
noprompt archivelog until time 'SYSDATE-2';
EOF
fi
done
exit
