Switch between the primary and standby databases of DG (physical standby database)

Source: Internet
Author: User
Switch between the primary and standby databases of DG 1. in the sequence of database opening and database shutting down, the Standby database is started first, and then the primary database (start listening, open alarm logs) is shut down first, and then the standby database is shut down. master/Slave database switchover 1. Operation Process Overview Step 1: Start the slave database, listen, and alert; Step 2: Start the master database, listen, and alert; Step 3: Query Information SQ in the master database (bj)

Switch between the primary and standby databases of DG 1. in the sequence of database opening and database shutting down, the Standby database is started first, and then the primary database (start listening, open alarm logs) is shut down first, and then the standby database is shut down. master/Slave database switchover 1. Operation Process Overview Step 1: Start the slave database, listen, and alert; Step 2: Start the master database, listen, and alert; Step 3: Query Information SQ in the master database (bj)

Switch the master database and slave database of DG 1. Sequence of opening and closing Databases

Database opening sequence

Start the slave database first, and then the master database (start listening and open alarm logs)

Database close sequence

First shut down the master database, then shut down the slave Database

II. master/Slave database switchover 1. Operation Process Overview Step 1: Start the slave database, listen, and alert; Step 2: Start the master database, listen, and alert; Step 3: Query Information of the master database (bj)

SQL>Selectname, dbid, database_role, protection_mode from v $ database;

Step 4: recover the slave database (sh)

SQL>Recovermanaged standby database disconnect from session;

Step 5: insert data to the slave Database

SQL>Insert into scott. emp1 select * from scott. emp;

Error

Although the standby database is open, it is read-only.

Step 6: Check whether the logs of the master database (bj) and slave database (sh) are consistent

SQL>Selectmax (sequence #) from v $ archived_log where name is not null;

Or SQL>Selectmax (sequence #) from v $ archived_log;

Example:

Master database:

Slave database:

It indicates that the maximum serial number that has been archived is 34, indicating that logs are synchronized. You can switch between the master and slave databases.

Step 7: Check whether switchover is possible in the master database (bj)

SQL>Selectname, database_role, protection_mode, switchover_status from v $ database;

Switchover_status: "to standby" indicates that switching is allowed.

Step 8: Check whether switchover is possible in the standby database (sh)

SQL>Selectname, database_role, protection_mode, switchover_status from v $ database;


Note: The standby database cannot be automatically cut into the primary database. The not allowed status indicates that the database is normal.

Step 9: Check sessions on the master database (bj)

SQL>Select username, sid from v $ session where username is notnull;

Check whether a user still uses the database. If another user still uses the database, the switchover is not allowed. Before the switchover, make sure that no user is using the database.

Step 10: Change the master database (bj) to the physical standby database (sh)

If no session exists, change the master database (bj) to the physical standby database (sh)

SQL>Alter databasecommit to switchover to physical standby;

If a session exists, close the session and change the master database (bj) to the physical standby database (sh)

SQL>Alter database commit to switchover to physical standby withsession shutdown;

Step 11: Shut down and restart the master database (bj) to the mount status

SQL>Shutdown immediate;

SQL>Startup mount;

SQL>Selectswitchover_status from v $ database;

SWITCHOVER_STATUS

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

TO PRIMARY

Note: At this time, both databases should be in the standby database status.

Step 12: Verify the slave database's Switch Status

SQL>SELECTSWITCHOVER_STATUS from v $ DATABASE;

SWITCHOVER_STATUS

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

TO_PRIMARY -- can be converted to the master database, which indicates the status of the slave database.

Step 13: Convert the target physical standby database (sh) to the master database

SQL>Alter databasecommit to switchover to primary (withsession shutdown );

Step 14: complete the conversion from the slave database (sh) to the master database, and start the physical slave database to open

If the physical standby database (bj) is not in read-only mode during the last startup, open the database directly and then proceed.

SQL>Select status fromv $ instance;

STATUS

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

MOUNTED

SQL>Alter database open;

SQL>Select switchover_statusfrom v $ database;

SWITCHOVER_STATUS

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

TO STANDBY

If the physical standby database (bj) is in read-only mode when it was last started, shut down the database normally before starting the database.

SQL>Shutdown immediate;

SQL>STARTUP;

Step 15: restart the log application on the new slave database (bj)

SQL>Alter databaserecover managed standby database disconnect from session;

Step 16: Switch logs on the new master database (sh) and start to transmit data to the slave database.

SQL>Alter system switchlogfile;

SQL>/

SQL>Select max (sequence #) from v $ archived_log;

MAX (SEQUENCE #)

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

56

SQL>Col name for a50;

SQL>Select NAME, SEQUENCE # from v $ archived_log;View archived logs

Step 17: view the archive on the slave database (bj). If the two values are equal, the slave database can receive logs.

SQL>Select max (sequence #) from v $ archived_log;

MAX (SEQUENCE #)

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

56

SQL>Col name for a50;

SQL>Select NAME, SEQUENCE # from v $ archived_log;View archived logs

2. Summarize the key points of Master/Slave database switchover

Hour

Room

Axis

Bytes

Bytes

Bytes

Bytes

Bytes

Bytes

Bytes

Bytes

Bytes

Bytes

Bytes

Bytes

Master Database

Slave Database

Check whether switchover is allowed (display allowed)

Whether the standby database query can be switched (it should be displayed as not allowed)

Switch to physical standby Database

Switch to the mount status

View the slave database Switch Status

Convert to master database

Switch to the mount status

Start to open

Start to open

Enable log application

Switch logs

View the archive serial number

View the archive serial number

Query status: slave Database

Query status: master database

**************************************** *******Statement************************************************

Original works, from "Deep Blue blog" blog, welcome to reprint, reprint please be sure to indicate the source (http://blog.csdn.net/huangyanlong ).

Please leave a message if the statement is incorrect. Thank you very much.

Reminder: Click the directory to view the directory.

*****************************************************************************************************

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.