Snapshot standby features in Oracle 11g

In Oracle 11g, data guard is the most attractive, in addition to the real-time query characteristics of active data guard (that is, the physical standby database can be opened as read-only while the MRP process can continue to do recover), the snapshot standby database is also a good feature , the comparison applies to quickly deploy a temporary test database that is the same as the online environment. It is through the restore point and the principle of the Flashback database (flashback databases), you can open the physical standby database in read/write mode, modify the database, It then recovers to the physical standby database based on the restore point.

The operation is fairly straightforward and the following tests:

I. Physical standby database to snapshot standby database
--View the role of the current standby database
Sql> select Open_mode,database_role from V$database;

Open_mode Database_role
-------------------- ----------------
READ only with APPLY physical STANDBY
--close and start to mount
sql> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
sql> startup Mount;
ORACLE instance started.

Total System Global area 730714112 bytes
Fixed Size 2256832 bytes
Variable Size 482345024 bytes
Database buffers 243269632 bytes
Redo buffers 2842624 bytes
Database mounted.
--Convert physical standby to snapshot standby
Sql> ALTER DATABASE convert to snapshot standby;

Database altered.

Sql> select status from V$instance;

STATUS
------------
Mounted

sql> ALTER DATABASE open;

Database altered.
--View the role of the current standby database
Sql> select Open_mode,database_role from V$database;

Open_mode Database_role
-------------------- ----------------
READ WRITE SNAPSHOT STANDBY

-As you can see, Oracle has built a restore point
Sql> select Name,storage_size from V$restore_point;

NAME
--------------------------------------------------------------------------------
Storage_size
------------
snapshot_standby_required_08/10/2015 05:33:52
52428800

--Test whether the database can be modified
Sql> CREATE TABLE scott.test02 as SELECT * from Dba_objects;

Table created.

Sql> Select COUNT (1) from scott.test02;

COUNT (1)
----------
86267

Two. Snapshot the standby database to the physical standby database
--View the role of the current standby database
Sql> select Open_mode,database_role from V$database;

Open_mode Database_role
-------------------- ----------------
Mounted SNAPSHOT STANDBY

--close and start to mount
sql> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

sql> startup Mount;
ORACLE instance started.

Total System Global area 730714112 bytes
Fixed Size 2256832 bytes
Variable Size 482345024 bytes
Database buffers 243269632 bytes
Redo buffers 2842624 bytes
Database mounted.

--Convert snapshot standby to physical standby
Sql> ALTER DATABASE convert to physical standby;

Database altered.
sql> shutdown immediate;
Ora-01507:database not mounted

ORACLE instance shut down.
Sql> Startup
ORACLE instance started.

Total System Global area 730714112 bytes
Fixed Size 2256832 bytes
Variable Size 482345024 bytes
Database buffers 243269632 bytes
Redo buffers 2842624 bytes
Database mounted.
Database opened.
sql> ALTER DATABASE recover managed standby database disconnect;

Database altered.

--View the role of the current standby database
Sql> select Open_mode,database_role from V$database;

Open_mode Database_role
-------------------- ----------------
READ only with APPLY physical STANDBY

--View the test table that was built at snapshot standby no longer exists.
Sql> select * from scott.test02;
SELECT * FROM scott.test02
*
ERROR at line 1:
Ora-00942:table or view does not exist

Note:
The physical standby is the highest protected mode (maximum protection) and cannot be converted to snapshot standby.
The physical standby uses the standby redo log, after the Create restore point, to alter the system switch logfile, to ensure that the SCN of the restore points is on the physical standby library
Archived, otherwise it may not be possible to flash back to the restore point successfully.
Physical standby after switching to snapshot standby, if the interval is long, the primary database produces a large number of redo logs, which can be converted to physical standby by
Incremental backups of the primary database and recover to physical standby to speed up the physical standby restore.

Snapshot standby features in Oracle 11g