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 20GAlso, 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:
SUCCESSWhen 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 successfullyCheck 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:
SUCCESSYou 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 0One 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 openCheck 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 0Now, 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:
SUCCESSOpen 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 existThe last check showed that any updates in the snapshot standby database will be discarded after it converted back to the physical standby database.