Skip to content

Converting Standby Database to Snapshot (1/4) - Convert by Broker

  • by

Before you convert a physical standby database, you must make sure FRA is configured properly.

[oracle@standby01 ~]$ sqlplus / as sysdba
...
SQL> show parameter db_recovery

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      +DATA
db_recovery_file_dest_size           big integer 20G

Also, make sure Flashback Database is enabled on database-level and tablespace-level in the standby database.

SQL> SELECT flashback_on FROM v$database;

FLASHBACK_ON
------------------
YES

SQL> SELECT name, flashback_on FROM v$tablespace;

NAME                           FLA
------------------------------ ---
SYSTEM                         YES
SYSAUX                         YES
UNDOTBS1                       YES
USERS                          YES
TEMP                           YES
EXAMPLE                        YES
UNDOTBS2                       YES

7 rows selected.

Check the status of broker before conversion.

[oracle@primary01 ~]$ dgmgrl
...
DGMGRL> connect sys/password@primdb1
Connected.
DGMGRL> show configuration

Configuration - COMPDR

  Protection Mode: MaxPerformance
  Databases:
    primdb - Primary database
    standb - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> show database primdb

Database - primdb

  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    primdb1
    primdb2

Database Status:
SUCCESS

DGMGRL> show database standb

Database - standb

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       0 seconds
  Real Time Query: OFF
  Instance(s):
    standb1 (apply instance)
    standb2

Database Status:
SUCCESS

When everything is ready, we can convert it to snapshot standby.

DGMGRL> CONVERT DATABASE standb TO SNAPSHOT STANDBY;
Converting database "standb" to a Snapshot Standby database, please wait...
Operation requires shutdown of instance "standb2" on database "standb"
Shutting down instance "standb2"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Continuing to convert database "standb" ...
Database "standb" converted successfully

Check the status of the broker after conversion

DGMGRL> show configuration

Configuration - COMPDR

  Protection Mode: MaxPerformance
  Databases:
    primdb - Primary database
    standb - Snapshot standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> show database primdb

Database - primdb

  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    primdb1
    primdb2

Database Status:
SUCCESS

DGMGRL> show database standb

Database - standb

  Role:            SNAPSHOT STANDBY
  Intended State:  APPLY-OFF
  Transport Lag:   0 seconds
  Apply Lag:       4 hours 35 minutes 21 seconds
  Instance(s):
    standb1 (apply instance)
    standb2

Database Status:
SUCCESS

You may have noticed that the snapshot standby is APPLY-OFF, no more redo data transported from the primary database will be applied, and Apply Lag will increase as time goes on.

Check the status of the standby database.

[oracle@standby01 ~]$ srvctl status database -d compdb
Instance standb1 is running on node standby01
Instance standb2 is not running on node standby02
[oracle@standby01 ~]$ sqlplus / as sysdba
...
SQL> select inst_id, open_mode, database_role, switchover_status from gv$database;

   INST_ID OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS
---------- -------------------- ---------------- --------------------
         1 READ WRITE           SNAPSHOT STANDBY NOT ALLOWED

SQL> select inst_id, process, status, thread#, sequence#, block#, blocks from gv$managed_standby where process in ('RFS','LNS','MRP0');

   INST_ID PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
---------- --------- ------------ ---------- ---------- ---------- ----------
         1 RFS       IDLE                  0          0          0          0
         1 RFS       IDLE                  0          0          0          0
         1 RFS       IDLE                  1        126        234          1
         1 RFS       IDLE                  2        124        184          1
         1 RFS       IDLE                  0          0          0          0

One instance of the standby database is not running, same phenomenon happened as "switchover", that is, the broker will restart only one instance of the current standby.

Hence, we should restart all instances of the standby database manually.

[oracle@standby01 ~]$ srvctl stop database -d compdb
[oracle@standby01 ~]$ srvctl start database -d compdb -o open

Check the status of the standby database again.

[oracle@standby01 ~]$ sqlplus / as sysdba
...
SQL> select inst_id, open_mode, database_role, switchover_status from gv$database;

   INST_ID OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS
---------- -------------------- ---------------- --------------------
         1 READ WRITE           SNAPSHOT STANDBY NOT ALLOWED
         2 READ WRITE           SNAPSHOT STANDBY NOT ALLOWED

SQL> select inst_id, process, status, thread#, sequence#, block#, blocks from gv$managed_standby where process in ('RFS','LNS','MRP0');

   INST_ID PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
---------- --------- ------------ ---------- ---------- ---------- ----------
         1 RFS       IDLE                  0          0          0          0
         1 RFS       IDLE                  2        127        702          1
         2 RFS       IDLE                  1        129       1118          1
         2 RFS       IDLE                  0          0          0          0

Now, the snapshot standby database is opened to read/write, let's create a table on the snapshot standby and we can expect that the table will be discarded after the snapshot standby is converted back to physical.

[oracle@standby01 ~]$ sqlplus / as sysdba
...
SQL> CREATE TABLE HR.NAME (FIRST_NAME VARCHAR2(20), LAST_NAME VARCHAR2(20));

Table created.

SQL> desc hr.name;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 FIRST_NAME                                         VARCHAR2(20)
 LAST_NAME                                          VARCHAR2(20)

Convert the snapshot standby database to physical standby.

DGMGRL> CONVERT DATABASE standb TO PHYSICAL STANDBY;
Converting database "standb" to a Physical Standby database, please wait...
Operation requires shutdown of instance "standb2" on database "standb"
Shutting down instance "standb2"...
Database closed.
Database dismounted.
ORACLE instance shut down.
Continuing to convert database "standb" ...
Operation requires shutdown of instance "standb1" on database "standb"
Shutting down instance "standb1"...
Database closed.
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "standb1" on database "standb"
Starting instance "standb1"...
ORACLE instance started.
Database mounted.
Continuing to convert database "standb" ...
Operation requires shutdown of instance "standb1" on database "standb"
Shutting down instance "standb1"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "standb1" on database "standb"
Starting instance "standb1"...
ORACLE instance started.
Database mounted.
Database "standb" converted successfully
DGMGRL> show database standb

Database - standb

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       0 seconds
  Real Time Query: OFF
  Instance(s):
    standb1 (apply instance)
    standb2

Database Status:
SUCCESS

Open the standby database to read only and make sure the table HR.NAME is discarded after it converted to a snapshot.

[oracle@standby01 ~]$ srvctl stop database -d compdb
[oracle@standby01 ~]$ srvctl start database -d compdb -o open
[oracle@standby01 ~]$ sqlplus / as sysdba
...
SQL> select inst_id, open_mode, database_role, switchover_status from gv$database;

   INST_ID OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS
---------- -------------------- ---------------- --------------------
         1 READ ONLY WITH APPLY PHYSICAL STANDBY NOT ALLOWED
         2 READ ONLY WITH APPLY PHYSICAL STANDBY NOT ALLOWED

SQL> desc hr.name;
ERROR:
ORA-04043: object hr.name does not exist

The last check showed that any updates in the snapshot standby database will be discarded after it converted back to the physical standby database.

Leave a Reply

Your email address will not be published.