Golden Gate Schema Setup Part 1

The idea behind this post is not simply write one more post about GG Schema setup. There are tons of information about them from the official oracle documentation which is very good and some excellent blogs on this especially the Gavinsoorma Blogs, from which I learnt myself.

This posting is just to fill the gaps in-between and and I still suggest that you go to the other resources if you are a newbie.

To be honest, I like to keep things simple and prefer to run a standard script at both the source and destination eventhough a few privs are more specific to source and some to destination.

There are a few things which i do differently. One of them is creating a checkpoint table (obviously this is at the destination) for each REPLICAT process instead of a common checkpoint table per destination. This is just to ensure that one DBA does not mess-up with the checkpoint information of an other DBA in environments where there is a common reporting database and multiple people work on setting up their own replicat process.


Without further ado , let me jump into the gory syntax stuff.

COMMON STUFF AT BOTH SOURCE AND DESTINATION
=============================================

create tablespace ggdata          
datafile '+DATA' size 100m
autoextend on maxsize 4096m
extent management local uniform size 1m
/
create user ggddlusr identified by welcome123
/

alter user ggddlusr
default tablespace ggdata
temporary tablespace temp
quota unlimited on ggdata
/
grant dba to ggddlusr
/

--The below is needed at the source only -read my note below
--but I run them at source and target -keep things simple policy.
exec dbms_goldengate_auth.grant_admin_privilege('GGDDLUSR')
/

That is it....I just skip stuff like  execute on flashback_database etc.
I just grant dba privilege and that is all I do....As I said I keep things simple as you wouldn't expect anybody else except the DBA team to use ggddlusr.

Also please note that

Manager (RMAN) works with Extract to retain the archive logs that Extract needs for
recovery. The special privileges are required for interaction with an underlying Oracle
Streams Capture and with RMAN.

For this to happen , you need to make sure that at the source you need to do the following based on the oracle version.

Oracle EE version Privileges
10.2 1. Run package to grant Oracle Streams admin privilege.
exec dbms_streams_auth.grant_admin_privilege('<user>')
2. Grant INSERT into logmnr_restart_ckpt$.
grant insert on system.logmnr_restart_ckpt$ to <user>;
3. Grant UPDATE on streams$_capture_process.
grant update on sys.streams$_capture_process to <user>;
4. Grant the 'become user' privilege.
grant become user to <user>;
11.1 and 11.2.0.1 1. Run package to grant Oracle Streams admin privilege.
exec dbms_streams_auth.grant_admin_privilege('<user>')
2. Grant the 'become user' privilege.
grant become user to <user>;
11.2.0.2 and later Run package to grant Oracle Streams admin privilege.
exec dbms_goldengate_auth.grant_admin_privilege('<user>');

Since I am running the above on 11.2.0.2 release, I just had to run


exec dbms_goldengate_auth.grant_admin_privilege('GGDDLUSR')
/
Please make sure that you do what is appropriate for your version.







Now go to Golden Gate Home, and open the file called GLOBALS (if not there, feel free to create a new file called GLOBALS) and put the following entry
GGSCHEMA <schema name>

In our case the <schema name> is GGDDLUSR.






Now that we have the common user GGDDLUSER setup in both source and target , let us go into some source and target specific actions.


STEPS AT SOURCE DATABASE
==========================
This step is to add DDL support to make sure that DDL changes like new tables, modified tables (ALTER TABLE...) at the source is pushed to destination side automatically. I just do this as this is a common requirement for all my replication needs.nowadays I just do this as a matter of habit.i recommend you do it too.
Just go to golden gate home directory and invoke

sqlplus '/ as sysdba' and run the following ----

--SETUP DDL USER
@marker_setup.sql
--when prompted type in ggddlusr
@ddl_setup.sql
--when prompted type in ggddlusr AND INITIALSETUP
@role_setup.sql
--when prompted type in ggddlusr
grant GGS_GGSUSER_ROLE to ggddlusr
/
@ddl_enable.sql
@ddl_pin ggddlusr

It is a very good idea to check whether all the above commands really setup the DDL replication by running
the following command.
  ddl_status.sql

whew!!!We are almost done except that we will create a checkpoint table at our target database to enable REPLICAT to use it to know where it was !!!


STEPS AT TARGET DATABASE
=========================

 Login to Golden Gate, by typing ggsci
 and do the following

dblogin userid ggddlusr,password welcome123
add checkpointtable ggddlusr.ckptpubs

Let me jump ahead and tell you the command to make sure how the above checkpoint table can be made to be used by the replicat process
add replicat pubsrep, exttrail /oracle/software/goldengate/11.1.1.1/dirdat/cc, CHECKPOINTTABLE ggddlusr.ckptpubs

There you got it!!!!We are all done except that we have to prepare the source database by adding supplemental logging and few other stuff.

I am going to discuss that in the next blog.Hope you find things easy.