Friday, December 20, 2013

How to send a email if any ERROR occurs in oracle database alert log files in oracle 10g, 11g, 12c. Steps 

 1) Create a external table for the alert log content.  
  How to create external table for alert log.  
 2) Create a view from this external table like...  
 ---------------------------------------------------------  
   CREATE OR REPLACE FORCE VIEW DBA_MAIN.VW_ALERT_LOG  
 (  
   NO_OF_LINE,  
   TEXT  
 )  
 AS  
   SELECT NO_of_line, text  
    FROM ( SELECT ROWNUM NO_of_line, text  
         FROM dba_main.alert_log_external  
       ORDER BY ROWNUM DESC)  
   WHERE UPPER (text) LIKE UPPER ('%ERROR%')   
 /  
 -----------------------------------------------------------  
 3) Create a table from this above view like....  
 -------------------------------------------------------------  
 CREATE TABLE DBA_MAIN.TBL_ALERT_LOG_ERROR  
 AS  
   SELECT No_of_line, text, 'Y' send_flg FROM DBA_MAIN.VW_ALERT_LOG;  
 ---------------------------------------------------------------  
 3) Create a email address table and insert some email addresses like below...  
 ------------------------------------------------------------  
 CREATE TABLE DBA_MAIN.EMAIL_ADDRESS  
 (  
  ID    NUMBER,  
  NAME   VARCHAR2(50 BYTE),  
  EMAIL   VARCHAR2(100 BYTE),  
  ACT_FLAG VARCHAR2(1 BYTE)          DEFAULT 'Y'  
 ) ;  
 Insert into DBA_MAIN.EMAIL_ADDRESS  
   (ID, NAME, EMAIL, ACT_FLAG)  
  Values  
   (1, 'Abdul Halim', 'halim@test.com', 'Y');  
 Insert into DBA_MAIN.EMAIL_ADDRESS  
   (ID, NAME, EMAIL, ACT_FLAG)  
  Values  
   (2, 'user2', 'test@test.com', 'N');  
 Insert into DBA_MAIN.EMAIL_ADDRESS  
   (ID, NAME, EMAIL, ACT_FLAG)  
  Values  
   (3, 'user1', 'test2@test.com', 'N');  
 COMMIT;  
 4) Create a package for sending email from the oracle database   
 (In this package, i created two procedures , one for sending email with attachment   
 another is sending email without attachment ment. I am using first one here)  
 -------------------------------------------------------------  
 SEND_EMAIL_FROM_PL_SQL.send_email_with_attachment  
 -----------------------------------------------------------  
 4) create belows trigger on "DBA_MAIN.TBL_ALERT_LOG_ERROR" this table..  
 --------------------------------------------------------------------------  
 CREATE OR REPLACE TRIGGER DBA_MAIN.TRG_ALERT_LOG_ERROR_EMAIL  
 before INSERT ON DBA_MAIN.TBL_ALERT_LOG_ERROR  
 REFERENCING NEW AS new     
 FOR EACH ROW  
 WHEN (  
 new.send_flg='N'  
    )  
 begin  
 /*  
  SEND_EMAIL_FROM_PL_SQL.send_email_with_attachment  
     ( sender_email_address     varchar2, -- Must be single E-mail address  
      recipient_email_addresses  varchar2,   
      email_subject        varchar2,  
      email_body_text       varchar2,  
      email_attachment_text    clob,   -- No restriction on size of text  
      email_attachment_file_name  varchar2    
     )  
    */  
  for i in (select email from dba_main.email_address where act_flag='Y') loop   
  SEND_EMAIL_FROM_PL_SQL.send_email_with_attachment  
     ( 'halim@test.com', -- Must be single E-mail address  
      i.email,      'Test Email from Database!',  
      'Plz check the Alert for more info.... ' || :new.text,  
      :new.text,   -- No restriction on size of text  
      'Attention'   
     ) ;  
     end loop;  
 end;  
 /  
 -------------------------------------------------------------------------  
 5) create a procedure...for job....  
 ---------------------------------------------------------  
 CREATE OR REPLACE PROCEDURE dpr_alter_log_error_marge  
 IS  
 BEGIN  
   MERGE INTO TBL_ALERT_LOG_ERROR f_table  
     USING DBA_MAIN.VW_ALERT_LOG v_table  
       ON (v_table.text = f_table.text) --- and UPPER (v_table.text) LIKE UPPER ('%ERROR%') )  
   WHEN MATCHED  
   THEN  
    UPDATE SET f_table.SEND_FLG = 'Y'  
   WHEN NOT MATCHED  
   THEN  
    INSERT   (NO_OF_LINE, TEXT, SEND_FLG)  
      VALUES (ROWNUM, v_table.text, 'N');  
 END;  
 ----------------------------------------------------------  
 6) create a job like belows ( it will check alert log error and if found then send it email to DBA..  
 -----------------------------------------------------------  
 DECLARE  
  X NUMBER;  
 BEGIN  
  SYS.DBMS_JOB.SUBMIT  
  ( job    => X   
   ,what   => 'DBA_MAIN.DPR_ALTER_LOG_ERROR_MARGE;'  
   ,next_date => to_date('20/12/2013 10:55:34','dd/mm/yyyy hh24:mi:ss')  
   ,interval => 'SYSDATE+60/1440 '  
   ,no_parse => FALSE  
  );  
  SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));  
 COMMIT;  
 END;  
 /  
 ------------------------------------------------------------ 
Posted by at No comments:

Tuesday, October 1, 2013

Windows batch job script for oracle export import backup

windows batch job script for oracle export (and import) backup (2 copy 1. ORCL 2. ORCL_1)

----------------------------------------------------
@echo off
set ORACLE_SID=ORCL

if exist C:\oracle_exp\export_backup\ORCL\ORCL.log copy C:\oracle_exp\export_backup\ORCL\ORCL.log C:\oracle_exp\export_backup\ORCL\ORCL_1.log >NUL:

if exist C:\oracle_exp\export_backup\ORCL\ORCL.dmp copy C:\oracle_exp\export_backup\ORCL\ORCL.dmp C:\oracle_exp\export_backup\ORCL\ORCL_1.dmp >NUL:

exp 'sys/sys@ORCL_10g as sysdba' full=yes file=C:\oracle_exp\export_backup\ORCL\ORCL.dmp log=C:\oracle_exp\export_backup\ORCL\ORCL.log
-----------------------------------------------------------------------------------

===========================================
 =========================================
  Expdp logical backup with windows batch job script
  ========================================
==========================================



 The below script is for windows batch job to backup an Oracle database 10g/11g with expdp (datapump) utility. This script setting oracle_sid, NLS_LANG, some local and remote (mapped) drive locations and Archive_program (7-Zip) location.
it creating and zipped an expdb backup, then copied to various location. (local and remote).

This script keeping three copy of backup file to the local location and eight copy to the remote location.

[Note: select batch job option "Run whether user is logged on or not" and "run with highest Privileges" and give the user password once.]

--------------------------------------------------------------------------------------------------------------------
@echo off

set ORACLE_SID=HALIMDB
set NLS_LANG=.WE8MSWIN1252

SET Today=%Date:~4,2%-%Date:~7,2%-%Date:~10,4%


SET BASE_FOLDER=C:\oracle_exp\export_backup\HALIMDB
SET BASE_NAME=zip_%ORACLE_SID%
SET DUMPFILE_FOLDER=C:\oracle_exp\export_backup\HALIMDB

SET ARCHIVE_PROGRAM="C:\Program Files\7-Zip\7z.exe"

SET REMOTE_LOC=\\sbwc_share\h$\10g_backup_220_important\Export_backup_10g_220\HALIMDB


SET DUMPFILE_NAME=%ORACLE_SID%.dmp
SET LOGFILE_NAME=%ORACLE_SID%.log
SET BACKUP_FOLDER=%BASE_FOLDER%


SET BACKUP_FILENAME=%BASE_NAME%


REM Script begin
REM expdp, full backup to expdb_dw_dump_dir,
expdp 'EXPDP_HALIMDB/EXPDP_HALIMDB123@HALIMDB' full=yes directory=expdb_HALIMDB_dump_dir dumpfile=%DUMPFILE_NAME% logfile=%LOGFILE_NAME% PARALLEL=5

IF %ERRORLEVEL% NEQ 0 GOTO ERROR


if exist %BACKUP_FOLDER%\%BACKUP_FILENAME%_2.zip copy %BACKUP_FOLDER%\%BACKUP_FILENAME%_2.zip %BACKUP_FOLDER%\%BACKUP_FILENAME%_3.zip /Y
 if exist %BACKUP_FOLDER%\%BACKUP_FILENAME%_1.zip copy %BACKUP_FOLDER%\%BACKUP_FILENAME%_1.zip %BACKUP_FOLDER%\%BACKUP_FILENAME%_2.zip /Y
if exist %BACKUP_FOLDER%\%BACKUP_FILENAME%.zip copy %BACKUP_FOLDER%\%BACKUP_FILENAME%.zip %BACKUP_FOLDER%\%BACKUP_FILENAME%_1.zip /Y



%ARCHIVE_PROGRAM% a -tzip %BACKUP_FOLDER%\%BACKUP_FILENAME% %DUMPFILE_FOLDER%\%DUMPFILE_NAME% %DUMPFILE_FOLDER%\%LOGFILE_NAME%


IF %ERRORLEVEL% NEQ 0 GOTO ERROR

if exist %REMOTE_LOC%\%BACKUP_FILENAME%_6.zip copy %REMOTE_LOC%\%BACKUP_FILENAME%_6.zip %REMOTE_LOC%\%BACKUP_FILENAME%_7.zip /Y
if exist %REMOTE_LOC%\%BACKUP_FILENAME%_5.zip copy %REMOTE_LOC%\%BACKUP_FILENAME%_5.zip %REMOTE_LOC%\%BACKUP_FILENAME%_6.zip /Y
if exist %REMOTE_LOC%\%BACKUP_FILENAME%_4.zip copy %REMOTE_LOC%\%BACKUP_FILENAME%_4.zip %REMOTE_LOC%\%BACKUP_FILENAME%_5.zip /Y
if exist %REMOTE_LOC%\%BACKUP_FILENAME%_3.zip copy %REMOTE_LOC%\%BACKUP_FILENAME%_3.zip %REMOTE_LOC%\%BACKUP_FILENAME%_4.zip /Y
 if exist %REMOTE_LOC%\%BACKUP_FILENAME%_2.zip copy %REMOTE_LOC%\%BACKUP_FILENAME%_2.zip %REMOTE_LOC%\%BACKUP_FILENAME%_3.zip /Y
if exist %REMOTE_LOC%\%BACKUP_FILENAME%_1.zip copy %REMOTE_LOC%\%BACKUP_FILENAME%_1.zip %REMOTE_LOC%\%BACKUP_FILENAME%_2.zip /Y
if exist %REMOTE_LOC%\%BACKUP_FILENAME%.zip copy %REMOTE_LOC%\%BACKUP_FILENAME%.zip %REMOTE_LOC%\%BACKUP_FILENAME%_1.zip /Y



copy %BACKUP_FOLDER%\%BACKUP_FILENAME%.zip %REMOTE_LOC%\%BACKUP_FILENAME%.zip /Y


IF %ERRORLEVEL% NEQ 0 GOTO ERROR

DEL %DUMPFILE_FOLDER%\%DUMPFILE_NAME%
DEL %DUMPFILE_FOLDER%\%LOGFILE_NAME%


EXIT 0

:ERROR
EXIT 1


--------------------------------------------------------------------------------------------------------------------
Posted by at 1 comment:

Friday, September 20, 2013

Installation of Oracle Database on Windows 2008 64 bits and listener service is not created and started

This problem you will face when you will work with a domain user, for creating a listerner service in windows 2008, you have to be a administrator of this server.

oracle database service is created fine but no listener service is creating.......

you can do it like belows

1)  Opening CMD prompt with administrator privileges
(Search for cmd and right click and run as administrator)
or
you can find it here C:\Windows\System32\cmd.exe  (right click and Run as Administrator)

> C:\Windows\system32> lsnrctl start

(it will automatically created the listener service...)

or

(As as administrator)

C:\Windows\system32> sc create OracleOraDb11g_home1TNSListener binPath="H:\oracle\product\10.2.0\db_1\BIN\TNSLSNR.exe"



Posted by at No comments:

Friday, July 12, 2013

ORA-65096: invalid common user or role name or ORA-65049: creation in oracle 12c database

ORA-65096: invalid common user or role name or ORA-65049: creation in oracle 12c


65049, 00000,"invalid common user or role name"
 *Cause:  An attempt was made to create a common user or role with a name
          that wass not valid for common users or roles.  In addition to
          the usual rules for user and role names, common user and role
          names must start with C## or c## and consist only of ASCII
          characters.
  *Action: Specify a valid common user or role name.


65049, 00000, "creation of local user or role is not allowed in CDB$ROOT"
  *Cause:   An attempt was made to create a local user or role in CDB$ROOT.
  *Action:  If trying to create a common user or role, specify CONTAINER=ALL.



 Solution
================================
================================
(you must have to create "common user"
under CDB$ROOT with "c##" or "C##" beging of the user name. it is a rule)


In oracle 12c Database there are mainly two type of users

1) common users
2) local users

[ Note :  CDB = Container Database : A container is a collection of schemas, objects, and related structures in a Multitenant container Database that appears logically to an application as a separate database. Within a CDB, each container has a unique ID and name.
       
            PDB = Pluggable Database : The root and every PDB is considered a container. PDBs isolate data and operations so that from the perspective of a user or application, each PDB appears as if it were a traditional non-CDB.
          A PDB is a user-created set of schemas, objects, and related structures that appears logically to an application as a separate database. Every PDB is owned by SYS, which is a common user in the CDB, regardless of which user created the PDB.

        ROOT Container : Every CDB has one and only one root container, which stores the system metadata required to manage PDBs. All PDBs belong to the root. The name of the root is CDB$ROOT.
The root does not store user data. Thus, you must not add user data to the root or modify system-supplied schemas in the root. However, you can create common users and roles for database administration . A common user with the necessary privileges can switch between PDBs.
 ]



you have to understand the difference between them for mitigation the error.

Common Users in a CDB
======================

A common user is a database user that has the same identity in the root and in every existing and future PDB. Every common user can connect to and perform operations within the root, and within any PDB in which it has privileges.

Every common user is either Oracle-supplied or user-created. Examples of Oracle-supplied common users are SYS and SYSTEM.


Common users have the following characteristics:

    A common user can log in to any container (including CDB$ROOT) in which it has the CREATE SESSION privilege.


    The name of every user-created common user must begin with the characters c## or C##. (Oracle-supplied common user names do not have this restriction.)


    The names of common users must contain only ASCII or EBCDIC characters.


    Every common user is uniquely named across all containers.

  
    The schemas for a common user can differ in each container.




Local Users in a CDB
============================

A local user is a database user that is not common and can operate only within a single PDB. Local users have the following characteristics:

    A local user is specific to a particular PDB and owns a schema in this PDB.

    A local user on one PDB cannot log in to another PDB or to the root
   
    The name of a local user must not begin with the characters c## or C##.

    The name of a local user must only be unique within its PDB.

    Whether local users can access objects in a common schema depends on their user privileges.
   

=======================================
======================================
In details with live examples
======================================
=========================================


SQL>
SQL>
SQL> conn sys@halim12c as sysdba
Enter password:
Connected.
SQL>
SQL>
SQL>
SQL> select * from v$version ;

BANNER                                                                             CON_ID
------------------------------------------------------------------------------ ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production            0
PL/SQL Release 12.1.0.1.0 - Production                                                  0
CORE    12.1.0.1.0      Production
TNS for Linux: Version 12.1.0.1.0 - Production                                          0
NLSRTL Version 12.1.0.1.0 - Production                                                  0

SQL>
SQL>
SQL>
SQL>
SQL>
SQL> create user halim identified by halim ;
create user halim identified by halim
            *
ERROR at line 1:
ORA-65096: invalid common user or role name
SQL>
SQL>
SQL>
SQL> create user halimdb identified by halimdb container=current;
create user halimdb identified by halimdb container=current
                                  *
ERROR at line 1:
ORA-65049: creation of local user or role is not allowed in CDB$ROOT
SQL>
SQL>
SQL>
SQL>
SQL> create user halim identified by halim container=ALL ;
create user halim identified by halim container=ALL
            *
ERROR at line 1:
ORA-65096: invalid common user or role name

SQL>

===========================================
===========================================
           Solution
(you must have to create "common user"
under CDB$ROOT with "c##" or "C##" beging of the user name.
 it is a rule)
===========================================
============================================
SQL>
SQL> create user c##halim identified by halim ;

User created.

SQL>
SQL>
SQL> create user c##halim_1 identified by halim container=ALL ;

User created.

SQL>
SQL>
SQL>
SQL> SELECT SYS_CONTEXT('USERENV', 'CON_NAME')
  2    AS CUR_CONTAINER FROM DUAL;

CUR_CONTAINER
------------------------------------------------------------------------------------------
--------------------------------------------------------
CDB$ROOT

SQL>
SQL>
SQL>
SQL>
SQL> grant create session to c##halim ;

Grant succeeded.

SQL>
SQL>
SQL> conn c##halim/halim
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
SQL>
SQL>
SQL>
SQL>
SQL> conn c##halim/halim@halim12c
Connected.
SQL>
SQL>
SQL> conn sys/sys@halim12c as sysdba
Connected.
SQL>
SQL>
SQL>
SQL> SELECT NAME, PDB FROM V$SERVICES
  2    ORDER BY PDB, NAME;

NAME                           PDB
------------------------------ ------------------------------
HALIM12C                       CDB$ROOT
HALIM12CXDB                    CDB$ROOT
SYS$BACKGROUND                 CDB$ROOT
SYS$USERS                      CDB$ROOT
pdb_hr                         PDB_HR

SQL>
SQL>
SQL>
SQL>
SQL>
SQL> alter session set container=pdb_hr ;

Session altered.

SQL>
SQL> SELECT SYS_CONTEXT('USERENV', 'CON_NAME')
  2    AS CUR_CONTAINER FROM DUAL;

CUR_CONTAINER
------------------------------------------------------------------------------------------
--------------------------------------------------------
PDB_HR

SQL>
SQL>
SQL>
SQL>
SQL> create user halim identified by halim ;
create user halim identified by halim
                                *
ERROR at line 1:
ORA-01109: database not open


SQL>
SQL>
SQL> startup
ORA-24543: instance startup or shutdown not allowed in pluggable database
SQL>
SQL>
SQL> alter database pdb_hr open ;

Database altered.

SQL>
SQL>
SQL>
SQL> create user halim identified by halim ;

User created.

SQL>
SQL>
SQL>
SQL> grant dba to halim ;

Grant succeeded.

SQL>
SQL>
SQL>





Posted by at No comments:

Thursday, July 11, 2013

Identity column in oracle 12c Database

Identity column in oracle 12c Database
-----------------------------------------------

Now in Oracle Database 12c Release 1 database has ANSI-compliant IDENTITY column
like Microsoft SQL Server's Identity column for auto increment number generation.

So, now migration from Microsoft SQL Server's to oracle database will be easier
in case of IDENTITY COLUMN.


There are three option for identity  when we will create a table.

1)  by default : BY DEFAULT If you specify BY DEFAULT, then Oracle Database uses
                 the sequence generator to assign a value to the column by default,
                 but you can also explicitly assign a specified value to the column.

2)  by default on null : If you specify ON NULL, then Oracle Database uses the
                         sequence generator to assign a value to the column when a subsequent
                         INSERT statement attempts to assign a value that evaluates to NULL.

3)  always  : ALWAYS If you specify ALWAYS, then Oracle Database always uses the
              sequence generator to assign a value to the column. If you attempt to
              explicitly assign a value to the column using INSERT or UPDATE,
              then an error will be returned. This is the default.


In details explanation is below
=================================


[oracle@halim12c] $ sqlplus /nolog

SQL*Plus: Release 12.1.0.1.0 Production on Thu Jul 11 18:32:33 2013

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

SQL> conn sys@halim12c as sysdba
Enter password:
Connected.
SQL>
SQL> col banner format a78
SQL>
SQL>
SQL> select * from v$version ;

BANNER                                                                             CON_ID
------------------------------------------------------------------------------ ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production            0
PL/SQL Release 12.1.0.1.0 - Production                                                  0
CORE    12.1.0.1.0      Production                                                              0
TNS for Linux: Version 12.1.0.1.0 - Production                                          0
NLSRTL Version 12.1.0.1.0 - Production                                                  0

SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> select host_name from v$instance ;

HOST_NAME
----------------------------------------------------------------
halim-srv.localdomain

SQL>
SQL> select name from V$database ;

NAME
---------
HALIM12C

SQL>
SQL>
SQL>
               =============================================================
                       Identity column "generated by default" example
                ===========================================================
              =================================================================
SQL>
SQL>
SQL>
SQL> create table t_identity_def (id number generated by default as identity, name varchar2(20));

Table created.

SQL>
SQL>
SQL> insert into t_identity_def (name) values ('halim');

1 row created.

SQL>
SQL> insert into t_identity_def (name) values ('halim1');

1 row created.

SQL>
SQL>
SQL> select * from t_identity_def ;

        ID NAME
---------- ------------------------------
         1 halim
         2 halim1

SQL>
SQL>
SQL> truncate table  t_identity_def ;

Table truncated.

SQL>
SQL>
SQL> select * from t_identity_def ;

no rows selected

SQL>
SQL>
SQL> insert into t_identity_def (name) values ('halim2');

1 row created.

SQL>
SQL>
SQL> select * from t_identity_def ;

        ID NAME
---------- ------------------------------
         3 halim2

SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> insert into t_identity_def (id,name) values (null,'halim');
insert into t_identity_def (id,name) values (null,'halim')
                                             *
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SYS"."T_IDENTITY_DEF"."ID")

SQL>
SQL>


               =============================================================
                  Identity column "generated by default on null" example
                ===========================================================
              =================================================================


SQL>
SQL> create table t_identity_def_null (id number generated by default on null as identity, name varchar2(20));

Table created.

SQL>
SQL>
SQL>
SQL> select * from t_identity_def_null ;

no rows selected

SQL>
SQL>
SQL>
SQL> insert into t_identity_def_null (id,name) values (null,'halim');

1 row created.

SQL>
SQL> insert into t_identity_def_null (id,name) values (null,'halim1');

1 row created.

SQL>
SQL>
SQL> commit ;

Commit complete.

SQL>
SQL>
SQL> select * from t_identity_def_null ;

        ID NAME
---------- ------------------------------
         1 halim
         2 halim1

SQL>
SQL>
SQL> insert into t_identity_def_null (name) values ('halim1');

1 row created.

SQL>
SQL>
SQL> select * from t_identity_def_null ;

        ID NAME
---------- ------------------------------
         1 halim
         2 halim1
         3 halim1

SQL>
SQL>
SQL> insert into t_identity_def_null (id,name) values (66,'halim1');

1 row created.

SQL>
SQL> commit ;

Commit complete.

SQL>
SQL>
SQL> select * from t_identity_def_null ;

        ID NAME
---------- ------------------------------
         1 halim
         2 halim1
         3 halim1
        66 halim1

SQL>
SQL>
SQL> insert into t_identity_def_null (name) values ('halim1');

1 row created.

SQL>
SQL> commit ;

Commit complete.

SQL>
SQL> select * from t_identity_def_null ;

        ID NAME
---------- ------------------------------
         1 halim
         2 halim1
         3 halim1
        66 halim1
         4 halim1

SQL>
SQL>
SQL> truncate table  t_identity_def_null ;

Table truncated.

SQL>
SQL>
SQL> select * from  t_identity_def_null;

no rows selected

SQL>
SQL>
SQL> insert into t_identity_def_null (name) values ('halim1');

1 row created.

SQL>
SQL>
SQL> insert into t_identity_def_null (name) values ('halim1');

1 row created.

SQL>
SQL>
SQL> insert into t_identity_def_null (name) values ('halim2');

1 row created.

SQL>
SQL>
SQL> commit ;

Commit complete.

SQL>
SQL>
SQL> select * from  t_identity_def_null;

        ID NAME
---------- ------------------------------
         5 halim1
         6 halim1
         7 halim2

SQL>
SQL>
SQL>

                =============================================================
                       Identity column "generated always" example
                ===========================================================
              =================================================================

SQL>
SQL>
SQL>
SQL> create table t_identity_always (id number generated always as identity, name varchar2(20));

Table created.

SQL>
SQL>
SQL> insert into t_identity_always (name) values ('always');

1 row created.

SQL>
SQL> insert into t_identity_always (name) values ('always1');

1 row created.

SQL>
SQL>
SQL> commit ;

Commit complete.

SQL>
SQL>
SQL> select * from  t_identity_always;

        ID NAME
---------- ------------------------------
         1 always
         2 always1

SQL>
SQL>
SQL> insert into t_identity_always (id,name) values (3,'always1');
insert into t_identity_always (id,name) values (3,'always1')
                               *
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column


SQL>
SQL>
SQL>
SQL>



Posted by at No comments:

Tuesday, June 25, 2013

Oracle Database 12c is available for Download for Linux and Solaris

Now Oracle Database 12c is available for Download. Lets see what inside it......

http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html
Posted by at 3 comments:

Thursday, March 21, 2013

ORA-28547: connection to server failed, probable Oracle Net admin error

Yesterday one of our developer facing following error when she was connecting with SQLPLUS to newly setup database(11.2.0.1) on windows.
and also her forms developer 6i was crashing without any error.

ORA-28547: connection to server failed, probable Oracle Net admin error

Solution:-

I had got the solution by commenting following line from listerner file of the DB.
(PROGRAM = extproc) . like below

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
#(PROGRAM = extproc)
(SID_NAME = orcl)
(ORACLE_HOME = G:\oracle11gR2\app\Administrator\product\11.2.0\dbhome_1)
)
)

and I had to make change also to G:\oracle11gR2\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN\sqlnet.ora file like below.


#SQLNET.AUTHENTICATION_SERVICES= (NTS)
SQLNET.AUTHENTICATION_SERVICES= (NONE)

and then reload the listener like

cmd > lsnrctl
lsnrctl> reload

Cheers.....



Posted by at 11 comments:

Sunday, February 10, 2013

ISO 8583 message, a clear concept and the complete java solution download


I am sorry. Not able to provide the java solution from now. 

(not for production)

What is ISO 8583 message and Its using?

Interchange the DATA that takes place between one system to another system needs to follow standard formats for integration and exchange. There are many such standards exist. ISO 8583 message format is using for financial transaction such as ATM, POS, Credit Card, Mobile Banking, Internet Banking, KIOSK, e-commerce etc. transactions

Financial transaction is communication between 2 systems through socket connection. After connection established, each system can send message in ISO 8583 format which commonly will be request and the other system will response. Following scenarios can occurs.

  • System A request connection to System B (through specific IP and Port).
  • Connection established.
  • System A send Sign-On request message.
  • System B send Sign-on response message.
  • System A will start send Echo request message periodically (e.g every 30 seconds).
  • System B will send Echo response message when receive Echo request message.
  • When financial transaction happens, System A will send Transaction request message.
  • Then System B will send Transaction response message.
  • If something wrong happen (usually timeout so System A didn’t get the response), System A will send Reversal request message to cancel the previous transaction.
  • System B will send Reversal response message.

Now the practically explanation of ISO 8583 Messaging

Someone can ask you to generate a ISO 8583 (version 1987) financial request (Internet banking funds transfer) message by the following fields.  (Just an example)

So what you do?

Internet Banking Funds Transfer
FIELD
NAME OF THE FIELD
FORMAT
CHARS
LENGTH
CODING
SAMPLE DATA
2
Primary Account Number
HLVAR
n
7-19
1000001111222230
3
Processing Code
FIXED
n
6
280000
4
Amount Transaction
FIXED
n
12
000001000000
5
Settlement Amount
FIXED
n
12
000001000000
6
Amount, Cardholder Billing
FIXED
n
12
000001000000
7
Transmission Date & Time
FIXED
n
10
1015113400
9
Settlement Conversion Rate
FIXED
n
8
00000000
10
Cardholder Billing Conversion Rate
FIXED
n
8
00000000
11
System Trace Audit Number
FIXED
n
6
153401
12
Local Transaction Time
FIXED
n
6
113400
13
Local Transaction Date
FIXED
n
4
1015
14
Expiration Date
FIXED
n
4
3209
18
Merchant's Type
FIXED
n
4
5999
22
Point of Service Entry Mode
FIXED
n
3
021
32
Acquiring Institution ID, Code
HLVAR
n
5-11
111111
35
Track-2 Data
HLVAR
z*
37
1000001111222234=3209
37
Retrieval Reference Number
FIXED
an
12
211015113400
39
Response Code
FIXED
an
2
00
41
Card Acceptor Terminal Id.
FIXED
ans
8
90001000
42
Card Acceptor Identification Code
FIXED
ans
15
999998999998998
43
Card Acceptor Name/Location
FIXED
ans
40
1 Gulshan Ave DHAKA BD
49
Transaction Currency Code
FIXED
n
3
050
50
Settlement Currency Code
FIXED
n
3
050
51
Cardholder Billing Currency Code
FIXED
n
3
050
102
Account Identification 1
HLVAR
ans
1111222231
103
Account Identification 2
HLVAR
ans
1231111222235
122
Additional Record Data
HLLVAR
ans
999
IBFT












The ISO 8583 Message will be like below (1987 version): 

Internet Banking fund Transfer

                                                                                                               
0200FEFC44012AE0E00000000000060000401610000011112222302800000000010000000000010000000000010000001015113400000000000000000015340111340010153209599902106111111211000001111222234=320921101511340000900010009999989999989981 Gulshan Ave DHAKA BD                  050050050101111222231131231111222235004IBFT


(here 16 is an extra indicator, it just idicate the 2nd field’s length is 16, actually (2, Primary Account Number) field is Variable length, its highest limit is 19, so it is not actually 19, so it indicate these think here)


Now, I will go details explanation of the above message,

The message has three parts
1)       Message Type Indicator
2)       Bitmap
3)       Data elements


1) Message Type Indicator


        0200

Message Type Indicator (MTI):
                                                MTI is a (n4 - 4 Digits Numeric Value) required for all ISO 8583 messages in order to identify the message class/type. Like 0200, 0210, 1200, 1210 etc.

0200    

0 = Version Number
      2 = Message Class: - Is it financial message?
      0 = Message sub-class/function: - Is it request message?
      0 = Transaction Originator


1)      Version Number

0xxx
ISO 8583:1987 version
1xxx
ISO 8583:1993 version
2xxx
ISO 8583:2003 version

      2) Message Class: - Is it financial message?

                 1    -         Authorization
                 2   -          Financial
                 3  -           Acquirer/ Issuer
                 4  -           Acquirer Reversal/Issuer Reversal
                 5  -            Reconciliation Request Messages
                 6 -             Administrative Request Messages
                 8 -             Network Management Request


3)  Message sub-class/function: - Is it request message?
            0          -     Request
            1         -      Response
            2        -       Advice
            3       -        Advice Response

4)      Transaction Originator

Transaction Originator
Description
0
Acquirer
1
Acquirer repeat
2
Card issuer
3
Card issuer repeat
4
Other
5
Other repeat
6 - 9
Reserved for ISO use

By knowing the MTI value we can identify the type of message.

For Example:

If MTI value is 0200,  then from the table above we can find other details.

  • First position is for Version Number and value in MTI is 0, so message version is based on ISO 8583:1987 (1 is for ISO 8583:1993)
  • Second Position is for Message Class and value in MTI is 2, so message is a financial message.
  • Third position is for message sub-class/function and the value in MTI is 0, so the given message is a request message.
  • Fourth position is for Transaction Originator and value in MTI is 0, so the message is coming from Acquirer.

Message Type can be one of the following values (this is a partial list):
MTI
Description
x100
Authorization Request
x101
Authorization Request Repeat
x102
Authorization completion confirmation
x103
Authorization completion confirmation repeat
x110
Authorization Response
x112
Authorization completion response
x120
Authorization Advice
x121
Authorization advice repeat
x122
Authorization advice completion confirmation
x123
Authorization advice completion confirmation Report
x130
Authorization Advice Response
x132
Authorization advice completion response
x200
Financial Request
x210
Financial Response
x220
Financial Advice
x230
Financial Advice Response
x300
Acquirer File Update Request
x302
Issuer File Update Request
x310
Acquirer File Update Response
x312
Issuer File Update Response
x320
Acquirer File Update Advice
x322
Issuer File Update Advice
x330
Acquirer File Update Advice Response
x332
Issuer File Update Advice Response
x400
Acquirer Reversal Request
x402
Issuer Reversal Request
x410
Acquirer Reversal Request Response
x412
Issuer Reversal Request Response
x420
Acquirer Reversal Advice
x422
Issuer Reversal Advice
x430
Acquirer Reversal Advice Response
x432
Issuer Reversal Advice Response
x5xx
Reconciliation Request Messages
x6xx
Administrative Request Messages
x800
Network Management Request
x810
Network Management Request Response



2) Bitmap


Hexadecimal value: - FEFC44012AE0E0000000000006000040 = 32

And The binary value of this hexa value like below

11111110111111000100010000000001001010101110000011100000000000000000000000000000000000000000000000000110000000000000000001000000 = 128

(here 1= Special meaning of first 1 it’s indicate the existence of secondary bitmap)

Within ISO 8583, a bitmap is a field or subfield within a message which indicates which other data elements or data element subfields may be present elsewhere in a message.
A message will contain at least one bitmap, called the Primary Bitmap which indicates which of Data Elements 1 to 64 are present. A secondary bitmap may also be present, generally as data element one and indicates which of data elements 65 to 128 are present. Similarly, a tertiary, or third, bitmap can be used to indicate the presence or absence of fields 129 to 192, although these data elements are rarely used.
A field is present only when the specific bit in the bitmap is true.
For example, binary = '1000 0010'
 which means fields 1 and 7 are present in the message and fields 2, 3, 4, 5, 6, and 8 are not present.
Now, just try to compare the binary with our message generation requirement (internet banking fund transfer).


3)  Data elements

10000011112222302800000000010000000000010000000000010000001015113400000000000000000015340111340010153209599902106111111211000001111222234=320921101511340000900010009999989999989981 Gulshan Ave DHAKA BD  050050050101111222231131231111222235004IBFT



Data Elements is the essence of the whole ISO message, contain information about the transaction (Pan or account number, date time or the transaction, transaction type, amount, customer id, etc).
Each data element have their on format, attribute and length.
Each data element number also have standard purpose, for example full list of

There are up to 128 data elements specified in the original ISO 8583:1987 standard, and up to 192 data elements in later releases.

ISO-defined data elements
Data element
Type
Usage
1
b 64
Bit map (b 128 if secondary is present and b 192 if tertiary is present)
2
n ..19
Primary account number (PAN)
3
n 6
Processing code
4
n 12
Amount, transaction
5
n 12
Amount, settlement
6
n 12
Amount, cardholder billing
7
n 10
Transmission date & time
8
n 8
Amount, cardholder billing fee
9
n 8
Conversion rate, settlement
10
n 8
Conversion rate, cardholder billing
11
n 6
Systems trace audit number
12
n 6
Time, local transaction (hhmmss)
13
n 4
Date, local transaction (MMDD)
14
n 4
Date, expiration
15
n 4
Date, settlement
16
n 4
Date, conversion
17
n 4
Date, capture
18
n 4
Merchant type
19
n 3
Acquiring institution country code
20
n 3
PAN extended, country code
21
n 3
Forwarding institution. country code
22
n 3
Point of service entry mode
23
n 3
Application PAN number
24
n 3
Function code (ISO 8583:1993)/Network International identifier (NII)
25
n 2
Point of service condition code
26
n 2
Point of service capture code
27
n 1
Authorizing identification response length
28
n 8
Amount, transaction fee
29
n 8
Amount, settlement fee
30
n 8
Amount, transaction processing fee
31
n 8
Amount, settlement processing fee
32
n ..11
Acquiring institution identification code
33
n ..11
Forwarding institution identification code
34
n ..28
Primary account number, extended
35
z ..37
Track 2 data
36
n ...104
Track 3 data
37
an 12
Retrieval reference number
38
an 6
Authorization identification response
39
an 2
Response code
40
an 3
Service restriction code
41
ans 16
Card acceptor terminal identification
42
ans 15
Card acceptor identification code
43
ans 40
Card acceptor name/location (1-23 address 24-36 city 37-38 state 39-40 country)
44
an ..25
Additional response data
45
an ..76
Track 1 data
46
an ...999
Additional data - ISO
47
an ...999
Additional data - national
48
an ...999
Additional data - private
49
an 3
Currency code, transaction
50
an 3
Currency code, settlement
51
an 3
Currency code, cardholder billing
52
b 64
Personal identification number data
53
n 18
Security related control information
54
an ...120
Additional amounts
55
ans ...999
Reserved ISO
56
ans ...999
Reserved ISO
57
ans ...999
Reserved national
58
ans ...999
Reserved national
59
ans ...999
Reserved for national use
60
an .7
Advice/reason code (private reserved)
61
ans ...999
Reserved private
62
ans ...999
Reserved private
63
ans ...999
Reserved private
64
b 16
Message authentication code (MAC)
65
b 64
*Bit indicator of tertiary bitmap only*, tertiary bitmap data follows secondary in message stream.
66
n 1
Settlement code
67
n 2
Extended payment code
68
n 3
Receiving institution country code
69
n 3
Settlement institution country code
70
n 3
Network management information code
71
n 4
Message number
72
ans ...999
Data record (ISO 8583:1993)/n 4 Message number, last(?)
73
n 6
Date, action
74
n 10
Credits, number
75
n 10
Credits, reversal number
76
n 10
Debits, number
77
n 10
Debits, reversal number
78
n 10
Transfer number
79
n 10
Transfer, reversal number
80
n 10
Inquiries number
81
n 10
Authorizations, number
82
n 12
Credits, processing fee amount
83
n 12
Credits, transaction fee amount
84
n 12
Debits, processing fee amount
85
n 12
Debits, transaction fee amount
86
n 15
Credits, amount
87
n 15
Credits, reversal amount
88
n 15
Debits, amount
89
n 15
Debits, reversal amount
90
n 42
Original data elements
91
an 1
File update code
92
n 2
File security code
93
n 5
Response indicator
94
an 7
Service indicator
95
an 42
Replacement amounts
96
an 8
Message security code
97
n 16
Amount, net settlement
98
ans 25
Payee
99
n ..11
Settlement institution identification code
100
n ..11
Receiving institution identification code
101
ans 17
File name
102
ans ..28
Account identification 1
103
ans ..28
Account identification 2
104
ans ...100
Transaction description
105
ans ...999
Reserved for ISO use
106
ans ...999
Reserved for ISO use
107
ans ...999
Reserved for ISO use
108
ans ...999
Reserved for ISO use
109
ans ...999
Reserved for ISO use
110
ans ...999
Reserved for ISO use
111
ans ...999
Reserved for ISO use
112
ans ...999
Reserved for national use
113
n ..11
Authorizing agent institution id code
114
ans ...999
Reserved for national use
115
ans ...999
Reserved for national use
116
ans ...999
Reserved for national use
117
ans ...999
Reserved for national use
118
ans ...999
Reserved for national use
119
ans ...999
Reserved for national use
120
ans ...999
Reserved for private use
121
ans ...999
Reserved for private use
122
ans ...999
Reserved for private use
123
ans ...999
Reserved for private use
124
ans ...255
Info text
125
ans ..50
Network management information
126
ans ...999
Issuer trace id
127
ans ...999
Reserved for private use
128
b 16
Message authentication code


Field Definition
Meaning
n6
Fixed length field of six digits
n.6
LVAR numeric field of up to 6 digits in length
a..11
LLVAR alpha field of up to 11 characters in length
b...999
LLLVAR binary field of up to 999 bytes in length


For details you can follow others links
http://en.wikipedia.org/wiki/ISO_8583 
http://www.codeproject.com/Articles/100084/Introduction-to-ISO-8583 
http://jimmod.com/blog/2011/07/jimmys-blog-iso-8583-introduction-beginners-tutorial/ 
 


Now the Solution:-

You can generate data(pack data) to ISO 8583 message format as well as you can unpack it by this java solution very easily.

1)      Download the Zip file. ( i will attach it later)
2)      Unzip it.
3)      Open the java project with your favorite IDE (like Net Beans )
4)      There are two class one for pack data as ISO another is Unpack data.
5)      Oh, an XML file (test) is there (in zip file) you have keep it in his place because it contain the ISO 8583’s (1987 versions) 128 DE/fields and its definitions.

plz feel free to drop question in the comment if you confused.

Thanks
Posted by at 97 comments:
Subscribe to: Posts (Atom)