Requirements in the China core banking products

Worked in a project to gathering/analysis core banking requirements for the China market. Had been working for banking technology for like 6 years and unfortunately had only one short period to work with a local bank and didn't understand the requirement very much. So always very eager to know more about core banking and the special thing about core banking in China.
I worked in the team for like 8 months and covered localization requirements in Current Account and Savings Account(CASA), Term Deposit(TD) and some other common/cross function requirements like Holiday processing...

Will update the notes of those requirement one by one soon.   

JSON vs WSDL/SOAP integration

Recently worked for 2 banks for integrating the front office(the corporate channel) with their back office system(core banking systems T24) via ESB.

One project/bank is using WSDL/SOAP based web service call for integration. Difficult things include:

1. The team spent some time to add the web service framework(axis 2) into the existing framework/product because of jar version conflicts in local dev environment, as well as in bank's own environment which is based on IBM Websphere;
2. And also for Axis 2 web service, need to generate client source code, which sometime is not that easy to learn for junior developers.  
3. for testing purpose in offshore team, also need to create a simulator/mock up for those web service, which would also include some additional time which is not covered in the initial estimate; 

Actually in the product we're delivering, we use Json format to integrate in some layers(some features uses the json format to develop the application layer with the database layer) and for ajax calls, we all using json format as response(a natural choice for json and ajax calls). It looks would be very good/easy to integrate with other systems via json format as usually json format is easier to understand than SOAP and the API calls looks so simple, the response mock can be simulated in a single flat file. Until I was involved in the second project, the advantages of JSON format is still there, but maybe it's the root of its disadvantages.  

1. JSON format so simple to call without need to generate client source code(BTW, the Axis generated client source code and server code are very difficult to understand/call); 
2. Server side mockup/simulator can be easily prepared via a simple file and just read the file as response; 
3. Just a few jars added and usually no jar version conflict in both the local environment and the server environment; 

also found some problems:

1. it's so hard to get an agreement with the other side on the format of the json format especially when both sides are in a development stage as there is no specification to define the API/format of the message to request/response; 
2. it's easy to add new fields in a json format, so people tended to do enough investigation and analysis before delivery a format;  

In short, would say that WSDL/SOAP based integration is difficult for junior developers without similar experience, but after the first like 2/3 hard weeks. the integration will be more smoothly. While for JSON, it's easier to start, but without a specification and discipline, it will cause problem soon and not easy to fix those problems.

One more difference is in the first project, we're working with a well-known software vendor(Enterprise service bus vendor), which knows their system very much and also integration.  I think they're truly integration experts. Had some experiences working with people from the company and also friends in their Beijing office, all very knowledgeable in the field. While in the second case, we work with a consulting company for the integration, the resources I think are not as good as the previous one, not very knowledgeable to the system; attitude sometimes is also interesting, try to push integration things to the 2 other sides as much as possible, even from system design's view, the work/logic should happen at the ESB side.  

So maybe it's not a technology(WSDL/SOAP vs JSON) problem, maybe it's about whom you're working with.

I am now working in the second project, wish me good luck.  

Weblogic’s config of trust keystore and identity keystore to call https service

 

When access a https service in the web browser, the service provider should provide some certificate on its site which proves it’s the authorized service provider. Sure, in production environment, the certificate should be certificated/provided by well-known certification providers like Verisign. The browser has build-in verify/accept abilities if the certification is from big players like Verisign.

So actually, during https access, the service provider sends something to the browser. If it’s a mutal https access, the browser’s machine really also sends something to the server.

First Scenario: With single Java program

use the following statement to claim the certificates related:

//tells the JVM that I trusted the certificates stored in the trust store.

//usually in the JRE/lib/security/cacerts

System.setProperty("javax.net.ssl.trustStore", trustStorePath); 

//tells the JVM that when I connect the https service

//I will using certificate like below. This contains my identify information.

System.setProperty("javax.net.ssl.keyStore", keyStorePath);

//keystore password, used to extract my identity.

System.setProperty("javax.net.ssl.keyStorePassword", keyStorePassword);

Second Scenario: Program within Weblogic server

First attention: the accesing url should be in a DNS format, don’t use the ip address.

Second Attention: the DNS of the url should be the same as in the keystore’s entry’s CN value. otherwise, hostname verification should be ignored or customized in the server’s SSL configuration console. 

Beside the code above, following configuration are needed.

In the server’s keystore configuration console.

Configure the trust keystore and identify keystore.

Sometimes, both trust keystore and identity keystore are provided with keystore file extension.

Trust keystore tells that the weblogic trusted the one who provides certificate like in the keystore. 

While the identity keystore tells others should trust the weblogic server(as a client) as the one presented in the identity keystore.

In the SSL console, the identity keystore’s alias should be configured. Also, sometimes, it works if you configure ‘using the server’s certificate’ checkbox, and configure the mutual SSL options.

The keystore files provided by the service providers sometimes should be merged with the existing ones, like JRE/lib/security/cacerts.  The command is as below:

First export the certificate stored in the keystore by using the keytool:

keytool –export –alias testserver –file testserver.crt –keystore testtrust.keystore

password of the testtrust.keystore is needed.

Then import the exported certificate to the cacerts file:

keytool –import –alias testserver –file testserver.crt –keystore cacerts

password of the cacerts is needed.

Attention: the testtrust.keystore is generated by the service provider maybe by using the keytool command.

Log4j, isDebugEnabled check necessary when logging a debug message?

 

logger.debug() method in Log4j already checks isDebutEnabled, maybe it’s not necessary to check isDebugEnabled() before every debug() method.

But consider when there is very complex operations in the debug()’s message construction. When calling the debug() method, the construction logic of the message are always executed first. It’ time and space consuming.

So, practically speaking, in situations when the debug message’s construction logic is not complex, for example, a simple constant string, the isDebugEnabled() check before the logging is not necessary.  On the other hand, in situations when the debug message’s construction logic is complex, for example, involving string buffer and loops, the isDebugEnabled() check before the logging is definitely necessary.

In practice, I would prefer to make a consistency to alway add the check before the real message logging.

SWIFT Message Type

SWIFT provides a network to allow financial and non-financial institution(e.g. corporates) to transfer financial transactions through a ‘financial message’.

Currently SWIFT’s network can support the following message standards.

SWIFT MT

   SWIFT messages, developed by SWIFT Standards, consist of five blocks of data including headers, message content, and a trailer. Message types are crucial to identifying content. All SWIFT messages include the literal ‘MT’(Message Type). This is followed by a 3-digit number that denotes the message type, category, and group.

  Example MT304.

  The first digit(3) represents the category. A category denotes messages that relate to particular financial instruments or services such as precious metals(6), Treasury(3), or Travelers Cheques(8). The category denoted by 3 is Treasury Markets.

  The second digit(0) represents a group of related parts in a transaction life cycle. The group indicated by 0 is a financial institution Transfer.

  The third digit(4) is the type that denotes the specific message. There are several hundred message types across the categories. The type represented by 4 is a notification.

  Overview of SWIFT MT Categories:

      MT0xx: System Messages

      MT1xx: Customer Payments and Cheques

      MT2xx: Financial Institution Transfers

      MT3xx: Treasury Markets

      MT4xx: Collection and Cash Letters

      MT5xx: Securities Markets

      MT6xx: Treasury Markets – Metals and Syndications

      MT7xx: Documentary Credits and Guarantees

     MT8xx: Travellers Cheques

     MT9xx: Cash Management and Customer Status.

ISO 15022 MT

 

ISO 20022 MX

BIC code and IBAN code

Technorati 标签: banking

ISO 9362, also known as SWIFT-BIC, BIC code, SWIFT ID or SWIFT code, is a standard format of Business Identifier Codes approved by the ISO. It is a unqiue identification code for both financial and non-financial institutions. These codes are used when transferring money between banks, particularly for international wire transfers, and also for the exchange of other messages between banks.

IBAN: The international bank account number(IBAN) is an international standard for identifying bank accounts across national borders with a minimal risk of propagating transaction errors. It was originally adopted by the European Committee for Banking Standards, and was letter adopted as an international standard under ISO. The official IBAN registrar under ISO is SWIFT.

 

Please refer wike page:

http://en.wikipedia.org/wiki/ISO_9362

http://en.wikipedia.org/wiki/International_Bank_Account_Number

for detailed information.

Add Web Projects from Eclipse to Tomcat

I have a Java projects workspace, with several pure Java projects and several Web J2EE projects.

When import the existing projects into the workspace.  Eclipse can not identify it as a web project, so it can not be added into the tomcat runtime.

After some googling, the solution is as below:
  1. configure the web project's project facet in the properties menu.
     A. First choose 'Dynamic Web Module', please pay attention to the version of the Dynamic Web Module at the end of the check box line.
          The error message tells us that 'Dynamic Web Module 3.0 need Java 6.
     B. Check Java too and change the version of Java to 1.6.
2. Apply and OK.

Install erlang in CentOS 6

 In order to learn more about concurrent programming language, I decide to learn erlang programming in the Linux platform.

Here are the install procedures:
   1. Download the source for linux from :
  2.  Follow the INSTALL.md file in the source package, install available packages.
  3. for the wxWidgets, yum install wxGTK works.  even without wxWidgets, the erlang source can be build and installed.
  4. for openssl, both openSSL and openssl-devel should be installed
  5. some of the devel package should be installed.
  6. build the erlang by reference the INSTALL.md file. 

RPA---Repayment pending Appropriation

In case the customer pays a single or multiple installment amounts before it is due, then the system applies the amount as per the appropriation sequence and any excess over and above that is stored as RPA - Repayment pending appropriation.

Which means that the customer does not get any interest benefit for the same. On the next due date the system uses the RPA amount for satisfying the arrears.

Any shortfall is then recovered from the CASA account (if a CASA draw down is set up), If there are no outstanding arrears then the entire payment is stored in the RPA. Thus the customer only gets the advantage of his arrears getting knocked off in time avoiding any penalty. RPA does not also result in the term or the installment getting reduced as the outstanding does not come down nor does the interest base. In Advance payment the system reduces the interest base on the same day (as defined in the schedule Type) thereby bringing down the interest burden as in case of partial payoff and also stores the advance amount as RPA (principal and interest RPA). Thus like in case of RPA the system recovers the principle and interest arrears on the due date. Thus the salary draw down set on a date before the due date of payment gets the advantage of advance payment as the system recovers the arrears of the next cycle.

RPA maybe widely used in Advanced repayment, which is not widely used.

Oracle Tips 20110614- about data type conversion

This post explains how to use the Explain plan for Oracle's SQL execution to debug sqls.
First create two tables:

create table char_tables (party_id varchar2(30), party_name varchar2(30))

create table number_tables (party_id number, party_name varchar2(30));

insert into number_tables select rownum, table_name from user_tables

insert into char_tables select rownum, table_name from user_tables

insert into char_tables select table_name, table_name|| rownum from user_tables

When issue the following SQL, there is an invalid number exception:
   select  n.party_id,c.party_id
 from  number_tables n,char_tables c
 where   n.party_id = c.party_id and n.party_id =1;

it maybe the data type conversion problem, but let's confirm it with explain plan.

explain plan
 for
select  n.party_id,c.party_id
 from  number_tables n,char_tables c
 where   n.party_id = c.party_id and n.party_id =1;
 commit;
 select * from table(dbms_xplan.display);
  The plan is as below:
  
  We can see from the plan that the sql engine try to convert the VARCHAR2 column into the NUMBER column.

  Let's try another sql:
  explain plan
  for
  delete from char_tables where party_id in (1,2,3);
  commit;
  select * from table(dbms_xplan.display);

the plan is as below:
  
  The SQL engine try to convert the column into the NUMBER type.

 EXPLAIN PLAN
 FOR 
DELETE FROM char_tables WHERE party_id not in (1,2,3);
  COMMIT;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
  The plan is as below:
 
  For above, the NOT clause is translated into the <> operator.  This SQL doesn't work because some values of the party_id column in the char_tables CAN NOT BE CONVERTED INTO NUMBER.

Another SQL:
   explain plan
  for
  delete from char_tables where party_id in (1,2,3);
  commit;
  select * from table(dbms_xplan.display);

  The plan is as below:
 
  
   If the variable's type matches the column's type, no conversion is needed, otherwise, a conversion is applied.
Try to convert the VARCHAR2 into NUMBER.

  Let's considering the scenarios when indexes are used:
   create index char_index on char_tables(party_id) ;
   create index number_index on number_tables(party_id);

The SQL is as below:
   explain plan
  for
  delete from char_tables where party_id in (1,2,3);
  commit;
  select * from table(dbms_xplan.display);

 

  it's a full-table access query. The index is not used because the SQL was translated into the TO_NUMBER()...
 A function index ON TO_NUMBER(party_id) may works.

 Let's try the other SQL:
 
  It's not a full table access, but an index range access.

  So, in Oracle, since data conversion will be automatically executed, the developer has to pay attention to those default tips.

 

 
 

Arrears Appropriation Sequence

 

The sequence which determines the order in which arrears are to be paid in a loan account.

At the product level, the bank can set the rules for treatment of payment when the amount paid is less than the arrears due. A loan installment may only be partly paid by the customer in which case the system will require to know the o rder in which the existing arrears need to be set off. This order is specified in the arrears appropriation sequence. A separate sequence can be specified for regular loans and for non-performing loans.

The sequence makes use of the following abbreviations:

• I – Interest Arrears
• F – Fee Arrears
• S – Legal Fees Arrears
• O – Outgoing Arrears
• D – Suspended Fees
• E – Suspended legal fees
• G – Suspended Premium
• M – Suspended outgoings
• P – Premium Arrears
• N – Non Accrual Interest Arrears (Suspended Interest)
• U – Post Maturity interest Arrears
• T – Post Maturity – Non Accrual Interest arrears (Suspended PMI)
• C – Principal Arrears

It may also be possible that within a particular type of arrear, there maybe more than one installment due. In t his case the longest overdue first flag will specify in which order each each arrear will need to be set off within a date. Ticking this flag will indicate that the system will pick up the earliest date for which arrears are due and then set off arrears based on the appropriate sequence.

Example:

Appropriation sequence specified as – I F A C S O D G E M P L N U T

(Interest, Fees, Arrears on interest(penalty interest), Capital etc.)

Total arrears due – INR 15,000

Total amount paid on 20th May 2004 – INR 10000

The arrears on account and their appropriation as follows for longest overdue first flag not ticked:

Order the arrears by the sequence specified at the product level.

 

Due Date	Arrear Type	Amount Due	Order of Set off	Amount of Appropriation	Cumulative Amount setoff
31-Mar-2004	C - Capital	4000	5	3500	10000
31-Mar-2004	I - Interest	2000	1	2000	2000
30-Apr-2004	A – Arrears on Interest	100	4	1000	6500
30-Apr-2004	C - Capital	4500	None	None	None (since there is no money for this arrear)
30-Apr-2004	I - Interest	3000	2	3000	5000
15-May-2004	F - Fees	500	3	500	500

 

The arrears on account and their appropriation as follows if longest overdue first flag is ticked:

Order the arrears based on the due date of the arrear as well as the sequence specified at the product level.

Due Date	Arrear Type	Amount Due	Order of Set off	Amount of Appropriation	Cumulative Amount setoff
31-Mar-2004	C - Capital	4000	2	4000	6000
31-Mar-2004	I - Interest	2000	1	2000	2000
30-Apr-2004	A – Arrears on Interest	100	5	500	10000
30-Apr-2004	C - Capital	4500	None	None	None (there is no money for this arrear)
30-Apr-2004	I - Interest	3000	3	3000	9000
15-May-2004	F - Fees	500	4	500	9500

Algorithm of Fibonacci numbers

 long fibonacci(int n){
  assert(n>=0);
  if(n == 0) return 1;
  if(n == 1) return 1;
  return fibonacci(n-1) + fibonacci(n-2);
}

long fibonacci(int n){
  if(n ==0 ) return 1;
  long[] fibs = new long[n+1];
  fibs[0] = 1;
  fibs[0] = 2;
  for(int i=2;i<=n;i++){
    fibs[i] = fibs[i-1] + fibs[i-2];
  }
  return fibs[n];
}

long fibonacci(int n){
  if(n==0) || (n==1) return 1;
  int sum = 0;
  int number0 = 1;
  int number1 = 1;
  for(int i=2;i<=n;i++){
    sum = number0 + number1;
    number0 = number1;
    number1 = sum;
  }
  return sum;
}

CASA: Current And Saving Account

RD in FCR is implemented as a variant of CASA.

For CASA. there are four things to do when configure a CASA account and make a transaction.

1. CASA definition

CASA definitions include the following sides: 

    A. Product Definitions

    B. Bank Parameters

    C. RD Product Rates

    D. RD Grace Days

2. Account Opening Process

     The Account opening process will use Branch code and Customer Information from the CIF module to open an account for the customer.

     A. Open Account

     B. A/C operating Instructions

     C. A/C Reporting Code/User Define Fields

     D. Account Memo

3.  Online Transactions

    Online transaction includes the following categories:

      A. Credit

           Cash/Single Cheque/Data Entry for Consolidated/Postdated(Deposit/ Cancel) / Purchase Cheque/ Liquidating BC/Liquidating DD/ Liquidating TC/ Purchase TC/ Redemption by Transfer/ Transferring Funds/ Disbursement by Transfer/ Making Loan Rescission/Loans Refund/Miscellaneous Credit/ IDC/ODC

      B. Debit

           Cash/Single Cheque/ Sale TC/Issue BC/Sale DD/Sale TT/Transferring Funds/Payin by Transfer/Paying Installment by Transfer/Making Partial Pay-off of Loan/Setting a Loan by Fund Transfer/Loan Advance Payment by Transfer/Miscellaneous Debit/Common Billing Transactions/Bill Payment

      C. Inquiry

           Account Balance/Account Statement/Inward Check status/ Account number checkdigit/Account status Audit Trail/SWIFT Statement Details

      D. Passbook:

           Issue New Passbook/Inquiring on passbook/passbook balance check digit.

      E. Fund Management / Account Instructions

           Hold Funds/Delete Earmark/Standing Instructions/Sweep-out/Sweep-in/Group Accounts/Inward Direct Debit/Account cheque purchase limit/Sweep out execution to CASA/TD/ SC for Sweep out/ Sweep-in processing –TD Redemption/Sweep-in Processing –Unmark lien/Standing Instruction Execution/Hold Funds Expiry

      F. Statement

            Special Frequency/Account Address Maintenance/Statement generation

      G. Cheque Book

             Check Box Request/Check Book Maintenance/Maintaining Stop cheque instruction

      H. Collateral & Limit

            Limit Collateral Inquiry/OD Limit Master Maintenance/Account Collateral/OD Limit Expiry Processing/Identifying Limits Expiring next month

      I. Account Closure

             Account close/Close out withdrawal by cash/close-out withdrawal by banker’s check/Close out withdrawal by GL

      J. Recurring Deposit (RD)

          RD Schedual Inquiry

      K. Other

          Maintaining the Account Master/Account Master Attribute/Account Status/SC Account NSF Preference/Interest Adjustment/Account SC waiver/Interest Freeze/JPA account cross reference

4. Batch Transactions

       A. NPA (Non-performing Asset, popularly known as Bad Loan).

               Reminder Processing/NPA classification processing/NPA Reserve Creation/NPA Appropriation

       B,. Interest and charges

               Interest Accrual/Reset ADB Statistics/ Interest Capitalization/ SC capitalization/ SC processing for transactions/SC/Interest – Internal Transfer processing.

      C. Others

                Transaction posting/Other House Keeping/RD Maturity Processing/GL handoff/Dormancy Processing/Combine Stream/Unclaimed Processing/TDS Processing/Dormancy Automatic re-activation.

Humble thoughts on data structure

 

Technorati Tags: data structure

Data structure actually contains two words. The first one is data, which is the abstract representation of reality in the computer programs.

For the other word, structure, which means the way to organize one or more than more things. The main purpose to organize something is to make the access to them available and easier.

Hereafter, something are referred as a set of data. The set here is not the same concept of set in pure mathematics, nor the class set  in java collection framework. Here element in the set can be duplicated or not, depends on the specific scenario.

Sometimes, the set of data are organized as an array because maybe random access and indexed access to the elements in the set are necessary and important. Sometimes, the set of data are organized as a linked list because maybe it’s very difficult to allocate a large space at the beginning (a necessity when using array ) or the above features are not important. 

Generally, array and linked list are two main low-level data structures that widely used in the compute program. Know how to manipulate elements and design algorithms on these two structures are very important to programmers.

High level data structures like List, Map, Set , Tree, graph are all represented by the two elementary structures.

In order to master the manipulation of the two, some practices are necessary. Let’s take some examples of practices to learn arrays and linked lists in programs.

Equilibrium array

Technorati Tags: Algorithm,Equibilbrium array,data structure

Equilibrium index of a sequence is an index such that the sum of elements at lower indexes is equal to the sum of elements at higher indexes. For example, in a sequence A:
A[0]=-7 A[1]=1 A[2]=5 A[3]=2 A[4]=-4 A[5]=3 A[6]=0

3 is an equilibrium index, because:
A[0]+A[1]+A[2]=A[4]+A[5]+A[6]

6 is also an equilibrium index, because:
A[0]+A[1]+A[2]+A[3]+A[4]+A[5]=0

(sum of zero elements is zero) 7 is not an equilibrium index, because it is not a valid index of sequence A.
Assume the sum of zero elements is equal zero. Write a function

int equi(int[] A);

that given a sequence, returns its equilibrium index (any) or -1 if no equilibrium indexes exist. Assume that the sequence may be very long.

 

Solution:

1. First get the sum of all the numbers in the array first. sum

2. adding the array from the left one by one. tempSum

3. if the sum – tempSum – A[i] == tempSum, then i is an equibibrium index.

int equip(int[] A){
  int sum = sum(A);
  int tempSum = 0;
  for(int i=0;i<A.length;i++){
   if(sum - tempSum - A[i] == tempSum){
    return i;
   }
   tempSum += A[i];
   }
   return -1;
}

long sum(int [] A){
int sum = 0;
for(int i=0;i<A.length;i++){
   sum += A[i];
  }
}

The best solution, which are very often available should be the O(N) complex one. Always try to find the solution with O(N) time complexity or less.

Given a positive integer number, how to find if it’s anagram.

Technorati Tags: Algorithm,Anagram of integer

public class Symmetric {

    // Solution A

    int[] digits = new int[] {};

    boolean symmetrical(int i) {

        int n = i;

        int index = 0;

        while (n != 0) {

            digits[index++] = n % 10;

            n = n / 10;

        }

        return symmetrical(digits, 0, digits.length - 1);

    }

    //recursive way to just if the array is symmetrical

    boolean symmetrical(int[] digits, int begin, int end) {

        if (begin >= end)

            return true;

        return (digits[begin] == digits[end])

                && symmetrical(digits, begin + 1, end - 1);

    }

    //traditional way to judge if the array is symmetrical

    boolean symmetrical(int[] digits, int begin,int end){

      int length = (end - begin)/2;

      for(int i=0;i< length;i++){

       if(digits[begin+i] != digits[end-i]){ return false;}

      }

      return true;

    }

    //end of Solution A

    // Solution B

    //just compute the new number from the end to first.  This method is recommended since there is no need to assign an new int[] array.

    boolean symmetrical_B(int i) {

        int n = i;

        int m = 0;

        int t = 0;

        while (n != 0) {

            t = n % 10;

            m = m * 10 + t;

            n = n / 10;

        }

        return m == i;

    }

}

When using the first algorithm, the algorithm tries to convert an integer into an array. That’s really a space consuming method. For every single problem, please consider just using the existing space and data structure, don’t try to convert the existing data structure into another structure.

Rownum in Oracle for filtering

 It's a traditional question in Oracle:

  How to select the 11th record to the 20 th record in a database table.
A. The simplest form:
  How to select the 1 to 10 records.
  Just the following sql:
  SELECT * FROM ta WHERE rownum < 11
  will select the first 10 records if there are 10 or more records in the database.
  SELECT 1 FROM ta WHERE rownum < 11
  will select 10 ones if there are 10 or more records in the database, if there is no record in the database, there will be no records returned by the SQL.

B. How to select the 11th to 20 th records.
  Please don't use :
  SELECT * FROM ta WHERE rownum BETWEEN 11 AND 21
    between means 11<=x<=21
  The statement will return no record. it's because the rownum of the statement begins with 0 and is a virtual column, it will add 1 unit a new row is caught. So, from beginning, the rownum will never meet the condition.
  Let's use another way:
 SELECT * FROM ( 
( SELECT T.*, ROWNUM ROWCOUNTER FROM ta T WHERE rownum <21 )
  ) WHERE ROWCOUNTER > 10

  Once, I thought the following statement may works:
   ( SELECT t.ctr_cont_no FROM ta WHERE rownum < 21 )
   MINUS
   (SELECT t.ctr_cont_no FROM ta WHERE rownum < 11)
  but it turns out the MINUS operator is an set operator, so the result may have less than 10 results. So use the following instead:
  (SELECT t.rowid, t.ctr_cont_no FROM ta WHERE ROWNUM <21 )
   MINUS
  (SELECT t.rowid,t.ctr_cont_no FROM ta WHERE ROWNUM < 11)
 it works but with an additional column.
  For
  (SELECT T.* FROM ta WHERE rownum < 21)
  MINUS
  (SELECT T.* FROM ta WHWER rownum < 10)
 I don't know the exact, then have the following test.
  create a single column table:
   create table sin_column_test (COLUMN1 NUMBER NOT NULL);
   insert into sin_column_test select 10 from all_objects where rownum <=100;

  issue the command:
    (select * from sin_column_test where rownum< 21 )
   minus
  (select * from sin_column_test where rownum < 11)

 There is no record returned. So T.* only means the columns defined in the DML SQL, not any virtual columns are included.

Automating Oracle 10g Database Startup and shutdown on Linux

  Steps to configure the automatically startup and shutdown on Linux

   Once the instance is created, edit the "/etc/oratab" file setting the restart flag for each instance to 'Y'.
   iflex:/u01/app/oracle/product/10.1.0/db_1:Y

   Next, edit the dbstart batch
   vi $ORACLE_HOME/bin/dbstart
   edit the line
   ORACLE_HOME_LISTENER=$ORACLE_HOME
  
   Create a file called /etc/init.d/dbora as the root user, containing the following:
   #!/bin/sh
   # chkconfig: 345 99 10
   # description: Oracle auto start-stop script
  ORA_HOME=/u01/app/oracle/product/10.1.0/db_1
  ORA_OWNER=oracle

  if [ ! -f $ORA_HOME/bin/dbstart ]
  then
    echo "Oracle Startup: cannot start"
    exit
  fi

  case "$1" in
    'start')
    #start the oracle database
    #The following command assumes that the oracle
    #login will not prompt the user for any value
    su - $ORA_OWNER -c "$ORA_HOME/bin/dbstart $ORA_HOME"
    touch /var/lock/subsys/dbora
    ;;
    'stop')
    #stop the oracle database
    #The following command assumes that the oracle
    #login will not prompt the user for any value
    su - $ORA_OWNER -c "$ORA_HOME/bin/dbshut $ORA_HOME"
    rm -f /var/lock/subsys/dbora
    ;;
 esac
 
  Please pay attention two the second and the third comment line, it should begin with chkconfig and description to make it follow the chkconfig standard. Please refer the 'man chkconfig' to see the full version of this standard.
 
  Login as root, issue the following command:
  chmod 750 /etc/init.d/dbora
  Associate the dbora service with the appropriate run levels and set it to auto-start using the following command:
  chkconfig --add dbora
  The relevant instances should now startup/shutdown automatically at the system startup and shutdown.

Tablespaces, Datafiles, and Control files

Tablespace---The primary logical database structures of any oracle database. datafiles correspond to each tablespace.

Oracle stores data logically in tablespace and physically in datafiles associated with the corresponding tablespace.  Objects store in tablespaces may span several datafiles but a datafile is a physical structure associated with only one tablespace.

    Database, tablespaces and datafiles:

         An Oracle database consists of one or more logical storage units called tablespaces, which collectively store all of the database’s data;

         Each tablespace in an Oracle database consists of one or more files called datafiles, which are physical structures that conform to the operating system in which Oracle is running.

         A database’s data is collectively stored in the datafiles that constitute each tablespace of the database.

Oracle-Managed Files

      Oracle-managed files eliminate the need for the DBA to directly manage the operating system files comprising an Oracle database.  The DBA can then specify operations in terms of database objects rather than filenames.  Oracle internally uses standard file system interfaces to create and delete files as needed for the following database structures:

          A. Tablespace

          B. Redo log files

          C. Control files

      The size of a tablespace is the size of the datafiles that consitute the tablespace. The size of the a database is the collective size of the tablespaces that consitute the database. The DBA can add a datafile to a tablespace; add a new tablespace; increase the size of a datafile. Tablespaces are divided into logical units of storage called segments, which are further divided into …

Bigfile tablespace

      Bigfile tablespace allows Oracle database to contain tablespaces made up of single large files rather than numerous smaller ones.

SYSTEM tablespace

     Every Oracle database contains a tablespace named SYSTEM, which Oracle creates automatically when the database is created. The SYSTEM tablespace is always online when the database is open.

     Create a locally managed SYSTEM tablespace or migrate an existing dictionary managed SYSTEM tablespace to a locally managed format.  With a locally managed SYSTEM tablespace, dictionary managed tablespace can not be created. If a tablespace is locally managed, it cannot be reverted back to being dictionary managed.

      The SYSTEM tablespace always contains the data dictionary tables for the entire database.  The data dictionary tables are stored in datafile 1.

      All data stored on behalf of stored PL/SQL program units (procedures, functions, packages and triggers) resides in the SYSTEM tablespace.

SYSAUX tablespace

     The SYSAUX tablespace is an auxiliary tablespace to the SYSTEM tablespace. Many database components use the SYSAUX tablespace as their default location to store data. The SYSAUX tablespace is always created during database creation or database upgrade. The SYSAUX tablespace provides a centralized location for database metadata that does not reside in the SYSTEM tablespace.  During normal database operation, the Oracle database server does not allow the SYSAUX tablespace to be dropped or renamed. Transportable tablespaces for SYSAUX is not supported.

Undo tablespace

      Undo tablespaces are special tablespaces used solely for storing undo information. Any other segment types (e.g tables or indexes) are not allowed to be created in the undo tablespace. 

      Each undo tablespace is composed of a set of undo files and is locally managed. Undo blocks are also grouped in extents and the status of each extent is represented in the bitmap.

Default Temporary Tablespace

      When the SYSTEM tablespace is locally managed, the DBA must define at least one default temporary tablespace when creating a database. A locally managed SYSTEM tablespace cannot be used for default temporary storage.  If SYSTEM is dictionary managed and if The DBA do not define default temporary tablespace when creating the database, the SYSTEM is still used for default temporary storage.

 

      Tablespace allocates space in extents. Tablespace can use two different methods to keep track of their free and used space:

          A. Locally managed tablespace: extent management by the tablespace

          B. Dictionary managed tablespace: extent management by the data dictionary.

       Locally Managed Tablespace: a tablespace that manages its own extents maintains a bitmap in each datafile to keep track of the free or used status of blocks in that datafile. Each bit in the bitmap corresponds to a block or a group of blocks. Oracle changes the bitmap values to show the new status of the blocks when an extent is allocated for freed for reuse.  These changes do not generate rollback information because they do not update tables in the data dictionary.

      Locally managed tablespaces’ advantages:

           1. automatically tracks adjacent free space, eliminating the need to coalesce free extents.

           2. avoids recursive space management operations. Such recursive operations can occur in dictionary managed tablespace if consuming or releasing space in an extent results in another operation that consumes or release space in a data dictionary table or rollback segment.

     The sizes of extents that are managed locally can be determined automatically by the system. Alternatively, all extents can have the same size in a locally managed tablespace and override object storage options.

     Segment Space Management in Locally Management Tablespace can be both AUTO and MANUAL.

 

Dictionary Managed Tablespace

     For a tablespace that uses the data dictionary to manage its extents, Oracle updates the appropriate tables in the data dictionary whenever an extent is allocated or freed for reuse. Oracle Also stores rollback information about each update of the dictionary tables.

 

Oracle supports multiple block sizes in a database. The standard block size is used for the SYSTEM tablespace. The DBA specifies the standard block size by setting the initialization parameter DB_BLOCK_SIZE, values from 2K to 32K.

A DBA can bring any tablespace other than the SYSTEM tablespace online(accessible) or offline(not accessible) whenever the database is open. The SYSTEM tablespace is always online when the database is open because the data dictionary must always be available to Oracle.

The primary purpose of read-only tablespace is to eliminate the need to perform backup and recovery of large, static portions of a database.  Oracle never updates the fiels of a read-only tablespace, and therefore the files can be reside on read-only media such as CD-ROMs or WORM drives.

Temporary tablespace for sort operations

    The DBA can manage space for sort operations more efficiently by designating one or more temporary tablespace exclusively for sorts.  Doing so effectively eliminates serialization of space management operations involved in the allocation and deallocation of sort space.  A single SQL operation can use more than one temporary tablespace for sorting. All operations that use sorts, including joins, index builds, ordering, computing aggregates and collecting optimizer statistics benefit from temporary tablespace.

    One or more temporary tablespace can be used only for sort segments. Sort segments are used when a segment is shared by multiple sort operations. One sort segment exists for every instance that performs a sort operation in a given tablespace.  Temporary tablespace provides performance improvements when you have multiple sorts that are too large to fit into memory.

Transport of Tablespace between database

     A transportable tablespace lets you move a subset of an Oracle database from one Oracle database to another, even cross different platforms.

     A tablespace repository is a collection of tablespace sets.  Tablespace repositories are built on file group repositories, but tablespace repositories only contain the files required to move or copy tablespaces between databases.

    A tablespace in an Oracle database consists of one or more physical datafiles.  a datafile can be associated with only one tablespace and only one database.

 

Datafiles

     When a datafile is first created, the allocated disk space is formatted but does not contain any user data. Oracle reserves the space to hold the data for future segments of the associated tablespace—it’s used exclusively by Oracle.

     Data associated with schema objects in a tablespace is physically stored in one or more of the datafiles that constitute the tabelspace. Schema object does not correspond to a specific datafile; a datafile is a repository for the data of any schema object within a specific tablespace.  The DBA can alter the size of a datafile after its creation or can specify that a datafile should dynamically grow as schema objects in the tablespace grow.

Logical storage structures in Oracle database

Oracle allocates logical database space for all data in a database. The units of database space allocation are data blocks, extents, and segments.

At the finest level of granularity, Oracle stores data in data blocks( also called logical blocks, Oracle blocks or pages). One data block corresponds to a specific number of bytes of physical database space on disk.

The next level of logical database space is an extent. An extent is a specific number of contiguous data blocks allocated for storing a specific type of information.

A segment is a set of extents, each of which has been allocated for a specific data structure and all of which are stored in the same table-space.  Each table’s data is stored in its own data segment, which each index’s data is stored in its own index segment.  If the table or index is partitioned, each partition is stored in its own segment. When the existing extents of a segment are full, Oracle allocates another extent for that segment.  Because extents are allocated as needed, the extents of a segment may or may not be contiguous on disk.  A segment and all its extents are stored in one table-space. Within a tablespace, a segment can include extends from more than one file; The segment can span datafiles. Each extent can contain data from only one datafile.

If you allocate an extent to a specific instance(Manually storage management), the blocks are immediately allocated to the free list. However, if the extent is not allowed to a specific instance(Automatically storage management), then the blocks themselves are allocated only when the high water mark moves.  The high water mark is the boundary between used and unused space in a segment. 

Data blocks

Storage space in the datafiles of a database  is managed in units called data blocks.  At the OS level, all data is stored in bytes. Oracle block size is different from system blocks.

The standard block size is specified by the DB_BLOCK_SIZE initialization parameter.

Data block format is similar regardless of whether the data block contains table, index, or clustered data.

   A. common and variable Header: the header contains general block information, such as the block address and the type of segment ( for example, data or index).

   B. Table directory: This portion of the data block contains information about the table having rows in this block.

   C. Row directory: contains information about the actual rows in the block(including addresses for each row piece in the row data area). The space for a row is not reclaimed when the row is deleted. Oracle reuses this space only when new rows are inserted in the block.

    E. Overhead: The data block header, table directory, and row directory are referred to collectively as overhead.

    F. Row Data: contains table or index data, rows can span blocks.

    G. Free space: for insertion of new rows and for updates to rows that requir additional space. In data blocks allocated for the data segment of a table or cluster, or for the index segment of an index, free space can also hold transaction entries. A transaction entry is required in a block for each INSERT, UPDATE, DELETE and SELECT … FOR UPDATE statement accessing one or more rows in the block. The transaction entry tells that the block is now updating by this transaction, so others have to wait?????

Free Space Management

    Free space can be managed automatically or manually.

    Free space can be managed automatically inside database segments. The in-segment free/used space is tracked using bitmaps, as opposed to free lists. You specify automatic segment-space management when you create a locally managed tablespace. The specification then applies to all segments subsequently created in this tablespace.

Availability and  Optimization of Free Space in a Data Block

Two types of statement can increase the free space of one or more data blocks: DELETE statement, and UPDATE statements that update existing values to smaller values.  The released space from these types of statements is available for subsequent INSERT statements under the following conditions.

     1. If the INSERT statement is in the same transaction and subsequent to the statement that frees space, then the INSERT statement can use the space made available

     2. If the INSERT statement is in a separate transaction from the statement that frees space, then the INSERT statement can use the space made available only after the other transaction commits and only if the space is needed.

   Based in pre-conditions, Oracle will compress free spaces in a block.

Row Chaining and Migrating

   In two circumstances, the data for a row in a table maybe too large to fit into a single data block.

     1. The rows is too large to fit into one data block when it is first inserted. Then Oracle stores the data for the row in a chain of data blocks reserved for that segment. Row chaining most often occurs with large rows, such as rows that contain a column of data types LONG or LONG RAW. Row chaining in these cases is unavoidable.

      2. A row that originally fit into one data block is updatable so that the overall row length increases, and the block’s free space is already completely filled. Oracle migrates the data for the entire row to a new data block, assuming the entire row can fit in a new block. Oracle preserves the original row piece of a migrated row to point to the new block containing the migrated row. The rowid of a migrated row does not change.

When a row is chained or migrated, I/O performance associated with this row decreases because Oracle must scan more than one data block to retrieve the information for the row.

 

PCTFREE, PCTUSED and Row Chaining

For manually managed tablespaces, two space management parameters: PCTFREE and PCTUSED enable you to control the use of the free space of inserts and updatesd to the rows in all the data blocks of a particular segment.

   PCTFREE: sets the minimum percentage of a data block to be reserved as free space for possible updates to rows that already exist in that block. If set PCTFREE=20, this states 20% of each data block in this table’s data segment be kept free and available for possible updates to the existing rows already within each block.  New rows can be added to the row data area, and corresponding information can be added to the variable portions of the overhead area until the row data and overhead total 80% of the total block size.

   PCTUSED: set the minimum percentage of a block that can be used for row data plus overhead before new rows are added to the block.  After a data block is filled to the limit determined by PCTFREE, oracle considers the block unavailable for the insertion of new rows until the percentage of that block falls beneath the parameter PCTUSED. Until this value is achieved, Oracle uses the free space of the data block only for updates to rows already contained in the data block.

   Which means with PCTFREE and PCTUSED parameters, the free space is sometimes only available to update commands.

   For each data and index segment, oracle maintains one or more free lists---lists of data blocks that have been allocated for that segment’s extents and have free space greater than PCTFREE. This blocks are available for inserts.  If the free space in that block is not large enough and the block is at least PCTUSED, then Oracle takes the block off the free list. Multiple free lists for each segment can reduce contention for free lists when concurrent inserts take place.

  After issue a DELETE or UPDATE statement, Oracle processes the statement and checkes to see if the space being used in the block is now less than PCTUSED. If it is, then the block goes to the beginning of the transaction free list, and it is the first of the available blocks to be used in that transaction.

 

Overview of Extents

An Extent is made up a number of contiguous data blocks. When the existing space in a segment is completely used, Oracle allocates a new extent for the segment.  

When create a table, Oracle allocates to the table’s data segment an initial extent of a specified number of data blocks. When the initial extent become full and more space is required, Oracle automatically allocates an incremental extent for that segment. An incremental extent is a subsequent extent of the same or greater size than the previously allocated extent in that segment.

The header block of each segment contains a directory of the extents in that segment.

Storage parameters expressed in terms of extents define every segment.

Data dictionary managed table-space and locally managed table-space by bitmap.

The extents of a segment do not return to the table-space until you drop the schema object whose data is stored in the segment with some exceptions:

When extents are freed, Oracle modifies the bitmap in the data-file(for locally managed table-space) or updates the data dictionary ( for dictionary managed table-space) to reflect the regained extents as available space. Any data in the blocks of freed extents becomes inaccessible.

Extents in Non-clustered tables

As long as a non-clustered table exists or until you truncate the table, any data block allocated to its data segment remains allocated for the table.  Even if you delete all rows of a table, oracle does not reclaim the data blocks for use by other objects in the table-space. After the dropping of a non-clustered table, this space can be reclaimed when other extents require free space. Oracle reclaims all the extents and makes them available for other schema objects in the same tablespace.

In dictionary managed table-space,when a segment requires an extent larger than the available extents, Oracle identifies and combines contiguous reclaimed extents to form a larger one. this is called coalescing extents.

For clustered tables, if you drop one table in a cluster, the data segment remains for the other tables in the cluster, and no extents are de-allocated. You can also truncate clusters(except for hash clusters) to free extents. 

All extents allocated to an index segment remain allocated as long as the index exists.

Extents in Temporary Segments

When Oracle completes the execution of a statement requiring a temporary segment, Oracle automatically drops the temporary segment and returns the extents allocated for that segment to the associated table-space.  Multiple sorts, can use sort segments in temporary tablespaces designated exclusively for sorts. The sort segments are allocated only once for the instance and they are not returned after the sort, but remain available for other multiple sorts.  A temporary segment in a temporary table contains data for multiple statements of a single transaction or session. Oracle drops the temporary segment at the end of the transaction or session, returning the extents allocated for that segment to the associated tables.  So, there are two categories of temporary table-space, the one for temporary table and the one for multiple sorts.

Extents in Rollback Segments

Oracle periodically checks the rollback segments of the database to see if they have grown larger than their optimal size. If a rollback segment is larger than is optimal (that is , it has too many extents), then Oracle automatically de-allocates one or more extents from the roll-back segment.

 

Overview of Segments

A segment is a set of extents that contains all the data for a specific logical storage structure within a table-space.

Data Segments

A single data segment in an Oracle database holds all of the data for one of the following:

    1. A table that is not partitioned or clustered

    2. A partition of a partitioned table

    3. A cluster of tables

The specified storage parameters when creating the table/cluster affect the efficiency of data retrieval and storage for the data segment associated with the object.

Index Segment

Every non-partitioned index in an Oracle database has a single index segment to hold all of its data. For a partitioned index, every partition has a single index segment to hold its data.  Also the storage parameters directly affects the efficiency of data retrieval and storage.

Temporary Segments

      A. When processing queries, Oracle often requires temporary workspace for intermediate stages of SQL statement parsing and execution. Oracle automatically allocates this disk space called a temporary segment.  Typically, Oracle requires a temporary segments as a database area for sorting. Oracle does not create a segment if the sorting operation can be done in memory or if Oracle finds some other way to perform the operation using indexes. Operations that requires temporary segments include: CREATE INDEX/SELECT …ORDER/ SELECT DISTINCT/SELECT ..GROUP/SELECT …UNION/ SELECT …INTERSECT/SELECT …MINUS

      B. Segments in Temporary tables and their indexes. Oracle can also allocate temporary segments for temporary tables and indexes created on temporary tables. Temporary tables hold data that exists only for the duration of a transaction or session.

      Oracle allocates temporary segments differently for queries and temporary tables.

      A. Allocation of Temporary segments for queries: If no temporary table-space is defined for the user, then the default temporary table-space is the SYSTEM table-space.  Oracle drops temporary segments when the statement completes.

      B. Oracle allocates segments for a temporary table when the first INSERT into that table is issued. The first INSERT into a temporary table allocates the segments for the table and its indexes, creates the root page for the indexes, and allocates any LOB segments.  Segments for a temporary table are allocated in a temporary table-space for the user who created the temporary table. Oracle drops segments for a transaction/session-specific temporary table at the end of the transaction/session-specific temporary table at the end of the transaction/session.  If other transactions/sessions share the use of that temporary table, the segments containing their data remain in the table.  Means a temporary segment for every transaction/session.

Automatic Undo Management

    Oracle maintains information to nullify changes made to the database. This information consists of records of the actions of transactions, collectively known as undo. Oracle use undo to do the following:

    A. Rollback an active transaction

    B. Recover a terminated transaction

    C. Provide read consistency

    D. Recovery from logical corruptions

     Oracle recommends you use undo tablespaces to manage undo rather rollback segments.

     Undo mode provides a more flexible way to migrate from manual undo ma

Data Integrity and Triggers

An integrity constraint is a declarative way to define a business rule for a column of a table.  If an integrity constraint is created for a table and some existing table data does not satisfy the constraint, then the constraint can not be enforced.  After a constraint is defined, if any of the results of a DML statement violate the integrity constraint, then the statement is rolled back, and an error is returned.  Integrity constraint are defined with a table and are stored as part of the table’s definition in the data dictionary.

Supported Integrity constraint:

1. NOT NULL

2. UNIQUE KEY

3. CHECK.

4. PRIMARY KEY

5. FOREIGN KEY

Key is used in the definitions of several types of integrity constraints. Different types of keys include:

   1. Primary Key

   2. Unique Key

   3. Foreign Key

    4. Referenced Key

Triggers

Triggers are procedures written in PL/SQL, java or C that run (fire) implicitly whenever a table or view is modified or when some user actions or database system actions occur.   

 

SQL Statement categories:

    Data definition Language (DDL) Statements

    Data Manipulation Language (DML) Statements

    Transaction Control Statements

    Session Control Statements

     System control Statements

      Embedded SQL statements

Structures used for recovery

Oracle uses several structures to provide complete recovery from an instance or disk failure: the redo log, undo records, a control file, and database backups.

The Redo Log: is a set of files that protect altered database data in memory that has not been written to the datafiles. The redo log can consist of the online redo log and the archived redo log.  The online redo log is a set of two or more online redo log files that record all changes made to the database, including uncommitted and committed changes.  The background process LGWR writes the redo entries sequentially to an online redo log file. LGWR writes redo entries continually, and it also writes a commit record every time a user process commits a transaction.

Filled online redo files can be manually or automatically archived before being reused, creating archived redo logs. ARCHIVELOG and NOARCHIVELOG options.

Undo Records: undo records are stored in undo tablespaces.  Oracle uses the undo data for a variety of purposes, including accessing before-images of blocks changed in uncommitted transactions. During database recovery, Oracle applies all changes recorded in the redo log and then uses undo information to roll back any uncommitted transactions.

Control files: The control files include information about the file structure of the database and the current log sequence number being written by LGWR. During normal recovery procedures, the information in a control file guides the automatic progression of the recovery operation.

Concurrency

Concurrency is the simultaneous access of the same data by many users. Without adequate concurrency controls, data could be updated or changed improperly, compromising data integrity. Destructive interactions between concurrent transactions must be prevented.

Oracle resolves such issues by using various types of locks and a multiversion consistency model.  This features are based on the concept of a transaction.

Read consistency:

read consistency supports the following features:

   1.  Guarantees that the set of data seen by a statement is consistent with respect to a single point in time and does not change during statement execution(statement-level read consistency)

   2. Ensures that readers of database data do not wait for writers or other readers of the same data.

   3.  Ensures that writers of database data do not wait for readers of the same data

   4. Ensures that writers only wait for other writers if they attempt to update identical rows in concurrent transactions.

   The simplest way to think of read consistency is to imagine each user operating a private copy of the database, hence the multi-version consistency model.

Read consistency, undo records and transactions: When an update occurs, the original data values changed by the update are recorded in the database undo records. As long as this update remains part of an uncommitted transaction, any user that later queries the modified data views the original data values.   Oracle uses current information in the system global area and information in the undo records to construct a read-consistent view of a table’s data for a query. 

The transaction is key to Oracle’s strategy for providing read consistency. This unit of committed or uncommitted SQL statements:

    1. Dictates the start point for read-consistent views generated on behalf of readers.

     2. Controls when modified data can be seen by other transactions of the database for reading or updating.

Read-only transactions: By default, oracle guarantees statement-level read consistency. The set of data returned by a single query is consistent with respect to a single point in time. In some situations, you might also require transaction-level read consistency. This is the ability to run multiple queries within a single transaction, all of which are read-consistent with respect to the same point in time, so that queries in this transaction do not see the effects of intervening committed transactions. If you want to run a number of queries against multiple tables and if you are not doing any updating ,you prefer a read-only transaction.

Locking Mechanisms

Oracle also uses locks to control concurrent access to data. When updating information, the data server holds that information with a lock until the update is submitted or committed.  Until that happens, no one else can make changes to the locked information.

Oracle provides unique non-escalating row-level locking. Oracle always locks only the row of information being updated. Oracle can lock an unlimited number or rows so users can work concurrently without unnecessary delays.

Automatic Locking: Oracle locking is performed automatically and requires no user action.

The two general types of locks are exclusive locks and share locks. Only one exclusive lock can be placed on a resource(such as a row or a table), Many share locks can be placed on a single resource.  Both exclusive and share locks always allow queries on the locked resource but prohibit other activity on the resource(such as updates and deletes).

Manual locking: …..

Quiesce Database:

How Oracle works

Most basic level of operations that Oracle performs. User and associated server process are on separate computers.

    1. An instance has started on the computer running Oracle (a Host or database server)

    2. A computer running an application ( a local computer or client workstation) runs the application in a user process. The client application attempts to establish a connection to the server using the proper Oracle Net Services driver.

    3. The server is running the proper Oracle Net Services driver. The server detects the connection request from the application and creates a dedicated server process on behalf of the user process.

    4. The user runs a SQL statement and commits the transaction.

    5. The server process receives the statement and checks the shared pool for any shared SQL area that contains a similar SQL statement. If a shared SQL area is found, then the server process checks the user’s access privileges to the requested data, and the previously existing shared SQL area is used to process the statement. If not, then a new shared SQL area is allocated for the statement, so it can be parsed and processed.

    6. The server process retrieves any necessary data values from the actual datafile (table) or those stored in the SGA.

    7. The server process modifies data in the system global area. The DBWn process writes modified blocks permanently to the disk when doing so is efficient. Because the transaction is committed, the LGWR process immediately records the transaction in the redo log file.

    8. If the transaction is successful, then the server process sends a message across the network to the application. If it is not successful, then an error message is transmitted.

    9. Throughout this entire procedure, the other background processes run, watching the conditions that require intervention. The database server also manages other users’ transactions and prevents contention between transactions that request the same data.

Overview of Accessing the database

Network connections

Oracle Net Services is Oracle’s mechanism for interfacing with the communication protocols used by the networks that facilitate distributed processing and distributed database.  Oracle Net Service supports communications on all major network protocols, including TCP/IP, HTTP, FTP and WebDAV.

Oracle Net, a component of Oracle Net Services, enables a network session from a client application to an Oracle database server. Once a network session is established, Oracle Net acts as the data courier for both the client application and the database server. It establishes and maintains the connection between the client application and database server, as well as exchanges messages between them. Oracle Net can perform these jobs because it is located on each computer in the network.

Starting up the database

Three steps to starting an Oracle database:

     1. Start an instance

     2. Mount the database

     3. Open the database

Oracle instance

Technorati Tags: Oracle instance

An oracle database server consists of an Oracle database and an Oracle instance. Every time a database is started, a system global area(SGA) is allocated and Oracle background processes are started. The combination of the background processes and memory buffers is called an Oracle instance.

Real Application Clusters: Multiple Instance Systems

Some hardware architectures like shared disk systems, enables multiple computers to share access to data, software, or peripheral devices. Real Application clusters (RAC) takes advantage of such architecture by running multiple instances that share a single physical database. RAC enables access to a single database by users on multiple computers with increased performance.

Instance Memory Structures: The basic memory structures are associated with Oracle: the system global area and the program global area.

System global area: SGA

SGA is a shared memory region that contains data and control information for one Oracle instance. Oracle allocates the SGA when an instance starts and de-allocate it when the instance shuts down. Each instance has its own SGA.

User currently connected to an Oracle database share the data in the SGA. Information stored in the SGA is divided into several types of memory structures, including the database buffers, redo log buffer and the shared pool.

   Database Buffer Cache: store the most recently used blocks of data. The buffer cache contains modified as well as unmodified blocks.

    Redo Log Buffer: the redo log buffer stores redo entries – a log of changes made to the database. The redo entries stored in the redo log buffers are written to an online redo log, which is used if database recovery is necessary.  The size of the redo log is static.

   Shared Pool of the SGA: contains shared memory constructs, such as shared SQL areas. A shared SQL area is required to process every unique SQL statement submitted to a database. A shared SQL area contains information such as the parse tree and execution plan for the corresponding statement. A single shared SQL area is used by multiple applications that issue the same statement, leaving more shared memory for other uses.

    Statement Handles or Cursors: A cursor is a handler or name for a private SQL area in which a parsed statement and other information for processing the statement are kept.

Program Global Area(PGA)

    PGA is a memory buffer that contains data and control information for a server process. A PGA is created by Oracle when a server process is started. The information in a PGA depends on the Oracle configuration.

Oracle Background Processes

Oracle creates a set of background process for each instance.  The background processes consolidate functions that would otherwise be handled by multiple Oracle program running for each user process.  Each Oracle instance can use several background processes.

Process Architecture

A process is a ‘thread of control’ or a mechanism in an OS that can run a series of steps.  A process generally has its own private memory area in which it runs.  An Oracle database server has two general types of processes: user processes and Oracle processes.

   User (Client) Process: user processes are created and maintained to run the software code of an application program. User processes also manage communication with the server process through the program interface.

   Oracle process: Oracle processes are invoked by other processes to perform functions on behalf of the invoking process.  Oracle creates server processes to handle requests from connected user processes. A server process communicates with the user process and interacts with Oracle to carry out requests from the associated user process.

    Oracle can be configured to vary the number of user processes for each server process. In a dedicated server configuration, a server process handles requests for a single user process. A shared server configuration lets many user processes share a small number of server process.  On some systems, the user and server processes are separate, while on others, they are combined into a single process. Client/Server systems separate the user and server processes.

Schema and common schema objects

Technorati Tags: Oracle schema

A schema is a collection of database objects. A schema is owned by a database user and has the same name as that user.  Schema objects are the logical structures that directly refer to the database’s data. Objects in the same schema can be in different tablespaces, and a tablespace can hold objects from different schemas.

Tables:

basic unit of data storage in an Oracle database. Database tables hold all user-accessible data. each table has columns and rows.

Indexes

Indexes are optional structures associated with tables. An Oracle index provides an access path to table data.  Indexes are useful when applications frequently query a table for a range of rows or a specific row.

Views

Views are customized presentations of data in one or more tables or other views. Views derive their data from the tables on which they are based, referred to as the base tables of the views.  Like tables, views can be queried, updated, inserted into and deleted from, with some restrictions. All operations performed on a view actually affect the base tables of the view.

Clusters

clusters are groups of one or more tables physically stored together because they share common columns and are often used together. Because related rows are physically stored together, disk access time improves.

Synonyms

A synonym is an alias for any table, view, materialized view, sequence, procedure, function, package, type, Java class schema object, user-defined object type, or another synonym. A synonym requires no storage other than its definition in the data dictionary.

Oracle data dictionary

Each Oracle database has a data dictionary. An Oracle data dictionary is a set of tables and views that are used as a read-only reference about the database. A data dictionary stores information about both the logical and physical structure of the database.  A data dictionary also stores the following information:

  1. The valid users of an oracle database

  2. Information about integrity constraints defined for tables in the database

  3. The amount of space allocated for a schema object and how much of it is in use.

  During database operation, Oracle reads the data dictionary to verify that schema objects exist and that users have proper access to them.

Oracle Database Concept

The database has logical structure and physical structures. Physical and logical structures are separate, the physical storage of data can be managed without affecting the access to logical storage structures.

Physical Database Structure includes datafiles, redo log files and control files.

 Data files

Every oracle database has one or more physical datafiles. The datafiles contain all the database data. The data of logical database structures, such as tables and indexes, is physically stored in the datafiles allocated for a database. The datafiels are located in the $ORACLE_BASE/oradata directory

  A data file can be associated with only one database. Datafiles can be set to automatically extend when the database runs out of space. One or more datafiles form a logical unit of database storage called a tablespace. .

Modified or new data is not necessarily written to a datafile immediately. Data is pooled in memory and written to the appropriate detafiles all at once, as determined by the database writer process (DBWn) background process.

Control Files

Every oracle database has a control file. A control file contains entries that specify the physical structure of the database.  Every time, an instance of an Oracle database is started, its control file identifies the database and redo log files that must be opened for database operation to proceed.

Redo log file

Parameter files

Alert and trace log file

backup files

Logical database structures

Table spaces

A database is divided into logical storage units called tablespaces, which group related logical structures together. One or more datafiles are explicitly created for each tablespace to physically store the data of all the logical structure. The combined size of the datafiles in a tablespace is the total storage capacity of the tablespace.

Every Oracle database contains a SYSTEM tablespace and a SYSAUX tablespace. Oracle creates them automatically when the database is created. Each of this two tablespaces has a datafiles, named system01.dbf and sysaux01.dbf.

Bigfile tablespaces and smallfile tablespace.

Online and offline tablespaces.

Data Blocks: At the finest level of granularity, oracle database data is stored in data blocks.

Extents: A specific number of contiguous data blocks,obtained in a single allocation, used to store a specific type of information.

Segments: A segment is a set of extents allocated for a certain logical structure. Type of segments include: Data Segment, Index Segment, Temporary segment for temporary tables, Rollback segment for undo,

Login the Linux using single user mode

I

Technorati Tags: Linux,Single user mode

t’s common to boot a Unix/Linux system into single user mode to debug boot problems.

1.  Select the kernel that should be booted.

2. Press ‘a’ to modify what parameters are passed to the kernel and init.

2.5 use Backspace to remove rhgb and quiet from the list of kernel parameters. The rhgb parameter starts up the graphical bootup display and quiet suppresses almost all of the kernel’s output on boot.

3. Type S. Adding an s to the end of the line tells the system to start in single user mode. Make sure there is space before the s. This can be an S, an s or the word single.

4. Press enter.

After the system has booted, a root shell will be loaded without requiring the root user’s password to be entered.

At the command prompt, verify that this is indeed a root shell by issuing the id command.

With root access, it is possible to perform a file system check, turn off services that are stopping the system from booting correctly, change the root password, fix incorrect network settings, etc.

Sometimes, it’s necessary to change runlevels from single user mode to the default runlevel for the system:

Press Ctrl + d

Linux command

locate and which:
  using locate to find files in the file system. using which to locate command/shells under the $PATH.

dmesg
  print the boot able message. Seems it will print all the hda/sda and other drivers.  Examining the messages produced by the kernel as it loads is a good way to see what hardware has been detected. A copy of the kernel message is made during the boot sequence. The message is located at /var/log/dmesg file. So the dmesg command has the same effect as cat /var/log/dmesg.

dd
  convert and copy a file. For example,
  dd if=/dev/hda of=MBR bs=446 count=1
  will copy the /dev/hda (boot area) of the system to the file MBR.
  dd if=/dev/hda of=/dev/sda bs=446 count=1
  will backup the MBR of the system to the /dev/sda disk.
  the dd command will remove any partitions or data currenctly on the /dev/sda device.
  so pay special attention to the OF parameter of this command when it's a device.

mount and umount
  mount a file system and umount a file system.

passwd
  change the password of the current user. If the current root password is unknown, then you can boot to single user mode (where you will be automatically logged in as root without being prompted for a password) and then run the passwd command from the prompt.
  system-config-rootpassword will change the password of the root.

system-config-***
  system-config-time or system-config-date programs can change the time zone and the date/time settings after installation of the system.
  system-config-authentication program can be used to configure the password file location and encryption algorithm.
  system-config-*** tools are used to configure various devices and services.
 

lspci, kudzu, ddcprobe, lsusb
  lspci command can be used to show all devices connected to the PCI bus.
  kudzu command for getting information about the video card .
  kudze -p -c VIDEO
  ddcprobe command can detect the resolutions and refresh rates supported by the monitor.
  lsusb command seems to list all the usb hardware drivers.

sysctl
  /proc was to provide information about running process on the system. Commands link ps depend on /proc as the source of process information.  Inside /proc exists a sub-directory whose name corresponds with the PID of each process on the system.
  configuring the kernel via /proc/sys.
  using cat command and echo command to view and set the value in /proc/sys..
  cat /proc/sys/fs/file-max
  echo "110000" > /proc/sys/fs/file-max
  sysctl command is a better way to view and set value in /proc/.
to view:  # sysctl fs.file-max
   fs.file-max=767604
to set: # sysctl -w "fs.file-max=1100000"
  sysctl can also list all available kernel tuning options as shown:
  #sysctl -a
Add/modify the line in the /etc/sysctl.conf file can persist the kernel tuning parameters.  
  Changes to the values in /proc will not survive as reboot. so change the /etc/sysctl.conf file maybe a better way.
 
date and hwclock command, ntpdate and rdate
The hwclock command is the primary way of interacting with the hardware clock.
  hwclock --show
  hwclock --set --date "Fri Apr  8 13:33:12 HKT 2011"
  date --set "Fri Apr  8 13:33:12 HKT 2011"

RPM and the YUM..
  rpm database, typically located in the /var/lib/rpm directory, contains a list of all installed applications, and the files which belong to those applications.
  rpm package file's name:
  name-version-release.architecture.rpm
  The name indicates the software which is packages in that RPM. Usually, this name is the name of the application.
   Version indicates the version of the software which is packaged in that RPM.
   Release in the RPM package file name is used to indicate revisions of the packaging of that particular version of that application.
  Architecture is the platform on which that RPM can be executed, if binary. Typical values seen here include:
  i386 -any 32-bit interl-compatible CPU
  i686 - any 686-class 32-bit intel-compatible CPU
  x86_64 - any AMD64 or Intel EM64T CPUs
  ppc - any 32 bit powerPC CPU
  ppc64 - any 64-bit powerPC CPU
  ia64 - any 64 bit Itanium CPU
  noarch - the package can be used on any CPU architecture
  scr - package application source code, patches, and scripts specifying how that source code should be configured and compiled into binary RPMs.
  RPM package files are cpio archives with a binary header attached.
  The rpm command is one of the most important commands in the Linux command in the system.

/etc/yum.conf file and the /etc/yum.repos.d directory.

/sbin/runlevel
  command can be used to view the current runlevel. It also displays the previous run level.
  To switch runlevels, the standard way is to use the init or the telinit command.

/sbin/init
  the /etc/inittab file contains init program's configuration.

chkconfig , ntsysv and serviceconf
  update and query runlevel information for system services.
  To determine what the current configuration of a given daemon is ,issue the following command:
  chkconfig --list sshd
  This will list the availability of the sshd service in all the run levels.
  to view the status of all daemons run:
  chkconfig --list
The ntsysv and serviceconf commands provide a graphical browser, respectively of all services available on the system and the ability to select graphically which services should be started or stopped in a given run level. Using system-config-services is the ultimate choice.

Shutdown, reboot and poweroff
  The shutdown command is the preferred method of rebooting or shutting down linux. Shutdown will automatically notify all logged-in users of the impending action.  As the time approaches, it will alert logged-in users with a greater frequency and urgency.

 

uname

The uname command will print certain system information.

for example,

uname –r will print the kernal name and version.

 

useradd, userdel, usermod

New users can be added to the system by the root user with the useradd command. Behavior of the useradd program is controlled by the following:

   1. command-line switches

   2. /etc/login.defs configuration file

   3. /etc/default/useradd configuration file

useradd oracle

passwd oracle  --to set the password of the user oracle.

 

The corresponding userdel command is used to delete existing accounts. userdel only supports a single option –r, which tells the command to delete the user’s account, home directory and mail pool file.

 

In addition to editing the passwd file directly by vipw command, the usermod command can be used by root to modify existing accounts.  for example:

usermod –d /home2/brandon –m brandon

The command will move a user to a new home directory (updating the passwd entry and moving existing files).

usermod –G webguys,mis jdoe

will add the user jdoe to the secondary groups webguys and mis.

For the above command, maybe vigr to graphically edit the /etc/group file is also good choice.

 

The chage command to change a user password’s age.

chage –M 90 oracle

 

su:

su username.

The effective UID and GID will be switched to that of username, but nothing else about the environment will be changed. This can sometimes pose difficulties, since different users often have different PATH statements.

su – username

rather just switching effective UIDs and GIDs, it will change the entire environment, behaving as if logged in as username.

su –c command username

will just execute a command.

 

fdisk
to create a partition table of the disk.
to print the partition table for a given drive, use fdisk -l /dev/XXX, for example:
  fdisk -l /dev/hda
  fdisk -l /dev/sda
The most commonly used filesystem is ext3, the standard Linux filesystem.

mkfs
After creating a partition with fdisk or sfdisk, a filesystem is typically created on it using mkfs.
mkfs is the standard utility used to create linux filesystems.
--the following command will create an ext2 file system.
mkfs /dev/hda1
--the following command will create an MS-DOS file system.
mkfs -t msdos /dev/hda1  
The ext3 file system is essentially just an ext2 file system with a journaling file; the on-disk file system structures of ext2 and ext3 are identical.
mkfs -j
mkfs.ext3
mke2fs -j
the above commands will create an ext3 file system.

mount and umount
  A basic task of most administrators is maintaining file system layouts. New partitions can be transparently grafted into existing file system structures using the mount command.  These mounts are listed in /etc/fstab so they will occur automatically at boot.
  The mount and umount modify a text database, /etc/mtab, which lists all currently mounted partitions. The kernel itself also maintains a database, /proc/mounts, which lists all currently mounted partitions.  The /proc/mounts file tends to be more accurate than /etc/mtab.
  Figure out why the file system is still busy.
  fuser: displays the PIDs of processes using the specified files or file systems.
  lsof: lists information about files opened by processes
  lslk: lists locks held on local files.

 

Determining Disk usage

df and du command.

File system can run out of both free space (data blocks) and name space for new files (inodes), so usage of both needs to be monitored.

###du bind.ps evolution-1.o.tar.gz

will display the disk usage of the given two files.

###du –h bind.ps evolution-1.o.tar.gz

will display the disk usage of the given two files in a human readable format

 

df command can be used to display disk usage(blocks) on mounted file system.

####df –h

display the human readable format of the df command’s result.

 

Backup software

dump, restore, tar, cpio, star, pax…. cdrecord

didn’t consider the tar command as a backup software.

Viewing process
ps, pstree, top, jobs, pkill
ps - standard process listing tool.
  ps -ef |grep *** maybe the mostly used ps command.
  It can be replaced by the pgrep command.
pstree - show process tree
top - provides summary information and stats on each running
         process in real-time fashion
jobs - display backgrounded and suspended processes invoked from shell
  Tools like ps, top and pstree use the /proc file system to collect the data they need to operate.
   strace command: to watch the system calls that these commands make when running.
   stract ps
  In order to see threads in the output of ps, use the -m switch.
  pkill is a combination of pgrep and kill, use it carefully.

Signaling a Process
   Processes react to various signals sent to them by other processes or manually by the system administrator. Common signals include Hang Up: SIGHUP, Terminate : SIGTERM and Stop : SIGSTOP.
Process's priority.
  nice and renice command
  nice -5 mysql.  the kernel's process scheduler uses this nice value as a relative indicator that it is allowed shift the scheduling priority of that process.
  pgrep mysql
  renice 10 3306
  The mysql process (process id=3306) 's nice value would then be 5.
  As with the nice command, only root can use negative values with renice. In addition, only root can renice other users' processes.

Syslog facilities and priorities
  The facility is used to specify what type of program is generating the message.  syslogd can then be configured to handle messages from different sources differently.

System logs
  logrotate
Process accounting
ac
  The ac command is used to view summaries of user logins. It usually works even when process accounting is not being used.  It accesses the /var/log/wtmp file.
  ac --individual
  ac -d

Limiting resource consumption - via ulimit
The ulimit command provides control over the consumption of resources available to the shell and to processes started by it.
  What is a hard limit and a soft limit??? A hard limit can not be increased once it's set, a soft limit maybe increased to the value of the hard limit.