testcontent
Using Snapshot Standby
Purpose
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.
Time to Complete
Approximately 1 hour
Topics
This tutorial covers the following topics:
Viewing Screenshots
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.
Overview
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.
Prerequisites
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. | ||
Enabling Flashback Database on the Physical Standby Database
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
|
Converting the Physical Standby Database to a Snapshot Standby Database
To convert the physical standby database to a snapshot standby database, perform the following steps:
| 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.
|
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.
|
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.
|
In this tutorial, you learned how to:
Printer View