To convert from the snapshot mode to a physical standby, the procedure is the same as discussed earlier. Perform the following steps:
- For validation, perform some DML transactions to verify the number of rows of any table before and after the conversion, as shown in the following query:
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
- Shut down the database and put it in the
MOUNTstatus 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
- After successful conversion, the instance will be brought to the
STARTEDstatus, and you have to perform complete shutdown and startup in theRead Onlymode 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:
