1. Enabled forced logging on Primary DB
ALTER DATABASE FORCE LOGGING;
2. Have the Initialization Parameters set on Primary side
alter system set fal_client=PROD scope=spfile;
alter system set fal_server=PROD_sby scope=spfile;
alter system set log_archive_config='dg_config=(PROD,PROD_sby)' scope=spfile;
alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PROD'scope=spfile;
alter system set log_archive_dest_2='SERVICE=PROD_sby LGWR ASYNC NOAFFIRM DELAY=0 OPTIONAL REOPEN=15 MAX_FAILURE=10 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PROD_sby' scope=spfile;
alter system set log_archive_dest_state_1='ENABLE' scope=spfile;
alter system set log_archive_dest_state_2='ENABLE' scope=spfile;
alter system set standby_file_management='AUTO' scope=spfile;
alter system set log_file_name_convert='+DATA/PROD_SBY’,'+DATA/PROD' scope=spfile;
alter system set db_file_name_convert='+DATA/PROD_SBY','+DATA/PROD','+FRA/PROD_SBY','+FRA/PROD' scope=spfile;
3. Create Standby redo logs :
We have 5 redo logs on primary :
We need to create minimun 6 standby redo log :
GROUP# THREAD# SEQUENCE# ARC STATUS REDOLOG_FILE_NAME SIZE_MB
---------- ---------- ---------- --- ---------------- -------------------------------------------------- -----------------------------------------------
1 1 1478 NO CURRENT +DATA/PROD/ONLINELOG/group_1.266.877782429 400
1 1 1478 NO CURRENT +FRA/PROD/ONLINELOG/group_1.263.877782431 400
2 1 1475 YES INACTIVE +DATA/PROD/ONLINELOG/group_2.262.877782527 400
2 1 1475 YES INACTIVE +FRA/PROD/ONLINELOG/group_2.442.877782529 400
3 1 1474 YES INACTIVE +FRA/PROD/ONLINELOG/group_3.311.877782393 400
3 1 1474 YES INACTIVE +DATA/PROD/ONLINELOG/group_3.267.877782391 400
4 1 1476 YES INACTIVE +DATA/PROD/ONLINELOG/group_4.269.877782579 400
4 1 1476 YES INACTIVE +FRA/PROD/ONLINELOG/group_4.395.877782581 400
5 1 1477 YES INACTIVE +FRA/PROD/ONLINELOG/group_5.257.877782591 400
5 1 1477 YES INACTIVE +DATA/PROD/ONLINELOG/group_5.485.877782587 400
10 rows selected.
ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('+DATA','+FRA') SIZE 400M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('+DATA','+FRA') SIZE 400M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 8 ('+DATA','+FRA') SIZE 400M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 9 ('+DATA','+FRA') SIZE 400M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 10 ('+DATA','+FRA') SIZE 400M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 11 ('+DATA','+FRA') SIZE 400M;
4. Add TNS entries for standby and primary
make sure to have the tns entries for both standby and
primary in tnsnames.ora files
ON PRIMARY ADD STANDBY TNS
ENTRY
PROD_sby =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = prod_stby)(PORT
= 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = prod)
)
)
ON STANDBY ADD PRIMARY TNS
ENTRY
PROD.imagenet =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = PROD)(PORT
= 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = prod)
)
)
5. Copy the password file orapwPROD to standby as orapwPROD_sby
6. Create a standby controlfile and have it copied to standby
ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/stndby01.ctl';
7. Initiate backup of prod
8. Prepare a standby
create a pfile from spfile on primary
create pfile from spfile;
Copy the pfile to standby site and name it as initPROD_sby.ora
Modify necessary parameters as below accordingly
db_unique_name=PROD_sby
fal_client=PROD_sby
fal_server=PROD.imagenet
log_archive_config='dg_config=(PROD_sby,PROD)'
log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PROD_sby'
log_archive_dest_2='SERVICE=PROD LGWR ASYNC NOAFFIRM DELAY=0 OPTIONAL REOPEN=15 MAX_FAILURE=10 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PROD'
log_archive_dest_state_1='ENABLE'
log_archive_dest_state_2='ENABLE'
standby_file_management='AUTO'
log_file_name_convert='+DATA/PROD','+DATA/PROD_SBY'
db_file_name_convert='+DATA/PROD','+DATA/PROD_SBY','+FRA/PROD','+FRA/PROD_SBY'
9. Once done have the instance in nomount state
10. Restore control file from the copy created in step 6
11. Once backups are completed on prod,have the backup pieces available on standby
12. Start with Restore of the database
13. Once restore completed,create standby redo logs on standby site as well
ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('+DATA','+FRA') SIZE 400M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('+DATA','+FRA') SIZE 400M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 8 ('+DATA','+FRA') SIZE 400M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 9 ('+DATA','+FRA') SIZE 400M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 10 ('+DATA','+FRA') SIZE 400M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 11 ('+DATA','+FRA') SIZE 400M;
14. Once done,verify once all the parameters are set correct on both the sites
Also ensure tnsping is fine to both the servers
show parameter log_archive_config
show parameter fal
show parameter log_archive_dest_2
show parameter log_archive_dest_state_2
show parameter log_file_name;
show parameter db_file_name;
15. One everything good.
Put the Standby database into managed standby mode:
shutdown immediate;
startup nomount;
alter database mount standby database;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
16. Verify log shipping is fine:
On primary :
alter system switch logfile;
select max(sequence#) from V$log_history;
on standby :
select max(sequence#) from V$archived_log where applied='YES';
SELECT PROCESS, STATUS,SEQUENCE#,BLOCK#,BLOCKS, DELAY_MINS FROM V$MANAGED_STANDBY;
Monitor the both the alert logs for any error :
On primary query v$acrhive_dest_status for errors on the standby dest
DATAGUARD BROKER CONFIGURATION
ON PRIMARY :
alter system set
dg_broker_config_file1='/u01/app/oracle/product/12.1.0/db_1/dbs/PROD_01.dat'
scope=spfile;
alter system set
dg_broker_config_file2='/u01/app/oracle/product/12.1.0/db_1/dbs/PROD_02.dat'
scope=spfile;
alter system set
dg_broker_start=true scope=spfile;
EDIT LISTENER FILE :
SID_LIST_LISTENER=(SID_LIST=(SID_DESC=(SID_NAME=PROD)(GLOBAL_DBNAME=PROD_DGMGRL)(ORACLE_HOME=/u01/app/oracle/product/12.1.0/db_1)))
ON STANDBY :
alter system set
dg_broker_config_file1='/u01/app/oracle/product/12.1.0/db_1/dbs/PROD_sby_01.dat'
scope=spfile;
alter system set
dg_broker_config_file2='/u01/app/oracle/product/12.1.0/db_1/dbs/PROD_sby02.dat'
scope=spfile;
alter system set
dg_broker_start=true scope=spfile;
EDIT LISTENER FILE :
SID_LIST_LISTENER=(SID_LIST=(SID_DESC=(SID_NAME=PROD_sby)(GLOBAL_DBNAME=PROD_sby_DGMGRL)(ORACLE_HOME=/u01/app/oracle/product/12.1.0/db_1)))
We need to reload the
listeners on primary and standby site to reflect the changes
Now on primary connect to
dgmgrl:
1. launch dgmgrl from primary
db
>dgmgrl
2. connect
connect sys/pwd
3. >CREATE
CONFIGURATION PROD_DGMGRL AS PRIMARY DATABASE IS PROD CONNECT IDENTIFIER IS PROD;
4. >ADD DATABASE PROD_sby
AS CONNECT IDENTIFIER IS PROD_sby MAINTAINED AS PHYSICAL;
5. show configuration;
6. SHOW DATABASE VERBOSE PROD_sby;
7. SHOW DATABASE VERBOSE PROD;
8. Enable BROKER
CONFIGURATION
>ENABLE CONFIGURATION;
9. Enable the standby DB
ENABLE DATABASE PROD_sby
10. Verify the configurations,show
configuration;
ex : Configuration Status
should be success
>show configuration;
Configuration - dgbrkr
Protection Mode: MaxPerformance
Databases:
db1 - Primary database
db2 - Physical standby database
Fast-Start Failover:
DISABLED
Configuration Status:
SUCCESS
