DATA GUARD MODES AND FAILOVER, SWITCHOVER- II Practical

DATA GUARD MODES AND FAILOVER, SWITCHOVER- II

To change the primary database protection m     ode

    For maximize availability mode.

Standby database (stand) Side:-

$ export ORACLE_SID=stand

$ sqlplus / as sysdba

SQL> startup

SQL> select open_mode,protection_mode,database_role from v$database;

(DATABASE_ROLE=PHYSICAL STANDBY)

Primary database(prod) Side:-

$ export ORACLE_SID=prod

$ sqlplus / as sysdba

SQL> startup

SQL> select open_mode,protection_mode,database_role from v$database;

(DATABASE_ROLE=PRIMARY)

SQL> shut immediate

SQL> exit

~]$ cd $ORACLE_HOME/dbs

dbs]$ vi initprod.ora

log_archive_dest_2='service=tostand lgwr'

:wq! (save & quit)

$ export ORACLE_SID=prod

dbs]$ sqlplus / as sysdba

SQL> startup

SQL> select group#,bytes/1024/1024 from v$log;

Create standby logfile in standby database

Note:- if you change the protection mode , that time you have to create standby

logfile same log group(if primary database have 3 log group) and same size.

Standby database(stand) Side:-

SQL> select GROUP#,bytes/1024/1024 from v$standby_log;        (no rows selected)

SQL> select member from v$logfile;

SQL> alter database add standby logfile group 11 '/u01/azhar/stand/redo11.log' size 100m;

SQL> alter database add standby logfile group 12 '/u01/azhar/stand/redo12.log' size 100m;

SQL> alter database add standby logfile group 13 '/u01/azhar/stand/redo13.log' size 100m;

SQL> select GROUP#,bytes/1024/1024 from v$standby_log;        (GROUP#=11,12,13)

Primary database(prod) Side:-

SQL> shut immediate

SQL> startup mount

SQL> alter database set standby database to maximize availability;

SQL> select open_mode, protection_mode, database_role from v$database;

OPEN_MODE          PROTECTION_MODE               DATABASE_ROLE

MOUNTED                MAXIMUM AVAILABILITY       PRIMARY

Standby database(stand) Side:-

SQL> select open_mode, protection_mode, database_role from v$database;

OPEN_MODE          PROTECTION_MODE                   DATABASE_ROLE

READ ONLY             MAXIMUM PERFORMANCE          PHYSICAL STANDBY

Primary database(prod) Side:-

SQL> alter database open;

SQL> select open_mode, protection_mode, database_role from v$database;

OPEN_MODE          PROTECTION_MODE                   DATABASE_ROLE

READ WRITE           MAXIMUM AVAILABILITY            PRIMARY

Standby database(stand) Side:-

SQL> select open_mode, protection_mode, database_role from v$database;

OPEN_MODE          PROTECTION_MODE                   DATABASE_ROLE

READ ONLY             MAXIMUM AVAILABILITY            PHYSICAL STANDBY

For maximize protection mode.

Primary database(prod) Side:-

SQL> exit

dbs]$ vi initprod.ora

log_archive_dest_2='service=tostand lgwr sync affirm'

:wq! (save & quit)

dbs]$ sqlplus / as sysdba

SQL> select member from v$logfile;

SQL> select group#, bytes/1024/1024 from v$log;                            (GROUP#=4,5,6)

Standby database (stand) Side:-

SQL> select group#, bytes/1024/1024 from v$standby_log;          (GROUP#=11,12,13)

SQL> select member from v$logfile;

Primary database (prod) Side:-

SQL> shut immediate

SQL> startup mount

SQL> alter database set standby database to maximize protection;

SQL> select database_role,protection_mode,open_mode from v$database;

DATABASE_ROLE            PROTECTION_MODE            OPEN_MODE

PRIMARY                             MAXIMUM PROTECTION       MOUNTED

Standby database(stand) Side:-

SQL> select database_role,protection_mode,open_mode from v$database;

DATABASE_ROLE            PROTECTION_MODE              OPEN_MODE

PHYSICAL STANDBY        MAXIMUM AVAILABILITY       READ ONLY

Primary database(prod) Side:-

SQL> alter database open;

SQL> select database_role,protection_mode,open_mode from v$database;

DATABASE_ROLE            PROTECTION_MODE           OPEN_MODE

PRIMARY                             MAXIMUM PROTECTION      READ WRITE

Standby database(stand) Side:-

SQL> select database_role,protection_mode,open_mode from v$database;

DATABASE_ROLE            PROTECTION_MODE            OPEN_MODE

PHYSICAL STANDBY        MAXIMUM PROTECTION       READ ONLY

Primary database(prod) Side:-

SQL> shut immediate

SQL> exit

dbs]$ vi initprod.ora

standby_file_management=auto

log_archive_dest_2='service=tostand '

log_archive_dest_1='location=/u01/home/prod/arch'

db_unique_name=hyd

:wq! (save & quit)

dbs]$ sqlplus / as sysdba

SQL> startup mount

SQL> alter database set standby database to maximize performance;

SQL> select database_role,protection_mode,open_mode from v$database;

DATABASE_ROLE            PROTECTION_MODE                        OPEN_MODE

PRIMARY                             MAXIMUM PERFORMANCE              MOUNTED

Standby database(stand) Side:-

SQL> select database_role,protection_mode,open_mode from v$database;

DATABASE_ROLE            PROTECTION_MODE            OPEN_MODE

PHYSICAL STANDBY        MAXIMUM PROTECTION       READ ONLY

Primary database(prod) Side:-

SQL> alter database open;

SQL> select database_role,protection_mode,open_mode from v$database;

DATABASE_ROLE            PROTECTION_MODE                 OPEN_MODE

PRIMARY                             MAXIMUM PERFORMANCE       READ WRITE

Standby database(stand) Side:-

SQL> select database_role,protection_mode,open_mode from v$database;

DATABASE_ROLE            PROTECTION_MODE                         OPEN_MODE

PHYSICAL STANDBY        MAXIMUM PERFORMANCE               READ ONLY

Switchover:-

Primary database (prod) Side:-

SQL> exit

dbs]$ netmgr(create listener for switchover)                         (service name=prod)

dbs]$ vi initprod.ora

log_archive_dest_3='location=/u01/home/prod/sarch'

:wq! (save & quit)

dbs]$ cd

~]$ cd prod/

prod]$ mkdir sarch

$ sqlplus / as sysdba

SQL> shut immediate

Standby database(stand) Side:-

SQL> exit

dbs]$ vi initstand.ora

log_archive_dest_3='location=/u01/home/stand/parch'

log_archive_dest_2='service=toswitch'

standby_file_management=auto

:wq! (save & quit)

dbs]$ sqlplus / as sysdba

SQL> shut immediate

SQL> exit

dbs]$ netmgr(create TNS)                       (service name=prod)

dbs]$ tnsping toswitch

dbs]$ cd

~]$ cd stand/

stand]$ mkdir parch

$ sqlplus / as sysdba

SQL> startup mount

Primary database(prod) Side:-

prod]$ sqlplus / as sysdba

SQL> startup

SQL> select database_role,protection_mode,open_mode from v$database;

DATABASE_ROLE            PROTECTION_MODE                   OPEN_MODE

PRIMARY                             MAXIMUM PERFORMANCE          READ WRITE

SQL> alter database commit to switchover to physical standby;

SQL> select database_role,protection_mode,open_mode from v$database;

DATABASE_ROLE            PROTECTION_MODE                     OPEN_MODE

PHYSICAL STANDBY        MAXIMUM PERFORMANCE           READ WRITE

Standby database(stand) Side:-

SQL> select database_role,protection_mode,open_mode from v$database;

DATABASE_ROLE            PROTECTION_MODE                     OPEN_MODE

PHYSICAL STANDBY        MAXIMUM PERFORMANCE            READ ONLY

SQL> alter database commit to switchover to primary;

ERROR at line 1: ORA-16139: media recovery required

SQL> alter database recover managed standby database disconnect;

SQL> alter database recover managed standby database cancel;

SQL> alter database commit to switchover to primary;

SQL> select database_role,protection_mode,open_mode from v$database;

DATABASE_ROLE            PROTECTION_MODE                   OPEN_MODE

PRIMARY                             MAXIMUM PERFORMANCE         MOUNTED

Converting physical standby to snapshot standby

Configure FRA (flashback recovery area) at standby side.

Standby database (stand) Side:-

SQL> show parameter db_recovery_file_dest

SQL> alter database recover managed standby database disconnect;

SQL> alter database recover managed standby database cancel;

SQL> alter database convert to snapshot standby;

SQL> select database_role,protection_mode,open_mode from v$database;

DATABASE_ROLE            PROTECTION_MODE                   OPEN_MODE

SNAPSHOT STANDBY       MAXIMUM PERFORMANCE          MOUNTED

SQL> alter database open;

SQL> select database_role,protection_mode,open_mode from v$database;

DATABASE_ROLE            PROTECTION_MODE                   OPEN_MODE

SNAPSHOT STANDBY       MAXIMUM PERFORMANCE          READ WRITE

converting snapshot standby database back to physical standby

Standby database(stand) Side:-

SQL> shut immediate

SQL> startup mount

SQL> alter database convert to physical standby;

SQL> shut immediate

SQL> startup

SQL> select database_role, protection_mode, open_mode from v$database;

DATABASE_ROLE            PROTECTION_MODE                   OPEN_MODE

PHYSICAL STANDBY        MAXIMUM PERFORMANCE          READ ONLY

For failover:-

Primary database(prod) Side:-

SQL> shut abort

Standby database(stand) Side:-

SQL> alter database recover managed standby database finish;

SQL> alter database commit to switchover to primary;

SQL> select database_role, protection_mode, open_mode from v$database;

DATABASE_ROLE            PROTECTION_MODE                   OPEN_MODE

PRIMARY                             MAXIMUM PERFORMANCE          MOUNTED

SQL> alter database open;

SQL> select database_role,protection_mode,open_mode from v$database;

DATABASE_ROLE            PROTECTION_MODE                     OPEN_MODE

PRIMARY                             MAXIMUM PERFORMANCE           READ WRITE

Viwes:-

V$standby_log (to see the standby redo log inforamtion.)