Snapshot Standby DB
Prior
to
Oracle Database 11g, the DBA could opt for one of two
mutually-exclusive choices for a physical standby databases
operating mode:
• The database could operate in Managed Recovery mode, in
which redo data would be applied as soon as it was received from
the corresponding primary database. The physical standby would
thus be available immediately for transition to the primary role
during either a switchover or a failover operation.
• Alternatively, the database could be
opened in READ ONLY mode
so that applications could execute queries against it. This
offered the ability to leverage the physical standby database
platform for the intensive read operations that data
warehousing, DSS, and OLAP applications typically perform, thus
offloading that workload from the production database.
While it was certainly possible to maintain two physical standby
databases – one in managed recovery mode, and another in READ
ONLY mode – to satisfy both requirements, it also potentially
increased licensing costs and the complexity of the Data Guard
environment.
Thankfully, Oracle 11g now
offers the capability to satisfy both modes within one physical
standby database with Real Time Query, part of the separately
licensee Active Data Guard option.
This is one of the greatest
features added in Oracle 11g.
A snapshot standby database can allow you to easily test your
application / code on a copy of your REAL production
environment. Once you are done with your tests, you can easily
go back to your Standby DB and apply all the remaining logs that
you already received from PROD.
A snapshot standby database is a fully updateable standby
database where we can basically convert the physical standby
database into a read-write real time clone of the production
database and we can then temporarily use this environment for
carrying out any kind of development testing, QA type work or to
test the impact of a proposed production change on an
application.
Basically, the Snapshot Standby database in turn uses the
Flashback Database technology to create a guaranteed restore
point to which the database can be later flashed back to.
So we need to keep in mind that whatever limitations are present
in the Flashback database technology, the same will also be
inherent in the Snapshot database feature.
Physical to
Standby with SQL Plus
Standby to Physical
with SQL Plus
Physical to
Standby with DGMGRL
Standby to Physical
with DGMGRL
Convert a
Physical Standby to Snapshot Standby with SQL*Plus
1-
Confirm that you have Flashback feature enabled
If that is not enabled, perform the following steps:
1- Review and set (if needed) the db_recovery parameters on both
database(standby & primary) and then activate flashback
On Primary
SQL> show parameters
db_recovery;
NAME
TYPE VALUE
----------------------------
---------- -------------------------
db_recovery_file_dest
string
db_recovery_file_dest_size big integer 0
SQL> alter system set
db_recovery_file_dest_size=50g scope=both;
SQL> alter system set
db_recovery_file_dest=’+ASM_PROD_FRA’ scope=both;
SQL> alter system set
db_flashback_retention_target=60 scope=both;
SQL> shutdown immediate
On Standby
SQL> show parameters
db_recovery;
NAME
TYPE VALUE
----------------------------
---------- -------------------------
db_recovery_file_dest
string
db_recovery_file_dest_size big integer 0
SQL> alter system set
db_recovery_file_dest_size=50g scope=both;
SQL> Alter system set
db_recovery_file_dest=’+ASM_STDBY_FRA’ scope=both;
SQL> alter system set
db_flashback_retention_target=60 scope=both;
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database
flashback on;
SQL> select
name,database_role,flashback_on from v$database;
On Primary
SQL> startup mount
SQL> alter database
flashback on;
SQL> alter database open;
SQL> select
name,database_role,flashback_on from v$database;
2-
Convert the Standby in Snapshot Standby
After the conversion takes place, internally a guaranteed restore
point has been created and the database has been opened in
read-write mode
SQL> shutdown immediate
SQL> startup mount
SQL> ALTER DATABASE
CONVERT TO SNAPSHOT STANDBY;
After that you are ready to perform any DDL and DML operations on
it.
SQL> shutdown immediate;
SQL> startup;
As you can see, this process is very simple, you just type a few
commands, re-start your DB and you are ready to test this amazing
feature!!!
Convert
Snapshot Standby back to Physical Standby with SQL Plus
The Redo data received while the database was a snapshot
standby database will be automatically applied when Redo Apply is
started.
Note:
A snapshot standby database must be opened at least once in
read-write mode before it can be converted into a physical standby
database.
Connect to the Stanbdy DB, shut it down and mount it:
SQL> shutdown immediate;
SQL> startup mount;
Convert the DB.
SQL> ALTER DATABASE
CONVERT TO PHYSICAL STANDBY;
Shutdown and mount it again:
SQL> shutdown immediate;
SQL> startup mount
Finally, enable recovery mode:
SQL> ALTER DATABASE
RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
Convert
Physical Standby to Snapshot Standby with DGMGRL
As you can see, this process is very simple, you just type a few
commands, re-start your DB and you are ready to test this amazing
feature!!!
Redo data will continue to be received by the database while it is
operating as a snapshot standby database, but it will not be applied
until the snapshot standby is converted back into a physical standby
database.
Note that the Flashback Database feature is required to create a
snapshot standby database.
If Flashback database is disabled, it is automatically enabled
during conversion to a snapshot standby database.
The broker automatically restarts the database to the mounted state
if it had been opened with Flashback Database disabled. No user
action is required.
1-
Connect to DG Broker
$ dgmgrl
DGMGRL> connect sys
DGMGRL> show configuration
2-
Convert the DB.
After the conversion takes place, internally a guaranteed restore
point has been created and the database has been opened in
read-write mode
DGMGRL> convert database
'STDBY' to snapshot standby;
3-
Verify that the database was successfully converted by issuing the
SHOW CONFIGURATION command:
DGMGRL> show configuration
Configuration - prod_dg
Protection Mode:
MaxPerformance
Databases:
PROD - Primary database
STDBY – Snapshot standby
database
Fast-Start Failover: DISABLED
Configuration Status: SUCCESS
Convert
Snapshot Standby back to Physical Standby with DGMGRL
The Redo data received while the database was
a snapshot standby database will be automatically applied when Redo
Apply is started.
Note:
A snapshot standby database must be opened at least once in
read-write mode before it can be converted into a physical standby
database.
1-
Connect to DG Broker.
You should be on the primary server to complete this step.
$ dgmgrl
DGMGRL > connect sys
2-
Convert the DB back to physical
DGMGRL > convert database 'stdby' to physical standby;
3-
Verify that the database was successfully converted by issuing the
SHOW CONFIGURATION command:
DGMGRL> show
configuration
Configuration - prod_dg
Protection Mode:
MaxPerformance
Databases:
PROD
- Primary database
STDBY –
Physical standby database
Fast-Start Failover:
DISABLED
Configuration Status: SUCCESS
NOTE:
You may get the following message during the conversion process:
“Converting database "stdby" to a Physical Standby database, please
wait...
Operation requires shutdown of instance "STDBY" on database "stdby"
Shutting down instance "STDBY"...
Database closed.
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "STDBY" on database "stdby"
Starting instance "STDBY"...
Unable to connect to database
ORA-12514: TNS:listener does not currently know of service requested
in connect descriptor
Failed.
Warning: You are no longer connected to ORACLE.
Please complete the following steps and reissue the CONVERT command:
start up and mount
instance "STDBY" of database "stdby“
If so, then connect to the STDBY and startup and mount the instance:
startup mount
a