Home » Server Options » Replication » Cannot create 2 different users on database to handle extract and replicat operations. (Oracle : Golden GAte:
Cannot create 2 different users on database to handle extract and replicat operations. [message #648670] Mon, 29 February 2016 04:19 Go to next message
Messages: 1
Registered: February 2016
Junior Member

I want to create 2 different golden gate schemas on my oracle database for a bi-directional replication rather than just creating a single ggadmin user for taking care of both the operations.

At the start I created a single schema named ggadmin and have executed the following scripts:
a. @marker_setup.sql (when prompted enter "ggadmin")
b. @ddl_setup.sql (when prompted enter "ggadmin" and "INITIALSETUP")
c. @role_setup.sql (when prompted enter "ggadmin")
d. grant GGS_GGSUSER_ROLE to ggadmin;
e. @ddl_enable.sql

But now, I dnt want to make things complex and my seniors have advised me to create ggextract and ggreplicat users on both the databases. SO I created new users and different tablespaces for this users, ran the above scripts but I got the following error when I executed the second sql .... @ddl_setup.sql

SQL> @ddl_setup.sql

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

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.

ERROR at line 1:
Oracle GoldenGate DDL Replication setup:
*** INITIALSETUP used, but DDLREPLICATION package exists under different schema (GGADMIN). Please use ddl_remove.sql to
remove DDL installation from that schema first
ORA-06512: at line 26

Can anyone please suggest whether or not we can create 2 different users ggexgtract and ggreplicat instead of a single golden gate user. And if yes then what I am doing wrong or missing over here because I am setting GG for first time and I dont know will executing ddl_remove.sql will help, because if ggextract is created the same error then would prompt when I run ddl_setup.sql through ggreplicat user.

Re: Cannot create 2 different users on database to handle extract and replicat operations. [message #652070 is a reply to message #648670] Tue, 31 May 2016 19:36 Go to previous message
Messages: 2
Registered: May 2016
Location: Australia
Junior Member
Hi Sagar,
There are several gotchas in what you are trying to do.
You can do DDL bi-directional but only in a restricted way. You need to nominate one node as the DDL modifier. Imagine doing alter table on both sides at the same time. This will fail as you cannot resolve conflict for DDL.
As you are running 10g you are stuck with OGG DDL trigger tables ( database can handle OGG DDL within the database). As you noticed from your errors you can only have one set of DDL trigger tables within one database, so having different users will not work.
I suppose I need to ask why 2 users, why not one OGG user? The application schema is irrelevant in configuring OGG, you can login as ggadmin and then capture from schemaA, schemaB , anything and you can replicate from schemaA to schemaB.
Treat OGG as part of the database, IE one central control. In the same way you can have 100 applications running on a single database you have a SYS to handle all of them because the database is a single central uility.
Kee Gan
Previous Topic: Rowid ORACLE Golden Gate
Next Topic: golden gate replication to partitioned table
Goto Forum:

Current Time: Tue Jun 28 00:51:41 CDT 2022