Check the status of standby database before doing a conversion.
[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 MOUNTED PHYSICAL STANDBY NOT ALLOWED
2 MOUNTED PHYSICAL STANDBY NOT ALLOWED
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO
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.
You can see flashback is off on database-level, but it's on on tablespace-level.
Let's do a convert and see whether the conversion is successful or not.
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
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 database status after a conversion.
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 flashback_on from v$database;
FLASHBACK_ON
------------------
RESTORE POINT ONLY
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.
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
RESTORE POINT ONLY
SQL> select name from v$restore_point;
NAME
--------------------------------------------------------------------------------
SNAPSHOT_STANDBY_REQUIRED_03/21/2013 11:29:44
OK, you can see the broker enabled flashback database temporarily and created a restore point for flashback database.
Let's convert it back.
DGMGRL> show database standb
Database - standb
Role: SNAPSHOT STANDBY
Intended State: APPLY-OFF
Transport Lag: 0 seconds
Apply Lag: 12 minutes 58 seconds
Instance(s):
standb1 (apply instance)
standb2
Database Status:
SUCCESS
DGMGRL> convert database standb to physical standby;
Converting database "standb" to a Physical Standby database, please wait...
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>
Converting back to physical standby is also not a problem. The conclusion is that the broker will enable flashback database during conversion for flashing back to physical standby.