Dataguard standby DB处于READ ONLY WITH APPLY状态
1、检查备库ADG运行状况,是否有延迟
select name,value from v$dataguard_stats;
2、若备库无延时,停止备库日志应用功能
alter database recover managed standby database cancel;
3、关闭standby数据库,并重新启动到mount状态(如果是备库是RAC环境,关闭所有节点,然后启动任一节点到mount)
shutdown immediate;
startup mount;
4、切换数据库到snapshot模式,并open数据库
alter database convert to snapshot standby;
alter database open;
(如果是备库是RAC环境,将其它节点startup)
SQL> select dbid,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database;
DBID NAME CURRENT_SCN PROTECTION_MODE DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS
---------- --------- ----------- -------------------- ---------------- --- -------------------- --------------------
3777632382 EISOO 11891369 MAXIMUM PERFORMANCE SNAPSHOT STANDBY YES READ WRITE NOT ALLOWED
3777632382 EISOO 11891366 MAXIMUM PERFORMANCE SNAPSHOT STANDBY YES READ WRITE NOT ALLOWED
此时数据库处于snapshot模式,可以提供读写功能
观察执行snapshot转换节点的告警日志,可以发现下面的相关信息
Created guaranteed restore point SNAPSHOT_STANDBY_REQUIRED_12/10/2016 14:38:09
说明切换snapshot时数据库创建了一个恢复时间点,当再从snapshot模式切回physical standby时,数据库会回到该时间点,然后重新从该时间点应用主库日志。
该时间点以后的时间段在snapshot模式下所有的数据修改都会消失。
snapshot模式下备库仍然从主库接收日志,只是没有应用日志。
从snapshot模式切换回physical standby模式
1、关闭所有节点,并将其中一个节点启动到mount
SQL> select database_role,open_mode from v$database;
DATABASE_ROLE OPEN_MODE
---------------- --------------------
SNAPSHOT STANDBY MOUNTED
2、在mount的节点执行切换
SQL> alter database convert to physical standby;
Database altered.
切换后的状态为nomount
SQL> select instance_name,status from gv$instance;
INSTANCE_NAME STATUS
---------------- ------------
eisoo2 STARTED
3、关闭该节点,并重新启动所有节点到open
SQL> select dbid,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database;
DBID NAME CURRENT_SCN PROTECTION_MODE DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS
---------- ---------------------------------------------------------------------- ----------- -------------------- ---------------- --- -------------------- --------------------
3777632382 EISOO 11886987 MAXIMUM PERFORMANCE PHYSICAL STANDBY YES READ ONLY RECOVERY NEEDED
3777632382 EISOO 11886987 MAXIMUM PERFORMANCE PHYSICAL STANDBY YES READ ONLY RECOVERY NEEDED
4、开启实时应用(任一节点执行)
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
SQL> select dbid,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database;
DBID NAME CURRENT_SCN PROTECTION_MODE DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS
---------- ---------------------------------------------------------------------- ----------- -------------------- ---------------- --- -------------------- --------------------
3777632382 EISOO 11886987 MAXIMUM PERFORMANCE PHYSICAL STANDBY YES READ ONLY WITH APPLY RECOVERY NEEDED
3777632382 EISOO 11886987 MAXIMUM PERFORMANCE PHYSICAL STANDBY YES READ ONLY WITH APPLY RECOVERY NEEDED
1、检查备库ADG运行状况,是否有延迟
select name,value from v$dataguard_stats;
2、若备库无延时,停止备库日志应用功能
alter database recover managed standby database cancel;
3、关闭standby数据库,并重新启动到mount状态(如果是备库是RAC环境,关闭所有节点,然后启动任一节点到mount)
shutdown immediate;
startup mount;
4、切换数据库到snapshot模式,并open数据库
alter database convert to snapshot standby;
alter database open;
(如果是备库是RAC环境,将其它节点startup)
SQL> select dbid,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database;
DBID NAME CURRENT_SCN PROTECTION_MODE DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS
---------- --------- ----------- -------------------- ---------------- --- -------------------- --------------------
3777632382 EISOO 11891369 MAXIMUM PERFORMANCE SNAPSHOT STANDBY YES READ WRITE NOT ALLOWED
3777632382 EISOO 11891366 MAXIMUM PERFORMANCE SNAPSHOT STANDBY YES READ WRITE NOT ALLOWED
此时数据库处于snapshot模式,可以提供读写功能
观察执行snapshot转换节点的告警日志,可以发现下面的相关信息
Created guaranteed restore point SNAPSHOT_STANDBY_REQUIRED_12/10/2016 14:38:09
说明切换snapshot时数据库创建了一个恢复时间点,当再从snapshot模式切回physical standby时,数据库会回到该时间点,然后重新从该时间点应用主库日志。
该时间点以后的时间段在snapshot模式下所有的数据修改都会消失。
snapshot模式下备库仍然从主库接收日志,只是没有应用日志。
从snapshot模式切换回physical standby模式
1、关闭所有节点,并将其中一个节点启动到mount
SQL> select database_role,open_mode from v$database;
DATABASE_ROLE OPEN_MODE
---------------- --------------------
SNAPSHOT STANDBY MOUNTED
2、在mount的节点执行切换
SQL> alter database convert to physical standby;
Database altered.
切换后的状态为nomount
SQL> select instance_name,status from gv$instance;
INSTANCE_NAME STATUS
---------------- ------------
eisoo2 STARTED
3、关闭该节点,并重新启动所有节点到open
SQL> select dbid,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database;
DBID NAME CURRENT_SCN PROTECTION_MODE DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS
---------- ---------------------------------------------------------------------- ----------- -------------------- ---------------- --- -------------------- --------------------
3777632382 EISOO 11886987 MAXIMUM PERFORMANCE PHYSICAL STANDBY YES READ ONLY RECOVERY NEEDED
3777632382 EISOO 11886987 MAXIMUM PERFORMANCE PHYSICAL STANDBY YES READ ONLY RECOVERY NEEDED
4、开启实时应用(任一节点执行)
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
SQL> select dbid,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database;
DBID NAME CURRENT_SCN PROTECTION_MODE DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS
---------- ---------------------------------------------------------------------- ----------- -------------------- ---------------- --- -------------------- --------------------
3777632382 EISOO 11886987 MAXIMUM PERFORMANCE PHYSICAL STANDBY YES READ ONLY WITH APPLY RECOVERY NEEDED
3777632382 EISOO 11886987 MAXIMUM PERFORMANCE PHYSICAL STANDBY YES READ ONLY WITH APPLY RECOVERY NEEDED
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30373263/viewspace-2130207/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/30373263/viewspace-2130207/