What is Snapshot Standby database (from Oracle Docs):
A snapshot standby database is a fully updatable standby database that is created by converting a physical standby database into a snapshot standby database. A snapshot standby database receives and archives, but does not apply, redo data from a primary database. Redo data received from the primary database is applied when a snapshot standby database is converted back into a physical standby database, after discarding all local updates to the snapshot standby database.
A snapshot standby database typically diverges from its primary database over time because redo data from the primary database is not applied as it is received. Local updates to the snapshot standby database will cause additional divergence. The data in the primary database is fully protected however, because a snapshot standby can be converted back into a physical standby database at any time, and the redo data received from the primary will then be applied.
A snapshot standby database provides disaster recovery and data protection benefits that are similar to those of a physical standby database. Snapshot standby databases are best used in scenarios where the benefit of having a temporary, updatable snapshot of the primary database justifies additional administrative complexity and increased time to recover from primary database failures.
Below is an example of How to convert Physical Standby Database into Snapshot Standby database.
1. Login to Standby database.
[code]</pre>
TESTDB) (/app/oracle/product/11.1.0/db_1)
oracle@houoradb1 $ ss
SQL*Plus: Release 11.1.0.7.0 – Production on Fri Aug 31 16:18:48 2012
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
<pre>[/code]
2. Check if the Flashback on standby is Turned ON?
[code]</pre>
SQL> set lines 10000
SQL> select flashback_on from v$database;
FLASHBACK_ON
——————
NO
<pre>[/code]
Since we are using Snapshot Standby database feature, we dont need to Turn on Flashback as it will be taken care by the process only.
Check the Size for Flash Recovery Area and if not set, set it to appropriate Size. This will be used by Database to hold the Guaranteed Restore Point.
[code]</pre>
SQL> show parameter db_recovery
NAME TYPE VALUE
———————————— ———– ——————————
db_recovery_file_dest string /dr/flash_recovery_area
db_recovery_file_dest_size big integer 20G
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /arclog/TESTDB/arch
Oldest online log sequence 6661
Next log sequence to archive 0
Current log sequence 6663
QL> select open_mode from v$database;
OPEN_MODE
———-
MOUNTED
SQL> select PROCESS,CLIENT_PROCESS,THREAD#,SEQUENCE#,BLOCK# from v$managed_stand by where process = ‘MRP0′ or client_process=’LGWR’;
PROCESS CLIENT_P THREAD# SEQUENCE# BLOCK#
——— ——– ———- ———- ———-
RFS LGWR 1 6663 40217
MRP0 N/A 1 6663 40216
SQL> select NAME,SCN,TIME from v$restore_point;
no rows selected
<pre>[/code]
3. Convert the Database to Snapshot Standby database.
First of all cancel the Media Recovery.
[code]</pre>
SQL> alter database recover managed standby database cancel;
Database altered.
<pre>[/code]
Convert the Standby database to Snapshot Standby database.
[code]</pre>
SQL> alter database convert to snapshot standby;
Database altered.
<pre>[/code]
4. Shutdown the database
[code]</pre>
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
<pre>[/code]
5. Startup the database.
[code]</pre>
SQL> startup mount
ORACLE instance started.
Total System Global Area 1068937216 bytes
Fixed Size 2166536 bytes
Variable Size 515899640 bytes
Database Buffers 545259520 bytes
Redo Buffers 5611520 bytes
Database mounted.
SQL> select flashback_on from v$database;
FLASHBACK_ON
——————
RESTORE POINT ONLY
SQL> select NAME,SCN,TIME from v$restore_point;
NAME S CN TIME
——————————————————————————– ———————————————— ———- ——————– ——————————————————-
SNAPSHOT_STANDBY_REQUIRED_08/31/2012 16:22:12 5.9743E+12 31-AUG-12 04.22.12.000000000 PM
SQL> alter database open;
Database altered.
SQL> select controlfile_type,open_mode from v$database;
CONTROL OPEN_MODE
——- ———-
CURRENT READ WRITE
<pre>[/code]
6. Do some work in Database ( Some changes.)
[code]
SQL> create user nupneja identified by nupneja;
User created.
SQL> grant connect,resource to nupneja;
Grant succeeded.
SQL> conn nupneja/nupneja
Connected.
SQL> create table snapshot_test as select * from all_tables;
Table created.
SQL> select count(*) from snapshot_test;
COUNT(*)
———-
104
SQL> conn / as sysdba
Connected.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /arclog/TESTDB/arch
Oldest online log sequence 1
Next log sequence to archive 1
Current log sequence 1
SQL> show parameter db_recovery
NAME TYPE VALUE
———————————— ———– ——————————
db_recovery_file_dest string /dr/flash_recovery_area
db_recovery_file_dest_size big integer 20G
SQL> alter system switch logfile;
System altered.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – 64 bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[/code]
6. Now convert snapshot standby database to Physical Standby database
[code]
(TESTDB) (/arclog/TESTDB/arch)
oracle@houoradb1 $ ss
SQL*Plus: Release 11.1.0.7.0 – Production on Fri Aug 31 16:30:59 2012
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> <strong>shutdown immediate</strong>
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> <strong>startup mount</strong>
ORACLE instance started.
Total System Global Area 1068937216 bytes
Fixed Size 2166536 bytes
Variable Size 528482552 bytes
Database Buffers 532676608 bytes
Redo Buffers 5611520 bytes
Database mounted.
<strong>SQL> alter database convert to physical standby;</strong>
<strong> </strong>
Database altered.
<strong>SQL> shutdown immediate</strong>
ORA-01507: database not mounted
ORACLE instance shut down.
<strong>SQL> startup mount</strong>
ORACLE instance started.
Total System Global Area 1068937216 bytes
Fixed Size 2166536 bytes
Variable Size 528482552 bytes
Database Buffers 532676608 bytes
Redo Buffers 5611520 bytes
Database mounted.
<strong>SQL></strong> <strong>select open_mode,controlfile_type ,flashback_on from v$database;</strong>
<strong> </strong>
OPEN_MODE CONTROL FLASHBACK_ON
———- ——- ——————
MOUNTED STANDBY NO
<strong>SQL> select name,scn from v$restore_point;</strong>
no rows selected
[/code]
7. Open standby database in Read only mode to check if the changes that we made are rolled back
[code]</pre>
<table width="100%" border="1" cellspacing="0" cellpadding="0">
<tbody>
<tr>
<td valign="top">database recover managed standby database cancel;Database altered.
SQL> alter database opn read only;
alter database opn read only
*
ERROR at line 1:
ORA-02231: missing or invalid option to ALTER DATABASE
SQL> alter database open read only;
Database altered.
SQL> select username from dba_users where username=’NUPNEJA’;
no rows selected
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1068937216 bytes
Fixed Size 2166536 bytes
Variable Size 528482552 bytes
Database Buffers 532676608 bytes
Redo Buffers 5611520 bytes
Database mounted.
<pre>[/code]
You see all the changes have been discarded from the standby database. Now you can go ahead check the routine stuff like Logs are getting applied on Standby database as usual etc…
Hope the small demo will be helpful to others….
Thanks….