SNAPSHOT STANDBY、PHYSICAL STANDBY

Oracle DG：物理备库、快照备库的相互转换

1、将物理备库转换成快照备库（SNAPSHOT STANDBY）

1.1 转换前检查

–检查同步状态

select process,status from v$managed_standby where process='MRP0';

PROCESS   STATUS
--------- ------------
MRP0      APPLYING_LOG

–检查延迟时间

select (sysdate-(sysdate-TO_DSINTERVAL(value)))*86400 as lag_seconds
from v$dataguard_stats
where name='apply lag';

LAG_SECONDS
-----------
          0

1.2 取消日志应用

alter database recover managed standby database cancel;

Database altered.

1.3 停止数据库

srvctl stop database -d cnbmwagas

1.4 启动数据库到mount

starup mount

ORACLE instance started.

Total System Global Area 5.7982E+10 bytes
Fixed Size                 23087272 bytes
Variable Size            1.0737E+10 bytes
Database Buffers         4.6976E+10 bytes
Redo Buffers              245346304 bytes
Database mounted.

1.5 转换为快照备库

ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;

Database altered.

1.6 停止数据库

shutdown immediate;

ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.

1.7 启动数据库

srvctl start database -d cnbmwagas

1.8 转换后检查

– 查看数据库状态

select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

2、将快照备库转换成物理备库（PHYSICAL STANDBY）

2.1 停止数据库

srvctl stop database -d cnbmwagas

2.2 启动数据库到mount

starup mount

ORACLE instance started.

Total System Global Area 5.7982E+10 bytes
Fixed Size                 23087272 bytes
Variable Size            1.0737E+10 bytes
Database Buffers         4.6976E+10 bytes
Redo Buffers              245346304 bytes
Database mounted.

2.3 转换为物理备库

ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

Database altered.

2.4 停止数据库

shutdown immediate;

ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.

2.5 启动数据库

srvctl start database -d cnbmwagas

2.6 应用日志

– 查看数据库状态

alter database recover managed standby database using current logfile disconnect from session;

Database altered.

2.7 转换后检查

– 检查同步状态

select process,status from v$managed_standby where process='MRP0';

PROCESS   STATUS
--------- ------------
MRP0      APPLYING_LOG

– 检查延迟时间

select (sysdate-(sysdate-TO_DSINTERVAL(value)))*86400 as lag_seconds
from v$dataguard_stats
where name='apply lag';

LAG_SECONDS
-----------
          0

– 检查数据库状态

select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY WITH APPLY

文档说明：本文档为个人工作中的总结，适用于DG环境迁移前测试场景，拿去使用。