Sunday, 13 April 2014

GoldenGate - Add new table to existing Replication setup

###########################################################
Source -  SOURCE.TABLE                          Target -  TARGET.TABLE
###########################################################

STEP 1 (TARGET): Create table on Target using dblink or export import or DDL and DML statements. (Initial Load)

SQL> create table TARGET.TABLE as select * from SOURCE.TABLE@dblink_to_src;

Table created.

SQL> select count(*) from TARGET.TABLE;

  COUNT(*)
----------
        24

SQL> select count(*) from SOURCE.TABLE@dblink_to_src;

  COUNT(*)
----------
        24

STEP 2 (SOURCE): Add Trandata for table which needs to be replicated

GGSCI (SOURCE) 2> dblogin userid system, password tiger
Successfully logged into database.

GGSCI (SOURCE) 4> add trandata SOURCE.TABLE

Logging of supplemental redo data enabled for table SOURCE.TABLE.

GGSCI (SOURCE) 5> info trandata SOURCE.TABLE

Logging of supplemental redo log data is enabled for table SOURCE.TABLE


STEP 3 (SOURCE): Take a backup of parameter file

-rw-r--r--   1 oracle     oinstall      4813 Jan  2 14:58 extsrc.prm_bkp
-rw-r--r--   1 oracle     oinstall      4854 Apr  9 09:20 extsrc.prm


STEP 4 (SOURCE): Added table name to parameter file and check the difference between the backup and current param files.

oracle@SOURCE:/u02/GG/source/dirprm$ diff extsrc.prm_bkp extsrc.prm

> TABLE SOURCE.TABLE;


STEP 5 (SOURCE): Create a new definition file by adding a new table to the defgen param file.

oracle@SOURCE:/u02/GG/source/dirdef$ mv proddefgen.def proddefgen.def_bkp   --- rename the existing .def file to avoid file exist error.
oracle@SOURCE:/u02/GG/source$ cp -pr proddefgen.prm proddefgen.prm_bkp

added table entry to defgen param file

oracle@SOURCE:/u02/GG/source$ diff proddefgen.prm proddefgen.prm_bkp

< TABLE SOURCE.TABLE;


STEP 6 (SOURCE) : create a new defnition file using defgen utility

oracle@SOURCE:/u02/GG/source$ ./defgen paramfile proddefgen.prm

***********************************************************************
        Oracle GoldenGate Table Definition Generator for Oracle
     Version 11.1.1.1.2 OGGCORE_11.1.1.1.2_PLATFORMS_111004.2100
  HP/UX, IA64, 64bit (optimized), Oracle 10g on Oct  5 2011 00:39:52

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


                    Starting at 2014-04-09 09:34:41
***********************************************************************
.
.
.
.
.
.
TABLE SOURCE.TABLE;
Retrieving definition for SOURCE.TABLE


STEP 7 (SOURCE): Check whether the definition got generated for new table.

oracle@SOURCE:/u02/GG/source$ diff proddefgen.def_bkp proddefgen.def

< * Definitions created/modified  2014-01-09 22:39
---
> * Definitions created/modified  2014-04-09 09:34
> *
> Definition for table SOURCE.TABLE
> Record length: 56
> Syskey: 0
> Columns: 3
> EVENT_ID     134     11        0  0  0 1 0      8      8      8 0 0 0 0 1    0 1 3
> PKUSERID     134     11       12  0  0 1 0      8      8      8 0 0 0 0 1    0 0 3
> PKTIMESTAMP  192     29       24  0  0 1 0     29     29     29 0 6 0 0 1    0 0 0


STEP 8 (SOURCE): Copy the new definition file to TARGET location.

oracle@SOURCE:/u02/GG/source$ scp proddefgen.def TARGET:/u02/GG/target/dirdef
Password:
proddefgen.def                           100%  154KB 153.5KB/s   00:00


STEP 9 (TARGET): Add Mapping to the Replicat Param file.

oracle@TARGET:/u02/GG/target/dirprm$ diff reptgt.prm reptgt.prm_bkp

< MAP SOURCE.TABLE, TARGET TARGET.TABLE;

oracle@TARGET:/u02/GG/target/dirprm$


STEP 10 (SOURCE) : STOP EXTRACT <EXTRACT NAME>
                                          : START EXTRACT <EXTRACT NAME>

STEP 11 (TARGET) : STOP REPLICAT <REPLICAT NAME>
                                          : START REPLICAT <REPLICAT NAME>


STEP 12 (SOURCE) : If pump process is configured for the extract, dont forget to add the table name to pump process param file.



If you do not have primary/Unique key constraints on any of the columns you may receive this error during update statements.


Expected Errors and resolution.

To avoid this error use APPLYNOOPUPDATES key word on Replicat param file.

2014-04-11 10:25:10  ERROR   OGG-01168  Oracle GoldenGate Delivery for Oracle, reptgt.prm:  Encountered an update for target table TARGET.TABLE, 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-04-11 10:25:10  ERROR   OGG-01668  Oracle GoldenGate Delivery for Oracle, reptgt.prm:  PROCESS ABENDING.

Sunday, 30 March 2014

Performance Issues – Get SQL and session details with OS process ID

Here are some handy queries, which are helpful to find out the performance killing sessions.

It will be easy for DBA's to get the culprit process with a single query. Here are few for you.

-- Get SID with PID

set lines 200 pages 5000
col sid format 999999
col username format a20
col osuser format a15
select b.spid,a.sid, a.serial#,a.username, a.osuser
from v$session a, v$process b
where a.paddr= b.addr
and b.spid='&spid'
order by b.spid;


SPID             SID    SERIAL# USERNAME             OSUSER
------------ ------- ---------- -------------------- ---------------
7887            1518      31396 TEST_USER           SYSTEM


-- Get Details for SID

set lines 200 pages 5000
col program for a25
col event for a40
select a.sid, a.serial#, a.status, a.program, b.event,to_char(a.logon_time, 'dd-mon-yy hh24:mi') LOGON_TIME,
to_char(Sysdate, 'dd-mon-yy--hh24:mi') CURRENT_TIME, (a.last_call_et/3600) "Hrs connected" from v$session a,
v$session_wait b where a.sid=&sid and a.sid=b.sid;

     SID    SERIAL# STATUS   PROGRAM               EVENT                      LOGON_TIME            CURRENT_TIME           Hrs connected
------- ---------- -------- --------------------- -------------------------- --------------------- ---------------------- -------------
   1518      31396 ACTIVE   JDBC Thin Client      latch: library cache       14-mar-14 15:31       19-mar-14--11:43              110.82



 -- Identifying Sever Machine and Client Machine names of a session

col server_machine for a15
col client_machine for a15
select p.spid server_pid, s.sid oracle_sid, s.machine client_machine,i.host_name server_machine
from gv$session s, gv$process p, gv$instance I
where
p.inst_id = s.inst_id and
p.addr = s.paddr and
i.inst_id = s.inst_id and
p.spid = '&server_pid';


SERVER_PID   ORACLE_SID CLIENT_MACHINE  SERVER_MACHINE
------------ ---------- --------------- ---------------
7887               1518 prodweb20b      prodinst5

-- Identifying Sever Machine and Client Machine names, SQL details  of a session

set head on
set lin 700
col "SQL TEXT" for a100
col module format a20

SELECT s.sid, p.spid "OS Pid", s.module, s.process, s.username "Username",
s.osuser "OS User", s.program "Program", a.sql_id, substr(a.sql_text,1,550) "SQL Text"
FROM v$session s, v$sqlarea a, v$process p
WHERE s.sql_hash_value = a.hash_value (+)
AND s.sql_address = a.address (+)
AND s.paddr = p.addr
and s.sid = (select s.sid from v$session s, v$process p where s.paddr = p.addr and p.spid = &p);

    SID OS Pid       MODULE               PROCESS      Username             OS User     Program               SQL_ID        SQL Text
------- ------------ -------------------- ------------ -------------------- ----------- --------------------- ------------- ------------------------------------------------------------------------------------------------------------------------------------------------------
   1518 7887         JDBC Thin Client     1234         TEST_USER           SYSTEM      JDBC Thin Client      aqdb7xr4cm72h DELETE TEST_RECORDS WHERE EVENTID = :B3 AND ACTID = :B2 AND INVDATE = :B1


Thursday, 27 February 2014

Optimize Oracle UNDO Parameters


Optimize Oracle UNDO Parameters



Overview 

Starting in Oracle9i, rollback segments are re-named undo logs. Traditionally transaction undo information was stored in Rollback Segments until a commit or rollback statement was issued, at which point it was made available for overlaying. 

Best of all, automatic undo management allows the DBA to specify how long undo information should be retained after commit, preventing "snapshot too old" errors on long running queries.
This is done by setting the UNDO_RETENTION parameter.  The default is 900 seconds (5 minutes), and you can set this parameter to guarantee that Oracle keeps undo logs for extended periods of time.
Rather than having to define and manage rollback segments, you can simply define an Undo tablespace and let Oracle take care of the rest. Turning on automatic undo management is easy.  All you need to do is create an undo tablespace and set UNDO_MANAGEMENT = AUTO.
However it is worth to tune the following important parameters
1.      The size of the UNDO tablespace
2.      The UNDO_RETENTION parameter 

Calculate UNDO_RETENTION  for given UNDO Tabespace
You can choose to allocate a specific size for the UNDO tablespace and then set the UNDO_RETENTION parameter to an optimal value according to the UNDO size and the database activity. If your disk space is limited and you do not want to allocate more space than necessary to the UNDO tablespace, this is the way to proceed. The following query will help you to optimize the UNDO_RETENTION parameter:

http://www.akadia.com/img/optimal_undo_retention.gif
 Because these following queries use the V$UNDOSTAT statistics, run the queries only after the database has been running with UNDO for a significant and representative time!
Actual Undo Size
 
SELECT SUM(a.bytes) "UNDO_SIZE"
  FROM v$datafile a,
       v$tablespace b,
       dba_tablespaces c
 WHERE c.contents = 'UNDO'
   AND c.status = 'ONLINE'
   AND b.name = c.tablespace_name
   AND a.ts# = b.ts#;
UNDO_SIZE
----------
  209715200

Undo Blocks per Second

SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
    
 "UNDO_BLOCK_PER_SEC"
  FROM v$undostat;
UNDO_BLOCK_PER_SEC
------------------
        3.12166667
 
DB Block Size 

SELECT TO_NUMBER(value) "DB_BLOCK_SIZE [KByte]"
 FROM v$parameter
WHERE name = 'db_block_size';
DB_BLOCK_SIZE [Byte]
--------------------
                4096

Optimal Undo Retention

209'715'200 / (3.12166667 * 4'096) = 16'401 [Sec]

Using Inline Views, you can do all in one query!

SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
       SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
       ROUND((d.undo_size / (to_number(f.value) *
       g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]"
  FROM (
       SELECT SUM(a.bytes) undo_size
          FROM v$datafile a,
               v$tablespace b,
               dba_tablespaces c
         WHERE c.contents = 'UNDO'
           AND c.status = 'ONLINE'
           AND b.name = c.tablespace_name
           AND a.ts# = b.ts#
       ) d,

       v$parameter e,
       v$parameter f,
       (
       SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
              undo_block_per_sec
         FROM v$undostat
       ) g

WHERE e.name = 'undo_retention'
  AND f.name = 'db_block_size'
/
ACTUAL UNDO SIZE [MByte]
------------------------
200

UNDO RETENTION [Sec]
--------------------
10800

OPTIMAL UNDO RETENTION [Sec]
----------------------------
16401

Calculate Needed UNDO Size for given Database Activity
If you are not limited by disk space, then it would be better to choose the UNDO_RETENTION time that is best for you (for FLASHBACK, etc.). Allocate the appropriate size to the UNDO tablespace according to the database activity: 

 
Again, all in one query:

SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
       SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
       (TO_NUMBER(e.value) * TO_NUMBER(f.value) *
       g.undo_block_per_sec) / (1024*1024)
      "NEEDED UNDO SIZE [MByte]"
  FROM (
       SELECT SUM(a.bytes) undo_size
         FROM v$datafile a,
              v$tablespace b,
              dba_tablespaces c
        WHERE c.contents = 'UNDO'
          AND c.status = 'ONLINE'
          AND b.name = c.tablespace_name
          AND a.ts# = b.ts#
       ) d,
      v$parameter e,
       v$parameter f,
       (
       SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
         undo_block_per_sec
         FROM v$undostat
       ) g
 WHERE e.name = 'undo_retention'
  AND f.name = 'db_block_size'
/
ACTUAL UNDO SIZE [MByte]
------------------------
200
UNDO RETENTION [Sec]
--------------------
10800
NEEDED UNDO SIZE [MByte]
------------------------
131.695313

The previous query may return a "NEEDED UNDO SIZE" that is less than the "ACTUAL UNDO SIZE". If this is the case, you may be wasting space. You can choose to resize your UNDO tablespace to a lesser value or increase your UNDO_RETENTION parameter to use the additional space.