Oracle Database 11g introduced the Snapshot Standby feature in Data Guard. Using this feature you can use your physical standby database to temporary process transactions. If you have a need to test out application fixes or new functionality you can leverage your physical standby while still protecting the primary database.
A Snapshot Standby database still receives redo data from the primary but it does not apply the redo data until after it converted back to a physical standby. Keep in mind that a snapshot standby database cannot be the target of a switchover or failover. A snapshot must be converted back to a physical standby prior to performing a role transition. Flashback Database technology is used in the conversion process so the Fast (Flash) Recovery area must be configured.
This document will detail the steps to manually convert a physical standby to a snapshot standby.
Convert the Physical Standby Database into a Snapshot Standby Database
On the standby database stop redo apply.
|
1
2
3
4
5 |
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> |
Next convert the standby database to a snapshot standby.
|
1
2
3
4
5 |
SQL> alter database convert to snapshot standby;
Database altered.
SQL> |
Once the conversion is complete all that is left is to open the database.
|
1
2
3
4
5 |
SQL> alter database open;
Database altered.
SQL> |
You can verify the role change by querying the DATABASE_ROLE from V$DATABASE.
|
1
2
3
4
5
6
7 |
SQL> select database_role from v$database;
DATABASE_ROLE
----------------
SNAPSHOT STANDBY
SQL> |
While the standby is in snapshot standby mode you are free to run transactions against the snapshot standby.
While the standby is in snapshot mode it still continues to receive redo data from the primary but it does not apply the redo data. You verify the transport by switching logs on the primary and looking at the alert log on the standby.
|
1
2
3
4
5
6 |
Thu Jun 17 11:15:41 2010
RFS[6]: Selected log 5 for thread 1 sequence 981 dbid 459961910 branch 719914169
Thu Jun 17 11:15:41 2010
Archived Log entry 1513 added for thread 1 sequence 980 ID 0x1b7c5492 dest 2:
RFS[6]: Selected log 4 for thread 1 sequence 982 dbid 459961910 branch 719914169
Thu Jun 17 11:15:42 2010 |
When the standby was converted to a snapshot a guaranteed restore point was created. You can see this in the alert log for the standby.
|
1
2
3
4
5
6
7 |
Thu Jun 17 09:44:44 2010
RVWR started with pid=30, OS id=9171
Allocated 3981120 bytes in shared pool for flashback generation buffer
Created guaranteed restore point SNAPSHOT_STANDBY_REQUIRED_06/17/2010 09:44:44
krsv_proc_kill: Killing 3 processes (all RFS)
Begin: Standby Redo Logfile archival
End: Standby Redo Logfile archival |
When the snapshot standby is converted back into a physical standby this restore point will be used to flashback the standby to its original state prior to the conversion. If any operation is performed on the snapshot that cannot be reversed with Flashback Database will prevent the snapshot standby from being converted back to a physical standby.
Convert the Snapshot Standby Database back to a Physical Standby Database
Shutdown the snapshot standby database and bring it back up in the mount state.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22 |
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 830930944 bytes
Fixed Size 2217912 bytes
Variable Size 603981896 bytes
Database Buffers 222298112 bytes
Redo Buffers 2433024 bytes
Database mounted.
SQL>
Next convert the snapshot to a physical standby.
1
SQL> alter database convert to physical standby;
Database altered.
SQL> |
In the standby alert log you can see that Flashback restore completed and the restore point was dropped.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21 |
Thu Jun 17 11:48:29 2010
alter database convert to physical standby
ALTER DATABASE CONVERT TO PHYSICAL STANDBY (standby)
krsv_proc_kill: Killing 4 processes (all RFS)
Flashback Restore Start
Flashback Restore Complete
Stopping background process RVWR
Deleted Oracle managed file /u01/app/flash_recovery_area/STANDBY/flashback/o1_mf_61nf6w8g_.flb
Deleted Oracle managed file /u01/app/flash_recovery_area/STANDBY/flashback/o1_mf_61ngk05r_.flb
Guaranteed restore point dropped
Clearing standby activation ID 461943091 (0x1b88b133)
The primary database controlfile was created using the
'MAXLOGFILES 16' clause.
There is space for up to 13 standby redo logfiles
Use the following SQL commands on the standby database to create
standby redo logfiles that match the primary database:
ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 52428800;
Completed: alter database convert to physical standby |
Shutdown the database and bring it back to the mount state.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14 |
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 830930944 bytes
Fixed Size 2217912 bytes
Variable Size 603981896 bytes
Database Buffers 222298112 bytes
Redo Buffers 2433024 bytes
Database mounted.
SQL> |
If you take a look in the alert log you will see that the archive logs shipped when the standby was a snapshot standby are now applied.
|
1
2
3
4
5
6
7
8 |
Media Recovery Log /u01/app/oracle/oradata/standby/arch/1_970_719914169.dbf
Media Recovery Log /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_971_719914169.dbf
Media Recovery Log /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_972_719914169.dbf
Media Recovery Log /u01/app/oracle/oradata/standby/arch/1_973_719914169.dbf
Media Recovery Log /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_974_719914169.dbf
Media Recovery Log /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_975_719914169.dbf
Media Recovery Log /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_976_719914169.dbf
Media Recovery Log /u01/app/oracle/oradata/standby/arch/1_977_719914169.dbf |
Using Snapshot Standby you can leverage your standby for testing or other special purposes temporarily will still protecting your primary database.