Wednesday, November 17, 2010

Configure DDL synchronization/replication (Post 22 of series,DDL synchronization/replication with Tutorial)

The soul-bride is of no use at all, if she is evil and without virtue. She does not find peace in this world or the next; she burns in falsehood
and corruption. Coming and going are very difficult for that bride who is abandoned and forgotten by her Husband Lord.


Index of all the posts of Gurpartap Singh's Blog

Now before proceeding with more complex replication tutorials/examples lets install the DDL synchronization/replication first.

Now after sometime you would like to patch your application to add new features to it. One part of it would be to
upgrade the schema to next version i.e. change DDL. Don't worry goldengate is a golden product with lots of flexiblility and it will
happily replicate your DDL to target too. To replicate DDL's we need to configure DDL synchronization. It has few steps to it.
Lets configure DDL synchronization.

To do the following steps you should have user "goldengate" created in the database as we did in our previous posts.
From Goldengate installation directroy execute the following scripts as sysdba in source database as:


oracle : rac1.rac.meditate.com : @simar1 : /u01/app/oracle/product/goldengate
$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Nov 17 22:03:36 2010

Copyright (c) 1982, 2009, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

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:GOLDENGATE


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

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:GOLDENGATE

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 GOLDENGATE 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 GOLDENGATE

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/simar/simar1/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:GOLDENGATE
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 GOLDENGATE;

Grant succeeded.

SQL> @ddl_enable

Trigger altered.

SQL> @ddl_pin GOLDENGATE

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

oracle : rac1.rac.meditate.com : @simar1 : /u01/app/oracle/product/goldengate
$


Now we will enable additional suplemental logging for the tables to enable DDL replication for them.
Though we can enable additional supplemental logging on at column level also but here we will enable
at table level.

Use command:
DBLOGIN USERID meditate, PASSWORD meditate
ADD TRANDATA meditate.EMPLOYEE;
ADD TRANDATA meditate.RESULTS;
ADD TRANDATA meditate.LIFE;

Output is as:

GGSCI (rac1.rac.meditate.com) 6> DBLOGIN USERID meditate, PASSWORD meditate
Successfully logged into database.

GGSCI (rac1.rac.meditate.com) 7> ADD TRANDATA meditate.EMPLOYEE;
ERROR: No viable tables matched specification.

GGSCI (rac1.rac.meditate.com) 8> ADD TRANDATA meditate.RESULTS;
ERROR: No viable tables matched specification.

GGSCI (rac1.rac.meditate.com) 9> ADD TRANDATA meditate.LIFE;
ERROR: No viable tables matched specification.

GGSCI (rac1.rac.meditate.com) 10>


Output in logs is as:

2010-11-17 22:28:00 GGS INFO 399 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): ADD TRANDATA meditate.EMPLOYEE;.
2010-11-17 22:28:18 GGS INFO 399 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): ADD TRANDATA meditate.RESULTS;.
2010-11-17 22:28:32 GGS INFO 399 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): ADD TRANDATA meditate.LIFE;.



Now alter the parameter recyclebin from default on to off using following command and bounce the database as:
alter system set recyclebin=OFF scope=spfile;
srvtl stop database -d simar
srcvctl start database -d simar

Output is as:

SQL> alter system set recyclebin=OFF scope=spfile;

System altered.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

oracle : rac1.rac.meditate.com : @simar1 : /home/oracle
$


oracle : rac1.rac.meditate.com : @crs : /home/oracle
$ srvctl stop database -d simar


oracle : rac1.rac.meditate.com : @crs : /home/oracle
$ srvctl start database -d simar


oracle : rac1.rac.meditate.com : @crs : /home/oracle
$ crs_stat -t -v
Name Type R/RA F/FT Target State Host
----------------------------------------------------------------------
ora.DATA1.dg ora....up.type 0/5 0/ ONLINE ONLINE rac1
ora....ER.lsnr ora....er.type 0/5 0/ ONLINE ONLINE rac1
ora....N1.lsnr ora....er.type 0/5 0/0 ONLINE ONLINE rac2
ora.RECV1.dg ora....up.type 0/5 0/ ONLINE ONLINE rac1
ora.asm ora.asm.type 0/5 0/ ONLINE ONLINE rac1
ora.eons ora.eons.type 0/3 0/ ONLINE ONLINE rac1
ora.gsd ora.gsd.type 0/5 0/ ONLINE ONLINE rac1
ora....network ora....rk.type 0/5 0/ ONLINE ONLINE rac1
ora.ons ora.ons.type 0/3 0/ ONLINE ONLINE rac1
ora.prabgun.db ora....se.type 0/2 0/2 ONLINE OFFLINE
ora....sv3.svc ora....ce.type 0/0 0/0 ONLINE OFFLINE
ora....SM1.asm application 0/5 0/0 ONLINE ONLINE rac1
ora....C1.lsnr application 0/5 0/0 ONLINE ONLINE rac1
ora.rac1.gsd application 0/5 0/0 ONLINE ONLINE rac1
ora.rac1.ons application 0/3 0/0 ONLINE ONLINE rac1
ora.rac1.vip ora....t1.type 0/0 0/0 ONLINE ONLINE rac1
ora....SM2.asm application 0/5 0/0 ONLINE ONLINE rac2
ora....C2.lsnr application 0/5 0/0 ONLINE ONLINE rac2
ora.rac2.gsd application 0/5 0/0 ONLINE ONLINE rac2
ora.rac2.ons application 0/3 0/0 ONLINE ONLINE rac2
ora.rac2.vip ora....t1.type 0/0 0/0 ONLINE ONLINE rac2
ora....SM3.asm application 0/5 0/0 ONLINE ONLINE rac3
ora....C3.lsnr application 0/5 0/0 ONLINE ONLINE rac3
ora.rac3.gsd application 0/5 0/0 ONLINE ONLINE rac3
ora.rac3.ons application 0/3 0/0 ONLINE ONLINE rac3
ora.rac3.vip ora....t1.type 0/0 0/0 ONLINE ONLINE rac3
ora....ry.acfs ora....fs.type 0/5 0/ ONLINE ONLINE rac1
ora.scan1.vip ora....ip.type 0/0 0/0 ONLINE ONLINE rac2
ora.simar.db ora....se.type 0/2 0/1 ONLINE ONLINE rac1
ora....sv1.svc ora....ce.type 0/0 0/0 ONLINE ONLINE rac1

oracle : rac1.rac.meditate.com : @crs : /home/oracle
$


oracle : rac1.rac.meditate.com : @simar1 : /home/oracle
$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Nov 17 23:15:04 2010

Copyright (c) 1982, 2009, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> show parameter recycle

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
buffer_pool_recycle string
db_recycle_cache_size big integer 0
recyclebin string OFF
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

oracle : rac1.rac.meditate.com : @simar1 : /home/oracle
$


------------------------------------------------------------------------------------------------------------------

Now lets enable DDL replication by adding parameter "DDL INCLUDE MAPPED" in teh extract parameter file.

From documentation we have :

DDL
Valid for Extract and Replicat
Use the DDL parameter to enable DDL support and filter DDL operations. When used
without options, the DDL parameter causes all DDL operations to be propagated as follows:
? As an Extract parameter, it captures all supported DDL operations that are generated
on all supported database objects and sends them to the trail.
? As a Replicat parameter, it replicates all DDL operations from the GoldenGate trail
and applies them to the target. This is the same as the default behavior without this
parameter.
When used with options, DDL acts as a filtering agent to include or exclude DDL operations
based on:
? scope
? object type
? operation type
? object name
? strings in the DDL command syntax or comments, or both
SPTHREAD | NOSPTHREAD Valid for Extract and Replicat. Creates a separate database
connection thread for stored procedures. The default is
NOSPTHREAD.
TDSPACKETSIZE Valid for Replicat. Sets the TDS packet size for replication to a
Sybase target. Must be set to a multiple of 512. Valid values are
512 (the default) through 8192.
TRUSTEDCONNECTION Valid for Extract and Replicat for SQL Server. Causes
GoldenGate to connect by using trusted connection = yes. Contact
GoldenGate Technical Support before using this option.
XMLBUFSIZE Valid for Extract for Oracle. Sets the size of the memory buffer
that stores XML data that was extracted from the sys.xmltype
attribute of a SDO_GEORASTER object type. The default is 1048576
bytes (1MB). If the data exceeds the default buffer size, Extract
will abend. If this occurs, increase the buffer size and start
Extract again. The valid range of values is 1024 to 10485760 bytes.


Only one DDL parameter can be used in a parameter file, but you can combine multiple
inclusion and exclusion options to filter the DDL to the required level.
? When combined, multiple option specifications are linked logically as AND statements.
? All criteria specified with multiple options must be satisfied for a DDL statement to be
replicated.
? When using complex DDL filtering criteria, it is recommended that you test your
configuration in a test environment before using it in production.
WARNING Do not include any GoldenGate-installed DDL objects in a DDL parameter, in
a TABLE parameter, or in a MAP parameter, nor in a TABLEEXCLUDE or
MAPEXCLUDE parameter. Make certain that wildcard specifications in those
parameters do not include GoldenGate-installed DDL objects. These objects
must not be part of the GoldenGate configuration, but the Extract process
must be aware of operations on them, and that is why you must not explicitly
exclude them from the configuration with an EXCLUDE, TABLEEXCLUDE, or
MAPEXCLUDE parameter statement.
Do not use DDL for an Extract data pump or for a VAM-sort Extract. These process types do
not permit mapping or conversion of DDL and will propogate DDL records automatically
in PASSTHRU mode (see page 250). DDL that is performed on a source table of a certain name
(for example ALTER TABLE TableA...) will be applied by Replicat with the same table name (ALTER
TABLE TableA). It cannot be mapped as ALTER TABLE TableB.
For detailed information about how to use GoldenGate DDL support, see the GoldenGate
for Windows and UNIX Administrator Guide.


==========================================================================================================


GGSCI (rac1.rac.meditate.com) 11> edit params etruth

EXTRACT etruth
USERID goldengate@simarsv1, PASSWORD AACAAAAAAAAAAAKAPATACEHBIGQGCFZCCDIGAEMCQFFBZHVC, ENCRYPTKEY default
TRANLOGOPTIONS ASMUSER sys@RAC1ASM, ASMPASSWORD AACAAAAAAAAAAAJASAOBIHRGNFGAYCKBHJPAGIXFGGBJUAXI, ENCRYPTKEY default
EXTTRAIL /u01/app/oracle/product/goldengate/dirdat/tr
DDL INCLUDE MAPPED
TABLE meditate.results;


Since we have bounced the database without stopping the extracts etruth and ecreator will go to status ABENDED. So, we need to restart these processes as:

GGSCI (rac1.rac.meditate.com) 12> start extract etruth


GGSCI (rac1.rac.meditate.com) 12> start extract ecreator

I am not showing logout put here as by now we have done this many times earlier.

Now lets test it as:

Before DDL change talbe in source and target is as:

SQL> desc results
Name Null? Type
----------------------------------------- -------- ----------------------------
DEED_ID NUMBER
DEED VARCHAR2(60)
DEED_RESULT VARCHAR2(100)

SQL>

and after DDL change the table in source is as:


SQL> alter table results add test_col VARCHAR2(30);

Table altered.

SQL> desc results
Name Null? Type
----------------------------------------- -------- ----------------------------
DEED_ID NUMBER
DEED VARCHAR2(60)
DEED_RESULT VARCHAR2(100)
TEST_COL VARCHAR2(30)

SQL>



and target is also as:

SQL> desc results
Name Null? Type
----------------------------- -------- --------------------
DEED_ID NUMBER
DEED VARCHAR2(60)
DEED_RESULT VARCHAR2(100)
TEST_COL VARCHAR2(30)

SQL>


In an instant, one is born, and in an instant, one dies. In an instant one comes, and in an instant one goes. One who recognizes the Shabad merges into it, and is not afflicted by death.

No comments:

Post a Comment