snapshot standby switch

physical standby切换至snapshot standby

The standby database opens the flash recovery area

According to the amount of data written later,设置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='*';

Insufficient flash recovery area,Write operations will be stuck,Expand the flashback recovery area,告警日志如下

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运行状况,Check for delays

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

停止备库日志应用

Confirm that there is no delay in the standby database,Stop the standby log application function

alter database recover managed standby database cancel;

关闭standby重启到mount

RACThe environment requires all nodes to be shut down,Then start any node tomount状态

shutdown immediate;
startup mount;

切换至snapshot standby

切换数据库到snapshot模式,并open数据库,RACThe environment will be other nodesstartup

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

关闭所有节点,and start one of the nodes into itmount状态

shutdown immediate
startup mount

SQL> select database_role,open_mode from v$database;

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

切换至physical standby

在mountNode performs the switch

alter database convert to physical standby;

SQL> select instance_name,status from gv$instance;

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

重启所有节点

关闭mount节点,and restart all nodes toopen状态

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

关闭闪回恢复区

The snapshot library is no longer enabled later,You can reset the parameters related to the flash recovery area

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;