在生产环境中，经常会遇到各种情况，需要临时对调下主，备库的角色来满足复杂的生产环境要求！本文简单的对比下使用手工切换和使用dataguard broker工具切换的步骤！
一：手工切换
1：确认主库（dg1）和备库（dg2)的日志应用状态正常，主库的switchover_status应当为to_standby
- SQL> select db_unique_name,open_mode,database_role,switchover_status from v$database;
- DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
- -------------------- -------------------- ---------------- --------------------
- dg1 READ WRITE PRIMARY TO STANDBY
- SQL> archive log list;
- Database log mode Archive Mode
- Automatic archival Enabled
- Archive destination /u01/app/oracle/standbylog
- Oldest online log sequence 16
- Next log sequence to archive 18
- Current log sequence 18
- SQL> select db_unique_name,open_mode,database_role,switchover_status from v$database;
- DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
- -------------------- -------------------- ---------------- --------------------
- dg2 READ ONLY WITH APPLY PHYSICAL STANDBY NOT ALLOWED
- SQL> archive log list;
- Database log mode Archive Mode
- Automatic archival Enabled
- Archive destination /u01/app/oracle/standbylog
- Oldest online log sequence 17
- Next log sequence to archive 0
- Current log sequence 18
2:在dg1上执行switchover操作后，重新启动dg1到mount状态
- SQL> alter database commit to switchover to physical standby;
- Database altered.
- SQL> select db_unique_name,open_mode,database_role,switchover_status from v$database;
- DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
- -------------------- -------------------- ---------------- --------------------
- dg1 READ WRITE PHYSICAL STANDBY RECOVERY NEEDED
| [oracle@dg1 ~]$ tail -f /u01/app/oracle/diag/rdbms/dg1/dg/trace/alert_dg.log Mon Apr 30 11:59:21 2012 alter database commit to switchover to physical standby ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY [Process Id: 13575] (dg) Mon Apr 30 11:59:21 2012 LGWR: Standby redo logfile selected to archive thread 1 sequence 19 LGWR: Standby redo logfile selected for thread 1 sequence 19 for destination LOG_ARCHIVE_DEST_2 Thread 1 advanced to log sequence 19 (LGWR switch) Current log# 1 seq# 19 mem# 0: /u01/app/oracle/oradata/dg/redo01.log Mon Apr 30 11:59:21 2012 Stopping background process CJQ0 Stopping background process QMNC Mon Apr 30 11:59:22 2012 Archived Log entry 26 added for thread 1 sequence 18 ID 0x6501e627 dest 1: All dispatchers and shared servers shutdown CLOSE: killing server sessions. CLOSE: all sessions shutdown successfully. Waiting for all non-current ORLs to be archived... All non-current ORLs have been archived. Waiting for all FAL entries to be archived... All FAL entries have been archived. Waiting for potential Physical Standby switchover target to become synchronized... Active, synchronized Physical Standby switchover target has been identified Switchover End-Of-Redo Log thread 1 sequence 19 has been fixed Switchover: Primary highest seen SCN set to 0x0.0x10758d ARCH: Noswitch archival of thread 1, sequence 19 ARCH: End-Of-Redo Branch archival of thread 1 sequence 19 ARCH: LGWR is actively archiving destination LOG_ARCHIVE_DEST_2 ARCH: Standby redo logfile selected for thread 1 sequence 19 for destination LOG_ARCHIVE_DEST_2 Archived Log entry 27 added for thread 1 sequence 19 ID 0x6501e627 dest 1: ARCH: Archiving is disabled due to current logfile archival Primary will check for some target standby to have received alls redo Final check for a synchronized target standby. Check will be made once. LOG_ARCHIVE_DEST_2 is a potential Physical Standby switchover target Active, synchronized target has been identified Target has also received all redo Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/dg1/dg/trace/dg_ora_13575.trc Clearing standby activation ID 1694623271 (0x6501e627) The primary database controlfile was created using the 'MAXLOGFILES 16' clause. There is space for up to 13 standby redo logfiles Use the following SQL commands on the standby database to create standby redo logfiles that match the primary database: ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 52428800; ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 52428800; ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 52428800; ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 52428800; Archivelog for thread 1 sequence 19 required for standby recovery Switchover: Primary controlfile converted to standby controlfile succesfully. Switchover: Complete - Database shutdown required Completed: alter database commit to switchover to physical standby Mon Apr 30 11:59:53 2012 Process (ospid 13546) is suspended due to switchover to physical standby operation. Mon Apr 30 11:59:57 2012 Process (ospid 13581) is suspended due to switchover to physical standby operation. |
- SQL> shutdown immediate
- SQL> startup mount
- SQL> select db_unique_name,open_mode,database_role,switchover_status from v$database;
- DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
- -------------------- -------------------- ---------------- --------------------
- dg1 MOUNTED PHYSICAL STANDBY RECOVERY NEEDED
3：在dg2上执行switchover到主库的操作,执行完后将dg2启动到读写状态
- SQL> select db_unique_name,open_mode,database_role,switchover_status from v$database;
- DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
- -------------------- -------------------- ---------------- --------------------
- dg2 READ ONLY WITH APPLY PHYSICAL STANDBY TO PRIMARY
- SQL> alter database commit to switchover to primary;
- Database altered.
- SQL> select db_unique_name,open_mode,database_role,switchover_status from v$database;
- DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
- -------------------- -------------------- ---------------- --------------------
- dg2 MOUNTED PRIMARY NOT ALLOWED
- SQL> alter database open;
- Database altered.
- SQL> select db_unique_name,open_mode,database_role,switchover_status from v$database;
- DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
- -------------------- -------------------- ---------------- --------------------
- dg2 READ WRITE PRIMARY TO STANDBY
|
[oracle@dg2 ~]$ tail -f /u01/app/oracle/diag/rdbms/dg2/dg/trace/alert_dg.log previously cleared previously cleared previously cleared |
4：将dg1启动到read only with apply状态下，完成切换操作
- SQL> select db_unique_name,open_mode,database_role,switchover_status from v$database;
- DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
- -------------------- -------------------- ---------------- --------------------
- dg1 MOUNTED PHYSICAL STANDBY RECOVERY NEEDED
- SQL> alter database open;
- Database altered.
- SQL> alter database recover managed standby database using current logfile disconnect from session;
- Database altered.
- SQL> select db_unique_name,open_mode,database_role,switchover_status from v$database;
- DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
- -------------------- -------------------- ---------------- --------------------
- dg1 READ ONLY PHYSICAL STANDBY RECOVERY NEEDED
- SQL> select db_unique_name,open_mode,database_role,switchover_status from v$database;
- DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
- -------------------- -------------------- ---------------- --------------------
- dg1 READ ONLY WITH APPLY PHYSICAL STANDBY NOT ALLOWED
二：使用dataguard broker进行切换
1：配置dg1和dg2数据库服务器的listener.ora和tnsnames.ora文件
|
[oracle@dg2 ~]$ cat $TNS_ADMIN/listener.ora LISTENER = [oracle@dg2 ~]$ cat $TNS_ADMIN/tnsnames.ora dg2 = for_db = |
2：启动dg1和dg2数据库的dmon进程
- SQL> select db_unique_name,open_mode,database_role from v$database;
- DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE
- ------------------------------ -------------------- ----------------
- dg2 READ WRITE PRIMARY
- SQL> alter system set dg_broker_start=true;
- System altered.
- SQL> !ps -ef |grep dmon
- oracle 14037 1 0 12:30 ? 00:00:00 ora_dmon_dg
- oracle 14103 13958 0 12:35 pts/4 00:00:00 /bin/bash -c ps -ef |grep dmon
- oracle 14105 14103 0 12:35 pts/4 00:00:00 grep dmon
- SQL> select db_unique_name,open_mode,database_role from v$database;
- DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE
- -------------------- -------------------- ----------------
- dg1 READ ONLY WITH APPLY PHYSICAL STANDBY
- SQL> alter system set dg_broker_start=true;
- System altered.
3：创建配置文件，可以在dg1和dg2上观察日志的输出
- [oracle@dg2 ~]$ dgmgrl sys/123456@dg2
- DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production
- Copyright (c) 2000, 2009, Oracle. All rights reserved.
- Welcome to DGMGRL, type "help" for information.
- Connected.
- DGMGRL> show configuration;
- ORA-16541: database is not enabled
- Configuration details cannot be determined by DGMGRL
- DGMGRL> create configuration 'DG_BROKER_CONFIG' as primary database is dg2 connect identifier is dg2;
- Configuration "DG_BROKER_CONFIG" created with primary database "dg2"
- DGMGRL> add database dg1 as connect identifier is dg1 maintained as physical;
- Database "dg1" added
- DGMGRL> enable configuration
- Enabled
|
[oracle@dg2 ~]$ tail -f /u01/app/oracle/diag/rdbms/dg2/dg/trace/drcdg.log DGMGRL> show configuration; Configuration - DG_BROKER_CONFIG Protection Mode: MaxAvailability Fast-Start Failover: DISABLED Configuration Status: DGMGRL> show database dg2; Database - dg2 Role: PRIMARY Database Status: DGMGRL> show database dg1; Database - dg1 Role: PHYSICAL STANDBY Database Status: |
4：开始切换
- DGMGRL> switchover to dg1;
- Performing switchover NOW, please wait...
- New primary database "dg1" is opening...
- Operation requires shutdown of instance "dg" on database "dg2"
- Shutting down instance "dg"...
- ORACLE instance shut down.
- Operation requires startup of instance "dg" on database "dg2"
- Starting instance "dg"...
- Unable to connect to database
- ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
- Failed.
- Warning: You are no longer connected to ORACLE.
- Please complete the following steps to finish switchover:
- start up instance "dg" of database "dg2"
5：验证
- [oracle@dg2 ~]$ sqlplus /nolog
- SQL*Plus: Release 11.2.0.3.0 Production on Mon Apr 30 12:30:25 2012
- Copyright (c) 1982, 2011, Oracle. All rights reserved.
- SQL> conn /as sysdba
- Connected to an idle instance.
- SQL> startup
- ORACLE instance started.
- Total System Global Area 417546240 bytes
- Fixed Size 2228944 bytes
- Variable Size 339742000 bytes
- Database Buffers 67108864 bytes
- Redo Buffers 8466432 bytes
- Database mounted.
- Database opened.
- SQL> alter database recover managed standby database using current logfile disconnect from session;
- Database altered.
- SQL> select db_unique_name,database_role,open_mode from v$database;
- DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE
- ------------------------------ ---------------- --------------------
- dg2 PHYSICAL STANDBY READ ONLY WITH APPLY
- SQL> select protection_mode,protection_level,switchover_status from v$database;
- PROTECTION_MODE PROTECTION_LEVEL SWITCHOVER_STATUS
- -------------------- -------------------- --------------------
- MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY NOT ALLOWED
- [oracle@dg1 ~]$ sqlplus /nolog
- SQL*Plus: Release 11.2.0.3.0 Production on Mon Apr 30 12:32:58 2012
- Copyright (c) 1982, 2011, Oracle. All rights reserved.
- SQL> conn /as sysdba
- Connected.
- SQL> select db_unique_name,database_role,open_mode from v$database;
- DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE
- ------------------------------ ---------------- --------------------
- dg1 PRIMARY READ WRITE
- SQL> select protection_mode,protection_level,switchover_status from v$database;
- PROTECTION_MODE PROTECTION_LEVEL SWITCHOVER_STATUS
- -------------------- -------------------- --------------------
- MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY TO STANDBY
- [oracle@dg1 ~]$ dgmgrl sys/123456@dg1
- DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production
- Copyright (c) 2000, 2009, Oracle. All rights reserved.
- Welcome to DGMGRL, type "help" for information.
- Connected.
- DGMGRL> show configuration;
- Configuration - DG_BROKER_CONFIG
- Protection Mode: MaxAvailability
- Databases:
- dg1 - Primary database
- dg2 - Physical standby database
- Fast-Start Failover: DISABLED
- Configuration Status:
- SUCCESS
- DGMGRL> show database verbose dg1;
- Database - dg1
- Role: PRIMARY
- Intended State: TRANSPORT-ON
- Instance(s):
- dg
- Properties:
- DGConnectIdentifier = 'dg1'
- ObserverConnectIdentifier = ''
- LogXptMode = 'SYNC'
- DelayMins = '0'
- Binding = 'OPTIONAL'
- MaxFailure = '0'
- MaxConnections = '1'
- ReopenSecs = '300'
- NetTimeout = '30'
- 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 = 'dg'
- StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.61)(PORT=1521))
- (CONNECT_DATA=(SERVICE_NAME=dg1_DGMGRL.yang.com)(INSTANCE_NAME=dg)(SERVER=DEDICATED)))'
- StandbyArchiveLocation = '/u01/app/oracle/standbylog'
- AlternateLocation = ''
- LogArchiveTrace = '0'
- LogArchiveFormat = '%t_%s_%r.dbf'
- TopWaitEvents = '(monitor)'
- Database Status:
- SUCCESS
总结：个人推荐在生产环境中使用手工切换的方式，dataguard broker是比较方便，除了前期的配置比较麻烦外，在本次的测试中出现了ORA-12514错误，oracle推荐listener.ora文件中将GLOBAL_DBNAME设置成db_unique_name_DGMGRL.db_domain格式，在10g平台下broker测试没有问题，详情可参考：http://ylw6006.blog.51cto.com/470441/686900，另外在平常的使用broker的测试中，曾出现过ORA-16014错误，详情可参考：http://www.itpub.net/thread-1604189-1-1.html
扩展阅读：
http://ylw6006.blog.51cto.com/470441/841815
http://ylw6006.blog.51cto.com/470441/661228
http://ylw6006.blog.51cto.com/470441/678157
