Switchover is a master-slave library role switching, lossless conversion, usually the user manually triggered or planned to let it automatically trigger, such as hardware upgrades, software upgrades and the like. The failover is how to switch from the library to the main library when the main library fails.
First, how to use SQL command to switchover.
Check whether the main library supports switchover operations
Log in to the database and execute the following query
Sql> select Switchover_status from V$database; Switchover_status------------------------------------------------------------to STANDBY
If the column value is "to STANDBY" then the primary database support translates to the STANDBY role.
2. Switch the main library into a standby library
Sql> ALTER DATABASE COMMIT to switchover to physical STANDBY;
Database altered.
3. Close the main library and boot to mount state
Sql> shutdown immediateora-01092:oracle instance terminated. Disconnection forcedsql> Conn/as sysdbaconnected to an idle instance. Sql> Startup Mountoracle instance started. Total System Global area 839282688 bytesfixed size 2233000 bytesvariable size 499125592 bytesdatabase buffers 335544 Bytesredo buffers 2379776 bytesdatabase mounted.
4. Check whether the switchover operation is supported from the library
Sql> select Switchover_status from V$database; Switchover_status------------------------------------------------------------SESSIONS ACTIVE
Indicates that there are active sessions connected to the database. Shut down the database and reboot to the Mount state.
Sql> STARTUP mount;oracle instance started. Total System Global area 839282688 bytesfixed size 2233000 bytesvariable size 494931288 bytesdatabase buffers 339738 624 Bytesredo buffers 2379776 bytesdatabase mounted. Sql> select Switchover_status from V$database; Switchover_status------------------------------------------------------------to PRIMARY
5. Perform switchover operations
Sql> ALTER DATABASE commit to switchover to primary;database altered. sql> ALTER DATABASE Open;database altered.
You can also use the new syntax to close the session when switching
ALTER DATABASE COMMIT to switchover-physical STANDBY with SESSION SHUTDOWN;
6. Open the new from library to read-only.
Sql> select Switchover_status from V$database; Switchover_status------------------------------------------------------------RECOVERY neededsql> Alter Database recover managed Standby database disconnect from Session;database altered. Sql> select Switchover_status from V$database; Switchover_status------------------------------------------------------------not allowedsql> ALTER DATABASE Open read Only;database altered.
Second, use broker for switchover.
Special note: Once the broker is configured, do not use Sqlplus to manage Dataguard. Otherwise, the broker will be having problems.
After executing SQL switchover, the broker reports the following error.
dgmgrl> show configuration; Configuration - dr1 protection mode: maxavailability databases: 11gdg2 - primary database error: ora-16816: incorrect database role 11gdg1 - physical standby database Error: ORA-16810: multiple errors or warnings detected for the databasefast-start failover: Disabledconfiguration status:errordgmgrl> show database verbose 11gdg2;database - 11gdg2 Role: Primary intended state: transport-on instance (s): dgtst database error (s): ora-16816: incorrect database& NBsp;role properties: dgconnectidentifier = ' 11GDG2 ' observerconnectidentifier = ' LogXptMode = ' SYNC ' DelayMins = ' 0 ' Binding = ' OPTIONAL ' maxfailure = ' 0 ' maxconnections = ' 1 ' ReopenSecs = ' ' NetTimeout = ' redocompression ' = ' DISABLE ' LogShipping = ' on ' preferredapplyinstance = ' applyinstancetimeout = ' 0 ' applyparallel = ' AUTO ' StandbyFileManagement = ' AUTO ' ArchiveLagTarget = ' 0 ' LogArchiveMaxProcesses = ' 4 ' LogArchiveMinSucceedDest = ' 1 ' DbFileNameConvert = ' LogFileNameConvert = ' FastStartFailoverTarget = ' InconsistentProperties = ' (monitor) ' InconsistentLogXptProps = ' (monitor) ' SendQEntries = ' (monitor) ' LogXptStatus = ' (monitor) ' RecvQEntries = ' (monitor) ' SidName = ' dgtst ' staticconnectidentifier = ' (DESCRIPTION= (ADDRESS= ( PROTOCOL=TCP) (host=11gdg2.localdomain.com) (port=1521)) (Connect_data= (SERVICE_NAME=11GDG2_DGMGRL) (INSTANCE_NAME =DGTST) (server=dedicated)) ' StandbyArchiveLocation = ' Use_db_recovery_file_dest ' AlternateLocation = ' LogArchiveTrace = ' 0 ' LogArchiveFormat = '%t_%s_%r.dbf ' topwaitevents = ' (monitor) ' Database status:error
Broker in primary or 11GDG2 it ~ the information is inconsistent. The broker can only be reconfigured.
dgmgrl> REMOVE CONFIGURATION PRESERVE destinations; Removed configuration
... After reconfiguration, execute the following statement to switchover.
Dgmgrl> dgmgrl> 11gdg1-> dgmgrldgmgrl for linux:version 11.2.0.3.0-64bit productioncopyright (c), O Racle. All rights reserved. Welcome to DGMGRL, type ' help ' for information. Dgmgrl> CONNECT sys/[email protected]password:connected.dgmgrl> switchover to 11GDG2;
Three, analog fault, realize failover.
Note the points:
After failover, the original primary database is no longer part of the data guard configuration by default.
In most cases, other logical/physical standby databases do not participate directly in the failover process, so these databases do not require any action.
In some cases, after the new primary database is configured, all other standby databases need to be recreated. In addition, if the standby of the role to be converted is in maximum protection or maximum availability mode, the archive log should be contiguous
3.1 Close the main library, assuming the main library crashes.
Sql> conn/as sysdbaconnected.sql> shutdown immediatedatabase closed. Database dismounted. ORACLE instance shut down.
3.2 Check if there is a log gap from the library
Query the V$archive_gap view of the standby database to be converted to confirm that the archived file is connected:
Sql> SELECT thread#, low_sequence#, high_sequence# from V$archive_gap;
If a record is returned, copy the corresponding archive file to the standby server to be converted by the record number listed. This is a very heavy step.
To do this, you must ensure that all the archived files that have been generated are already present in the standby server, otherwise the data inconsistency can cause the conversion times to be wrong.
After the file is copied, add it to the data dictionary by using the following command:
sql> ALTER DATABASE REGISTER physical LOGFILE ' filespec1 ';
3.3 switch from library to main library
Sql> select Switchover_status from V$database; Switchover_status------------------------------------------------------------not allowed
Switchover is not allowed, what should we do?
First, cancel the log app
sql> ALTER DATABASE recover managed standby database Cancel;database altered.
Again, a command is issued that simulates the database switchover of the main library. In the log, hit Eor.
sql> ALTER DATABASE recover managed standby database Finish;database altered.
If the log has gap, the above command will fail, you need to execute ALTER DATABASE ACTIVATE physical STANDBY database;
Once the finish operation is complete, all will be the maximum performance, regardless of the original protection mode of the database. In order to ensure that the new main library will open.
Switch to the main library and open the database.
Sql> ALTER DATABASE COMMIT to switchover to PRIMARY with SESSION Shutdown;database altered. sql> ALTER DATABASE open;database altered.
3.3 Test Insert Data
Sql> Conn Scott/tigerconnected.sql> Select COUNT (*) from T1; COUNT (*)----------28sql> INSERT INTO T1 SELECT * from t1;28 rows created. Sql> commit; Commit complete.
This article is from the "Ding Dong" blog, please be sure to keep this source http://lqding.blog.51cto.com/9123978/1682064
Oracle Learning Dataguard (eight) switchover and failover