Netsoftmate

Tag: Physical standby to Snapshot standby

  • Convert Physical Standby to Snapshot standby

    Overview:

    This article explain the process of converting Physical Standby in Read Only mode to a Snapshot Standby.Snapshot Standby open DB in Read Write mode for disaster recovery testing.  This is to allow the database in DR site to be accessed by applications and perform tests on how the database could be accessed and application purposes be met during a disaster scenario while the actual primary database is not shutdown completely.

    Environment Details:
    Hostname
    Node1 and Node2
    Operating system
    Linux
    Environment
    Production and Standby
    Oracle Home
    /u01/app/oracle/product/11.2.0/db
    Prerequisites for converting the
    database
    • Ensure
      the FRA is configured.

    SQL> sho parameter recover
    Note:- Snapshot standby need not have Flashback enabled at
    db level but if it is enabled then FRA would have already been configured
    • Ensure the redo transport parameters for the
      primary and redo apply for the standby are configured:

    SQL> set lin 999
    SQL> column NAME format
    a30
    SQL> column VALUE format
    a84
    SQL> select NAME, VALUE
    from v$parameter
    where NAME in
    (‘db_name’, ‘db_unique_name’, ‘control_files’,
    ‘log_archive_config’,’fal_client’, ‘fal_server’,
    ‘log_archive_dest_1’,
    ‘log_archive_dest_2’, ‘log_archive_dest_3’, ‘log_archive_dest_4’,
    ‘log_archive_dest_state_1′,’log_archive_dest_state_2’,‘log_archive_dest_state_3’,
    ‘log_archive_dest_state_4′,’log_file_name_convert’,
    ‘remote_login_passwordfile’);

    • Ensure the standby redo log files are
      configured on the primary and standby database.

    SQL> column member
    format a50
    SQL> SELECT a.group#,
    a.member, b.bytes/1024/1024 bytes_MB     FROM v$logfile a,
    v$log b WHERE a.group# = b.group#     union select a.group#,
    a.member, b.bytes/1024/1024 bytes_MB     from v$logfile a,
    v$standby_log b  WHERE a.group# =
    b.group#;
    Checks on DR Test
    • Run the following query to pull information
      about the database on both Primary and standby

    SQL> set lin 175
    SQL> column NAME format
    a10
    SQL> column LOG_MODE
    format a10
    SQL> column OPEN_MODE
    format a10
    SQL> COLUMN CONTROLFILE_TYPE
    format a16
    SQL> column
    PROTECTION_MODE format a20
    SQL> column
    PROTECTION_LEVEL format a20
    SQL> column
    DATABASE_ROLE format a20
    SQL> column GUARD_STATUS
    format a12
    SQL> column
    DATAGUARD_BROKER format a16
    SQL> select NAME,DATABASE_ROLE,LOG_MODE,CONTROLFILE_TYPE, OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,GUARD_STATUS,FORCE_LOGGING,DATAGUARD_BROKER from v$database;
    • Verifying That the Standby Has Received All
      Redo, you can carry your individual steps.

    On Primary and on
    standby –> To check if the scn’s are close.
    SQL> set numwidth 20
    SQL> select current_scn
    from v$database;
    On Standby –>
    To check if the MRP is running

    SQL> select
    status,process from v$managed_standby where process like ‘%MRP%’;
     STATUS                                          
    PROCESS
    ————————————————
    ————————————
    APPLYING_LOG                                     MRP0
    On standby  –> to Check if all or near to all logs
    are applied

    SQL> SELECT
    SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
    And to check real
    time apply On primary / standby –> It should say “MANAGED REAL TIME
    APPLY” in one of the rows.

    SQL> SELECT
    RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS;
    SQL> SELECT
    CLIENT_PROCESS,PROCESS,SEQUENCE#,STATUS FROM V$MANAGED_STANDBY;
    Compare the
    sequence# from primary to standby query. 
    If the standby is not receiving the current redo, you cannot convert/switchover.
    Note that if your primary is a RAC, you should see multiple LGWR to RFS
    connections, one for each primary thread. You must validate that each primary
    thread is caught up.
    • Check the apply is caught up

    Once determined the
    redo stream is current, ensure all redo has been applied to standby database.
    On the standby
    database:
    SQL> SELECT
    CLIENT_PROCESS,PROCESS,SEQUENCE#,STATUS FROM V$MANAGED_STANDBY;

    Note: if you don’t
    see MRP0, the apply is not running
    To
    convert/switchover, you must start the apply service and wait for it to catch
    up with the current redo stream before starting. If you see the MRP0 line but
    it has a status of WAIT_FOR_GAP, you cannot convert/switchover until the gap
    has been resolved.
    • Verify there are no large Gaps

    Identify the
    current sequence number for each thread on the primary database.
    On the Primary:
    SQL> SELECT
    THREAD#,SEQUENCE#,STATUS FROM V$LOG;
    Verify the target
    physical standby database has applied up to, but not including the logs from
    the primary query. On the standby the following query should be within 1 or 2
    of the primary query result.
    On the Standby
    database:
    SQL> SELECT
    THREAD#, MAX(SEQUENCE#) FROM V$ARCHIVED_LOG     WHERE APPLIED = ‘YES’ AND RESETLOGS_CHANGE# = SELECTRESETLOGS_CHANGE# FROM V$DATABASE_INCARNATION WHERE STATUS =’CURRENT’) GROUP BY THREAD#;
    Convert to Physical
    standby to Snapshot Standby manual way
    •  On standby
      to be converted to snapshot mode.

    Tail the Alert Logs (optional) on all
    instances:
    Locate alert logs by showing database parameter
    background_dump_dest:
    SQL> SHOW PARAMETER background_dump_dest;
    % tail -f /u01/app/diag/rdbms/standby/standby1/trace/alert*
    • Stop MRP

    SQL> alter database recover managed standby
    database cancel;


    Database alerted
    • Record SCN
      and timestamp

    SQL>
    col current_scn format 99999999999999
    SQL>
    select current_scn from v$database;
    •  Manual
      SQLPLUS Convert to snapshot standby:

                    For
    standalone
    SQL> shutdown
    immediate
    SQL> startup
    mount

                    For
    RAC
    $srvctl
    stop database -d DBNAME
    $srvctl
    start database -d DBNAME -o mount
         
    SQL>
    select open_mode, database_role from v$database;

    SQL>
    ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;

    SQL>
    shutdown immediate

    SQL>
    startup mount

    SQL>
    select open_mode, database_role from v$database;

    SQL>
    ALTER DATABASE OPEN;

    SQL> select open_mode, database_role from v$database;

    Convert to 
    Snapshot Standby back to Physical standby manual way

    For RAC

    $srvctl stop database -d DBNAME
    $srvctl startup database -d DBNAME
    -o mount
    SQL> ALTER
    DATABASE CONVERT TO PHYSICAL STANDBY;

    srvctl stop database -d DBNAME
    For Standalone
    SQL> shutdown immediate

    SQL> startup mount

    SQL> ALTER DATABASE CONVERT TO
    PHYSICAL STANDBY;

    SQL> shutdown immediate
    Restart database
    For standalone
      SQL> startup mount

    For RAC
    srvctl start database -d DBNAME
    -o mount
    • Start MRP and open read only

    SQL> alter
    database open read only;

    SQL> alter
    database recover managed standby database using current logfile disconnect;
    •  Perform the Physical standby check out

     On Primary:

    Check the alert log of primary database and tail the alert.
    Check the archive error destination
    sql>col
    DEST_NAME for a40
    sql>set
    lines 200
    sql>select
    DEST_ID,DEST_NAME,STATUS,ERROR from v$archive_dest;
    Check the current archive log (sequence)
    sql>select thread#,max(sequence#) from v$log where
    status=’CURRENT’ group by thread# order by 1;
    On Standby:
    Check the alert log of standby database and tail the alert.
    Check the current archive log (sequence) applied.
    sql>select thread#,max(sequence#) from
    v$archived_log where applied=’YES’ group by thread# order by 1;
    Check the status of MRP
    $ps -ef |grep mrp
    sql>select PROCESS,THREAD#,SEQUENCE#,STATUS from
    v$managed_standby where process=’MRP0′;
    PROCESS                        THREAD#  SEQUENCE# STATUS
    —————————
    ———- ———- ————————————
    MRP0                                 1     279486 WAIT_FOR_LOG

    Conclusion: 

    From this article we have learnt how to convert Physical standby database to Snapshot standby database using manual method.