实验DG Snapshot Standby

备库切换为SnapshotStandby

备机的状态

SQL> select DB_UNIQUE_NAME,NAME,database_role,open_mode from v$database;

DB_UNIQUE_NAME                 NAME      DATABASE_ROLE    OPEN_MODE
------------------------------ --------- ---------------- --------------------
newcdbdg                       NEWCDB    PHYSICAL STANDBY READ ONLY WITH APPLY

备库切换为Snapshot Standby

DGMGRL> CONVERT DATABASE newcdbdg to SNAPSHOT STANDBY;
Converting database "newcdbdg" to a Snapshot Standby database, please wait...
Error: ORA-38786: Recovery area is not enabled.

Failed.
Failed to convert database "newcdbdg"

一个报错，原因是备库需要启用recover area

SQL> alter system set db_recovery_file_dest_size=2g;
System altered.

SQL> alter system set db_recovery_file_dest='/arch/recovery';
System altered.

备库再次切换为Snapshot Standby

DGMGRL> CONVERT DATABASE newcdbdg to SNAPSHOT STANDBY;
Converting database "newcdbdg" to a Snapshot Standby database, please wait...
Database "newcdbdg" converted successfully

DGMGRL> SHOW CONFIGURATION;

Configuration - dg_newcdb

  Protection Mode: MaxPerformance
  Members:
  newcdb   - Primary database
    newcdbdg - Snapshot standby database 

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 6 seconds ago)

备库可以读写

SQL> select DB_UNIQUE_NAME,NAME,database_role,open_mode from v$database;

DB_UNIQUE_NAME                 NAME      DATABASE_ROLE    OPEN_MODE
------------------------------ --------- ---------------- --------------------
newcdbdg                       NEWCDB    SNAPSHOT STANDBY READ WRITE

测试备库写入

SQL> create table test as select * from dba_objects;
Table created.

SQL> select count(*) from test;
  COUNT(*)
----------
     72658

备库切换回PhysicalStandby

DGMGRL> CONVERT DATABASE newcdbdg to PHYSICAL STANDBY;
Converting database "newcdbdg" to a Physical Standby database, please wait...
Operation requires shut down of instance "newcdb" on database "newcdbdg"
Shutting down instance "newcdb"...
Unable to connect to database using (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.16.130)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=newcdbdg_DGMGRL)(INSTANCE_NAME=newcdb)(SERVER=DEDICATED)))
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Failed.
Warning: You are no longer connected to ORACLE.

Please complete the following steps and reissue the CONVERT command:
        shut down instance "newcdb" of database "newcdbdg"
        start up and mount instance "newcdb" of database "newcdbdg"

又一个报错，原因是备库需要重启到mount状态再切换回Physical Standby

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  838860800 bytes
Fixed Size                  8798312 bytes
Variable Size             322965400 bytes
Database Buffers          503316480 bytes
Redo Buffers                3780608 bytes
Database mounted.

DGMGRL> CONVERT DATABASE newcdbdg to PHYSICAL STANDBY;
Converting database "newcdbdg" to a Physical Standby database, please wait...
Database "newcdbdg" converted successfully

DGMGRL> SHOW CONFIGURATION;

Configuration - dg_newcdb

  Protection Mode: MaxPerformance
  Members:
  newcdb   - Primary database
    newcdbdg - Physical standby database 

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 29 seconds ago)

DGMGRL> show database newcdbdg;

Database - newcdbdg

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 0 seconds ago)
  Apply Lag:          0 seconds (computed 0 seconds ago)
  Average Apply Rate: 8.00 KByte/s
  Real Time Query:    ON
  Instance(s):
    newcdb

Database Status:
SUCCESS

SQL> show pdbs;  

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 DGPDB1                         READ ONLY  NO
         4 DGPDB2                         READ ONLY  NO
         5 DGPDB3                         READ ONLY  NO

也可以使用以下SQL命令切换

Alter database  convert to snapshot standby;
alter database convert to physical standby;