前言:
环境介绍:
主库:10.9.21.57
备库:10.9.21.59
要想完成主从切换,需要保证如下几点:
1.需要保证主备库都得设置了下面的参数
*.log_archive_config='DG_CONFIG=(stdb59,testdb57)'
*.log_archive_dest_2='SERVICE=testdb57
LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=testdb57'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.LOG_FILE_NAME_CONVERT='/data/u01/app/oracle/oradata/testdb57','/data/u01/app/oracle/oradata/stdb59'
*.DB_FILE_NAME_CONVERT='/data/u01/app/oracle/oradata/testdb57','/data/u01/app/oracle/oradata/stdb59'
最好都设置:
FAL_SERVER
2.如果你想实时应用的话,主备库都得添加了standby redo log;
概述:本文首先介绍正常的switchover,这样方式用于主从切换演练,数据库迁移等等,接着会介绍另一种切换方式是failover,也就是当主库crash了,需要把从库提升为新的主库,failover可能会丢失部分数据!
首先介绍switchover:
一:在主库上执行(10.9.21.57)
1.查看主库的状态:
SQL> select name,open_mode,protection_mode from v$database;
NAME OPEN_MODE PROTECTION_MODE
--------- -------------------- --------------------
TESTDB57 READ WRITE MAXIMUM PERFORMANCE
SQL> select switchover_status ,database_role from v$database;
SWITCHOVER_STATUS DATABASE_ROLE
-------------------- ----------------
TO STANDBY PRIMARY
SWITCHOVER_STATUS–>TO STANDBY,表示可以正常切换.
SWITCHOVER_STATUS–>SESSIONS ACTIVE,表示当前有会话处于ACTIVE状态,此时切换的话,需要加参数with session shutdown wait
2.在主库执行切换:
SQL> alter database commit to switchover to physical standby with session shutdown wait ;
Database altered.
3.主库执行切换之后,发现主库实例已经down了!
SQL> select switchover_status ,database_role from v$database;
select switchover_status ,database_role from v$database
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 18269
Session ID: 196 Serial number: 9
4.启动主库到mount状态
SQL> startup mount
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size 2253664 bytes
Variable Size 973081760 bytes
Database Buffers 620756992 bytes
Redo Buffers 7319552 bytes
Database mounted.
5查看主库的状态,发现数据库已经变成了PHYSICAL STANDBY的角色
SQL> select switchover_status ,database_role from v$database;
SWITCHOVER_STATUS DATABASE_ROLE
-------------------- ----------------
RECOVERY NEEDED PHYSICAL STANDBY
6.以redo only方式启动新的standby 库
SQL> alter database open read only;
Database altered.
二:备库上操作:(10.9.21.59)
1.主库的切换操作会传输到备库,备库的状态就会自动转换为to primary状态;接下来查看备库的状态:
SQL> select name,open_mode,protection_mode from v$database;
NAME OPEN_MODE PROTECTION_MODE
--------- -------------------- -----------------------------------------------------------------
TESTDB57 READ ONLY WITH APPLY MAXIMUM PERFORMANCE
SQL> select switchover_status ,database_role from v$database;
SWITCHOVER_STATUS DATABASE_ROLE
-------------------- ----------------
TO PRIMARY PHYSICAL STANDBY
2.确认没有问题后,可以进行切换转换standby 到primary 角色,这个过程会把数据库从open read only的状态变成为mounted的状态,他必须这样,因为之前是read only的,现在他要变成主了,需要以read write方式从新open;
SQL> alter database commit to switchover to primary;
Database altered.
3.再次查看备库的状态,发现数据库角色变成了primary,
SQL> select switchover_status ,database_role from v$database;
SWITCHOVER_STATUS DATABASE_ROLE
-------------------- ----------------
NOT ALLOWED PRIMARY
4.此时查看新的主库的状态,发现确实已经由之前的open read only的状态变成为mounted的状态,
SQL> select status from v$instance;
STATUS
------------
MOUNTED
5.以read write方式从新open
SQL> alter database open;
Database altered.
三:回到新的从库( 10.9.21.57)
1.打开mrp进程应用日志,重演变化!
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
2.查看从库的各个进程的状态,
SQL> select process,status from v$managed_standby;
PROCESS STATUS
--------- ------------
ARCH CLOSING
ARCH CONNECTED
ARCH CONNECTED
ARCH CLOSING
MRP0 APPLYING_LOG
RFS IDLE
RFS IDLE
RFS IDLE
验证:
在主库创建一个表
1.10.9.21.59 上操作:
SQL> create table liuwenhe.liuwenhe(id int ,name varchar(100));
Table created.
SQL> insert into liuwenhe.liuwenhe values ( 1,'liuwenhe');
1 row created.
SQL> commit;
Commit complete.
2.在10.9.21.57 上查看是够已经同步过来,如下已经同步过来了!
SQL> select * from liuwenhe.liuwenhe;
ID NAME
---------- ------------------------------------------
1 liuwenhe
至此主从switchover切换完毕了
接着介绍failover的操作过程:
一:在主库上操作(10.9.21.57)
1.模拟主库crash的场景:
[root@testdb57 ~]# reboot
二:在从库执行:( 10.9.21.59)
1.查看有没有gap
SQL> select * from v$archive_gap;
no rows selected
2.查看此时的状态:
SQL> select name,open_mode,switchover_status, DATABASE_ROLE,PROtection_mode from v$database;
NAME OPEN_MODE SWITCHOVER_STATUS DATABASE_ROLE PROTECTION_MODE
--------- -------------------- -------------------- ---------------- --------------------
TESTDB57 READ ONLY WITH APPLY NOT ALLOWED PHYSICAL STANDBY MAXIMUM PERFORMANCE
3.初始化failover,相当于switchover的时候在主库执行了 alter database commit to switchover to physical standby with session shutdown wait;
SQL> alter database recover managed standby database finish force;
Database altered.
4.再次查看备库的状态,发现SWITCHOVER_STATUS 变成了TO PRIMARY;
SQL> select name,open_mode,switchover_status, DATABASE_ROLE,protection_mode from v$database;
NAME OPEN_MODE SWITCHOVER_STATUS DATABASE_ROLE PROTECTION_MODE
--------- -------------------- -------------------- ---------------- --------------------
TESTDB57 MOUNTED TO PRIMARY PHYSICAL STANDBY MAXIMUM PERFORMANCE
5.将备课转换成主库!
SQL> alter database commit to switchover to primary;
Database altered.
6.再次查看备库的状态,发现database_role变成了 PRIMARY了!并且open_mode变成了mounted的状态了!
SQL> select name,open_mode,switchover_status, DATABASE_ROLE,PROtection_mode from v$database;
NAME OPEN_MODE SWITCHOVER_STATUS DATABASE_ROLE PROTECTION_MODE
--------- -------------------- -------------------- ---------------- --------------------
TESTDB57 MOUNTED NOT ALLOWED PRIMARY MAXIMUM PERFORMANCE
7.打开新的主
SQL> alter database open ;
Database altered.
至此oracle11g的主从failover切换完毕!
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29654823/viewspace-2158290/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29654823/viewspace-2158290/