To convert from the snapshot mode to a physical standby, the procedure is the same as discussed earlier. Perform the following steps:
SQL> select open_mode from v$database; OPEN_MODE -------------------- READ WRITE SQL> conn packt/packt; Connected. SQL> select count(*) from packt.oracle; COUNT(*) ---------- 41943040 SQL> insert into oracle select * from oracle where sal > 4500; 2097152 rows created. SQL> commit; Commit complete. SQL> select count(*) from packt.oracle; COUNT(*) ---------- 44040192
MOUNT
status to initiate the conversion as follows:SQL> alter database convert to physical standby; Database altered. SQL>
The following output will be visible:
Wed Nov 07 22:28:12 2012 alter database convert to physical standby ALTER DATABASE CONVERT TO PHYSICAL STANDBY (INDIA) krsv_proc_kill: Killing 2 processes (all RFS) Flashback Restore Start Wed Nov 07 22:30:23 2012 Flashback Restore Complete
STARTED
status, and you have to perform complete shutdown and startup in the Read Only
mode with the recovery mode as the standby database for the purpose of reporting, as shown in the following query:SQL> select count(*) from packt.oracle; COUNT(*) ---------- 41943040
From step 1, the number of rows inserted are 2097152, and after performing a flashback to the restore point, all the newly inserted rows will be reverted.
We've just revised how to convert a database from a snapshot standby to a physical standby using the SQL* Plus command and we also verified how the new DMLs are reverted using the flashback restore point.
We have converted the physical standby to a snapshot standby database for read and write purposes using traditional SQL *Plus. This procedure can also be accomplished using a broker. By using SQL *Plus, we have to bounce the database to the MOUNT
status; if you are managing it using the broker, it will handle this automatically. Refer to the following screenshot for a better understanding: