[Index]
System design for the Basic Banking System

July 24, 2009 - March 8, 2010

Author: Bart klein Ikink
Introduction



This document describes the system design for the Basic Banking System. The Basic Banking System (BBS) is an information system that supports the basic banking operations in the Natural Money Financial System. The BBS should be a simple system in the functional as well in the technical sense. The BBS should cover all basic operations of banking. The functionality of the BBS must be standardised for all banks.

The BBS performs the following functions:
- administrating account holders;
- administrating accounts;
- handling transactions;
- system administration and managing master data;
- accounting.

The following issues are discussed:
- design considerations;
- data model;
- functional model;
- technical architecture.

Progress information:

The design of the BBS is shown as it was on October 5, 2009. As of March 8, 2010 the design has been refined further while around 30% of the BBS has been built and tested. Probably the BBS will be finished and ready for use somewhere between January 1, 2011 and April 1, 2011.




Design considerations




Introduction

The following issues are discussed:
- simplicity;
- scope;
- software design method;
- user interface;
- decentralisation;
- transaction routing.



Simplicity

Small banks should not have to consider issues of organisational design and systems design. Those things should be straightforward and easy to implement. Therefore the basic operations of a bank should be simplified and standardised. This makes it possible to operate a small bank in a cost effective way. The BBS should be part of the public domain. This will subject the BBS to the most extensive scrutiny possible which will help to make the BBS more robust and secure. Software companies may offer software packages that offer the same functionality.

Dilbert Feature Creep Many software products are degenerating because of feature creep. According to Wikipedia feature creep is the proliferation of features in a product such as computer software. Extra features go beyond the basic function of the product and can result in baroque over-complication rather than simple, elegant design. Feature creep can also be observed in other products such as cars and mobile phones.

The most common cause of feature creep is the desire to provide the consumer with a more useful or desirable product in order to increase sales. However once the point at which a product does everything that it is expected to do is reached, the manufacturer is left with the choice of adding unneeded functions at the cost of efficiency, or sticking with the old product at the cost of a perceived lack of improvement.

People that have money to spend on systems such as managers and politicians often do not understand the basic concepts of systems design, while users and engineers often like features. Therefore high tech features in software products or weapons systems do increase sales because they impress decision makers. Decision makers often hire consultants for advice but those consultants seldom have to implement or operate the systems they recommend. In many cases the decision making process resembles the Dilbert comic.



Scope

Introduction

Limiting the scope of a system will make a system more manageable. Effort should be put in minimising the project scope and lowering the expectations regarding the time table and the product features. It should be well understood that the BBS is a basic system with minimal functionality that only performs the essential functions of banking. It is imperative that there is no pressure to modify the project scope.

According to systems theory the most optimal designs are functional designs that maximise internal relationships and minimise external relationships. Therefore non essential functionality and non banking functionality should not be included in the BBS.


Spinning off functionality

Limiting the functionality of the BBS to the essential functions of banking will greatly improve the manageability of the BBS. Supportive functions that are not part of the basic banking operations may be spinned off. Also country specific functions should be implemented in separate software packages.

The following functionality may be spinned off to separate software packages:
- messaging: the messaging solution may be a standardised solution that can be applied to other software packages. If this is the case, the messaging functionality may become a separate Message Broker application;
- country specific address check: many countries have a postal code system that makes it possible to uniquely identify addresses and check address existence. Because natural money banks operate in only one country, using a locally installed country specific address check application may be more efficient than using a remote Address Verification Service;
- mortgage acceptance: mortgage acceptance criteria differ per country and therefore mortgage acceptance not be part of the BBS;
- logical standby: the logical standby solution may be a standardised solution that can be applied to other software packages. If this is the case, the logical standby solution may become a separate software package.

The software packages that are to be used together with the BBS should be built on the same software platform as the BBS, preferably using the same design and programming standards.


The BBS should not cover all operations of a bank

The BBS should not become an ERP package that covers all organisational operations within a bank, like sales, procurement, project management and human resource management. ERP may sometimes be efficient for large companies. Most smaller companies do not benefit from ERP. The banks in the Natural Money Financial System are mostly small and very small and may sometimes be run by volunteers. Those banks should not use ERP. The BBS should therefore be a basic banking system that only covers the basic banking operations.


Data warehouses

A fool can ask more than a thousand wise men can answer.
(Dutch proverb)

Many organisations spend large amounts of money on copying existing data or keeping obsolete data. This data is used to answer all kinds of questions like: How much of product 34716 is sold at branch 5421 on January 17, 2006? Such queries may be useful to keep inventories at adequate levels, but the value of using this data for other purposes is questionable.

Staff and managers need an occupation. If there is no productive work to do, they may find a purpose in crunching numbers, making reports and monitoring trends using cross sections, line charts, bar charts and pie charts. Data warehouses have the potential of bringing an organisation out of touch with reality. Most management information that comes from data warehousing can also be retrieved by being in touch with reality and using common sense.

If an organisation feels the need for data warehouses, this is probably a sign of the organisation being to large to manage or the numbers of staff and management being to high. Therefore the best step is often not to build a data warehouse but to reduce staff and management or to break up the organisation into more manageable parts. Most natural money banks are small and the customers are often known in person. Those banks are manageable and should not make use of data warehouses.



Software design method

Introduction

The software design method should be structured. Only structured software design methods can result in good designs if they are well applied.

Ideally the software design method should consist of the following phases:
- choosing a tailor made system or opt for a standard software package;
- modeling the business processes, the data and the functions;
- choosing a software development platform (if needed);
- setting up development standards and procedures (if needed).


Choosing for a tailor made system or a standard software package

Software systems should help to implement the business of an organisation. The first step is to describe the functional areas of a business and to identify the essential or primary parts of the business. In larger organisations or organisations that have a unique business model the primary parts of a business often are best served by information systems that are tailor made.

An organisation should choose software packages that best fit the business requirements of the supportive functions. The organisation should adapt its supportive processes to the standard software package. A supportive process almost never justifies the cost of making adaptations to the software package or making a tailor made system because the supportive process is not essential for the organisation.

The BBS covers the primary part of the natural money banking business. For the banks in the Natural Money Financial System the BBS will become a software package because those banks will often be small in size.


Modeling the business processes, the data and the functions

When building tailor made systems the business processes should be identified and modeled. When modeling business processes, the needed business functions and data to support the business processes emerge. The data should then be modeled into a data model and the functions should be modeled into a function model. The data model is by far the most important part of the design because of the following reasons:
- good data models result in dramatically lower software development costs because better data models reduce the need for programming;
- good data models result in dramatically lower system maintenance costs over the systems lifetime because systems with good data models can be less complex;
- changing the data model once the system is operational is extremely expensive because it often involves a data conversion.


Choosing a software development platform

Experts often tend to classify software development platforms into groups like 3GL, 4GL and 5GL. This does not give a good insight in the nature of the software development process because the higher generation languages tend to incorporate the features of the previous generations. The following division of development methods may provide a better insight in the way the nature of software development changed over time:
- machine programming;
- machine driven programming (for example: assembler);
- structured process driven programming (for example: Cobol);
- data driven development (for example: SQL);
- object oriented or feature driven development (for example: Java).

Object oriented or feature driven development may be a good choice for realising computer games, but is not the best method for building systems that administrate business processes because those processes are data driven. Therefore the BBS can best be built on a data driven development platform.


Setting up development standards and procedures

Development standards are useful to make software programs easier to maintain. Structural standards such as programming all checks and procedures referring to a table in one package are more important than lay out. However well structured programs that have a well structured lay out are the easiest to maintain.

A programming language can do a lot to make programs easy to understand. C and Java programs often hide their functionality while SQL and PL/SQL programs often show their functionality. SQL and PL/SQL programs can often be built based on a functional design only and rarely ever need a technical design.



Organising design and development

Introduction

The BBS may have to be built in a situation of crisis. Failure is not an option and there may be little time. Therefore a structured design and development approach has to be chosen. Management and procedures should be reduced to a minimum and the project should be executed by highly skilled professionals that communicate openly and are accustomed to make decisions based on their own professional judgement. Management efforts should be focussed on reducing the length of the critical path by redistributing work or adding resources.

The following issues are discussed:
- procedures and quality assurance;
- consequences for the project;
- consequences for organising the IT-department;
- consequences for information systems.


Procedures and quality assurance

Procedures are introduced to improve organisational performance. However in many cases only reducing complexity will improve the performance of an organisation. Procedures have become a burden to many organisations, but they are perceived as needed because work is divided into small tasks and responsibilities while nobody oversees the complete picture.

Many organisations have implemented procedures such as quality assurance systems like ISO to improve the quality of their products and their organisational processes. For software development and system administration quality assurance systems such as CMMI and ITIL have been devised. Those systems have been introduced because products, systems and organisations have become complex and employees have a limited field of expertise and responsibility which leads to communication errors.

Procedures such as quality assurance systems have setbacks. They tend to formalise all aspects of organisation. The result is often that people depend on procedures and feel no responsibility for their work and therefore do not think for themselves. Because of this managers often start to work on mission statements, team spirit, social skills and organisational culture using books and theories of management gurus. However mission statements, team spirit, social skills and organisational culture do not fix the structural problems of an organisation that make people disinterested in their work. Procedures can never replace human judgement. Humans will likely perform better if they find their work to be interesting and feel that their judgement is needed.

Managers often think that tools, such as tools for development, testing and project management will solve problems that often are the result from a lack of skills. Additional tools require additional skills and make matters only worse. Developers, testers and project managers that have the required skills, can do their work adequately using only a text editor.

A fool with a tool is still a fool.
(saying in the world of information technology)

Projects often fail because of too much focus on side issues and too little focus on the critical success factors of a project. System scope and project scope are often the most important critical success factors. Procedures make matters worse because they move management attention away from the critical success factors. Distraction often results in project failure which is perceived as a crisis. In such a crisis managers may choose for an unstructured approach to get results in a shorter timeframe when it is better to focus on project scope, the critical path and reducing the number of procedures, software platforms and tools. Unstructured approach methods such as prototyping and timeboxing may give managers the impression that progress is made, but those methods often result in unmanageable systems.


Consequences for the project

System scope and project scope are often the most important critical success factors. If they are well managed then only a few essential procedures are needed for project success. Those essential procedures concern the following:
- personnel;
- project deadline, transitional interfaces and critical path management;
- testing of software before using it;
- keeping sources and versions of sources.

Choosing and keeping the right personnel is essential for the success of projects and maintaining software systems:
- Each team should have at least two very experienced members that can make decisions based on professional judgement. There should be at least two very experienced designers, two very experienced developers and two very experienced testers and two very experienced system administrators in a project that is crucial for the business.
- When there is not enough workload to keep the project members busy all the time. It is better to assign team members to additional projects than to withdraw some of them from the project if the project is crucial for the success of the business.
- People that work for the project must be willing and able to assume different roles. A software developer should also be able to make designs and test software.
- Efforts must be made to keep people working on the same systems as long as possible. This will make them more effective and this should also be rewarded financially.

The project should not have a deadline but an estimated delivery date. The project implementation date should therefore be made independent of the project implementation dates of related projects. This may result in building transitional interfaces that may have to work both ways. The transitional interfaces must ensure that related projects can deliver without the project being ready and the project can deliver without the related projects being ready. Giving a project a strict deadline only leads to postponements and inferior products. Much project management effort is spent on the organisational politics of managing the project deadline which may result in pushing project members to deliver faster and removing essential functions from the software release. Project management effort should be focussed on managing the length of the critical path. The estimated delivery date should only be made final after testing showed that the system is ready to be implemented.

No software should go into production without extensive testing. There should be a procedure for testing. All possible test cases should be written out and tested. The testers should have extensive experience with the development tools. They must be able to create test sets and write test scripts using the development tools. Senior testers must be able to oversee the functionality of the complete system.

There should be a central database for sources. Developers should not keep sources in directories. There should be a simple procedure for versioning, maintaining a baseline version, a development version and a bugfix version.
- When the development of a software version is finished and the development of a new software version starts, the database containing the sources will be copied from the development source database into a baseline source database containing baseline software versions and a bugfix source database containing bugfix software versions.
- The baseline source database should contain baseline releases for documentation purposes only.
- During the life cycle of an application system version, bugs are fixed in the bugfix source database. The bugfixes also have to be implemented in the development source database.
- The new application system version is developed in the development source database.
- It is often not a good idea to start the development of a new software version before the previous version is released. It may be a better idea to let developers perform test work to speed up the release of the previous version.


Consequences for organising the IT-department

In a less complex environment, IT specialists can have a broader employability and combine fields of expertise. The use of combined fields of expertise will reduce communication problems within an IT organisation and therefore the management efforts needed to run an IT organisation. The following types of expertise may be combined:
- information analysis / project management;
- functional design / development / functional test;
- technical design / development / technical test;
- technical test / application system administration;
- database administration / operating system administration.

To serve customers better, IT organisations can better be organised around customers instead of technical disciplines. If the IT environment is straightforward, IT specialists can have a broader employability. In this way it becomes easier to make IT specialists serve specific customers. Knowledge of the customer's systems will become more concentrated. This will improve the quality of service for the customer. Also there is no conflict of interest because IT specialists do not serve different customers. They are dedicated to their customer. This will also improve the quality of service for the customer.

The customer may be a department of an organisation that has its own IT department or an organisation that has outsourced its operations to an IT facilitator. The customer should have a budget that can be spent on the customer's priorities. In this way the customer is served better and less effort has to be put in managing priorities. The information analyst / project manager discusses the priorities with a person representing the customer's management and application users. The customer representative is responsible for realising the customer's priorities. People assigned to the customer can consult specialists or software suppliers if they see the need for this. In this way there is less need for account managers and service desks to manage the relation between the customer and the IT department.


Consequences for information systems

Organising IT in this way often means that it is better to have a dedicated information system for each department. If this results in separate systems sharing a lot of data, reorganising departments around data sets may be a good solution. Data sets often represent a functional area while departments also often represent a functional area. Different data sets can be placed on separate servers.

Departments still should use each others data. When a department needs data from another department, the data should be duplicated to the information system of the department that needs the data. Every piece of data should be entered once and owned by a department that is responsible for the piece of data.

ERP systems may be modeled in this way too. This makes ERP systems more flexible. Maintenance actions to serve a specific department will not cause downtime for other departments. This will make system maintenance in ERP systems more easy and less political.
System design



Introduction

The system design of the BBS consists of the following parts:
- global data flows;
- data flow reports;
- global data model;
- data model reports;
- global function model;
- function model reports;
- data design and integrity; - user interfaces; - distribution of transaction processing; - account naming; - bank card payments; - user identification; - third party packages.



Global data flows

DFD Basic Banking System Overview



Data flow reports

The following detailed data model reports give additional information about the data flows of the BBS:
- Detailed DFD Diagrams (last updated September 21, 2009);
- Datastore definitions (last updated September 21, 2009);
- Dataflow definitions (last updated September 21, 2009).



Global data model

ERD Basic Banking System Overview



Data model reports

The following detailed data model reports give additional information about the data model of the BBS:
- Detailed ERD Diagrams (last updated September 21, 2009);
- System glossary (last updated September 21, 2009);
- Domain definitions (last updated September 21, 2009);
- Entity definitions (last updated September 21, 2009);
- Entities and their attributes (last updated September 21, 2009);
- Attributes in a domain (last updated September 21, 2009).



Global function model

FHD Basic Banking System Overview



Function model reports

The following detailed data model reports give additional information about the data model of the BBS:
- Detailed FHD Diagrams (last updated September 21, 2009);
- Function Hierarchy Summary (last updated September 21, 2009);
- Function Definitions (last updated September 21, 2009);
- Function to Entity Matrix (last updated September 21, 2009);
- Function to Attribute Matrix (last updated September 21, 2009).



Data design and integrity

The data should be central in the system design because the application is data driven. The data design is the most essential element in making the system easy to build and easy to maintain. A flaw in the data model will cost the most to repair when the system is operational. All other errors are more easy to solve. Therefore the data design is the most critical element in design.

Because data is so important, it should not be possible to insert data that does not meet data integrity rules. Therefore the database management system should enforce the data integrity rules where possible using constraints and transactional triggers. In this way new functionality can be added without threatening the data integrity rules. Data should therefore be checked in the following situations:
- the application module should check the data before posting the data to the database;
- the database should check the data when accepting the data.

Only when the database check leads to an unacceptable degradation of the performance of the system, the check should be disabled.

It may be efficient to use table API packages. The table API packages may be used by application modules to check the data before posting the data to the database. Other software systems should never be able to query and manipulate data in the BBS directly via the tables. Those software systems should retrieve an manipulate data using API's. The table API packages may be generated if this is possible. Otherwise they should be built manually.



Site replication

We should never underestimate the ability of humans to mess things up completely. Even experienced personnel can make destructive errors such as deleting all data. Therefore copies of the BBS that can take over operations without data loss should run at least two other sites with different personnel. This also guards the BBS against deliberate destruction. The BBS should therefore have a replication and recovery mechanism at the application level.



User interfaces

Users that process large amounts of data often like character based terminals best because they respond directly to user input and have function keys that can be used as shortcuts. Function keys greatly improve the efficiency of experienced users. Character based terminal systems are very reliable and almost never fail because there is only one layer in the system, which is the central computer.

Graphical User Interfaces that were introduced in the client/server architecture did look better but were less efficient and showed more malfunctions. With the introduction of Java and middle tiers efficiency and reliability took another nosedive. Inefficient systems lead to higher levels of stress because of performance problems and malfunctions. Shortcuts often are replaced by mouse activity which often results a rise in job related diseases such as repetitive strain injury, because using the mouse is far more stressful than typing. Repetitive strain injury emerged in earnest with the introduction of Windows and graphical user interfaces.

Therefore the user interface should run on the same server as the data and should work like a character based terminal as much as possible. This will eliminate performance problems and malfunction because of networking errors and synchronisation issues between different layers of the application system. The user interface should also have shortcut keys and it must be easy to operate the user interface completely without using the mouse.



Distribution of transaction processing

It must be possible for banks to run the BBS at a decentralised site. Logically there must be no centralised handling of financial transactions, making bank operations independent of a location.

This does not mean that there is no centralised handling of financial transactions in data centres. Especially for small banks it may be more safe and cost effective to handle financial transactions at a centralised site. Banks have now the option facilitate their operations themselves or use the services of facilitators that host banking operations.

If a certified software package is used, it must be possible for a bank to make a change of facilitator without complications.

Transactions can be routed from one bank to another using directory services that locate a specific bank holding the account. Messages may be used to relay financial transactions from one bank to another.

Because of the use of directory services bank account names may have the following format: [account name].[bank name].[country code], for example "myaccount.mybank.us". Now people can have account names that are easy to remember.



Account names and numbers

In the Natural Money Financial System accounts have names. This makes account names easier to remember. Account holders can be free to choose account names. For international transactions account numbers are still needed because of the existence of different character sets. The account number can also be used to check the account name if needed. Account names in the Natural Money Financial System have the following structure: [accountname].[bankname].[country code].

To detect typing errors when specifying an account number, the account number must have a checksum. In this way there is only a small chance that a typing error will result in a valid account number. Such a check exists in Dutch bank account numbers. Dutch bank account numbers are divisible by 11.



Bank card payments

The bank card may hold an amount of money that can be considered as digital cash. In this way terminal payment transactions can be reduced. Digital cash should be anonymous so the digital cash can be used to pay anonymously. The bank card must be able to hold a balance in a number of currencies. Banks can make the use of this feature attractive by demanding lower account fees if this feature is used.

The use of a bank card for cash money results in holding tax calculation issues. Those issues can be solved by recording the transaction times and amounts on the bank card and transmit them to the bank when doing a terminal payment. In this way the bank can calculate the holding tax due on the digital cash.



User identification

User identification methods for banks on the internet differ widely and often involve code generators generating long code numbers that have to be typed over by the bank customer. Most methods are not user friendly and make internet banking a troublesome experience for people that do not understand computers very well. Also banks and credit card companies supply cards with PIN codes. When people have different cards, remembering all PIN codes becomes difficult for most people.

The user identification methods for banks should be simplified and standardised to achieve the following:
- identification methods are more simple and the same for all banks;
- small banks can make use of user identification methods in a cost effective way;
- the reuse of identification devices for different banking relations becomes possible.

The following types of user identification can exist:
- A bank card (cash card) with a PIN code. People must be able to choose their own PIN code so they can choose a number that is easy to remember. Otherwise people may write down PIN codes. The Dutch bank ABN AMRO already offers this option.
- A code generator for bank transactions on the internet. The same code generator should be usable for all banking relationships. The code generator may use the bank card with PIN code for additional security.
- A biometric identification (fingerprint) device for bank transactions on the internet. It must be possible to use the same biometric identification device for all banks. The biometric techology should be available for people that have difficulties using code generators.

Also stock brokers can make use the same technologies to identify users of their brokerage accounts.



Third party packages

Software suppliers offer supporting packages that are used to speed up the application development process. The following supporting packages can be used in a Oracle database and Apex environment:
- code generators;
- Workflow and BPEL;
- B2B for messaging;
- complex data types and advanced queueing.

Often supporting packages result in systems that are less easy to understand, crash more often or do not perform very well. System administrators often find it hard to cope with new options that are introduced on a regular basis. If system administrators do not understand the basic operations of the system, the organisation will become heavily dependent on the hardware and software suppliers.

Code generation may help to speed up the system development process but often creates much code that will never be used. Generated code is also more difficult to understand than programmed code.

Workflow and BPEL control the flow of operations. Those packages are quite complex while in most cases the required flow of operations can be achieved using a status field. Workflow and BPEL also generate massive amounts of status information. Also BPEL is not based on PL/SQL and requires an additional application server.

Oracle offers B2B for messaging. B2B is standards based and uses XML. Therefore B2B probably cannot be used for efficient tailor made message protocols. Also B2B is not based on PL/SQL and requires an additional application server.

Advanced queueing makes use of queue tables that contain complex data types. Complex data types are not easy to query using simple tools like SQL*Plus. System administrators that have to keep the system operational often have no knowledge of complex data types or advanced queueing.
Technical architecture



Introduction

The technical architecture of the Basic Banking System (BBS) environment is based on years of experience and observing good ideas and mistakes. In many situations there is a best way of doing things. If an organisation has good reasons to ignore suggestions made, this is good. If there is no good reason to ignore the suggestions then this may cost money.

The following general guidelines should be used for the technical architecture:
- Avoid the use of different software platforms. Ideally there should be one OS platform, one database platform and one development platform.
- Minimise the use of solutions that require expert knowledge.
- Avoid the use of solutions that consume a lot of resources, such as middle tiers and Java.
- Minimise the use of software and hardware components because each component introduces and additional downtime by maintenance and the risk of failure.

The following issues are discussed:
- application design development platform;
- database platform;
- database platform and character set;
- operating system platform;
- high availability;
- features to avoid.



Application design development platform

To make the design simple, the technical architecture should be a scalable without a middle tier. The application design should be reliable, safe and easy to use. In this way the application is cheap to maintain. The system should be made to accomodate small banks and therefore simplicity is imperative. The system can be built in Oracle Apex. In this way only one component has to be installed to run the application, which is the Oracle database.

Other products with similar features may be used but probably they are not available. In this document I assume the Oracle database and Oracle Apex will be used. The Oracle database and Oracle Apex are suitable for the following reasons:
- The Oracle database is the most reliable product of Oracle and is one of the best database systems available.
- The Oracle database is widely used, so it is easy to find people that can manage it.
- The Oracle database is scalable and available on most operating system platforms.
- The Oracle database offers a complete programming environment with APEX and PL/SQL and includes Java and XML.
- Apex requires no additional licences apart from the Oracle database.
- Apex is easy to learn and only requires knowledge of the Oracle database language PL/SQL.
- Apex requires only a database server. The database can perform all needed functions. In this way there may be only one platform for programming, execution, data management, security and web access.
- Apex is the best modern equivalent to the character based terminal that is best suited for online transaction processing. To make Apex more user friendly, function keys may be added in the future. A character based terminal emulation over the Internet may even be better.

The design of the BBS can be done using Oracle Designer. Oracle Designer is a structured data driven design and development tool. Currently Oracle Designer is to be decomissoned in favour of feature driven development tools and ERP packages. However it may be a good idea for Oracle to make Designer fit for use in combination with Oracle Apex.



Database platform and character set

If Oracle Apex is used, the database platform will automatically be Oracle. Oracle is one of the most widely used database platforms. The Oracle database is available on a wide range of operating systems and hardware platforms. The Oracle database is therefore a suitable database platform for the BBS.

If the preferred platforms will be Oracle Apex and the Oracle database, then the Natural Financial System project should have the full support of Oracle. Oracle should make enhancements to Apex or fix bugs if needed.

Small banks should be able to run a BBS cheaply. Licenses and support contracts should be standardised and negotiated. License costs and support fees must be affordable for small banks. For small banks running a BBS should not cost more than running Windows on a PC for non commercial purposes.

The system should be built in such a way that labels and table content can be shown in all possible languages. Using an UTF-8 character set, all possible languages can be represented. The database character set should therefore be AL32UTF8.



Operating system platform

To save cost and to be independent of hardware suppliers the preferred OS platform is Red Hat Linux. Small banks may run the BBS on a windows PC. Larger banks and IT facilitators can choose their own operating system to run the BBS because the Oracle database is available on a wide range of operating system platforms.

OS/400 and VMS are more reliable operating systems than Unix or Windows but they are not widely used. Therefore only a few people have knowledge of those operating systems. Also those operating systems can only be used in combination with specific hardware, which makes them unfit for being a preferred operating system platform.



High availability

Introduction

If a system needs to be available all the time and hardware failure should not interrupt operations, additional hardware should be available to take over operations. This paragraph will describe a simple solution for high availability that can be introduced with minimal technological knowledge.

It is preferable to have the redundant hardware at a second location to prevent site failure from bringing down operations. The second location should be nearby to prevent that networking delays may cause performance degradation. To achieve high availability, the following hardware should be redundant:
- servers;
- storage;
- network.


Server redundancy

It is possible to bring up the database instance on another server in case of server downtime by using a virtual node name. Every database instance may run on a virtual node that points to a specific server. If the server goes down, the virtual node can point to another server. In this way the database can be brought back online with minimal downtime on the other server. Only a DNS entry has to be changed to implement this solution.

There is a difference between virtual nodes and virtual servers. The virtual node only points to the IP address of a specific server. If the server fails only the IP address of the virtual node is changed. This solution does not require virtual server software or expert knowledge.

The following example shows how this solution works out. The instance of database TEST1 may run on virtual node TEST1 that points to server SERVER1. In the case of a failure of SERVER1, the DNS registration of virtual node TEST1 may be changed to point to SERVER2. After that the database instance can be restarted on node TEST1 which is now SERVER2.


Storage redundancy

Using mirroring storage redundancy can be achieved. It is preferable to have the mirror copy at another location to prevent site failure from bringing down operations. It is not sufficient to have only storage on different locations but the data on the storage should also be redundant and available on both locations.


Network redundancy

Most network topologies offer redundancy. Ring networks can transfer data in both directions. If a part of the ring is not working, network traffic can choose the other direction. Other types of networks may need duplicated lines that preferably are not in the same tubes.



Features to avoid

Introduction

Hardware suppliers and software suppliers offer technical features to improve scalability, availability and performance of applications. However in many situations those features are costly and in reality the use of those features often leads to lower scalability, availability and performance because the solutions are complex. Only in large data centres with skilled personnel there may be improvement of scalability, availability and performance when using those features.

Small organisations should avoid those features because the cost of operating those features is often higher than the gains that may come from them. Banks should also ask themselves why they should be open 7 days a week and 24 hours a day without interruption. Before internet banking emerged, most banks were only open during office hours. In many countries people had to take a leave to visit a bank. So why should banks invest in solutions to achieve 99% uptime of their website when 97% uptime can be achieved at 50% of the cost or even less.

Small and medium sized banks operating the BBS should avoid the use of the following features that require advanced knowledge and have little or no value in most environments:
- virtual servers;
- operating system clustering;
- middle tiers;
- network attached storage and storage area networks;
- advanced monitoring tools;
- storage management;
- database clustering;
- standby databases.


Virtual servers

Virtual servers are servers that exist on physical servers but there is no link between the virtual servers and the physical servers. A physical server can only go down because of the physical server failing. A virtual server can go down because of a physical server failing, virtual server software failing and cascading virtual server errors because all virtual servers share the same physical server pool. Therefore virtual servers cause additional downtime.


Operating system clustering

Clustering at the operating system level combines a number of servers together for a specific purpose. If one server fails, the others should take over the workload of the failed server. However in many cases a malfunction of a machine in a cluster brings down the cluster completely. Therefore in reality the cluster is more often down than a single machine.

If clustering is not used there is also no need for load balancers. Load balancers require specific knowledge and are another source of failures.


Middle tiers

Middle tiers are used to make systems scalable but they generate additional network traffic and may cause performance problems that are difficult to solve. Performance problems in application environments with middle tiers require advanced problem solving skills that are difficult to maintain for smaller organisations. Because of synchronisation issues and network traffic, middle tiers may make systems less scalable.


Network attached storage and storage area networks

NAS (Network Attached Storage) and SAN (Storage Area Network) solutions offer a pool of storage over the network that is independent of the servers using the storage. NAS and SAN require specific knowledge and their use should be avoided when they have little added value, which is often the case in small organisations. However enterprise and departemental data storage should not be attached to a specific server because it must be possible to change over to another server in case of a server failure.

NAS and SAN have added value for larger organisations because of their load balancing characteristics. Enterprise NAS and SAN solutions however should be avoided. A complete failure of an enterprise SAN or NAS will bring down the operations of a company entirely. If restoring data is the only option, it will take a lot of time to bring the system up again. It is therefore better to split up storage in a number of smaller managable storage systems. It probably is also better to cluster servers and storage around data sets which makes management of servers and storage a departemental issue requiring less organisational politics.


Advanced monitoring tools

Advanced monitoring tools like Oracle Enterprise Manager (OEM) or HP OpenView promise a higher availability by detecting problems earlier and taking corrective action where possible. However in reality advanced monitoring tools often lead to lower availability because of the following:
- Advanced monitoring tools require effort to introduce and to maintain. The operation of an advanced monitoring tool requires specific expertise that may not be available in the organisation.
- Components of the advanced monitoring tools may go down and critical situations may remain unnoticed. If an agent goes down, problems in specific components cannot be detected any more. Also system administrators may depend on email alerts for detecting problems while the email system may be down.
- Components of advanced monitoring tools may cause system downtime because of system crashes and spinning processes caused by advanced monitoring tool components.

Advanced monitoring tools may have value in complex environments but for most banks using the BBS those tools have no value. Scripts made by system administrators that monitor specific conditions causing trouble will probably be a better solution for most banks using the BBS.


Storage management

Oracle ASM (Automatic Storage Management) is a storage management feature for Oracle database that is in fact a file system for Oracle files. ASM offers the same functionality as a file system combined with an intelligent SAN or NAS system. Therefore ASM is an additional software layer that introduces extra downtime and failure. Also with ASM the database files are only accessible using RMAN, which makes them more difficult to manage because operating system commands cannot be used to manage files.


Database clustering

Database clustering also known as Oracle RAC (Real Application Clusters) or grid computing promises database scalability and high availability. However in reality this is often not the case. Scalability of RAC is often limited and places strict requirements on application design.

When data is changed by one RAC node and requested by another RAC node, this data must be transmitted over the cluster interconnect which is the network that binds the RAC cluster together. This may cause performance degradation when there is high volume data processing.

It is often cheaper to buy a more powerful server. Also in reality RAC systems have more downtime than single node systems. Just like it the case with clusters, a failure of one RAC node may bring down the entire RAC system. RAC systems often depend on operating system clustering and the cluster interconnect which causes additional failure and downtime.


Standby databases

Oracle Data Guard maintains standby databases that are copies of a primary source database. In case of failure of the primary database, one of the standby databases can take over the role of the primary database. If Oracle Data Guard is used, patching and upgrading databases may cause trouble. A version upgrade of the source database requires a version upgrade of all standby databases, which requires down time of the system. In theory a logical standby solution should require no downtime. However if the logical high availability solution poses serious problems such as complexity, band width consumption or performance degradation then the use of Oracle Data Guard should be considered.



Server and database setup

Introduction

The server and database setup should be standardised. In this way it is possible to set up and maintain servers and databases in large numbers. In it may also be possible to make an installation script that installs the servers and the databases automatically.

The following issues are discussed:
- server names;
- user names;
- global file system setup;
- Oracle software volume file system setup;
- Oracle data volume file system setup;
- Oracle backup volume file system setup;
- Oracle logging volume file system setup;
- database parameters.


Server names

There are no specific requirements for server names, however servers often have a names consisting of:
- a set of characters referring to the name of the organisation;
- a number which is the sequence number of the server;
- sometimes a character referring to the environment in which the server is located.

For example: the Miami Bank may choose for the company code MBK to refer to in all hardware and software component names that require company specific naming. Therefore Miami Bank may have server mbk0037d which is development server 37 of Miami Bank or mbk0001p which is production server 1 of Miami Bank.


Domain names

The bank domain name should be something like [bank name].[country code]. Ideally the bank domain name should match the internet domain name of the bank. This may not be possible in some countries such as the United Kingdom where company names should have an additional code identifying the domain name as a company domain name. For the BBS this is no problem.

If the bank has it own IT organisation and is developing, testing and bugfixing software, there may be additional sub domains prd for production, dev for development, tst for test, acc for acceptance test and bfx for bugfix.

For example:
- Miami Bank has domain name miambank.us and may have sub domains dev.miambank.us for development, tst.miambank.us for test, acc.miambank.us for acceptance test and bfx.miambank.us for bugfix.
- Server domain names may be mbk0001p.prd.miambank.us for production server 1 of Miami Bank and mbk0037d.dev.miambank.us for development server 37 of Miami Bank.
- Database domain names may be MBK02P.prd.miambank.us for production database MBK02P of Miami Bank and MBK11D.dev.miambank.us for development database MBK11D of Miami Bank.


User names

Apart from the system users already available on the system, a Unix system should have at least have one Oracle user. Typically the user is named oracle and has group dba. In simple environments database administrators login as user oracle directly. In more complex environments database administrators have their own user accounts and switch to the oracle user using a user switch command such as su or sudo.

It is possible to have more than one oracle user. This can be done when more than one environment is running on the same machine. Oracle users may then be named: devoracle, tstoracle, accoracle, prdoracle and bfxoracle. Also it is possible to separate application server environments and database environments. In that case Oracle users have names like dbdevora, asdevora, dbtstora, aststora, dbaccora, asaccora, dbprdora and asprdora. In most cases this is the best solution.


Global file system setup

A file transparent file system has a functional division of volumes, each of them having different backup characteristics. A server file system basically has the following types of volumes:
- software: the location of the software that runs on the server;
- data: the location of the files that contain the data set;
- backup: the location of the files that contain the backup set. The back up set must be sufficient to restore the data set completely at any time;
- logging: the files that show information of the progress of the operations.

If there is a possibility that required volume sizes may exceed maximum volume sizes, or the files of a specific type will be distributed across multiple volumes, or the operating system requires specific volume names then the following global file system setup may be chosen: /[vvv]/software, /[vvv]/data, /[vvv]/backup and /[vvv]/logging where [vvv] is the volume name. For example:
/u01/software, /u01/data, /u02/backup, /u02/logging or C:\software, D:\data, E:\backup, E:\logging.

If /data and /backup are two different volumes on different logical units (luns) owned by root (system administrator), it is not possible for the system administrator or the database administrator to accidentally remove the data and the backup simultaneously. In this way it is nearly always possible to recover completely from a situation in which a system administrator or database administrator accidentally removes files. If the /data and the /backup volume reside on different physical disks on different locations, this will result in additional data protection from site destruction in cases where there is no mirroring of data across different sites.

The volumes /software, /data, /backup and /logging have different subdirectories for each software supplier. Therefore the division of the volumes in sub directories may be:
- /software/oracle: the location of the Oracle software;
- /software/futuresoft: the location of the Futuresoft software;
- /data/oracle: the location of the Oracle data;
- /data/futuresoft: the location of the Futuresoft data;
- /backup/oracle: the location of the Oracle backups;
- /backup/futuresoft: the location of the Futuresoft backups;
- /logging/oracle: the location of the Oracle logging;
- /logging/futuresoft: the location of the Futuresoft logging.


Operating system modifications

Operating systems such as Unix and Windows give system administrators many opportunities to mess things up. Using the rm command, the root user of a Unix system can delete any file anywhere on the system. Using the rm -rf * command from the root directory / will delete all files from the system. Humans do not always pay attention and make errors so this is not a desirable feature of the operating system.

The rm command should be modified using a superseding script in such a way that it is not possible for the user root to remove Oracle database files. Also it should not be possible for the database administrator to accidentally remove database files. Therefore the database adminstrator must not be allowed to rm -rf * on /data/oracle, /backup/oracle, /software/oracle or /logging/oracle. Other commands that may be dangerous should also be modified in this way.


Oracle software volume file system setup

The setup of the Oracle software directory can best be done as follows:
/software/oracle/Ora[Ss][rrr][nn] where
- [Ss] is the type of software installed, For example:
db is database, as is application server;
- [rrr] is the version of the software, for example 10g, 11g;
- [nn] is the sequence number of the Oracle Home, which is the base directory for a specific Oracle software installation.

Oracle home names should not refer to the application systems running in the Oracle homes. This creates maximum flexibility in adding and removing application systems in an Oracle home. This solution requires that there is only one operating system user running all installations of a specific product.

For example, the following Oracle homes may exist:
- OraDb10g01 on /software/oracle/db10g01: location of Oracle 10g database home 01, running version 10.2.0.4.0, CPU Apr 2009;
- OraDb10g02 on /software/oracle/db10g02: location of Oracle 10g database home 02, running version 10.2.0.4.0, CPU Jul 2009;
- OraDb10g03 on /software/oracle/db10g03: location of Oracle 10g database home 03, running version 10.2.0.5.0;
- OraDb11g01 on /software/oracle/db11g01: location of Oracle 11g database home 01, running version 11.1.0.6.0, CPU Apr 2009;
- OraDb11g02 on /software/oracle/db11g02: location of Oracle 11g database home 02, running version 11.1.0.6.0, CPU Jul 2009;
- OraAs11g01 on /software/oracle/as11g01: location of Oracle 11g application server home 01, running version 11.1.1.1.0;
- OraAs11g02 on /software/oracle/as11g02: location of Oracle 11g application server home 02, running version 11.1.1.1.0, CPU Jul 2009.

In this example the Oracle home name OraDb10g01 in directory /software/oracle/db10g01 is the Oracle home of all databases running at database version 10.2.0.4.0, CPU Apr 2009. Using this method, databases can be migrated individually to a higer patch level, while the number of Oracle homes is minimised. Each Oracle home can be used to run more than one database.

If a specific set of patches is requested, a new Oracle home may be created with the patches added. Databases may then be moved to the new Oracle home individually.


Oracle data volume file system setup

Assuming Oracle Managed Files are used, all subdirectories of /data/oracle are created automatically assuming that the directory /data/oracle is writable for the Oracle user.

The setup of the Oracle data directory can best be done as follows:
- /data/oracle/[DBNAME]/controlfile: the location for the database controlfiles;
- /data/oracle/[DBNAME]/datafile: the location for the database data files;
- /data/oracle/[DBNAME]/onlinelog: the location for the database recovery logfiles;
- /data/oracle/[DBNAME]/pfile: the location for the database parameter file.

[DBNAME] is the name of the database which is [CCC][nn][E] where
- [CCC] is the three letter code for the company;
- [nn] is the sequence number of the database;
- [E] is the environment, which can be D (Development), T (Systems test), A (Acceptance test), P (Production), B (Bugfix).

Database names should not refer to the application systems using the databases. This creates maximum flexibility in adding and removing application systems in a database.

For example:
- /data/oracle/MBK02P/controlfile
- /data/oracle/MBK02P/datafile
- /data/oracle/MBK02P/onlinelog
- /data/oracle/MBK02P/pfile
- /data/oracle/MBK11D/controlfile
- /data/oracle/MBK11D/datafile
- /data/oracle/MBK11D/onlinelog
- /data/oracle/MBK11D/pfile

In this example the /data/oracle/MBK02P/controlfile directory contains the control files of the MBK02P database, which is production (P) database 2 of Miami Bank (MBK).


Oracle backup volume file system setup

Assuming Oracle Managed Files are used, all sub directories of /backup/oracle except the /backup/oracle/[DBNAME]/archivelog directory are created automatically assuming that the directory /data/oracle is writable for the Oracle user.

The setup of the Oracle data directory can best be done as follows:
- /backup/oracle/[DBNAME]/archivelog: the location for the archived database recovery logfiles;
- /backup/oracle/[DBNAME]/backupset: the location for the Recovery Manager (RMAN) backup sets;
- /backup/oracle/[DBNAME]/controlfile: the second location for the database controlfiles;
- /backup/oracle/[DBNAME]/onlinelog: the second location for the database recovery logfiles.

For example:
- /backup/oracle/MBK02P/archivelog
- /backup/oracle/MBK02P/backupset
- /backup/oracle/MBK02P/controlfile
- /backup/oracle/MBK02P/onlinelog
- /backup/oracle/MBK11D/archivelog
- /backup/oracle/MBK11D/backupset
- /backup/oracle/MBK11D/controlfile
- /backup/oracle/MBK11D/onlinelog

In this example the /backup/oracle/MBK02P/archivelog directory contains the archived database recovery logfiles of the MBK02P database, which is production (P) database 2 of Miami Bank (MBK).

The backup procedure may be as follows:
- create a complete backup set containing all data (level 0) on a low activity day, such as a Sunday;
- create a partial backup set containing all changes (level 1) since the last complete backup (level 0) on all other days;
- the backup files that reside on the backup volume, can then be backed up to an offline location such as a tape device.


Oracle logging volume file system setup

The setup of the Oracle logging directory can best be done as follows:
- /logging/oracle/[DBNAME]/adump: the location for the database audit logfiles;
- /logging/oracle/[DBNAME]/bdump: the location for the database background process logfiles;
- /logging/oracle/[DBNAME]/cdump: the location for the database core process logfiles;
- /logging/oracle/[DBNAME]/udump: the location for the database user process logfiles;
- /logging/oracle/[LSNAME]/log: the location for the listener logfiles;
- /logging/oracle/[LSNAME]/trc: the location for the listener trace files.

[LSNAME] is the name of the database listener. Listener names do not have to meet specific requirements to make them more managable. They may have the format [NODENAME]_LISTENER[nn] where
- [NODENAME] is the name of the node running the listener;
- [nn] is the sequence number of the listener within the node.

For example:
- /logging/oracle/MBK11D/adump
- /logging/oracle/MBK11D/bdump
- /logging/oracle/MBK11D/cdump
- /logging/oracle/MBK11D/udump
- /logging/oracle/MBKD0037_LISTENER01/log
- /logging/oracle/MBKD0037_LISTENER01/trc


Database parameters

Assuming the database is created using the DBCA having Oracle managed files, the user defined database parameters should be:
- audit_file_dest = /logging/oracle/[DBNAME]/adump
- background_dump_dest = /logging/oracle/[DBNAME]/bdump
- core_dump_dest = /logging/oracle/[DBNAME]/cdump
- db_create_file_dest = /data/oracle
- db_create_online_log_dest_1 = /backup/oracle
- db_create_online_log_dest_2 = /data/oracle
- db_domain = [environment].[bank name].[country code]
- log_archive_dest_1=LOCATION = /backup/oracle/[DBNAME]/archivelog
- log_archive_format = [dbname]%t_%s_r%.arc
- user_dump_dest = /logging/oracle/[DBNAME]/udump

For example:
- audit_file_dest = /logging/oracle/MBK02P/adump
- background_dump_dest = /logging/oracle/MBK02P/bdump
- core_dump_dest = /logging/oracle/MBK02P/cdump
- db_create_file_dest = /data/oracle
- db_create_online_log_dest_1 = /backup/oracle
- db_create_online_log_dest_2 = /data/oracle
- db_domain = prd.miambank.us
- log_archive_dest_1=LOCATION = /backup/oracle/MBK02P/archivelog
- log_archive_format = mbk02p%t_%s_r%.arc
- user_dump_dest = /logging/oracle/MBK02P/udump
[Index]
[leave a message]