Data Guard Physical Standby Setup

09:50 Feroz Khan 0 Comments


Data Guard Physical Standby Setup


Details:

PRIMARY DB:                                                                         STANDBY DB:                                                                                              
NAME-DB1 (uniqname=PROD)                                            NAME-DB1 (uniqname=PROD_sby)                                                                              
STORAGE – ASM +DATA and +FRA                                    STORAGE – ASM +DATA and +FRA                                
HOST - PROD                                                                          HOST – prod_stby                                                                     

    

Steps:


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




You Might Also Like

0 comments:

Subscribe to: Post Comments (Atom)