Loading....

One of the very good new features in Oracle 11g is the Snapshot 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 fetaure.

Let us illustrate this feature by using an example where we would like to test the impact of the change of some important optimizer related parameters on a key application query using the actual production data.

In production, the SQL query below is shown to be peforming a full table/partition scan and using hash joins. We would like to see if a nested loop join and index usage will prove beneficial to this SQL query that is used frequently by the application.

explain plan for
SELECT c.cust_last_name, sum(s.amount_sold) AS dollars,sum(s.quantity_sold) as quantity
FROM sales s , customers c, products p
WHERE c.cust_id = s.cust_id
AND s.prod_id = p.prod_id
AND c.cust_state_province IN ('Dublin','Galway')GROUP BY c.cust_last_name
/

SQL> select * from table(dbms_xplan.display);


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
Plan hash value: 1248656060

------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |             |   519 | 23355 |   935   (6)| 00:00:12 |       |       |
|   1 |  HASH GROUP BY         |             |   519 | 23355 |   935   (6)| 00:00:12 |       |       |
|*  2 |   HASH JOIN            |             | 99644 |  4378K|   927   (5)| 00:00:12 |       |       |
|   3 |    INDEX FULL SCAN     | PRODUCTS_PK |    72 |   288 |     1   (0)| 00:00:01 |       |       |
|*  4 |    HASH JOIN           |             | 99644 |  3989K|   924   (5)| 00:00:12 |       |       |
|*  5 |     TABLE ACCESS FULL  | CUSTOMERS   |   766 | 18384 |   408   (1)| 00:00:05 |       |       |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
|   6 |     PARTITION RANGE ALL|             |   918K|    14M|   507   (6)| 00:00:07 |     1 |    28 |
|   7 |      TABLE ACCESS FULL | SALES       |   918K|    14M|   507   (6)| 00:00:07 |     1 |    28 |
------------------------------------------------------------------------------------------------------

We will now convert the physical standby database to a Snapshot Standby database and note that after the conversion takes place, internally a guaranteed restore point has been created and the database has been opened in read-write mode. Also note the DATABASE_ROLE column.

SQL>  ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;

Database altered.


SQL> shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area  521936896 bytes
Fixed Size                  2139784 bytes
Variable Size             415238520 bytes
Database Buffers           96468992 bytes
Redo Buffers                8089600 bytes
Database mounted.
Database opened.


SQL> select NAME,GUARANTEE_FLASHBACK_DATABASE from  v$restore_point;

NAME                                                         GUA
------------------------------------------------------------ ---
SNAPSHOT_STANDBY_REQUIRED_09/02/2009 10:12:21                YES


SQL> select open_mode,database_role from v$database;

OPEN_MODE  DATABASE_ROLE
---------- ----------------
READ WRITE SNAPSHOT STANDBY

We will now make some changes to some optimizer related parameters:

SQL> alter system set optimizer_index_cost_adj=20;

System altered.

SQL> alter system set optimizer_index_caching=10;

System altered.

SQL> alter system set optimizer_mode=FIRST_ROWS;

System altered.

We run the same query again and we can see that the plan has changed and the optimizer is now using the index scan with nested loop joins.

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                      |   519 | 23355 |   588  (11)| 00:00:08 |
|   1 |  HASH GROUP BY                        |                      |   519 | 23355 |   588  (11)| 00:00:08 |
|   2 |   NESTED LOOPS                        |                      | 99644 |  4378K|   580  (10)| 00:00:07 |
|*  3 |    HASH JOIN                          |                      | 99644 |  3989K|   577  (10)| 00:00:07 |
|*  4 |     TABLE ACCESS BY INDEX ROWID       | CUSTOMERS            |   766 | 18384 |   241   (2)| 00:00:03 |
|   5 |      BITMAP CONVERSION TO ROWIDS      |                      |       |       |            |          |
|   6 |       BITMAP INDEX FULL SCAN          | CUSTOMERS_GENDER_BIX |       |       |            |          |
|   7 |     PARTITION RANGE ALL               |                      |   918K|    14M|   326  (12)| 00:00:04 |
|   8 |      TABLE ACCESS BY LOCAL INDEX ROWID| SALES                |   918K|    14M|   326  (12)| 00:00:04 |
|   9 |       BITMAP CONVERSION TO ROWIDS     |                      |       |       |            |          |
|  10 |        BITMAP INDEX FULL SCAN         | SALES_PROMO_BIX      |       |       |            |          |
|* 11 |    INDEX UNIQUE SCAN                  | PRODUCTS_PK          |     1 |     4 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------

While the original physical standby database has been now converted to snapshot database, some changes are happening on the Primary database and those changes are shipped to the standby site but not yet applied. They will accumulate on the standby site and will be applied after the snapshot standby database is converted back to a physical standby database.

SQL> create table mytest as select * from sales;

Table created.

SQL> update mytest set prod_id=1;

918843 rows updated.

On the standby site, we will now convert the shapshot standby database to the original mode of physical standby database in Active Data Guard mode.

Note that after the physical standby database is opened, changes that were made on the Primary database while it was open in shapshot standby mode are now applied as well.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  521936896 bytes
Fixed Size                  2139784 bytes
Variable Size             415238520 bytes
Database Buffers           96468992 bytes
Redo Buffers                8089600 bytes
Database mounted.
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

Database altered.

SQL> shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.


SQL> startup mount;
ORACLE instance started.

Total System Global Area  521936896 bytes
Fixed Size                  2139784 bytes
Variable Size             415238520 bytes
Database Buffers           96468992 bytes
Redo Buffers                8089600 bytes
Database mounted.
SQL>  recover managed standby database disconnect;
Media recovery complete.
SQL> recover managed standby database cancel;
Media recovery complete.
SQL> alter database open;

Database altered.

Elapsed: 00:01:06.29
SQL> recover managed standby database using current logfile disconnect;
Media recovery complete.


SQL> select count(*) from sh.mytest;

  COUNT(*)
----------
    918843

SQL> select distinct prod_id from sh.mytest;

   PROD_ID
----------
         1

Please follow and like us:

Last Update: September 2, 2009  

September 2, 2009 143 Gavin Soorma
Total 0 Votes:
0

Tell us how can we improve this post?

+ = Verify Human or Spambot ?

Add A Knowledge Base Question !

You will receive an email when your question will be answered.

+ = Verify Human or Spambot ?

, ,

2 thoughts on “11g Snapshot Standby for real time testing

  1. Chandra Cheedella

    Gavin, great article and thank you very much for showing us a test case with examples. However, could you please clarify little bit more on what happened after converting back to physical standby (real-time apply) as below.
    1) Why the testing related table/data still exist? Is it not flashed back automatically?
    2) How the physical standby is able to continue media recovery even when the test data exists after converting to phy standby? What if I did some dml changes on a table and media recovery is going to take care of it?
    2) Does the DBA need to flashback manually to the restore point (internally created) to keep it 100% sync with primary DB? What are the commands?

    Thank you.

Leave a Comment

Your email address will not be published. Required fields are marked *

*
*

Social media & sharing icons powered by UltimatelySocial
Back To Top

Add A Knowledge Base Question !

You will receive an email when your question will be answered.

+ = Verify Human or Spambot ?