Oracle Pipelined Functions

Let me give you a typical scenario which we encounter everday.

Say we have a table called employee and the fields are (or rather the most significant fields for our discussion) are

ename and dno .

SQL> select substr(ename,1,25),dno from employee;

SUBSTR(ENAME,1,25)               DNO

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

JACK                              10

JILL                              10

MASON                             20

MARK                              20

you are being asked to write a small pl/sql code to return the enames of a particular department, the normal way we would

do is to write a procedure like

CREATE OR REPLACE TYPE vcarray AS TABLE OF VARCHAR2(500)

/

CREATE or replace procedure p1(dno_p IN number,ename_p OUT vcarray)

IS

CURSOR c1 is select ename from employee where dno=dno_p;

i number:=1;

BEGIN

ename_p := vcarray();

for c2 in c1

loop

 ename_p.extend;

 ename_p(ename_p.count) :=c2.ename ;

 i:=i+1;

end loop;

END;

/

To call the above procedure , we are forced to write a pl/sql block like

SQL> declare

  2  ename vcarray ;

  3  begin

  4  p1(10,ename);

  5  for i in 1..ename.COUNT

  6  LOOP

  7  dbms_output.put_line(ename(i));

  8  end loop;

  9  end;

 10  /

JACK

JILL

PL/SQL procedure successfully completed.

So in the above example we are forced to write a pl/sql block to read from the procedure.

The below example uses pipelined functions which will make it possibel for the same

job to be done via a function (eventhough we think of function as only returning

a single value) and the icing on the cake is the function can be referenced as a simple select

from sqlplus prompt itself....

CREATE OR REPLACE TYPE vcarray AS TABLE OF VARCHAR2(500)

/

CREATE OR REPLACE FUNCTION get_employees (dno_p IN number)

RETURN vcarray

PIPELINED

AS

CURSOR c1 is select ename from employee where dno=dno_p;

BEGIN

FOR c2 in c1

LOOP

   pipe row (c2.ename);

END LOOP;

RETURN;

END;

/

SQL> select * from TABLE (get_employees(10) );

COLUMN_VALUE

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

JACK

JILL

Script to monitor and restart failed GoldenGate Processes

The below script should restart aborted GG processes.
The script is capable of restarting Manager, Extract and Replicat Processes.

The logic is it just looks at info all command (of ggsci) and attempts to restart any process which it sees as not RUNNING .
A non-running process can be either in STOPPED or ABORTED status.

It is intelligent enough to find out whether the stopped process is an extract, replicat or manager.

Please note I have used a script call ed /usr/local/bin/sid to set the env parameters. This script can be replaced by your own script or by setting env variables manually.



#!/bin/ksh
. /usr/local/bin/sid xxx
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
export GG_HOME=/oracle/software/goldengate/11.1.1.1.2
export PATH=$PATH:.
MSGFILE="/tmp/mail.txt"
EMAILFILE="/tmp/email.txt"
SERVERNAME=`hostname`
rm -rf $EMAILFILE 2>/dev/null



cd $GG_HOME
ggsci <<EOF >$MSGFILE
info all
exit
EOF

ERROR_CTR=`cat $MSGFILE|grep -v -i "running"|wc -l`


#following loop is for manager process restart

if  [ $ERROR_CTR -ne 0 ] ; then

cat $MSGFILE|grep -v -i "running"|grep -i "manager"|while read LINE ;do

echo "Manager has aborted ">>$EMAILFILE
echo "Attempting to restart" >>$EMAILFILE


cd $GG_HOME
ggsci <<EOF >>$EMAILFILE
start mgr
info all
exit
EOF

done
fi

#following loop is for extract process restart


if  [ $ERROR_CTR -ne 0 ] ; then

cat $MSGFILE|grep -v -i "running"|grep -i "extract"|awk '  {print $3 } '|while read LINE ;do

echo "EXTRACT $LINE has aborted ">>$EMAILFILE
echo "attempting to restart" >>$EMAILFILE


cd $GG_HOME
ggsci <<EOF >>$EMAILFILE
start extract $LINE
info all
exit
EOF



done
fi

#following loop is for replicat process restart

if  [ $ERROR_CTR -ne 0 ] ; then

cat $MSGFILE|grep -v -i "running"|grep -i "replicat"|awk '  {print $3 } '|while read LINE ;do

echo "REPLICAT $LINE has aborted ">>$EMAILFILE
echo "Attempting to restart" >>$EMAILFILE


cd $GG_HOME
ggsci <<EOF >>$EMAILFILE
start replicat $LINE
info all
exit
EOF



done
fi


[ -f $EMAILFILE ] && cat $EMAILFILE|mailx -s "Extract died on $SERVERNAME" gautha@xxx.xxx

Accurately predicting the redo log sequence needed for a Golden Gate Restart

Bounded Recovery is set to 20 Minutes.

1. Started an active transaction which runs for 5 hours at 8.11AM.
The log sequence at the start of transaction is 16230 (please see below logfile).

jupiter<oracle>/tmp> cat test.sh
#!/bin/ksh
. /usr/local/bin/sid athenadv
sqlplus ops\$gchandra/test123 << EOF >test.log

select max(sequence#) from v\$log;
select to_char(sysdate,'dd-mon-yyyy hh24:mi:ss') from dual;
drop table testtab;
create table testtab (col1 number);
insert into testtab values(1);
execute dbms_lock.sleep(18000);
commit;
exit
EOF

Run the above script(in the background)
nohup ./test.sh &

Now check the spool file created.....

jupiter<oracle>/tmp> cat test.log

SQL*Plus: Release 11.2.0.3.0 Production on Fri Jun 8 08:11:22 2012

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


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

SQL> SQL>
MAX(SEQUENCE#)
--------------
         16230

SQL>
TO_CHAR(SYSDATE,'DD-MON-YYYYH
-----------------------------
08-jun-2012 08:11:22

SQL>
Table dropped.

SQL>
Table created.

SQL>
1 row created.


2.

At around 10am, I stop the extract process.
GGSCI (jupiter) 2> stop ext xtathdv

Sending STOP request to EXTRACT XTATHDV ...
Request processed.

3. Check the BR checkpoint from ggserr.log and also from BR directory.
It hapenned at around 9.42am.

2012-06-08 09:42:24  INFO    OGG-01738  Oracle GoldenGate Capture for Oracle, xtathdv.prm:  BOUNDED RECOVERY: CHECKPOINT: for object pool 1: p28822_extr: start=SeqNo: 16293, RBA: 75422736, SCN: 1932.1174983728 (8299051799600), Timestamp: 2012-06-08 09:42:23.000000, end=SeqNo: 16293, RBA: 75423744, SCN: 1932.1174983728 (8299051799600), Timestamp: 2012-06-08 09:42:23.000000.

jupiter<oracle>/oracle/software/goldengate/11.1.1.1.2/BR/XTATHDV> ls -lt
total 88
-rw-rw----   1 oracle     dba          65536 Jun  8 09:42 CP.XTATHDV.000000039
-rw-rw----   1 oracle     dba          65536 Jun  8 09:22 PO.XTATHDV.0000007.0000000
-rw-rw----   1 oracle     dba          65536 Jun  8 08:42 PO.XTATHDV.0000006.0000000
drwxrwxr-x   2 oracle     dba             96 Jun  7 15:54 stale


4.Check the archive log sequence at around 9.42am.

select sequence# from v$archived_log
  where to_date('08-jun-2012 09:42:00','dd-mon-yyyy hh24:mi:ss') between first_time and next_time;

SQL> select sequence# from v$archived_log
  2    where to_date('08-jun-2012 09:42:00','dd-mon-yyyy hh24:mi:ss') between first_time and next_time;

 SEQUENCE#
----------
     16293
    
This means I need only archive log from sequence 16293 when I restart GG extract process.
I should not need from sequence 16230(log sequence # from start of transaction).


5.Perform a few log switches and delete all archive files from RMAN.
SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

Run the following from rman -

run {
allocate channel c1 type 'sbt_tape' parms 'ENV=(TDPO_OPTFILE=/oracle/admin/backup/config/jupiter_tdpo.opt)';
backup
filesperset = 4
format 'al_%d%s%t_%U'
archivelog until time 'SYSDATE'
delete all input;
sql 'alter system archive log current';
release channel c1;
}


6.Kill the test.sh script.

jupiter<oracle>/oracle/arch/athenadv/arch> ps -ef|grep test.sh
  oracle  5072     1  0 08:11:22 ?         0:00 /bin/ksh ./test.sh
  oracle 15014 26448  0 10:16:01 pts/6     0:00 grep test.sh
jupiter<oracle>/oracle/arch/athenadv/arch> ps -ef|grep sqlplus
  oracle  5077  5072  0 08:11:22 ?         0:00 sqlplus ops$gchandra/test123
  oracle 15018 26448  1 10:16:08 pts/6     0:00 grep sqlplus
jupiter<oracle>/oracle/arch/athenadv/arch> kill -9 5077
jupiter<oracle>/oracle/arch/athenadv/arch>
jupiter<oracle>/oracle/arch/athenadv/arch> ps -ef|grep test.sh
  oracle 16879 26448  1 10:16:27 pts/6     0:00 grep test.sh
jupiter<oracle>/oracle/arch/athenadv/arch>


7.

GGSCI (jupiter) 2> start ext xtathdv

Sending START request to MANAGER ...
EXTRACT XTATHDV starting


2012-06-08 10:44:42  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle): start ext xtathdv.
2012-06-08 10:44:42  INFO    OGG-00963  Oracle GoldenGate Manager for Oracle, mgr.prm:  Command received from GGSCI on host 10.64.17.19 (START EXTRACT XTATHDV ).
2012-06-08 10:44:42  INFO    OGG-00975  Oracle GoldenGate Manager for Oracle, mgr.prm:  EXTRACT XTATHDV starting.
2012-06-08 10:44:42  INFO    OGG-00992  Oracle GoldenGate Capture for Oracle, xtathdv.prm:  EXTRACT XTATHDV starting.
2012-06-08 10:44:43  INFO    OGG-01639  Oracle GoldenGate Capture for Oracle, xtathdv.prm:  BOUNDED RECOVERY:  ACTIVE: for object pool 1: p28822_extr.
2012-06-08 10:44:43  INFO    OGG-01640  Oracle GoldenGate Capture for Oracle, xtathdv.prm:  BOUNDED RECOVERY: recovery start XID: 0.0.0.
2012-06-08 10:44:43  INFO    OGG-01641  Oracle GoldenGate Capture for Oracle, xtathdv.prm:  BOUNDED RECOVERY: recovery start position: SeqNo: 16293, RBA: 75422736, SCN: 1932.1174983728 (8299051799600), Timestamp: 2012-06-08 09:42:23.000000.
2012-06-08 10:44:43  INFO    OGG-01642  Oracle GoldenGate Capture for Oracle, xtathdv.prm:  BOUNDED RECOVERY: recovery end position: SeqNo: 16293, RBA: 75423744, SCN: 1932.1174983728 (8299051799600), Timestamp: 2012-06-08 09:42:23.000000.
2012-06-08 10:44:43  INFO    OGG-01579  Oracle GoldenGate Capture for Oracle, xtathdv.prm:  BOUNDED RECOVERY: VALID BCP: CP.XTATHDV.000000039.
2012-06-08 10:44:43  INFO    OGG-01629  Oracle GoldenGate Capture for Oracle, xtathdv.prm:  BOUNDED RECOVERY: PERSISTED OBJECTS RECOVERED: 2.
2012-06-08 10:44:45  INFO    OGG-01513  Oracle GoldenGate Capture for Oracle, xtathdv.prm:  Positioning to Sequence 16293, RBA 75422736.
2012-06-08 10:46:12  ERROR   OGG-00446  Oracle GoldenGate Capture for Oracle, xtathdv.prm:  Could not find archived log for sequence 16293 thread 1 under default destinations SQL <SELECT  name    FROM v$archived_log   WHERE sequence# = :ora_seq_no AND         thread# = :ora_thread AND         resetlogs_id = :ora_resetlog_id AND         archived = 'YES' AND         deleted = 'NO>, error retrieving redo file name for sequence 16293, archived = 1, use_alternate = 0Not able to establish initial position for sequence 16293, rba 75422736.
2012-06-08 10:46:12  ERROR   OGG-01668  Oracle GoldenGate Capture for Oracle, xtathdv.prm:  PROCESS ABENDING.


The above log extract says that it reads from 16293. That is as per my expectation.

Since 16293 is not present in archive destination, the GG process abends as expected.



8.Restore the archive log from rman

RMAN> RESTORE ARCHIVELOG FROM SEQUENCE 16293;

Starting restore at 08-Jun-2012
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=112 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Data Protection for Oracle: version 5.5.2.0
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=594 device type=DISK

archived log for thread 1 with sequence 16348 is already on disk as file /oracle/arch/athenadv/arch/1_16348_783838261.dbf
archived log for thread 1 with sequence 16349 is already on disk as file /oracle/arch/athenadv/arch/1_16349_783838261.dbf
..
..
..

9. Restart the GG extract process and observe the logfile

GGSCI (jupiter) 3> start ext xtathdv

Sending START request to MANAGER ...
EXTRACT XTATHDV starting


Observe the logfile

2012-06-08 10:51:46  INFO    OGG-00975  Oracle GoldenGate Manager for Oracle, mgr.prm:  EXTRACT XTATHDV starting.
2012-06-08 10:51:46  INFO    OGG-00992  Oracle GoldenGate Capture for Oracle, xtathdv.prm:  EXTRACT XTATHDV starting.
2012-06-08 10:51:46  INFO    OGG-01639  Oracle GoldenGate Capture for Oracle, xtathdv.prm:  BOUNDED RECOVERY:  ACTIVE: for object pool 1: p28822_extr.
2012-06-08 10:51:46  INFO    OGG-01640  Oracle GoldenGate Capture for Oracle, xtathdv.prm:  BOUNDED RECOVERY: recovery start XID: 0.0.0.
2012-06-08 10:51:46  INFO    OGG-01641  Oracle GoldenGate Capture for Oracle, xtathdv.prm:  BOUNDED RECOVERY: recovery start position: SeqNo: 16293, RBA: 75422736, SCN: 1932.1174983728 (8299051799600), Timestamp: 2012-06-08 09:42:23.000000.
2012-06-08 10:51:46  INFO    OGG-01642  Oracle GoldenGate Capture for Oracle, xtathdv.prm:  BOUNDED RECOVERY: recovery end position: SeqNo: 16293, RBA: 75423744, SCN: 1932.1174983728 (8299051799600), Timestamp: 2012-06-08 09:42:23.000000.
2012-06-08 10:51:46  INFO    OGG-01579  Oracle GoldenGate Capture for Oracle, xtathdv.prm:  BOUNDED RECOVERY: VALID BCP: CP.XTATHDV.000000039.
2012-06-08 10:51:46  INFO    OGG-01629  Oracle GoldenGate Capture for Oracle, xtathdv.prm:  BOUNDED RECOVERY: PERSISTED OBJECTS RECOVERED: 2.
2012-06-08 10:51:49  INFO    OGG-01513  Oracle GoldenGate Capture for Oracle, xtathdv.prm:  Positioning to Sequence 16293, RBA 75422736.
2012-06-08 10:51:49  INFO    OGG-01516  Oracle GoldenGate Capture for Oracle, xtathdv.prm:  Positioned to Sequence 16293, RBA 75422736, Jun 8, 2012 9:42:23 AM.
2012-06-08 10:51:49  INFO    OGG-00993  Oracle GoldenGate Capture for Oracle, xtathdv.prm:  EXTRACT XTATHDV started.
2012-06-08 10:51:49  INFO    OGG-01055  Oracle GoldenGate Capture for Oracle, xtathdv.prm:  Recovery initialization completed for target file /oracle/goldengatedata/dirdat/at000011, at RBA 32117725.
2012-06-08 10:51:49  INFO    OGG-01478  Oracle GoldenGate Capture for Oracle, xtathdv.prm:  Output file /oracle/goldengatedata/dirdat/at is using format RELEASE 10.4/11.1.
2012-06-08 10:51:49  INFO    OGG-01026  Oracle GoldenGate Capture for Oracle, xtathdv.prm:  Rolling over remote file /oracle/goldengatedata/dirdat/at000011.
2012-06-08 10:51:49  INFO    OGG-01053  Oracle GoldenGate Capture for Oracle, xtathdv.prm:  Recovery completed for target file /oracle/goldengatedata/dirdat/at000012, at RBA 1032.
2012-06-08 10:51:49  INFO    OGG-01057  Oracle GoldenGate Capture for Oracle, xtathdv.prm:  Recovery completed for all targets.
2012-06-08 10:51:50  INFO    OGG-01517  Oracle GoldenGate Capture for Oracle, xtathdv.prm:  Position of first record processed Sequence 16293, RBA 75422736, SCN 1932.1174983725, Jun 8, 2012 9:42:23 AM.
2012-06-08 10:51:50  INFO    OGG-01644  Oracle GoldenGate Capture for Oracle, xtathdv.prm:  BOUNDED RECOVERY: COMPLETE: for object pool 1: p28822_extr at SeqNo: 16293, RBA: 75423760, SCN: 1932.1174983729 (8299051799601).
2012-06-08 10:52:37  WARNING OGG-00869  Oracle GoldenGate Capture for Oracle, xtathdv.prm:  No unique key is defined for table DV3_STATUS. All viable columns will be used to represent the key, but may not guarantee uniqueness.  KEYCOLS may be used to define the key.


Looks like GG extract is chuggling along.

Hope you find this useful.

Golden Gate Bounded Recovery ( BR) Process

I created a sample script called test.sh and introduced a sleeptime of 5 hours(or 18,000 seconds).

Make sure below user has DBA privs so that he can query from v$log(to check the log sequence).


++++++Contents of test.sh +++++++++++++++++++++++++

#!/bin/ksh
sqlplus ops\$gchandra/test123 << EOF >test.log

select max(sequence#) from v\$log;
select to_char(sysdate,'dd-mon-yyyy hh24:mi:ss') from dual;
drop table testtab;
create table testtab (col1 number);
insert into testtab values(1);
execute dbms_lock.sleep(18000);
commit;
exit
EOF

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

I run the script in background with no interruption.
jupiter<oracle>/tmp> nohup ./test.sh &
[1]     28917




jupiter<oracle>/oracle/arch/athenadv/arch> cat /tmp/test.log

SQL*Plus: Release 11.2.0.3.0 Production on Thu Jun 7 20:29:25 2012

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


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

SQL> SQL>
MAX(SEQUENCE#)
--------------
         15625

SQL>
TO_CHAR(SYSDATE,'DD-MON-YYYYH
-----------------------------
07-jun-2012 20:29:26

SQL>
Table dropped.

SQL>
Table created.

SQL>
1 row created.





The point to note is if the GG process is stopped or aborts at this point of time and restarted later, it needs
access to redo log from sequence 15625 . This is because uncomitted transactions are never written to the trail file
by golden gate. Only commited transactions are written.

There is something called bounded recovery which is basically a time period i.e 4 hours by default.
After every BR time interval , GG looks at the active transactions which qualify as old transactions ( i.e
they have been active for more than BR time period) and writes it to a temp location GG HOME/BR/<extract name> .

For example if the BR is set to 4 hours(default), and say a BR flush happens at 9am. At that time it scans all the active transactions
which started earlier than 5am (9am -4 hrs).
Say there were say 2 active transactions A and B  .Transaction A started at 5.01am and transaction B started at
3.59am. Therefore transaction B is flushed to the BR location and transaction A is ignored as it has not been running for 4 hours.

Therefore if the golden gate crashed just a few seconds before the next flushout i.e.1pm , then transaction A which started
at 5.01am is lost.Therefore when you restart the GG it needs archive logs generated from 5.01am.

So in this worst case scenario , it needs access to log files(or archive files) from 2 times BR interval.
So prepare to retain archivelogs (or have capacity to restore archive logs) for 2 X (BR Interval period).



Now via RMAN, I delete the archivelog 15625.
Please note that the transaction is still active but now if GG is stopped and restarted, it will not have access to archive sequence 15625
as we have deleted it via rman using a simple command

backup
filesperset = 4
format 'al_%d%s%t_%U'
archivelog until time 'sysdate'
delete all input;



Now let us see if bounded recovery comes to our rescue.

I have on purpose set BR to 20 minutes and will patiently wait for the BR to happen.
Then I will note the log sequence as of the BR . Ideally after this point if GG process is stopped and restarted, it should
not need the original log sequence 15625 but should need only the log sequence during the BR flushout.

The BR flushout happens (as shown by GG log at 8.50pm)

2012-06-07 20:50:20  INFO    OGG-01738  Oracle GoldenGate Capture for Oracle, xtathdv.prm:  BOUNDED RECOVERY: CHECKPOINT: for object pool 1: p28822_extr: start=SeqNo: 15642, RBA: 14661648, SCN: 1932.1131459784 (8299008275656), Timestamp: 2012-06-07 20:50:19.000000, end=SeqNo: 15642, RBA: 14662656, SCN: 1932.1131459784 (8299008275656), Timestamp: 2012-06-07 20:50:19.000000.


Also see it here
jupiter<oracle>/oracle/software/goldengate/11.1.1.1.2/BR/XTATHDV> ls -lt
total 64
-rw-rw----   1 oracle     dba          65536 Jun  7 20:50 CP.XTATHDV.000000003
-rw-rw----   1 oracle     dba          65536 Jun  7 20:50 PO.XTATHDV.0000001.0000000
drwxrwxr-x   2 oracle     dba             96 Jun  7 15:54 stale



SQL> select sequence# from v$archived_log
  2   where to_date('07-jun-2012 20:50:00','dd-mon-yyyy hh24:mi:ss') between first_time and next_time;

 SEQUENCE#
----------
     15641

SQL> select sequence# from v$archived_log
  2    where to_date('07-jun-2012 20:50:59','dd-mon-yyyy hh24:mi:ss') between first_time and next_time;

 SEQUENCE#
----------
     15642


So at this point if I stop GG and restart I should need only from sequence 15641 or 15642 . To be on safer side, make
sure you have archive logs from sequence 15641.

let us test this !!!!


GGSCI (jupiter) 39> stop ext xtathdv

Sending STOP request to EXTRACT XTATHDV ...
Request processed.


Restart the extract and carefully observe the ggserr.log file


2012-06-07 20:53:46  INFO    OGG-00975  Oracle GoldenGate Manager for Oracle, mgr.prm:  EXTRACT XTATHDV starting.
2012-06-07 20:53:46  INFO    OGG-00992  Oracle GoldenGate Capture for Oracle, xtathdv.prm:  EXTRACT XTATHDV starting.
2012-06-07 20:53:47  INFO    OGG-01639  Oracle GoldenGate Capture for Oracle, xtathdv.prm:  BOUNDED RECOVERY:  ACTIVE: for object pool 1: p28822_extr.
2012-06-07 20:53:47  INFO    OGG-01640  Oracle GoldenGate Capture for Oracle, xtathdv.prm:  BOUNDED RECOVERY: recovery start XID: 0.0.0.
2012-06-07 20:53:47  INFO    OGG-01641  Oracle GoldenGate Capture for Oracle, xtathdv.prm:  BOUNDED RECOVERY: recovery start position: SeqNo: 15642, RBA: 14661648, SCN: 1932.1131459784 (8299008275656), Timestamp: 2012-06-07 20:50:19.000000.
2012-06-07 20:53:47  INFO    OGG-01642  Oracle GoldenGate Capture for Oracle, xtathdv.prm:  BOUNDED RECOVERY: recovery end position: SeqNo: 15642, RBA: 14662656, SCN: 1932.1131459784 (8299008275656), Timestamp: 2012-06-07 20:50:19.000000.
2012-06-07 20:53:47  INFO    OGG-01579  Oracle GoldenGate Capture for Oracle, xtathdv.prm:  BOUNDED RECOVERY: VALID BCP: CP.XTATHDV.000000003.
2012-06-07 20:53:47  INFO    OGG-01629  Oracle GoldenGate Capture for Oracle, xtathdv.prm:  BOUNDED RECOVERY: PERSISTED OBJECTS RECOVERED: 1.
2012-06-07 20:53:50  INFO    OGG-01513  Oracle GoldenGate Capture for Oracle, xtathdv.prm:  Positioning to Sequence 15642, RBA 14661648.
2012-06-07 20:53:51  INFO    OGG-01516  Oracle GoldenGate Capture for Oracle, xtathdv.prm:  Positioned to Sequence 15642, RBA 14661648, Jun 7, 2012 8:50:19 PM.
2012-06-07 20:53:51  INFO    OGG-00993  Oracle GoldenGate Capture for Oracle, xtathdv.prm:  EXTRACT XTATHDV started.
2012-06-07 20:53:51  INFO    OGG-01055  Oracle GoldenGate Capture for Oracle, xtathdv.prm:  Recovery initialization completed for target file /oracle/goldengatedata/dirdat/at000005, at RBA 2213190.
2012-06-07 20:53:51  INFO    OGG-01478  Oracle GoldenGate Capture for Oracle, xtathdv.prm:  Output file /oracle/goldengatedata/dirdat/at is using format RELEASE 10.4/11.1.
2012-06-07 20:53:51  INFO    OGG-01026  Oracle GoldenGate Capture for Oracle, xtathdv.prm:  Rolling over remote file /oracle/goldengatedata/dirdat/at000005.
2012-06-07 20:53:51  INFO    OGG-01053  Oracle GoldenGate Capture for Oracle, xtathdv.prm:  Recovery completed for target file /oracle/goldengatedata/dirdat/at000006, at RBA 1032.
2012-06-07 20:53:51  INFO    OGG-01057  Oracle GoldenGate Capture for Oracle, xtathdv.prm:  Recovery completed for all targets.
2012-06-07 20:53:51  INFO    OGG-01517  Oracle GoldenGate Capture for Oracle, xtathdv.prm:  Position of first record processed Sequence 15642, RBA 14661648, SCN 1932.1131459784, Jun 7, 2012 8:50:19 PM.
2012-06-07 20:53:51  INFO    OGG-01644  Oracle GoldenGate Capture for Oracle, xtathdv.prm:  BOUNDED RECOVERY: COMPLETE: for object pool 1: p28822_extr at SeqNo: 15642, RBA: 14662672, SCN: 1932.1131459785 (8299008275657).
2012-06-07 20:54:02  WARNING OGG-00869  Oracle GoldenGate Capture for Oracle, xtathdv.prm:  No unique key is defined for table DV3_STATUS. All viable columns will be used to represent the key, but may not guarantee uniqueness.  KEYCOLS may be used to define the key.


If you look above it starts looking at archive logs only from sequence 15642. This is indeed a big relief as some transactions
run longer than 4 hours and if i left it to default I might have to restore as much as 8 hours  of archive logs.

Install XML DB on Oracle 11gR2

This note is copied from the Metalink Note and talks about removal and re-install of an XML DB on a 11gR2 Oracle Database.
This is more for my reference and sorry for duplicating ....

XDB Removal

The catnoqm.sql script drops XDB.

SQL> spool xdb_removal.log
SQL> set echo on;
SQL> connect / as sysdba
SQL> shutdown immediate;
SQL> startup
SQL> @?/rdbms/admin/catnoqm.sql
SQL> spool off;


XDB Installation

The catqm.sql script requires the following parameters be passed to it when run:

A. XDB user password
B. XDB user default tablespace
      (Any tablespace other than SYSTEM, UNDO and TEMP can be specified.
       The specified tablespace must already exist prior to running the script.)
C. XDB user temporary tablespace
D. SecureFiles = YES or NO
      (If YES is specified, the XDB repository will use SecureFile storage.
       If NO is specified, LOBS will be used.
       To use SecureFiles, compatibility must be set to 11.2.
       The tablespace specified for the XDB repository must be using
       Automatic Segment Space Management (ASSM) for SecureFiles to be used.)

Therefore the syntax to run catqm.sql is the following:
SQL> catqm.sql A B C D

For Example:
SQL> @?/rdbms/admin/catqm.sql XDB XDB TEMP YES

## IMPORTANT: You must shutdown and restart the database between removal and reinstall ##

SQL> spool xdb_install.log
SQL> set echo on;
SQL> connect / as sysdba
SQL> shutdown immediate;
SQL> startup;
SQL> @?/rdbms/admin/catqm.sql <XDB pwd> <XDB default tbs> <XDB temporary tbs> <SecureFiles = YES/NO>
SQL> @?/rdbms/admin/utlrp.sql
SQL> spool off

Verify XDB Installation

spool xdb_status.txt

set echo on;
connect / as sysdba
set pagesize 1000
col comp_name format a36
col version format a12
col status format a8
col owner format a12
col object_name format a35
col name format a25

-- Check status of XDB

select comp_name, version, status
from dba_registry
where comp_id = 'XDB';

-- Check for invalid objects owned by XDB

select owner, object_name, object_type, status
from dba_objects
where status = 'INVALID'
and owner = 'XDB';

spool off;

Golden Gate Setup 7 - Replicat Process on Target

Go to dirprm directory under golden gate home on target and create a file called <replicat name>.prm.
in our case the replicat name is RPATHDV.

I am going to explain the contents of the parameter file in a subsequent post.So please have patience with me
before scratching your head looking at the below parameter file.

Please refer to Metalink Note
Example How to Use DML Exception Handling and DDL Replication Together in GoldenGate Replicat [ID 1382092.1

I believe the support analyst wrote this based on my ticket :-).

***changed the below on 11/30/2012 to put a better exception handling syntax
as suggested by oracle support. The Part in red shows the exact place where it was changed.
Also read metalink note :Replicat Exceptions Mapping Is Not Working (Doc ID 1504267.1)*******
 +++++++++++++++++++++++++++++++++++++++
replicat rpathdv
SETENV (ORACLE_HOME="/oracle/software/rdbms/11.2.0.3")
SETENV (ORACLE_SID="xxx")
SETENV (NLS_LANG = "AMERICAN_AMERICA.UTF8")
userid ggddlusr, password <password>
Reportcount every 30 Minutes, Rate
Report at 15:00
ReportRollover at 15:15
discardfile /oracle/goldengatedata/dirdsc/rpathdv.dsc, megabytes 50, append
DDL INCLUDE MAPPED, &
EXCLUDE OBJNAME GGDDLUSR.EXCEPTIONS
DDLOPTIONS REPORT
AssumeTargetDefs
REPERROR (DEFAULT, EXCEPTION)
map scott.*, target scott.*, mapexception (TARGET ggadm.exceptions
, INSERTALLRECORDS
-- , EXCEPTIONSONLY
, colmap ( rep_name = 'rpathdv'
, table_name = @GETENV ('GGHEADER', 'TABLENAME')
, errno = @GETENV ('LASTERR', 'DBERRNUM')
, dberrmsg = @GETENV ( 'LASTERR', 'DBERRMSG')
, optype = @GETENV ( 'LASTERR', 'OPTYPE')
, errtype = @GETENV ( 'LASTERR', 'ERRTYPE')
, logrba = @GETENV ( 'GGHEADER', 'LOGRBA')
, logposition = @GETENV ( 'GGHEADER', 'LOGPOSITION')
, TRAILRBA = @GETENV('RECORD', 'FILERBA')
, TRAILSEQUENCE = @GETENV('RECORD', 'FILESEQNO')
, committimestamp = @GETENV ( 'GGHEADER', 'COMMITTIMESTAMP')
) );

 +++++++++++++++++++++++++++++++++++++++++++++


Login into ggsci on the target

[oracle@xxxx 11.1.1.1]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.1.1.1.2_06 13694062 OGGCORE_11.1.1.1.3_PLATFORMS_120216.1755
Linux, x64, 64bit (optimized), Oracle 11g on Feb 19 2012 23:07:54

Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.



GGSCI  1> dblogin userid ggddlusr,password <password>
Successfully logged into database.
GGSCI  2> add checkpointtable ggddlusr.ckptathdv

Successfully created checkpoint table GGDDLUSR.CKPTATHDV.

The above step adds a dedicated checkpoint table for our replicat. This is not mandatory but desirable.

GGSCI  3> add replicat rpathdv, exttrail /oracle/software/goldengate/11.1.1.1/dirdat/at, CHECKPOINTTABLE ggddlusr.ckptathdv
REPLICAT added.

GGSCI  4> start replicat rpathdv ,aftercsn 8297974408496

Sending START request to MANAGER ...
REPLICAT RPATHDV starting

Please note that the above is the flashback scn of the export dump on the source database. Since the export was done at the above flashback scn and the extract was started before that, we are telling GG replicat to apply only transactions after that SCN. This feature is really cool. This is the reason why the extract process should be started before you take the export of the source schema.

Now as soon as GG replicat encounters an exception, like "No data found" or Primary Key Violation
you should see an entry in GGDDLUSR.exceptions table.

A sample entry is like this ....

Unfortunatley the literals are replaced by bind variables.  Will soon right a blog to see the literal values.


SQL> select dberrmsg from exceptions where rownum<2;

DBERRMSG
--------------------------------------------------------------------------------
OCI Error ORA-01403: no data found, SQL <UPDATE "SCOTT"."EMPLOYEES" SET "EMP_
ETYP_CD" = :a1,"EMP_PPL_CD" = :a2,"EMP_ORGU_CD" = :a3,"EMP_SUPPORT_EXT_NO" = :a4
,"EMP_SRCH_NM" = :a5,"EMP_USERID" = :a6,"EMP_HDC_DEPT_CODE" = :a7,"EMP_HDC_SUPER
VISOR_EMP_CD" = :a8 WHERE "EMP_CD" = :b0>

Cool, right!!!!

Golden Gate Setup 6 - Exceptions Table on Target

This post is optional to follow in the sense that I always like to trap exceptions on the target database for sql's which could not be applied. Some common errors are primary key violations and no data found errors.
Usually in 1 way replication , this should never happen but being a creature of habit i just put this everywhere.
I will show you later how cool this feature is for trapping exceptions.

At the target database, you should have a golden gate admin schema called GGDDLUSR(same as in source database too).

CREATE TABLE EXCEPTIONS
(
REP_NAME VARCHAR2(8 BYTE),
TABLE_NAME VARCHAR2(61 BYTE),
ERRNO NUMBER,
DBERRMSG VARCHAR2(4000 BYTE),
OPTYPE VARCHAR2(20 BYTE),
ERRTYPE VARCHAR2(20 BYTE),
LOGRBA NUMBER,
LOGPOSITION NUMBER,
TRAILRBA NUMBER,
TRAILSEQUENCE NUMBER,
COMMITTIMESTAMP TIMESTAMP(6)
);

ALTER TABLE EXCEPTIONS ADD CONSTRAINT PK_EXCEPTIONS
PRIMARY KEY (TRAILRBA,TRAILSEQUENCE,COMMITTIMESTAMP)
/

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.