Snapshot standby database是ORACLE 11g的新特性。允许Physical standby短时间的使用read write模式。
Snapshot standby是由Physical standby 全新转换而来,可以独立于primary 处理事务,同时能够不断地从primary接受redo data,归档redo data以备后用维护保护。
alter system set db_recovery_file_dest_size=5G scope=both sid='*'; alter system set db_recovery_file_dest='/oradata/fra' scope=both sid='*';
若为启用FRA时,会遇到错误ORA-38784和ORA-38786。
SQL> alter database convert to snapshot standby; alter database convert to snapshot standby * ERROR at line 1: ORA-38784: Cannot create restore point 'SNAPSHOT_STANDBY_REQUIRED_12/01/2020 10:23:25'. ORA-38786: Recovery area is not enabled.
需要注意的是,启动FRA时,db_recovery_file_dest_size要先于db_recovery_file_dest设置,否则会遇到错误ORA-19802: cannot use DB_RECOVERY_FILE_DEST without DB_RECOVERY_FILE_DEST_SIZE
alert.log
Tue Dec 01 10:26:12 2020 ALTER SYSTEM SET db_recovery_file_dest_size='5G' SCOPE=BOTH SID='*'; Using STANDBY_ARCHIVE_DEST parameter default value as USE_DB_RECOVERY_FILE_DEST ALTER SYSTEM SET db_recovery_file_dest='/oradata/fra' SCOPE=BOTH SID='*'; Tue Dec 01 10:26:18 2020 db_recovery_file_dest_size of 5120 MB is 0.00% used. This is a user-specified limit on the amount of space that will be used by this database for recovery-related files, and does not reflect the amount of space available in the underlying filesystem or ASM diskgroup.
SQL> select name,DATABASE_ROLE,SWITCHOVER_STATUS from v$database; NAME DATABASE_ROLE SWITCHOVER_STATUS --------- ---------------- -------------------- SYK PHYSICAL STANDBY NOT ALLOWED SQL> alter database recover managed standby database cancel; Database altered.
SQL> shutdown immediate SQL> startup mount
SQL> alter database convert to snapshot standby; Database altered.
alert.log
Tue Dec 01 10:26:28 2020 alter database convert to snapshot standby Starting background process RVWR Tue Dec 01 10:26:28 2020 RVWR started with pid=24, OS id=34478 Allocated 8388608 bytes in shared pool for flashback generation buffer Created guaranteed restore point SNAPSHOT_STANDBY_REQUIRED_12/01/2020 10:26:28 <----- 1 Begin: Standby Redo Logfile archival End: Standby Redo Logfile archival RESETLOGS after incomplete recovery UNTIL CHANGE 1143899 Waiting for all non-current ORLs to be archived... All non-current ORLs have been archived. Resetting resetlogs activation ID 1250286088 (0x4a85da08) Online log /oradata/SYK/redo01.log: Thread 1 Group 1 was previously cleared Online log /oradata/SYK/redo02.log: Thread 1 Group 2 was previously cleared Online log /oradata/SYK/redo03.log: Thread 1 Group 3 was previously cleared Standby became primary SCN: 1143897 <----- 2 Tue Dec 01 10:26:28 2020 Setting recovery target incarnation to 3 WARNING: STANDBY_FILE_MANAGEMENT initialization parameter is not set to the value "AUTO". This may cause recovery of the standby database to terminate prior to applying all available redo data. It may be necessary to use the ALTER DATABASE CREATE DATAFILE command to add datafiles created on the primary database. CONVERT TO SNAPSHOT STANDBY: Complete - Database mounted as snapshot standby <----- 3 Completed: alter database convert to snapshot standby
SQL> alter database open; Database altered. SQL> select open_mode,name,DATABASE_ROLE,SWITCHOVER_STATUS from v$database; OPEN_MODE NAME DATABASE_ROLE SWITCHOVER_STATUS -------------------- --------- ---------------- -------------------- READ WRITE SYK SNAPSHOT STANDBY NOT ALLOWED
alert.log
Tue Dec 01 10:26:44 2020 alter database open Tue Dec 01 10:26:44 2020 Assigning activation ID 1250413997 (0x4a87cdad) Thread 1 opened at log sequence 1 Current log# 1 seq# 1 mem# 0: /oradata/SYK/redo01.log Successful open of redo thread 1 MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set Tue Dec 01 10:26:44 2020 SMON: enabling cache recovery [34375] Successfully onlined Undo Tablespace 2. Undo initialization finished serial:0 start:1273929404 end:1273929484 diff:80 (0 seconds) Dictionary check beginning Dictionary check complete Verifying file header compatibility for 11g tablespace encryption.. Verifying 11g file header compatibility for tablespace encryption completed SMON: enabling tx recovery Database Characterset is AL32UTF8 No Resource Manager plan active ********************************************************** WARNING: Files may exists in db_recovery_file_dest that are not known to the database. Use the RMAN command CATALOG RECOVERY AREA to re-catalog any such files. If files cannot be cataloged, then manually delete them using OS command. One of the following events caused this: 1. A backup controlfile was restored. 2. A standby controlfile was restored. 3. The controlfile was re-created. 4. db_recovery_file_dest had previously been enabled and then disabled. ********************************************************** replication_dependency_tracking turned off (no async multimaster replication found) Starting background process QMNC Tue Dec 01 10:26:45 2020 QMNC started with pid=25, OS id=34494 LOGSTDBY: Validating controlfile with logical metadata LOGSTDBY: Validation complete Completed: alter database open
SQL> shutdown immediate SQL> startup mount SQL> select open_mode,name,DATABASE_ROLE,SWITCHOVER_STATUS from v$database; OPEN_MODE NAME DATABASE_ROLE SWITCHOVER_STATUS -------------------- --------- ---------------- -------------------- MOUNTED SYK SNAPSHOT STANDBY NOT ALLOWED
SQL> alter database convert to physical standby;
此时数据库切换成功后,其状态未非mount状态,需要重启重新mount。
SQL> select open_mode,name,DATABASE_ROLE,SWITCHOVER_STATUS from v$database; select open_mode,name,DATABASE_ROLE,SWITCHOVER_STATUS from v$database * ERROR at line 1: ORA-01507: database not mounted
alert.log
Tue Dec 01 13:21:27 2020 alter database convert to physical standby ALTER DATABASE CONVERT TO PHYSICAL STANDBY (SYK) Flashback Restore Start Flashback Restore Complete Drop guaranteed restore point <----- 删除GRP Stopping background process RVWR Deleted Oracle managed file /oradata/fra/SYK/flashback/o1_mf_hwcbgnl0_.flb Deleted Oracle managed file /oradata/fra/SYK/flashback/o1_mf_hwcbgp0n_.flb Guaranteed restore point dropped Clearing standby activation ID 1250413997 (0x4a87cdad) The primary database controlfile was created using the 'MAXLOGFILES 16' clause. There is space for up to 13 standby redo logfiles Use the following SQL commands on the standby database to create standby redo logfiles that match the primary database: ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 52428800; ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 52428800; ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 52428800; ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 52428800; Shutting down archive processes Archiving is disabled Tue Dec 01 13:21:29 2020 ARCH shutting down ARC0: Archival stopped Tue Dec 01 13:21:29 2020 ARCH shutting down ARC3: Archival stopped Tue Dec 01 13:21:29 2020 ARCH shutting down ARC2: Archival stopped Tue Dec 01 13:21:29 2020 ARCH shutting down ARC1: Archival stopped Completed: alter database convert to physical standby <----- 完成切换
SQL> shutdown immediate SQL> startup mount SQL> alter database open read only; SQL> select open_mode,name,DATABASE_ROLE,SWITCHOVER_STATUS from v$database; OPEN_MODE NAME DATABASE_ROLE SWITCHOVER_STATUS -------------------- --------- ---------------- -------------------- READ ONLY SYK PHYSICAL STANDBY RECOVERY NEEDED SQL> alter database recover managed standby database using current logfile disconnect from session; Database altered. SQL> select open_mode,name,DATABASE_ROLE,SWITCHOVER_STATUS from v$database; OPEN_MODE NAME DATABASE_ROLE SWITCHOVER_STATUS -------------------- --------- ---------------- -------------------- READ ONLY WITH APPLY SYK PHYSICAL STANDBY NOT ALLOWED
alert.log
Tue Dec 01 13:26:41 2020 alter database recover managed standby database using current logfile disconnect from session Attempt to start background Managed Standby Recovery process (SYK) Tue Dec 01 13:26:41 2020 MRP0 started with pid=24, OS id=45436 MRP0: Background Managed Standby Recovery process started (SYK) started logmerger process Tue Dec 01 13:26:46 2020 Managed Standby Recovery starting Real Time Apply Parallel Media Recovery started with 2 slaves Waiting for all non-current ORLs to be archived... All non-current ORLs have been archived. Clearing online redo logfile 1 /oradata/SYK/redo01.log Clearing online log 1 of thread 1 sequence number 1 Clearing online redo logfile 1 complete Clearing online redo logfile 2 /oradata/SYK/redo02.log Clearing online log 2 of thread 1 sequence number 2 Clearing online redo logfile 2 complete Media Recovery Log /oraarch/1_22_1057859074.arc Completed: alter database recover managed standby database using current logfile disconnect from session Media Recovery Log /oradata/fra/SYK/archivelog/2020_12_01/o1_mf_1_23_hwcbph9p_.arc Media Recovery Log /oraarch/1_24_1057859074.arc Recovery of Online Redo Log: Thread 1 Group 13 Seq 25 Reading mem 0 Mem# 0: /oradata/stby_redo13.log