1 2 3 Previous Next 31 Replies Latest reply: Jan 17, 2013 6:36 AM by CKPT Go to original post RSS
      • 15. Re: not able to get data of primary in standby database (configured dataguard)
        974427
        sorry for late reply.
        no... i dont think any progress is there.. as no new entries are found in alertlog...
        at primary i have this..
        SQL> select process,status from v$managed_standby;

        PROCESS STATUS
        --------- ------------
        ARCH CLOSING
        ARCH CLOSING
        ARCH CONNECTED
        ARCH CONNECTED
        ARCH CONNECTED
        ARCH CLOSING
        ARCH CONNECTED
        ARCH CONNECTED
        ARCH CONNECTED
        ARCH CONNECTED
        ARCH CONNECTED

        PROCESS STATUS
        --------- ------------
        ARCH CONNECTED
        ARCH CONNECTED
        ARCH CONNECTED
        ARCH CONNECTED
        ARCH CONNECTED
        ARCH CONNECTED
        ARCH CONNECTED
        ARCH CONNECTED
        ARCH CONNECTED
        ARCH CONNECTED
        ARCH CONNECTED

        PROCESS STATUS
        --------- ------------
        ARCH CONNECTED
        ARCH OPENING
        ARCH CONNECTED
        ARCH CLOSING
        ARCH CLOSING
        ARCH CLOSING
        ARCH CLOSING
        ARCH CLOSING....

        also i had issued "recover managed standby database" at standby, its hung i guess... as its d same from yst....

        i am not sure, but may be, may be this might be the problem.. while configuring listener i had given different port numbers... is this whole problem due to this?
        • 16. Re: not able to get data of primary in standby database (configured dataguard)
          Hemant K Chitale
          placed datafiles in the location
          How did you do this ? Did you put the datafiles in backup mode at the source and then copoy them to the standby ?

          The ORA-1152 errors you keep getting indicate that the datafiles are "future" to archivelogs.

          Hemant K Chitale
          • 17. Re: not able to get data of primary in standby database (configured dataguard)
            974427
            i had shutdown both databases n then copied...
            • 18. Re: not able to get data of primary in standby database (configured dataguard)
              Hemant K Chitale
              i had shutdown both databases n then copied
              and the control file ? Still using the older standby controlfile or did you create a new standby controlfile from the primary and copy it to the standby ?
              You can still do this : create a new standby controlfile from the primary and copy it to the standby and then startup mount the standby.


              Hemant K Chitale
              • 19. Re: not able to get data of primary in standby database (configured dataguard)
                974427
                ya controlfile was old..
                i created controlfile from primary n started standby at mount but gives error..

                SQL> startup mount;
                ORACLE instance started.

                Total System Global Area 1603411968 bytes
                Fixed Size 2115376 bytes
                Variable Size 939526352 bytes
                Database Buffers 654311424 bytes
                Redo Buffers 7458816 bytes
                ORA-00214: control file
                'D:\ORACLE11G\APP\ADMINISTRATOR\ORADATA\STAND\STAND.CTL' version 26618
                inconsistent with file
                'D:\ORACLE11G\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\STAND\STAND.CTL' version
                25146
                • 20. Re: not able to get data of primary in standby database (configured dataguard)
                  Hemant K Chitale
                  You have two standby controlfiles. You have updated only one of them.


                  Hemant K Chitale
                  • 21. Re: not able to get data of primary in standby database (configured dataguard)
                    974427
                    ya... thanx now standby is mounted with no errors..
                    also in standby alertlog a few entries are there..

                    starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
                    starting up 1 shared server(s) ...
                    ORACLE_BASE from environment = D:\Oracle11g\app\Administrator
                    Thu Jan 17 12:38:50 2013
                    ALTER DATABASE MOUNT
                    Thu Jan 17 12:38:50 2013
                    VKTM started with pid=3, OS id=8716 at elevated priority
                    VKTM running at (20)ms precision
                    Thu Jan 17 12:38:50 2013
                    MMNL started with pid=15, OS id=5308
                    Set as converted control file due to db_unique_name mismatch ------------------ any problem here?
                    Changing di2dbun from prod to STAND
                    Setting recovery target incarnation to 2
                    ARCH: STARTING ARCH PROCESSES
                    Thu Jan 17 12:38:57 2013

                    ARCr: Archival started
                    ARCs: Archival started
                    ARCt: Archival started
                    ARCH: STARTING ARCH PROCESSES COMPLETE
                    ARC0: Becoming the 'no FAL' ARCH
                    ARC0: Becoming the 'no SRL' ARCH
                    ARC0: Thread not mounted
                    ARC1: Becoming the heartbeat ARCH
                    ARC2: Thread not mounted
                    ARC3: Thread not mounted
                    ARC1: Thread not mounted
                    Successful mount of redo thread 1, with mount id 216809309
                    Physical Standby Database mounted.
                    Lost write protection disabled
                    Completed: ALTER DATABASE MOUNT
                    Thu Jan 17 12:38:57 2013
                    ARCq started with pid=45, OS id=7904
                    ARC4: Thread not mounted
                    ARC5: Thread not mounted
                    ARCb: Thread not mounted
                    ARCc: Thread not mounted
                    ARC6: Thread not mounted
                    ARC7: Thread not mounted
                    ARCe: Thread not mounted
                    ARC9: Thread not mounted
                    ARCf: Thread not mounted
                    ARCd: Thread not mounted
                    ARCi: Thread not mounted
                    ARCp: Thread not mounted how do i go about now....?
                    • 22. Re: not able to get data of primary in standby database (configured dataguard)
                      Hemant K Chitale
                      I wonder what documentation / instructions you are following.

                      First, you hadn't copied all the datafiles.
                      Then, when you did, you hadn't the right standby controlfile.
                      You haven't configured DB_UNIQUE_NAME.

                      There are a lot of holes in your setup.

                      Please read the documentation : http://docs.oracle.com/cd/E11882_01/server.112/e25608/toc.htm

                      Hemant K Chitale
                      • 23. Re: not able to get data of primary in standby database (configured dataguard)
                        974427
                        there is a slight change is the response after placing controlfile..

                        SQL> select process,status from v$managed_standby;

                        PROCESS STATUS
                        --------- ------------
                        ARCH CONNECTED
                        ARCH CONNECTED
                        ARCH CONNECTED
                        ARCH CONNECTED
                        ARCH CONNECTED
                        ARCH CONNECTED
                        ARCH CONNECTED
                        ARCH CONNECTED
                        ARCH CONNECTED
                        ARCH CONNECTED
                        ARCH CONNECTED

                        PROCESS STATUS
                        --------- ------------
                        ARCH CONNECTED
                        ARCH CONNECTED
                        ARCH CONNECTED
                        ARCH CONNECTED
                        ARCH CONNECTED
                        ARCH CONNECTED
                        ARCH CONNECTED
                        ARCH CONNECTED
                        ARCH CONNECTED
                        ARCH CONNECTED
                        ARCH CONNECTED

                        PROCESS STATUS
                        --------- ------------
                        ARCH CONNECTED
                        ARCH CONNECTED
                        ARCH CONNECTED
                        ARCH CLOSING
                        ARCH OPENING
                        ARCH CONNECTED
                        ARCH CONNECTED
                        ARCH CONNECTED

                        does this indicate that logs are being applied? "connected"?
                        • 24. Re: not able to get data of primary in standby database (configured dataguard)
                          974427
                          ok... i looked into the link and cross verified.. i have below entries in my pfile of primary

                          db_unique_name=prod
                          LOG_ARCHIVE_CONFIG='DG_CONFIG=(PROD,STAND)'
                          LOG_ARCHIVE_DEST_1=
                          'LOCATION=D:\Oracle11g\app\Administrator\flash_recovery_area\PROD\ARCHIVELOG VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
                          DB_UNIQUE_NAME=PROD'
                          LOG_ARCHIVE_DEST_2=
                          'SERVICE=STAND LGWR ASYNC
                          VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
                          DB_UNIQUE_NAME=STAND'
                          LOG_ARCHIVE_DEST_STATE_1=ENABLE
                          LOG_ARCHIVE_DEST_STATE_2=ENABLE
                          LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
                          LOG_ARCHIVE_MAX_PROCESSES=30
                          remote_login_passwordfile='EXCLUSIVE'
                          FAL_SERVER=STAND
                          FAL_CLIENT=PROD
                          STANDBY_FILE_MANAGEMENT=AUTO

                          standby pfile has below entries

                          db_unique_name=STAND
                          LOG_ARCHIVE_CONFIG='DG_CONFIG=(PROD,STAND)'
                          LOG_ARCHIVE_DEST_1=
                          'LOCATION=D:\Oracle11g\app\Administrator\flash_recovery_area\STAND\ARCHIVELOG VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
                          DB_UNIQUE_NAME=STAND'
                          LOG_ARCHIVE_DEST_2=
                          'SERVICE=PROD LGWR ASYNC
                          VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
                          DB_UNIQUE_NAME=PROD'
                          LOG_ARCHIVE_DEST_STATE_1=ENABLE
                          LOG_ARCHIVE_DEST_STATE_2=ENABLE
                          LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
                          LOG_ARCHIVE_MAX_PROCESSES=30
                          remote_login_passwordfile='EXCLUSIVE'
                          FAL_SERVER=PROD
                          FAL_CLIENT=STAND
                          STANDBY_FILE_MANAGEMENT=AUTO

                          should i replace
                          LOG_ARCHIVE_DEST_2= 'SERVICE=PROD
                          VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=PROD'
                          in standby pfile?
                          • 25. Re: not able to get data of primary in standby database (configured dataguard)
                            CKPT
                            Will look at it, mean time can you get output from primary and standby and post in coded format using script http://www.oracle-ckpt.com/dataguard_troubleshoot_snapper/
                            • 26. Re: not able to get data of primary in standby database (configured dataguard)
                              974427
                              thanx, dg_Primary_output.log has below entries

                              SQL> set feedback off
                              SQL> set trimspool on
                              SQL> set line 500
                              SQL> set pagesize 50
                              SQL> column name for a30
                              SQL> column display_value for a30
                              SQL> column ID format 99
                              SQL> column "SRLs" format 99
                              SQL> column active format 99
                              SQL> col type format a4
                              SQL> column ID format 99
                              SQL> column "SRLs" format 99
                              SQL> column active format 99
                              SQL> col type format a4
                              SQL> col PROTECTION_MODE for a20
                              SQL> col RECOVERY_MODE for a20
                              SQL> col db_mode for a15
                              SQL> SELECT name, display_value FROM v$parameter WHERE name IN ('db_name','db_unique_name','log_archive_config','log_archive_dest_2','log_archive_dest_state_2','fal_client','fal_server','standby_file_management','standby_archive_dest','db_file_name_convert','log_file_name_convert','remote_login_passwordfile','local_listener','dg_broker_start','dg_broker_config_file1','dg_broker_config_file2','log_archive_max_processes') order by name;

                              NAME DISPLAY_VALUE
                              ------------------------------ ------------------------------
                              db_file_name_convert D:\Oracle11g\app\Administrator
                              \oradata\STAND, D:\Oracle11g\a
                              pp\Administrator\oradata\PROD

                              db_name PROD
                              db_unique_name prod
                              dg_broker_config_file1 D:\ORACLE11G\APP\ADMINISTRATOR
                              \PRODUCT\11.1.0\DB_1\DATABASE\
                              DR1PROD.DAT

                              dg_broker_config_file2 D:\ORACLE11G\APP\ADMINISTRATOR
                              \PRODUCT\11.1.0\DB_1\DATABASE\
                              DR2PROD.DAT

                              dg_broker_start FALSE
                              fal_client PROD
                              fal_server STAND
                              local_listener
                              log_archive_config DG_CONFIG=(PROD,STAND)
                              log_archive_dest_2 SERVICE=STAND LGWR ASYNC
                              VALID_FOR=(ONLINE_LOGFILES,PRI
                              MARY_ROLE)
                              DB_UNIQUE_NAME=STAND

                              log_archive_dest_state_2 ENABLE
                              log_archive_max_processes 30
                              log_file_name_convert D:\Oracle11g\app\Administrator
                              \oradata\STAND, D:\Oracle11g\a
                              pp\Administrator\oradata\prod,
                              D:\Oracle11g\app\Administrato
                              r\flash_recovery_area\STAND\ON
                              LINELOG, D:\Oracle11g\app\Admi
                              nistrator\flash_recovery_area\
                              PROD\ONLINELOG

                              remote_login_passwordfile EXCLUSIVE
                              standby_archive_dest %ORACLE_HOME%\RDBMS
                              standby_file_management AUTO
                              SQL> col name for a10
                              SQL> col DATABASE_ROLE for a10
                              SQL> SELECT name,db_unique_name,protection_mode,DATABASE_ROLE,OPEN_MODE,switchover_status from v$database;

                              NAME DB_UNIQUE_NAME PROTECTION_MODE DATABASE_R OPEN_MODE SWITCHOVER_STATUS
                              ---------- ------------------------------ -------------------- ---------- ---------- --------------------
                              PROD prod MAXIMUM PERFORMANCE PRIMARY READ WRITE SESSIONS ACTIVE
                              SQL> select thread#,max(sequence#) from v$archived_log group by thread#;

                              THREAD# MAX(SEQUENCE#)
                              ---------- --------------
                              1 162
                              SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
                              2 FROM
                              3 (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
                              4 (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
                              5 WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

                              Thread Last Sequence Received Last Sequence Applied Difference
                              ---------- ---------------------- --------------------- ----------
                              1 162 162 0
                              SQL> col severity for a15
                              SQL> col message for a70
                              SQL> col timestamp for a20
                              SQL> select severity,error_code,to_char(timestamp,'DD-MON-YYYY HH24:MI:SS') "timestamp" , message from v$dataguard_status where dest_id=2;

                              SEVERITY ERROR_CODE timestamp MESSAGE
                              --------------- ---------- -------------------- ----------------------------------------------------------------------
                              Error 12514 17-JAN-2013 12:38:23 LGWR: Error 12514 creating archivelog file 'STAND'
                              Error 12514 17-JAN-2013 12:38:27 FAL[server, ARCq]: Error 12514 creating remote archivelog file 'STAND'
                              Error 12514 17-JAN-2013 12:44:19 PING[ARCo]: Heartbeat failed to connect to standby 'STAND'. Error is 1
                              2514.

                              Error 12514 17-JAN-2013 12:49:19 PING[ARCo]: Heartbeat failed to connect to standby 'STAND'. Error is 1
                              2514.

                              Error 12514 17-JAN-2013 12:54:19 PING[ARCo]: Heartbeat failed to connect to standby 'STAND'. Error is 1
                              2514.

                              Error 12514 17-JAN-2013 12:59:20 PING[ARCo]: Heartbeat failed to connect to standby 'STAND'. Error is 1
                              2514.

                              Error 12514 17-JAN-2013 13:04:20 PING[ARCo]: Heartbeat failed to connect to standby 'STAND'. Error is 1
                              2514.

                              Error 12514 17-JAN-2013 13:09:20 PING[ARCo]: Heartbeat failed to connect to standby 'STAND'. Error is 1
                              2514.

                              Error 12514 17-JAN-2013 13:14:21 PING[ARCo]: Heartbeat failed to connect to standby 'STAND'. Error is 1
                              2514.

                              Error 12514 17-JAN-2013 13:19:21 PING[ARCo]: Heartbeat failed to connect to standby 'STAND'. Error is 1
                              2514.

                              Error 12514 17-JAN-2013 13:24:21 PING[ARCo]: Heartbeat failed to connect to standby 'STAND'. Error is 1
                              2514.

                              Error 12514 17-JAN-2013 13:29:21 PING[ARCo]: Heartbeat failed to connect to standby 'STAND'. Error is 1
                              2514.

                              Error 12514 17-JAN-2013 13:34:22 PING[ARCo]: Heartbeat failed to connect to standby 'STAND'. Error is 1
                              2514.

                              Error 12514 17-JAN-2013 13:39:23 PING[ARCo]: Heartbeat failed to connect to standby 'STAND'. Error is 1
                              2514.

                              Error 12514 17-JAN-2013 13:44:24 PING[ARCo]: Heartbeat failed to connect to standby 'STAND'. Error is 1
                              2514.

                              Error 12514 17-JAN-2013 13:49:24 PING[ARCo]: Heartbeat failed to connect to standby 'STAND'. Error is 1
                              2514.

                              Error 12514 17-JAN-2013 13:54:26 PING[ARCo]: Heartbeat failed to connect to standby 'STAND'. Error is 1
                              2514.


                              SEVERITY ERROR_CODE timestamp MESSAGE
                              --------------- ---------- -------------------- ----------------------------------------------------------------------
                              Error 12514 17-JAN-2013 13:59:26 PING[ARCo]: Heartbeat failed to connect to standby 'STAND'. Error is 1
                              2514.

                              Error 12514 17-JAN-2013 14:04:26 PING[ARCo]: Heartbeat failed to connect to standby 'STAND'. Error is 1
                              2514.

                              Error 12514 17-JAN-2013 14:09:27 PING[ARCo]: Heartbeat failed to connect to standby 'STAND'. Error is 1
                              2514.

                              Error 12514 17-JAN-2013 14:14:27 PING[ARCo]: Heartbeat failed to connect to standby 'STAND'. Error is 1
                              2514.

                              Error 12514 17-JAN-2013 14:19:27 PING[ARCo]: Heartbeat failed to connect to standby 'STAND'. Error is 1
                              2514.

                              Error 12514 17-JAN-2013 14:24:27 PING[ARCo]: Heartbeat failed to connect to standby 'STAND'. Error is 1
                              2514.

                              Error 12514 17-JAN-2013 14:29:28 PING[ARCo]: Heartbeat failed to connect to standby 'STAND'. Error is 1
                              2514.

                              Error 12514 17-JAN-2013 14:34:29 PING[ARCo]: Heartbeat failed to connect to standby 'STAND'. Error is 1
                              2514.

                              Error 12514 17-JAN-2013 14:39:29 PING[ARCo]: Heartbeat failed to connect to standby 'STAND'. Error is 1
                              2514.

                              Error 12514 17-JAN-2013 14:44:29 PING[ARCo]: Heartbeat failed to connect to standby 'STAND'. Error is 1
                              2514.

                              Error 12514 17-JAN-2013 14:49:29 PING[ARCo]: Heartbeat failed to connect to standby 'STAND'. Error is 1
                              2514.

                              Error 12514 17-JAN-2013 14:54:30 PING[ARCo]: Heartbeat failed to connect to standby 'STAND'. Error is 1
                              2514.

                              Error 12514 17-JAN-2013 14:59:30 PING[ARCo]: Heartbeat failed to connect to standby 'STAND'. Error is 1
                              2514.

                              Error 12514 17-JAN-2013 15:04:31 PING[ARCo]: Heartbeat failed to connect to standby 'STAND'. Error is 1
                              2514.

                              Error 12514 17-JAN-2013 15:09:31 PING[ARCo]: Heartbeat failed to connect to standby 'STAND'. Error is 1
                              2514.

                              Error 12514 17-JAN-2013 15:14:32 PING[ARCo]: Heartbeat failed to connect to standby 'STAND'. Error is 1
                              2514.

                              SEVERITY ERROR_CODE timestamp MESSAGE
                              --------------- ---------- -------------------- ----------------------------------------------------------------------

                              Error 12514 17-JAN-2013 15:19:32 PING[ARCo]: Heartbeat failed to connect to standby 'STAND'. Error is 1
                              2514.

                              Error 12514 17-JAN-2013 15:24:32 PING[ARCo]: Heartbeat failed to connect to standby 'STAND'. Error is 1
                              2514.

                              Error 12514 17-JAN-2013 15:29:33 PING[ARCo]: Heartbeat failed to connect to standby 'STAND'. Error is 1
                              2514.

                              Error 12514 17-JAN-2013 15:34:33 PING[ARCo]: Heartbeat failed to connect to standby 'STAND'. Error is 1
                              2514.

                              Error 12514 17-JAN-2013 15:39:33 PING[ARCo]: Heartbeat failed to connect to standby 'STAND'. Error is 1
                              2514.

                              Error 12514 17-JAN-2013 15:44:33 PING[ARCo]: Heartbeat failed to connect to standby 'STAND'. Error is 1
                              2514.

                              Error 12514 17-JAN-2013 15:49:34 PING[ARCo]: Heartbeat failed to connect to standby 'STAND'. Error is 1
                              2514.

                              Error 12514 17-JAN-2013 15:54:34 PING[ARCo]: Heartbeat failed to connect to standby 'STAND'. Error is 1
                              2514.

                              Error 12514 17-JAN-2013 15:59:34 PING[ARCo]: Heartbeat failed to connect to standby 'STAND'. Error is 1
                              2514.

                              Error 12514 17-JAN-2013 16:04:35 PING[ARCo]: Heartbeat failed to connect to standby 'STAND'. Error is 1
                              2514.

                              Error 12514 17-JAN-2013 16:09:37 PING[ARCo]: Heartbeat failed to connect to standby 'STAND'. Error is 1
                              2514.

                              Error 12514 17-JAN-2013 16:14:37 PING[ARCo]: Heartbeat failed to connect to standby 'STAND'. Error is 1
                              2514.

                              Error 12514 17-JAN-2013 16:19:37 PING[ARCo]: Heartbeat failed to connect to standby 'STAND'. Error is 1
                              2514.

                              Error 12514 17-JAN-2013 16:24:37 PING[ARCo]: Heartbeat failed to connect to standby 'STAND'. Error is 1
                              2514.

                              Error 12514 17-JAN-2013 16:29:38 PING[ARCo]: Heartbeat failed to connect to standby 'STAND'. Error is 1
                              2514.

                              SQL> select ds.dest_id id
                              2 , ad.status
                              3 , ds.database_mode db_mode
                              4 , ad.archiver type
                              5 , ds.recovery_mode
                              6 , ds.protection_mode
                              7 , ds.standby_logfile_count "SRLs"
                              8 , ds.standby_logfile_active active
                              9 , ds.archived_seq#
                              10 from v$archive_dest_status ds
                              11 , v$archive_dest ad
                              12 where ds.dest_id = ad.dest_id
                              13 and ad.status != 'INACTIVE'
                              14 order by
                              15 ds.dest_id;

                              ID STATUS DB_MODE TYPE RECOVERY_MODE PROTECTION_MODE SRLs ACTIVE ARCHIVED_SEQ#
                              --- --------- --------------- ---- -------------------- -------------------- ---- ------ -------------
                              1 VALID OPEN ARCH IDLE MAXIMUM PERFORMANCE 0 0 162
                              2 ERROR UNKNOWN LGWR IDLE MAXIMUM PERFORMANCE 0 0 0
                              SQL> column FILE_TYPE format a20
                              SQL> col name format a60
                              SQL> select name
                              2 , floor(space_limit / 1024 / 1024) "Size MB"
                              3 , ceil(space_used / 1024 / 1024) "Used MB"
                              4 from v$recovery_file_dest
                              5 order by name;

                              NAME Size MB Used MB
                              ------------------------------------------------------------ ---------- ----------
                              D:\Oracle11g\app\Administrator\flash_recovery_area 4096 2362
                              SQL> spool off

                              dg_standby_output.log has below entries

                              SQL> set feedback off
                              SQL> set trimspool on
                              SQL> set line 500
                              SQL> set pagesize 50
                              SQL> set linesize 200
                              SQL> column name for a30
                              SQL> column display_value for a30
                              SQL> col value for a10
                              SQL> col PROTECTION_MODE for a15
                              SQL> col DATABASE_Role for a15
                              SQL> SELECT name, display_value FROM v$parameter WHERE name IN ('db_name','db_unique_name','log_archive_config','log_archive_dest_2','log_archive_dest_state_2','fal_client','fal_server','standby_file_management','standby_archive_dest','db_file_name_convert','log_file_name_convert','remote_login_passwordfile','local_listener','dg_broker_start','dg_broker_config_file1','dg_broker_config_file2','log_archive_max_processes') order by name;

                              NAME DISPLAY_VALUE
                              ------------------------------ ------------------------------
                              db_file_name_convert D:\Oracle11g\app\Administrator
                              \oradata\PROD, D:\Oracle11g\ap
                              p\Administrator\oradata\STAND

                              db_name PROD
                              db_unique_name STAND
                              dg_broker_config_file1 D:\ORACLE11G\APP\ADMINISTRATOR
                              \PRODUCT\11.1.0\DB_1\DATABASE\
                              DR1STAND.DAT

                              dg_broker_config_file2 D:\ORACLE11G\APP\ADMINISTRATOR
                              \PRODUCT\11.1.0\DB_1\DATABASE\
                              DR2STAND.DAT

                              dg_broker_start FALSE
                              fal_client STAND
                              fal_server PROD
                              local_listener
                              log_archive_config DG_CONFIG=(PROD,STAND)
                              log_archive_dest_2 SERVICE=PROD LGWR ASYNC
                              VALID_FOR=(ONLINE_LOGFILES,PRI
                              MARY_ROLE)
                              DB_UNIQUE_NAME=PROD

                              log_archive_dest_state_2 ENABLE
                              log_archive_max_processes 30
                              log_file_name_convert D:\Oracle11g\app\Administrator
                              \oradata\PROD, D:\Oracle11g\ap
                              p\Administrator\oradata\STAND,
                              D:\Oracle11g\app\Administrato
                              r\flash_recovery_area\PROD\ONL
                              INELOG, D:\Oracle11g\app\Admin
                              istrator\flash_recovery_area\S
                              TAND\ONLINELOG

                              remote_login_passwordfile EXCLUSIVE
                              standby_archive_dest %ORACLE_HOME%\RDBMS
                              standby_file_management AUTO
                              SQL> col name for a10
                              SQL> col DATABASE_ROLE for a10
                              SQL> SELECT name,db_unique_name,protection_mode,DATABASE_ROLE,OPEN_MODE from v$database;

                              NAME DB_UNIQUE_NAME PROTECTION_MODE DATABASE_R OPEN_MODE
                              ---------- ------------------------------ --------------- ---------- ----------
                              PROD STAND MAXIMUM PERFORM PHYSICAL S MOUNTED
                              ANCE TANDBY

                              SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;
                              SQL> select process, status,thread#,sequence# from v$managed_standby;

                              PROCESS STATUS THREAD# SEQUENCE#
                              --------- ------------ ---------- ----------
                              ARCH CONNECTED 0 0
                              ARCH CONNECTED 0 0
                              ARCH CONNECTED 0 0
                              ARCH CONNECTED 0 0
                              ARCH CONNECTED 0 0
                              ARCH CONNECTED 0 0
                              ARCH CONNECTED 0 0
                              ARCH CONNECTED 0 0
                              ARCH CONNECTED 0 0
                              ARCH CONNECTED 0 0
                              ARCH CONNECTED 0 0
                              ARCH CONNECTED 0 0
                              ARCH CONNECTED 0 0
                              ARCH CONNECTED 0 0
                              ARCH CONNECTED 0 0
                              ARCH CONNECTED 0 0
                              ARCH CONNECTED 0 0
                              ARCH CONNECTED 0 0
                              ARCH CONNECTED 0 0
                              ARCH CONNECTED 0 0
                              ARCH CONNECTED 0 0
                              ARCH CONNECTED 0 0
                              ARCH CONNECTED 0 0
                              ARCH CONNECTED 0 0
                              ARCH CONNECTED 0 0
                              ARCH CONNECTED 0 0
                              ARCH CONNECTED 0 0
                              ARCH CONNECTED 0 0
                              ARCH CONNECTED 0 0
                              ARCH CONNECTED 0 0
                              SQL> col name for a30
                              SQL> select * from v$dataguard_stats;

                              NAME VALUE UNIT TIME_COMPUTED
                              ------------------------------ ---------- ------------------------------ ------------------------------
                              apply finish time day(2) to second(1) interval
                              apply lag day(2) to second(0) interval
                              estimated startup time 28 second
                              standby has been open N
                              transport lag day(2) to second(0) interval
                              SQL> select * from v$archive_gap;
                              SQL> col name format a60
                              SQL> select name
                              2 , floor(space_limit / 1024 / 1024) "Size MB"
                              3 , ceil(space_used / 1024 / 1024) "Used MB"
                              4 from v$recovery_file_dest
                              5 order by name;

                              NAME Size MB Used MB
                              ------------------------------------------------------------ ---------- ----------
                              D:\Oracle11g\app\Administrator\flash_recovery_area 4096 0
                              SQL> spool off
                              • 27. Re: not able to get data of primary in standby database (configured dataguard)
                                CKPT
                                As of now issue is below
                                Error 12514 17-JAN-2013 16:24:37 PING[ARCo]: Heartbeat failed to connect to standby 'STAND'. Error is 12514.
                                Error 12514 17-JAN-2013 16:29:38 PING[ARCo]: Heartbeat failed to connect to standby 'STAND'. Error is 12514.
                                That is "ORA-12514:     TNS:listener does not currently know of service requested in connect descriptor", means there are no services registered to the listener on standby system.

                                post from standby

                                $lsnrctl status
                                $lsnrctl services

                                post listener.ora , tnsnames.ora files.
                                Have you used static listener entry?
                                • 28. Re: not able to get data of primary in standby database (configured dataguard)
                                  974427
                                  listener.ora
                                  LISTPROD =
                                  (DESCRIPTION_LIST =
                                  (DESCRIPTION =
                                  (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.0.98)(PORT = 1523))
                                  )
                                  )

                                  SID_LIST_LISTENER =
                                  (SID_LIST =
                                  (SID_DESC =
                                  (GLOBAL_DBNAME = PROD.WORLD)
                                  (ORACLE_HOME = D:\Oracle11g\app\Administrator\product\11.1.0)
                                  (SID_NAME = PROD)
                                  )
                                  (SID_DESC =
                                  (GLOBAL_DBNAME = AUX.WORLD)
                                  (ORACLE_HOME = D:\Oracle11g\app\Administrator\product\11.1.0)
                                  (SID_NAME = AUX)
                                  )
                                  )

                                  LISTSTAND =
                                  (DESCRIPTION_LIST =
                                  (DESCRIPTION =
                                  (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.0.98)(PORT = 1522))
                                  )
                                  )

                                  LISTTEST =
                                  (DESCRIPTION_LIST =
                                  (DESCRIPTION =
                                  (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.0.98)(PORT = 1521))
                                  )
                                  )

                                  tnsnames.ora
                                  STAND =
                                  (DESCRIPTION =
                                  (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.0.98)(PORT = 1522))
                                  (CONNECT_DATA =
                                  (SERVER = DEDICATED)
                                  (SERVICE_NAME = STAND)
                                  )
                                  )

                                  PROD =
                                  (DESCRIPTION =
                                  (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.0.98)(PORT = 1522))
                                  (CONNECT_DATA =
                                  (SERVER = DEDICATED)
                                  (SERVICE_NAME = PROD)
                                  )
                                  )
                                  TOTEST=
                                  (DESCRIPTION =
                                  (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.0.98)(PORT = 1521))
                                  (CONNECT_DATA =
                                  (SERVER = DEDICATED)
                                  (SERVICE_NAME = TEST)
                                  )
                                  )

                                  ######################
                                  C:\Users\Administrator>tnsping PROd

                                  TNS Ping Utility for 64-bit Windows: Version 11.1.0.6.0 - Production on 15-JAN-2
                                  013 17:59:05

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

                                  Used parameter files:
                                  D:\Oracle11g\app\Administrator\product\11.1.0\db_1\network\admin\sqlnet.ora


                                  Used TNSNAMES adapter to resolve the alias
                                  Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.0
                                  .98)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = PROD)))
                                  OK (0 msec)

                                  C:\Users\Administrator>tnsping stand

                                  TNS Ping Utility for 64-bit Windows: Version 11.1.0.6.0 - Production on 16-JAN-2
                                  013 15:47:02

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

                                  Used parameter files:
                                  D:\Oracle11g\app\Administrator\product\11.1.0\db_1\network\admin\sqlnet.ora


                                  Used TNSNAMES adapter to resolve the alias
                                  Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.0
                                  .98)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = STAND)))

                                  OK (0 msec)

                                  C:\Users\Administrator>tnsping PROd

                                  TNS Ping Utility for 64-bit Windows: Version 11.1.0.6.0 - Production on 16-JAN-2
                                  013 15:47:04

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

                                  Used parameter files:
                                  D:\Oracle11g\app\Administrator\product\11.1.0\db_1\network\admin\sqlnet.ora


                                  Used TNSNAMES adapter to resolve the alias
                                  Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.0
                                  .98)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = PROD)))
                                  OK (10 msec)

                                  C:\Users\Administrator>lsnrctl status

                                  LSNRCTL for 64-bit Windows: Version 11.1.0.6.0 - Production on 17-JAN-2013 17:01
                                  :55

                                  Copyright (c) 1991, 2007, Oracle. All rights reserved.

                                  Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
                                  STATUS of the LISTENER
                                  ------------------------
                                  Alias LISTTEST
                                  Version TNSLSNR for 64-bit Windows: Version 11.1.0.6.0 - Produ
                                  ction
                                  Start Date 13-JAN-2013 03:50:24
                                  Uptime 4 days 13 hr. 11 min. 35 sec
                                  Trace Level off
                                  Security ON: Local OS Authentication
                                  SNMP OFF
                                  Listener Parameter File D:\Oracle11g\app\Administrator\product\11.1.0\db_1\net
                                  work\admin\listener.ora
                                  Listener Log File d:\oracle11g\app\administrator\diag\tnslsnr\A960M\list
                                  test\alert\log.xml
                                  Listening Endpoints Summary...
                                  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.0.98)(PORT=1521)))
                                  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=8080))(Presentation=
                                  HTTP)(Session=RAW))
                                  Services Summary...
                                  Service "STAND_XPT" has 1 instance(s).
                                  Instance "stand", status READY, has 1 handler(s) for this service...
                                  Service "XEXDB" has 1 instance(s).
                                  Instance "xe", status READY, has 1 handler(s) for this service...
                                  Service "XE_XPT" has 1 instance(s).
                                  Instance "xe", status READY, has 1 handler(s) for this service...
                                  Service "prod" has 1 instance(s).
                                  Instance "prod", status READY, has 1 handler(s) for this service...
                                  Service "prodXDB" has 1 instance(s).
                                  Instance "prod", status READY, has 1 handler(s) for this service...
                                  Service "prod_XPT" has 1 instance(s).
                                  Instance "prod", status READY, has 1 handler(s) for this service...
                                  Service "stand" has 1 instance(s).
                                  Instance "stand", status READY, has 1 handler(s) for this service...
                                  Service "xe" has 1 instance(s).
                                  Instance "xe", status READY, has 1 handler(s) for this service...
                                  The command completed successfully

                                  C:\Users\Administrator>lsnrctl services

                                  LSNRCTL for 64-bit Windows: Version 11.1.0.6.0 - Production on 17-JAN-2013 17:02
                                  :31

                                  Copyright (c) 1991, 2007, Oracle. All rights reserved.

                                  Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
                                  Services Summary...
                                  Service "STAND_XPT" has 1 instance(s).
                                  Instance "stand", status READY, has 1 handler(s) for this service...
                                  Handler(s):
                                  "DEDICATED" established:0 refused:0 state:ready
                                  LOCAL SERVER
                                  Service "XEXDB" has 1 instance(s).
                                  Instance "xe", status READY, has 1 handler(s) for this service...
                                  Handler(s):
                                  "D000" established:0 refused:0 current:0 max:1002 state:ready
                                  DISPATCHER <machine: A960M, pid: 2960>
                                  (ADDRESS=(PROTOCOL=tcp)(HOST=A960M)(PORT=1034))
                                  Service "XE_XPT" has 1 instance(s).
                                  Instance "xe", status READY, has 1 handler(s) for this service...
                                  Handler(s):
                                  "DEDICATED" established:0 refused:0 state:ready
                                  LOCAL SERVER
                                  Service "prod" has 1 instance(s).
                                  Instance "prod", status READY, has 1 handler(s) for this service...
                                  Handler(s):
                                  "DEDICATED" established:0 refused:0 state:ready
                                  LOCAL SERVER
                                  Service "prodXDB" has 1 instance(s).
                                  Instance "prod", status READY, has 1 handler(s) for this service...
                                  Handler(s):
                                  "D000" established:0 refused:0 current:0 max:1022 state:ready
                                  DISPATCHER <machine: A960M, pid: 9304>
                                  (ADDRESS=(PROTOCOL=tcp)(HOST=A960M)(PORT=49274))
                                  Service "prod_XPT" has 1 instance(s).
                                  Instance "prod", status READY, has 1 handler(s) for this service...
                                  Handler(s):
                                  "DEDICATED" established:0 refused:0 state:ready
                                  LOCAL SERVER
                                  Service "stand" has 1 instance(s).
                                  Instance "stand", status READY, has 1 handler(s) for this service...
                                  Handler(s):
                                  "DEDICATED" established:0 refused:0 state:ready
                                  LOCAL SERVER
                                  Service "xe" has 1 instance(s).
                                  Instance "xe", status READY, has 1 handler(s) for this service...
                                  Handler(s):
                                  "DEDICATED" established:0 refused:0 state:ready
                                  LOCAL SERVER
                                  The command completed successfully
                                  • 29. Re: not able to get data of primary in standby database (configured dataguard)
                                    CKPT
                                    Tnsping will work if listener is up, Can you try connect from primary to standby and vice versa using TNS net service?

                                    Example from Primary:
                                    $sqlplus sys/*****@aux as sysdba

                                    Example from Standby:
                                    $sqlplus sys/*****@prod as sysdba