Using Snapshot Standby
Using Snapshot Standby
This tutorial shows you how to:
- Enable Flashback database on the Physical standby
if not already enabled
- Convert the Physical standby to a Snapshot standby
- Make changes to the Primary and the the Snapshot
standby in some common tables
- Verify that the redo is still being shipped to the
standby
- Convert the Snapshot standby back to a Physical
standby
- Switch logs on the Primary
- Stop the Apply process when the standby is caught
up with the Primary
- Open the Physical standby database read only
- Verify that the Snapshot changes are no longer exist
- Verify that the Primary changes are present.
Approximately 1 hour
This tutorial covers the following topics:
Place
the cursor over this icon to load and view all
the screenshots for this tutorial. (Caution: This action loads all
screenshots simultaneously, so response time may be slow depending on your Internet
connection.)
Note: Alternatively, you can place the cursor over
an individual icon in the following steps to load and view only the screenshot
associated with that step. You can hide an individual screenshot by clicking
it.
What Is a Snapshot Standby Database?
A snapshot standby database is a fully updateable standby
database 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. The redo data received from the
primary database is applied once 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 can be created from Enterprise Manager,
the Data Guard Broker command line interface (DGMGRL) or from SQL*Plus.
In this OBE, we will be using DGMGRL to create a Snapshot
Standby database.
Back to Topic List
Before you perform this tutorial, you should:
| 1. |
Perform an Oracle Database 11g installation (software
and db installed) for a single server environment OR Oracle Database
11g installation (software and db installed) on 1 server and Oracle
Database 11g software only on 2nd server for a dual server environment. |
| 2. |
Creating
a DG Broker Configuration OBE |
Back to Topic List
To enable flashback database, perform the following
steps:
| 1. |
On the physical standby database, determine
if Flashback Database is enabled by querying V$DATABASE.
|
| 2. |
Invoke DGMGRL and connect as sys/oracle.
Disable the MRP process.
|
| 3. |
If Flashback Database has not been enabled
for the physical standby database, enable it by using the
ALTER DATABASE command. You will need to shutdown orclsby1
and issue the command startup
mount to issue the ALTER
DATABASE command.
|
| 4. |
Invoke DGMGRL again and restart the MRP
process
|
Back to Topic List
To convert the physical standby database to a snapshot standby
database, perform the following steps:
Back to Topic List
| 1. |
Invoke DGMGRL in your primary database window and connect to the
primary database.
Convert the physical standby database to a snapshot standby database
by executing the CONVERT DATABASE
TO SNAPSHOT STANDBY DATABASE DGMGRL command.
|
| 2. |
Verify that the database was successfully converted by issuing the
SHOW CONFIGURATION command.
|
Back to Topic
To update the databases, perform the following steps:
| 1. |
To confirm that redo data is being received by the standby
database, query V$MANAGED_STANDBY
on the snapshot standby database and note the value in the BLOCK#
column.
|
| 2. |
Invoke SQL*Plus in your primary database
window and connect as SYSDBA. Insert a row into the HR.REGIONS
table on the primary database as follows:
|
| 3. |
Invoke SQL*Plus in your standby database
window. Insert a row into the HR.REGIONS
table on the snapshot standby database as follows:
|
| 4. |
On the standby database, verify that redo
data is being shipped to the snapshot standby database by again querying
V$MANAGED_STANDBY and
comparing the returned BLOCK#
value with the BLOCK#
value from step 1.
|
Back to Topic
To convert the snapshot standby database to a physical standby
database, perform the following steps:
| 1. |
Now that you have completed your work on
the snapshot standby database, convert the snapshot standby database
back to a physical standby database. You should be on the primary server
to complete this step.
|
| 2. |
Verify the status of the standby database
by executing the SHOW CONFIGURATION
command.
|
| 3. |
Invoke SQL*Plus in your primary database window. Switch
redo log files on the primary database.
|
| 4. |
Invoke DGMGRL in your standby database window. Stop
the MRP process on the physical standby database.
|
| 5. |
Open the physical standby database in read-only mode.
|
| 6. |
Query the HR.REGIONS
table. Note that the change you made when the database was a snapshot
standby database is not present in the table. Note that the INSERT
into the HR.REGIONS that
you performed on the primary database is present in the table.
|
| 7. |
Shut down the physical standby database and restart it
in MOUNT mode. This changes
the physical standby state from read only to a state ready
to receive redo.
|
| 8. |
Invoke DGMGRL and restart the MRP process.
|
Back to Topic
In this tutorial, you learned how to:
Back to Topic List
Place the cursor over this icon to hide all screenshots.
| 
Bookmark