Golden Gate Oracle DDL Replication
DDL Replication means that any structure change in the primary datatabase is replicated to the secondary database. Support if new column in added in a table with alter command that is also replicated to secondary database with golden gate replication.
Following restrictions with Golden Gate for setup the DDL replication:
1. Must turn off the Oracle recycle bin in Oracle 10g and later prior to Oracle 11gR1.
2. Golden Gate can only automatically replicate DDL statements that are less than 2MB in length. Any DDL longer than that must be processed manually using a Golden Gate-provided script.
3. DDL replication is supported for replication between only two systems. Bidirectional DDL replication is also supported
4. source and target schemas must be identical. This means you must be using the ASSUMETARGETDEFS parameter on your Replicat
5. You must use passthru mode on your data-pump Extracts for tables that require DDL replication.
6.You should keep the DML and DDL replication for a single table and for related groups of tables together in the same Extract and Replicat groups.
Steps for implement the Golden Gate replication with DDL Statements:
This example uses the gger user schema to store the DDL tables:
1. Grant execute permission on UTL_FILE to the gger user:
SQL> grant execute on utl_file to gger;
2. Add the following line to your GLOBALS file. Remember, this file is in your GoldenGate software installation directory. If it doesn’t exist, you need to create it the first time:
GGSCHEMA GGER
3. Run the marker_setup.sql script from the GoldenGate software installation directory. This script is included as part of the GoldenGate software installation. It prompts for the schema name, which in this case is gger:
SQL> connect / as sysdba
Connected.
SQL> @marker_setup.sql
Marker setup script:
------------------------------------------------------------
You will be prompted for the name of a schema for the Golden Gate database objects.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.
Enter Golden Gate schema name: gger
Marker setup table script complete, running verification script...
Please enter the name of a schema for the Golden Gate database objects:
Setting schema name to GGER
MARKER TABLE
-------------------------------
OK
MARKER SEQUENCE
-------------------------------
OK
Script complete.
4. Run the ddl_setup.sql from the GoldenGate software installation directory. This script is included as part of the GoldenGate software installation:
SQL> @ddl_setup.sql
GoldenGate DDL Replication setup script
Verifying that current user has privileges to install DDL Replication...
Checking user sessions...
Check complete.
You will be prompted for the name of a schema for the GoldenGate database objects.
NOTE: For an Oracle 10g source, the system recycle bin must be disabled.
For Oracle 11g and later, it can be enabled.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.
Enter GoldenGate schema name:gger
You will be prompted for the mode of installation.
To install or reinstall DDL replication, enter INITIALSETUP
To upgrade DDL replication, enter NORMAL
Enter mode of installation:INITIALSETUP
105
Working, please wait ...
Spooling to file ddl_setup_spool.txt
Using GGER as a GoldenGate schema name, INITIALSETUP as a mode of installation.
Working, please wait ...
...
STATUS OF DDL REPLICATION
--------------------------------------------------------------------------------
SUCCESSFUL installation of DDL Replication software components
Script complete.
5. Run the role-setup script to set up the roles needed for DDL synchronization and grant the role to the gger database user:
SQL> @role_setup.sql
GGS Role setup script
This script will drop and recreate the role GGS_GGSUSER_ROLE
To use a different role name, quit this script and then edit the params.sql script
to change the gg_role parameter to the preferred name. (Do not run the script.)
You will be prompted for the name of a schema for the GoldenGate database objects.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.
Enter GoldenGate schema name:gger
Wrote file role_setup_set.txt
PL/SQL procedure successfully completed.
Role setup script complete
Grant this role to each user assigned to the Extract, GGSCI, and Manager processes,
by using the following SQL command:
GRANT GGS_GGSUSER_ROLE TO
where is the user assigned to the GoldenGate processes.
SQL> GRANT GGS_GGSUSER_ROLE TO gger;
Grant succeeded.
6. Run the ddl_enable.sql script to enable the DDL trigger:
SQL> @ddl_enable.sql
Trigger altered.
7. Update the local Extract parameter file to include the DDL parameter, to tell GoldenGate you wish to replicate DDL.
In this example you’re only replicating DDL statements that are mapped in your replication scenario. You can choose to INCLUDE other tables or EXCLUDE them from DDL replication using different parameter options as well:
DDL Include Mapped
Smart way of Technology 