Golden Gate DDL senkorinasyonu (active-standby)

Bu Makalemizde, iki veritabani arasinda, Kaynak veritabanindaki bir DDL isleminin Hedef veritabanina nasil aktarilacagini gorecegiz.

########################## Kaynak Makinede ###########################
    [oracle@db1 gg]$ sqlplus / as sysdba;
    
    SQL> @marker_setup
      Marker setup 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:GGS_ADMIN

      Marker setup table script complete, running verification script...
      Please enter the name of a schema for the GoldenGate database objects:
      Setting schema name to GGS_ADMIN

      MARKER TABLE
      -------------------------------
      OK

      MARKER SEQUENCE
      -------------------------------
      OK
      Script complete.

    SQL> alter session set recyclebin=OFF;
      Session altered.

    SQL> @ddl_setup

      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 GoldenGate database objects.
      NOTE: The schema must be created prior to running this script.
      NOTE: On Oracle 10g and up, system recycle bin must be disabled.
      NOTE: Stop all DDL replication before starting this installation.

      Enter GoldenGate schema name:GGS_ADMIN

      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

      Working, please wait ...
      Spooling to file ddl_setup_spool.txt


      Using GGS_ADMIN as a GoldenGate schema name, INITIALSETUP as a mode of installation.

      Working, please wait ...

      RECYCLEBIN must be empty.
      This installation will purge RECYCLEBIN for all users.
      To proceed, enter yes. To stop installation, enter no.

      Enter yes or no:yes

      DDL replication setup script complete, running verification script...
      Please enter the name of a schema for the GoldenGate database objects:
      Setting schema name to GGS_ADMIN

      DDLORA_GETTABLESPACESIZE STATUS:

      Line/pos   Error
      ---------- -----------------------------------------------------------------
      No errors  No errors

      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/ORCL/orcl/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:GGS_ADMIN
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 GGS_ADMIN;
 Grant succeeded.

      SQL> @ddl_enable
 Trigger altered.

      SQL> @ddl_pin GGS_ADMIN
 PL/SQL procedure successfully completed.
 PL/SQL procedure successfully completed.
 PL/SQL procedure successfully completed.

Turn Recyclebin OFF

    [oracle@db1 gg]$ ggsci

      GGSCI (db1) 5> DBLOGIN USERID GGS_ADMIN, PASSWORD GGS_ADMIN
      Successfully logged into database.

      GGSCI (db1) 6> ADD TRANDATA scott.emp

      Logging of supplemental redo data enabled for table SCOTT.EMP.

      Edit the parameter file for the Extract process to enable DDL synchronization

      We had earlier created a parameter file for an Extract process ext1. We now edit that parameter file and add the entry
      DDL INCLUDE MAPPED

      This means that DDL support is now enabled for all tables which have been mapped and in this case it will only apply to the SCOTT.EMP
      table as that is the only table which is being processed here. We can also use the INCLUDE ALL or EXCLUDE ALL or wildcard characters t
      o specify which tables to enable the DDL support for.

      GGSCI (db1) 1> EDIT PARAM EXT1

EXTRACT ext1
USERID  GGS_ADMIN, PASSWORD GGS_ADMIN
RMTHOST db2, MGRPORT 7809
RMTTRAIL /u01/oracle/gg/dirdat/rt
DDL INCLUDE MAPPED
TABLE scott.emp;

########### Test ##########################################

############# Kaynak Makinede ############## 
    
    [oracle@db1 gg]$ sqlplus scott/tiger;
    
    SQL> ALTER TABLE EMP ADD MYCOL VARCHAR2(10);
         Table altered.

###### Hedef Makinede ##############3
  
    [oracle@db2 gg]$ sqlplus scott/tiger;
    
    SQL> desc emp
Name Null? Type
—————————————– ——– —————————-
SQL> desc emp
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
EMPNO                                     NOT NULL NUMBER(4)
ENAME                                              VARCHAR2(10)
JOB                                                VARCHAR2(9)
MGR                                                NUMBER(4)
HIREDATE                                           DATE
SAL                                                NUMBER(7,2)
COMM                                               NUMBER(7,2)
DEPTNO                                             NUMBER(2)
NEW_COL2                                           VARCHAR2(10)
MYCOL                                              VARCHAR2(10)