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

MySQLhandlesconcurrencyusingamixofrow-levelandtable-levellocking,primarilythroughInnoDB'srow-levellocking.ComparedtootherRDBMS,MySQL'sapproachisefficientformanyusecasesbutmayfacechallengeswithdeadlocksandlacksadvancedfeatureslikePostgreSQL'sSerializa

mySqlHandLestActionSefectefectionalytheinnodbengine,supportingAcidPropertiessimilartopostgresqlesqlandoracle.1)mySqluessRepeTableReadAbereadasTheDefaultIsolationLeleleteLevel,whatcanBeadJustEdToreDtoreDtoreDtoreadCommittedCommittenCommententCommittedForHigh-TrafficsCenarios.2)

最佳實踐包括:1)理解數據結構和MySQL處理方式,2)適當索引,3)避免SELECT*,4)使用合適的JOIN類型,5)謹慎使用子查詢,6)使用EXPLAIN分析查詢,7)考慮查詢對服務器資源的影響,8)定期維護數據庫。這些做法能使MySQL查詢不僅快速,還具備可維護性、可擴展性和資源效率。

MySQLisbetterforspeedandsimplicity,suitableforwebapplications;PostgreSQLexcelsincomplexdatascenarioswithrobustfeatures.MySQLisidealforquickprojectsandread-heavytasks,whilePostgreSQLispreferredforapplicationsrequiringstrictdataintegrityandadvancedSQLf

MySQL通過異步、半同步和組複製三種模式處理數據複製。 1)異步複製性能高但可能丟失數據。 2)半同步複製提高數據安全性但增加延遲。 3)組複製支持多主複製和故障轉移,適用於高可用性需求。

EXPLAIN語句可用於分析和提升SQL查詢性能。 1.執行EXPLAIN語句查看查詢計劃。 2.分析輸出結果,關注訪問類型、索引使用情況和JOIN順序。 3.根據分析結果,創建或調整索引,優化JOIN操作,避免全表掃描,以提升查詢效率。

使用mysqldump進行邏輯備份和MySQLEnterpriseBackup進行熱備份是備份MySQL數據庫的有效方法。 1.使用mysqldump備份數據庫:mysqldump-uroot-pmydatabase>mydatabase_backup.sql。 2.使用MySQLEnterpriseBackup進行熱備份:mysqlbackup--user=root--password=password--backup-dir=/path/to/backupbackup。恢復時,使用相應的命


熱AI工具

Undresser.AI Undress
人工智慧驅動的應用程序,用於創建逼真的裸體照片

AI Clothes Remover
用於從照片中去除衣服的線上人工智慧工具。

Undress AI Tool
免費脫衣圖片

Clothoff.io
AI脫衣器

Video Face Swap
使用我們完全免費的人工智慧換臉工具,輕鬆在任何影片中換臉!

熱門文章

熱工具

SublimeText3漢化版
中文版,非常好用

mPDF
mPDF是一個PHP庫,可以從UTF-8編碼的HTML產生PDF檔案。原作者Ian Back編寫mPDF以從他的網站上「即時」輸出PDF文件,並處理不同的語言。與原始腳本如HTML2FPDF相比,它的速度較慢,並且在使用Unicode字體時產生的檔案較大,但支援CSS樣式等,並進行了大量增強。支援幾乎所有語言,包括RTL(阿拉伯語和希伯來語)和CJK(中日韓)。支援嵌套的區塊級元素(如P、DIV),

Dreamweaver CS6
視覺化網頁開發工具

WebStorm Mac版
好用的JavaScript開發工具

Safe Exam Browser
Safe Exam Browser是一個安全的瀏覽器環境,安全地進行線上考試。該軟體將任何電腦變成一個安全的工作站。它控制對任何實用工具的訪問,並防止學生使用未經授權的資源。