This example add the DDL replication (trigger-based) to the bi-directional replication between Oracle DBs.
Setup DDL Replication
SQL> connect sys/oracle@ogg11r2 as sysdba Connected. SQL> grant execute on utl_file to ogguser; Grant succeeded. SQL> @marker_setup.sql Marker setup script You will be prompted for the name of a schema for the Oracle GoldenGate database objects. NOTE: The schema must be created prior to running this script. NOTE: Stop all DDL replication before starting this installation. Enter Oracle GoldenGate schema name:ogguser Marker setup table script complete, running verification script... Please enter the name of a schema for the GoldenGate database objects: Setting schema name to OGGUSER MARKER TABLE ------------------------------- OK MARKER SEQUENCE ------------------------------- OK Script complete. SQL> @ddl_setup Oracle GoldenGate DDL Replication setup script Verifying that current user has privileges to install DDL Replication... You will be prompted for the name of a schema for the Oracle 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 Oracle GoldenGate schema name:ogguser Working, please wait ... Spooling to file ddl_setup_spool.txt Checking for sessions that are holding locks on Oracle Golden Gate metadata tables ... Check complete. Using OGGUSER as a Oracle GoldenGate schema name. Working, please wait ... DDL replication setup script complete, running verification script... Please enter the name of a schema for the GoldenGate database objects: Setting schema name to OGGUSER CLEAR_TRACE STATUS: Line/pos Error ---------------------------------------- ----------------------------------------------------------------- No errors No errors CREATE_TRACE STATUS: Line/pos Error ---------------------------------------- ----------------------------------------------------------------- No errors No errors TRACE_PUT_LINE STATUS: Line/pos Error ---------------------------------------- ----------------------------------------------------------------- No errors No errors INITIAL_SETUP STATUS: Line/pos Error ---------------------------------------- ----------------------------------------------------------------- No errors No errors DDLVERSIONSPECIFIC PACKAGE STATUS: Line/pos Error ---------------------------------------- ----------------------------------------------------------------- No errors No errors DDLREPLICATION PACKAGE STATUS: Line/pos Error ---------------------------------------- ----------------------------------------------------------------- No errors No errors DDLREPLICATION PACKAGE BODY STATUS: Line/pos Error ---------------------------------------- ----------------------------------------------------------------- No errors No errors DDL HISTORY TABLE ----------------------------------- OK DDL HISTORY TABLE(1) ----------------------------------- OK DDL DUMP TABLES ----------------------------------- OK DDL DUMP COLUMNS ----------------------------------- OK DDL DUMP LOG GROUPS ----------------------------------- OK DDL DUMP PARTITIONS ----------------------------------- OK DDL DUMP PRIMARY KEYS ----------------------------------- OK DDL SEQUENCE ----------------------------------- OK GGS_TEMP_COLS ----------------------------------- OK GGS_TEMP_UK ----------------------------------- OK DDL TRIGGER CODE STATUS: Line/pos Error ---------------------------------------- ----------------------------------------------------------------- No errors No errors DDL TRIGGER INSTALL STATUS ----------------------------------- OK DDL TRIGGER RUNNING STATUS ------------------------------------------------------------------------------------------------------------------------ ENABLED STAYMETADATA IN TRIGGER ------------------------------------------------------------------------------------------------------------------------ OFF DDL TRIGGER SQL TRACING ------------------------------------------------------------------------------------------------------------------------ 0 DDL TRIGGER TRACE LEVEL ------------------------------------------------------------------------------------------------------------------------ 0 LOCATION OF DDL TRACE FILE ------------------------------------------------------------------------------------------------------------------------ /u01/app/oracle/diag/rdbms/ogg11r2/ogg11r2/trace/ggs_ddl_trace.log Analyzing installation status... STATUS OF DDL REPLICATION ------------------------------------------------------------------------------------------------------------------------ SUCCESSFUL installation of DDL Replication software components Script complete. SQL> @role_setup 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:ogguser 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> @ddl_enable Trigger altered. SQL> grant GGS_GGSUSER_ROLE to ogguser; Grant succeeded.
Update the GGGSCHEMA in GLOBAL
checkpointtable ggs_chkpt GGSCHEMA ogguser
Repeat the same setup on the target.
Extract exwest SETENV(ORACLE_HOME="/u01/app/oracle/product/11.2.0/dbhome_1") SETENV(ORACLE_SID = "ogg11r2") ExtTrail ./dirdat/ew UserID ogguser@ogg11r2, Password Welcome1 DDL INCLUDE MAPPED OBJNAME west.* DDLOPTIONS ADDTRANDATA StatOptions ResetReportStats ReportCount Every 10 Records TranLogOptions ExcludeUser ogguser Table WEST.*;
Replicat rewest SETENV(ORACLE_HOME="/u01/app/oracle/product/11.2.0/dbhome_1") SETENV(ORACLE_SID = "ogg11r2") AssumeTargetDefs DiscardFile ./dirrpt/rewest.dsc, Purge UserID ogguser@ogg11r2, Password Welcome1 DDLERROR DEFAULT IGNORE Map EAST.*, Target WEST.*;
Perform the same setup on the target.
Next, perform the DDL replication.
Next, perform the DDL replication.
SQL> connect west/Welcome1@ogg11r2 Connected. SQL> create table try_ddl (field_1 varchar2(30) not null primary key,field_2 number); Table created. SQL> insert into try_ddl values ('GoldenGate',1); 1 row created. SQL> commit; Commit complete. SQL> connect east/Welcome1@dwnstr Connected. SQL> select table_name from user_tables; TABLE_NAME ------------------------------ TRY_DDL CUSTOMER BRANCH ACCOUNT SQL> select * from try_ddl; FIELD_1 FIELD_2 ------------------------------ ---------- GoldenGate 1
GGSCI (EDLVC3AR5P5) 1> dblogin userid ogguser@ogg11r2, password Welcome1 Successfully logged into database. GGSCI (EDLVC3AR5P5) 2> info trandata ERROR: Missing/invalid argument. GGSCI (EDLVC3AR5P5) 3> info trandata west.* Logging of supplemental redo log data is enabled for table WEST.ACCOUNT. Columns supplementally logged for table WEST.ACCOUNT: ACCT_ID, CUST_ID. Logging of supplemental redo log data is enabled for table WEST.BRANCH. Columns supplementally logged for table WEST.BRANCH: BRANCH_ID. Logging of supplemental redo log data is enabled for table WEST.CUSTOMER. Columns supplementally logged for table WEST.CUSTOMER: CUST_ID. Logging of supplemental redo log data is enabled for table WEST.TRY_DDL. Columns supplementally logged for table WEST.TRY_DDL: FIELD_1. GGSCI (EDLVC3AR5P5) 4> info trandata west.account; ERROR: No viable tables matched specification. GGSCI (EDLVC3AR5P5) 5> info trandata west.account Logging of supplemental redo log data is enabled for table WEST.ACCOUNT. Columns supplementally logged for table WEST.ACCOUNT: ACCT_ID, CUST_ID. GGSCI (EDLVC3AR5P5) 6> delete trandata west.account Logging of supplemental redo log data disabled for table WEST.ACCOUNT. add trandata west.account, cols (ACCOUNT_TYPE,ACCOUNT_NUMBER,ACCOUNT_BALANCE,ACCOUNT_OPENED,ACCO ERROR: Invalid ADD TRANDATA specification. GGSCI (EDLVC3AR5P5) 8> add trandata west.account, cols (ACCOUNT_TYPE,ACCOUNT_NUMBER,ACCOUNT_BALANCE,ACCOUNT_OPENED,ACCOUNT_BRANCH_ID,UPDATE_TS) Logging of supplemental redo data enabled for table WEST.ACCOUNT. GGSCI (EDLVC3AR5P5) 10> delete trandata west.branch Logging of supplemental redo log data disabled for table WEST.BRANCH. GGSCI (EDLVC3AR5P5) 11> add trandata east.branch, cols(BRANCH_STREET_TYPE,BRANCH_STREET_NAME,BRANCH_STREET_NUMBER, BRANCH_LEVEL,BRANCH_CITY,BRANCH_STATE,BRANCH_COUNTRY,BRANCH_SIZE,BRANCH_OPENED,ZIP_CODE,BRANCH_NAME,UPDATE_TS) Logging of supplemental redo data enabled for table WEST.BRANCH. GGSCI (EDLVC3AR5P5) 12> add trandata west.customer, cols(NAME,MIDDLE_NAME,SURNAME,FULL_NAME,DOB,NATIONALITY,EMAIL,UPDATE_TS) Logging of supplemental redo log data is already enabled for table WEST.CUSTOMER. GGSCI (EDLVC3AR5P5) 14> info trandata west.* Logging of supplemental redo log data is enabled for table WEST.ACCOUNT. Columns supplementally logged for table WEST.ACCOUNT: ACCT_ID, CUST_ID, ACCOUNT_TYPE, ACCOUNT_NUMBER, ACCOUNT_BALANCE, ACCOUNT_OPENED, ACCOUNT_BRANCH_ID, UPDATE_TS. Logging of supplemental redo log data is enabled for table WEST.BRANCH. Columns supplementally logged for table WEST.BRANCH: BRANCH_ID, BRANCH_STREET_TYPE, BRANCH_STREET_NAME, BRANCH_STREET_NUMBER, BRANCH_LEVEL, BRANCH_CITY, BRANCH_STATE, BRANCH_COUNTRY, BRANCH_SIZE, BRANCH_OPENED, ZIP_CODE, BRANCH_NAME, UPDATE_TS. Logging of supplemental redo log data is enabled for table WEST.CUSTOMER. Columns supplementally logged for table WEST.CUSTOMER: CUST_ID. Logging of supplemental redo log data is enabled for table WEST.TRY_DDL. Columns supplementally logged for table WEST.TRY_DDL: FIELD_1.
Extract exwest SETENV(ORACLE_HOME="/u01/app/oracle/product/11.2.0/dbhome_1") SETENV(ORACLE_SID = "ogg11r2") ExtTrail ./dirdat/ew UserID ogguser@ogg11r2, Password Welcome1 DDL INCLUDE MAPPED OBJNAME west.* DDLOPTIONS ADDTRANDATA StatOptions ResetReportStats ReportCount Every 10 Records GetUpdateBefores TranLogOptions ExcludeUser ogguser Table west.account GetBeforeCols(on update all); Table west.branch GetBeforeCols(on update all); Table west.customer GetBeforeCols(on update all);
Replicat rewest SETENV(ORACLE_HOME="/u01/app/oracle/product/11.2.0/dbhome_1") SETENV(ORACLE_SID = "ogg11r2") AssumeTargetDefs DiscardFile ./dirrpt/rewest.dsc, Purge UserID ogguser@ogg11r2, Password Welcome1 DDLERROR DEFAULT IGNORE getupdates getinserts getdeletes Map east.account,Target west.account, comparecols(on update all) resolveconflict(updaterowexists, (delta_resolution_method, usedelta, cols(account_balance)), (max_resolution_method, usemax(update_ts), cols(account_type,account_number,account_opened, account_branch_id,update_ts)), (default overwrite)); Map east.branch,Target west.branch, comparecols(on update all) resolveconflict(updaterowexists, (max_resolution_method, usemax(update_ts), cols(branch_street_type,branch_street_name, branch_street_number,branch_level,branch_city,branch_state, branch_country,branch_size,branch_opened,zip_code, branch_name,update_ts)), (default overwrite)); Map east.customer,Target west.customer, comparecols(on update all) resolveconflict(updaterowexists, (max_resolution_method, usemax(update_ts), cols(name,middle_name,surname,full_name,dob,nationality, email,update_ts)), (default overwrite));
Extract exeast SETENV(ORACLE_HOME="/u01/app/oracle/product/11.2.0/dbhome_1") SETENV(ORACLE_SID = "dwnstr") ExtTrail ./dirdat/ee UserID ogguser@dwnstr, Password Welcome1 DDL INCLUDE MAPPED OBJNAME east.* DDLOPTIONS ADDTRANDATA StatOptions ResetReportStats ReportCount Every 10 Records GetUpdateBefores TranLogOptions ExcludeUser ogguser Table east.account GetBeforeCols(on update all); Table east.branch GetBeforeCols(on update all); Table east.customer GetBeforeCols(on update all);
GetUpdates GetInserts GetDeletes Map west.account, Target east.account, comparecols(on update all) resolveconflict(updaterowexists, (delta_resolution_method, usedelta, cols(account_balance)), (max_resolution_method, usemax(update_ts), cols(account_type,account_number,account_opened, account_branch_id,update_ts)), (default overwrite)); Map west.branch, Target east.branch, comparecols(on update all) resolveconflict(updaterowexists, (max_resolution_method, usemax(update_ts), cols(branch_street_type,branch_street_name, branch_street_number,branch_level,branch_city,branch_state, branch_country,branch_size,branch_opened,zip_code, branch_name,update_ts)), (default overwrite)); Map west.customer, Target east.customer, comparecols(on update all) resolveconflict(updaterowexists, (max_resolution_method, usemax(update_ts), cols(name,middle_name,surname,full_name,dob,nationality, email,update_ts)), (default overwrite));
SQL> select acct_id, cust_id, account_balance from account where acct_id=299; ACCT_ID CUST_ID ACCOUNT_BALANCE ---------- ---------- --------------- 299 99 7192 SQL> SQL> SQL> UPDATE account set account_balance = account_balance - 30 where acct_id = 299 and account_balance = 7192; 1 row updated. SQL> commit; Commit complete. SQL> select acct_id, cust_id, account_balance from account where acct_id=299; ACCT_ID CUST_ID ACCOUNT_BALANCE ---------- ---------- --------------- 299 99 7112
SQL> update account set account_balance = account_balance - 50 where acct_id=299 and account_balance = 7192; 1 row updated. SQL> commit; Commit complete. SQL> update account set account_balance = account_balance - 50 where acct_id=299 and account_balance = 7192; 0 rows updated. SQL> select acct_id, cust_id, account_balance from account where acct_id=299; ACCT_ID CUST_ID ACCOUNT_BALANCE ---------- ---------- --------------- 299 99 7112
GGSCI ([Host]) 14> stats rewest reportcdr Sending STATS request to REPLICAT REWEST ... Start of Statistics at 2012-07-14 21:58:15. Replicating from EAST.ACCOUNT to WEST.ACCOUNT: *** Total statistics since 2012-07-14 20:31:50 *** Total inserts 2.00 Total updates 1.00 Total deletes 0.00 Total discards 0.00 Total operations 3.00 Total CDR conflicts 1.00 CDR resolutions succeeded 1.00 CDR UPDATEROWEXISTS conflicts 1.00
GGSCI (EDLVC3AR5P5) 55> stats reeast reportcdr Sending STATS request to REPLICAT REEAST ... Start of Statistics at 2015-07-17 10:08:22. Replicating from WEST.ACCOUNT to EAST.ACCOUNT: *** Total statistics since 2015-07-17 10:06:13 *** Total inserts 0.00 Total updates 1.00 Total deletes 0.00 Total discards 0.00 Total operations 1.00 Total CDR conflicts 1.00 CDR resolutions succeeded 1.00 CDR UPDATEROWEXISTS conflicts 1.00 *** Daily statistics since 2015-07-17 10:06:13 *** Total inserts 0.00 Total updates 1.00 Total deletes 0.00 Total discards 0.00 Total operations 1.00 Total CDR conflicts 1.00 CDR resolutions succeeded 1.00 CDR UPDATEROWEXISTS conflicts 1.00 *** Hourly statistics since 2015-07-17 10:06:13 *** Total inserts 0.00 Total updates 1.00 Total deletes 0.00 Total discards 0.00 Total operations 1.00 Total CDR conflicts 1.00 CDR resolutions succeeded 1.00 CDR UPDATEROWEXISTS conflicts 1.00 *** Latest statistics since 2015-07-17 10:06:13 *** Total inserts 0.00 Total updates 1.00 Total deletes 0.00 Total discards 0.00 Total operations 1.00 Total CDR conflicts 1.00 CDR resolutions succeeded 1.00 CDR UPDATEROWEXISTS conflicts 1.00 End of Statistics.