This discussion is archived
1 2 3 Previous Next 31 Replies Latest reply: Jan 17, 2013 4:36 AM by CKPT Go to original post RSS
  • 15. Re: not able to get data of primary in standby database (configured dataguard)
    974427 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points