物理dataguard 正常切换 脚色转换

本文详细介绍了Oracle数据库使用DATAGUARD进行角色切换的过程,包括切换前后主备库的状态变化、检查环境的要求及切换的具体步骤。通过本教程,读者可以了解如何在不影响业务的情况下完成数据库的角色转换。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

物理dataguard 正常切换 脚色转换,switchover_status 状态改变:

[@more@]


正常切换

切换前:
主库:

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> SELECT COUNT(*) FROM V$SESSION WHERE USERNAME IS NOT NULL;

COUNT(*)
----------
1


在切换前,杀掉所有的数据库连接 观察SWITCHOVER_STATUS,如果是 TO STANDBY,则可以直接切换
如果SESSIONS ACTIVE ,则用

备用库 :

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 NOT ALLOWED


说明:

主库需要注意事项
A 如果switchover_status为TO_STANDBY说明可以转换
直接转换
alter database commit to switchover to physical standby;
B 如果switchover_status为SESSIONS ACTIVE 则关闭会话
SQL>alter database commit to switchover to physical standby with session shutdown;

在备库中操作,查看备库
SQL> select switchover_status from v$database;

A 如果switchover_status为TO_PRIMARY 说明标记恢复可以直接转换为primary库
SQL>alter database commit to switchover to primary

B 如果switchover_status为SESSION ACTIVE 就应该断开活动会话
SQL>alter database commit to switchover to primary with session shutdown;

C 如果switchover_status为NOT ALLOWED 说明切换标记还没收到,此时不能
执行转换。

切换中 :

备用库
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 SESSIONS ACTIVE

切换后备用的状态,模式:


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


2. 检查环境:

确认主库和从库间网络连接通畅;

确认没有活动的会话连接在数据库中;

PRIMARY数据库处于打开的状态,STANDBY数据库处于MOUNT状态;

确保STANDBY数据库处于ARCHIVELOG模式;

如果设置了REDO应用的延迟,那么将这个设置去掉;

确保配置了主库和从库的初始化参数,使得切换完成后,DATA GUARD机制可以顺利的运行。

如果是最大保护模式,先变成最大性能模式:


3. 切换的顺序: 先从主库到备用,再从备库到主库


主切备:

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>
SQL>
SQL> alter database commit to switchover to physical standby ;

Database altered.

SQL> shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup nomount ;
ORACLE instance started.

Total System Global Area 3242987696 bytes
Fixed Size 733360 bytes
Variable Size 1174405120 bytes
Database Buffers 2063597568 bytes
Redo Buffers 4251648 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

备切主
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

SQL> alter database commit to switchover to primary;

Database altered.

SQL> SQL> SQL>
SQL>
SQL>
SQL> shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup ;
ORACLE instance started.

Total System Global Area 3242987696 bytes
Fixed Size 733360 bytes
Variable Size 1174405120 bytes
Database Buffers 2063597568 bytes
Redo Buffers 4251648 bytes
Database mounted.
Database opened.


修改主 备库 的 tnsnames.ora

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

转载于:http://blog.itpub.net/66233/viewspace-997031/

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值