Sunday, March 12, 2017

Scenario’s in DATAGAURD failover and switchover


Hai, in this blog I will explain how to perform FAILOVER and SWITCHOVER.
Now lets perform Scenario’s in DATAGUARD
1.  FAILOVER
2.  SWITCHOVER
3.  UPDATE VALES

List of Topics (Linux, Database, RAC, EBS)


Scenario-1 : FAIL OVER 
PRIMARY DB (192.168.1.11)
SQL>
SQL> select NAME,DATABASE_ROLE,DATAGUARD_BROKER from v$database;

NAME   DATABASE_ROLE    DATAGUAR
------ ---------------- --------
DELL   PRIMARY          ENABLED

SQL>
SQL> set linesize 100;
SQL> col name for a6;
SQL> col DB_UNIQUE_NAME for a10;
SQL> select NAME,DB_UNIQUE_NAME,CONTROLFILE_TYPE,OPEN_MODE,DATABASE_ROLE,PROTECTION_MODE, DATAGUARD_BROKER from v$database;

SQL> select NAME,CONTROLFILE_TYPE,OPEN_MODE,DATABASE_ROLE,PROTECTION_MODE from v$database;

NAME   CONTROL OPEN_MODE            DATABASE_ROLE    PROTECTION_MODE
------ ------- -------------------- ---------------- --------------------
DELL   CURRENT READ WRITE           PRIMARY          MAXIMUM PERFORMANCE

SQL>

==============================================================================================================
Open a new window and connect to DGMGRL at PRIMARY
[root@rac1 ~]# su - oracle
[oracle@rac1 ~]$ . dell.env
[oracle@rac1 ~]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/sys
Connected.
DGMGRL> show configuration;

Configuration - dell

  Protection Mode: MaxPerformance
  Databases:
    dell_live - Primary database
    dell_stby - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL>
DGMGRL>


STANDBY DB (192.168.1.12)
SQL>
SQL> select NAME,DATABASE_ROLE,DATAGUARD_BROKER from v$database;

NAME      DATABASE_ROLE    DATAGUAR
--------- ---------------- --------
DELL      PHYSICAL STANDBY ENABLED

SQL>
SQL> set linesize 100;
SQL> col name for a6;col DB_UNIQUE_NAME for a8
SQL> col DB_UNIQUE_NAME for a10;
SQL> select NAME,DB_UNIQUE_NAME,CONTROLFILE_TYPE,OPEN_MODE,DATABASE_ROLE,PROTECTION_MODE, DATAGUARD_BROKER from v$database;

SQL> select NAME,CONTROLFILE_TYPE,OPEN_MODE,DATABASE_ROLE,PROTECTION_MODE from v$database;

NAME   CONTROL OPEN_MODE            DATABASE_ROLE    PROTECTION_MODE
------ ------- -------------------- ---------------- --------------------
DELL   STANDBY MOUNTED              PHYSICAL STANDBY MAXIMUM PERFORMANCE

SQL>
==============================================================================================================
Open a new window and connect to DGMGRL at STANDBY

[root@rac2 ~]# su - oracle
[oracle@rac2 ~]$ . dell.env
[oracle@rac2 ~]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/sys
Connected.
DGMGRL> show configuration

Configuration - dell

  Protection Mode: MaxPerformance
  Databases:
    dell_live - Primary database
    dell_stby - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL>

Now let’s perform a failover to STANDBY Server
DGMGRL> failover to dell_stby;
Performing failover NOW, please wait...
Failover succeeded, new primary is "dell_stby"
DGMGRL>

==============================================================================================================
[oracle@rac2 ~]$
[oracle@rac2 ~]$ sqlplus / as sysdba
SQL> select NAME,DB_UNIQUE_NAME,CONTROLFILE_TYPE,OPEN_MODE,DATABASE_ROLE,PROTECTION_MODE, DATAGUARD_BROKER from v$database;

SQL> select NAME,CONTROLFILE_TYPE,OPEN_MODE,DATABASE_ROLE,PROTECTION_MODE from v$database;

NAME      CONTROL OPEN_MODE            DATABASE_ROLE       DATAGUAR
--------- ------- -------------------- ---------------- --------
DELL      CURRENT READ WRITE           PRIMARY    ENABLED

SQL>


PRIMARY DB (192.168.1.11)
[oracle@rac1 ~]$
[oracle@rac1 ~]$ sqlplus / as sysdba
SQL> select NAME,DB_UNIQUE_NAME,CONTROLFILE_TYPE,OPEN_MODE,DATABASE_ROLE,PROTECTION_MODE, DATAGUARD_BROKER from v$database;

SQL> select NAME,CONTROLFILE_TYPE,OPEN_MODE,DATABASE_ROLE,PROTECTION_MODE from v$database;

NAME   CONTROL OPEN_MODE            DATABASE_ROLE    DATAGUAR
------ ------- -------------------- ---------------- --------
DELL   CURRENT READ WRITE           PRIMARY          DISABLED

SQL>
SQL>
SQL> shut immediate
SQL> startup mount


STANDBY DB (192.168.1.12)
DGMGRL>
DGMGRL> show configuration;

Configuration - dell

  Protection Mode: MaxPerformance
  Databases:
    dell_stby - Primary database
    dell_live - Physical standby database (disabled)
      ORA-16661: the standby database needs to be reinstated

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

Now we have to reinstate OLD-PRIMARY
DGMGRL>
DGMGRL> reinstate database dell_live;
Reinstating database "dell_live", please wait...
Operation requires shutdown of instance "DELL" on database "dell_live"
Shutting down instance "DELL"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "DELL" on database "dell_live"
Starting instance "DELL"...
ORACLE instance started.
Database mounted.
Continuing to reinstate database "dell_live" ...
Reinstatement of database "dell_live" succeeded
DGMGRL>
DGMGRL>
DGMGRL> show configuration

Configuration - dell

  Protection Mode: MaxPerformance
  Databases:
    dell_stby - Primary database
    dell_live - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL>
Reinstate of OLD_PRIMARY completed successfully.

==============================================================================================================
If your reinstate completed with error as follows “ORA-01017”
Then simply once again mount the OLD-PRIMARY and reinstate
DGMGRL> reinstate database dell_live;
Reinstating database "dell_live", please wait...
Operation requires shutdown of instance "DELL" on database "dell_live"
Shutting down instance "DELL"...
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

Please complete the following steps and reissue the REINSTATE command:
        shut down instance "DELL" of database "dell_live"
        start up and mount instance "DELL" of database "dell_live"

DGMGRL>


PRIMARY DB (192.168.1.11)
SQL>
SQL> col name for a6;
SQL> select NAME,DB_UNIQUE_NAME,CONTROLFILE_TYPE,OPEN_MODE,DATABASE_ROLE,PROTECTION_MODE, DATAGUARD_BROKER from v$database;

SQL> select NAME,CONTROLFILE_TYPE,OPEN_MODE,DATABASE_ROLE,DATAGUARD_BROKER from v$database;

NAME   CONTROL OPEN_MODE            DATABASE_ROLE    DATAGUAR
------ ------- -------------------- ---------------- --------
DELL   STANDBY MOUNTED              PHYSICAL STANDBY DISABLED

SQL>
SQL> shut immediate
SQL> startup mount


STANDBY DB (192.168.1.12)

Our DGMGRL has lost Connection because of database down
DGMGRL>
DGMGRL> connect sys/sys
Connected.
DGMGRL> reinstate database dell_live;
Reinstating database "dell_live", please wait...
Reinstatement of database "dell_live" succeeded
DGMGRL>
DGMGRL>
DGMGRL> show configuration;

Configuration - dell

  Protection Mode: MaxPerformance
  Databases:
    dell_stby - Primary database
    dell_live - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL>

Now the STANDBY became PRIMARY and PRIMARY became STANDBY

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


PRIMARY DB (192.168.1.11)
SQL>
SQL> col name for a6;
SQL> select NAME,DB_UNIQUE_NAME,CONTROLFILE_TYPE,OPEN_MODE,DATABASE_ROLE,PROTECTION_MODE, DATAGUARD_BROKER from v$database;

SQL> select NAME,CONTROLFILE_TYPE,OPEN_MODE,DATABASE_ROLE,DATAGUARD_BROKER from v$database;

NAME   CONTROL OPEN_MODE            DATABASE_ROLE    DATAGUAR
------ ------- -------------------- ---------------- --------
DELL   STANDBY MOUNTED              PHYSICAL STANDBY DISABLED

SQL>
SQL> shut immediate
SQL> startup mount


STANDBY DB (192.168.1.12)

Our DGMGRL has lost Connection because of database down
DGMGRL>
DGMGRL> connect sys/sys
Connected.
DGMGRL> reinstate database dell_live;
Reinstating database "dell_live", please wait...
Reinstatement of database "dell_live" succeeded
DGMGRL>
DGMGRL>
DGMGRL> show configuration;

Configuration - dell

  Protection Mode: MaxPerformance
  Databases:
    dell_stby - Primary database
    dell_live - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL>

Now the STANDBY became PRIMARY and PIMARY became STANDBY

PRIMARY DB (192.168.1.11)
SQL>
SQL> select NAME,DB_UNIQUE_NAME,CONTROLFILE_TYPE,OPEN_MODE,DATABASE_ROLE, DATAGUARD_BROKER from v$database;

SQL> select NAME,CONTROLFILE_TYPE,OPEN_MODE,DATABASE_ROLE,DATAGUARD_BROKER from v$database;

NAME      CONTROL OPEN_MODE            DATABASE_ROLE       DATAGUAR
--------- ------- -------------------- ---------------- --------
DELL      STANDBY MOUNTED              PHYSICAL STANDBY ENABLED

SQL>

Scenario-2 : Switch Over 
Now lets perform a switch over Scenario

STANDBY DB (192.168.1.12)
[oracle@rac2 ~]$ sqlplus / as sysdba

Before performing switchover check the status
SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
TO STANDBY

SQL> select LOG_MODE,FLASHBACK_ON,FORCE_LOGGING from v$database;

LOG_MODE     FLASHBACK_ON       FOR
------------ ------------------ ---
ARCHIVELOG   YES                YES

SQL>
==============================================================================================================
Window Check the following Background Process on PRIMARY & STANDBY
[oracle@rac1 ~]$ ps -ef | grep -i DELL
oracle   12212     1  0 15:45 ?        00:00:00 ora_reco_DELL
oracle   12361     1  0 15:46 ?        00:00:00 ora_arch_DELL
----------
[oracle@rac2 ~]$ ps -ef | grep -i DELL
oracle   12212     1  0 15:45 ?        00:00:00 ora_reco_DELL
oracle   12361     1  0 15:46 ?        00:00:00 ora_arch_DELL
==============================================================================================================
Open a new window and connect to DGMGRL at PRIMARY
[oracle@rac2 ~]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/sys
Connected.
DGMGRL> show configuration

Configuration - dell

  Protection Mode: MaxPerformance
  Databases:
    dell_stby - Primary database
    dell_live - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL>
DGMGRL> show database verbose dell_stby

Database - dell_stby

  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    DELL

  Properties:
    DGConnectIdentifier             = 'dell_stby'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'ASYNC'
    DelayMins                       = '0'
    Binding                         = 'OPTIONAL'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyParallel                   = 'AUTO'
    StandbyFileManagement           = 'AUTO'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '30'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = 'DELL_LIVE, DELL_STBY'
    LogFileNameConvert              = 'DELL_LIVE, DELL_STBY'
    FastStartFailoverTarget         = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    SidName                         = 'DELL'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.12)(PORT=1528))(CONNECT_DATA=(SERVICE_NAME=DELL_STBY_DGMGRL)(INSTANCE_NAME=DELL)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = '/u01/app/oracle/DELL/arch'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = 'arch%s%t%r.arc'
    TopWaitEvents                   = '(monitor)'

Database Status:
SUCCESS

DGMGRL>
DGMGRL> edit database dell_stby set property 'LogXptMode'='SYNC';
Property "LogXptMode" updated
DGMGRL>


PRIMARY DB (192.168.1.11)
[oracle@rac1 ~]$
[oracle@rac1 ~]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/sys
Connected.
DGMGRL> show database verbose dell_live;

Database - dell_live

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       0 seconds
  Real Time Query: ON
  Instance(s):
    DELL

  Properties:
    DGConnectIdentifier             = 'dell_live'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'ASYNC'
    DelayMins                       = '0'
    Binding                         = 'optional'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyParallel                   = 'AUTO'
    StandbyFileManagement           = 'auto'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '30'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = 'DELL_STBY, DELL_LIVE'
    LogFileNameConvert              = 'DELL_STBY, DELL_LIVE'
    FastStartFailoverTarget         = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    SidName                         = 'DELL'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.11)(PORT=1529))(CONNECT_DATA=(SERVICE_NAME=DELL_LIVE_DGMGRL)(INSTANCE_NAME=DELL)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = '/u01/app/oracle/DELL/arch'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = 'arch%s%t%r.arc'
    TopWaitEvents                   = '(monitor)'

Database Status:
SUCCESS

DGMGRL> edit database dell_live set property 'LogXptMode'='SYNC';
Property "LogXptMode" updated
DGMGRL>


STANDBY DB (192.168.1.12)
DGMGRL>
DGMGRL> show configuration

Configuration - dell

  Protection Mode: MaxPerformance
  Databases:
    dell_stby - Primary database
    dell_live - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL>
DGMGRL> switchover to dell_live;
Performing switchover NOW, please wait...
New primary database "dell_live" is opening...
Operation requires shutdown of instance "DELL" on database "dell_stby"
Shutting down instance "DELL"...
ORACLE instance shut down.
Operation requires startup of instance "DELL" on database "dell_stby"
Starting instance "DELL"...
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "dell_live"
DGMGRL>
DGMGRL>
DGMGRL> show configuration

Configuration - dell

  Protection Mode: MaxPerformance
  Databases:
    dell_live - Primary database
    dell_stby - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL>
DGMGRL> switchover to dell_stby;
Performing switchover NOW, please wait...
New primary database "dell_stby" is opening...
Operation requires shutdown of instance "DELL" on database "dell_live"
Shutting down instance "DELL"...
ORACLE instance shut down.
Operation requires startup of instance "DELL" on database "dell_live"
Starting instance "DELL"...
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "dell_stby"
DGMGRL>
DGMGRL> show configuration

Configuration - dell

  Protection Mode: MaxPerformance
  Databases:
    dell_stby - Primary database
    dell_live - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL>


Scenario-3 : Updating 

 STANDBY DB (192.168.1.12)
DGMGRL> edit database dell_live set property 'LogXptMode'='SYNC';
Property "LogXptMode" updated
DGMGRL>

Thanks for Reading

Regards,
Mohammed Areefuddin.
Suggested Topics :

Posted by at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)