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

Source: Internet
Author: User

Switch between the primary and standby databases (physical standby databases) of DG and switch between dg
Switch between the master and slave databases of DG
I. 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.

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

















































What types of databases are applicable to the logical standby database of oracle replicuard physical standby database? Dataguard establishes a standby database and regularly imports archived logs to ensure that the standby database and the offline database are synchronized. When a problem occurs, the Standby database is switched to the offline database to directly replace the problematic database.
I understand that physical standby databases and logical standby databases can be used to back up and recover new data only when the database is closed; logical Hot Standby performs database line backup and restores new data
Two servers and two databases are SQL server 2005, which are divided into master and slave databases. How can I write a trigger when I modify and synchronize data to the slave database in the master database as an image?

Related Article

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.