Skip to content

Converting Standby Database to Snapshot (3/4) - With Flashback Database Disabled

  • by
  • March 25, 2013August 5, 2020
Converting Standby Database to Snapshot (2/4) - Convert Manually
It would be better to make sure flashback database is enabled before conversion, but it seems not a required step because the broker will try to enable flashback during conversion. Let's see a demonstration on this.

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.

Converting Standby Database to Snapshot (4/4) - With Flashback Tablespace Disabled

Leave a Reply

Your email address will not be published.