snapshot standby切换

physical standby切换至snapshot standby

备库开启闪回恢复区

根据后期写入数据量，设置db_recovery_file_dest_size大小

alter system set db_recovery_file_dest_size=20G scope=both sid='*';
alter system set db_recovery_file_dest='+DATADG' scope=both sid='*';

闪回恢复区不足，写操作会夯住，扩大闪回恢复区即可，告警日志如下

Errors in file /u01/app/oracle/diag/rdbms/orcl_dg1/orcl/trace/orcl_m000_10060.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 134217728 bytes is 98.83% used, and has 1572864 remaining bytes available.
************************************************************************
You have following choices to free up space from recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
   then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
   BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
   reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
   system command was used to delete files, then use RMAN CROSSCHECK and
   DELETE EXPIRED commands.
************************************************************************


*************************************************************
Unable to allocate flashback log of 809 blocks from
current recovery area of size 134217728 bytes.
Recovery Writer (RVWR) is stuck until more space
is available in the recovery area.
Unable to write Flashback database log data because the
recovery area is full, presence of a guaranteed
restore point and no reusable flashback logs.

检查备库ADG

检查备库ADG运行状况，确认是否有延迟

select name,value from v$dataguard_stats;

NAME                             VALUE
-------------------------------- ----------------------------------------------------------------
transport lag                    +00 00:00:00
apply lag                        +00 00:00:00
apply finish time                +00 00:00:00.000
estimated startup time           21

停止备库日志应用

确认备库无延迟，停止备库日志应用功能

alter database recover managed standby database cancel;

关闭standby重启到mount

RAC环境需要关闭所有节点，然后启动任意节点到mount状态

shutdown immediate;
startup mount;

切换至snapshot standby

切换数据库到snapshot模式，并open数据库，RAC环境将其他节点startup

alter database convert to snapshot standby;
alter database 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    FORCE_LOGGING        OPEN_MODE            SWITCHOVER_STATUS
---------- --------- ----------- -------------------- ---------------- -------------------- -------------------- --------------------
1046462443 RACDB        17295546 MAXIMUM PERFORMANCE  SNAPSHOT STANDBY YES                  READ WRITE           NOT ALLOWED
1046462443 RACDB        17295546 MAXIMUM PERFORMANCE  SNAPSHOT STANDBY YES                  READ WRITE           NOT ALLOWED

snapshot standby切换至physical standby

关闭数据库，启动到mount

关闭所有节点，并将其中一个节点启动到mount状态

shutdown immediate
startup mount

SQL> select database_role,open_mode from v$database;

DATABASE_ROLE    OPEN_MODE
---------------- --------------------
SNAPSHOT STANDBY MOUNTED

切换至physical standby

在mount节点执行切换

alter database convert to physical standby;

SQL> select instance_name,status from gv$instance;

INSTANCE_NAME    STATUS
---------------- ------------
tzracdb1         MOUNTED

重启所有节点

关闭mount节点，并重新启动所有节点到open状态

shutdown immediate
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    FORCE_LOGGING        OPEN_MODE            SWITCHOVER_STATUS
---------- --------- ----------- -------------------- ---------------- -------------------- -------------------- --------------------
1046462443 RACDB        17290122 MAXIMUM PERFORMANCE  PHYSICAL STANDBY YES                  READ ONLY            RECOVERY NEEDED
1046462443 RACDB        17290122 MAXIMUM PERFORMANCE  PHYSICAL STANDBY YES                  READ ONLY            RECOVERY NEEDED

开启实时应用

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


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    FORCE_LOGGING        OPEN_MODE            SWITCHOVER_STATUS
---------- --------- ----------- -------------------- ---------------- -------------------- -------------------- --------------------
1046462443 RACDB        17295481 MAXIMUM PERFORMANCE  PHYSICAL STANDBY YES                  READ ONLY WITH APPLY NOT ALLOWED
1046462443 RACDB        17295481 MAXIMUM PERFORMANCE  PHYSICAL STANDBY YES                  READ ONLY WITH APPLY NOT ALLOWED

关闭闪回恢复区

后期不再启用快照库，可以重置闪回恢复区相关参数

alter system set db_recovery_file_dest='' scope=both sid='*';
alter system reset db_recovery_file_dest_size scope=spfile sid='*';
shutdown immediate
startup
alter database recover managed standby database using current logfile disconnect from session;