Role TransitionsInvolving 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 TransitionsInvolving 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 a Failoverto a Physical Standby Database
Fault Simulation
Original Primary:
SQL>set linesize 200
SQL> selectOPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,DB_UNIQUE_NAME,SWITCHOVER_STATUS fromv$database;
OPEN_MODE PROTECTION_MODE DATABASE_ROLE DB_UNIQUE_NAME SWITCHOVER_STATUS
---------------------------------------- ---------------- --------------------------------------------------
READWRITE MAXIMUM AVAILABILITYPRIMARY prod TO STANDBY
SQL>exit
Disconnectedfrom Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
Withthe Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@prod~]$ ps -ef | grep smon
oracle 3915 1 0 16:20 ? 00:00:01 ora_smon_prod
oracle 4584 3323 0 16:57 pts/1 00:00:00 grep --color=auto smon
[oracle@prod~]$ kill -9 3915
Step1 Flush any unsent redo from the primary database to thetarget standby database.
If the primary database can be mounted, it may be possible to flush any unsent archived andcurrent redo from the primary database to the standby database. If thisoperation is successful, a zero data loss failover is possible even if theprimary database is not in a zero data loss data protection mode.
Ensure that Redo Apply is active at the targetstandby database.
Standby:
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
---------------------------------------- ---------------- --------------------------------------------------
READ ONLY WITH APPLY MAXIMUM AVAILABILITY PHYSICALSTANDBY standby TOPRIMARY
Primary:
Mount, but do not open the primarydatabase. If the primary database cannot be mounted, go toStep2.
Issue the following SQL statement at the primarydatabase:
SQL>startup mount;
ORACLEinstance started.
TotalSystem Global Area 263639040 bytes
FixedSize 1344312 bytes
VariableSize 230689992 bytes
DatabaseBuffers 25165824 bytes
RedoBuffers 6438912 bytes
Databasemounted.
SQL>alter system flush redo to 'standby';
Systemaltered.
ALTER SYSTEMFLUSH REDO TO target_db_name .
For target_db_name, specify the DB_UNIQUE_NAME of thestandby database that is to receive the redo flushed from the primary database.
This statement flushes any unsent redo from theprimary database to the standby database, and waits for that redo to be appliedto the standby database.
If this statement completes without anyerrors, go toStep5. If the statement completes with any error,or if it must be stopped because you cannot wait any longer for the statementto complete, continue with Step2.
Step2 Verify that the standby database has the most recentlyarchived redo log file for each primary database redo thread.
Query the V$ARCHIVED_LOG view on the target standbydatabase to obtain the highest log sequence number for each redo thread.
Primary and Standby
SQL>SELECT UNIQUE THREAD# AS THREAD, MAX(SEQUENCE#) OVER (PARTITION BY thread#) ASLAST from V$ARCHIVED_LOG;
THREAD LAST
--------------------
1 98
standby
If possible, copy the most recently archived redo logfile for each primary database redo thread to the standby database if it doesnot exist there, and register it. This must be done for each redo thread.
ALTERDATABASE REGISTER PHYSICAL LOGFILE 'redo_logfile';
Step3 Identify and resolve any archived redo log gaps.
Query the V$ARCHIVE_GAP view on the target standbydatabase to determine if there are any redo gaps on the target standbydatabase.
SQL>SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
norows selected
THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------
1 90 92
In this example the gap comprises archived redo logfiles with sequence numbers 90, 91, and 92 for thread 1.
If possible, copy any missing archived redo log filesto the target standby database from the primary database and register them atthe target standby database. This must be done for each redo thread.
SQL>ALTER DATABASE REGISTER PHYSICAL LOGFILE 'redo_logfile';
Step4 Repeat Step 3 until all gaps are resolved.
The query executed inStep3 displays information for the highest gaponly. After resolving a gap, you must repeat the query until no more rows arereturned.
If, after performingStep2 throughStep4, you are not able to resolve all gaps in thearchived redo log files (for example, because you do not have access to thesystem that hosted the failed primary database), some data loss will occurduring the failover.
Step5 Stop Redo Apply.
Issue the following SQL statement on the targetstandby database:
SQL>alter database recover managed standby database cancel;
Databasealtered.
Step6 Finish applying all received redo data.
Issue the following SQL statement on the targetstandby database:
SQL>alter database recover managed standby database finish;
Databasealtered.
If this statement completes without anyerrors, proceed toStep7.
If an error occurs, some received redo data was notapplied. Try to resolve the cause of the error and re-issue the statementbefore proceeding to the next step.
Note that if there is a redo gap thatwas not resolved inStep3 andStep4, you will receive an error stating that thereis a redo gap.
If the error condition cannot be resolved, a failovercan still be performed (with some data loss) by issuing the following SQLstatement on the target standby database:
SQL>alter database activate physical standby database;
Databasealtered.
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 AVAILABILITY PRIMARY standby NOT ALLOWED
Proceed toStep9 when the ACTIVATE statement completes.
Step7 Verify that the target standby database is ready to become aprimary database.
Query the SWITCHOVER_STATUS column of the V$DATABASEview on the target standby database.
SQL>SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
--------------------
NOTALLOWED
A value of either TO PRIMARY or SESSIONS ACTIVEindicates that the standby database is ready to be switched to the primaryrole. If neither of these values is returned, verify that Redo Apply is activeand continue to query this view until either TO PRIMARY or SESSIONS ACTIVE isreturned.
Step8 Switch the physical standby database to the primary role.
Issue the following SQL statement on the targetstandby database:
SQL>alter database commit to switchover to primary with session shutdown;
Note:
The WITH SESSION SHUTDOWN clause can be omitted fromthe switchover statement if the query of the SWITCHOVER_STATUS column performedin the previous step returned a value of TO PRIMARY.
Step9 Open the new primary database.
SQL>alter database open;
Databasealtered.
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 AVAILABILITYPRIMARY standby FAILED DESTINATION
Step10 Back up the new primary database.
Oracle recommends that a full backup be taken of thenew primary database.
Step11 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;
Step12 Optionally, restore the failed primary database.
After a failover, the original primary database canbe converted into a physical standby database of the new primary database usingthe method
As following:
Flashing Back a FailedPrimary Database into a Physical Standby Database
作者:xiangsir
QQ:444367417
MSN:xiangsir@hotmail.com

MySQL menggunakan lesen GPL. 1) Lesen GPL membolehkan penggunaan percuma, pengubahsuaian dan pengedaran MySQL, tetapi taburan yang diubah suai mesti mematuhi GPL. 2) Lesen komersial boleh mengelakkan pengubahsuaian awam dan sesuai untuk aplikasi komersil yang memerlukan kerahsiaan.

Keadaan ketika memilih innoDB dan bukannya myisam termasuk: 1) sokongan transaksi, 2) persekitaran konkurensi tinggi, 3) konsistensi data yang tinggi; Sebaliknya, keadaan apabila memilih myisam termasuk: 1) terutamanya membaca operasi, 2) Tiada sokongan transaksi diperlukan. InnoDB sesuai untuk aplikasi yang memerlukan konsistensi data yang tinggi dan pemprosesan urus niaga, seperti platform e-dagang, manakala MyISAM sesuai untuk aplikasi bacaan dan bebas transaksi seperti sistem blog.

Di MySQL, fungsi kunci asing adalah untuk mewujudkan hubungan antara jadual dan memastikan konsistensi dan integriti data. Kekunci asing mengekalkan keberkesanan data melalui pemeriksaan integriti rujukan dan operasi cascading. Perhatikan pengoptimuman prestasi dan elakkan kesilapan biasa apabila menggunakannya.

Terdapat empat jenis indeks utama dalam MySQL: Indeks B-Tree, Indeks Hash, Indeks Teks Penuh dan Indeks Spatial. 1. B-Tree Index sesuai untuk pertanyaan, penyortiran dan pengelompokan, dan sesuai untuk penciptaan pada lajur Nama Jadual Pekerja. 2. Indeks hash sesuai untuk pertanyaan yang setara dan sesuai untuk penciptaan pada lajur ID jadual hash_table enjin penyimpanan memori. 3. Indeks teks penuh digunakan untuk carian teks, sesuai untuk penciptaan pada lajur kandungan jadual artikel. 4. Indeks spatial digunakan untuk pertanyaan geospatial, sesuai untuk penciptaan pada lajur geom jadual lokasi.

TOCREATEANINDEXINMYSQL, USETHECreateIndexStatement.1) forasingLecolumn, gunakan "createIndexidx_lastNameonemployees (lastName);" 2) foracompositeIndex, gunakan "createindexidx_nameonemployees (lastName, firstName)

Perbezaan utama antara MySQL dan SQLite adalah konsep reka bentuk dan senario penggunaan: 1. MySQL sesuai untuk aplikasi besar dan penyelesaian peringkat perusahaan, menyokong prestasi tinggi dan kesesuaian yang tinggi; 2. SQLITE sesuai untuk aplikasi mudah alih dan perisian desktop, ringan dan mudah dibenamkan.

Indeks dalam MySQL adalah struktur yang diperintahkan satu atau lebih lajur dalam jadual pangkalan data, yang digunakan untuk mempercepat pengambilan data. 1) Indeks meningkatkan kelajuan pertanyaan dengan mengurangkan jumlah data yang diimbas. 2) Indeks B-Tree menggunakan struktur pokok yang seimbang, yang sesuai untuk pertanyaan dan penyortiran pelbagai. 3) Gunakan pernyataan createIndex untuk membuat indeks, seperti createIndexidx_customer_idonorders (customer_id). 4) Indeks komposit boleh mengoptimumkan pertanyaan berbilang lajur, seperti createIndexidx_customer_orderonorders (customer_id, order_date). 5) Gunakan Jelaskan untuk menganalisis rancangan pertanyaan dan elakkan

Menggunakan transaksi dalam MySQL memastikan konsistensi data. 1) Mulakan transaksi melalui starttransaction, dan kemudian laksanakan operasi SQL dan serahkannya dengan komit atau rollback. 2) Gunakan SavePoint untuk menetapkan titik simpan untuk membolehkan rollback separa. 3) Cadangan Pengoptimuman Prestasi termasuk memendekkan masa urus niaga, mengelakkan pertanyaan berskala besar dan menggunakan tahap pengasingan yang munasabah.


Alat AI Hot

Undresser.AI Undress
Apl berkuasa AI untuk mencipta foto bogel yang realistik

AI Clothes Remover
Alat AI dalam talian untuk mengeluarkan pakaian daripada foto.

Undress AI Tool
Gambar buka pakaian secara percuma

Clothoff.io
Penyingkiran pakaian AI

Video Face Swap
Tukar muka dalam mana-mana video dengan mudah menggunakan alat tukar muka AI percuma kami!

Artikel Panas

Alat panas

ZendStudio 13.5.1 Mac
Persekitaran pembangunan bersepadu PHP yang berkuasa

SublimeText3 versi Inggeris
Disyorkan: Versi Win, menyokong gesaan kod!

Notepad++7.3.1
Editor kod yang mudah digunakan dan percuma

Penyesuai Pelayan SAP NetWeaver untuk Eclipse
Integrasikan Eclipse dengan pelayan aplikasi SAP NetWeaver.

Versi Mac WebStorm
Alat pembangunan JavaScript yang berguna
