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?