This discussion is archived
8 Replies Latest reply: Dec 15, 2012 8:19 AM by 868234 RSS

Physical stand by configuration

868234 Newbie
Currently Being Moderated
Hi Experts,

Please help me with the below issue:


1) I have configured a physical stand by database on windows with oracle 10g. It is working and redo apply is enable at mount stage. Now here on mount stage i have to run manually commands to check reports like.. redo applied, pending, status etc. I want to do it with job. My requirement is to configure all queries in a job so that i can receive auto report in my mail every two hours. Please check and help.

2) We do servers shutdown in night and up in the morning. When i up my physical stand by server and check the status it is in open mode. I have to shut and start again on mount stage and have to start redo apply again by command firing. I need to maintain it with a script so that in the morning when my physical standby server goes up it will auto come at mount stage and redo apply should enable auto and after that i receive reports of synchronization with primary server in my mail as per point no. one. Please help.
  • 1. Re: Physical stand by configuration
    SHANOJ Newbie
    Currently Being Moderated
    +1) I have configured a physical stand by database on windows with oracle 10g. It is working and redo apply is enable at mount stage. Now here on mount stage i have to run manually commands to check reports like.. redo applied, pending, status etc. I want to do it with job. My requirement is to configure all queries in a job so that i can receive auto report in my mail every two hours. Please check and help.+

    Primary:

    SETEP -1
    ========

    LOGIN TO THE PRIMIREY SERVER

    $ su - orapr1
    Password:

    STEP -2
    =======

    GET THE SEQUENCE MAX FROM V$LOG_HISTORY

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

    MAX(SEQUENCE#)
    --------------
    76968

    SQL> alter system switch logfile;

    System altered.

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

    MAX(SEQUENCE#)
    --------------
    76969

    SQL> exit

    =========================================

    ON STANDBY SERVER:

    $ ps -ef|grep pmon
    oratst 2978 1 0 Sep 08 ? 147:34 ora_pmon_amantst
    oracle 3039 1 0 Sep 08 ? 137:34 ora_pmon_airman
    e460269 16109 16104 0 18:54:44 pts/1 0:00 grep pmon

    $ su - oracle

    Password:
    mesg: cannot change mode

    $ sqlplus

    SQL*Plus: Release 10.2.0.3.0 - Production on Thu May 17 18:55:10 2012

    Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

    Enter user-name: /as sysdba

    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
    With the Partitioning, OLAP and Data Mining options

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

    MAX(SEQUENCE#)
    --------------
    8548

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

    MAX(SEQUENCE#)
    --------------
    8549

    SQL> select * from v$archive_gap;
    SQL> select sequence#, archived, applied, status from v$archived_log;


    http://shanojkumar.wordpress.com/2012/05/23/oracle-best-practice-primary-and-standby-archive-crosscheck/


    +2) We do servers shutdown in night and up in the morning. When i up my physical stand by server and check the status it is in open mode. I have to shut and start again on mount stage and have to start redo apply again by command firing. I need to maintain it with a script so that in the morning when my physical standby server goes up it will auto come at mount stage and redo apply should enable auto and after that i receive reports of synchronization with primary server in my mail as per point no. one. Please help.+




    Temporarily disabling the log shipping to standby database

    $ sqlplus

    SQL*Plus: Release 10.2.0.3.0 - Production on Wed Jul 25 21:07:57 2012

    Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

    Enter user-name: /as sysdba

    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
    With the Partitioning, OLAP and Data Mining options


    SQL> select name,open_mode from v$database;

    NAME OPEN_MODE
    --------- ----------
    AIRMAN READ WRITE

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

    MAX(SEQUENCE#)
    --------------
    8941

    SQL> alter system switch logfile;

    System altered.

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

    MAX(SEQUENCE#)
    --------------
    8942

    SQL> select status, DEST_NAME, DESTINATION from v$archive_dest where status = 'VALID';

    STATUS
    ---------
    DEST_NAME
    --------------------------------------------------------------------------------
    DESTINATION
    --------------------------------------------------------------------------------
    VALID
    LOG_ARCHIVE_DEST_1
    /u01/oradata/airman/archive

    VALID
    LOG_ARCHIVE_DEST_2
    airman_sj

    STATUS
    ---------
    DEST_NAME
    --------------------------------------------------------------------------------
    DESTINATION
    --------------------------------------------------------------------------------


    SQL> show parameter LOG_ARCHIVE_DEST_2

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    log_archive_dest_2 string SERVICE=airman_sj LGWR ASYNC V
    ALID_FOR=(ONLINE_LOGFILE, PRIM
    ARY_ROLE) db_unique_name=airma
    n_sj

    SQL> show parameter log_archive

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    log_archive_config string DG_CONFIG=(airman_kj,airman_sj
    )
    log_archive_dest string
    log_archive_dest_1 string LOCATION=/u01/oradata/airman/a
    rchive VALID_FOR=(ALL_LOGFILES
    , ALL_ROLES) db_unique_name=ai
    rman_kj
    log_archive_dest_10 string
    log_archive_dest_2 string SERVICE=airman_sj LGWR ASYNC V
    ALID_FOR=(ONLINE_LOGFILE, PRIM
    ARY_ROLE) db_unique_name=airma

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    n_sj
    log_archive_dest_3 string
    log_archive_dest_4 string
    log_archive_dest_5 string
    log_archive_dest_6 string
    log_archive_dest_7 string
    log_archive_dest_8 string
    log_archive_dest_9 string
    log_archive_dest_state_1 string enable
    log_archive_dest_state_10 string enable
    log_archive_dest_state_2 string ENABLE
    log_archive_dest_state_3 string enable
    log_archive_dest_state_4 string enable
    log_archive_dest_state_5 string enable
    log_archive_dest_state_6 string enable
    log_archive_dest_state_7 string enable
    log_archive_dest_state_8 string enable
    log_archive_dest_state_9 string enable
    log_archive_duplex_dest string
    log_archive_format string %t_%s_%r.arc
    log_archive_local_first boolean TRUE
    log_archive_max_processes integer 2
    log_archive_min_succeed_dest integer 1
    log_archive_start boolean FALSE
    log_archive_trace integer 0


    SQL> alter system set log_archive_dest_state_2=defer scope=both;

    System altered.

    SQL> show parameter log_archive_dest_state_2

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    log_archive_dest_state_2 string DEFER

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

    MAX(SEQUENCE#)
    --------------
    8942

    SQL> alter system switch logfile;

    System altered.

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

    MAX(SEQUENCE#)
    --------------
    8943

    SQL> alter system switch logfile;

    System altered.

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

    MAX(SEQUENCE#)
    --------------
    8944

    SQL> alter system switch logfile;

    System altered.

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

    MAX(SEQUENCE#)
    --------------
    8945
  • 2. Re: Physical stand by configuration
    719669 Newbie
    Currently Being Moderated
    Operating System on which the Database is running ?
  • 3. Re: Physical stand by configuration
    SHANOJ Newbie
    Currently Being Moderated
    http://shrikantrao.wordpress.com/2011/08/03/script-to-check-physical-standby-in-sync-with-the-primary/
  • 4. Re: Physical stand by configuration
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    Assuming Linux / Unix :
    My requirement is to configure all queries in a job so that i can receive auto report in my mail every two hours
    You have the commands when you say "i have to run manually commands to check reports like.. redo applied, pending, status etc."
    So you need to store those commands in an SQL script that spools to a log file. Have the SQL script executed by a shell script which then emails the spool file to you.
    When i up my physical stand by server and check the status it is in open mode
    Apparently, you have enabled autostartup of the database via /etc/oratab (the third field is set to 'Y' to autostart).
    Disable the autostart and write an init script to have it STARTUP NOMOUNT, ALTER DATABASE MOUNT STANDBY DATABASE, RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION


    Hemant K Chitale
  • 5. Re: Physical stand by configuration
    XBOX Newbie
    Currently Being Moderated
    Hi,

    FOR PHYSICAL STANDBY STARTUP:-

    Since you have physical standby database on windows, just create Oracle service using oradim utility using -startmode=manual.
    After server starts u can run/schedule script, which will do following tasks:-
    1. Startup nomount
    2. Alter database mount standby database
    3. Recover managed standby database disconnect from session.

    FOR STANDBY SYNC CHECK:-

    You can add this in same script or you can run separate script for sync check

    set ORACLE_SID
    set ORACLE_HOME
    sqlplus / as sysdba

    1. Check archivelog sequence using this commend on Primary database,
    SQL> select max(sequence#) from v$archived_log;
    2. Check archivelog sequence on standby database ,
    SQL> Select max(sequence#) from v$archived_log where applied='YES';

    Regards,
  • 6. Re: Physical stand by configuration
    868234 Newbie
    Currently Being Moderated
    windows 2003 r2 server
  • 7. Re: Physical stand by configuration
    868234 Newbie
    Currently Being Moderated
    Hi, Thanks 4 ur reply.. It really helpful. I have implemented with the help of your solution. It's working fine. Thanks again.
  • 8. Re: Physical stand by configuration
    868234 Newbie
    Currently Being Moderated
    Hi, Thanks 4 ur link.. It really helpful. I have implemented with the help of your scripts. It's working fine. Thanks again.

Legend

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