Random Oracle Posts

Resolving Gaps in Data Guard Apply Using Incremental RMAN BACKUP

2019-12-03T14:35:00.000-05:00

Arup Nanda had written a blogpost on this in 2009 and I highly recommend all of you read this.

http://arup.blogspot.com/2009/12/resolving-gaps-in-data-guard-apply.html

The basic technique of using SCN of dataguard and taking backup from primary is well documented in his blog and I am not going to repeat that.

Unfortunately the blog was written using normal filesystems and did not take into account about ASM with oracle managed files .

Example is production might have all the files under

+DATA/<prod db name>/DATAFILE

whereas dataguard will have files under

+DATA/<DR db name>/DATAFILE

Since the path do NOT match , you will have to tweak his technique a bit.

In this example , I will put in my perspective of how to fix this part.

PLEASE NOTE TO REFER TO ARUP NANDA'S ORIGINAL BLOG FOR THE RECOVERY PORTION USING RMAN.

PRIMARY
++++++

oracle@prodhost orclpr1 trace: sqlplus '/ as sysdba'

SQL*Plus: Release 12.1.0.2.0 Production on Tue Mar 7 10:21:58 2017

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> alter database create standby controlfile as '/tmp/control01.ctl';

Database altered.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
oracle@prodhost orclpr1 trace: scp /tmp/control01.ctl gchandra@drhost:/tmp/.
This computer system is for authorized users only. Unauthorized users are
prohibited. Users (authorized or unauthorized) have no explicit or implicit
expectation of privacy. Discontinue access immediately if you do not agree to
the conditions stated in this notice
Password:
control01.ctl 100% 23MB 23.3MB/s 00:01

Data Guard
++++++++++

[root@drhost AKA ws-dr-bmodb01 tmp]# chown oracle:oinstall control01.ctl

oracle@drhost orcldr1 trace: srvctl stop database -d orcldr
oracle@drhost orcldr1 trace: sqlplus '/ as sysdba'

SQL*Plus: Release 12.1.0.2.0 Production on Tue Mar 7 10:26:28 2017

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

starConnected to an idle instance.

SQL> tup nomount
ORACLE instance started.

Total System Global Area 4731174912 bytes
Fixed Size 4506744 bytes
Variable Size 1291848584 bytes
Database Buffers 3422552064 bytes
Redo Buffers 12267520 bytes
SQL> exit

oracle@drhost orcldr1 trace: rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Tue Mar 7 10:26:49 2017

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

connected to target database: FI9219PR (not mounted)

RMAN> restore controlfile from '/tmp/control01.ctl';

Starting restore at 07-MAR-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=774 instance=orcldr1 device type=DISK

channel ORA_DISK_1: copied control file copy
output file name=+DATA/orcldr/control01.ctl
Finished restore at 07-MAR-17

RMAN> sql 'alter database mount';

sql statement: alter database mount
released channel: ORA_DISK_1

RMAN> catalog start with '+data/orcldr/datafile';

Starting implicit crosscheck backup at 07-MAR-17
allocated channel: ORA_DISK_1
Finished implicit crosscheck backup at 07-MAR-17

Starting implicit crosscheck copy at 07-MAR-17
using channel ORA_DISK_1
Finished implicit crosscheck copy at 07-MAR-17

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: +RECO/orcldr/ARCHIVELOG/2017_03_07/thread_1_seq_793.2753.937994585
File Name: +RECO/orcldr/ARCHIVELOG/2017_03_07/thread_2_seq_714.5482.937994587
File Name: +RECO/orcldr/ARCHIVELOG/2017_03_07/thread_2_seq_715.672.937994749
File Name: +RECO/orcldr/ARCHIVELOG/2017_03_07/thread_1_seq_794.2341.937994749Production

searching for all files that match the pattern +data/orcldr/datafile

List of Files Unknown to the Database
=====================================
File Name: +DATA/orcldr/DATAFILE/system.468.929733757
File Name: +DATA/orcldr/DATAFILE/sysaux.469.929733759
File Name: +DATA/orcldr/DATAFILE/undotbs2.470.929733765
File Name: +DATA/orcldr/DATAFILE/admin.471.929733767
File Name: +DATA/orcldr/DATAFILE/archive.472.929733769
File Name: +DATA/orcldr/DATAFILE/hubload.473.929733771
File Name: +DATA/orcldr/DATAFILE/hubsys.474.929733773
File Name: +DATA/orcldr/DATAFILE/intplat.475.929733779
File Name: +DATA/orcldr/DATAFILE/keystore.476.929733781
File Name: +DATA/orcldr/DATAFILE/undotbs1.477.929733787
File Name: +DATA/orcldr/DATAFILE/users.478.929733787
File Name: +DATA/orcldr/DATAFILE/webinit.479.929733789
File Name: +DATA/orcldr/DATAFILE/webrpt.480.929733789
File Name: +DATA/orcldr/DATAFILE/websys.481.929733797
File Name: +DATA/orcldr/DATAFILE/wpdata.482.929733797
File Name: +DATA/orcldr/DATAFILE/system.536.937132397
File Name: +DATA/orcldr/DATAFILE/sysaux.537.937132397
File Name: +DATA/orcldr/DATAFILE/undotbs2.538.937132413
File Name: +DATA/orcldr/DATAFILE/admin.539.937132417
File Name: +DATA/orcldr/DATAFILE/archive.540.937132425
File Name: +DATA/orcldr/DATAFILE/hubload.541.937132431
File Name: +DATA/orcldr/DATAFILE/hubsys.542.937132433
File Name: +DATA/orcldr/DATAFILE/intplat.543.937132441
File Name: +DATA/orcldr/DATAFILE/keystore.544.937132441
File Name: +DATA/orcldr/DATAFILE/undotbs1.545.937132449
File Name: +DATA/orcldr/DATAFILE/users.546.937132449
File Name: +DATA/orcldr/DATAFILE/webinit.547.937132453
File Name: +DATA/orcldr/DATAFILE/webrpt.548.937132453
File Name: +DATA/orcldr/DATAFILE/websys.549.937132461
File Name: +DATA/orcldr/DATAFILE/wpdata.550.937132461

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: +DATA/orcldr/DATAFILE/system.468.929733757
File Name: +DATA/orcldr/DATAFILE/sysaux.469.929733759
File Name: +DATA/orcldr/DATAFILE/undotbs2.470.929733765
File Name: +DATA/orcldr/DATAFILE/admin.471.929733767
File Name: +DATA/orcldr/DATAFILE/archive.472.929733769
File Name: +DATA/orcldr/DATAFILE/hubload.473.929733771
File Name: +DATA/orcldr/DATAFILE/hubsys.474.929733773
File Name: +DATA/orcldr/DATAFILE/intplat.475.929733779
File Name: +DATA/orcldr/DATAFILE/keystore.476.929733781
File Name: +DATA/orcldr/DATAFILE/undotbs1.477.929733787
File Name: +DATA/orcldr/DATAFILE/users.478.929733787
File Name: +DATA/orcldr/DATAFILE/webinit.479.929733789
File Name: +DATA/orcldr/DATAFILE/webrpt.480.929733789
File Name: +DATA/orcldr/DATAFILE/websys.481.929733797
File Name: +DATA/orcldr/DATAFILE/wpdata.482.929733797
File Name: +DATA/orcldr/DATAFILE/system.536.937132397
File Name: +DATA/orcldr/DATAFILE/sysaux.537.937132397
File Name: +DATA/orcldr/DATAFILE/undotbs2.538.937132413
File Name: +DATA/orcldr/DATAFILE/admin.539.937132417
File Name: +DATA/orcldr/DATAFILE/archive.540.937132425
File Name: +DATA/orcldr/DATAFILE/hubload.541.937132431
File Name: +DATA/orcldr/DATAFILE/hubsys.542.937132433
File Name: +DATA/orcldr/DATAFILE/intplat.543.937132441
File Name: +DATA/orcldr/DATAFILE/keystore.544.937132441
File Name: +DATA/orcldr/DATAFILE/undotbs1.545.937132449
File Name: +DATA/orcldr/DATAFILE/users.546.937132449
File Name: +DATA/orcldr/DATAFILE/webinit.547.937132453
File Name: +DATA/orcldr/DATAFILE/webrpt.548.937132453
File Name: +DATA/orcldr/DATAFILE/websys.549.937132461
File Name: +DATA/orcldr/DATAFILE/wpdata.550.937132461

RMAN> switch database to copy;

datafile 1 switched to datafile copy "+DATA/orcldr/DATAFILE/system.536.937132397"
datafile 2 switched to datafile copy "+DATA/orcldr/DATAFILE/sysaux.537.937132397"
datafile 3 switched to datafile copy "+DATA/orcldr/DATAFILE/undotbs2.538.937132413"
datafile 4 switched to datafile copy "+DATA/orcldr/DATAFILE/admin.539.937132417"
datafile 5 switched to datafile copy "+DATA/orcldr/DATAFILE/archive.540.937132425"
datafile 6 switched to datafile copy "+DATA/orcldr/DATAFILE/hubload.541.937132431"
datafile 7 switched to datafile copy "+DATA/orcldr/DATAFILE/hubsys.542.937132433"
datafile 8 switched to datafile copy "+DATA/orcldr/DATAFILE/intplat.543.937132441"
datafile 9 switched to datafile copy "+DATA/orcldr/DATAFILE/keystore.544.937132441"
datafile 10 switched to datafile copy "+DATA/orcldr/DATAFILE/undotbs1.545.937132449"
datafile 11 switched to datafile copy "+DATA/orcldr/DATAFILE/users.546.937132449"
datafile 12 switched to datafile copy "+DATA/orcldr/DATAFILE/webinit.547.937132453"
datafile 13 switched to datafile copy "+DATA/orcldr/DATAFILE/webrpt.548.937132453"
datafile 14 switched to datafile copy "+DATA/orcldr/DATAFILE/websys.549.937132461"
datafile 15 switched to datafile copy "+DATA/orcldr/DATAFILE/wpdata.550.937132461"

RMAN> exit

Recovery Manager complete.

oracle@drhost orcldr1 trace: sqlplus '/ as sysdba'

SQL*Plus: Release 12.1.0.2.0 Production on Tue Mar 7 10:30:15 2017

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> shutdown immediate
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.

NOW WE HAVE SUCESSFULLY GOT THE STANDBY CONTROLFILE FROM PRODUCTION INTO DATAGUARD AND SUCESSFULLY CHANGED THE PATH OF ALL THE FILE NAMES.

Once the above is done, you can start from step 10 in his blog .

Note to confused myself
++++++++++++++++++

You might be wondering what hapenned to the redo logs under +DATA/orcldr/onlinelog directory as we never renamed them but oracle will automatically clear them up for all the groups once you open the database.

example of alert log -

ORA-17503: ksfdopn:2 Failed to open file +DATA/FI9219DR/onlinelog/group_3.414.929526809

ORA-15173: entry 'group_3.414.929526809' does not exist in directory 'onlinelog'

Deleted Oracle managed file +DATA/orcldr/onlinelog/group_3.414.929526809

Deleted Oracle managed file +RECO/orcldr/onlinelog/group_3.562.929526811

Clearing online redo logfile 3 complete

GoldenGate Password Encryption

2019-12-03T14:09:00.003-05:00

The goldengate admin user is GGADMIN and password is secretpassword.

GET ENCRYPTED PASSWORD for "secretpassword"

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

GGSCI (homelinux.local) 1> encrypt password secretpassword,encryptkey default

Using Blowfish encryption with DEFAULT key.

Encrypted password: AACAAAAAAAAAAAMALFJHGAYIMJSFLYTVDHBLHDIEHVJSFSJVD

Algorithm used: BLOWFISH

VERIFY MANUALLY USING THE ENCRYPTED PASSWORD GENERATED FROM ABOVE

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

dblogin userid ggadmin@ORCL, password AACAAAAAAAAAAAMALFJHGAYIMJSFLYTVDHBLHDIEHVJSFSJVD

CHANGE THE CLEARTEXT PASSWORD IN YOUR PARAMETER FILE TO ENCRYPTED

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

The cleartext password

userid ggadmin@ORCL, password secretpassword

can now be changed to

userid ggadmin@ORCL, password AACAAAAAAAAAAAMALFJHGAYIMJSFLYTVDHBLHDIEHVJSFSJVD, ENCRYPTKEY DEFAULT

Oracle export with where clause and parallelism

2019-12-03T14:01:00.001-05:00

This pretty much sums up the way to export tables with a where clause.

export DATA_PUMP_DIR="EXPORT_DIR"

expdp \'/ as sysdba\' tables=scott.emp query=scott.emp:'"where eno <>0"' dumpfile=emp%U.dmp parallel=5 logfile=emp.log compression=all

parallelism is NOT mandatory and can be skipped

The above is put into a shell script and can be run from the unix prompt.
Tested only on UNIX systems.

Goldengate ALLOWNOOPUPDATES error - Why it happens ?

2014-08-09T21:32:00.002-04:00

Before you read this article , you might want to read my other 3 blogs -
Goldengate supplemental logging , keycols, logallsupcols testcase 1/2/3

As I have explained , an update on the source can either trigger a GGSPKUpdate or a FieldComp record on the trail file.

Now ALLOWNOOPUPDATES is specific to FieldComp update.

A usual update statement consists of

update <table name> set <set clause> where <where clause> ;

The error happens when a replicat tries to construct the 'set clause' from a trail record after it has successfully constructed the 'where clause' .

Again as explained in the earlier blog, if the trail file does not have the column information which replicat needs (based on PK/UK on the target table or KEYCOLS in replicat parameter table) then the "where clause" construction part of the update will itself fail. All those failures are documented in the other blogs mentioned above.

Considering that it has gone past that part successfully, it has to construct the "set clause" of the update statement.

Usually if a particular column is picked by where clause that column should be skipped from set clause.

When replicat sees that all the columns in the trail file are used by the where clause then it reaches a condition that there are no more columns left to construct the "set clause". This is exactly the reason why we encounter a ALLOWNOOPUPDATES.

Let me start with a simple example with logdump views to explain more clearly.

1.Create a table in Source and make sure a PK is built. A PK is needed to make sure any update goes as FieldComp.

SQL> create table junk(col1 number ,col2 number,col3 number,col4 number,col5 num
ber,col6 number)
2 /

Table created.

SQL> alter table junk add constraint pk_junk primary key(col1)
2 /

Table altered.

2.Make sure you add supplemental logging for ALL Columns -

GGSCI (titan.gautham.com) 239> add trandata scott.junk, allcols

Logging of supplemental redo data enabled for table SCOTT.JUNK.
TRANDATA for scheduling columns has been added on table 'SCOTT.JUNK'.TRANDATA for all columns has been added on table 'SCOTT.JUNK'.
GGSCI (titan.gautham.com) 240>

GGSCI (titan.gautham.com) 240> info trandata scott.junk

Logging of supplemental redo log data is enabled for table SCOTT.JUNK.

Columns supplementally logged for table SCOTT.JUNK: ALL.

3.Ensure the table has arrived on target and drop the PK on the target .

The reason why we drop the PK on target is, that when replicat tries to construct the 'where clause' of the update, we want all the columns in the trail file to be used so that nothing is left for the 'set clause'

SQL> desc junk
Name Null? Type
----------------------------------------- -------- ----------------------------

COL1 NOT NULL NUMBER
COL2 NUMBER
COL3 NUMBER
COL4 NUMBER
COL5 NUMBER
COL6 NUMBER

SQL> alter table junk drop constraint pk_junk
2 /

Table altered.

SQL> desc junk
Name Null? Type
----------------------------------------- -------- ----------------------------

COL1 NUMBER
COL2 NUMBER
COL3 NUMBER
COL4 NUMBER
COL5 NUMBER
COL6 NUMBER

SQL>

4.Insert a record in the source and ensure record arrives in target

--source
SQL> insert into junk values(100,100,100,100,100,100);

1 row created.

SQL> commit;

Commit complete.

--target
SQL> select * from junk;

COL1 COL2 COL3 COL4 COL5 COL6
---------- ---------- ---------- ---------- ---------- ----------
100 100 100 100 100 100

We are not interested in seeing logdump for above insert as it does not serve us any purpose

5.Update the source -

SQL> update junk set col5=500 where col1=100;

1 row updated.

SQL> commit;

Commit complete.

Check the replicat -

GGSCI (titan.gautham.com) 242> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING
JAGENT STOPPED
EXTRACT RUNNING xxxxx 00:00:09 00:00:01
REPLICAT ABENDED yyyy 00:00:00 00:00:25

Check the error from ggserr.log

2014-08-09 21:28:51 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): info all.
2014-08-09 21:29:08 ERROR OGG-01168 Oracle GoldenGate Delivery for Oracle, rphvn1d.prm: Encountered an update for target table SCOTT1.JUNK, which has no unique key defined. KEYCOLS can be used to define a key. Use ALLOWNOOPUPDATES to process the update without applying it to the target database. Use APPLYNOOPUPDATES to force the update to be applied using all columns in both the SET and WHERE clause.
2014-08-09 21:29:17 ERROR OGG-01668 Oracle GoldenGate Delivery for Oracle, rphvn1d.prm: PROCESS ABENDING.

I hope the above explanation is clear.

Obviously if we create a PK or UK on the target or use KEYCOLS in replicat , we should be able to start the replicat with no issues.

Part 3 - Goldengate supplemental logging , keycols, logallsupcols - With Final Comments

2014-08-09T17:24:00.001-04:00

Same as in testcase 1 i.e. NO PK's on source or target, no LOGALLSUPCOLS in extract, no KEYCOLS in replicat except that extract now has KEYCOLS defined on col1 .

Extract Parameter file--------------------------------

--LOGALLSUPCOLS -->commented out
GETTRUNCATES
table SCOTT.junk, keycols(col1);

Replicat Paramer file------------------------------

Please don't get confused with the mapexception part as it is the exception handling routine I always add in dev/qa to trap unique key violated and no data found.
This has no meaning in this experiment.

DDL INCLUDE MAPPED &
EXCLUDE OBJNAME GGADM.EXCEPTIONS
..
ASSUMETARGETDEFS
GETTRUNCATES
REPERROR (-1, EXCEPTION)
REPERROR (-1403, EXCEPTION)

map scott.junk, target scott1.junk, mapexception (TARGET ggadm.exceptions
, INSERTALLRECORDS
-- , EXCEPTIONSONLY
, colmap ( rep_name = 'xxxxx'
, 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')

) );

As always create table, add supplemental logging on col1,col2,col3 and insert a row on source and ensure that travelled to target.
Please see this relevant portion from test case 1.

Now the important update
SQL> update junk set col4=500 where col1=100;

1 row updated.

SQL> commit;

And here is the record from logdump in trail file

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

Logdump 781 >n
___________________________________________________________________
Hdr-Ind : E (x45) Partition : . (x04)
UndoFlag : . (x00) BeforeAfter: A (x41)
RecLength : 44 (x002c) IO Time : 2014/08/09 16:57:33.000.000
IOType : 15 (x0f) OrigNode : 255 (xff)
TransInd : . (x03) FormatType : R (x52)
SyskeyLen : 0 (x00) Incomplete : . (x00)
AuditRBA : 10578 AuditPos : 245809680
Continued : N (x00) RecCount : 1 (x01)

2014/08/09 16:57:33.000.000 FieldComp Len 44 RBA 5876
Name: SCOTT.JUNK
After Image: Partition 4 G s
0000 0007 0000 0003 3130 3000 0100 0700 0000 0331 | ........100........1
3030 0002 0007 0000 0003 3130 3000 0300 0700 0000 | 00........100.......
0335 3030 | .500
Column 0 (x0000), Len 7 (x0007)
0000 0003 3130 30 | ....100
Column 1 (x0001), Len 7 (x0007)
0000 0003 3130 30 | ....100
Column 2 (x0002), Len 7 (x0007)
0000 0003 3130 30 | ....100
Column 3 (x0003), Len 7 (x0007)
0000 0003 3530 30 | ....500

GGS tokens:
TokenID x52 'R' ORAROWID Info x00 Length 20
4141 4155 476a 4141 4641 4141 414b 3141 4141 0001 | AAAUGjAAFAAAAK1AAA..
TokenID x4c 'L' LOGCSN Info x00 Length 9
3539 3131 3131 3537 31 | 591111571
TokenID x36 '6' TRANID Info x00 Length 12

3231 2e33 332e 3138 3830 3833 | 21.33.188083

See the huge difference that KEYCOL made ?
In test case 1, we saw a GGSPKUpdate whereas now we are seeing a FieldComp.
remember supplemental logging was done on col1,col2 and col3 and KEYCOLS had col1.

Replicat has the same abort issue .

2014-08-09 16:57:14 WARNING OGG-06439 No unique key is defined for table JUNK. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
Using following columns in default map by name:
COL1, COL2, COL3, COL4, COL5, COL6

Using the following key columns for target table SCOTT1.JUNK: COL1, COL2, COL3, COL4, COL5, COL6.

This is because since the target has no PK/UK or KEYCOLS , the replicat wants to set all the columns in the where condition of the update but it sees only col1, col2, col3, col4. therefore it fails.

The resolving is simple, just add primary key on either or all of columns - col1, col2,col3 and col4 and you are set.
SQL> alter table junk add constraint pk_junk primary key(col2);

Table altered.

--restart replicat and see the magic

SQL> /

COL1 COL2 COL3 COL4 COL5 COL6

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

100 100 100 500 100 100

I am going to redo the same experiment above but I am going to set KEYCOLS to a column which is not logged by supplemental logging and then going to update an totally different column.

1.Set supplemental logging on col1, col2, col3

2.Set KEYCOLS on col4 on extract side

i.e. table SCOTT.junk, keycols(col4);

3.Update a column say col5 not defined in 1,2.

Update junk set col5=500 where col1=100;

SQL> create table junk(col1 number,col2 number,col3 number,col4 number,col5 numb

er,col6 number);

Table created.

add trandata scott.junk, cols(col1,col2,col3),nokey

SQL> insert into junk values(100,100,100,100,100,100);

1 row created.

SQL> commit;

Commit complete.

SQL> update junk set col5=500 where col1=100;

1 row updated.

SQL> commit;

Commit complete.

logdump as always
---------------------

Logdump 794 >n

___________________________________________________________________

Hdr-Ind : E (x45) Partition : . (x04)

UndoFlag : . (x00) BeforeAfter: A (x41)

RecLength : 44 (x002c) IO Time : 2014/08/09 17:17:47.000.000

IOType : 15 (x0f) OrigNode : 255 (xff)

TransInd : . (x03) FormatType : R (x52)

SyskeyLen : 0 (x00) Incomplete : . (x00)

AuditRBA : 10578 AuditPos : 248909840

Continued : N (x00) RecCount : 1 (x01)

2014/08/09 17:17:47.000.000 FieldComp Len 44 RBA 3031

Name: SCOTT.JUNK

After Image: Partition 4 G s

0000 0007 0000 0003 3130 3000 0100 0700 0000 0331 | ........100........1

3030 0002 0007 0000 0003 3130 3000 0400 0700 0000 | 00........100.......

0335 3030 | .500

Column 0 (x0000), Len 7 (x0007)

0000 0003 3130 30 | ....100

Column 1 (x0001), Len 7 (x0007)

0000 0003 3130 30 | ....100

Column 2 (x0002), Len 7 (x0007)

0000 0003 3130 30 | ....100

Column 4 (x0004), Len 7 (x0007)

0000 0003 3530 30 | ....500

GGS tokens:

TokenID x52 'R' ORAROWID Info x00 Length 20

4141 4155 476e 4141 4641 4141 414c 4e41 4141 0001 | AAAUGnAAFAAAALNAAA..

TokenID x4c 'L' LOGCSN Info x00 Length 9

3539 3131 3133 3834 32 | 591113842

TokenID x36 '6' TRANID Info x00 Length 12

3231 2e33 302e 3138 3735 3938 | 21.30.187598

Very Interesting !!!!

Because KEYCOLs is mentioned in the parameter file , that is why we see a FieldComp instead of GGSPKUpdate operation (otherwise I am sure we would see a GGSPKUpdate in the absence of KEYCOLS) but unfortunately col4 which is set in KEYCOLS is missing in the

trail file as the supplemental logging only covered col1, col2 and col3 and offcourse we see col5 as that was the row updated.
So please, please ensure that your supplemental logging covers the KEYCOLS.

Also KEYCOLS or PK/UK decide whether the update would be a FieldComp vs GGSPKUpdate.

As always we see REPLICAT abended in target and i guess you all know what to do (if you had been reading my previous testcases).

Here are the golden rules on how the target system constructs the where condition of the target
based on whether it encounters a FieldComp or GGSPKUpdate

GGSPKUpdate

GGSPKUpdate has both before and after images in same record.
It is created only if source has no PK/UK nor does it have KEYCOLS defined on extract.

Rules - (1)If The target should have PK/UK or KEYCOLS defined in replicat - it will use those columns
for where condition(If both of them are available I am assuming KEYCOLS takes precedence over PK/UK)
Then the before image in the GGSPKUpdate should have those columns set. Otherwise replicat will bomb as it does not have enough information in trail file to construct the where clause.

(2)If the target has no PK/UK nor does it have KEYCOLS defined in replicat -
The before image of GGSPKUpdate should have all the table columns . Otherwise replicat will bomb as it does not have enough information in trail file to construct the where clause.

FieldComp

FieldComp has no before image. It has only after image as basic assumption is PK is not changed.It is created only if source has a PK/UK or has KEYCOLS defined in extract parameter file.

Rules -

(1)If The target should have PK/UK or KEYCOLS defined in replicat - it will use those columns for where condition.
If both of them are available I am assuming KEYCOLS takes precedence over PK/UK.
Now the afterimage should cover those columns.If not it will bomb.
If it covers, then it will use them in the where condition and use the rest of the columns in set.

(2)If the target has no PK/UK nor does it have KEYCOLS defined in replicat -
The after image of FieldComp should have all the table columns . Otherwise replicat will bomb.
Assuming after image has all columns , then it uses all those columns for where condition and since it cannot reuse the columns in set, the update effectively becomes

update <table name> set <nothing> where <column list from goldengate>;

When the above case is encountered, we get the APPLYNOOPUPDATES error.

2014-08-09 20:58:36 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): info all.
2014-08-09 20:58:58 ERROR OGG-01168 Oracle GoldenGate Delivery for Oracle, rphvn1d.prm: Encountered an update for target table SCOTT1.JUNK, which has no unique key defined. KEYCOLS can be used to define a key. Use ALLOWNOOPUPDATES to process the update without applying it to the target database. Use APPLYNOOPUPDATES to force the update to be applied using all columns in both the SET and WHERE clause.

2014-08-09 20:59:08 ERROR OGG-01668 Oracle GoldenGate Delivery for Oracle, rphvn1d.prm: PROCESS ABENDING.

Part 2 - Goldengate supplemental logging , keycols, logallsupcols testcase

2014-08-09T16:26:00.003-04:00

This is in continuation to my series of findings and I recommend that you start from the first post
http://gauthamc.blogspot.com/2014/08/goldengate-supplemental-logging-keycols.html to understand my environment much better.

TEST CASE 2
==========
The test case is pretty similar to test case 1 except that I have modified to extract parameter to add
LOGALLSUPCOLS.

Otherwise the setup is exactly the same as test case 1.

You might want to read more about LOGALLSUPCOLS (extract parameter) which is GG 12c feature and seems to be mandatory if you are using INTEGRATED replicat (again a 12c feature) with parallelism >1 (which should be true in most cases). Here we are going to look at what changes in the trail file when this parameter is set .

So here it goes again, the same steps as in test case 1.

--extract parameter file
..
..

DDL include Mapped
LOGALLSUPCOLS
table SCOTT.junk;

sequence SCOTT.*;
--end of extract parameter file

--create table in source with no PK/UK defined
SQL> create table junk(col1 number,col2 number,col3 number,col4 number,col5 numb
er,col6 number);

Table created.

--make sure the table DDL has moved to target
SQL> select * from junk;

no rows selected

--set supplemental logging for col1,col2 and col3

GGSCI (titan.gautham.com) 140> add trandata scott.junk, cols(col1,col2,col3),nokey

2014-08-09 15:52:09 WARNING OGG-06439 No unique key is defined for table JUNK. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.

Logging of supplemental redo data enabled for table SCOTT.JUNK.

TRANDATA for scheduling columns has been added on table 'SCOTT.JUNK'.

--verify

GGSCI (titan.gautham.com) 142> info trandata scott.junk

Logging of supplemental redo log data is enabled for table SCOTT.JUNK.

Columns supplementally logged for table SCOTT.JUNK: COL1, COL2, COL3.

--perform an insert on source
SQL> insert into junk values(100,100,100,100,100,100);

1 row created.

SQL> commit;

Commit complete.

--verify insert has travelled to target
SQL> select * from junk;

COL1 COL2 COL3 COL4 COL5 COL6
---------- ---------- ---------- ---------- ---------- ----------

100 100 100 100 100 100

--perform an update on the source of col4 .Remember only col1,col2 and col3 is added for supplemental logging

SQL> update junk set col4=500 where col1=100;

1 row updated.

SQL> commit;

Commit complete.

Look at the trailfile from logdump for the update
--------------------------------------------------------------------
The interesting thing is you will see 2 records in the trail file for the above update.
You will see a FieldComp (before image) followed by a GGSPKUpdate(before/after image).

It does look like the  LOGALLSUPCOLS is responsible for the 1st FieldComp record .
With my interactions with Oracle Support and update operation always appears as either a FieldComp or GGSPKUpdate(when there is a PK update).In most practical situations where a table always has a PK and logging is setup either on the PK or all columns, I have only seen a FieldComp when an update operation takes place.
I am assuming that if the source had a PK or KEYCOLS defined in parameter file(assuming supplemental logging covered the PK or the KEYCOLS), we would see the GGSPKUpdate replaced with a FieldComp .That will be my test case 3.

Logdump 752 >n
___________________________________________________________________
Hdr-Ind : E (x45) Partition : . (x04)
UndoFlag : . (x00) BeforeAfter: B (x42)
RecLength : 44 (x002c) IO Time : 2014/08/09 16:17:18.000.000
IOType : 15 (x0f) OrigNode : 255 (xff)
TransInd : . (x00) FormatType : R (x52)
SyskeyLen : 0 (x00) Incomplete : . (x00)
AuditRBA : 10578 AuditPos : 243605008
Continued : N (x00) RecCount : 1 (x01)

2014/08/09 16:17:18.000.000 FieldComp Len 44 RBA 8711
Name: SCOTT.JUNK
Before Image: Partition 4 G b
0000 0007 0000 0003 3130 3000 0100 0700 0000 0331 | ........100........1
3030 0002 0007 0000 0003 3130 3000 0300 0700 0000 | 00........100.......
0331 3030 | .100
Column 0 (x0000), Len 7 (x0007)
0000 0003 3130 30 | ....100
Column 1 (x0001), Len 7 (x0007)
0000 0003 3130 30 | ....100
Column 2 (x0002), Len 7 (x0007)
0000 0003 3130 30 | ....100
Column 3 (x0003), Len 7 (x0007)
0000 0003 3130 30 | ....100

GGS tokens:
TokenID x52 'R' ORAROWID Info x00 Length 20
4141 4155 4763 4141 4641 4141 414b 4641 4141 0001 | AAAUGcAAFAAAAKFAAA..
TokenID x4c 'L' LOGCSN Info x00 Length 9
3539 3131 3038 3032 37 | 591108027
TokenID x36 '6' TRANID Info x00 Length 12
3130 2e32 392e 3637 3134 3533 | 10.29.671453

Important findings from the above record -
As expected we see col1,col2,col3 records because of supplemental logging.
Looks like since we updated col4 (4th column of the table or column 3 in GG(as GG numbers column from 0),
it stores the before image of col4 which is 100

Logdump 753 >n
___________________________________________________________________
Hdr-Ind : E (x45) Partition : . (x04)
UndoFlag : . (x00) BeforeAfter: A (x41)
RecLength : 90 (x005a) IO Time : 2014/08/09 16:17:18.000.000
IOType : 115 (x73) OrigNode : 255 (xff)
TransInd : . (x02) FormatType : R (x52)
SyskeyLen : 0 (x00) Incomplete : . (x00)
AuditRBA : 10578 AuditPos : 243605008
Continued : N (x00) RecCount : 1 (x01)

2014/08/09 16:17:18.000.000 GGSPKUpdate Len 90 RBA 8873
Name: SCOTT.JUNK
After Image: Partition 4 G e
002c 0000 0007 0000 0003 3130 3000 0100 0700 0000 | .,........100.......
0331 3030 0002 0007 0000 0003 3130 3000 0300 0700 | .100........100.....
0000 0331 3030 0000 0007 0000 0003 3130 3000 0100 | ...100........100...
0700 0000 0331 3030 0002 0007 0000 0003 3130 3000 | .....100........100.
0300 0700 0000 0335 3030 | .......500
Before Image Len 46 (x0000002e)
KeyLen 44 (x0000002c)
KeyCol 0 (x0000), Len 7 (x0007)
0000 0003 3130 30 | ....100
KeyCol 1 (x0001), Len 7 (x0007)
0000 0003 3130 30 | ....100
KeyCol 2 (x0002), Len 7 (x0007)
0000 0003 3130 30 | ....100
KeyCol 3 (x0003), Len 7 (x0007)
0000 0003 3130 30 | ....100

After Image Len 44 (x0000002c)
Column 0 (x0000), Len 7 (x0007)
0000 0003 3130 30 | ....100
Column 1 (x0001), Len 7 (x0007)
0000 0003 3130 30 | ....100
Column 2 (x0002), Len 7 (x0007)
0000 0003 3130 30 | ....100
Column 3 (x0003), Len 7 (x0007)
0000 0003 3530 30 | ....500

GGS tokens:
TokenID x52 'R' ORAROWID Info x00 Length 20
4141 4155 4763 4141 4641 4141 414b 4641 4141 0001 | AAAUGcAAFAAAAKFAAA..

As with testcase 1, the replicat was broken here too -
REPLICAT ABENDED RPHVN1D 00:00:00 00:02:35

report file
---------------
...
...
2014-08-09 16:16:54 WARNING OGG-06439 No unique key is defined for table JUNK. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
Using following columns in default map by name:
COL1, COL2, COL3, COL4, COL5, COL6

Using the following key columns for target table SCOTT1.JUNK: COL1, COL2, COL3, COL4, COL5, COL6.
..
..

It does seem like we had the same abort as testcase 1 and we can fix this issue either by putting KEYCOLS on the replicat parameter file or just adding a PK(on target table) on one of the columns of col1, col2,col3 and col4.

Just for curiousity, instead of adding a PK on col1 or col2 or col3 (unchanged), let me add the PK on the changed column col4.

SQL> alter table junk add constraint pk_junk primary key(col4);

Table altered.

--restart the replicat and check the target

SQL> select * from junk;

COL1 COL2 COL3 COL4 COL5 COL6
---------- ---------- ---------- ---------- ---------- ----------

100 100 100 500 100 100

Looks like we are all good now.

Part 1 - Goldengate supplemental logging , keycols, logallsupcols testcase

2014-08-09T13:50:00.003-04:00

My goldengate set up is a 11.2.0.4 oracle database on a goldengate 12c .
I am running a series of tests to understand how goldengate writes data into the trail file and how replicat behaves based on supplemental logging (all columns vs few columns), no PK/UK defined on the tables, effect of keycols in the extract and replicat parameter file, LOGALLSUPCOLS(a 12c GG parameter).

I hope to document a bunch of them and will be writing multiple testcases.

TEST CASE 1
=============

Table has no PK or unique key defined on both source and target and table structure is identical.
Supplemental logging is done for 3 columns out of 6 columns.
Extract has no KEYCOLS defined.
EXTRACT has no LOGALLSUPCOLS.
replicat has no KEYCOLS defined.

SQL> create table junk(col1 number,col2 number,col3 number,col4 number,col5 numb
er,col6 number)
2 /

Table created.

GGSCI (titan.gautham.com) 119> add trandata scott.junk, cols(col1,col2,col3),nokey

2014-08-09 13:07:44 WARNING OGG-06439 No unique key is defined for table JUNK. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.

Logging of supplemental redo data enabled for table SCOTT.JUNK.
TRANDATA for scheduling columns has been added on table 'SCOTT.JUNK'.
GGSCI (titan.gautham.com) 120> info trandata scott.junk

Logging of supplemental redo log data is enabled for table SCOTT.JUNK.

Columns supplementally logged for table SCOTT.JUNK: COL1, COL2, COL3.

Extract parameter file

..
--LOGALLSUPCOLS -->commented out
table SCOTT.junk; -->no keycols mentioned here

Perform the following from sqlplus at the source-
SQL> insert into junk values(100,100,100,100,100,100);

1 row created.

SQL> commit;

Commit complete.

SQL> update junk set col4=500 where col1=100;

1 row updated.

SQL> commit;

Commit complete.

The below logdump portion is relevant only to the update statement .I have not included the logdump portion for the insert as it does not serve us any purpose for this discussion.

logdump of trail file

1.Has info on col1, col2,col3 (as they are added in add trandata)
2.Has info on col4 as it was changed
3.shows GGSPKUpdate (for the update) with before and after image

Logdump 692 >n
___________________________________________________________________
Hdr-Ind : E (x45) Partition : . (x04)
UndoFlag : . (x00) BeforeAfter: A (x41)
RecLength : 90 (x005a) IO Time : 2014/08/09 13:23:00.000.000
IOType : 115 (x73) OrigNode : 255 (xff)
TransInd : . (x03) FormatType : R (x52)
SyskeyLen : 0 (x00) Incomplete : . (x00)
AuditRBA : 10578 AuditPos : 230433296
Continued : N (x00) RecCount : 1 (x01)

2014/08/09 13:23:00.000.000 GGSPKUpdate Len 90 RBA 4260
Name: SCOTT.JUNK
After Image: Partition 4 G s
002c 0000 0007 0000 0003 3130 3000 0100 0700 0000 | .,........100.......
0331 3030 0002 0007 0000 0003 3130 3000 0300 0700 | .100........100.....
0000 0331 3030 0000 0007 0000 0003 3130 3000 0100 | ...100........100...
0700 0000 0331 3030 0002 0007 0000 0003 3130 3000 | .....100........100.
0300 0700 0000 0335 3030 | .......500
Before Image Len 46 (x0000002e)
KeyLen 44 (x0000002c)
KeyCol 0 (x0000), Len 7 (x0007)
0000 0003 3130 30 | ....100
KeyCol 1 (x0001), Len 7 (x0007)
0000 0003 3130 30 | ....100
KeyCol 2 (x0002), Len 7 (x0007)
0000 0003 3130 30 | ....100
KeyCol 3 (x0003), Len 7 (x0007)
0000 0003 3130 30 | ....100

After Image Len 44 (x0000002c)
Column 0 (x0000), Len 7 (x0007)
0000 0003 3130 30 | ....100
Column 1 (x0001), Len 7 (x0007)
0000 0003 3130 30 | ....100
Column 2 (x0002), Len 7 (x0007)
0000 0003 3130 30 | ....100
Column 3 (x0003), Len 7 (x0007)
0000 0003 3530 30 | ....500

GGS tokens:
TokenID x52 'R' ORAROWID Info x00 Length 20
4141 4155 4751 4141 4641 4141 4149 7641 4141 0001 | AAAUGQAAFAAAAIvAAA..
TokenID x4c 'L' LOGCSN Info x00 Length 9
3539 3130 3932 3935 36 | 591092956
TokenID x36 '6' TRANID Info x00 Length 11
352e 3132 2e32 3138 3937 35 | 5.12.218975

replicat parameter file
=========================
DDL INCLUDE MAPPED &
EXCLUDE OBJNAME GGADM.EXCEPTIONS
ASSUMETARGETDEFS
GETTRUNCATES
REPERROR (-1, EXCEPTION)
REPERROR (-1403, EXCEPTION)
..
..
map scott.junk, target scott1.junk, mapexception (TARGET ggadm.exceptions
, INSERTALLRECORDS
-- , EXCEPTIONSONLY
, colmap ( rep_name = 'rphvn1d'
, 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')
) );

EFFECT ON REPLICAT
==================

REPLICAT CRASHES

ggserr.log
------------

2014-08-09 13:23:05 WARNING OGG-01151 Oracle GoldenGate Delivery for Oracle, rphvn1d.prm: Error mapping from SCOTT.JUNK to SCOTT1.JUNK.
2014-08-09 13:23:05 WARNING OGG-01003 Oracle GoldenGate Delivery for Oracle, rphvn1d.prm: Repositioning to rba 4260 in seqno 14.
2014-08-09 13:23:05 ERROR OGG-01296 Oracle GoldenGate Delivery for Oracle, rphvn1d.prm: Error mapping from SCOTT.JUNK to SCOTT1.JUNK.
2014-08-09 13:23:14 ERROR OGG-01668 Oracle GoldenGate Delivery for Oracle, rphvn1d.prm: PROCESS ABENDING.

report file
-----------
2014-08-09 13:40:18 WARNING OGG-06439 No unique key is defined for table JUNK. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
Using following columns in default map by name:
COL1, COL2, COL3, COL4, COL5, COL6
Using the following key columns for target table SCOTT1.JUNK: COL1, COL2, COL3, COL4, COL5, COL6.

From above error message , it is clear that at replicat side since it does not see any PK (on the target)or KEYCOL defined (in replicat parameter file)
, it needs all the 6 columns in the trail file. But the trail file has before and after image for only first 4 columns (col1,col2.col3 as it is defined by supplemental logging and col4 as it was the changed column).

So to remediate, temporarily add a PK to target-
SQL> alter table junk add constraint pk_junk primary key(col1);

Table altered.

And restart the replicat

GGSCI (titan.gautham.com) 137> start rphvn1d

Sending START request to MANAGER ...
REPLICAT RPHVN1D starting

GGSCI (titan.gautham.com) 138> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING
JAGENT STOPPED
EXTRACT RUNNING XTHVN1D 00:00:09 00:00:07
REPLICAT RUNNING RPHVN1D 02:18:58 00:00:02

--Check the data on the target
SQL> select * from junk;

COL1 COL2 COL3 COL4 COL5 COL6
---------- ---------- ---------- ---------- ---------- ----------
100 100 100 500 100 100

Now since the testing is complete, get the source and target ready for the next testcase by dropping junk table at the source and make sure the DDL got replicated to target -

source
--------
SQL> drop table junk;

Table dropped.

target
------

SQL> select * from junk;
select * from junk
*
ERROR at line 1:
ORA-00942: table or view does not exist

GG Procedure calling

2014-02-19T16:01:00.001-05:00

--begin character setissue handler
--MAP HIGHERONE.tblemailqueue ,target HIGHERONE.tblemailqueue ,
--SQLEXEC(SPNAME higherone.p_string_transform,ID x,PARAMS(in_str=sbody),TRACE ALL),
--COLMAP(
--usedefaults,
--sbody=@getval(x.out_str));
--end character setissue handler

Convert Single Instance Database to Oracle RAC One Node Database / RAC 1 node database

2014-01-07T22:06:00.003-05:00

I had the recent need to convert a single instance DB to a RAC 1-node. I haven't seen many documents
which talks about the steps involved As most docs focus on conversion from single instance to a regular RAC.

In the past, I have used DBCA to create a 1-Node RAC and always used the "Generate database creation scripts" at the end. Then I have had manually edited the scripts generated to suit my purpose and this approach has served me very well as allowing dbca to create a DB directly has caused many unnecessary schemas to creep in.

I drove into the scripts and to my best abilities, tried to reproduce what it does and have written the below blog.

Briefly here are the steps to convert a single instance DB to 1 Node RAC.

Before you start, ensure
+++++++++++++++++++++

I assume you just have single instance init.ora parameters enabled.

No RAC init.ora parameters at this point should be enabled especially CLUSTER_DATABASE.

You might want to consider setting the below init.ora parameters to valid ASM disk group

db_create_file_dest="+DATA_DEV"
db_recovery_file_dest="+FRA_DEV"

Please make sure that your control files, data files, redo logs are already in ASM before you perform these steps.

I also recommend that you use init.ora file which internally points to a spfile as shown below.

Also make sure, you have x init.ora files (where x is the number of nodes)in each node's ORACLE_HOME/dbs directory
with the naming convention init<DB Name>_1.ora, init<DB Name>_2.ora, init<DB Name>_3.ora etc

For example,since my setup is a 2-Node RAC, I have 2 init.ora files with the name init<DB Name>_1.ora and init<DB Name>_2.ora in
ORACLE_HOME/dbs directory of both the RAC nodes.

$ cat initTESTDB_1.ora
SPFILE='+DATA_DEV/TESTDB/spfileTESTDB.ora'

$ cat initTESTDB_2.ora
SPFILE='+DATA_DEV/TESTDB/spfileTESTDB.ora'

My Oracle Home is /u02/app/oracle/product/11.2.0.4/db_1 and DB Name is TESTDB.

The Grid Home is /u02/app/11.2.0/grid .

1)Create the password file if it does not exist (recommended) on 1 node and copy it to other nodes.
Please note that there is NO suffix to password file name.

/u02/app/oracle/product/11.2.0.4/db_1/bin/orapwd file=/u02/app/oracle/product/11.2.0.4/db_1/dbs/orapwTESTDB force=y

Make the following change in your init.ora file(If using spfile perform an alter system set)
remote_login_passwordfile=exclusive

Please shutdown the DB at this point.

2)This can be skipped if already performed(please note dbca automatically does it if you have ever created a DB on this
OH in the past) but again no harm in doing this again.

/u02/app/11.2.0/grid/bin/setasmgidwrap o=/u02/app/oracle/product/11.2.0.4/db_1/bin/oracle

3)Add the database to CRS.

/u02/app/oracle/product/11.2.0.4/db_1/bin/srvctl add database -d TESTDB -o /u02/app/oracle/product/11.2.0.4/db_1 -p +DATA_DEV/TESTDB/spfileTESTDB.ora -n TESTDB -c RACONENODE -e node1,node2 -i TESTDB -a "DATA_DEV,FRA_DEV"

Please pay particular attention to
(a)The option "-c RACONENODE"
(b)The places which will need you to plug in your ASM disk group.In my case , I have two of them called DATA_DEV,FRA_DEV

(4) Add a service.
/u02/app/oracle/product/11.2.0.4/db_1/bin/srvctl add service -s TESTDB.gautham.com -d TESTDB -P BASIC

(5)Disable the database
/u02/app/oracle/product/11.2.0.4/db_1/bin/srvctl disable database -d TESTDB

(6)Startup the database normally via

sqlplus '/ as sysdba'
startup

(7) Add the redo log groups for thread 2.

I assume that GROUP 1 and GROUP 2 are the only 2 groups in the single instance DB belonging to thread 1.By default, thread 1 should already exist. If there
are more than 2 groups in thread 1, then change the group name below accordingly .

ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 3 SIZE 250m,
GROUP 4 SIZE 250m;

ALTER DATABASE ENABLE PUBLIC THREAD 2;

Repeat it with THREAD 3 , if it is a 3 node RAC and so on.

(8) There is no need to add a 2nd Tablespace as this is a 1-Node RAC and only 1 instance will be acive at any point.

(9) Set the 2 parameters in the spfile to

ALTER SYSTEM SET cluster_database=TRUE SCOPE=spfile;
ALTER SYSTEM SET remote_listener='<scan name>:1521' scope=spfile ;

(10)Shutdown the DB via a normal "shutdown immediate" command.

(11) /u02/app/oracle/product/11.2.0.4/db_1/bin/srvctl enable database -d TESTDB;
(12) /u02/app/oracle/product/11.2.0.4/db_1/bin/srvctl start database -d TESTDB;

Extraction of Grants from a schema

2013-11-07T08:32:00.001-05:00

The following should be able to extract all the grants given from a particular schema to the other schemas.

This is particular useful when you have a bunch of schemas (with cross grants) and you are planning to move schema by schema and then finally apply the grants when all schemas are moved over.

expdp \'/ as sysdba\' schemas=scott dumpfile=scott_role.dmp nologfile=yes include=owner_grant

impdp \'/ as sysdba\' dumpfile=scott_role.dmp sqlfile=scott_role.sql nologfile=yes

Script to monitor Alert log files for an Oracle Database

2013-11-07T08:30:00.000-05:00

Recently I was being asked to write a script to monitor the Oracle Alert Logs for "ORA-" errors and send out an automated email to the DBA team.

Here are the challenges :
1.How do I make the script scan only the lines which were not scanned by the previous scan .
An other way to reframe the question - if scan x happened at 3pm, how do I ensure that the Scan (x+1) picked up only the new lines generated after 3pm. Should I store the time stamp of the last scan or should I store the high-water line count . I finally decided to go with the HWM line number.

2.Also how do I deal when a alert log is recycled?Will my script be able to find out that alert log has been recycled and then automatically set by line HWM to start from line 1 of the new alert log.
Looks like checking the stored HWM line count and the current line count of the alert log would help me in finding this out .If the alert log line count is smaller than the stored HWM line count then it would be safe to assume the alert log was recycled. Again in extraordinary circumstances where an alert log was recycled and the subsequent script ran so late(say after a couple of weeks or so) that new alert log line count exceeded the stored HWM line count ,then my logic would make it skip the first stored HWM lines of the alert log for that particular run only (but then again regaing normal operations for the subsequent scans)but I choose to ignore this possibility as this is extremely very remote and even if so, only the first scan would fail to detect the ORA- errors.

#!/bin/bash
#Alert Log Monitoring Script
#Usage: alert_monitor.sh
#Author:Gautham Chandrasekaran
#Version 1.0 06 NOVEMBER 2013

EMAIL_LIST=gautha@hotmail.com
ALERT_LOG_FILE=/u01/app/oracle/diag/rdbms/prod/PROD_1/trace/alert_PROD_1.log
EMAIL_FILE=/tmp/alert_mail.log

#Error out if Alert Log is missing
if [ ! -f $ALERT_LOG_FILE ] ; then
echo "**** $ALERT_LOG_FILE FILE MISSING FROM ALERT LOG MONITOR ******" > $EMAIL_FILE
cat $EMAIL_FILE|mailx -s "Alert Log Errors" $EMAIL_LIST
exit 1
fi

ALERT_LOG_LINE_CNT_FILE=${ALERT_LOG_FILE}.ctr

#First run of the script or somebody deleted it.Therefore start from zero.
if [ ! -f $ALERT_LOG_LINE_CNT_FILE ] ; then
echo 0 > $ALERT_LOG_LINE_CNT_FILE
fi

STORED_LINE_CTR=`cat $ALERT_LOG_LINE_CNT_FILE`

ALERT_LOG_LINE_CTR=`cat $ALERT_LOG_FILE|wc -l`

#check to see whether somebody has recycled alert log file.
#in this case the STORED_LINE_CTR will be higher than ALERT_LOG_LINE_CTR
#If so just reset STORED_LINE_CTR to 0.
if [ $ALERT_LOG_LINE_CTR -lt $STORED_LINE_CTR ] ; then
STORED_LINE_CTR=0
fi

LINES_TO_MONITOR=`expr $ALERT_LOG_LINE_CTR - $STORED_LINE_CTR`

tail -n $LINES_TO_MONITOR $ALERT_LOG_FILE|grep -i "ora-" > /tmp/alert_mail.log

#Reset line number to ctr file to skip the scanned rows in the next run
echo $ALERT_LOG_LINE_CTR > $ALERT_LOG_LINE_CNT_FILE

#Email only if the file is not empty
if [ -s $EMAIL_FILE ] ; then
cat $EMAIL_FILE|mailx -s "Alert Log Errors" $EMAIL_LIST
fi

ASM Persistent Device Mapping for Oracle 11gR2 RAC for RHEL 6

2013-05-08T10:45:00.003-04:00

Recently when I was setting up a RAC setup at home using some dell desktop servers , I hit a unique problem.
Before going into that , here was my setup details

RAC Node Names : siddhu.gautham.com, surya.gautham.com (CentOS 6.4)
Openfiler : openfiler.gautham.com (Openfiler 2.3 or 2.4 (not sure) 32-bit) (ip 192.168.1.195(eth0),
192.168.2.195(eth1)) ->Used to provide shared storage to the RAC Nodes.

The openfiler server was deployed to provide iscsi storage to the other RAC Nodes.

After the install of the 3 servers and provisioning of the storage to the 2 RAC nodes, I reached a point where i needed to install the ASMLib libraries for device persistance. That is when I realized that Oracle Corp had stopped supplying ASMLib packages starting RHEL 6.

So now I had to look at other alternatives for device persistence.

The below should tell you what I mean by device persistence
=========================================

[root@siddhu by-path]# pwd
/dev/disk/by-path

[root@siddhu by-path]# ls -lt |grep 192.168.2

lrwxrwxrwx 1 root root 9 May 6 23:27 ip-192.168.2.195:3260-iscsi-iqn.2006-01.com.openfiler:racdb.crs1-lun-0 -> ../../sdb
lrwxrwxrwx 1 root root 9 May 6 23:27 ip-192.168.2.195:3260-iscsi-iqn.2006-01.com.openfiler:racdb.data1-lun-0 -> ../../sde
lrwxrwxrwx 1 root root 9 May 6 23:27 ip-192.168.2.195:3260-iscsi-iqn.2006-01.com.openfiler:racdb.fra1-lun-0 -> ../../sdc

The reason I had to filter with 192.168.2 was because openfiler had 2 network interfaces and I want to use storage presented only via eth1 which was 192.168.2.195.

The problem above is the crs lun is available as /dev/sdb, data lun as /dev/sde and fra lun is /dev/sdc.
The moment the system is rebooted this would change to a different mapping.

Also if you go the other nac rode i.e. surya.gautham.com , you would see the above mappings as completely different i.e. CRS lun would not be presented as the same as /dev/sdb but as something else.

Therefore what i needed was a reliable mapping like say /dev/asm-crs-disk1 which would always map to crs lun and /dev/asm-data-disk1 which would map to data lun and so on across all nodes of the RAC.

The way we get this is to use udev rules.

Before you do this, please partition the the 3 devices above using fdisk command.
Create just 1 primary partition (I am not going to the details of this).

fdisk /dev/sdb
fdisk /dev/sde
fdisk /dev/sdc

Also perform partprobe on the other node to see the resulting /dev/sd[x]1 partition.

PLEASE NOTE THAT I HAD TO REBOOT BOTH THE RAC NODES AND THERFORE THE MAPPING CHANGED FROM ABOVE.PLEASE DO NOT LET THE MISMATCH ABOVE AND BELOW CONFUSE YOU.

From above we identified that /dev/sdd points to CRS in siddhu and /dev/sdc points to CRS in surya
[root@siddhu ~]# /sbin/scsi_id -g -u -d /dev/sdd
14f504e46494c45526834373035642d374d73772d794c386b

[root@surya iscsi]# /sbin/scsi_id -g -u -d /dev/sdc
14f504e46494c45526834373035642d374d73772d794c386b

The above shows that identifier is same irrespective of whether it is /dev/sdc(surya) or /dev/sdd(siddhu)
and this identifier will always be the same for CRS.

For FRA
=======
[root@surya iscsi]# /sbin/scsi_id -g -u -d /dev/sdf
14f504e46494c455252635354667a2d4a6153492d6c307a66

[root@siddhu ~]# /sbin/scsi_id -g -u -d /dev/sde
14f504e46494c455252635354667a2d4a6153492d6c307a66

For DATA
========
[root@siddhu ~]# /sbin/scsi_id -g -u -d /dev/sdf
14f504e46494c45523473334e397a2d70347a612d554a394f

[root@surya iscsi]# /sbin/scsi_id -g -u -d /dev/sdg
14f504e46494c45523473334e397a2d70347a612d554a394f

vi /etc/udev/rules.d/99-oracle-asmdevices.rules

KERNEL=="sd?1", BUS=="scsi", PROGRAM=="/sbin/scsi_id -g -u -d /dev/$parent", RESULT=="14f504e46494c45526834373035642d374d73772d794c386b", NAME="asm-crs-disk1", OWNER="oracle", GROUP="dba", MODE="0660"
KERNEL=="sd?1", BUS=="scsi", PROGRAM=="/sbin/scsi_id -g -u -d /dev/$parent", RESULT=="14f504e46494c455252635354667a2d4a6153492d6c307a66", NAME="asm-fra-disk1", OWNER="oracle", GROUP="dba", MODE="0660"
KERNEL=="sd?1", BUS=="scsi", PROGRAM=="/sbin/scsi_id -g -u -d /dev/$parent", RESULT=="14f504e46494c45523473334e397a2d70347a612d554a394f", NAME="asm-data-disk1", OWNER="oracle", GROUP="dba", MODE="0660"

After reboot
+++++++++++++

[root@surya dev]# (cd /dev/disk/by-path; ls -l *openfiler* |grep -i 192.168.2| awk '{FS=" "; print $9 " " $10 " " $11}')
ip-192.168.2.195:3260-iscsi-iqn.2006-01.com.openfiler:racdb.crs1-lun-0 -> ../../sde
ip-192.168.2.195:3260-iscsi-iqn.2006-01.com.openfiler:racdb.crs1-lun-0-part1 -> ../../asm-crs-disk1
ip-192.168.2.195:3260-iscsi-iqn.2006-01.com.openfiler:racdb.data1-lun-0 -> ../../sdg
ip-192.168.2.195:3260-iscsi-iqn.2006-01.com.openfiler:racdb.data1-lun-0-part1 -> ../../asm-data-disk1
ip-192.168.2.195:3260-iscsi-iqn.2006-01.com.openfiler:racdb.fra1-lun-0 -> ../../sdc
ip-192.168.2.195:3260-iscsi-iqn.2006-01.com.openfiler:racdb.fra1-lun-0-part1 -> ../../asm-fra-disk1
[root@surya dev]#

[root@surya dev]# ls -lt /dev/*asm*
brw-rw---- 1 oracle dba 8, 65 May 1 00:50 /dev/asm-crs-disk1
brw-rw---- 1 oracle dba 8, 33 May 1 00:50 /dev/asm-fra-disk1
brw-rw---- 1 oracle dba 8, 97 May 1 00:50 /dev/asm-data-disk1
[root@surya dev]#

Restore and recover from cold backup using rman

2013-03-04T09:54:00.003-05:00

Many a times before a application upgrade, we would like to take a cold backup of the database just to take care of some "mess-up" and would just want to restore it if something goes wrong.

The following is the preferred way for me to take a RMAN cold backup.

Take the BACKUP
===============
Put the DB in mount mode.

rman target / nocatalog<<EOF>backup.log

run{
allocate channel d1 device type disk format '/oracle/arch/rmanbkp/HOMEDB_FULL_%d_%T_%U';

backup as compressed backupset tag rman_backfulcold_HOMEDB database include current controlfile ;
}
EOF

Query from RMAN to see where the control file resides

list backup;

List of Backup Sets
===================

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
13 Full 39.13M DISK 00:00:06 04-MAR-13
BP Key: 13 Status: AVAILABLE Compressed: YES Tag: RMAN_BACKFULCOLD_HOMEDB
Piece Name: /oracle/arch/rmanbkp/HOMEDB_FULL_HOMEDB_20130304_0do3ltu7_1_1
List of Datafiles in backup set 13

..

..
..
..

Piece Name: /oracle/arch/rmanbkp/HOMEDB_FULL_HOMEDB_20130304_0no3ltuo_1_1
List of Datafiles in backup set 23
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
28 Full 301818 04-MAR-13 /oracle/oradata02/HOMEDB/data/forumindextabspace01.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
24 Full 1.03M DISK 00:00:01 04-MAR-13
BP Key: 24 Status: AVAILABLE Compressed: YES Tag: RMAN_BACKFULCOLD_HOMEDB
Piece Name: /oracle/arch/rmanbkp/HOMEDB_FULL_HOMEDB_20130304_0oo3ltup_1_1
Control File Included: Ckp SCN: 301818 Ckp time: 04-MAR-13

STEPS TO RESTORE
================

startup nomount

Restore the CONTROL FILE from the backup piece identified above.

run
{
allocate channel c1 device type disk format '/oracle/arch/rmanbkp/HOMEDB_FULL_%d_%T_%U';
restore controlfile from '/oracle/arch/rmanbkp/HOMEDB_FULL_HOMEDB_20130304_0oo3ltup_1_1';
}

alter database mount;

run
{
allocate channel c1 device type disk format '/oracle/arch/rmanbkp/HOMEDB_FULL_%d_%T_%U';
restore database from tag='rman_backfulcold_HOMEDB';
}

Data Guard Catch-Up via RMAN using TSM

2013-02-19T15:48:00.001-05:00

Many a times, we DBA's are faced with an issue where the DG has been out of synch with the Production DB for quite a long time.
Certain examples are we ran an overnight rman duplicate script to create a standy database and by the time we got to look at the status of the restore, hundreds of logs had been generated in the primary .
In all probability, in a typical production environment, a hourly RMAN archivelog backup and delete script would have run and deleted the archivelogs (after backing it up, obviously).

So to help the standby get in synch with the primary , we have to either put the standby in recover mode and restore the archivelogs on production hoping the FAL services would pick it up.

Once we realize that the archivelogs number in 100's or 1000's, this task becomes nearly impossible with the risk of endangering the archive destination on the primary getting filled up.

I have expalined a method below uses the same RMAN technique to get the standby in synch with the primary.
The example uses TSM (Tivoli) as the storage medium and please change accordingly as per your environment's tape set up.

For TSM, we need to copy over the primary node's TSM password file to the standby to ensure that the auxiliary channel can access the tape backups created from the primary database's node.Along with it , we need to ensure that both primary and standby are talking to the same TSM server.

At the Standby, Cancel the Managed recover process if running and ensure that the database is in mount mode.

alter database recover managed standby database cancel;

1) Do the following in Primary.

create a file called recoverystandby.cmd, with the following contents

#++++++++++++++++++++++++++++++++++
#++RECOVERYSTANDBY.CMD++++++++++
#++++++++++++++++++++++++++++++++++
backup archivelog all not backed up;
run {
#Adjust it as per the archivelogs already present in standby
set until time 'sysdate-(1/24)';
#Make sure below /tmp/tdpo.opt file is available in standby database.
allocate auxiliary channel d2 type 'sbt_tape' parms 'ENV=(TDPO_OPTFILE=/tmp/tdpo.opt)';
recover standby clone database delete archivelog;
}

2)Do the following in Standby.

This is to make sure that the standby can access the tape backups.
******These steps are specific to TSM******
Make sure /tmp/tdpo.opt file is available. This TDPO file is used by auxiliary channel in step 1.

> cat /tmp/tdpo.opt
DSMI_LOG /tmp
DSMI_ORC_CONFIG /tmp/dsm.opt
TDPO_FS xxx
TDPO_NUM_BUFFERS 10
TDPO_NODE prmyserver
TDPO_OWNER oracle
TDPO_PSWDPATH /tmp

Make sure /tmp/dsm.opt file is present referred above as DSMI_ORC_CONFIG

> cat /tmp/dsm.opt
SERVERNAME <tsm server>

Make sure you have physically copied the primary node's TSM password file. The name of the password file would be TDPO.<tdpo_node> i.e. TDPO.prmyserver and should be available under
TDPO_PSWDPATH path.

3) Do the following in Primary

Set environmental variables to connect to local primary database
export ORACLE_HOME='<path of ORACLE_HOME>'
export ORACLE_SID='<SID of Primary Database>'
export PATH=$ORACLE_HOME/bin:$PATH

Run the above command via RMAN as

rman target / auxiliary sys/manager@<standby database> catalog rman/rman@<rman db> cmdfile=recoverstandby.cmd log=catchup_stdby.log

Please allow the above script to run and this script should allow the standby to catch up with the primary without the need to restore the files into primary.

Once it has caught up, you can put the primary back into managed recovery process.

Please note that there is one more way to achieve the same thing using backup as incremental via rman like
backup incremental from scn <scn number> database . You can look at other blogs to read more about this and decide for yourself which one to use. If there is a missing archivelog , then you cannot use my method and will be better off using the other approach I just mentioned above.

Golden Gate Add SCHEMATRANDATA Verification

2013-01-31T19:37:00.004-05:00

I just conjured up a small script to verify whether the GG Command

add schematrandata <schema name>

worked correctly.
By the way, please keep in mind that once you executed the above command, all future objects would be automatically covered. That is why I highly recommend setting logging at the schema level rather than the individual table level.

Basically the below query should be able to give you info

select * from table(logmnr$always_suplog_columns('name of the schema in upper case','table name in upper case'));

Please be aware that dba_log_groups will not show any entry if you had added schema level logging . Only if you had added logging at a table level using add trandata would there be an entry in dba_log_groups.

A small pl/sql snippet to go through an entire schema and report back is below

declare
l_schema varchar2(20) := upper('schema name') ;
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;
/

Oracle Pipelined Functions

2012-12-11T20:28:00.001-05:00

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)

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

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

2012-06-08T14:32:00.002-04:00

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

2012-06-08T10:55:00.001-04:00

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

2012-06-07T19:56:00.004-04:00

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

2012-06-01T11:36:00.000-04:00

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

2012-05-25T12:04:00.007-04:00

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

2012-05-25T11:54:00.000-04:00

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

2012-05-25T11:35:00.003-04:00

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

2012-05-25T10:45:00.000-04:00

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

2012-05-25T10:28:00.000-04:00

After a long gap, I am writing the 3rd post.
Please make sure all steps in posts 2 and 3 are completed before you start this.

Also in my environment, the source database is using ASM. Therefore it is mandatory you do the following before proceeding on the source(or extract) database.

TNSNAMES.ORA File
++++++++++++++++++++

ASM.xxx.COM =
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = <server name>)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = +ASM)
    )
)

LISTENER.ORA
+++++++++++++++
Please note that I have done an automatic instance registration and therefore I have not mentioned the SID_DESC part in the listener.ora file.

LISTENER =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = <server name>)(PORT = 1521))
)
)

At the ASM instance
+++++++++++++++++
This should automatically come up.
SQL> show parameter local_listener

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string      (DESCRIPTION=(ADDRESS_LIST=(AD
                                                 DRESS=(PROTOCOL=TCP)(HOST=<default ip address>)
(PORT=1521))))

In case , your listener is not listening on default port 1521, then make sure you explicitly put a local_listener parameter in the init.ora /spfile of the ASM instance with the appropriate port number.

Verify whether listener has the ASM instance registered

/oracle/software/grid/11.2.0.3/dbs> lsnrctl

LSNRCTL for HPUX: Version 11.2.0.3.0 - Production on 19-APR-2012 10:39:19

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

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xxx)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for HPUX: Version 11.2.0.3.0 - Production
Start Date                10-MAR-2012 02:14:14
Uptime                    40 days 7 hr. 25 min. 7 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oracle/software/grid/11.2.0.3/network/admin/listener.ora
Listener Log File         /oracle/software/grid/11.2.0.3/log/diag/tnslsnr/xxx/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xxxx)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM", status READY, has 1 handler(s) for this service...
..
..
..

Also make sure remote_login_passwordfile is setup for the ASM instance.Please use the orapwd utility to
set up the file and password. This password applies to the SYS account.

SQL> show parameter remote_log

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile            string      EXCLUSIVE
SQL>

Now you can use either the SYS schema or any schema which has the SYSDBA privilege on the ASM instance(SYSASM privilege in 11g upwards).

I use the SYS account for connecting to the ASM instance via Golden gate.

Verify whether it works

sqlplus "sys/<password>@asm as sysdba"

Now we need 2 processes running on the source system, which would be extract and Data Pump extract (DP) process.
+++++++++++++++++++++++++
Extract Process Configuration
++++++++++++++++++++++++

Assuming you ran , the create subdirs command, you should see the following directories created.

GGSCI (jupiter) 1> create subdirs

Creating subdirectories under current directory /oracle/software/goldengate/11.1.1.1.2

Parameter files                /oracle/software/goldengate/11.1.1.1.2/dirprm: created
Report files                   /oracle/software/goldengate/11.1.1.1.2/dirrpt: created
Checkpoint files               /oracle/software/goldengate/11.1.1.1.2/dirchk: created
Process status files           /oracle/software/goldengate/11.1.1.1.2/dirpcs: created
SQL script files               /oracle/software/goldengate/11.1.1.1.2/dirsql: created
Database definitions files     /oracle/software/goldengate/11.1.1.1.2/dirdef: created
Extract data files             /oracle/software/goldengate/11.1.1.1.2/dirdat: created
Temporary files                /oracle/software/goldengate/11.1.1.1.2/dirtmp: created
Veridata files                 /oracle/software/goldengate/11.1.1.1.2/dirver: created
Veridata Lock files            /oracle/software/goldengate/11.1.1.1.2/dirver/lock: created
Veridata Out-Of-Sync files     /oracle/software/goldengate/11.1.1.1.2/dirver/oos: created
Veridata Out-Of-Sync XML files /oracle/software/goldengate/11.1.1.1.2/dirver/oosxml: created
Veridata Parameter files       /oracle/software/goldengate/11.1.1.1.2/dirver/params: created
Veridata Report files          /oracle/software/goldengate/11.1.1.1.2/dirver/report: created
Veridata Status files          /oracle/software/goldengate/11.1.1.1.2/dirver/status: created
Veridata Trace files           /oracle/software/goldengate/11.1.1.1.2/dirver/trace: created
Stdout files                   /oracle/software/goldengate/11.1.1.1.2/dirout: created

Start the manager process
===================
Please note that the manager process needs a mgr.prm file in dirprm directory which you have to create with just 1 line as
PORT 7809.

GGSCI (jupiter) 4> start mgr

Manager started.

GGSCI (jupiter) 5> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING

EXTRACT PROCESS
=================
We are planning to replicate a schema called SCOTT for both ddl and dml.
Please make sure that schema level logging is setup for the SCOTT schema (you should have typed
add schematrandata SCOTT).
Please go the the directory called dirprm under the GG home and create a file called <extract name>.prm.

In our case the extract name is xtathdv and therefore the file is xtathdv.prm.

Please populate the contents of xtathdv.prm as follows.

---START OF EXTRACT FILE--
Extract XTATHDV
----------------------------------------------------
--Extract Process for XXX Database
--List of Schemas
--SCOTT
----------------------------------------------------
SETENV (ORACLE_HOME="/oracle/software/rdbms/11.2.0.3")
SETENV (ORACLE_SID="xxx")
userid ggddlusr, password <password>
TRANLOGOPTIONS ASMUSER "sys@asm",asmpassword "<password>"
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.*;
--END OF EXTRACT FILE

Now go to the ggsci prompt and try starting the xtathdv extract process

GGSCI (jupiter) 2> add extract xtathdv,tranlog, begin now
EXTRACT added.

GGSCI (jupiter) 4> add exttrail /oracle/goldengatedata/dirdat/at,extract xtathdv,megabytes 250
EXTTRAIL added.

GGSCI (jupiter) 6> start ext xtathdv

Sending START request to MANAGER ...
EXTRACT XTATHDV starting

Check whether the extract is still running

GGSCI (jupiter) 11> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     XTATHDV     00:00:00      00:00:05

In the next tutorial , we will set up and DataPump Extract Process.

Random Oracle Posts

Resolving Gaps in Data Guard Apply Using Incremental RMAN BACKUP

GoldenGate Password Encryption

Oracle export with where clause and parallelism

Goldengate ALLOWNOOPUPDATES error - Why it happens ?

Part 3 - Goldengate supplemental logging , keycols, logallsupcols - With Final Comments

Extract Parameter file--------------------------------

Replicat Paramer file------------------------------

logdump as always---------------------

GGSPKUpdate

FieldComp

FieldComp has no before image. It has only after image as basic assumption is PK is not changed.It is created only if source has a PK/UK or has KEYCOLS defined in extract parameter file.

Part 2 - Goldengate supplemental logging , keycols, logallsupcols testcase

Part 1 - Goldengate supplemental logging , keycols, logallsupcols testcase

Extract parameter file

logdump of trail file

GG Procedure calling

Convert Single Instance Database to Oracle RAC One Node Database / RAC 1 node database

Extraction of Grants from a schema

Script to monitor Alert log files for an Oracle Database

ASM Persistent Device Mapping for Oracle 11gR2 RAC for RHEL 6

Restore and recover from cold backup using rman

Data Guard Catch-Up via RMAN using TSM

Golden Gate Add SCHEMATRANDATA Verification

Oracle Pipelined Functions

Script to monitor and restart failed GoldenGate Processes

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

Golden Gate Bounded Recovery ( BR) Process

Install XML DB on Oracle 11gR2

Verify XDB Installation

Golden Gate Setup 7 - Replicat Process on Target

Golden Gate Setup 6 - Exceptions Table on Target

Golden Gate Setup 5- Source Export and Target Import

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

Golden Gate Setup 3 (Source Database) Extract Process

logdump as always
---------------------