ODI - Change Data Capture

About

The goal of Change Data Capture is to track change in the source data. When running integration interface, ODI-EE can reduce the volume of source data processed in the flow by extracting only the changed data.

Reducing the volume of source data is useful in many field such as:

Synchronization/Replication

These changes are captured by Oracle Data Integrator and transformed into events that are propagated throughout the information system.

Changes tracked by Changed Data Capture constitute data events. The ability to track these events and process them regularly in batches or in real time is key to the success of an event-driven integration architecture.

Changed Data Capture is performed by journalizing models. Journalizing a model consists of setting up the infrastructure to capture the changes (inserts, updates and deletes) made to the records of this model's datastores.

Oracle Data Integrator supports two journalizing modes:

Simple Journalizing tracks changes in individual datastores in a model.

Consistent Set Journalizing tracks changes to a group of the model's datastores, taking into account the referential integrity between these datastores. The group of datastores journalized in this mode is called a Consistent Set.

 

Hope this Helps! Rupam

Oracle Active Data Guard

How Oracle Active Data Guard can help your organization ?

Check this Video on Youtube

RAC Infrastruture Guildlines

Dell RH Linux non-critical db servers:
lnx521 and lnx522 (dev)
lnx523 and lnx524 (UAT)
lnx525 and lnx526 (prod)


Storage:
OCR and Voting Disks: multiplexed on 2 – 13 GB luns
Data: allocated in 100GB lun chunks. (current sum of target dev/UAT db’s for
Linux: approx 375GB/650GB)
Recovery: 100GB lun each for dev/UAT


Naming Standards
Server: lnx###
Storage
OCR: OCR1, OCR2
Voting Disk: VOTE1, VOTE2, VOTE3
ASM
Disk Group: ORADATA1 and ORAFLASH1
Disks: oradatad###

Oracle Software Install
/ora01/oracle/product/crs_ CRS $ORACLE_HOME
/ora01/oracle/product/asm_ ASM $ORACLE_HOME
/ora01/oracle/product/db_ Database $ORACLE_HOME

Cluster Name (must be unique across enterprise)
CLDEV##, CLUAT##, CLPROD##

Global DB Name: xxx[D,T,P]
Instance Name: xxx[D,T,P][#]



Install Standards/Database creation
Password file – use file created by default
Companion disk – install in database $OH
dbca – use for db creation – (CSX_Default template will be created)
pfile – located in $OB/admin/SID/pfile (one line referencing spfile), link to $OH/dbs
spfile – on ASM (backup pfile kept in $OB location tbd)
Database Control - do not install/configure (Grid control will be used)
ASM instance sys/system passwords – kept in sync with dev/prod database sys/system passwords
Datafiles: created in ORADATA1 diskgroup
Controlfiles: created in ORADATA1 and ORAFLASH1 diskgroups
Redo logs: created in ORADATA1 and ORAFLASH1 diskgroups
Archive logs: created in ORAFLASH1 diskgroup


Suggested standards/will require follow-up:

ASMlibs created on /opt/oracle/extapi – local rpm package install common to scripts
directory – location cannot be changed – create links for all common directories
currently in use on /opt/oracle

CRS, ASM and database $OH – create on local storage - need to determine any
backup/DR implications/revisions to current standard

/oramisc01 – create on local storage <=50GB partition – need to determine any
backup/DR implications/revisions to current standard

Root access needed for cluster install – will need to develop procedure keeping
with Sarbanes Oxley requirements

Voting Disk cheat Sheet

Voting disk

dd if=voting_disk_name of=backup_file_name
dd if=backup_file_name of=voting_disk_name


crsctl query css votedisk - lists the voting disks used by CSS
crsctl add css votedisk - adds a new voting disk
crsctl delete css votedisk - removes a voting disk

OCR Cheatsheet

Restore OCR from Backup

Connect as root and from the OCR backup directory choose the last backup before the problem started

[root@vmractest1]# cd /vmasmtest/BACKUP/OCR/
[root@vmractest1]# ls -ltr
total 458328
-rw-r--r-- 1 oracle dba 156279808 Nov 21 16:48 OCR_21_nov_06_backup1
-rw-r--r-- 1 oracle dba 156279808 May 3 16:39 OCR_03_may_07
-rw-r--r-- 1 oracle dba 156279808 May 16 16:06 OCR_16_may_07_small
Restore the last backup using the dd command
[root@vmractest1]# dd if=/vmasmtest/BACKUP/OCR/OCR_03_may_07 of=/dev/raw/ocr.dbf
305234+0 records in
305234+0 records out

OCR.LOC
used by ASM to start CSS daemon

/var/opt/oracle on Sun Solaris
/etc/oracle on RHEL

$ cat ocr.loc
ocrconfig_loc=/dev/asmdisk/ocr1 <-specifies the location of the Oracle Cluster Registry (OCR) used by the CSS daemon
ocrmirrorconfig_loc=/dev/asmdisk/ocr2
local_only=FALSE

OCR

$ ocrconfig –option
$ ocrcheck
$ ocrconfig -showbackup
$ ocrconfig –export myfile
$ ocrdump -backupfile my-file
$ ocrconfig –restore my_file
$ cluvfy comp ocr –n all -verbose #check OCR integrity
$ ocrconfig -replace ocr destination_file or disk
$ ocrconfig -replace ocrmirror destination_file or disk
# ../bin/crs stop

Stop All Processes in 10g

Stop All Processes

Stop all listeners and other processes running in the Oracle home directory, where you need to install the patch set.

Note: If you shut down ASM instances, you must first shut down all database instances that use ASM, even if these databases run from different Oracle homes.

• If you are upgrading a single instance installation, shut down the following Oracle Database 10g processes before installing the patch set:

  
  

  Note: You must perform these steps in the order listed.

  
  

  1. Shut down all processes in the Oracle home that might be accessing a database, for example Oracle Enterprise Manager Database Control or iSQL*Plus.

     
     

     Note: Before you shutdown all processes that are monitored by Enterprise Manager Grid Control, set a blackout in Grid Control for the processes that you intend to shut down. This is necessary so that the availability records for these processes indicate that the shutdown was planned downtime rather than an unplanned system outage.

     
     

  2. Shut down all database instances running in the Oracle home directory, where you need to install the patch set.

  3. Shut down the ASM instance, if the ASM instance is running in the Oracle home directory, where you need to install the patch set.

  4. Shut down all listeners running in the Oracle home directory, where you need to install the patch set.

  5. Shut down the Oracle Cluster Synchronization Services (CSS) daemon as the root user:

     # oracle_home/bin/crsctl stop crs
     
     

• If you are upgrading an Oracle Clusterware installation, shut down the following Oracle Database 10g processes before installing the patch set:

  
  

  Note: You must perform these steps in the order listed.

  
  

  1. Shut down all processes in the Oracle home on each node that might be accessing a database, for example Oracle Enterprise Manager Database Control.

     
     

     Note: Before you shutdown all processes that are monitored by Enterprise Manager Grid Control, set a blackout in Grid Control for the processes that you intend to shut down. This is necessary so that the availability records for these processes indicate that the shutdown was planned downtime rather than an unplanned system outage.

     
     

  2. Shut down all RAC instances on all nodes. To shut down all RAC instances for a database, enter the following command where db_name is the name of the database:

     $ oracle_home/bin/srvctl stop database -d db_name -i inst_name
     
     

  3. Shut down all ASM instances on all nodes. To shut down an ASM instance, enter the following command where node is the name of the node where the ASM instance is running:

     $ oracle_home/bin/srvctl stop asm -n node
     
     

  4. Stop all node applications on all nodes. To stop node applications running on a node, enter the following command where node is the name of the node where the applications are running:

     $ oracle_home/bin/srvctl stop nodeapps -n node
     
     

  5. Shut down the Oracle Clusterware process by entering the following command on all nodes as the root user:

     # crs_home/bin/crsctl stop crs
     
     

     
     

     Note: In these examples, crs_home is the Oracle Clusterware home directory where the Oracle Clusterware 10g installation that you want to upgrade is installed.

     
     
     
     

     Note: If the Oracle Clusterware installation is not on a shared Oracle home you can upgrade the Oracle Clusterware installation one node at a time. To do this, perform the preceding steps only on the first node that you are upgrading, then follow the instructions on the installer screen.

     
     

• If you are upgrading a RAC installation, shut down the following Oracle Database 10g processes before installing the patch set:

  
  

  Note: You must perform these steps in the order listed.

  
  

  1. Shut down all processes in the Oracle home on each node that might be accessing a database, for example Oracle Enterprise Manager Database Control.

     
     

     Note: Before you shutdown all processes that are monitored by Enterprise Manager Grid Control, set a blackout in Grid Control for the processes that you intend to shut down. This is necessary so that the availability records for these processes indicate that the shutdown was planned downtime rather than an unplanned system outage.

     
     

  2. Shut down all RAC instances on the nodes, which run from the Oracle home on which you are going to apply the patch set. To shut down all RAC instances for a database, enter the following command where db_name is the name of the database:

     $ oracle_home/bin/srvctl stop database -d db_name
     
     

  3. Shut down all ASM instances on all nodes. To shut down an ASM instance, enter the following command where node is the name of the node where the ASM instance is running:

     $ oracle_home/bin/srvctl stop asm -n node
     
     

  4. Stop any listeners that are running from the Oracle home that you are patching on all nodes. To stop the listener running on a node, enter the following command, where node is the name of the node where the listener is running:

     $ oracle_home/bin/srvctl stop listener -n node [-l listenername]
     
     

config of a RAC database

lion336@PSPX1$ srvctl config database -d PSPX -a

lion336 PSPX1 /ora01/oracle/product/db_1020
lion337 PSPX2 /ora01/oracle/product/db_1020
lion338 PSPX3 /ora01/oracle/product/db_1020
DB_NAME: PSPX
ORACLE_HOME: /ora01/oracle/product/db_1020
SPFILE: +ORADATA001/PSPX/spfilePSPX.ora
DOMAIN: null
DB_ROLE: null
START_OPTIONS: null
POLICY: AUTOMATIC
ENABLE FLAG: DB ENABLED
lion336@PSPX1:/ora01/oracle/admin/network $

Query RAC sessions

col username format a12
col FAILED_OVER format a12
col machine format a18
set lines 120
select USERNAME,INST_ID, MACHINE,FAILED_OVER,FAILOVER_TYPE,FAILOVER_METHOD,count(*) from gv$session
where username not in ('SYS','DBSNMP') group by
USERNAME,MACHINE,INST_ID,FAILED_OVER,FAILOVER_TYPE,FAILOVER_METHOD
order by USERNAME,INST_ID;

Trace Oracle Command

How to Trace Oracle Command

export ORACLE_TRACE=T

RAC Commands

find out which NIC (private obviously) was used for Cache Fusion

SQL> oradebug setmypid
SQL> oradebug ipc
SQL> oradebug tracefile_name

Nodes in cluster

$ORA_CRS_HOME/bin/olsnodes -n

CRS

see CRS cheat Sheet

Storage -- List of rawdevices

cat /etc/sysconfig/rawdevices

OCR

see OCR Cheat Sheet

Voting disk

see Voting Disk Cheat Sheet