11g Datagaurd for EBS R12 & 11i

Hi Everyone!!

Here is interesting piece with 11g.
EBS R12 and 11i got certified with 11g Database last year. You may would like to visit Steven's
blog

R12 certified with 11g
11i certified with 11g

You may would like to review Steven's article on Dataguard with EBS

In 11g you have a new feature called Snapshot Standby.

What is there in 11g Datagaurd for EBS R12/11i?

Snapshot Standby:

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.



You can use the steps mentioned in this link(creating physical standby) to create physical standby. It basically a mechanism to protect your primary database in the case of disaster situation raising due to natural disaster or terrorist attack.

What you can get from snapshot standby for R12/11i?

1. Once you have physical standby created, you can convert to snapshot standby using

SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;

At this point standby will become physical standby. It will apply the archive logs. It will
give you the protection DR.

2) Once you have converted you physical standby of your EBS database to standby. you can do any update(its read writeable) and you back out the changes and convert back to physical standby. Oracle database works basically on Cost based optimization techniques. You query really gives faster output provided your database statistics are up to date. Gather statistics is basically a resource intensive task. Its needs to be done off peak hours. If your EBS 11i/R12 DB is critical Single Global Instance used by partners and employees around the globe, its likely that you will not get off-peak hours to do resource intensive tasks like gather statistics. Using this snapshot standby option what exactly you can do is you can gather statistics in snapshot database and you can export the stats and import it to your prod database.

2.a) create a stats table in apps schemaxx

exec dbms_stats.create_stat_table('SYS','dictstattab');

2.b) gather stats using FND_STATS

exec fnd_stats.gather_schema_statistics('APPLSYS');

2.c) Copy the stats to the stats table using export_schema_stats

exec dbms_stats.export_schema_stats('APPLSYS','applsysstattab',statown => 'SYSTEM');

2.d) Export the stats

exp buffer=10485760 file=prod_applsys_stats.dmp tables=applsysstattab
log=export.log

2.d) transfer the prod_applsys_stats.dmp file to prod server by ftp

3) convert back snapshot stanby to physical standby database

SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

4) Import the statistics to your prod database

imp userid=system/ file=prod_applsys_stats.dmp full=y ignore=y
log=import.log
5) move the statistics as prod statistics

exec
dbms_stats.import_schema_stats(ownname=>'SYSTEM',stattab=>'applsysstattab',statown=>'APPLSYS',no_invalidate=>DBMS_STATS.AUTO_INVALIDATE)
;


Happy Reading!! will see you all in next technical article.


References:
------------

http://blogs.oracle.com/stevenChan/2008/10/comparing_oracle_data_guard_vs_active_data_guard_f.html
http://blogs.oracle.com/stevenChan/2008/09/oracle_db_11gr1_11106_certified_with_ebs12.html
http://blogs.oracle.com/stevenChan/2008/03/oracle_database_11g_release_1.html
http://download.oracle.com/docs/cd/B28359_01/server.111/b28300/statistics.htm
Metalink note: 163208.1 - bde_last_analyzed.sql - Verifies CBO Statistics