GoldenGate: Steps of Resynchronizing a Table

In the target, Replicat process was abended due to the following error:

2013-06-17 10:17:30  ERROR   OGG-01163  Oracle GoldenGate Delivery for Oracle, rtdnfrd.prm:  Bad column length (3) specified for column TAX_AUTHORITY_TYPE in table WAX_TDN.V_TAX_DETAILS, maximum allowable length is 1.

I checked TAX_AUTHORITY_TYPE colume, it is char(1) in both source and target databases, so not sure how this error comes up. I have to comment it out in the Replicat parameter file in order to re-start the process. As a result, the table is out-of-sync. This actually gives me a chance to pratice steps of resynchronizing a table in GoldenGate for the first time.

I took the following steps:

1. Comment out the table in the Replicat parameter file
Already did.


2. Stop Replicat and start it again so that it continues for unaffected tables
Already did.

3. Record the time stamp on the source system
2013-06-17 13:49

4 Start a copy of the source data for the affected tables
 
Note: before making the copy, try to resolve any long-running transactions

5. Import the copy to the target table

6. Create a new Replicat group for the out-of-sync table, using Begin to start at the source time stamp that you recorded earlier and using the existing train for ExtTrain

Add Replicat  rvtd, ExtTrail ./dirdat/rb, Begin 2013-06-17 13:49:00

7. Create the new parameter file so that it includes 
  HandleCollisions

replicat rvtd
SETENV(ORACLE_HOME="/apps/opt/oracle/product/11.2.0/db_1")
SETENV(ORACLE_SID = "tdnprdfd1")
SETENV(NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
-- DBOPTIONS SUPPRESSTRIGGERS
userid gg_owner@tdnprdfd1, password AADAAAAAAAAAAAHAJIKGFGKGRJTBJBCIZGUERJNHBFPBLCAEOBUADFWASJMJCDWEICWGBEGHOIRESCPA, encryptkey securekey1
discardfile ./dirrpt/RTDNFRD.dsc, Append, megabytes 1
handlecollisions
assumetargetdefs
MAP WAX_TDN.V_TAX_DETAILS,             TARGET WAX_TDN.V_TAX_DETAILS;

8. Start the new Replicat

9. View the new Replicat's lag until it shows "At EOF, no more records to process"
GGSCI> Send replicat rvtd, GetLag

10. Turn off HandleCollisions in the new replicate with
GGSCI> send replicat rvtd NoHandleCollisions

11. Edit the parameter file to comment out or remove HandleCollisions if you ever bounce the process later
(The next steps merge the table back with the others so that only one Replicat group is needed

12. Stop Extract at source

13. View both Replicats's lag until you see "EOF" again

GGSCI> Send replicat rtdnfrd, GetLag
GGSCI> Send replicat rvtd, GetLag

14 stop both Replicats

15. Uncomment the resynced table in the original Replicat parameter file

16. Start Extract

17. Start the original Replicat:
GGSC> start rtdnfrd

18. Delete the new Replicat that you created:
GGSCI> delete replicat rvtd

All done!

GoldenGate Replicat Process Abending due to Tablespace Full and Discard File Exceeding Max Bytes

We have a cron job set up to monitor errors in the GoldenGate ggserr.log. This monrining, in a target database, we recieved:

< 2013-06-17 02:58:17  ERROR   OGG-01172  Oracle GoldenGate Delivery for Oracle, rvasip.prm:  Discard file (./dirrpt/RVASIP.dsc) exceeded max bytes (1000000).
< 2013-06-17 02:58:17  ERROR   OGG-01668  Oracle GoldenGate Delivery for Oracle, rvasip.prm:  PROCESS ABENDING.

It appeared that Replicat process abended due to  Discard file exceeded max bytes.
Discard file is used by GoldenGate to log records it cannot proccess. The maximum size of the discard file can be specified by MAXBYTES or MEGABYSTS options,
the defaults are 1000000 or 1MB.  If the specified size is exceeded, the process will abend.

Further troubleshooting showed the reason for the discard file filled up was due to a tablespace filled up in this case. In the RVASIP.dsc files we can found:

OCI Error ORA-01653: unable to extend table PPOWNER.VZ_JNR_FEED_TRX_LOG by 8192 in tablespace PPOWNER_DATA_1 (status = 1653). INSERT INTO "PPOWNER"."VZ_JNR_FEED_TRX_LOG" ("ID","JOURNAL_ID"
,"TRX_EXT_ID","BILLED","DB_MODIFICATION_DATE","DB_CREATION_DATE","SUB_TRX_ID","TRX_ID","CHG_ATTR","ACCESS_TYPE") VALUES (:a0,:a1,:a2,:a3,:a4,:a5,:a6,:a7,:a8,:a9)
Aborting transaction on ./dirdat/rt beginning at seqno 21275 rba 3795007
                         error at seqno 21275 rba 5169654

To fix the problem, I renamed RAVSIP.dsc, changed the max bytes of discard file to be 10MB in the parameter file:

discardfile ./dirrpt/RVASIP.dsc, Append,megabytes 10

Then I stopped and started the Replicat process. I have verified that those discarded dmls recorded in the discard file have been applied after Replicat process re-started. no manual intervene is ndeed.

Tuning a Hierarchical Query

Encountered an expensive production sql today, basically it is in the following structure:

select BM.*, V.*  
FROM 
   BMXYZ BM,
   VXYZ V,
   BBXYZ BB
WHERE 
  V.BO_ID=BM.BO_ID
 AND BM.VOL_PARENT_BO_ID IN(SELECT B.VOL_PARENT_BO_ID
                            FROM BMXYZ B
                        START WITH BB.BO_ID=B.VOL_PARENT_BO_ID
                CONNECT BY PRIOR B.BO_ID = B.VOL_PARENT_BO_ID )
AND BB.USER_ID='xyzuvw'
AND V.CONTENT_VENDOR_ID='3000000'
;

At the first glance, it seems there are no join conditions involving BB. Finally I was able to understand what the sql tries to do :

(1) obtain a set of BO_ID's from table BB
(2) for each BO_ID in the set, find all child rows of it from the table BM
(3) finally row source from (2) join table V.

After rewriting it as follows, the query run much faster with only hundreds gets:

select BM.*, V.*  
FROM 
   BMXYZ BM,
   VXYZ V
WHERE 
  V.BO_ID=BM.BO_ID
 AND BM.VOL_PARENT_BO_ID IN(SELECT B.VOL_PARENT_BO_ID
                            FROM BMXYZ B, (select bo_id from BBXYZ  where user_id='xyzuvw') BB
                        START WITH BB.BO_ID=B.VOL_PARENT_BO_ID
                CONNECT BY PRIOR B.BO_ID = B.VOL_PARENT_BO_ID )
AND V.CONTENT_VENDOR_ID='3000000'
;

A Case of Changing or Creating OSDBA and OSOPER Groups

Goal

Created an Unix user (e.g. tier1 ) in the Oracle database server that can perform some database administration tasks but cannot be used to view or modify any application data.


Current typical Unix/Liux Environment:

- OS user: "oracle" is the Oracle software owner and its primary group is "dba" (i.e. Oracle database files have ownership oracle:dba )

- Assuming OSDBA group is "dba" (i.e. every os user that belongs to "dba" group can login as sysdba and view any data)

Suggested changes


- add a Unix group "oper" if not exist
# /usr/sbin/groupadd -g 503 oper

- add a Unix group "dbax" if not exist
# /usr/sbin/groupadd -g 504 dbax

- Change OSDBA group to "dbax" and define OSOPER group in $ORACLE_HOME/rdbms/lib/config.c, edit and ensure the following:
#define SS_DBA_GRP "dbax"
#define SS_OPER_GRP "oper"
shutdown instance and relink:
  cd $ORACLE_HOME/rdbms/lib/
   mv config.o config.o.orig
    make -f ins_rdbms.mk ioracle

- assign "dbax" as secondary unix group to "oracle"
# /usr/sbin/usermod -g dba -G dbax oracle

- create tier1 user with priamry group as "dba" and secondry group as "oper" # /usr/sbin/useradd -g dba -G oper tier1

Test cases


Test case 1 - "oracle" user can login as sysdba as it belongs to OSDBA group:dbax

[oracle@localhost ~]$ id
uid=500(oracle) gid=54322(dba) groups=504(dbax),54322(dba)
[oracle@localhost ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.2.0 Production on Thu May 9 13:37:51 2013

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

SQL> conn / as sysdba
Connected.
SQL> shutdown 
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup 
ORACLE instance started.

Total System Global Area  456146944 bytes
Fixed Size                  1344840 bytes
Variable Size             390073016 bytes
Database Buffers           58720256 bytes
Redo Buffers                6008832 bytes
Database mounted.
Database opened.
SQL>

Test case 2 - "tier1" user can login as sysoper but not as sysdba

[tier1@localhost ~]$ id
uid=502(tier1) gid=54322(dba) groups=54322(dba),54324(oper)
[tier1@localhost ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.2.0 Production on Thu May 9 13:50:41 2013

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

SQL> conn / as sysdba
ERROR:
ORA-01031: insufficient privileges


SQL> conn / as sysoper
Connected to an idle instance.
SQL> startup 
ORACLE instance started.
Database mounted.
Database opened.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

Test case 3 - "tier1" and "oracle" belong to same primary OS group "dba", so "tier1" can operate on various Oracle files based on group privillege:

[tier1@localhost 2013_05_09]$ id
uid=502(tier1) gid=54322(dba) groups=54322(dba),54324(oper)
[tier1@localhost 2013_05_09]$ ls -lh o1_mf_1_401_8rr62tq0_.arc
-rw-rw---- 1 oracle dba 7.2M May  9 14:42 o1_mf_1_401_8rr62tq0_.arc
[tier1@localhost 2013_05_09]$ mv o1_mf_1_401_8rr62tq0_.arc /tmp
[tier1@localhost 2013_05_09]$ ls -lh o1_mf_1_401_8rr62tq0_.arc
ls: o1_mf_1_401_8rr62tq0_.arc: No such file or directory

Problems of Temporary Tablespace Usage after a Recent Release

After a recent release, we’ve encountered two problems in a production database:  (1) Temporary tablespace usage high; (2) Instance shutdown when temporary tablespace space used up.
 
Problem 2 turns out to be an Oracle feature:

 

Root cause of crash appears to be "Bug" 7691270 - Crash the DB in case of write errors (rather than just offline files) [ID 7691270.8]

Datafile Write Errors (Oracle Database 11gR1 to 11gR2 Change):

Starting with the 11.2.0.2 patchset for Oracle Database 11g Release 2, a write error to any data file will cause the database to perform a shutdown abort of the instance.

In prior releases, I/O errors to datafiles not in the system tablespace would offline the respective datafiles when the database is in archivelog mode. This behavior is not always desirable. Some customers would prefer that the instance crash due to a datafile write error. A new hidden parameter called _datafile_write_errors_crash_instance has been introduced to control whether the instance should crash on a write error or if the datafiles should be taken offline on a write error.

If _datafile_write_errors_crash_instance = TRUE (default) then any write to a datafile which fails due to an IO error causes an instance crash.
If _datafile_write_errors_crash_instance = FALSE then the behavior reverts to the previous behavior (before this fix) such that a write error to a datafile offlines the file (provided the DB is in archivelog mode and the file is not in SYSTEM tablespace in which case the instance is aborted)

Best Practices: Monitor temporary tablespace usage with alerts, tune SQL to use less temporary space.

 

The increasing usage of temporary space after the release was due to the increasing usage of temporary lobs, which reside in the temporary tablespace. For example, there were 30+ queries using to_clob function in the release. Oracle won’t release temp lob space in the Weblogic connection unless we recycle the Weblogic app servers. There are several related MOS notes about this behavior:
 

• How to Release the Temp LOB Space and Avoid Hitting ORA-1652 (Doc ID 802897.1)
• How to Release Temporary LOB Segments without Closing the JDBC Connection (Doc ID 1384829.1)
• Bug 5723140 - Temp LOB space not released after commit (Doc ID 5723140.8)
• Temporary LOB space not released after commit: ora-1652 being hit (Doc ID 750209.1)

 

In the above notes, the fix to the temp lob space releasing problem is to set 60025 event. Below is a simple test to demonstrate this event works:

In Session 1, execute the following statement

select to_clob(text) from dba_source where rownum <100;



In session 2 monitoring session 1 temp space usage with:

SELECT   S.sid || ',' || S.serial# sid_serial,
         S.username,
         S.osuser,
         P.spid,
         S.module,
         S.machine,
         S.program,
         SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used,
         T.tablespace,
         T.segtype,
         COUNT(*) sort_ops
FROM     v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE    T.session_addr = S.saddr
AND      S.paddr = P.addr
and    s.sid=2927   --- this is the sid of session 1
AND      T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
         S.machine, S.program, TBS.block_size, T.tablespace,T.segtype 
ORDER BY mb_used 
/


You will see even after session 1 finish and idle there, in session 2 you will have 1MB temp space allocated to it


Second test:

In session 1, before run that query do:
alter session set events '60025 trace name context forever';


Then in session 2, you will see after session 1 becomes idle, no temp space allocated.

We did not set this event in our production to fix the issue, instead, dev team change the code and avoid use temporary lob. To demonstrate difference of the temp space usage before and after release and after the fix, I queried dba_hist_active_sess_history view.  From 11g there is a field called TEMP_SPACE_ALLOCATED in this view. So it is possible to calculate the average temp space allocated per active session during a certain period of time:

 

===== Look at all active session between 16:00 ~ 16:59 in past 30 days in dba_hist_active_sess_history
select stime, size_m/c avg_mb_sess from
(
  select to_char(sample_time, 'YYYY-MM-DD HH24') stime,count(*) c, round(sum(TEMP_SPACE_ALLOCATED)/1024/1024) size_m
  from dba_hist_active_sess_history
  where to_char(sample_time, 'HH24') = 16 and sample_time > sysdate-30
  group by to_char(sample_time, 'YYYY-MM-DD HH24')
)
order by 1

STIME         AVG_MB_SESS
------------- -----------
2013-02-26 16   .00135459
2013-02-27 16  .002029264
2013-02-28 16  .000963298
2013-03-01 16  .000670166
2013-03-02 16  .001373417
2013-03-03 16   .00049838
2013-03-04 16  .000991616
2013-03-05 16  1.49810976
2013-03-06 16  1.12322684
2013-03-07 16  .000634719
2013-03-08 16  .001408054
2013-03-09 16  .081960706
2013-03-10 16  .000473485
2013-03-11 16  1.21929313
2013-03-12 16  .001113121
2013-03-13 16  .001084893
2013-03-14 16  .000864518
2013-03-15 16  1.70157371
2013-03-16 16  .114989041
2013-03-17 16  231.198656
2013-03-18 16  254.193626
2013-03-19 16  96.2858973
2013-03-20 16   93.731513
2013-03-21 16  135.284032
2013-03-22 16    1.462451
2013-03-23 16  .000423101
2013-03-24 16  .000462321
2013-03-25 16  .000395163
2013-03-26 16  16.1351924
2013-03-27 16  1.84635633

The release was happened on Saturday night of Mar 16 and Dev team fixed code on Mar 21 night completely. It can be seen, from Mar 17 till Mar 21, the average temp space allocated per active sessions were much higher compared to those before release and after the fix. During Mar 17 and Mar 21, what we did was that once the total temp space reached 150 GB, we recycled the app servers  in a controlled manner to release the temp space.