Golden Gate Setup 5- Source Export and Target Import

Please give special attention to this section as we use SCN based export and import.

At this stage, please ensure that both extract and datapump extract are running before you do this. I repeat please do NOT perform this step unless both of them are running (or at the minimum the extract should be running).

SOURCE DATABASE
======================

SQL> create directory ATHDP as '/oracle/goldengatedata/exp';

Directory created.

SQL> SELECT TO_CHAR(CURRENT_SCN) FROM V$DATABASE;

TO_CHAR(CURRENT_SCN)
----------------------------------------
8297974408496

Go back to unix prompt,
export DATA_PUMP_DIR="ATHDP"

expdp \'/ as sysdba\' schemas=scott dumpfile=scott_exp.dmp include=table,index logfile=scott_exp.log flashback_scn=8297974408496 content=all

My requirement is that our app dev team needs only table data. Therefore the above way of using include makes sure that all other objects like procedure,package,views are omitted.Still triggers come across as they are attached to tables.Therefore I use excluse=trigger during the impdp process.

(Please note that since I precreate an empty schema on destination with all privs and grants , I exclude them in the expdp statement.please feel free to change as per your liking. Please make sure that you use FLASHBACK_SCN parameter in the expdp which is plugged in from the current_scn sql statement).
FTP the file to the remote server and import it to the destination schema.

TARGET DATABASE
++++++++++++++++++

I usually pre-create the schema on the target database with all the system roles and grants.

impdp \'/ as sysdba\' dumpfile=scott_exp.dmp   exclude=trigger,grant,ref_constraint logfile=impdp.log


To summarize, we did a flashback_scn based export on source and imported this into target.

Golden Gate Setup 4 (Source Database) Data Pump Extract Process

This is a relatively easier step and is done at the source database.
The extract (in the previous post) and the Data Pump extract should be running on the source.
The role of the DP extract is to transfer the files from the local location (where extract is writing to) and push it to the remote server. Therefore it is only logical that it should have 2 directories and a remote server name to work on.

First and foremost, decide a name for the DP extract process. In my example I have named it as DPATHDV.

Please go to dirprm directory under the folden gate home and create a file called dpathdv.prm with the following content.
+++++++++++++++++++++++++++++++++++++++++++
extract dpathdv
passthru
rmthost <remote server name>,mgrport 7809
rmttrail /oracle/software/goldengate/11.1.1.1/dirdat/at
table scott.*;
+++++++++++++++++++++++++++++++++++++++++++++++

Port 7809 is the default port where the manager process is listening on the remote server.Please ensure that the manager process is running to receive the files.

Also  /oracle/software/goldengate/11.1.1.1/dirdat is the directory in the remote server and files will be pushed with a name starting with "at" .

Now login , into ggsci
1.
add extract dpathdv,exttrailsource /oracle/goldengatedata/dirdat/at

The above directory is the local directory

2. add rmttrail /oracle/software/goldengate/11.1.1.1/dirdat/at,extract dpathdv,megabytes 250
The above directory is remote directory (which is also mentioned in the prm file).

3. Start the DP extract

 start ext dpathdv


GGSCI (jupiter) 12> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING                                          
EXTRACT     RUNNING     DPATHDV     00:00:00      00:00:04   
EXTRACT     RUNNING     XTATHDV     00:00:00      00:00:01   


GGSCI (jupiter) 13>

The above shows that both extract and DP extract is running on the source database.

Golden Gate Setup 3 (Source Database) Extract Process

This summary is not available. Please click here to view the post.

Golden Gate RMAN Interaction Causing RMAN-08137

**6/3/2013 Update: The following applies to Classic Capture. Things have changed a little bit for Integrated Capture. Hope to address this in a new blog *****
We are test-running GG on our development instance and therefore we had to put the DB in archivelog mode.
Till this incident hapenned I used to think the only reson why the database was in ARCHIVELOG mode was because the golden gate can read the archive log files if it lags behind the redo log files.

We used to run a RMAN job via cron every 4 hours which would just run the following command -

delete noprompt archivelog all;

We did not backup the archive logs as this was a DEV instance and the database was in ARCHIVELOG only for the GG capture process and we were also aware that GG will prevent archivelogs from being deleted (if necessary)which it had to read because of a lag where it could not keep in pace with redo generation of the database.

Lately the archive destination was getting 100% full and when we ran the job manually we would see archive files as old as 8-10 hours still in the destination.
The rman delete also would fail for the above files complaining as -

RMAN> delete noprompt archivelog all;

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=8 device type=DISK
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=/oracle/arch/test123/arch/1_3071_767059108.dbf thread=1 sequence=3071
..
..
..
..
..
Then we checked which redo log the capture process (info ext <ext name>,detail) was currently reading and it would show the sequence number of the  online redo log file.

So the big question was

WHY IS GOLDEN GATE PREVENTING ARCHIVE LOGS FROM BEING DELETED IF IT IS NOT LAGGING BEHIND

So today I am going to share a few things I learnt working with Oracle Support.

The first thing I came to know was the capture process writes only committed transactions into a trail file. This seemed to be the root cause of the issue.

So technically if it is 3pm now and there is an active transaction running from 7am in the morning , all the changes between 7am and 3pm have to be recorded somewhere, right? This obviously cannot go into the trail file as only commited transactions can get into trail file (I am still not sure why  this was architected this way???Any thoughts from anybody?).

So it looks like the Golden Gate capture process stores the details of a transaction in memory and it is written into the trail file only when comitted. There is something called Bounded Recovery (BR) whose timeout is set to 4 hours(by default) . That means if an active transaction goes beyond 4 hours,  the transaction is copied from memory into the BR file under $$GG_HOME/BR/<extract name> directory.

This parameter can be changed by putting the following parameter into the extract parameter file.
The following line sets it to 1 hour.

BR BRINTERVAL 1H
or
BR BRINTERVAL 20M  -> 20 minutes

Now assuming the BR is set to 4 hours (default value) and somebody stopped the extract process at say 7pm and as of 7pm there were many active transactions of which the oldest transaction started at 8am .

Now without the BR feature, when the extract is restarted it has to read redo information starting from 8am which would most probably be archived. Therefore the extract just wouldn't start if it is not able to access this archived file. This seems to be the most important reason for any database to be in archivelog mode for GG capture prcess to function effectively.

Please note that even by reducing BR down to 1 hour or so, is not going to resolve the RMAN-08137 issue .

The golden rule is
However old the archive file is, if it contains a record from an active transaction , then it cannot be deleted.Also the subsequent archive logfiles will not be deleted.



For example , if you wrote a code like

BEGIN
insert into t1 values .... ; -->redo sequence is 3500 on day 1
dbms_lock.sleep('15 days'); -->sleep for 15 days
insert into t2 values(......); redo sequence is 6000 on day 16
END;
/
RMAN will be unable to delete from sequence 3500 onwards eventhough sequence 3501 ...5999 does not have anything to do with this transaction (I am testing this out next week and update if i see any anomalies).

And since it is not possible to ask people to commit their transactions frequently , I seem to favour to decouple extract and rman by adding the following parameter
 ggsci> stop ext <extract name>
ggsci> dbloging userid <id> password <pw>
ggsci> UNREGISTER EXTRACT <extract name> LOGRETENTION

and editing the extract parameter file
TRANLOGOPTIONS LOGRETENTION DISABLED

Again this could lead to other issues like extract abending as it had to read from archivelogs (as it was lagging)
and could not find the archived files as rman had deleted it.

Therefore a good way is to put a balance by deleting on like sysdate - 4 hours worth of archive log files.
Let me know if anybody has found out a better way instead of decoupling extract and rman.

*********IMPORTANT 6/7/2012 UPDATE ***************
PLEASE NOTE THAT THE DEFAULT BEHAVIOUR HAS CHANGED SINCE GOLDEN GATE VERSION 11.1.1.1.2. FROM THIS VERSION , GG AND RMAN ARE DE-COUPLED BY DEFAULT AND THEREFORE YOU HAVE TO MANUALLY COUPLE THEM (IF YOU WANT BOTH OF THEM TO INTERACT WITH EACH OTHER). A GOOD WAY TO CHECK IS TO QUERY DBA_CAPTURE.
HERE IS STATEMENT FROM ORACLE SUPPORT

                            ++++++++
I have checked again and the fix for Bug 12648838 went into 11.1.1.1.2 (the note I looked at is not correct) so the default behaviour is to not create an implicit capture process unless you have explicity specified
the LOGRETENTION parameter. In your case you have not therefore you do not have one.

So what you observe is correct in your version.

                         +++++++++++++++ 
*************************************************************************** 

A few useful commands

To check the time of the oldest transaction
============================

 select vt.xidusn, vt.xidslot, vt.xidsqn, vs.sid, vs.username, vs.program, vs.machine, vt.used_ublk,
     vt.start_time, TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS') Current_Time
   from gv$transaction vt, gv$session vs where vt.addr = vs.taddr order by vt.
 start_time ;

To find the oldest dependent archive file
============================

send <extract name>, showtrans

Decouple RMAN and GoldenGate

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

ggsci> stop ext <extract name>

Modify the extract param file as follows

Extract XTATHDV
----------------------------------------------------
--Extract Process for testdb Database
--List of Schemas
--SCOTT
----------------------------------------------------
SETENV (ORACLE_HOME="/oracle/software/rdbms/11.2.0.3")
SETENV (ORACLE_SID="testdb")
userid ggddlusr, password xxxx
TRANLOGOPTIONS ASMUSER "sys@asm",asmpassword "xxx" LOGRETENTION DISABLED
exttrail /oracle/goldengatedata/dirdat/at
Reportcount every 30 Minutes, Rate
Report at 11:00
ReportRollover at 11:15
DiscardFile /oracle/goldengatedata/dirrpt/xtathdv.dsc, Append
DiscardRollover at 06:00 ON MONDAY
DDL include Mapped
table SCOTT.*;

Make a backup of the checkpoint file (./dirchk/<extract>.cpe)

ggsci> dbloging userid <id> password <pw>
ggsci> UNREGISTER EXTRACT <extract name> LOGRETENTION

If the above returns that extract was not registered you can try the following to see if extract has an entry in the dba_capture table, which has been disconnected from Oracle GoldenGate.

select capture_name, queue_owner, capture_user, start_scn, status from dba_capture;

This entry could be stopping the RMAN from removing the archives. To remove the entry use the following command

exec DBMS_CAPTURE_ADM.DROP_CAPTURE ('OGG$_LGHTYC15666903B');

ggsci> start ext <extract name>

 Couple GG and RMAN

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

Please note that post GG version 11.1.1.1.2   and beyond, GG and RMAN are decoupled by default .
So If you want to couple them, here are the steps



ggsci> stop ext <extract name>

Modify the extract param file as follows

Extract XTATHDV
----------------------------------------------------
--Extract Process for ATHENADV Database
--List of Schemas
--PUBS
----------------------------------------------------
SETENV (ORACLE_HOME="/oracle/software/rdbms/11.2.0.3")
SETENV (ORACLE_SID="xxx")
userid ggddlusr, password xxx
TRANLOGOPTIONS ASMUSER "sys@asm",asmpassword "xxx" LOGRETENTION ENABLED
exttrail /oracle/goldengatedata/dirdat/at
Reportcount every 30 Minutes, Rate
Report at 11:00
ReportRollover at 11:15
DiscardFile /oracle/goldengatedata/dirrpt/xtathdv.dsc, Append
DiscardRollover at 06:00 ON MONDAY
DDL include Mapped
table PUBS.*;

Make a backup of the checkpoint file (./dirchk/<extract>.cpe)

ggsci> dblogin userid <id> password <pw>
ggsci> REGISTER EXTRACT <extract name> LOGRETENTION

Change refresh schedules for an Oracle Materialized View ( MV )

Here is an automated script to change the refresh schedule for all MV's for a particular schema(in our case the
schema name is GGAMADM).
Please run this as DBA user (or change dba_mviews to user_mviews).

The basic syntax is

ALTER MATERIALIZED VIEW abc

     REFRESH COMPLETE

     START WITH trunc(sysdate+1) +(10/24)

     NEXT case when to_char( sysdate,'hh24' ) between '10' and '11' then trunc(sysdate)+(14/24) else trunc(sysdate+1)+(10/24) end

/

The above command refreshes the MV at 10am and 2pm.

declare
--str1 varchar2(100);
str2 varchar2(4000);
cursor c1
is select mview_name from dba_mviews where owner='GGAMDADM';
begin
for c2 in c1
loop

str2:= 'ALTER MATERIALIZED VIEW ggamdadm.'|| c2.mview_name||
     ' REFRESH COMPLETE
     START WITH trunc(sysdate+1) +(10/24)
     NEXT case when to_char( sysdate,''hh24'' ) between ''10'' and ''11'' then trunc(sysdate)+(14/24) else trunc(sysdate+1)+(10/24) end';
dbms_output.put_line(str2);
execute immediate str2;
end loop;
end;
/
  

Golden Gate Database Setup Part 2

In the first post, we did a bit on setting up the schemas for GG Replication both on the source and target databases.
Now we have to do a few things at the source database.

Connect as sqlplus '/ as sysdba'
--enable supplemental logging
alter database add supplemental log data
/
--switch logfile so that the next logfile has supplemental logging
alter system switch logfile
/
--check whether supplemental login was really enabled.As if you did not even bother to read output of step 1.
select supplemental_log_data_min from v$database
/

Now the fun part, login to golden gate on the source side
./ggsci


dblogin userid ggddlusr,password welcome123

add schematrandata SCOTT

The above will enable logging at schema level and makes your life easier compared to
ADD TRANDATA <table name>, as new tables are automatically picked up from the schema SCOTT.
By the way , the above command is supported on 11gR2 and needs PATCH 10423000.
To verify whether the above command worked
select * from table(logmnr$always_suplog_columns('owner in upper case','table in upper case'))

Or I have neatly scripted the below


declare
l_schema varchar2(20) := upper('PINNACLE') ;
l_table_name varchar2(200);
x_ctr number(5);
x_refcur sys_refcursor;
begin
open x_refcur for 'select table_name from dba_tables where owner='''||l_schema||''' order by table_name';
loop
fetch x_refcur into l_table_name;
exit when x_refcur%NOTFOUND;

execute immediate 'select count(*) from table(logmnr$always_suplog_columns(:1,:2))'
into x_ctr using l_schema,l_table_name ;
if x_ctr =0 then

dbms_output.put_line('Analyzing  '||l_table_name||' ..**ERROR**');
else
dbms_output.put_line('Analyzing  '||l_table_name||' ..OK');
end if;

end loop;
end;
/



As of now, this patch is not available on our platform HP-UX Itanium and we have to still use
ADD TRANDATA which is very painful.

But here is the script to generate the script

set echo off
set verify off
set pagesize 2000
set linesize 250
set trim on
set heading off
set feedback off
spool /tmp/scott.obey
select 'add trandata SCOTT.'||table_name
from dba_tables where owner = 'SCOTT' ;
spool off


Once the above file is generated , go back in ggsci and type

obey /tmp/scott.obey

By the way , a small note on the above step ---

1. The "add trandata" does something like "alter table <name> add supplemental log group ggs_<table name>_object id (comma seperated columns) ALWAYS"
Its just to give you an idea and the actual "alter table" sql executed behind the scenes might differ depending on the db versions etc

2. To drop the table level supplemental logging you could either do it from ggsci or sqlplus

ggsci> dblogin userid <id> password <pw>
ggsci> info trandata schema.table_name
ggsci> DELETE TRANDATA schema.table_name

or

sql> select * from dba_log_groups where table_name=upper('&table_name') and owner=upper('&owner');
sql> alter table <table name> drop supplemental log group <log group name>;

By the way, if you want to add table level logging for all columns, you use 'supplemental log data' instead of supplemental log group -

ALTER TABLE tab1 ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
The valid options are ALL,PRIMARY,UNIQUE and FOREIGN KEY.
The above sql creates a supplemental log group starting with SYS_C instead of GGS_xxxx

To drop , you can use
ALTER TABLE tab1 DROP SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
OR
ALTER TABLE tab1 DROP SUPPLEMENTAL LOG GROUP SYS_Cxxxxxx

/

**PLEASE NOTE, IF YOU HAD USED ADD SCHEMATRANDATA THERE WILL BE NO ENTRIES IN DBA_LOG_GROUPS TABLE.YOU WILL HAVE TO USE THE BELOW QUERY

select * from table(logmnr$always_suplog_columns('owner in upper case','table in upper case'))

**

We are now all done and go to the next step.

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.