Switchover and failover in oracle 11g Data - Guard
SwitchoverAllows the primary database to switch roles with one of its standby databases. There is no data loss during a switchover. After a switchover, each database continues to participate in the Data Guard configuration with its new role.
Failover
Changes a standby database to the primary role in response to a primary database failure. If the primary database was not operating in either maximum protection mode or maximum availability mode before the failure, some data loss may occur. If Flashback Database is enabled on the primary database, it can be reinstated as a standby for the new primary database once the reason for the failure is corrected. verify the primary database can be switched to the standby role.
Preparing for a Role Transition
1) Verify that there are no redo transport errors or redo gaps at the standby database by querying the V$ARCHIVE_DEST_STATUS view on the primary database.
SELECT STATUS, GAP_STATUS FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID = 2;
STATUS GAP_STATUS
--------- ------------------------
VALID NO GAP
2) Ensure temporary files exist on the standby database that match the temporary files on the primary database.
3) Remove any delay in applying redo that may be in effect on the standby database that will become the new primary database.
4) Before performing a switchover to a physical standby database that is in real-time query mode, consider bringing all instances of that standby database to the mounted but not open state to achieve the fastest possible role transition and to cleanly terminate any user sessions connected to the physical standby database prior to the role transition
Switchover Steps
1) Check Primary and standby databases are ready to switchover
On Primary database
SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
-----------------
TO STANDBY
1 row selected
· A value of TO STANDBY or SESSIONS ACTIVE indicates that the primary database can be switched to the standby role. If neither of these values is returned, a switchover is not possible because redo transport is either misconfigured or is not functioning properly.
On Secondary Database
SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
-----------------
TO_PRIMARY
1 row selected
· A value of TO PRIMARY or SESSIONS ACTIVE indicates that the standby database is ready to be switched to the primary role. If neither of these values is returned, verify that Redo Apply is active and that redo transport is configured and working properly.
2) Initiate the switchover on the primary database.
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;
3) Shut down and then mount the former primary database.
SHUTDOWN ABORT;
STARTUP MOUNT;
4) Switch the target physical standby database role to the primary role.
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
5) Open the new primary database.
ALTER DATABASE OPEN;
6) Start Redo Apply on the new physical standby database.
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
Steps for failover
1) If the primary database can be mounted, it may be possible to flush any unsent archived and current redo from the primary database to the standby database. If this operation is successful, a zero data loss failover is possible even if the primary database is not in a zero data loss data protection mode.
ALTER SYSTEM FLUSH REDO TO target_db_name;
2) Verify that the standby database has the most recently archived redo log file for each primary database redo thread.
SELECT UNIQUE THREAD# AS THREAD, MAX(SEQUENCE#) OVER (PARTITION BY thread#) AS LAST from V$ARCHIVED_LOG;
THREAD LAST
---------- ----------
1 3555
3) If possible, copy the most recently archived redo log file for each primary database redo thread to the standby database if it does not exist there, and register it. This must be done for each redo thread.
ALTER DATABASE REGISTER PHYSICAL LOGFILE 'filename ';
4) Query the V$ARCHIVE_GAP view on the target standby database to determine if there are any redo gaps on the target standby database.
SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------
1 3555 3555
5) Stop Redo Apply.
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
6) Finish applying all received redo data.
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
7) Verify that the target standby database is ready to become a primary database.
SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
-----------------
TO_PRIMARY
8) Switch the physical standby database to the primary role.
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
9) Open the new primary database.
ALTER DATABASE OPEN;
Convert Physical Standby database into snapshot standby database .
We can open standby database in read-write mode .When switched back into standby mode, all changes made whilst in read-write mode are lost is know as Snapshot standby database .
Priversly This is achieved using flashback database, but from 11g standby database does not need to have flashback database explicitly enabled to take advantage
of this feature, thought it works just the same if it is.
How To Set Up Physical Standby Database You Can Check Here
Steps
1) Bring database in mount state
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
2) Disable recovery on standby
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
3) Convert standby database to flashback
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
4) Open database
ALTER DATABASE OPEN;
5) Check database status
Select NAME, OPEN_MODE, DATABASE_ROLE from v$database;
NAME OPEN_MODE DATABASE_ROLE
-------------- ---------- ----------------
ORCL_STBY READ WRITE SNAPSHOT STANDBY
SELECT flashback_on FROM v$database;
FLASHBACK_ON
------------------
RESTORE POINT ONLY
6) To convert it back to the physical standby, losing all the changes made since the conversion to snapshot standby, issue the following commands.
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
SHUTDOWN IMMEDIATE;
STARTUP NOMOUNT;
ALTER DATABASE MOUNT STANDBY DATABASE;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
SELECT flashback_on FROM v$database;
Select NAME, OPEN_MODE, DATABASE_ROLE from v$database;
NAME OPEN_MODE DATABASE_ROLE
-------------- ---------- ----------------
ORCL_STBY READ WRITE PYSICAL STANDBY
FLASHBACK_ON
------------------
NO
Active Data Guard : While the standby is open read-only on the same time the physical standby database can be in recovery mode is called ACTIVE DATA GUARD.
Advantage
This allows you to use this standby as a real-time reporting database or even to backup the primary data, also as a result it does not have any impact on RTO or RPO.
The following operations are disallowed
- Any Data Manipulation Language (DML) except for select statements
- Any Data Definition Language (DDL)
- Access of local sequences
- DMLs on local temporary tables
·
Note :-However, this benefit is offset to a certain extent by the fact that Active Data Guard is available on Enterprise Edition only and is cost option which must be licensed on both the primary and standby database.
Steps To create Active DATA Guard .
1) Check the status of the Primary database and Physical standby database and the latest sequence generated in the primary database.
Primary
select status,instance_name,database_role from v$instance,v$database;
STATUS INSTANCE_NAME DATABASE_ROLE
------------ ---------------- ----------------
OPEN ORCL PRIMARY
select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
3333
Standby
select status,instance_name,database_role from v$database,v$instance;
STATUS INSTANCE_NAME DATABASE_ROLE
-------- ------------- ---------------------
MOUNTED ORCL_STBY PHYSICAL STANDBY
SQL> select max(sequence#) from v$archived_log where applied='YES';
MAX(SEQUENCE#)
--------------
3333
) Check if the Managed Recovery Process (MRP) is active on the physcial standby database.
select process,status,sequence# from v$managed_standby;
2) Cancel the MRP on the physical standby database and open the standby database in READ-ONLY mode
alter database recover managed standby database cancel;
alter database open read only;
select status,instance_name,database_role,open_mode from v$database,v$instance;
STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE
------ -------------- ---------------- ---------------
OPEN ORCL_STBY PHYSICAL STANDBY READ ONLY
3) start the MRP on the physical standby database.
alter database recover managed standby database disconnectfrom session;
4) Database is now Active Data Guard
SELECT database_role, open_mode FROM v$database;
DATABASE_ROLE OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY READ ONLY WITH APPLY
No comments:
Post a Comment