oracle-databaseoracle12cdataguard

Can I use Data Guard to sync only data (not DB structure) between a primary and a staging schema

I have releases that require up to 4 hours of downtime of an application server to prevent users from adding any new data in an Oracle 12c database while various scripts including changes to table structure are being peformed.

Could data guard be used in a scenario like this :

  1. Replicate the current production schema in a staging area
  2. The the users continue their work in the production environment
  3. Run various scripts in the staging DB, including changes to the table structure
  4. At some point, use data guard to "push" all data changed in the production environment to the staging environment
  5. At this point, the staging environment becomes the production environment

This is only to be used for scheduled releases

Solution

  • You may be able to use a Data Guard Logical Standby for this.

    BE WARNED

    A logical Data Guard standby setup is far more complex than a typical physical standby, especially when talking about the type of changes you want to make and keep separate between the primary and the standby. Because of this, I would, at the very least, get very comfortable with administering a physical standby before jumping into administering a logical standby.

    1) Create a Data Guard physical standby.

    2) Convert the physical standby to a logical standby:

    --STANDBY 
alter database recover managed standby database cancel;

--PRIMARY
execute dbms_logstdby.build;

--STANDBY
alter database recover to logical standby keep identity;
alter database open;
alter database start logical standby apply immediate;

    3) Skip just DDL on an entire schema:

    --STANDBY
EXECUTE DBMS_LOGSTDBY.SKIP(STMT => 'SCHEMA DDL', -
     schema_name => 'HR', -
     table_name => '%', -
     proc_name => null);

    You can also skip DDL or DML for specific objects as well, but I would assume that in your case, the schema level would be more appropriate. DBMS_LOGSTDBY is very robust, which you can read about from the docs.

    4) When you need to push changes to the standby only without affecting the primary, you can disable the guard mode for that session:

    --STANDBY
alter session disable guard;
--Make your changes
alter session enable guard;

    5) When you are ready to go live, perform a Data Guard switchover, which you can read about from the docs. This doc is specific to using the Data Guard broker, which is highly recommended when administering a Data Guard environment.