Steps to configure Oracle 11g Data Guard Physical Standby – Active Data Guard Part-I

Here in this article I am going to cover steps by step approach to configure Oracle 11g Data Guard Physical Standby.

In my case, Ingredients to simulate Physical Standby data guard environment are as below:

  • 2 VM’s, Primary and DR with enough CPU and RAM in order to run oracle database.
  • Primary server configuration
    • CentOS 6.5
    • Server name: pr
    • IP: 192.168.17.131
    • Oracle 11g software plus oracle instance.
    • Oracle SID/Global_name: RTS
    • Oracle db_unique_name: RTS
  • Secondary server configuration
    • CentOS 6.5
    • Server name: dr
    • IP: 192.168.17.132
    • Oracle 11g software only.
    • Oracle SID/Global name: RTS
    • Oracle db_unique_name: RTSDR

Note:

  • Oracle version on Primary and secondary should be identical. i.e. In my case it’s 11.2.0.1.0
  • db_unique_name on standby and primary database should be different in order to used in DG_CONFIG setting of the LOG_ARCHIVE_CONFIG parameter.
  • Primary and DR server should ping each other by IP as well as its server name. In order to ping with its server name, edit /etc/hosts file accordingly.

 

Primary Server Configurations:

<Step – 1 >

Enable Archive log:
Primary database is in No Archive Mode, Enable it:

 SQL> archive log list
 Database log mode No Archive Mode
 Automatic archival Disabled
 Archive destination USE_DB_RECOVERY_FILE_DEST
 Oldest online log sequence 1
 Current log sequence 3
 SQL> SHUTDOWN IMMEDIATE;
 SQL> STARTUP MOUNT;
 SQL> ALTER DATABASE ARCHIVELOG;
 SQL> ALTER DATABASE OPEN;
 SQL> archive log list;
 Database log mode Archive Mode
 Automatic archival Enabled
 Archive destination USE_DB_RECOVERY_FILE_DEST
 Oldest online log sequence 1
 Next log sequence to archive 3
 Current log sequence 3

<Step – 2 >

Enable force logging with the help of following SQL command:

SQL> ALTER DATABASE FORCE LOGGING;

<Step – 3 >

Verify initialization parameters db_name and db_unique_name on primary, In my case those are set to: RTS

 SQL> show parameter db_name
 NAME TYPE VALUE
 ------------------------------------ ----------- ------------------------------
 db_name string RTS
 SQL> show parameter db_unique_name
 NAME TYPE VALUE
 ------------------------------------ ----------- ------------------------------
 db_unique_name string RTS

<Step – 4 >

Issue following command to set LOG_ARCHIVE_CONFIG parameter for data guard config.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(RTS,RTSDR)';

Ensure your above changes as below:

 SQL> show parameter LOG_ARCHIVE_CONFIG
 NAME TYPE VALUE
 ------------------------------------ ----------- ------------------------------
 log_archive_config string DG_CONFIG=(RTS,RTSDR)

<Step – 5 >

Issue following to set LOG_ARCHIVE_DEST_2, In my case i have used flash recovery area for remote archive log destination.

Note: Service and DB_UNIQUE_NAME reference the remote standby location.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=RTSDR NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=RTSDR';

Ensure your above changes as below:

SQL> show parameter LOG_ARCHIVE_DEST_2
 NAME TYPE VALUE
 ------------------------------------ ----------- ------------------------------
 log_archive_dest_2 string SERVICE=rtsdr NOAFFIRM ASYNC V
 ALID_FOR=(ONLINE_LOGFILES,PRIM
 ARY_ROLE) DB_UNIQUE_NAME=rtsdr

Enable LOG_ARCHIVE_DEST_2:

SQL> alter system set log_archive_dest_state_2=enable;
SQL> show parameter log_archive_dest_state_2
 NAME TYPE VALUE
 ------------------------------------ ----------- ------------------------------
 log_archive_dest_state_2 string ENABLE

<Step – 6 > 

Set log_archive_format parameter as below:

SQL> alter system set log_archive_format='%t_%s_%r.arc' scope=spfile;
SQL> show parameter log_archive_format
 NAME TYPE VALUE
 ------------------------------------ ----------- ------------------------------
 log_archive_format string %t_%s_%r.arc

Set log_archive_max_processes parameter to 30:

SQL> alter system set log_archive_max_processes=30;
SQL> show parameter log_archive_max_processes
 NAME TYPE VALUE
 ------------------------------------ ----------- ------------------------------
 log_archive_max_processes integer 30

Set remote_login_passwordfile parameter to exclusive:

 SQL> alter system set remote_login_passwordfile=exclusive scope=spfile;
 SQL> show parameter remote_login_passwordfile
 NAME TYPE VALUE
 ------------------------------------ ----------- ------------------------------
 remote_login_passwordfile string EXCLUSIVE

<Step – 7 > 

Set fal_server and fal_client parameter for primary database:

SQL> alter system set fal_server=RTSDR;
SQL> show parameter fal_server
 NAME TYPE VALUE
 ------------------------------------ ----------- ------------------------------
 fal_server string RTSDR
SQL> alter system set fal_client='RTS';
 SQL> show parameter fal_client
 NAME TYPE VALUE
 ------------------------------------ ----------- ------------------------------
 fal_client string RTS

Set standby_file_management parameter to auto:

 SQL> alter system set standby_file_management=auto;
 SQL> show parameter standby_file_management
 NAME TYPE VALUE
 ------------------------------------ ----------- ------------------------------
 standby_file_management string AUTO

<Step – 8 > 

Configure listener file and copy it to DR @ location: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin
Listener file:

SID_LIST_LISTENER =
 (SID_LIST =
 (SID_DESC =
 (GLOBAL_DBNAME = RTS)
 (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
 (SID_NAME = RTS)
 ) )
 LISTENER =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = pr)(PORT = 1521))
 )
 ADR_BASE_LISTENER = /u01/app/oracle

<Step – 9 > 

Entries for the primary and standby databases are needed in the “$ORACLE_HOME/network/admin/tnsnames.ora” files on both the servers.

 RTS =
 (DESCRIPTION =
 (ADDRESS_LIST =
 (ADDRESS = (PROTOCOL = TCP)(HOST = pr)(PORT = 1521))
 )
 (CONNECT_DATA =
 (SERVICE_NAME = RTS)
 (GLOBAL_NAME = RTS)
 (UR=A)
 ) )
 RTSDR =
 (DESCRIPTION =
 (ADDRESS_LIST =
 (ADDRESS = (PROTOCOL = TCP)(HOST = dr)(PORT = 1521))
 )
 (CONNECT_DATA =
 (SERVICE_NAME = RTS)
 (GLOBAL_NAME = RTS)
 (UR=A)
 ) )

Ensure your above configuration by TNSPing utility on Primary and standby:

 [oracle@pr admin]$ tnsping RTS
 TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 03-OCT-2014 06:04:09
 Copyright (c) 1997, 2009, Oracle. All rights reserved.
 Used parameter files:
 /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora
 Used TNSNAMES adapter to resolve the alias
 Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = pr)(PORT = 1521))) (CONNECT_ DATA = (SERVICE_NAME = rts) (GLOBAL_NAME = RTS)))
 OK (10 msec)
 [oracle@pr admin]$ tnsping RTSDR
 TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 03-OCT-2014 06:04:15
 Copyright (c) 1997, 2009, Oracle. All rights reserved.
 Used parameter files:
 /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora
 Used TNSNAMES adapter to resolve the alias
 Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = dr)(PORT = 1521))) (CONNECT_ DATA = (SERVICE_NAME = RTS) (GLOBAL_NAME = RTS)))
 OK (20 msec)

<Step – 10 > 

Backup primary database via RMAN backup utility:

 [oracle@pr admin]$ rman target /
 Recovery Manager: Release 11.2.0.1.0 - Production on Sat Oct 4 02:02:11 2014
 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
 connected to target database: RTS (DBID=1421312347)
 RMAN> backup database plus archivelog;

Note: Backup location: Flash Recovery Area.

<Step – 11> 

Create Standby control file.

SQL> alter database create standby controlfile as '/u01/bkup/stndbyctrl.ctl';

<Step – 12>

Create pfile from spfile:

SQL> create pfile='/u01/bkup/initRTS.ora' from spfile;

<Step – 13>

After creating parameter file as above, edit following changes in newly created pfile:

 *.db_unique_name='RTSDR'
 *.fal_server='RTS';
 *.log_archive_dest_2='SERVICE=RTS ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=RTS'

<Step – 14>

Copy parameter file to DR @ location: /u01/app/oracle/product/11.2.0/dbhome_1/dbs/

 [oracle@pr bkup]$ scp initRTS.ora oracle@192.168.17.132:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/

Copy standby control file to DR @ location: /u01/app/oracle/oradata/RTS/control01.ctl & /u01/app/oracle/flash_recovery_area/RTS/control02.ctl

 [oracle@pr bkup]$ scp stndbyctrl.ctl oracle@192.168.17.132:/u01/app/oracle/oradata/RTS/control01.ctl
 [oracle@pr bkup]$ scp stndbyctrl.ctl oracle@192.168.17.132:/u01/app/oracle/flash_recovery_area/RTS/control02.ctl

Copy password file to DR @ location: /u01/app/oracle/product/11.2.0/dbhome_1/dbs/

 [oracle@pr ~]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/dbs/
 [oracle@pr dbs]$ scp orapwRTS oracle@192.168.17.132:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/

Copy Listener file to DR @ location: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin

 [oracle@pr bkup]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/network/admin
 [oracle@pr admin]$ scp listener.ora oracle@192.168.17.132:/u01/app/oracle/product/11.2.0/dbhome_1/network/admin

Edit DR database listener file with host name. ( i.e. (HOST = dr))

And finally copy RMAN backup to DR @ flash recovery area.

 [oracle@pr ~]$ cd /u01/app/oracle/flash_recovery_area/
 [oracle@pr flash_recovery_area]$ scp -r RTS oracle@192.168.17.132:/u01/app/oracle/flash_recovery_area/

Standby/DR Server Configurations:

<Step – 15>

Startup standby database in mount state:
Set following environment variable as oracle user OR edit those in /home/oracle/.bash_profile in order to set it for every time while oracle user logged in:

 export ORACLE_SID=RTS
 export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
 export PATH=$PATH:/u01/app/oracle/product/11.2.0/dbhome_1/bin
 [oracle@DR ~]$ mkdir -p /u01/app/oracle/admin/RTS/adump

 SQL> sqlplus / as sysdba
 SQL> startup mount
 ORACLE instance started.
 Total System Global Area 972898304 bytes
 Fixed Size 2219272 bytes
 Variable Size 566231800 bytes
 Database Buffers 398458880 bytes
 Redo Buffers 5988352 bytes
 Database mounted.

<Step – 16>

Create spfile from pfile:

 SQL> create spfile from pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initRTS.ora';

<Step – 17>

Start listener on standby:

[oracle@dr ~]$ lsnrctl start

<Step – 18>

Create follwoing directories on DR server, in case those are not available:
/u01/app/oracle/admin/RTS/adump
/u01/app/oracle/flash_recovery_area

<Step – 19>

Restore and recover database @ DR with RMAN backup utility.

RMAN> list backup of database summary;
 List of Backups
 ===============
 Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
 ------- -- -- - ----------- --------------- ------- ------- ---------- ---
 6 B F A DISK 05-OCT-14 1 1 NO TAG20141005T065604
RMAN> restore database;
RMAN> recover database;

Note: Recovery of database would be failed with RMAN-06054 error, We can ignore it because RMAN will ask for unknown archive log ( i.e. next archive log sequence, i.e. 10 ) who is not also available on Primary database.

Error log: RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 10 and starting SCN of 1009554

<Step – 20>

Create standby redolog file to Primary and DR for the user of switch over, It should be match the configuration of the primary server.
Note: Create one additional standby redolog file on both.

 SQL> sqlplus / as sysdba
 SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='MANUAL' scope=spfile;
 SQL> ALTER DATABASE ADD STANDBY LOGFILE('/u01/app/oracle/oradata/RTS/stndby1.log') size 51M;
 SQL> ALTER DATABASE ADD STANDBY LOGFILE('/u01/app/oracle/oradata/RTS/stndby2.log') size 51M;
 SQL> ALTER DATABASE ADD STANDBY LOGFILE('/u01/app/oracle/oradata/RTS/stndby3.log') size 51M;
 SQL> ALTER DATABASE ADD STANDBY LOGFILE('/u01/app/oracle/oradata/RTS/stndby4.log') size 51M;
 SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='AUTO' scope=spfile;

<Step – 21>

Start apply process @ DR.

 SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE nodelay DISCONNECT FROM SESSION;

In case of you want to cancel apply process, issue following command:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

<Step – 22>

After graceful completion of above apply process, verify archive logs on Primary as well as DR.
On Primary: 

 SQL> archive log list;
 Database log mode Archive Mode
 Automatic archival Enabled
 Archive destination USE_DB_RECOVERY_FILE_DEST
 Oldest online log sequence 8
 Next log sequence to archive 10
 Current log sequence 10

On Standby:

 SQL> archive log list;
 Database log mode Archive Mode
 Automatic archival Enabled
 Archive destination USE_DB_RECOVERY_FILE_DEST
 Oldest online log sequence 8
 Next log sequence to archive 0
 Current log sequence 10

By above result, archive logs on both the databases are in sync now.

To test your data guard configuration, generate archive logs on primary site and verify it on DR site:

SQL> alter system switch logfile; //Give this command multiple times for testing.

On Primary:

 SQL> archive log list;
 Database log mode Archive Mode
 Automatic archival Enabled
 Archive destination USE_DB_RECOVERY_FILE_DEST
 Oldest online log sequence 19
 Next log sequence to archive 21
 Current log sequence 21

OR

SQL> select max(sequence#) from v$archived_log;
 MAX(SEQUENCE#)
 --------------
 20

On Standby:

 SQL> archive log list;
 Database log mode Archive Mode
 Automatic archival Enabled
 Archive destination USE_DB_RECOVERY_FILE_DEST
 Oldest online log sequence 19
 Next log sequence to archive 0
 Current log sequence 21

OR

 SQL> select max(sequence#) from v$archived_log;
 MAX(SEQUENCE#)
 --------------
 20

Cheers!! Our Data Guard configuration has been configured successfully.

Verify database roles by below mentioned SQL query:

On Primary:

 SQL> select name,open_mode,database_role from v$database;
 NAME OPEN_MODE DATABASE_ROLE
 --------- -------------------- ----------------
 RTS READ WRITE PRIMARY

On DR:

 SQL> select name,open_mode,database_role from v$database;
 NAME OPEN_MODE DATABASE_ROLE
 --------- -------------------- ----------------
 RTS MOUNTED PHYSICAL STANDBY

You can also verify total number of log sequence generated and applied on DR site, by below SQL query:

 SQL> ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';
 SQL> SELECT sequence#, first_time, next_time, applied
 FROM v$archived_log
 ORDER BY sequence#;
 SEQUENCE# FIRST_TIM NEXT_TIME APPLIED
 ---------- --------- --------- ---------
 3 04-OCT-14 04-OCT-14 YES
 4 04-OCT-14 04-OCT-14 YES
 5 04-OCT-14 04-OCT-14 YES
 6 04-OCT-14 05-OCT-14 YES
 7 05-OCT-14 05-OCT-14 YES
 8 05-OCT-14 05-OCT-14 YES
 9 05-OCT-14 05-OCT-14 YES
 10 05-OCT-14 05-OCT-14 YES
 11 05-OCT-14 05-OCT-14 YES
 12 05-OCT-14 05-OCT-14 YES
 13 05-OCT-14 05-OCT-14 YES
 14 05-OCT-14 05-OCT-14 YES
 15 05-OCT-14 05-OCT-14 YES
 16 05-OCT-14 05-OCT-14 YES
 17 05-OCT-14 05-OCT-14 YES
 18 05-OCT-14 05-OCT-14 YES
 19 05-OCT-14 05-OCT-14 YES
 20 05-OCT-14 05-OCT-14 YES

In case of you are facing any kind of error than following SQL query will help you to diagnose it.

 SQL> select dest_name,status,error from v$archive_dest where dest_name='LOG_ARCHIVE_DEST_2';
 DEST_NAME
 --------------------------------------------------------------------------------
 STATUS ERROR
 --------- -----------------------------------------------------------------
 LOG_ARCHIVE_DEST_2
 VALID

Note: LOG_ARCHIVE_DEST_2 should be VALID in order to continue Data Guard sync.

OR

SQL> select message from v$dataguard_status;

Note: This command will give you appropriate message about the dataguard current status.

By default, for a newly created standby database, the primary database is in maximum performance mode.

Protection Mode:
Default protection mode of newly configured standby database would be maximum performance mode.
There are 3 protection modes: Maximum Availability, Maximum Performance and Maximum Protection. for more information, click me.

By above mentioned steps you can configure Data Guard in your environment, Stay tune with my next article about Data Guard Switch Over and Switch Back steps.

52 thoughts on “Steps to configure Oracle 11g Data Guard Physical Standby – Active Data Guard Part-I”

  1. Hi Jignesh,

    I am stuck on step 17…trying to start dr’s listener….

    i am getting the following error:

    LSNRCTL> start
    Starting /u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait…

    TNSLSNR for Linux: Version 11.2.0.1.0 – Production
    System parameter file is /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
    Log messages written to /u01/app/oracle/diag/tnslsnr/sg2/listener/alert/log.xml
    Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sg2)(PORT=1521)))
    TNS-01201: Listener cannot find executable /u01/app/oracle/product/11.2.0/dbhome_1/bin/oracle for SID RTS

    • Hello, Thank you for writing.
      Please ensure listener.ora and Tnsnames.ora file entries according to steps given.
      Also verify tnsping from both the server to both the databases.
      Step no 8 and 9 and 13.

      Please have a close look of TNS error: TNS-01201: Listener cannot find executable /u01/app/oracle/product/11.2.0/dbhome_1/bin/oracle for SID RTS
      Somewhere, You have set SID name to RTS. Please change it to your given SID name..

  2. could you please explain what is (UR=A) entry in tnslistner.ora file (step – 9).

  3. Dear Jignesh

    I found a very informative article that is named in the subject line from your website and I have followed it to configure Data Guard on my two oracle 11g servers. I have succeeded with all the steps until I get stuck when I have to configure the listener.ora file from the Production and copying it to the DR. Can you please give me some more details from there on how to configure the listeners and the tnsnames.ora file?

    Should the configured listener.ora file still exist in the production server? that is, should the two servers have the same identical listener.ora file?

    The same confusion happens with the configurations of the tnsnames.ora files on both servers, it is not clear how to configure them.

    Lastly, I have seen you have used pr and dr in the article as examples of hosts when configuring the above files in question. should I assume that pr is the production server while dr is the disaster recovery server?

    One more thing if you can clarify the ORACLE_HOME directories per which server that need to be configured in the listener.ora files and tnsnames.ora files

    kind regards

    Pakalitha

    • Thanks Pakalitha for writing…
      Good to know that you trying to configure Data Guard.

      You can directly configure listener.ora and tnsnames.ora files by editing them manually. OR you can configure it through “Net Configuration Assistance” OR “Net Manager” oracle utility.

      Listener.ora & tnsnames.ora files should exist on both the server.
      In case of listener file, Whole listener.ora file can be identical on both the server instead of HOST entry. You need to specify host entry.
      In case of my testing environment:
      PR Listener file:
      ——————————
      SID_LIST_LISTENER =
      (SID_LIST =
      (SID_DESC =
      (GLOBAL_DBNAME = RTS)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = RTS)
      )
      )
      LISTENER =
      (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = PR)(PORT = 1521))
      )
      ADR_BASE_LISTENER = /u01/app/oracle
      —-End———————–

      DR Listener file:
      ——————————
      SID_LIST_LISTENER =
      (SID_LIST =
      (SID_DESC =
      (GLOBAL_DBNAME = RTS)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = RTS)
      )
      )
      LISTENER =
      (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = DR)(PORT = 1521))
      )
      ADR_BASE_LISTENER = /u01/app/oracle
      —-End———————–

      In case of tnsnames.ora file, yeah it is identical over both the nodes.
      PR & DR tnsnames.ora file:
      ——————————
      RTS =
      (DESCRIPTION =
      (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = PR)(PORT = 1521))
      )
      (CONNECT_DATA =
      (SERVICE_NAME = RTS)
      (GLOBAL_NAME = RTS)
      (UR=A)
      )
      )
      RTSDR =
      (DESCRIPTION =
      (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = DR)(PORT = 1521))
      )
      (CONNECT_DATA =
      (SERVICE_NAME = RTS)
      (GLOBAL_NAME = RTS)
      (UR=A)
      ) )
      —-End———————–

      Yes you are right, You can assume PR as a Production Server and DR as a disaster recovery server.

      On following dir, you need to configured listener.ora and tnsnames.ora files:
      /u01/app/oracle/product/11.2.0/dbhome_1/network/admin

      i.e. ORACLE_HOME: /u01/app/oracle/product/11.2.0/dbhome_1/

      Hope this much is helpful for you.

      Stay Tune. 🙂

  4. I am on the tnsnames.ora files now, hope will be easy then
    my tnsping commands are not working from the production to the dr, it gives this error, [oracle@srvoradb ~]$ tnsping RTGSDR

    TNS Ping Utility for Linux: Version 11.2.0.1.0 – Production on 28-AUG-2015 11:09:00

    Copyright (c) 1997, 2009, Oracle. All rights reserved.

    Used parameter files:
    /oracle/111/network/admin/sqlnet.ora

    Used TNSNAMES adapter to resolve the alias
    Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = srvoradbbackup.centralbank.org.ls)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = RTGS) (GLOBAL_NAME = RTGS) (UR=A)))
    TNS-12543: TNS:destination host unreachable
    RTGS =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = srvoradb)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVICE_NAME = RTGS)
    (GLOBAL_NAME = RTGS)
    (UR=A)
    ) )
    RTGSDR =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = srvoradbbackup)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVICE_NAME = RTGS)
    (GLOBAL_NAME = RTGS)
    (UR=A)
    ) )
    my tnsnames.ora file is the above
    [oracle@srvoradb ~]$ tnsping RTGS

    TNS Ping Utility for Linux: Version 11.2.0.1.0 – Production on 28-AUG-2015 11:14:14

    Copyright (c) 1997, 2009, Oracle. All rights reserved.

    Used parameter files:
    /oracle/111/network/admin/sqlnet.ora

    Used TNSNAMES adapter to resolve the alias
    Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = srvoradb.centralbank.org.ls)(PORT = 1521))
    TNS-12533: TNS:illegal ADDRESS parameters

    • Kindly verify the network connection between PR & DR server. then Verify listener file entries like Oracle_home, and Oracle base and try to TNSPING again.

  5. The tnsping succeeded after your intervention, thank you very much for your great help, I learnt quite a lot. much appreciated.

  6. thank you very much for the tutorial
    I followed your steps but it’s not working
    when I try select error from v$archive_dest there is no errors but when select * i got the schedule for stndby db (pending) net_timout (300)
    the both vm can ping each other and tnsping is okay

  7. Hi Jignesh,

    How to configure the archivelog deletion policy using RMAN.After configuring dataguard i’m facing space issue for the archive location.

    After research,i’ve configured like this

    In Primary DB,
    CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;

    and in Standby DB,
    CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;

    Will this delete the log file right after it is applied in standby.Or will it wait for the space to fillup and once FRA is full,then only it’ll free up the space?.How does this thing works.

    My intention is to delete the applied archive logs from primary and standby once it’s applied.

    If you have any references for this please share it.

    and by the way i’m using Oracle 11g Release2 on Linux machine.

    Thank You.,
    Vimal.P.S

    • Thanks Vimal for writing.

      I haven’t went through the scenario you have mentioned, It need to test on test environment. I will get beck to you on this.

  8. Thank you so much for the post…
    we are trying to configure the AGD.. is it possible to take backup from the standby server..we dont want to use scp or copy the backup files from the primary to standby by server..
    Thanks

  9. thank you so much for the post. currently i’m trying to learn oracle data guard 11g r2 on my windows server.
    I have a problem. I already follow your instruction, but on my standby server, when i execute “select max(sequence#) from v$archived_log”, the result is blank or empty :

    SQL> select max(sequence#) from v$archived_log;

    MAX(SEQUENCE#)
    ————–

    Does this means my standby and primary database are not sync?? and what do i miss?

    Thanks,

  10. I’m trying to replicate this tutorial on windows machines.

    At Startup standby database in mount state, how can you initiate ‘sqlplus / as sysdba’ if on DR server there is only oracle software installed without any database created ?

    Even if I create listener service before that, if the database service it’s not started, the sqlplus command will generate ‘ORA-12514: TNS:listener does not currently know of service requested in connect
    descriptor’

    Do you have any clue about this?

    Thank you.

    • Thank you Mo for writing!!
      I can initiate ‘sqlplus / as sysdba’ on DR server because, I have created pfile and standby control file.
      Kindly verify step no: 9,11,12,13 and 14 and than try startup standby database in mount state.
      Stay Tune. 🙂

  11. thank you so much for the awesome post and i am new to data guard, i go through ur post and i successfully completed with Data guard…but after i got Error 12545 connecting to ORCL for fetching gap sequence can u suggest on this???

  12. Dear sir,

    when a programmer creates application to retrieve the data from a database , he uses connecting string by providing ipaddress/hostname , serviceid, username and password.

    Here my question is if one server crashed how the application will run without donwtime?

    is there any way to continue and connect the other server immediately ?

    or application will stop and again we need to modify connecting string details.

    • Dear Aslam, thanks for writing.
      This is Primary-Standby dataguard configuration, it means your application connected to primary database and transactions are being replicated to standby through archive logs.
      If your primary database unavilable(crashed) then your application will be stopped completely, in this case you need manual intervention to point your application to standby database server. Please dont forget to activate standby database as primary database.

  13. thank you for the tutorial, but I have some issues here.

    I carefully follow up your instruction.
    and here are the probem:
    1. My database can not be opened in DR site
    Database mounted.
    ORA-10458: standby database requires recovery
    ORA-01152: file 1 was not restored from a sufficiently old backup
    ORA-01110: data file 1: ‘/u01/app/oracle/oradata/orcl/system01.dbf’

    2. when I start apply process, it won’t sync to Primary site

    SQL> select message from v$dataguard_status;

    MESSAGE
    ——————————————————————————–
    ARC0: Archival started
    Managed Standby Recovery starting Real Time Apply
    Media Recovery Waiting for thread 1 sequence 9
    ARC1: Archival started
    ARC2: Archival started
    ARC3: Archival started
    ARC4: Archival started
    ARC5: Archival started
    ARC6: Archival started
    ARC7: Archival started
    ARC8: Archival started

    MESSAGE
    ——————————————————————————–
    ARC9: Archival started
    ARCa: Archival started
    ARCb: Archival started
    ARCc: Archival started
    ARCd: Archival started
    ARCe: Archival started
    ARCf: Archival started
    ARCg: Archival started
    ARCh: Archival started
    ARCi: Archival started
    ARCj: Archival started

    MESSAGE
    ——————————————————————————–
    ARCk: Archival started
    ARCl: Archival started
    ARCm: Archival started
    ARCn: Archival started
    ARCo: Archival started
    ARCp: Archival started
    ARCq: Archival started
    ARCr: Archival started
    ARCs: Archival started
    ARC1: Becoming the ‘no FAL’ ARCH
    ARC3: Becoming the heartbeat ARCH

    MESSAGE
    ——————————————————————————–
    ARC3: Becoming the active heartbeat ARCH
    Error 1017 received logging on to the standby
    FAL[client, USER]: Error 16191 connecting to orcl for fetching gap sequence
    Error 1017 received logging on to the standby
    FAL[client, ARC2]: Error 16191 connecting to orcl for fetching gap sequence
    ARCt: Archival started
    Wait timeout: thread 1 sequence 9
    Managed Standby Recovery not using Real Time Apply
    Attempt to start background Managed Standby Recovery process
    MRP0: Background Managed Standby Recovery process started
    Managed Standby Recovery not using Real Time Apply

    MESSAGE
    ——————————————————————————–
    Clearing online redo logfile 1 /u01/app/oracle/oradata/orcl/redo01.log
    Clearing online redo logfile 1 complete
    Clearing online redo logfile 2 /u01/app/oracle/oradata/orcl/redo02.log
    Clearing online redo logfile 2 complete
    Clearing online redo logfile 3 /u01/app/oracle/oradata/orcl/redo03.log
    Clearing online redo logfile 3 complete
    Media Recovery Waiting for thread 1 sequence 9
    Error 1017 received logging on to the standby
    FAL[client, USER]: Error 16191 connecting to orcl for fetching gap sequence
    MRP0: Background Media Recovery cancelled with status 16037
    MRP0: Background Media Recovery process shutdown

    MESSAGE
    ——————————————————————————–
    Managed Standby Recovery Canceled
    Attempt to start background Managed Standby Recovery process
    MRP0: Background Managed Standby Recovery process started
    Managed Standby Recovery not using Real Time Apply
    Clearing online redo logfile 1 /u01/app/oracle/oradata/orcl/redo01.log
    Clearing online redo logfile 1 complete
    Clearing online redo logfile 2 /u01/app/oracle/oradata/orcl/redo02.log
    Clearing online redo logfile 2 complete
    Clearing online redo logfile 3 /u01/app/oracle/oradata/orcl/redo03.log
    Clearing online redo logfile 3 complete
    Media Recovery Waiting for thread 1 sequence 9

    MESSAGE
    ——————————————————————————–
    Error 1017 received logging on to the standby
    FAL[client, USER]: Error 16191 connecting to orcl for fetching gap sequence

    3. when I tried to connect RMAN in DR site
    rman target sys/123456@stdby

    Recovery Manager: Release 11.2.0.3.0 – Production on Wed Nov 9 07:44:41 2016

    Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

    connected to target database (not started)

    RMAN>

    —–but when I using this command, in DR site as well
    rman target /

    Recovery Manager: Release 11.2.0.3.0 – Production on Wed Nov 9 07:45:27 2016

    Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

    connected to target database: ORCL (DBID=1455168142, not open)

    RMAN>

    it can connect to DR database (started but not opened yet)

    kindly need your help,

    Thank you.

    • Thank you Gustian for writing!
      1. My database can not be opened in DR site >>> It won’t be open in READ WRITE mode, it should be in MOUNTED(in order to apply archive logs) or READ ONLY mode(for query offloading for reporting).
      2. when I start apply process, it won’t sync to Primary site >>> Kindly ensure all the steps mentioned in this article.
      3. >>> Kindly ensure your connection string in tnsnames.ora file and copy password file from primary to standby.

  14. Hi to all of you !
    i want install the ORACLE VM VIRTUAL-BOX with vagrant for linkage of KoBo toolbox as offline
    so i don’t run this process step by step by commend line and other step please help me about these running of virtual box with vagrant for set up the KoBo toolbox as offline
    thanks
    aminyar

  15. Thanks a lot. its working but sometime its not a sync to standby database automatically then how to sync manually ??

    • There is two methods, one is to take incremental backup and shift to standby database and second is to copy your archive logs to standby database manually.

  16. hi,

    my archives are not appliying and below is the error

    select dest_name,status,error from v$archive_dest where dest_name=’LOG_ARCHIVE_DEST_2′;

    DEST_NAME
    ——————————————————————————–
    STATUS ERROR
    ——— —————————————————————–
    LOG_ARCHIVE_DEST_2
    ERROR ORA-12514: TNS:listener does not currently know of service

  17. Hello

    My sequence # does not increase on standby server

    SQL> archive log list;
    Database log mode Archive Mode
    Automatic archival Enabled
    Archive destination USE_DB_RECOVERY_FILE_DEST
    Oldest online log sequence 0
    Next log sequence to archive 0
    Current log sequence 0

    • Troubleshoot configuration with given help.
      Check:
      SQL> select message from v$dataguard_status;
      and
      SQL> select dest_name,status,error from v$archive_dest where dest_name=’LOG_ARCHIVE_DEST_2′;
      Note: in my case, LOG_ARCHIVE_DEST_2 is configured.

    • Can you check your ORACLE_SID on your standby? I think it has to be the same as your production. If not, that is where your problem is I think.

    • On primary:
      Step 1: ALTER DATABASE NO FORCE LOGGING;
      Step 2: alter system set log_archive_dest_2=” scope=both;
      Step 3: alter system set log_archive_dest_state_2=disable scope=both;
      Step 4: clear fal_server and fal_client from parameter file
      Step 5: Remove DR entries from tnsping.ora file
      Step 6: Discard complete DR server.
      Done

  18. Hello, could you please advise,if I did DML changes on the Primary database these changes were applied on the Standby database. The question is, if I will do changes on the Standby database (for example drop some table) these changes will apply on the Primary database or not? Thanks.

    • Hello Helice, thanks for writing!
      Its Active-Passive configuration, so for DML operations, your primary database will broadcast the DML changes and will be applied to standby.
      Primary can’t receive any changes.
      Create table is Data Defining Language. DDL.

  19. Hii
    whenever i am going to up listener file after configuration
    its shows an error …

    TNSLSNR for Linux: Version 11.2.0.4.0 – Production
    System parameter file is /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
    Log messages written to /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
    Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.249.129)(PORT=1521)))
    TNS-01155: Incorrectly specified SID_LIST_LISTENER parameter in LISTENER.ORA
    NL-00303: syntax error in NV string

    can you help me for that

  20. i just need to know sir, what are the mandatory parameters on primary and standby database.?

  21. Hi,, i lil bit confusing about step 8.. configure listener file.. should i make new listener file (new name) and copy it to DR or configuring existing listener file (listener.ora) and copy it to DR with the same name ?

  22. SQL> select dest_name,status,error from v$archive_dest where dest_name=’LOG_ARCHIVE_DEST_2′;

    DEST_NAME
    ——————————————————————————–
    STATUS ERROR
    ——— —————————————————————–
    LOG_ARCHIVE_DEST_2
    ERROR ORA-01031: insufficient privileges

    change password file also still getting the same error at primary database .pls help

  23. Hi,
    One of the best article for DG Configuration.
    Perfectly configured.
    Thanks for the Wonderful job.
    AShokan

  24. Wow…. It’s a great one. I have configured DG in my currnet live environment which is working fine.

    Thanks a lot..

    Raj

Leave a Reply