Friday, February 27, 2015

Oracle Data Integrator processing multiple files in ODI interface example

odi process multiple files

Wednesday, February 25, 2015

Creating ODI 12c Colocated Agent Nodemanager as Windows Service in Windows 8

Creating ODI 12c Colocated Agent as Windows Service in Windows 8.

First we need to Configure Domain and Start the Weblogic services.
Then Create agent in Topology as OracleDIAgent1 & Portno: 20910
Then call agent using command line for first time to apply all agent sdk jar files.

Then Follow the below steps to install nodemanager as windows service.
Step1:
Goto C:\ODI_12c\OracleHome\user_projects\domains\odi_domain\bin directory there we can find the installNodeMgrSvc.cmd

Step2:
Open CMD as Administrator user and Change directory to Domain\bin directory
CD C:\ODI_12c\OracleHome\user_projects\domains\odi_domain\bin
call installNodeMgrSvc.cmd and it will create Windows service.

Step3: Open Serices.msc file and here we can see service is created.

step4: Create startColocated_Agent.cmd file in C:\ODI_12c\Oracle_Home\user_projects\domains\odi_domain\bin directory
SETLOCAL

set DOMAIN_HOME=C:\ODI_12c\Oracle_Home\user_projects\domains\odi_domain

call %DOMAIN_HOME%\bin\startComponent.cmd OracleDIAgent1

ENDLOCAL
step5: call that startColocated_Agent.cmd file and it will ask password for Nodemanager
Password: Admin123

Test the agent in Topology

step6: Stopping agent command script creation.

create below commands as cmd file as stopColocated_Agent.cmd

C:\ODI_12c\Oracle_Home\user_projects\domains\odi_domain\bin

SETLOCAL

set DOMAIN_HOME=C:\ODI_12c\Oracle_Home\user_projects\domains\odi_domain

call %DOMAIN_HOME%\bin\agentstop.cmd -NAME=OracleDIAgent1

ENDLOCAL

Monday, February 23, 2015

Creating Oracle Data Integrator 11g Standalone Agent as Windows Service in windows 8

Download YAJSW zip from below link. We are using yajsw for odi agent as service configuration

Yet Another Java Service Wrapper.

find the steps in Oracle Blog.

step1) download YAJSW
step2) unzip and copy into ODI Home location.

step3: start your standalone agent using cmd line

step4: Goto Task manager and find PID for CMD line started agent as you can find java.exe

step5: Open CMD and CD C:\OBIA\Oracle_ODI1\yajsw-stable-11.11\bat and call Genconfig.bat PID (5500)

Enter and It will be created wrapper.conf file in

C:\OBIA\Oracle_ODI1\yajsw-stable-11.11\conf

step6: edit wrapper.conf file and change the below details in this faile.

SUPERVISOR and Master repository details will be fetched from odiparams.bat file only we need to change if password contains any , (comma) then add \ like \,

step7: Save the file and and create agent in Topology.

step8: call runConsole.bat file

step10: service is created and goto services.msc file check service.

Goto Topology Agent and Click on test agent.

Sunday, February 22, 2015

ORA-01172: recovery of thread 1 stuck at block 224 of file 3 while starting database

While restarting my database it is able start database but it is failing for mounting and open database
file (dbfile). I got below error while starting database.

ORA-01172: recovery of thread 1 stuck at block 224 of file 3
ORA-01151: use media recovery to recover block, restore backup if needed

I have did below fix for this issues. and after that its working fine.

SQL> Recover datafile 3;
media recovery complete.
SQL> alter database open;
database altered.

Tuesday, February 17, 2015

ODI Practice Database Source and target schemas creatioin

DROP USER SDEV CASCADE;
DROP USER STEST CASCADE;
DROP USER SPROD CASCADE;
DROP USER TDEV CASCADE;
DROP USER TTEST CASCADE;
DROP USER TPROD CASCADE;
CREATE USER SDEV IDENTIFIED by welcome;
CREATE USER STEST IDENTIFIED BY welcome;
CREATE USER SPROD IDENTIFIED BY welcome;
GRANT ALL PRIVILEGES TO SDEV,STEST,SPROD;
CREATE USER TDEV IDENTIFIED by welcome;
CREATE USER TTEST IDENTIFIED BY welcome;
CREATE USER TPROD IDENTIFIED BY welcome;
GRANT ALL PRIVILEGES TO TDEV,TTEST,TPROD;
CREATE TABLE SDEV.BONUS
        (ENAME VARCHAR2(10),
         JOB   VARCHAR2(9),
         SAL   NUMBER,
         COMM NUMBER);
CREATE TABLE SDEV.EMP
       (EMPNO NUMBER(4) NOT NULL,
        ENAME VARCHAR2(10),
        JOB VARCHAR2(9),
        MGR NUMBER(4),
        HIREDATE DATE,
        SAL NUMBER(7, 2),
        COMM NUMBER(7, 2),
        DEPTNO NUMBER(2));
INSERT INTO SDEV.EMP VALUES
        (7369, 'SMITH', 'CLERK',     7902,
        TO_DATE('17-DEC-1980', 'DD-MON-YYYY'), 800, NULL, 20);
INSERT INTO SDEV.EMP VALUES
        (7499, 'ALLEN', 'SALESMAN', 7698,
        TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600, 300, 30);
INSERT INTO SDEV.EMP VALUES
        (7521, 'WARD',   'SALESMAN', 7698,
        TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500, 30);
INSERT INTO SDEV.EMP VALUES
        (7566, 'JONES', 'MANAGER',   7839,
        TO_DATE('2-APR-1981', 'DD-MON-YYYY'), 2975, NULL, 20);
INSERT INTO SDEV.EMP VALUES
        (7654, 'MARTIN', 'SALESMAN', 7698,
        TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
INSERT INTO SDEV.EMP VALUES
        (7698, 'BLAKE', 'MANAGER',   7839,
        TO_DATE('1-MAY-1981', 'DD-MON-YYYY'), 2850, NULL, 30);
INSERT INTO SDEV.EMP VALUES
        (7782, 'CLARK', 'MANAGER',   7839,
        TO_DATE('9-JUN-1981', 'DD-MON-YYYY'), 2450, NULL, 10);
INSERT INTO SDEV.EMP VALUES
        (7788, 'SCOTT', 'ANALYST',   7566,
        TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO SDEV.EMP VALUES
        (7839, 'KING',   'PRESIDENT', NULL,
        TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
INSERT INTO SDEV.EMP VALUES
        (7844, 'TURNER', 'SALESMAN', 7698,
        TO_DATE('8-SEP-1981', 'DD-MON-YYYY'), 1500, NULL, 30);
INSERT INTO SDEV.EMP VALUES
        (7876, 'ADAMS', 'CLERK',     7788,
        TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);
INSERT INTO SDEV.EMP VALUES
        (7900, 'JAMES', 'CLERK',     7698,
        TO_DATE('3-DEC-1981', 'DD-MON-YYYY'),   950, NULL, 30);
INSERT INTO SDEV.EMP VALUES
        (7902, 'FORD',   'ANALYST',   7566,
        TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO SDEV.EMP VALUES
        (7934, 'MILLER', 'CLERK',     7782,
        TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);

CREATE TABLE SDEV.DEPT
       (DEPTNO NUMBER(2),
        DNAME VARCHAR2(14),
        LOC VARCHAR2(13) );
INSERT INTO SDEV.DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO SDEV.DEPT VALUES (20, 'RESEARCH',   'DALLAS');
INSERT INTO SDEV.DEPT VALUES (30, 'SALES',      'CHICAGO');
INSERT INTO SDEV.DEPT VALUES (40, 'OPERATIONS', 'BOSTON');
CREATE TABLE SDEV.SALGRADE
        (GRADE NUMBER,
         LOSAL NUMBER,
         HISAL NUMBER);
INSERT INTO SDEV.SALGRADE VALUES (1, 700, 1200);
INSERT INTO SDEV.SALGRADE VALUES (2, 1201, 1400);
INSERT INTO SDEV.SALGRADE VALUES (3, 1401, 2000);
INSERT INTO SDEV.SALGRADE VALUES (4, 2001, 3000);
INSERT INTO SDEV.SALGRADE VALUES (5, 3001, 9999);
COMMIT;
CREATE TABLE STEST.BONUS
        (ENAME VARCHAR2(10),
         JOB   VARCHAR2(9),
         SAL   NUMBER,
         COMM NUMBER);
CREATE TABLE STEST.EMP
       (EMPNO NUMBER(4) NOT NULL,
        ENAME VARCHAR2(10),
        JOB VARCHAR2(9),
        MGR NUMBER(4),
        HIREDATE DATE,
        SAL NUMBER(7, 2),
        COMM NUMBER(7, 2),
        DEPTNO NUMBER(2));
INSERT INTO STEST.EMP VALUES
        (7369, 'SMITH', 'CLERK',     7902,
        TO_DATE('17-DEC-1980', 'DD-MON-YYYY'), 800, NULL, 20);
INSERT INTO STEST.EMP VALUES
        (7499, 'ALLEN', 'SALESMAN', 7698,
        TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600, 300, 30);
INSERT INTO STEST.EMP VALUES
        (7521, 'WARD',   'SALESMAN', 7698,
        TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500, 30);
INSERT INTO STEST.EMP VALUES
        (7566, 'JONES', 'MANAGER',   7839,
        TO_DATE('2-APR-1981', 'DD-MON-YYYY'), 2975, NULL, 20);
INSERT INTO STEST.EMP VALUES
        (7654, 'MARTIN', 'SALESMAN', 7698,
        TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
INSERT INTO STEST.EMP VALUES
        (7698, 'BLAKE', 'MANAGER',   7839,
        TO_DATE('1-MAY-1981', 'DD-MON-YYYY'), 2850, NULL, 30);
INSERT INTO STEST.EMP VALUES
        (7782, 'CLARK', 'MANAGER',   7839,
        TO_DATE('9-JUN-1981', 'DD-MON-YYYY'), 2450, NULL, 10);
INSERT INTO STEST.EMP VALUES
        (7788, 'SCOTT', 'ANALYST',   7566,
        TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO STEST.EMP VALUES
        (7839, 'KING',   'PRESIDENT', NULL,
        TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
INSERT INTO STEST.EMP VALUES
        (7844, 'TURNER', 'SALESMAN', 7698,
        TO_DATE('8-SEP-1981', 'DD-MON-YYYY'), 1500, NULL, 30);
INSERT INTO STEST.EMP VALUES
        (7876, 'ADAMS', 'CLERK',     7788,
        TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);
INSERT INTO STEST.EMP VALUES
        (7900, 'JAMES', 'CLERK',     7698,
        TO_DATE('3-DEC-1981', 'DD-MON-YYYY'),   950, NULL, 30);
INSERT INTO STEST.EMP VALUES
        (7902, 'FORD',   'ANALYST',   7566,
        TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO STEST.EMP VALUES
        (7934, 'MILLER', 'CLERK',     7782,
        TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);

CREATE TABLE STEST.DEPT
       (DEPTNO NUMBER(2),
        DNAME VARCHAR2(14),
        LOC VARCHAR2(13) );
INSERT INTO STEST.DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO STEST.DEPT VALUES (20, 'RESEARCH',   'DALLAS');
INSERT INTO STEST.DEPT VALUES (30, 'SALES',      'CHICAGO');
INSERT INTO STEST.DEPT VALUES (40, 'OPERATIONS', 'BOSTON');
CREATE TABLE STEST.SALGRADE
        (GRADE NUMBER,
         LOSAL NUMBER,
         HISAL NUMBER);
INSERT INTO STEST.SALGRADE VALUES (1, 700, 1200);
INSERT INTO STEST.SALGRADE VALUES (2, 1201, 1400);
INSERT INTO STEST.SALGRADE VALUES (3, 1401, 2000);
INSERT INTO STEST.SALGRADE VALUES (4, 2001, 3000);
INSERT INTO STEST.SALGRADE VALUES (5, 3001, 9999);
COMMIT;
CREATE TABLE SPROD.BONUS
        (ENAME VARCHAR2(10),
         JOB   VARCHAR2(9),
         SAL   NUMBER,
         COMM NUMBER);
CREATE TABLE SPROD.EMP
       (EMPNO NUMBER(4) NOT NULL,
        ENAME VARCHAR2(10),
        JOB VARCHAR2(9),
        MGR NUMBER(4),
        HIREDATE DATE,
        SAL NUMBER(7, 2),
        COMM NUMBER(7, 2),
        DEPTNO NUMBER(2));
INSERT INTO SPROD.EMP VALUES
        (7369, 'SMITH', 'CLERK',     7902,
        TO_DATE('17-DEC-1980', 'DD-MON-YYYY'), 800, NULL, 20);
INSERT INTO SPROD.EMP VALUES
        (7499, 'ALLEN', 'SALESMAN', 7698,
        TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600, 300, 30);
INSERT INTO SPROD.EMP VALUES
        (7521, 'WARD',   'SALESMAN', 7698,
        TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500, 30);
INSERT INTO SPROD.EMP VALUES
        (7566, 'JONES', 'MANAGER',   7839,
        TO_DATE('2-APR-1981', 'DD-MON-YYYY'), 2975, NULL, 20);
INSERT INTO SPROD.EMP VALUES
        (7654, 'MARTIN', 'SALESMAN', 7698,
        TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
INSERT INTO SPROD.EMP VALUES
        (7698, 'BLAKE', 'MANAGER',   7839,
        TO_DATE('1-MAY-1981', 'DD-MON-YYYY'), 2850, NULL, 30);
INSERT INTO SPROD.EMP VALUES
        (7782, 'CLARK', 'MANAGER',   7839,
        TO_DATE('9-JUN-1981', 'DD-MON-YYYY'), 2450, NULL, 10);
INSERT INTO SPROD.EMP VALUES
        (7788, 'SCOTT', 'ANALYST',   7566,
        TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO SPROD.EMP VALUES
        (7839, 'KING',   'PRESIDENT', NULL,
        TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
INSERT INTO SPROD.EMP VALUES
        (7844, 'TURNER', 'SALESMAN', 7698,
        TO_DATE('8-SEP-1981', 'DD-MON-YYYY'), 1500, NULL, 30);
INSERT INTO SPROD.EMP VALUES
        (7876, 'ADAMS', 'CLERK',     7788,
        TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);
INSERT INTO SPROD.EMP VALUES
        (7900, 'JAMES', 'CLERK',     7698,
        TO_DATE('3-DEC-1981', 'DD-MON-YYYY'),   950, NULL, 30);
INSERT INTO SPROD.EMP VALUES
        (7902, 'FORD',   'ANALYST',   7566,
        TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO SPROD.EMP VALUES
        (7934, 'MILLER', 'CLERK',     7782,
        TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);

CREATE TABLE SPROD.DEPT
       (DEPTNO NUMBER(2),
        DNAME VARCHAR2(14),
        LOC VARCHAR2(13) );
INSERT INTO SPROD.DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO SPROD.DEPT VALUES (20, 'RESEARCH',   'DALLAS');
INSERT INTO SPROD.DEPT VALUES (30, 'SALES',      'CHICAGO');
INSERT INTO SPROD.DEPT VALUES (40, 'OPERATIONS', 'BOSTON');
CREATE TABLE SPROD.SALGRADE
        (GRADE NUMBER,
         LOSAL NUMBER,
         HISAL NUMBER);
INSERT INTO SPROD.SALGRADE VALUES (1, 700, 1200);
INSERT INTO SPROD.SALGRADE VALUES (2, 1201, 1400);
INSERT INTO SPROD.SALGRADE VALUES (3, 1401, 2000);
INSERT INTO SPROD.SALGRADE VALUES (4, 2001, 3000);
INSERT INTO SPROD.SALGRADE VALUES (5, 3001, 9999);
COMMIT;
CREATE TABLE TDEV.BONUS
        (ENAME VARCHAR2(10),
         JOB   VARCHAR2(9),
         SAL   NUMBER,
         COMM NUMBER);
CREATE TABLE TDEV.EMP
       (EMPNO NUMBER(4) NOT NULL,
        ENAME VARCHAR2(10),
        JOB VARCHAR2(9),
        MGR NUMBER(4),
        HIREDATE DATE,
        SAL NUMBER(7, 2),
        COMM NUMBER(7, 2),
        DEPTNO NUMBER(2));

CREATE TABLE TDEV.DEPT
       (DEPTNO NUMBER(2),
        DNAME VARCHAR2(14),
        LOC VARCHAR2(13) );
CREATE TABLE TDEV.SALGRADE
        (GRADE NUMBER,
         LOSAL NUMBER,
         HISAL NUMBER);
COMMIT;
CREATE TABLE TTEST.BONUS
        (ENAME VARCHAR2(10),
         JOB   VARCHAR2(9),
         SAL   NUMBER,
         COMM NUMBER);
CREATE TABLE TTEST.EMP
       (EMPNO NUMBER(4) NOT NULL,
        ENAME VARCHAR2(10),
        JOB VARCHAR2(9),
        MGR NUMBER(4),
        HIREDATE DATE,
        SAL NUMBER(7, 2),
        COMM NUMBER(7, 2),
        DEPTNO NUMBER(2));

CREATE TABLE TTEST.DEPT
       (DEPTNO NUMBER(2),
        DNAME VARCHAR2(14),
        LOC VARCHAR2(13) );
CREATE TABLE TTEST.SALGRADE
        (GRADE NUMBER,
         LOSAL NUMBER,
         HISAL NUMBER);
COMMIT;
CREATE TABLE TPROD.BONUS
        (ENAME VARCHAR2(10),
         JOB   VARCHAR2(9),
         SAL   NUMBER,
         COMM NUMBER);
CREATE TABLE TPROD.EMP
       (EMPNO NUMBER(4) NOT NULL,
        ENAME VARCHAR2(10),
        JOB VARCHAR2(9),
        MGR NUMBER(4),
        HIREDATE DATE,
        SAL NUMBER(7, 2),
        COMM NUMBER(7, 2),
        DEPTNO NUMBER(2));

CREATE TABLE TPROD.DEPT
       (DEPTNO NUMBER(2),
        DNAME VARCHAR2(14),
        LOC VARCHAR2(13) );
CREATE TABLE TPROD.SALGRADE
        (GRADE NUMBER,
         LOSAL NUMBER,
         HISAL NUMBER);
COMMIT;

Oracle Data integrator 11G & 12c Tutorials,