Home Database Mysql Tutorial Oracle 11g Data Guard Role Transitions: Switchover

Oracle 11g Data Guard Role Transitions: Switchover

Jun 07, 2016 pm 03:02 PM
data guard oracle role

Role Transitions Involving Physical Standby Databases A database operates in one of the following mutuallyexclusive roles: primary or standby . Data Guard enables you to change theseroles dynamically by issuing the SQL statements described

Role Transitions Involving Physical Standby Databases

 

A database operates in one of the following mutuallyexclusive roles:primary or standby. Data Guard enables you to change theseroles dynamically by issuing the SQL statements described in this chapter, orby using either of the Data Guard broker's interfaces. Oracle Data Guardsupports the following role transitions:

 

  • Switchover
    Allows 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.

 

 

Performing aSwitchover to a Physical Standby Database

 

Step1   Verify that the primary database can be switched to thestandby role.

Query the SWITCHOVER_STATUS column of the V$DATABASEview on the primary database.For example:

 

SQL>select OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,DB_UNIQUE_NAME,SWITCHOVER_STATUSfrom v$database;

 

OPEN_MODE            PROTECTION_MODE      DATABASE_ROLE    DB_UNIQUE_NAME      SWITCHOVER_STATUS

---------------------------------------- ---------------- -------------------- --------------------

READWRITE           MAXIMUM PROTECTION   PRIMARY          prod                 TO STANDBY

 

A value of TO STANDBY or SESSIONS ACTIVE indicatesthat the primary database can be switched to the standby role. If neither ofthese values is returned, a switchover is not possible because redo transportis either misconfigured or is not functioning properly.

 

Step2   Initiate the switchover on the primary database.

 

Issue the following SQL statement on the primarydatabase to switch it to the standby role:

SQL>alter database commit to switchover to physical standby with session shutdown;

 

This statement converts the primary database into aphysical standby database. The current control file is backed up to the currentSQL session trace file before the switchover. This makes it possible toreconstruct a current control file, if necessary.

 

The WITH SESSION SHUTDOWN clause can be omitted fromthe switchover statement if the query performed in the previous step returned avalue of TO STANDBY.

 

Step3   Shut down and then mount the former primary database.

SQL>startup mount;

At this point in the switchover process, the originalprimary database is a physical standby database

 

Step4   Verify that the switchover target is ready to be switched tothe primary role.

Query the SWITCHOVER_STATUS column of the V$DATABASEview on the standby database.

 

SQL>set linesize 200

SQL>select OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,DB_UNIQUE_NAME,SWITCHOVER_STATUSfrom v$database;

 

OPEN_MODE            PROTECTION_MODE      DATABASE_ROLE    DB_UNIQUE_NAME                 SWITCHOVER_STATUS

---------------------------------------- ---------------- --------------------------------------------------

READONLY WITH APPLY MAXIMUM PROTECTION  PHYSICAL STANDBY standby                        TO PRIMARY

 

 

A value of TO PRIMARY or SESSIONS ACTIVE indicatesthat the standby database is ready to be switched to the primary role. Ifneither of these values is returned, verify that Redo Apply is active and thatredo transport is configured and working properly. Continue to query thiscolumn until the value returned is either TO PRIMARY or SESSIONS ACTIVE.

 

Step5   Switch the target physical standby database role to theprimary role.

Issue the following SQL statement on the targetphysical standby database:

SQL>alter database commit to switchover to primary with session shutdown;

SQL>select OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,DB_UNIQUE_NAME,SWITCHOVER_STATUSfrom v$database;

 

OPEN_MODE            PROTECTION_MODE      DATABASE_ROLE    DB_UNIQUE_NAME                 SWITCHOVER_STATUS

---------------------------------------- ---------------- --------------------------------------------------

MOUNTED              MAXIMUM PROTECTION   PRIMARY          standby                        NOT ALLOWED

 

Step6   Open the new primary database.

 

SQL>alter database open;

SQL>select OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,DB_UNIQUE_NAME,SWITCHOVER_STATUSfrom v$database;

 

OPEN_MODE            PROTECTION_MODE      DATABASE_ROLE    DB_UNIQUE_NAME                 SWITCHOVER_STATUS

---------------------------------------- ---------------- --------------------------------------------------

READWRITE           MAXIMUM PROTECTION   PRIMARY          standby                        TO STANDBY

 

Step7   Start Redo Apply on the new physical standby database.

SQL>select OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,DB_UNIQUE_NAME,SWITCHOVER_STATUSfrom v$database;

 

OPEN_MODE            PROTECTION_MODE      DATABASE_ROLE    DB_UNIQUE_NAME                 SWITCHOVER_STATUS

---------------------------------------- ---------------- --------------------------------------------------

MOUNTED              MAXIMUM PROTECTION   PHYSICAL STANDBY prod                           RECOVERY NEEDED

 

SQL>alter database open read only;

SQL>select OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,DB_UNIQUE_NAME,SWITCHOVER_STATUSfrom v$database;

 

OPEN_MODE            PROTECTION_MODE      DATABASE_ROLE    DB_UNIQUE_NAME                 SWITCHOVER_STATUS

---------------------------------------- ---------------- --------------------------------------------------

READONLY            MAXIMUM PROTECTION   PHYSICAL STANDBY prod                           RECOVERY NEEDED

 

SQL>alter database recover managed standby database using current logfiledisconnect from session;

 

 

Step8   Restart Redo Apply if it has stopped at any of the otherphysical standby databases in your Data Guard configuration.

 

SQL>alter database recover managed standby database using current logfiledisconnect from session;

 

On new primary:

SQL>SELECT MAX(SEQUENCE#), THREAD# FROM V$ARCHIVED_LOG GROUP BY THREAD#;

 

MAX(SEQUENCE#)    THREAD#

------------------------

            82          1

SQL>SELECT  DESTINATION, STATUS,ARCHIVED_THREAD#, ARCHIVED_SEQ# FROM V$ARCHIVE_DEST_STATUS WHERE  STATUS 'DEFERRED'  AND STATUS 'INACTIVE';

 

DESTINATION                    STATUS    ARCHIVED_THREAD# ARCHIVED_SEQ#

--------------------------------------- ---------------- -------------

/u01/arch1/STANDBY             VALID                    1            82

prod                           VALID                    1            82


作者:xiangsir

QQ:444367417

MSN:xiangsir@hotmail.com


Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

What to do if the oracle can't be opened What to do if the oracle can't be opened Apr 11, 2025 pm 10:06 PM

Solutions to Oracle cannot be opened include: 1. Start the database service; 2. Start the listener; 3. Check port conflicts; 4. Set environment variables correctly; 5. Make sure the firewall or antivirus software does not block the connection; 6. Check whether the server is closed; 7. Use RMAN to recover corrupt files; 8. Check whether the TNS service name is correct; 9. Check network connection; 10. Reinstall Oracle software.

How to delete all data from oracle How to delete all data from oracle Apr 11, 2025 pm 08:36 PM

Deleting all data in Oracle requires the following steps: 1. Establish a connection; 2. Disable foreign key constraints; 3. Delete table data; 4. Submit transactions; 5. Enable foreign key constraints (optional). Be sure to back up the database before execution to prevent data loss.

How to solve the problem of closing oracle cursor How to solve the problem of closing oracle cursor Apr 11, 2025 pm 10:18 PM

The method to solve the Oracle cursor closure problem includes: explicitly closing the cursor using the CLOSE statement. Declare the cursor in the FOR UPDATE clause so that it automatically closes after the scope is ended. Declare the cursor in the USING clause so that it automatically closes when the associated PL/SQL variable is closed. Use exception handling to ensure that the cursor is closed in any exception situation. Use the connection pool to automatically close the cursor. Disable automatic submission and delay cursor closing.

How to paginate oracle database How to paginate oracle database Apr 11, 2025 pm 08:42 PM

Oracle database paging uses ROWNUM pseudo-columns or FETCH statements to implement: ROWNUM pseudo-columns are used to filter results by row numbers and are suitable for complex queries. The FETCH statement is used to get the specified number of first rows and is suitable for simple queries.

How to create cursors in oracle loop How to create cursors in oracle loop Apr 12, 2025 am 06:18 AM

In Oracle, the FOR LOOP loop can create cursors dynamically. The steps are: 1. Define the cursor type; 2. Create the loop; 3. Create the cursor dynamically; 4. Execute the cursor; 5. Close the cursor. Example: A cursor can be created cycle-by-circuit to display the names and salaries of the top 10 employees.

How to stop oracle database How to stop oracle database Apr 12, 2025 am 06:12 AM

To stop an Oracle database, perform the following steps: 1. Connect to the database; 2. Shutdown immediately; 3. Shutdown abort completely.

How to create oracle dynamic sql How to create oracle dynamic sql Apr 12, 2025 am 06:06 AM

SQL statements can be created and executed based on runtime input by using Oracle's dynamic SQL. The steps include: preparing an empty string variable to store dynamically generated SQL statements. Use the EXECUTE IMMEDIATE or PREPARE statement to compile and execute dynamic SQL statements. Use bind variable to pass user input or other dynamic values ​​to dynamic SQL. Use EXECUTE IMMEDIATE or EXECUTE to execute dynamic SQL statements.

How to read the oracle awr report How to read the oracle awr report Apr 11, 2025 pm 09:45 PM

An AWR report is a report that displays database performance and activity snapshots. The interpretation steps include: identifying the date and time of the activity snapshot. View an overview of activities and resource consumption. Analyze session activities to find session types, resource consumption, and waiting events. Find potential performance bottlenecks such as slow SQL statements, resource contention, and I/O issues. View waiting events, identify and resolve them for performance. Analyze latch and memory usage patterns to identify memory issues that are causing performance issues.

See all articles