dataguard主备switchover互切实验及理解

SWITCHOVER是人为计划的互相切换主备，FAILOVER是主库故障的情况下，把备库切换为主库



SWITHOVER大致步骤就是下面几步（主库先切换为standby，备库再切换为primary）
主库
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;
SQL> startup mount;
备库
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
SQL> alter database open
现备库原主库
SQL> alter database recover managed standby database disconnect from session;
SQL>alter database recover managed standby database cancel;
SQL>alter database open read only;
SQL>alter database recover managed standby database disconnect from session;






实验环境介绍
主库（已经DBCA建立了DB）
192.168.128.37
Red Hat Enterprise Linux Server release 6.3 (Santiago)
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
Service/service_names/db_name/db_unique_name/instance_name=TDB


备库（没有建立DB，只安装了ORACLE软件，和主库目录结构一致）
192.168.128.47
Red Hat Enterprise Linux Server release 6.3(Santiago)
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
Service/service_names/db_unique_name=TDG
db_name/instance_name=TDB


主库备库的tns都如下
master =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.128.37)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TDB)
    )
  )


slave =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.128.47)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TDG)
    )
  )


DATAGUARD搭建实验步骤
1.主库确保处于ARCHIVELOG、force logging模式
2.主库在线修改一下几个参数，并create pfile from spfile，主库把刚创建的pfile（$ORACLE_HOME/dbs/initTDB.ora）和
密码文件（$ORACLE_HOME/dbs/orapwTDB）拷贝到备库的$ORACLE_HOME/dbs目录
fal_client='master'
fal_server='slave'
standby_file_management='AUTO'
log_archive_config='DG_CONFIG=(TDB,TDG)'
remote_login_passwordfile='EXCLUSIVE'
log_archive_dest_1='location=/u01/app/oracle/archivelog
log_archive_dest_2='service=slave lgwr async VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=TDG'
3.主库备份数据文件和当前控制文件
rman>backup database format '/u01/backup/full_%U.bak';
rman>backup current controlfile for standby format '/u01/backup/control_%U.bak';
4.主库把刚生成的备份拷贝到备库/u01/backup/目录
5.备库修改从主库拷贝过来的pfile（$ORACLE_HOME/dbs/initTDB.ora）文件
增加*.db_unique_name='TDG'
修改如下三个参数为
*.fal_client='slave'
*.fal_server='master'
*.log_archive_dest_2='service=master lgwr async VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=TDB'
6.备库顺序执行如下
ORACLE_SID=TDB
sqlplus / as sysdba
SQL>startup nomount
SQL>exit
rman target /
RMAN>restore standby controlfile from '/u01/backup/control_%U.bak';
RMAN>alter database mount
RMAN>restore database;
RMAN>exit;
sqlplus / as sysdba
SQL>alter database recover managed standby database disconnect from session;
SQL>select sequence#,applied from v$archived_log;
（主库执行alter system archive log current后，执行上面这个语句看主库的日志是否已经在备库应用了，应用了的话再执行如下语句）
SQL>alter database recover managed standby database cancel;
SQL>alter database open read only;
SQL>alter database recover managed standby database disconnect from session;







SWITHOVER切换实验过程

1.查看主备库都正常的情况下，如下语句的结果
主SQL> select database_role, switchover_status from v$database;
DATABASE_ROLE    SWITCHOVER_STATUS
---------------- --------------------
PRIMARY          SESSIONS ACTIVE

备SQL> select database_role, switchover_status from v$database;
DATABASE_ROLE    SWITCHOVER_STATUS
---------------- --------------------
PHYSICAL STANDBY NOT ALLOWED


2.主库执行
上面第1步知道主库的SWITCHOVER_STATUS是SESSIONS ACTIVE，执行如下
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;
官方文档记录：A value of TO STANDBY or SESSIONS ACTIVE indicates that the primary database can be switched to the standby role.


3.再执行第1步的两个语句看什么情况
主SQL> select database_role, switchover_status from v$database;
select database_role, switchover_status from v$database
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 13039
Session ID: 36 Serial number: 7505

备SQL> select database_role, switchover_status from v$database;
DATABASE_ROLE    SWITCHOVER_STATUS
---------------- --------------------
PHYSICAL STANDBY TO PRIMARY


4. 主库启动数据库：
SQL> startup mount;


5.再执行第1步的两个语句看什么情况
主SQL> select database_role, switchover_status from v$database;
DATABASE_ROLE    SWITCHOVER_STATUS
---------------- --------------------
PHYSICAL STANDBY RECOVERY NEEDED

备SQL> select database_role, switchover_status from v$database;
DATABASE_ROLE    SWITCHOVER_STATUS
---------------- --------------------
PHYSICAL STANDBY TO PRIMARY


6.备库执行
上面第5步知道备库的SWITCHOVER_STATUS是TO PRIMARY，执行如下
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
官方文档记录：A value of TO PRIMARY or SESSIONS ACTIVE indicates that the standby database is ready to be switched to the primary role


7.再执行第1步的两个语句看什么情况
主SQL> select database_role, switchover_status from v$database;
DATABASE_ROLE    SWITCHOVER_STATUS
---------------- --------------------
PHYSICAL STANDBY RECOVERY NEEDED

备SQL> select database_role, switchover_status from v$database;
DATABASE_ROLE    SWITCHOVER_STATUS
---------------- --------------------
PRIMARY          NOT ALLOWED
 

8.备库开启到open状态（上面第6步执行后备库的v$database.open_mode会从read only状态到mounted状态）
SQL> alter database open


9.再执行第1步的两个语句看什么情况
原主SQL> select database_role, switchover_status from v$database;
DATABASE_ROLE    SWITCHOVER_STATUS
---------------- --------------------
PHYSICAL STANDBY RECOVERY NEEDED

原备SQL> select database_role, switchover_status from v$database;
DATABASE_ROLE    SWITCHOVER_STATUS
---------------- --------------------
PRIMARY          TO STANDBY|SESSIONS ACTIVE(在两者之间切来切去)


10.原主库即目前备库，执行
SQL> alter database recover managed standby database disconnect from session;
SQL>alter database recover managed standby database cancel;
SQL>alter database open read only;
SQL>alter database recover managed standby database disconnect from session;


11.再执行第1步的两个语句看什么情况
现备，原主
SQL> select database_role, switchover_status from v$database;
DATABASE_ROLE    SWITCHOVER_STATUS
---------------- --------------------
PHYSICAL STANDBY          NOT ALLOWED


现主，原备
SQL> select database_role, switchover_status from v$database;
DATABASE_ROLE    SWITCHOVER_STATUS
---------------- --------------------
PRIMARY               TO STANDBY|SESSIONS ACTIVE(在两者之间切来切去)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30126024/viewspace-2128272/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30126024/viewspace-2128272/