In addition to
providing replication support for all DML statements, we can also configure the
GoldenGate environment to provide DDL support as well.
A number of
prerequisite setup tasks need to be performed which we willl highlight here.
Run the following
scripts from the directory where the GoldenGate software was installed.
The assumption here
is that the database user GGS_ADMIN has already been created and granted the
required roles and privileges as discussed in our earlier tutorial.
Note - run the scripts 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/gavin/gavin/trace/ggs_ddl_trace.log
Analyzing installation status...
STATUS OF DDL REPLICATION
--------------------------------------------------------------------------------
SUCCESSFUL installation of DDL Replication software components
Script complete.
SQL>
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
We need to set the
parameter recyclebin to OFF via the ALTER SYSTEM SET RECYCLEBIN=OFF command in
order to prevent this error which we will see if we try and configure DDL
support and then start the Extract process.
2010-02-19 11:13:30
GGS ERROR 2003 RECYCLEBIN must be turned off. For 10gr2 and up, set RECYCLEBIN
in parameter file to OFF. For 10gr1, set _RECYCLEBI
N in parameter file to FALSE. Then restart database and extract.
2010-02-19 11:13:30 GGS ERROR 190 PROCESS ABENDING.
N in parameter file to FALSE. Then restart database and extract.
2010-02-19 11:13:30 GGS ERROR 190 PROCESS ABENDING.
Enable additional
logging at the table level
Note- We had earlier
enabled additional supplemental logging at the database level. Using the ADD
TRANDATA command we now enable it at even the table level as this is required
by GoldenGate for DDL support.
GGSCI
(redhat346.localdomain) 5> DBLOGIN USERID ggs_admin, PASSWORD ggs_admin
Successfully logged into database.
Successfully logged into database.
GGSCI
(redhat346.localdomain) 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
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 to specify which tables to enable the DDL support for.
GGSCI
(redhat346.localdomain) 1> EDIT PARAM EXT1
EXTRACT ext1
USERID ggs_admin, PASSWORD ggs_admin
RMTHOST 10.53.100.100, MGRPORT 7809
RMTTRAIL /u01/app/gg/dirdat/rt
DDL INCLUDE MAPPED
TABLE scott.emp;
USERID ggs_admin, PASSWORD ggs_admin
RMTHOST 10.53.100.100, MGRPORT 7809
RMTTRAIL /u01/app/gg/dirdat/rt
DDL INCLUDE MAPPED
TABLE scott.emp;
Test the same
We will now alter the
structure of the EMP table by adding a column and we can see that this new
table structure is also reflected on the target system.
On Source
SQL> ALTER
TABLE EMP ADD NEW_COL VARCHAR2(10);
Table altered.
Table altered.
On Target
SQL> desc emp
Name Null? Type
—————————————– ——– —————————-
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(20)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
MYCOL VARCHAR2(10)
NEW_COL VARCHAR2(10)
Name Null? Type
—————————————– ——– —————————-
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(20)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
MYCOL VARCHAR2(10)
NEW_COL VARCHAR2(10)
No comments:
Post a Comment