-------- ---------
primary Standby
(SITEA) (SITEB)
-------- ---------
Host:dbaovm03 Host:dbaovm05
db_name=dvlp db_name=dvlp
instance_name=dvlp1 instance_name=dvlp1
db_unique_name=dvlp db_unique_name=dvlpb
========================================================
Standby Host
========================================================
-- Create necessary directory
-- Note DB Unique Name Directory
mkdir -p /apps/oracle/diag/rdbms/dvlpb/dvlp1/trace
cp /apps/crs/grid/network/admin/listener.ora /apps/crs/grid/network/admin/listener.ora.orig
vi /apps/crs/grid/network/admin/listener.ora
-- Add a listener entry to connect as auxilary ( we need to connect later)
-- Append the following lines at the end in listenr.ora (GRID home)
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(ORACLE_HOME=/apps/oracle/product/11.2.0/db)
(SID_NAME=dvlp1))
(SID_DESC=
(SID_NAME=plsextproc)
(ORACLE_HOME=/apps/oracle/product/11.2.0/db)
(PROGRAM=extproc)))
-- chk if dvlp1 is now registered and NOT BLOCKED
oracle@dbaovm05# srvctl stop listener
oracle@dbaovm05# ps -ef | grep -i lis
Kill any listener process
oracle@dbaovm05# srvctl start listener
oracle@dbaovm05# lsnrctl services
-- create basic pfile
NOTE : Better to get a COPY from Primary and Modify
You want to Keep Many parameters same as the Primary
Example All Memory, Compatable, Disks Etc
vi /apps/oracle/product/11.2.0/db/dbs/initdvlp1.ora
<add memory parameters>
db_name=dvlp <== Same as Primary
instance_name=dvlp1 <== Same as Primary
db_unique_name=dvlpb <== DIFFERENt from primary
cluster_database=true <== Cluster
#dvlp1.undo_tablespace=UNDOTBS1
#instance_number=1
db_domain=oracle.cfx <== Check This this is NOT CARFAX.CFX
diagnostic_dest=/apps/oracle
db_recovery_file_dest_size=102400000M
db_block_size=????
compatible='11.2.0.0.0' ???/
#db_recovery_file_dest='+DG_BACKUP1';
log_archive_config='dg_config=(dvlp,dvlpb)' <== Primary,stby db_unique_name
fal_client='DVLP_SITEB' <== This is TNS Entry for STBY host
fal_server='DVLP_SITEA' <== This is TNS Entry for Primary Host
-- Get the password file from primary
copy password file from primary to standby and rename as per instance name
-- Now Start Standby in NOMOUNT Mode
export ORACLE_SID=dvlp1
export ORACLE_HOME=/apps/oracle/product/11.2.0/db
sqlplus / as sysdba
startup NOMOUNT
========================================================
Primary Host
========================================================
SQL> ALTER DATABASE FORCE LOGGING;
vi /apps/oracle/product/11.2.0/db/network/admin/tnsnames.ora
-- Add ------------
#TNS Entry to Reach Primary Host
DVLP_SITEA =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbaovm03)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = dbaovm04)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dvlp.oracle.cfx)
)
)
#TNS Entry to Reach Standby Host
DVLP_SITEB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbaovm05)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = dbaovm06)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dvlp1) ### Note this is SID
)
)
--- Transfer tnsnames.ora and password file to standby Host Location -----
From primary Node1# scp /apps/oracle/product/11.2.0/db/network/admin/tnsnames.ora dbaovm05:/apps/oracle/product/11.2.0/db/network/admin/
From primary Node2# scp /apps/oracle/product/11.2.0/db/network/admin/tnsnames.ora dbaovm06:/apps/oracle/product/11.2.0/db/network/admin/
From primary Node1# scp /apps/oracle/product/11.2.0/db/dbs/orapwdvlp1 dbaovm05:/apps/oracle/product/11.2.0/db/dbs/
From primary Node2# scp /apps/oracle/product/11.2.0/db/dbs/orapwdvlp1 dbaovm06:/apps/oracle/product/11.2.0/db/dbs/
=================================================
Test Connectivity from Both Hosts Standby/Primary
=================================================
-- test from Both Primary and Standby nodes ---------
tnsping dvlp_sitea
tnsping dvlp_siteb
sqlplus sys/pwd@dvlp_sitea as sysdba
sqlplus sys/pwd@dvlp_siteb as sysdba
===========================
Primary Host
===========================
1) Check
Make sure Standby is already started in the NOMOUNT Mode
2) from Primary Node
rman target sys/pwd@dvlp_sitea auxiliary sys/pwd@dvlp_siteb
run {
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate auxiliary channel stby type disk;
duplicate target database for standby from active database dorecover nofilenamecheck;
release channel prmy1;
release channel prmy2;
}
===========================
Stby Host
===========================
Goto Standby Database and try to shutdown and mount in standy mode
shutdown immediate
startup mount
alter database recover managed standby database disconnect from session;
Look for any errors and resolve
Once errors are resolved we need to change primary to transfer logs
===========================
Primary start log shipping
===========================
show parameter dest
-- WARNING MAke sure you made a copy of Pfile/Spfile
-- AND also check the old values of the following parameters before overwiting
ALTER SYSTEM SET log_archive_config='DG_CONFIG=(dvlp,dvlpb)'
ALTER SYSTEM SET log_archive_dest_2='service=dvlp_siteb ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=dvlpb' SCOPE=BOTH;
ALTER SYSTEM SET log_archive_dest_state_2='defer' SCOPE=BOTH;
ALTER SYSTEM SET fal_client='DVLP_SITEA' SCOPE=BOTH; <= tns of primary
ALTER SYSTEM SET fal_server='DVLP_SITEB' SCOPE=BOTH; <= tns of standby
alter system set log_archive_dest_state_2='defer'
/
alter system set log_archive_dest_state_2=enable
/
========== Issue/ Solution============
ARC7: Archivelog destination LOG_ARCHIVE_DEST_2 disabled: Data Guard configuration identifier mismatch
Archived Log entry 3016 added for thread 1 sequence 771 ID 0x573e0d58 dest 1:
Sun Mar 3
Error 16047 for archive log file 1 to 'dvlp_siteb'
Using STANDBY_ARCHIVE_DEST parameter default value as +DG_BACKUP1
Errors in file /apps/oracle/diag/rdbms/dvlp/dvlp1/trace/dvlp1_nsa2_19020.trc:
ORA-16047: DGID mismatch between destination setting and target database
Solution:
-- make sure you have set this on BOTH (PRIMARY & STBY )
alter system set log_archive_config='dg_config=(dvlp,dvlpb)' <== db_unique_names of prim,stby
/
===================================
Check Standby Files Both DB
===================================
-- Primary database check Standby logs if Not then add Some (log+1)
select thread#,count(*) from v$standby_log
group by thread#
/
select * from v$standby_log order by group#
/
-- Now check the Name and File Location as this info is not avail in v$standby_log
select group#,status,type,substr(member,1,50) mem, IS_RECOVERY_DEST_FILE
from v$logfile
where group# in ( select group# from v$standby_log)
/
-- drop all in standby
alter database drop standby logfile group NN;
-- readd
alter database add standby logfile thread 1 ('+DG_UNDO') size 50M;
startup mount
alter database recover managed standby database disconnect from session;
- access recovery rate - standby database
SELECT * FROM V$RECOVERY_PROGRESS;
OPEN_MODE
--------------------
READ WRITE
select max(sequence#) from gv$log_history;
select SEQUENCE# LAST_SEQ,(sysdate - FIRST_TIME)*24*60 Min_behind,
to_char(FIRST_TIME,'dd/mm/yy HH:MI:ss am') STANDBY_CURRENT_AS_OF,
COMPLETION_TIME RECEIVED_ON,applied
from V$ARCHIVED_LOG where sequence# = ( select max(SEQUENCE#)
from V$ARCHIVED_LOG where APPLIED='YES'
);
-- Stby
----- ---------
SELECT THREAD#,MAX(SEQUENCE#),to_char(max(first_time),'dd-Mon-yy HH:mi-AM') DateWhen,applied
FROM V$ARCHIVED_LOG
GROUP BY THREAD#,applied;
--- isses
> Standby got file but not applying
- chk other primry node tns entry standby might be waiting for other thread to come
- tnsping tns entries from other node
- try doing the following in Primary Side
alter system set log_archive_dest_state_2='defer';
alter system set log_archive_dest_state_2='enable';
===================== DONE ==================================
