Locking Privileges in Oracle

What permissions do you need to lock rows on an Oracle table?
What about to lock the whole table?

It’s not quite as much as you may think!

Lets have a couple of users; schema_owner and user1

SQL> show user
USER is "SYS"
SQL> create user schema_owner identified by schema_owner;
User created.
SQL> grant connect,resource to schema_owner;
Grant succeeded.
SQL> grant unlimited tablespace to schema_owner;
Grant succeeded.
SQL> create user user1 identified by user1;
User created.
SQL> grant create session to user1;
Grant succeeded.

Now for a table and grants

SQL> conn schema_owner/schema_owner
Connected.
SQL> create table tab1 (col1 date, col2 number);
Table created.
SQL> insert into tab1 values (sysdate,1);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from tab1;
COL1		COL2
--------- ----------
14-JUL-15	   1
SQL> grant select on tab1 to user1;
Grant succeeded.

So, what can USER1 do with that table?

SQL> conn user1/user1
Connected.
SQL> select * from schema_owner.tab1;
COL1 COL2
--------- ----------
14-JUL-15 1

good

SQL> update schema_owner.tab1 set col2=2 where col2=1;
update schema_owner.tab1 set col2=2 where col2=1
*
ERROR at line 1:
ORA-01031: insufficient privileges

nice

SQL> insert into schema_owner.tab1 values (sysdate,2);
insert into schema_owner.tab1 values (sysdate,2)
*
ERROR at line 1:
ORA-01031: insufficient privileges

yeah

SQL> delete from schema_owner.tab1;
delete from schema_owner.tab1
*
ERROR at line 1:
ORA-01031: insufficient privileges

great

SQL> select * from schema_owner.tab1 for update;
COL1      COL2
--------- ----------
14-JUL-15          1

oh

SQL> lock table schema_owner.tab1 in exclusive mode;
Table(s) Locked.

What?!? Is this real? Has that REALLY lock the entire table with only SELECT permissions? Can I delete from that table from a different session + user which has permissions?

SQL> show user
USER is "SCHEMA_OWNER"
SQL> select * from schema_owner.tab1;
COL1      COL2
--------- ----------
14-JUL-15	   1
SQL> delete from schema_owner.tab1;
(no return....)

A quick look in gv$session will show you that USER1 is indeed blocking SCHEMA_OWNER despite only having SELECT privileges on the table:

select .... from gv$session;
CON_ID SID USERNAME	   SQL_ID	 STATUS   BS_STAT    BL_SID EVENT
------ --- --------------- ------------- -------- ---------- ------ ---------------------------
     3	47 USER1			 INACTIVE NO HOLDER  BLOCK  SQL*Net message from client
     3	55 SCHEMA_OWNER    5n1hw77std3h5 ACTIVE   VALID      47     enq: TM - contention

SQL> select * from dba_blockers
 2 ;

HOLDING_SESSION CON_ID
--------------- ------
47                   3

SQL> select * from dba_waiters;

WAITING_SESSION WAITING_CON_ID HOLDING_SESSION HOLDING_CON_ID LOCK_TYPE MODE_HELD MODE_REQUESTED LOCK_ID1 LOCK_ID2
--------------- -------------- --------------- -------------- -------------------------- ---------------------------------------- ---------------------------------------- ---------- ----------
 55                          3              47              3 DML                 Exclusive Row-X (SX) 96178 0

This is because of a side effect of an Oracle philosophy; “don’t do now what you may never need to do”. If Oracle can defer any actions from now, such as writing a dirty buffer to disk, or seeing if a session has permissions to perform an update when all you have done is request a lock, then it will, if possible, do it later.

You may request the lock so Oracle checks that you can access the object (SELECT), but you may never try to actually change the row, or table so it’s not necessary to see if you can modify the object…

This is a pretty problematic security hole; In Oracle 12c, a new table privilege has appeared: READ. If we re-run the above with GRANT READ instead of GRANT SELECT…

SQL> show user
USER is "USER1"
SQL> select grantee,privilege from user_tab_privs where table_name = 'TAB1';
GRANTEE              PRIVILEGE
-------------------- ----------
USER1                READ
SQ> select * from schema_owner.tab1;
COL1      COL2
--------- ----------
14-JUL-15          1

ok

SQ> select * from schema_owner.tab1 for update;
select * from schema_owner.tab1
*
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> lock table schema_owner.tab1 in exclusive mode;
lock table schema_owner.tab1 in exclusive mode
*
ERROR at line 1:
ORA-01031: insufficient privileges

Thats better!

So the next time someone says “it’s only SELECT permissions”, it’s not. You might want to check out using READ.

Oracle ACE Award

I have just learned that I have been nominated and accepted as an Oracle ACE. I would like to thank Jonathan Lewis for the nomination and Oracle Corp. for the recognition.

O_ACELogo_clr

It’s a tremendous honour for me to receive this award. I just hope I can live up to it by continuing to serve the community, write some more blog posts (here) that people find useful, keep organising the UKOUG RAC, Cloud, Availability and Infrastructure SIG and keep helping with UKOUG Tech 15. Keep going to the London Oracle Beer [no link, just beer] and #ClubOracle and keep promoting the Oracle community.

I’d also like to thank Martin Widlake for persuading me to get up on stage a few years ago and start giving presentations too. I think your beer debt is finally paid :-)

Finally, I’d like to thank my wife and children for putting up with me sitting in my office at home, working on those presentations for hours and letting me enjoy myself off at conferences, SIGs and all the other events I attend when I could be spending time with them. I hope I get the work/life/community balance right.

Whilst all of that community stuff takes time and some effort, it’s rewarding and fulfilling, and remarkably social. Why don’t you try some or all of the above. I’m happy to help you get started. I’m short a talk on the next RAC SIG in July… fancy filling in? Send me an email. Lets talk.

Goldengate Data Manipulation – When Inserts & Updates differ

One very useful aspect of Golden Gate is to allow the manipulation of data between the source and the destinations.

One recent problem that I encountered was to alter the data differently for inserts than for updates. This was caused by the receiving system needing to have some default data in columns which may or may not be supplied by the insert or update statements. This is slightly more complex than first imagined:

  • If we have an INSERT and the column value IS NULL, or the column value IS NOT SUPPLIED by the insert statement, we should set the default for that column.
  • If we have an UPDATE and the column value IS NULL, then we should set the default for that column.
    We must NOT set the default if the UPDATE does not supply the column, otherwise we may incorrectly overwrite data in the target system.

The first problem is that, by default, you are not allowed to have more than one table mapping per table. To get around this, you need to use the “ALLOWDUPTARGETMAP” parameter. You can then add multiple mappings.

You need to be aware that each mapping will fire for each transaction action. If you have 2 active table mappings for the same table, you will end up with 2 inserts/updates/deletes. Get this mapping wrong and your data integrity will be destroyed, and you will get a lot of constraint errors. In this case we have 2 mappings, one for inserts and one for updates and deletes. I need to use the get/ignore commands to indicate which actions each mapping should use.

The following example was for a data pump, but it is valid to do this for all extracts and replicats.

 

-- ggsci: add extract p_neil, exttrailsource /u02/gg/bin12/dirdat/NE, BEGIN NOW
-- ggsci: add rmttrail ./dirdat/NP extract p_neil megabytes 100

EXTRACT p_neil
USERID owner_goldengate@DB_LOCAL PASSWORD password
passthru
TARGETDEFS ./dirdef/defgen.neil.def
RMTHOST remote.server.world mgrport 7809
RMTTRAIL ./dirdat/NP

-- So we can have multiple mappings for a single table. This is a dangerous parameter!
ALLOWDUPTARGETMAP

-- FOR INSERTS - REPLACE MISSING COLUMNS
getinserts
ignoreupdates
ignoredeletes

TABLE NCHA.NEIL, TARGET NCHA.NEIL
COLMAP (usedefaults, &
C2 = @IF (@COLTEST (C2 , NULL, MISSING) , '1900-01-01:00:00:00.000000' , C2 ), &
C3 = @IF (@COLTEST (C3 , NULL, MISSING) , '1900-01-01:00:00:00.000000' , C3 ), &
);

-- FOR UPDATES - IGNORE MISSING COLUMNS
-- We will do the deletes here too. If they are supplied as NULL they should be modified
-- You may need to do a separate section for deletes depending upon your rules.
ignoreinserts
getupdates
getdeletes

TABLE NCHA.NEIL, TARGET NCHA.NEIL
COLMAP (usedefaults, &
C2 = @IF (@COLTEST (C2 , NULL) , '1900-01-01:00:00:00.000000' , C2 ), &
C3 = @IF (@COLTEST (C3 , NULL) , '1900-01-01:00:00:00.000000' , C3 ), &
);

-- And back to normal for subsequent table mappings
getinserts
getupdates
getdeletes

Auditing Read-Only Standbys

If your company has a passing interest in database security, you are probably running some sort of session auditing at the very least [audit session] (although this can also be useful for troubleshooting connectivity issues). There’s a reasonable chance you’re also running some level of object auditing, or even DML access auditing if your employer so dictates:

Check access/change of objects in the DB
  1  select audit_option, success, failure
  2  from dba_stmt_audit_opts
  3  union
  4  select privilege, success, failure
  5  from dba_priv_audit_opts
  6* order by 2,1
AUDIT_OPTION                                       SUCCESS                                  FAILURE
-------------------------------------------------- ---------------------------------------- -----------------
ALTER ANY PROCEDURE                                BY ACCESS                                BY ACCESS
ALTER ANY TABLE                                    BY ACCESS                                BY ACCESS
ALTER DATABASE                                     BY ACCESS                                BY ACCESS
ALTER PROFILE                                      BY ACCESS                                BY ACCESS
ALTER SYSTEM                                       BY ACCESS                                BY ACCESS
ALTER TABLE                                        BY ACCESS                                BY ACCESS
ALTER USER                                         BY ACCESS                                BY ACCESS
AUDIT SYSTEM                                       BY ACCESS                                BY ACCESS
CREATE ANY JOB                                     BY ACCESS                                BY ACCESS
CREATE ANY LIBRARY                                 BY ACCESS                                BY ACCESS
CREATE ANY PROCEDURE                               BY ACCESS                                BY ACCESS
CREATE ANY TABLE                                   BY ACCESS                                BY ACCESS
CREATE EXTERNAL JOB                                BY ACCESS                                BY ACCESS
CREATE LIBRARY                                     BY ACCESS                                BY ACCESS
CREATE PROCEDURE                                   BY ACCESS                                BY ACCESS
CREATE PUBLIC DATABASE LINK                        BY ACCESS                                BY ACCESS
CREATE SESSION                                     BY ACCESS                                BY ACCESS
CREATE TABLE                                       BY ACCESS                                BY ACCESS
CREATE USER                                        BY ACCESS                                BY ACCESS
DATABASE LINK                                      BY ACCESS                                BY ACCESS
DIRECTORY                                          BY ACCESS                                BY ACCESS
DROP ANY PROCEDURE                                 BY ACCESS                                BY ACCESS
DROP ANY TABLE                                     BY ACCESS                                BY ACCESS
DROP PROFILE                                       BY ACCESS                                BY ACCESS
DROP USER                                          BY ACCESS                                BY ACCESS
EXEMPT ACCESS POLICY                               BY ACCESS                                BY ACCESS
GRANT ANY OBJECT PRIVILEGE                         BY ACCESS                                BY ACCESS
GRANT ANY PRIVILEGE                                BY ACCESS                                BY ACCESS
GRANT ANY ROLE                                     BY ACCESS                                BY ACCESS
PROFILE                                            BY ACCESS                                BY ACCESS
PUBLIC DATABASE LINK                               BY ACCESS                                BY ACCESS
PUBLIC SYNONYM                                     BY ACCESS                                BY ACCESS
ROLE                                               BY ACCESS                                BY ACCESS
SYSTEM AUDIT                                       BY ACCESS                                BY ACCESS
SYSTEM GRANT                                       BY ACCESS                                BY ACCESS
USER                                               BY ACCESS                                BY ACCESS
CREATE JOB                                         BY SESSION                               BY SESSION

And you’re probably writing it into a database table [AUDIT_TRAIL=’DB’]

so how does that work if you open a Dataguard database read only? You are writing into sys.aud$ on the Primary, and that table is replicated to the Standby. So what happens?

From Oracle 11G, if you are running Dataguard and opening the standby up for read access, you may not notice the line in the ALERT log which reads:

AUDIT_TRAIL initialization parameter is changed to OS, as DB is NOT compatible for database opened with read-only access

So it starts writing down O/S audit trail files for all of your auditing options (well, the session connect and DML audit options – you can’t run DDL in a r/o DB). You might want to go and see just how many files it has written to [audit_file_dest], as you may be surprised at just how many are in there.

You may, one day, either run out of space or (more worryingly) have so many millions of files that it causes a performance problem when Oracle access the O/S directory. You might want to think about some sort of periodic clean-up job.

Sequences – where did the view go?

I was pottering around with the sequence cache recently, investigating a few things like exactly when it refreshes (such as if it is flushed or aged from the shared pool – pin it!) and I was monitoring the next value using V$_SEQUENCE, like I have been since, erm, Oracle 8i in 1999 or summit.

I happened to log into an Oracle 12.1.0.2 DB for a check-test and it was empty! My first reaction was, how come there are no sequences cached in the shared pool, shortly followed by No! The view doesn’t work any more!

Right, it’s a v$ view. (Actually, it’s a V$_ view, of which the only other one I know is V_$_lock). It is based upon c-memory-structure-plus-helper x$ “tables” (warning, these tables required tuning/diagnostic licensing packs to query!), so let’s query the underlying object view: x$kglob.

What used to happen pre-Oracle 12c:

SYS @ orcl > select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production

select test_seq.nextval from dual;
NEXTVAL
----------
40067

select sequence_owner,sequence_name,nextvalue,cache_size from v$_sequences where sequence_name = 'TEST_SEQ'
/
SEQUENCE_OWNER                 SEQUENCE_NAME                  NEXTVALUE  CACHE_SIZE
------------------------------ ------------------------------ ---------- ----------
NEIL                           TEST_SEQ                            40068         20

select KGLNAOBJ,
KGLOBTN0 next_val,
KGLOBTN4 cache_size,
kglhdkmk kept
from x$kglob
where KGLOBTYP=6
and KGLNAOBJ = 'TEST_SEQ'
/
KGLNAOBJ             NEXT_VAL   CACHE_SIZE KEPT
-------------------- ---------- ---------- ----------
TEST_SEQ                  40068         20          0

And now what happened in Oracle 12:

SQL> select * from v$version;
BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production 0

select test_seq.nextval from dual;
NEXTVAL
----------
3

select sequence_owner,sequence_name,nextvalue,cache_size from v$_sequences where sequence_name = 'TEST_SEQ';

no rows selected

select KGLNAOBJ,
KGLOBTN0 next_val,
KGLOBTN4 cache_size,
kglhdkmk kept
from x$kglob
where KGLOBTYP=6
and KGLNAOBJ = 'TEST_SEQ';

KGLNAOBJ NEXT_VAL CACHE_SIZE KEPT
-------------------- ---------- ---------- ----------
TEST_SEQ 0

So the values are no longer exposed in Oracle 12c. NEXT_VAL and CACHE_SIZE are empty.

Does anybody know where to get this value? It's no longer in any column in X$KGLOB. Have Oracle hidden it in some weird container mode I don't understand yet? Can anybody help, or am I going to have to attach the SGA with a C program and step through a bunch of linked lists?

Sequences – a quick guide and an unexpected quirk

Sequences on Oracle databases are simple, but there are some basic truths about sequences which need to be understood if you are going to use them, especially on RAC:

  1. Sequences will have “holes” in them, even if you specify NOCACHE (e.g. if you rollback your transaction).
  2. Don’t specify NOCACHE. It doesn’t scale.
  3. Sequences will NOT necessarily be, erm, sequential for your transactions. That will depend upon the COMMIT order and if you are using RAC.
  4. The higher the CACHE value, the bigger the holes but the more scalable the sequence will be become. You will lose the entire set of unused cache values across instance restart.
  5. Oracle stores the CACHE value on EACH RAC node, so for a cache of 100,000, NODE 1 gets 1-100,000, NODE 2 get 100,001-200,000 to play with, and so on.
  6. If you are using RAC, and you have a heavy workload on one node and a low number of transactions on another node, there may be a significant disparity between the NEXTVAL on each node. I have seen this disparity manifest in the tens of millions.
  7. There is a global cache lock which must be passed from node to node when getting a new cache of sequence numbers on that node. It is lightweight, but it is a serialisation point. If your CACHE value is too low for your throughput, you will get contention on this lock.
  8. Same as the serialisation point if you specify ORDERED, so you get the sequences advancing in step (rather than each node having its own cache) on multiple RAC nodes. Don’t do this either.
  9. The point if sequences is to give you a unique value for a (surrogate) Primary Key. You can rely upon sequences to be unique. Well. Mostly. As long as they do not CYCLE.
  10. If you use sequences for a PK, you will probably end up with a right-hand index with hot-block contention during inserts, causing you to implement something suboptimal, like hash partitioning or reverse-key indexes to get around your design flaw.

Sequences on RAC can behave in unexpected ways. The below example is a little contrived but shows a potentially unexpected behaviour caused by CYCLEing your sequences:

NODE 1 NODE 2
create sequence seq_1
start     with 1
increment by   1
maxvalue       10
cache          2
cycle;

Sequence created.

NODE1 :select seq_1.nextval from dual;
NEXTVAL: 1
NODE2 :select seq_1.nextval from dual;
NEXTVAL
3 <- Gets next value above cache on node 1
NODE1 : /
NEXTVAL: 2
NODE1 : /
NEXTVAL: 5 (skipping 3 and 4 on Node 2)
6
7
8
9
10
1
2
3  <- erm. Hang on. Node 2 has this!
4  <- and this!!!
5
6
7
8
9
10
1
NODE2 : /
NEXTVAL
4 <- Reading from the local cache
NODE2 : /
NEXTVAL
3 <- And get a new cache value

So, if you are using sequences and you can’t guarantee sequence and you can’t guarantee no gaps and, as this demo shows, you can’t guarantee uniqueness, and they cause hot block right hand index problems, tell me again why you are using them?

Because they are fast and you don’t CYCLE them.

However, sequences are becoming the new evil in the database, right behind triggers.

Goldengate: Problems with character sets

One complication that you may face with replicating data using Goldengate (or other tools) is when your source character set is different to your destination character set. This is particularly true when the source character set is UTF-8 and the destination is not.

If the application does not sanitise (or you do not want to sanitise) inputs to restrict them to the lowest common denominator within your systems, you will need to ensure that you take action to ensure the source data is fed appropriately to the destination systems.

I recently experienced this at a client, but the special measures taken by me to allow Oracle UTF-8 data into a SQL Server database using a standard Windows-1252 character set also hit a Goldengate codepath bug, recreated here:

The source table, TAB1, has 3 columns for these purposes:
 

 ID     number
 NAME   varchar2(50)
 COL_TS timestamp

 
The source table is allowed to contain NULLS, but the destination table must not, so a null value test is specified in the COLMAP in the REPLICAT.

To cope with the character set conversion, the parameter REPLACEBADCHAR SPACE is specified in the replication. This states that is there are ANY characters in the trail file which the destination database cannot store, then that character should be converted to (in this instance) a space.

REPLICAT snippet:

REPLACEBADCHAR SPACE
MAP SCHEMA_OWNER.TAB1, TARGET DBO.TAB1,
    COLMAP (USEDEFAULTS,
            NAME =@IF(@COLTEST(NAME, NULL), ' ' ,NAME));

 
All processing progressed nicely, with the NULLs entered into TAB1.NAME being converted into a single space, until an unexpected character was pasted into the screen on the source system and the REPLICAT abended:

2015-02-25 22:32:00 WARNING OGG-00869 Conversion from character set UTF-8 of source column @IF() to character set windows-1252 of target column NAME failed because the source column contains a character that is not available in the target character set.
2015-02-25 22:32:00 WARNING OGG-01503 Aborting BATCHSQL transaction. Mapping error.
2015-02-25 22:32:01 WARNING OGG-01137 BATCHSQL suspended, continuing in normal mode.
2015-02-25 22:32:01 WARNING OGG-01003 Repositioning to rba 123 in seqno 2.
2015-02-25 22:32:01 WARNING OGG-00869 Conversion from character set UTF-8 of source column @IF() to character set windows-1252 of target column NAME failed because the source column contains a character that is not available in the target character set.
2015-02-25 22:32:01 WARNING OGG-01431 Aborted grouped transaction on 'dbo.TAB1', Mapping error.
2015-02-25 22:32:01 WARNING OGG-01003 Repositioning to rba 123 in seqno 2.
2015-02-25 22:32:01 WARNING OGG-01151 Error mapping from SCHEMA_OWNER.TAB1 to dbo.SUP_TAB1.
2015-02-25 22:32:01 WARNING OGG-01003 Repositioning to rba 123 in seqno 2.

Source Context :
SourceModule : [er.errors]
SourceID : [er/errors.cpp]
SourceFunction : [take_rep_err_action]
SourceLine : [682]
ThreadBacktrace : [12] elements
: [Z:\gg12\gglog.dll(?CreateMessage@CMessageFactory@@QEAAPEAVCMessage@@PEAVCSourceContext@@IZZ+0x886) [0x000007FEF00809D6]]
: [Z:\gg12\gglog.dll(?_MSG_ERR_MAP_TO_TANDEM_FAILED@@YAPEAVCMessage@@PEAVCSourceContext@@AEBV?$CQualDBObjName@$00@ggapp@gglib@ggs@@1W4MessageDisposition@CMessageFactory@@@Z+0x81) [0x000007FEF0043631]]
: [Z:\gg12\replicat.exe(ERCALLBACK+0x733c) [0x000000013F6E96BC]]
: [Z:\gg12\replicat.exe(ERCALLBACK+0x2fe7a) [0x000000013F7121FA]]
: [Z:\gg12\replicat.exe(ERCALLBACK+0x6a575) [0x000000013F74C8F5]]
: [Z:\gg12\replicat.exe(_ggTryDebugHook+0xea23) [0x000000013F7F2323]]
: [Z:\gg12\replicat.exe(_ggTryDebugHook+0xe000) [0x000000013F7F1900]]
: [Z:\gg12\replicat.exe(_ggTryDebugHook+0xe8cd) [0x000000013F7F21CD]]
: [Z:\gg12\replicat.exe(ERCALLBACK+0x6a5f9) [0x000000013F74C979]]
: [Z:\gg12\replicat.exe(CommonLexerNewSSD+0xc0d2) [0x000000013F8862D2]]
: [C:\Windows\system32\kernel32.dll(BaseThreadInitThunk+0xd) [0x00000000773B652D]]
: [C:\Windows\SYSTEM32\ntdll.dll(RtlUserThreadStart+0x21) [0x00000000774EC541]]

2015-02-25 22:32:01 ERROR OGG-01296 Error mapping from SCHEMA_OWNER.TAB1 to dbo.TAB1.

Looking in the Goldengate discard file (always a good place to start when you have a GG problem), you can see the problem character “ef bf bd”:

Oracle GoldenGate Delivery for SQL Server process started, group REP_SQL discard file opened: 2015-02-11 22:02:42.389000
Mapping error to target column: NAME
Mapping error to target column: NAME
Mapping error to target column: NAME
Current time: 2015-02-25 22:32:01
Discarded record from action ABEND on error 0

Aborting transaction on ./dirdat/NC beginning at seqno 2 rba 123
error at seqno 2 rba 123
Problem replicating SCHEMA_OWNER.TAB1 to dbo.TAB1
Mapping problem with insert record (source format)...
*
ID = 123
000000: 31 32 33  |123     |

NAME = NEIL \uFFFD CHA
000000: 4E 45 49 4C 20 ef bf bd 20 43 48 41 | NEIL ... CHA|

COL_TS = 2015-02-25:22:31:56.303000000
000000: 32 30 31 35 2d 30 32 2d 32 35 3a 32 32 3a 33 31 |2015-02-25:22:31|
000010: 3a 35 36 2e 33 30 33 30 30 30 30 30 30          |:56.303000000   |
*

Process Abending : 2015-02-25 22:32:01

So, why didn’t REPLACEBADCHAR catch this and turn the offending character into a space? There’s a clue in the ABEND report information

WARNING OGG-00869 Conversion from character set UTF-8 of source column @IF() to character set windows-1252 

The column is referred-to as @IF(), not as NAME. A quick scan of MOS show that this appears to be BUG 19818362 “Column function execution was happening internally under NOCHARSETCONVERSION cases” – it’s going through the wrong codepath for REPLCEBADCHAR to work. And this bug fix was released 10 days before this problem was encountered. Result!

The short-term fix? Remove the data manipulation from the REPLICAT

REPLACEBADCHAR SPACE
MAP SCHEMA_OWNER.TAB1, TARGET DBO.TAB1,
    COLMAP (USEDEFAULTS);

 

Start and run the REPLICAT until past the problem, then revert the REPLICAT back to data manipulation until either Goldengate is patched and tested or we have to repeat this exercise due to another Unicode character problem occurs.

Follow

Get every new post delivered to your Inbox.

Join 35 other followers

%d bloggers like this: