漫活部落

Snapshot Standby Characteristics

1. 快照資料庫會續繼接收和歸檔，但不會進行應用. 
2. 在轉換為物理資料庫後，會去自動應用從主資料庫傳來的數據. 
3. 從主資料庫傳來的數據資料，會被安全的保存在適當的位置.
4. 所有在快照資料庫上做的更新，在轉回物理資料庫後都會被丟棄. 
5. 如果主資料庫有做快閃還原或Open resetlogs，快照資料庫會繼續接收新主資料庫的數據. 
6. 快照資料庫不能是切換/故障切換的目標，必需先轉回物理資料庫才能被當成切換的目標. 
7. 在主資料庫進行物理資料庫或邏輯資料庫間的切換，快照資料庫會繼續接收新主資料庫傳送的數據.
8. 在最大保護的架構設定中，快照資料庫不能是唯一一台備用資料庫.

1. Snapshot standby database receives and archives, but does not apply the redo data. 
2. Redo data received from the primary database is applied automatically once it is converted back into a physical standby database. 
3. Data from the primary database is always protected as the archives are being received and stored in place.
4. All local updates will be discarded when snapshot database is converted back to physical standby database. 
5. If the primary database moves to new database branch (for example, because of a Flashback Database or an OPEN RESETLOGS), the snapshot standby database will continue accepting redo from new database branch. 
6. Snapshot standby database cannot be the target of a switchover or failover. A snapshot standby database must first be converted back into a physical standby database before performing a role transition to it. 
7. After a switchover or failover between the primary database and one of the physical or logical standby databases in a configuration, the snapshot standby database can receive redo data from the new primary database after the role transition. 
8. Snapshot standby database cannot be the only standby database in a Maximum Protection Data Guard configuration.

 

Check and Setting db_recovery_file_dest_size & db_recovery_file_dest 

1. 確認Standby db_recovery_file_dest_size & db_recovery_file_dest 已設定
(Make sure Standby db_recovery_file_dest_size & db_recovery_file_dest are set)

SQL> show parameter db_recovery_file_dest     

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest		     string	 +DATADG
db_recovery_file_dest_size	     big integer 10G

2. 如果未設定則需補上設定
(If not already configured , configure flash recovery area as given below)

SQL> alter system set db_recovery_file_dest_size=<size> scope=both sid='*';
SQL> alter system set db_recovery_file_dest=<path> scope=both sid='*';

 

Convert to Snapshot Standby

1. 確認Standby 資料庫是在 mount 階段
(Bring the physical standby database to mount stage)

SQL> select open_mode,database_role from v$database; 

OPEN_MODE	     DATABASE_ROLE
-------------------- ----------------
MOUNTED 	     PHYSICAL STANDBY

2. 停止Dataguard同步服務
(Stop managed recovery if it is active)

SQL> alter database recover managed standby database cancel;

3. 轉換至 snapshot standby
(Convert physical standby database to snapshot standby database)

SQL> alter database convert to snapshot standby;

如果未停止Dataguard同步，則會出現錯誤 ORA-38784 & ORA-01153
(Error ORA-38784 & ORA-01153 if Dataguard managed recovery is not stopped)

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_02/17/2022
10:42:53'.
ORA-01153: an incompatible media recovery is active

4. 檢查狀態
(Check database status)

SQL> select open_mode, database_role from v$database;

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

5. 開啟 Snapshot Database
(Open Snapshot database)

SQL> alter database open;

SQL> select open_mode, database_role from v$database;

OPEN_MODE	     DATABASE_ROLE
-------------------- ----------------
READ WRITE	     SNAPSHOT STANDBY

 

Convert to Physical Standby

1. 重啟 Snapshot Standby 資料庫到 mount 階段
(Restart the Snapshot Standby database to the mount stage)

SQL> shutdown immediate;
SQL> startup mount;

SQL> select open_mode, database_role, flashback_on from v$database;

OPEN_MODE	     DATABASE_ROLE    FLASHBACK_ON
-------------------- ---------------- ------------------
MOUNTED 	     SNAPSHOT STANDBY RESTORE POINT ONLY

2. 轉換至 physical standby
(Convert to physical standby)

SQL> alter database convert to physical standby;

3. 檢查資料庫是否恢復 Physical Standby
(Check if the database is converted to Physical Standby)

SQL> select open_mode, database_role, flashback_on from v$database;

OPEN_MODE	     DATABASE_ROLE    FLASHBACK_ON
-------------------- ---------------- ------------------
MOUNTED 	     PHYSICAL STANDBY NO

4. 啟動 Dataguard 同步服務
(Start the media recovery process)

SQL> alter database recover managed standby database disconnect from session;

 

using dgbroker

1. 使用 Data Guard Broker 只需要一行指令就能完成轉換
(Use Data Guard Broker with just one line of command)

DGMGRL> CONVERT DATABASE <db_unique_name> TO {SNAPSHOT | PHYSICAL} STANDBY;

DGMGRL> show configuration;

Configuration - orcldb

  Protection Mode: MaxPerformance
  Members:
  orcl1    - Primary database
    orclstby - Snapshot standby database   <= Snapshot standby role

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 9 seconds ago)

 

參考來源：

Using Snapshot Standby Database. (Doc ID 443720.1)

已轉向 blogger 記錄

https://slowlife-notes.blogspot.com