[Oracle] DataGuard switchover

Oracle DataGuard switchover

2013/07/11

Tag.Data Guard,primary,standby,switchover

切换前primary site和standby site状态检查

Primary site.

确认primary site和standby site没有出现日志裂隙(log file gap)

 SQL> select status,gap_status from v$archive_dest_status where dest_id=2;

 STATUS    GAP_STATUS

 --------- ------------------------

 VALID     NO GAP

确认primary可以转换成standby角色
```
 SQL> select switchover_status from v$database;

 SWITCHOVER_STATUS

 --------------------

 TO STANDBY
```
--注意:

switchover_status应为to standby/session active/not allowed

a. to standby 表示可以转换

b. session active 表示还有活动的session,通过v$session确认活动会话

c. not allowed 表示不能转换

查看当前会话数

 SQL> select count(*) from v$session where username is not null;

   COUNT(*)

 ----------

          1

查看primary其他信息

 SQL> select open_mode,protection_mode,protection_level,switchover_status from v$database;

 OPEN_MODE            PROTECTION_MODE      PROTECTION_LEVEL     SWITCHOVER_STATUS

 -------------------- -------------------- -------------------- --------------------

 READ WRITE           MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  TO STANDBY

Standby site.

查看standby site端switchover状态

 SQL> select switchover_status,database_role from v$database;

 SWITCHOVER_STATUS    DATABASE_ROLE

 -------------------- ----------------

 NOT ALLOWED          PHYSICAL STANDBY

确认standby site没有日志应用延迟

 SQL> select delay_mins from v$archive_dest where dest_id=2;

 DELAY_MINS

 ----------

          0

如果有延迟设置.在standby site禁用延迟

 alter database recover managed standby database nodelay;

查看standby其他信息

 SQL> select open_mode,protection_mode,protection_level,switchover_status from v$database;

 OPEN_MODE            PROTECTION_MODE      PROTECTION_LEVEL     SWITCHOVER_STATUS

 -------------------- -------------------- -------------------- --------------------

 READ ONLY WITH APPLY MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  NOT ALLOWED

切换

--注意:

现将primary将为standby,再将standby升为priamry

primary切到standby

 SQL> select database_role from v$database;

 DATABASE_ROLE

 ----------------

 PRIMARY

 SQL> select open_mode,protection_mode,protection_level,switchover_status from v$database;

 OPEN_MODE            PROTECTION_MODE      PROTECTION_LEVEL     SWITCHOVER_STATUS

 -------------------- -------------------- -------------------- --------------------

 READ WRITE           MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  TO STANDBY

 SQL> alter database commit to switchover to physical standby;

 Database altered.

========================================

 SQL> shutdown immediate;

 ORA-01092: ORACLE instance terminated. Disconnection forced

 SQL> shutdown immediate;

 ORA-24324: service handle not initialized

 ORA-01041: internal error. hostdef extension doesn't exist

 SQL> select open_mode from v$database;

 ERROR:

 ORA-03114: not connected to ORACLE

 SQL> exit

 Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

 With the Partitioning, OLAP, Data Mining and Real Application Testing options

========================================

 [oracle@TENCENT64 /u]$ sqlplus / as sysdba

 SQL*Plus: Release 11.2.0.2.0 Production on Thu Jul 11 19:57:50 2013

 Copyright (c) 1982, 2010, Oracle.  All rights reserved.

 Connected to an idle instance.

 SQL>

=========================================

 SQL> startup nomount;

 ORACLE instance started.

 Total System Global Area 1603411968 bytes

 Fixed Size                  2226912 bytes

 Variable Size             503317792 bytes

 Database Buffers         1090519040 bytes

 Redo Buffers                7348224 bytes

 SQL> alter database mount standby database;

 Database altered.

 SQL> alter database recover managed standby database disconnect from session;

 Database altered.

 SQL> select database_role from v$database;

 DATABASE_ROLE

 ----------------

 PHYSICAL STANDBY

 SQL> select open_mode,protection_mode,protection_level,switchover_status from v$database;

 OPEN_MODE            PROTECTION_MODE      PROTECTION_LEVEL     SWITCHOVER_STATUS

 -------------------- -------------------- -------------------- --------------------

 MOUNTED              MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  TO PRIMARY

观察alert日志可以发现primary已经成功切换成Physical Standby database

 Thu Jul 11 19:58:56 2013

 Successful mount of redo thread 1, with mount id 3458571643

 Physical Standby Database mounted.

standby切到primary

 SQL> select database_role from v$database;

 DATABASE_ROLE

 ----------------

 PHYSICAL STANDBY

 SQL> select open_mode,protection_mode,protection_level,switchover_status from v$database;

 OPEN_MODE            PROTECTION_MODE      PROTECTION_LEVEL     SWITCHOVER_STATUS

 -------------------- -------------------- -------------------- --------------------

 READ ONLY WITH APPLY MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  TO PRIMARY

 SQL> alter database commit to switchover to primary;

 Database altered.

 SQL> shutdown immediate;

 ORA-01109: database not open

 Database dismounted.

 ORACLE instance shut down.

 SQL> startup;

 ORACLE instance started.

 Total System Global Area 1603411968 bytes

 Fixed Size                  2226912 bytes

 Variable Size             486540576 bytes

 Database Buffers         1107296256 bytes

 Redo Buffers                7348224 bytes

 Database mounted.

 Database opened.

 SQL> select database_role from v$database;

 DATABASE_ROLE

 ----------------

 PRIMARY

 SQL> select open_mode,protection_mode,protection_level,switchover_status from v$database;

 OPEN_MODE            PROTECTION_MODE      PROTECTION_LEVEL     SWITCHOVER_STATUS

 -------------------- -------------------- -------------------- --------------------

 READ WRITE           MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  RESOLVABLE GAP

 SQL> /

 OPEN_MODE            PROTECTION_MODE      PROTECTION_LEVEL     SWITCHOVER_STATUS

 -------------------- -------------------- -------------------- --------------------

 READ WRITE           MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  TO STANDBY

观察alert日志可以发现standby已经成功切换成Primary

 Standby became primary SCN: 1362898

 Switchover: Complete - Database mounted as primary

 Completed: alter database commit to switchover to primary

最后,修改primary site和standby site的tnsnames.ora

--END--

巴特西

[Oracle] DataGuard switchover

Oracle DataGuard switchover

最新文章

热门文章