1 2 Previous Next 18 Replies Latest reply on Feb 14, 2013 9:31 AM by CKPT

    Some doubts regarding Data Guard

    Fabricio_Jorge
      Hi All,

      I'm trying to dig deeper into the world of Data Guard 11gR2 on a OEL 6.3 environment. At the present moment, i am reading the book 'Oracle Data Guard 11g Handbook' and reading some tutorials on the internet. And it's because of these tutorials that a became full of "small doubts':

      1) When creating a Standby database using cold backup, i have to copy all the datafiles? By all i mean ALL, including SYSTEM, for example?

      2) Again regarding cold backups standby creation. On a tutorial, i saw a way to create the standby database using RMAN. In this mode, it is only necessary to have installed the oracle binaries on the standby side, correct? Using cold backups, i have to previously create the database before copying the datafiles or it can be done only with the oracle binaries only?

      3) Regarding DB_FILE_NAME_CONVERT. When using this parameter, i have to put the full path of primary and standby datafiles? I saw some tutorials that it is said to put only a string, and i saw others that put the full path. For example:

      {code}

      -- what is the correct way (best practice)? To use the full path name?
      --TUTORIAL EXAMPLE
      db_file_name_convert='/home/oracle/primary/','/home/oracle/standby/'
      log_file_name_convert='/home/oracle/primary/','/home/oracle/standby/'

      --MY TESTS (What i really i would like to know if i put the full path name, the configurations will work)
      -- These, i saw on a tutorial, bu i didin't understand from where these lonelly strings came from
      db_file_name_convert='/dg01/','/dg02/' -- the full path is "/oradata/dg01" and "/oradata/dg02"
      log_file_name_convert='/dg01/','/dg02/' -- the full path is "/redologs/dg01" and "/redologs/dg02"
      {code}

      Sorry for my amateur questions...

      Thanks in advance.
        • 1. Re: Some doubts regarding Data Guard
          Mihael
          1) When creating a Standby database using cold backup, i have to copy all the datafiles? By all i mean ALL, including SYSTEM, for example?
          Yes, all of them. But if you use RMAN, RMAN will copy them.
          2) Again regarding cold backups standby creation. On a tutorial, i saw a way to create the standby database using RMAN. In this mode, it is only necessary to have installed the oracle binaries on the standby side, correct? Using cold backups, i have to previously create the database before copying the datafiles or it can be done only with the oracle binaries only?
          Yes, you should install only software.
          3) Regarding DB_FILE_NAME_CONVERT. When using this parameter, i have to put the full path of primary and standby datafiles?
          You use just one or more pairs of strings, partial paths or full.
          1 person found this helpful
          • 2. Re: Some doubts regarding Data Guard
            CKPT
            I'm trying to dig deeper into the world of Data Guard 11gR2 on a OEL 6.3 environment. At the present moment, i am reading the book 'Oracle Data Guard 11g Handbook' and reading some tutorials on the internet. And it's because of these tutorials that a became full of "small doubts':
            Best Book
            1) When creating a Standby database using cold backup, i have to copy all the datafiles? By all i mean ALL, including SYSTEM, for example?
            You have to copy all of the data files, Standby database is an mirror image copy(please note),
            2) Again regarding cold backups standby creation. On a tutorial, i saw a way to create the standby database using RMAN. In this mode, it is only necessary to have installed the oracle binaries on the standby side, correct? Using cold backups, i have to previously create the database before copying the datafiles or it can be done only with the oracle binaries only?
            You should have ORACLE_HOME/Binaries of course. You no need to create database there are two ways. One is using RMAN by this procedure either you can perform duplicate (or) Restore methods. If you are using manual method then you have to create standby control file and datafiles.
            3) Regarding DB_FILE_NAME_CONVERT. When using this parameter, i have to put the full path of primary and standby datafiles? I saw some tutorials that it is said to put only a string, and i saw others that put the full path. For example:
            {code}

            -- what is the correct way (best practice)? To use the full path name?
            --TUTORIAL EXAMPLE
            db_file_name_convert='/home/oracle/primary/','/home/oracle/standby/'
            log_file_name_convert='/home/oracle/primary/','/home/oracle/standby/'

            --MY TESTS (What i really i would like to know if i put the full path name, the configurations will work)
            -- These, i saw on a tutorial, bu i didin't understand from where these lonelly strings came from
            db_file_name_convert='/dg01/','/dg02/' -- the full path is "/oradata/dg01" and "/oradata/dg02"
            log_file_name_convert='/dg01/','/dg02/' -- the full path is "/redologs/dg01" and "/redologs/dg02"
            {code}
            You have to mention until the child directory. ex: DB_FILE_NAME_CONVERT='/u01/prim/oradata/datafiles','/u02/standby/oradata/datafiles'
            You can refer how to configure parameter using this link http://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams048.htm

            HTH.
            1 person found this helpful
            • 3. Re: Some doubts regarding Data Guard
              Fabricio_Jorge
              Thank you all for the clarifying answers.

              Following a tutorial to create the standby via RMAN, a faced the error below. I did all the configurations on both servers. Bu when i try to connect via RMAN on the standby (via auxiliary), the error below appear to me:
              #############################
              ### PRIMARY SERVER (dg01) ###
              #############################
              [oracle@dg1 admin]$ rman target sys
              
              target database Password: 
              connected to target database: DG01 (DBID=441447309)
              
              RMAN> connect auxiliary sys@'(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(Host=dg2)(Port=1521)))(CONNECT_DATA=(SERVICE_NAME=dg02)))'
              
              auxiliary database Password: 
              connected to auxiliary database (not started)
              
              RMAN> @script.rman
              
              RMAN> run{
              2> allocate channel prmy1 type disk;
              3> allocate channel prmy2 type disk;
              4> allocate auxiliary channel stby type disk;
              5> duplicate target database for standby from active database
              6> spfile
              7> parameter_value_convert 'dg01','dg02'
              8> set db_unique_name='dg02'
              9> set db_file_name_convert='/oradata/dg01/','/oradata/dg02/'
              10> set log_file_name_convert='/redologs/dg01/','/redologs/dg02/'
              11> set control_files='/oradata/dg02/control_dg02.ctl'
              12> set log_archive_max_processes='5'
              13> set fal_client='dg02'
              14> set fal_server='dg01'
              15> set standby_file_management='AUTO'
              16> set log_archive_config='dg_config=(dg01,dg02)'
              17> set log_archive_dest_2='service=dg01 ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=dg01'
              18> ;
              19> }
              using target database control file instead of recovery catalog
              allocated channel: prmy1
              channel prmy1: SID=37 device type=DISK
              
              allocated channel: prmy2
              channel prmy2: SID=33 device type=DISK
              
              released channel: prmy1
              released channel: prmy2
              RMAN-00571: ===========================================================
              RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
              RMAN-00571: ===========================================================
              RMAN-03002: failure of allocate command at 02/04/2013 12:21:31
              RMAN-06403: could not obtain a fully authorized session
              RMAN-04006: error from auxiliary database: ORA-01034: ORACLE not available
              ORA-27101: shared memory realm does not exist
              Linux Error: 2: No such file or directory
              
              RMAN> **end-of-file**
              
              
              ###############################
              ### STANDBY DATABASE (dg02) ###
              ###############################
              
              [oracle@dg2 dbs]$ sqlplus / as sysdba
              
              Connected to:
              Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
              With the Partitioning, OLAP, Data Mining and Real Application Testing options
              
              SQL> select status from v$instance;
              
              STATUS
              ------------
              STARTED
              
              SQL> 
              
              ### I started the standby (startup nomount pfile=mypfile.ora) with a pfile containing only the following parameter:
              DB_NAME=dg02
              The listener on the standby database is:
              [oracle@dg2 dbs]$ lsnrctl status
              
              LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 04-FEB-2013 12:27:04
              
              Copyright (c) 1991, 2011, Oracle.  All rights reserved.
              
              Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dg2.localdomain)(PORT=1521)))
              STATUS of the LISTENER
              ------------------------
              Alias                     LISTENER
              Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
              Start Date                03-FEB-2013 22:58:57
              Uptime                    0 days 13 hr. 28 min. 7 sec
              Trace Level               off
              Security                  ON: Local OS Authentication
              SNMP                      OFF
              Listener Parameter File   /opt/app/oracle/11.2.0/dbhome_1/network/admin/listener.ora
              Listener Log File         /opt/app/oracle/diag/tnslsnr/dg2/listener/alert/log.xml
              Listening Endpoints Summary...
                (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dg2.localdomain)(PORT=1521)))
                (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
              Services Summary...
              Service "dg02" has 2 instance(s).
                Instance "dg02", status UNKNOWN, has 1 handler(s) for this service...
                Instance "dg02", status BLOCKED, has 1 handler(s) for this service...
              The command completed successfully
              And i can tnsping from the primary (dg1) to the standby (dg2):
              [oracle@dg1 admin]$ tnsping '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = dg2)(Port = 1521))) (CONNECT_DATA = (SERVICE_NAME = dg02)))'
              
              TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 04-FEB-2013 12:28:25
              
              Copyright (c) 1997, 2011, Oracle.  All rights reserved.
              
              Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = dg2)(Port = 1521))) (CONNECT_DATA = (SERVICE_NAME = dg02)))
              OK (10 msec)
              I "think" it's all properly configured, so why am i not able to connect to the standby via rman (auxiliary)?

              Thanks again for your attention and pacience.
              • 4. Re: Some doubts regarding Data Guard
                Shivananda Rao
                Hello,
                RMAN-04006: error from auxiliary database: ORA-01034: ORACLE not available
                ORA-27101: shared memory realm does not exist
                Linux Error: 2: No such file or directory
                Please look out for the errors in the alert log file of the standby database and paste them here.


                Regards,
                Shivananda
                1 person found this helpful
                • 5. Re: Some doubts regarding Data Guard
                  Fabricio_Jorge
                  Thanks for your attention and help.

                  You see, there is no standby database created yet. I'm trying to duplicate my Primary database using RMAN. The standby instance is in nomount state using a pfile with only the DB_NAME parameter.

                  I suppose that using RMAN, it will create the standby database for my, using as baseline the primary, or am i saying nonsense things here?
                  • 6. Re: Some doubts regarding Data Guard
                    Shivananda Rao
                    You say that the auxiliary instance is started (nomount stage), but RMAN is not connecting to the right auxiliary instance. Its showing the status as "Not Started".
                    RMAN> connect auxiliary sys@'(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(Host=dg2)(Port=1521)))(CONNECT_DATA=(SERVICE_NAME=dg02)))'
                     
                    auxiliary database Password: 
                    connected to auxiliary database (not started)
                    Why not use Oracle Net Service name to connect to the instances ? Take a look at this http://shivanandarao.wordpress.com/2012/03/10/creating-physical-standby-database-on-oracle-11g/ on how to create a physical standby database in 11gR2. Hope it helps !


                    Regards,
                    Shivananda
                    • 7. Re: Some doubts regarding Data Guard
                      Fabricio_Jorge
                      Hum... this is strange

                      When a TNSPING the standby database using the service name dg02, it does not give me any error, but when i try to connect via RMAN, using the service name, the following error is shown:
                      oracle@dg1 admin]$ rman target sys@dg01 auxiliary sys@dg02
                      
                      Recovery Manager: Release 11.2.0.3.0 - Production on Mon Feb 4 14:22:31 2013
                      
                      Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
                      
                      target database Password: 
                      connected to target database: DG01 (DBID=441447309)
                      auxiliary database Password: 
                      RMAN-00571: ===========================================================
                      RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
                      RMAN-00571: ===========================================================
                      RMAN-00554: initialization of internal recovery manager package failed
                      RMAN-04006: error from auxiliary database: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
                      But if a tnsping it, no errors are returned:
                      [oracle@dg1 admin]$ tnsping dg02
                      
                      TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 04-FEB-2013 14:25:11
                      
                      Copyright (c) 1997, 2011, Oracle.  All rights reserved.
                      
                      Used parameter files:
                      
                      
                      Used TNSNAMES adapter to resolve the alias
                      Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = dg2)(Port = 1521))) (CONNECT_DATA = (SERVICE_NAME = dg02.world)))
                      OK (10 msec)
                      ### EDIT ###

                      Here is my tnsnames.ora on the primary server:
                      dg01 =
                         (DESCRIPTION =
                            (ADDRESS_LIST =
                               (ADDRESS = (PROTOCOL = TCP)(Host = dg1)(Port = 1521))
                            )
                            (CONNECT_DATA =
                               (SERVICE_NAME = dg01.world)
                            )
                         )
                      
                      dg02 =
                         (DESCRIPTION =
                            (ADDRESS_LIST =
                               (ADDRESS = (PROTOCOL = TCP)(Host = dg2)(Port = 1521))
                            )
                            (CONNECT_DATA =
                               (SERVICE_NAME = dg02.world)
                            )
                         )
                      I can ping the standby server (dg2) without problems.

                      Edited by: Fabricio_Jorge on Feb 4, 2013 10:29 AM
                      • 8. Re: Some doubts regarding Data Guard
                        mseberg
                        Hello;

                        Its probably not your tnsnames. Its probably a static listener entry or the lack of one causing the issue.

                        This example may help : ( SID_LIST_LISTENER is the static entry )

                        http://www.visi.com/~mseberg/data_guard/Data_Guard_Listener_Example.html

                        Best Regards

                        mseberg
                        1 person found this helpful
                        • 9. Re: Some doubts regarding Data Guard
                          Fabricio_Jorge
                          Hello, and thanks for your attention and help.

                          I followed your example, but without success. Below is how my listener.ora is now in both primary and standby:
                          #######################################################################
                          ###                            PRIMARY                              ###
                          #######################################################################
                          
                          LISTENER =
                            (DESCRIPTION_LIST =
                              (DESCRIPTION =
                                (ADDRESS = (PROTOCOL = TCP)(HOST = dg1.localdomain)(PORT = 1521))
                                (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
                              )
                            )
                          
                          
                          SID_LIST_LISTENER =
                            (SID_LIST =
                              (SID_DESC =
                          #      (GLOBAL_DBNAME = dg01.WORLD)
                                (GLOBAL_DBNAME = dg01_DGMGRL.dg1.localdomain)
                                (ORACLE_HOME = /opt/app/oracle/11.2.0/dbhome_1)
                                (SID_NAME = dg01)
                              )
                            )
                          
                          ADR_BASE_LISTENER = /opt/app/oracle
                          
                          ###############################
                          ### lsnrctl services output ###
                          ###############################
                          
                          [oracle@dg1 admin]$ lsnrctl services
                          
                          LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 04-FEB-2013 20:25:53
                          
                          Copyright (c) 1991, 2011, Oracle.  All rights reserved.
                          
                          Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dg1.localdomain)(PORT=1521)))
                          Services Summary...
                          Service "dg01.world" has 1 instance(s).
                            Instance "dg01", status READY, has 1 handler(s) for this service...
                              Handler(s):
                                "DEDICATED" established:30 refused:0 state:ready
                                   LOCAL SERVER
                          Service "dg01XDB.world" has 1 instance(s).
                            Instance "dg01", status READY, has 1 handler(s) for this service...
                              Handler(s):
                                "D000" established:0 refused:0 current:0 max:1022 state:ready
                                   DISPATCHER <machine: dg1, pid: 1862>
                                   (ADDRESS=(PROTOCOL=tcp)(HOST=dg1.localdomain)(PORT=37901))
                          Service "dg01_DGMGRL.dg1.localdomain" has 1 instance(s).
                            Instance "dg01", status UNKNOWN, has 1 handler(s) for this service...
                              Handler(s):
                                "DEDICATED" established:0 refused:0
                                   LOCAL SERVER
                          The command completed successfully
                          
                          #############################
                          ### lsnrctl status output ###
                          #############################
                          
                          [oracle@dg1 admin]$ lsnrctl status
                          
                          LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 04-FEB-2013 20:26:23
                          
                          Copyright (c) 1991, 2011, Oracle.  All rights reserved.
                          
                          Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dg1.localdomain)(PORT=1521)))
                          STATUS of the LISTENER
                          ------------------------
                          Alias                     LISTENER
                          Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
                          Start Date                04-FEB-2013 20:09:52
                          Uptime                    0 days 0 hr. 16 min. 30 sec
                          Trace Level               off
                          Security                  ON: Local OS Authentication
                          SNMP                      OFF
                          Listener Parameter File   /opt/app/oracle/11.2.0/dbhome_1/network/admin/listener.ora
                          Listener Log File         /opt/app/oracle/diag/tnslsnr/dg1/listener/alert/log.xml
                          Listening Endpoints Summary...
                            (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dg1.localdomain)(PORT=1521)))
                            (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
                          Services Summary...
                          Service "dg01.world" has 1 instance(s).
                            Instance "dg01", status READY, has 1 handler(s) for this service...
                          Service "dg01XDB.world" has 1 instance(s).
                            Instance "dg01", status READY, has 1 handler(s) for this service...
                          Service "dg01_DGMGRL.dg1.localdomain" has 1 instance(s).
                            Instance "dg01", status UNKNOWN, has 1 handler(s) for this service...
                          The command completed successfully
                          
                          
                          #######################################################################
                          ###                              STANDBY                            ###
                          #######################################################################
                          
                          LISTENER =
                            (DESCRIPTION_LIST =
                              (DESCRIPTION =
                                (ADDRESS = (PROTOCOL = TCP)(HOST = dg2.localdomain)(PORT = 1521))
                                (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
                              )
                            )
                          
                          SID_LIST_LISTENER=
                             (SID_LIST=
                                (SID_DESC=
                                   #(GLOBAL_DBNAME=dg02)
                                   (GLOBAL_DBNAME=dg02_DGMGRL.dg2.localdomain)
                                   (SID_NAME=dg02)
                                   (ORACLE_HOME=/opt/app/oracle/11.2.0/dbhome_1)
                                )
                             )
                          
                          ADR_BASE_LISTENER = /opt/app/oracle
                          
                          ###############################
                          ### lsnrctl services output ###
                          ###############################
                          
                          [oracle@dg2 admin]$ lsnrctl services
                          
                          LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 04-FEB-2013 20:29:05
                          
                          Copyright (c) 1991, 2011, Oracle.  All rights reserved.
                          
                          Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dg2.localdomain)(PORT=1521)))
                          Services Summary...
                          Service "dg02" has 1 instance(s).
                            Instance "dg02", status BLOCKED, has 1 handler(s) for this service...
                              Handler(s):
                                "DEDICATED" established:0 refused:0 state:ready
                                   LOCAL SERVER
                          Service "dg02_DGMGRL.dg2.localdomain" has 1 instance(s).
                            Instance "dg02", status UNKNOWN, has 1 handler(s) for this service...
                              Handler(s):
                                "DEDICATED" established:0 refused:0
                                   LOCAL SERVER
                          The command completed successfully
                          
                          
                          #############################
                          ### lsnrctl status output ###
                          #############################
                          
                          [oracle@dg2 admin]$ lsnrctl status
                          
                          LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 04-FEB-2013 20:29:21
                          
                          Copyright (c) 1991, 2011, Oracle.  All rights reserved.
                          
                          Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dg2.localdomain)(PORT=1521)))
                          STATUS of the LISTENER
                          ------------------------
                          Alias                     LISTENER
                          Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
                          Start Date                04-FEB-2013 20:20:29
                          Uptime                    0 days 0 hr. 8 min. 52 sec
                          Trace Level               off
                          Security                  ON: Local OS Authentication
                          SNMP                      OFF
                          Listener Parameter File   /opt/app/oracle/11.2.0/dbhome_1/network/admin/listener.ora
                          Listener Log File         /opt/app/oracle/diag/tnslsnr/dg2/listener/alert/log.xml
                          Listening Endpoints Summary...
                            (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dg2.localdomain)(PORT=1521)))
                            (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
                          Services Summary...
                          Service "dg02" has 1 instance(s).
                            Instance "dg02", status BLOCKED, has 1 handler(s) for this service...
                          Service "dg02_DGMGRL.dg2.localdomain" has 1 instance(s).
                            Instance "dg02", status UNKNOWN, has 1 handler(s) for this service...
                          The command completed successfully
                          As you can see, all the services are properly registered, so i really dont't understand why RMAN is not still able to connect to the standby instance via auxiliary channel.
                          • 10. Re: Some doubts regarding Data Guard
                            mseberg
                            Hello;

                            Your global_dbname does not look right.

                            (GLOBAL_DBNAME = dg01_DGMGRL.dg1.localdomain)

                            I have another example here

                            http://www.visi.com/~mseberg/rman/active_database_duplicating.html

                            Check out the Listener.ora Before and after section.

                            Best Regards

                            mseberg
                            1 person found this helpful
                            • 11. Re: Some doubts regarding Data Guard
                              989118
                              In dataguard configured environment, is it possible standby server can be used in read-only mode. I mean can we just read the data and use it for reporting? Can anyone [share your events|http://eventsbuz.com] of experiences utilizing data guard.

                              Edited by: user2722407 on Feb 4, 2013 7:52 PM
                              • 12. Re: Some doubts regarding Data Guard
                                Fabricio_Jorge
                                Thanks again (and sorry for the amount of questions and doubts)... now i was able to create the standdy database using RMAN (putting the "(UR=A)" in the tnsnames.ora)

                                Now i'm facing another issue that i think has to do with Oracle Net Services Again. The Redo data is not being transmitted to the standby. See the messages below, please:
                                ############################
                                ### alert.log from the primary database ###
                                ############################
                                
                                Error 1034 received logging on to the standby
                                2013-02-05 00:56:30.956000 -03:00
                                Error 1034 received logging on to the standby
                                2013-02-05 00:57:31.318000 -03:00
                                Error 1034 received logging on to the standby
                                2013-02-05 00:58:31.459000 -03:00
                                Error 1034 received logging on to the standby
                                2013-02-05 00:59:31.633000 -03:00
                                Error 1034 received logging on to the standby
                                2013-02-05 01:00:31.838000 -03:00
                                Error 1034 received logging on to the standb
                                
                                ############################################
                                ### querying the view v$archive_dest, i find the possible problem ###
                                ############################################
                                
                                DEST_NAME          TARGET  PROCESS     STATUS       ERROR
                                -------------------- ------- ---------- --------- -----------------------------------------------------------------
                                LOG_ARCHIVE_DEST_1   PRIMARY ARCH     VALID
                                LOG_ARCHIVE_DEST_2   STANDBY LGWR     ERROR       ORA-01034: ORACLE not available
                                LOG_ARCHIVE_DEST_3   PRIMARY ARCH     INACTIVE
                                LOG_ARCHIVE_DEST_4   PRIMARY ARCH     INACTIVE
                                LOG_ARCHIVE_DEST_5   PRIMARY ARCH     INACTIVE
                                LOG_ARCHIVE_DEST_6   PRIMARY ARCH     INACTIVE
                                LOG_ARCHIVE_DEST_7   PRIMARY ARCH     INACTIVE
                                
                                #########################################
                                ### My log_archive_dest_2 is configured this way on primary ###
                                #########################################
                                
                                
                                log_archive_dest_2 ...: service=dg02 async valid_for=(online_logfile,primary_role) db_unique_name=dg02
                                
                                
                                #######################################
                                ### ... and i'm still able to tnsping the standby database ###
                                #######################################
                                
                                [oracle@dg1 admin]$ tnsping dg01
                                
                                TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 05-FEB-2013 01:02:22
                                
                                Copyright (c) 1997, 2011, Oracle.  All rights reserved.
                                
                                Used parameter files:
                                
                                
                                Used TNSNAMES adapter to resolve the alias
                                Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = dg1)(Port = 1521))) (CONNECT_DATA = ( (SERVICE_NAME=dg01)(UR=A)))
                                OK (0 msec)
                                
                                
                                ##################################################
                                ### ... but cannot connect on the standby instance (it is in mount mode) ###
                                #################################################
                                
                                [oracle@dg1 admin]$ sqlplus sys@dg02 as sysdba
                                
                                SQL*Plus: Release 11.2.0.3.0 Production on Tue Feb 5 01:02:40 2013
                                
                                Copyright (c) 1982, 2011, Oracle.  All rights reserved.
                                
                                Enter password: 
                                Connected to an idle instance.
                                Below you can see the listener.ora on the standby side with some new configurations, following the link that you have provided:
                                [oracle@dg2 admin]$ lsnrctl status
                                
                                LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 05-FEB-2013 01:07:42
                                
                                Copyright (c) 1991, 2011, Oracle.  All rights reserved.
                                
                                Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dg2.localdomain)(PORT=1521)))
                                STATUS of the LISTENER
                                ------------------------
                                Alias                     LISTENER
                                Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
                                Start Date                05-FEB-2013 00:57:12
                                Uptime                    0 days 0 hr. 10 min. 30 sec
                                Trace Level               off
                                Security                  ON: Local OS Authentication
                                SNMP                      OFF
                                Listener Parameter File   /opt/app/oracle/11.2.0/dbhome_1/network/admin/listener.ora
                                Listener Log File         /opt/app/oracle/diag/tnslsnr/dg2/listener/alert/log.xml
                                Listening Endpoints Summary...
                                  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dg2.localdomain)(PORT=1521)))
                                  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
                                Services Summary...
                                Service "dg02" has 1 instance(s).
                                  Instance "dg02", status UNKNOWN, has 1 handler(s) for this service...
                                Service "dg02.world" has 1 instance(s).
                                  Instance "dg02", status READY, has 1 handler(s) for this service...
                                The command completed successfully
                                At last, here is the tnsnames.ora on the primary side:
                                dg01 =
                                   (DESCRIPTION =
                                      (ADDRESS_LIST =
                                         (ADDRESS = (PROTOCOL = TCP)(Host = dg1)(Port = 1521))
                                      )
                                      (CONNECT_DATA =
                                         (SERVICE_NAME=dg01)(UR=A)
                                      )
                                   )
                                
                                dg02 =
                                   (DESCRIPTION =
                                      (ADDRESS_LIST =
                                         (ADDRESS = (PROTOCOL = TCP)(Host = dg2)(Port = 1521))
                                      )
                                      (CONNECT_DATA =
                                         (SERVICE_NAME=dg02)(UR=A)
                                      )
                                   )
                                • 13. Re: Some doubts regarding Data Guard
                                  CKPT
                                  >
                                  LOG_ARCHIVE_DEST_2 STANDBY LGWR     ERROR     ORA-01034: ORACLE not available
                                  >

                                  Its clear that standby database is no more available.

                                  >
                                  [oracle@dg1 admin]$ sqlplus sys@dg02 as sysdba

                                  SQL*Plus: Release 11.2.0.3.0 Production on Tue Feb 5 01:02:40 2013

                                  Copyright (c) 1982, 2011, Oracle. All rights reserved.

                                  Enter password:
                                  Connected to an idle instance.
                                  >

                                  Why standby database is IDLE, have you shutdown it or crashed?
                                  Can you go to alert log and check last 100 lines of information, that you find any information on the same?
                                  Start again in mount status and then start MRP. Post if you have same issues again.
                                  • 14. Re: Some doubts regarding Data Guard
                                    Fabricio_Jorge
                                    Hi CKPT... thanks for your assistance.

                                    Thats the problem. The standby instance is available in mount mode. But i did what you suggestet me.
                                    ### standby instance ###
                                    
                                    shutdown immediate;
                                    startup mount;
                                    ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
                                    
                                    SQL> select status, instance_name, version from v$instance;
                                    
                                    STATUS          INSTANCE_NAME    VERSION
                                    ------------ ---------------- -----------------
                                    MOUNTED      dg02           11.2.0.3.0
                                    
                                    
                                    SQL> select database_role from v$database;
                                    
                                    DATABASE_ROLE
                                    ----------------
                                    PHYSICAL STANDBY
                                    Apparently, it's all normal. correct? On the alert.log from the primary site, the following message keeps poping up:
                                    013-02-05 15:16:25.217000 -03:00
                                    Error 1034 received logging on to the standby
                                    2013-02-05 15:16:31.167000 -03:00
                                    Error 1034 received logging on to the standby
                                    ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=MEMORY SID='*';
                                    Thread 1 advanced to log sequence 40 (LGWR switch)
                                      Current log# 1 seq# 40 mem# 0: /redologs/dg01/redo_m1_g1.dbf
                                      Current log# 1 seq# 40 mem# 1: /redologs/dg01/redo_m2_g1.dbf
                                    2013-02-05 15:16:49.113000 -03:00
                                    Archived Log entry 44 added for thread 1 sequence 39 ID 0x1a4f638d dest 1:
                                    2013-02-05 15:18:49.946000 -03:00
                                    Thread 1 advanced to log sequence 41 (LGWR switch)
                                      Current log# 2 seq# 41 mem# 0: /redologs/dg01/redo_m1_g2.dbf
                                      Current log# 2 seq# 41 mem# 1: /redologs/dg01/redo_m2_g2.dbf
                                    Archived Log entry 45 added for thread 1 sequence 40 ID 0x1a4f638d dest 1:
                                    2013-02-05 15:22:28.008000 -03:00
                                    Error 1034 received logging on to the standby
                                    2013-02-05 15:23:28.213000 -03:00
                                    Error 1034 received logging on to the standby
                                    2013-02-05 15:24:28.333000 -03:00
                                    Error 1034 received logging on to the standby
                                    2013-02-05 15:25:28.457000 -03:00
                                    Error 1034 received logging on to the standby
                                    2013-02-05 15:26:28.605000 -03:00
                                    Error 1034 received logging on to the standby
                                    2013-02-05 15:27:28.727000 -03:00
                                    Error 1034 received logging on to the standby
                                    2013-02-05 15:28:28.842000 -03:00
                                    Error 1034 received logging on to the standby
                                    I realize that the error 1034 has something to do with the inability for the primary to access the standby, but i can tnsping the standby site normally, and as you can se above, the standby instance is started and the MRP has been also started.

                                    After restarting the standby instance in mount stage, the error persists:
                                    DEST_NAME                 TARGET                     PROCESS      STATUS    ERROR
                                    ------------------------------ ------------------------------ ---------- --------- -----------------------------------------------------------------
                                    LOG_ARCHIVE_DEST_1            PRIMARY                     ARCH      VALID
                                    LOG_ARCHIVE_DEST_2            STANDBY                     LGWR      ERROR        ORA-01034: ORACLE not available
                                    LOG_ARCHIVE_DEST_3            PRIMARY                     ARCH      INACTIVE
                                    LOG_ARCHIVE_DEST_4            PRIMARY                     ARCH      INACTIVE
                                    
                                    
                                    
                                    [oracle@dg1 bin]$ tnsping dg02
                                    
                                    TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 05-FEB-2013 15:29:33
                                    
                                    Copyright (c) 1997, 2011, Oracle.  All rights reserved.
                                    
                                    Used parameter files:
                                    
                                    
                                    Used TNSNAMES adapter to resolve the alias
                                    Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = dg2)(Port = 1521))) (CONNECT_DATA = (SERVICE_NAME=dg02)(UR=A)))
                                    OK (0 msec)
                                    1 2 Previous Next