Oracle Data Guard Role Switching

About Oracle Data Guard Role Switching 1. Overview Oracle database uses its Data Guard technology to implement a remote security mechanism for Data in Oracle databases with real-time remote Data over the network. The implementation process is generally like this: first, create the same environment in a remote location, including the host operating system, database version, and data file storage methods. First, simulate the creation of a database, then create physical standby based on this library. Next, modify init on the master and slave database nodes. ora, listener. ora and tnsnames. configure the ora file and change the protection_mode of v $ database in the master database to MAXIMUMAVAILABILITY (maximum available ). Again, back up the control file on the master database, which is of course in the for standby mode. Finally, create a standby logfile on the slave database and start it to the recover managed standbydatabase state. Then, the master database will point to the log_archive_dest_state _ * of the slave database for defer and enable, that is, activate it. This completes the entire process. Okay, this is not the point. The point is, can I switch the slave database after the slave uard environment is set up? How to switch? 2. The master and slave databases are switched to each other. There are two types of OracleData Guard switches, which are called switchover and failover. The steps for switching switchover are simple and clear, as shown in the following figure: Step 1: Disable the listener on the master database and disable all connections on the master database. I can directly kill the oracle process with-9 local = no. Step 2: Set the database role to physical standby on the master database, and set the switching status to primary. The SQL statement for the operation is alter database commit to switchover to physical standby; Result check: SQL> select database_role, switchover_status from v $ database; DATABASE_ROLE SWITCHOVER_STATUS -------------------------------------- physicalstandby to primary the master database has become a standby database. However, the switchover status of the database is set to primary. If you regret this, you can switch back. After switching, the database instance needs to be restarted, The init. ora file needs to be changed, and the instance is set to Real-Time recovery. The SQL statement for the operation is: startupnomount force; alterdatabase mount standby database; alterdatabase recover managed standby database using current logfile disconnect fromsession; Step 3: Set the database role to PRIMARY in the standby database. The SQL statement for the operation is alter database commit to switchover to primary. Result check: Check the database role and switch status first to determine whether the slave database can be switched. SQL> select database_role, switchover_status from v $ database; DATABASE_ROLE SWITCHOVER_STATUS using physicalstandby to primary SQL> alter database commit to switchover to primary; Databasealtered. after switching, the database instance needs to be restarted, init. the ora file needs to be replaced. SQL> conn/as sysdba Connected. SQL> startup open force; ORACLEinstance started. databasemounted. databaseopened. SQL> select database_role, switchover_status from v $ database; DATABASE_ROLE SWITCHOVER_STATUS ---------------------------------- PRIMARY TO STANDBY through these three steps, the switch operation is completed. This switchover operation can be used for data migration. It is much simpler than the backup/restore that used RMAN previously. More importantly, it can switch back. These three steps seem simple, but involve the conversion of the master database role. What should I do if the role of the master database cannot be converted to the master database due to some reasons? Without the master database, it is a disaster for any application system. Okay, I admit, this is what I want to say. 3. If the slave database fails to be switched over to the master database, and the master database becomes a new slave database, you need to restore the master database to the master database again. When you execute the SQL statement alter database commit to switchover tophysical standby for the conversion operation on the master database, these records are displayed in alert. log. Wed Apr 39:40 2013 Thread 1 advanced to log sequence 32 (LGWR switch) Current log #2 seq #32 mem #0: + VG1/tesdb/redo02.log ********************************** ********************************** LGWR: resetting 'active' archival for destination LOG_ARCHIVE_DEST_2 ******************************** * ********************************** Wed Apr 2013 destinationLOG_ARCHIVE_DEST_2 no longer supp Orts SYNCHRONIZATION Wed Apr 39:45 2013 Thread 1 advanced to log sequence 33 (LGWR switch) Current log #3 seq #33 mem #0: + VG1/tesdb/paiwed Apr processing 2013 processing process CJQ0 Wed Apr processing 2013 SMON: disabling tx recovery Wed Apr processing 2013 Stoppingbackground process QMNC Wed Apr processing 2013 StoppingJob queue slave processes, flags = 27 Wed Apr 10:39:47 2 013 Job queueslave processes stopped Waiting fordispatcher 'd000' to shutdown Alldispatchers and shared servers shutdown Wed Apr 2013 SMON: disabling cache recovery Wed Apr limit 2013 Shuttingdown archive processes Archivingis disabled Wed Apr limit 2013 ARCHshutting down ARC1: Archival stopped Wed Apr limit 2013 ARCHshutting down ARC0: Archival stopped Wed Apr Limit 20 13 Thread 1 closed at log sequence 33 Successfulclose of redo thread 1 Wed Apr 2013 ARCH: Noswitch archival of thread 1, sequence 33 ARCH: end-Of-Redo Branch archival of thread 1 sequence 33 ARCH: Archiving is disabled due to current logfile archival Clearingstandby activation ID 1566058370 (0x5d582782) the primarydatabase controlfile was created using the 'maxlogfiles40' clause. there issp Ace for up to 37 standby redo logfiles Use thefollowing SQL commands on the standby database to create standbyredo logfiles that match the primary database: ALTERDATABASE ADD STANDBY LOGFILE 'srl1. f'size 52428800; alterdatabase add standby logfile' srl2. f'size 52428800; alterdatabase add standby logfile' srl3. f'size 52428800; alterdatabase add standby logfile' srl4. f'size 52428800; Archivelogf Or thread 1 sequence 33 required for standby recovery Switchover: Primary controlfile converted to standby controlfile succesfully. MRP0started with pid = 15, OS id = 15321 Wed Apr 1014:40:00 2013 MRP0: Background Managed Standby Recovery process started (tesdb) managedStandby Recovery not using Real Time Apply parallel recovery started with 16 processes Onlinelogfile pre-clearing operation disabled Switchover upload Log + VG1/tesdb/archivelog/logs/incluidentifiedend-Of-Redo for thread 1 sequence 33 Wed Apr limit 2013 MediaRecovery End-Of-Redo indicator encountered Wed Apr limit 2013 limit applied until change 639287 Wed Apr 2013 MRP0: mediaRecovery Complete: End-Of-REDO (tesdb) Resettingstandby activation ID 1566058370 (0x5d58 2782) Wed Apr release 2013 MRP0: Background Media Recovery process shutdown (tesdb) Wed Apr release 2013 SUCCESS: diskgroup VG1 was dismounted Wed Apr release 2013 Switchover: Complete-Database shutdown required (tesdb) wed Apr: 07 2013 Completed: alter database commit to switchover to physical standby Wed Apr: 07 2013 idledispatcher 'd000' terminated, pid = (15, 1) restart the database instance To read only. SQL> startup open ORACLEinstance started. databasemounted. databaseopened. SQL> conn test/test Connected. SQL> create table t1 as select * from all_objects; createtable t1 as select * from all_objects * ERROR atline 1: ORA-00604: error occurred at recursive SQL level 1 ORA-16000: database open for read-only access checks the operation results and confirms that the database role of the master database is changed to physical standby, and SWITCHOVER_STATUS is to primary. This is very important, indicating that the master database can be restored to the master database again. SQL> select database_role, switchover_status from v $ database; DATABASE_ROLE SWITCHOVER_STATUS using physicalstandby to primary use SQL: alter database commit to switchover to primary; Switch the master database TO the master database twice. SQL> alter database commit to switchover to primary; Databasealtered. the result is as follows: SQL> conn/as sysdba Connected. SQL> startup open force; ORACLEinstance started. databasemounted. databaseopened. SQL> select database_role, switchover_status from v $ database; DATABASE_ROLE SWITCHOVER_STATUS when PRIMARY TO STANDBY. Although nothing is done, it also indicates that the role of the master database can be restored TO PRIMARY again. During the second switchover, SWITCHOVER_STATUS of the slave database is converted from to primary to recovery needed, and finally to sessions active. SQL> r 1 * select database_role, switchover_status from v $ database DATABASE_ROLE SWITCHOVER_STATUS -------------------------------- physicalstandby to primary SQL> r 1 * select database_role, switchover_status from v $ database DATABASE_ROLE SWITCHOVER_STATUS ------------------------------------ physicalstandby recovery needed SQL> alter database recover managed standby database using current logfile Disconnect from session; Databasealtered. SQL> select database_role, switchover_status fromv $ database; DATABASE_ROLE SWITCHOVER_STATUS implements PHYSICALSTANDBY SESSIONS ACTIVE 4. Slave database failover first simulates master database downtime. Set log_archive_dest_state_2 to derfer on the master database, and set the IP address in tesdb_primary of tnsnames. ora of the slave database to another IP address. Otherwise, when alterdatabase recover managed standby database finish; is executed, the master database is still alive. SQL> alter database recover managed standby database finish; alterdatabase recover managed standby database finish * ERROR atline 1: ORA-00283: recovery session canceled due to errors ORA-16173: incompatible archival network connections active then performs the slave database switchover. The procedure is as follows: Step 1: Run select database_role and switchover_status from v $ database on the slave database to check the switchover status. SQL> select database_role, switchover_status from v $ database; DATABASE_ROLE SWITCHOVER_STATUS implements PHYSICALSTANDBY TO PRIMARY Step 2: Execute alter database recover managed standby database finish on the slave database TO restore all log files. I am not sure which SCN is recovered. SQL> alter database recover managed standby database finish; Databasealtered. step 3: Check the switchover status again in the slave database SQL> select database_role, switchover_status from v $ database; DATABASE_ROLE SWITCHOVER_STATUS -------------------------------- PHYSICALSTANDBY TO PRIMARY step 4, execute the SQL switch> alter database commit to switchover to primary with session shutdown; Databasealtered. SQL> select database_role, switchover_status from v $ da Tabase; DATABASE_ROLE SWITCHOVER_STATUS ------------------------------------ primary not allowed step 5, restart the database instance SQL> shutdown immediate ORA-01109: database not open Databasedismounted. ORACLEinstance shut down. SQL> startup open pfile = inittesdb_primary.ora ORACLEinstance started. databasemounted. databaseopened. the slave database is switched to the master database, but you do not know how much data is lost. SQL> select count (*) from test. t1; COUNT (*) ---------- 40779 5. Summary Oracle Database dataguard is a dynamic setting. It can ensure data security, but the premise is that detailed solutions, frequent drills, qualified technical personnel, and daily O & M real-time attention are required, in order to achieve the goal of zero data loss.