Snapshot standby features in Oracle 11g

Snapshot standby features in Oracle 11g

Among Oracle 11g, data guard is the most attractive. In addition to the Real-Time query feature of active data guard (you can open the physical standby database in read-only mode while the MRP process can continue to do recover ), snapshot Backup database is also a good feature and is suitable for quick deployment of a temporary test database that is the same as the online environment. it uses the restore point and flashback database principle to open the physical backup database in read/write mode and modify the database, then restore the database to the physical standby database based on the Restore Point.

The operation is quite simple. Test the following:

1. Physical backup database to snapshot backup database
-- View the role of the current backup 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 backup database
SQL> select open_mode, database_role from v $ database;

OPEN_MODE DATABASE_ROLE
------------------------------------
READ WRITE SNAPSHOT STANDBY

-- We can see that oracle has created 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

Ii. snapshot backup database to physical backup database
-- View the role of the current backup 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 the snapshot standby to the 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 backup database
SQL> select open_mode, database_role from v $ database;

OPEN_MODE DATABASE_ROLE
------------------------------------
READ ONLY WITH APPLY PHYSICAL STANDBY

-- Check that the test table created at the time of snapshot standby does not exist.
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 mode is maximum protection and cannot be converted to snapshot standby.
The physical standby uses the standby redo log. After the create restore point is created, the system switches logfile; To ensure that the scn of the restore point is in the physical standby Database
Otherwise, the Restore point may fail.
After the physical standby is switched to the snapshot standby, if the interval is long, the primary database generates a large number of redo logs, so that after being converted to the physical standby
Incremental backup of the primary database and recover it to the physical standby to speed up the restoration of the physical standby.