| Design for the Basic Banking System |
July 24, 2009 - January 25, 2011
Author: Bart klein Ikink
| Introduction |
Local currencies have proliferated in recent years [+] and there are a number of transaction systems to support those currencies. Linkages between various types of local currencies and co-operative finance organisations are developing. In the United States, Alternatives Credit Union and Ithaca Money have formed a joint board. In southern Germany the Chiemgauer, which is a currency with a holding tax, is being supported by local social and co-operative banks. In Great Britain local shadow currencies in Brixton, London and Bristol are developing credit union links. Mobile phones can be used to make payments in Brixton Pounds.
The current usury financial system may collapse as many banks are insolvent. Existing transactional systems will not be able to support a replacement of the current financial system by local currencies with a holding tax. If this happens then there may be need for a banking system that can support a million banks and a million currencies in a worldwide network. The Basic Banking System (BBS) is meant to provide support for a new financial system based on the principles of Natural Money. It will be able to provide all critical banking operations, such as current accounts, savings, loans and mortgages. The Basic Banking System will be able to facilitate transactions in a million currencies between a million banks worldwide and can therefore support a new financial system based on local, regional, national and international Natural Money currencies.
The Basic Banking System is a simple system in the functional as well in the technical sense. It covers all basic operations of banking but not more. The Basic Banking System is built on one component, which is the Oracle database. The Oracle database is the most widely used database and because no other components are used to support the system, it will be easy to maintain, requiring a minimum of technical staff. To make the system easy to operate, advanced technical features are not used. The Basic Banking system will become available as open source public domain software. This will make it possible to find bugs an other issues faster, so the system will become mature in a shorter timeframe.
The Basic Banking System supports only Natural Money, which means that:
- for money in the current account, a holding tax is levied by the government issuing the currency;
- when currencies are exchanged, an exchange tax is levied by the government issuing the currency;
- on savings a compensation is witheld for the costs of the bank to make loans;
- on loans no interest an other costs are charged.
The design of the BBS is shown as it was on October 5, 2009. As of December 26, 2011 the design has been refined further while around 90% of the BBS has been built and tested. The expected release of the first operational version of the BBS will be on April 9, 2012. A demonstration version is available on the web. |
The initial version is fully functional, but it has bugs and the user interface is minimal. Furthermore there are security issues that have to be solved. That the Basic Banking System could become the cornerstone of a future financial system based on Natural Money, so design issues had to be dealt with first in order to make the system ready to operate in a short timeframe. If the underlying structure is good, security issues can be resolved and a better user interface can be built in a short timeframe.
| Design considerations |
Scope
Small banks should not have to consider organisational design and systems design. Those issues should be straightforward and standard solutions should be provided. Therefore the basic operations of a bank in the BBS should be standardised. This makes it possible to operate a small bank in a cost effective way. Simple solutions are often cheaper and more robust. Limiting the scope of a system will make a system more manageable. The BBS will become a basic system with minimal functionality that only performs the essential functions of banking.
Supportive functions that are not part of the basic banking operations are spinned off in separate packages. The following separate packages will be available with the BBS:
- currency exchange;
- messaging;
- address checking;
- system administration;
- user administration.
The BBS will not cover all organisational operations within a bank, such sales, procurement, project management and human resource management. Banks in the Natural Money Financial System probably will often be small and may be run by volunteers. The BBS should therefore be a basic system that only covers only banking operations.
Design and development method
Introduction
Structured software design and development methods, as opposed to gradual development or prototyping, have a higher probability of resulting in good designs. A good outcome requires software designers that have knowledge of the business and the used technology. Ideally the software designer should also build the system. This will minimise the potential for communication errors.
Ideally software design, development and maintenance consists 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;
- choice of platforms;
- organising information technology;
- development standards and procedures;
- building and testing the system;
- implementing and maintaining the system.
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 can choose additional software packages that best fit the business requirements of the supportive functions. When standard software packages are used 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.
The BBS covers the primary part of the Natural Money banking operations. For the banks in the Natural Money Financial System the BBS will be a software package as those banks will often be small in size. In the Natural Financial System there is no competition of everybody against everybody, and therefore creating a unique business model is not needed.
Modeling the business processes, the data and the functions
When building a tailor made system or implementing a software package the business processes should be identified and modeled. When modeling business processes, the needed business functions and data to support the business processes emerge. If a tailor made system will be built then the data should be modeled into a data model and the functions should be modeled into a function model.
The data model is the most important part of the design because of the following reasons:
- Good data models explain the system, which reduces the need for additional documentation;
- Good data models result in significantly lower software development costs because better data models reduce the need for programming;
- Good data models result in significantly lower system maintenance costs because systems with good data models can be less complex;
- Changing the data model once the system is operational often involves extensive programming and a data conversion. Changing functions is often less expensive and this rarely requires a data conversion.
Choice of platforms
The platforms are chosen for their install base and simplicity. Administrators must be hired to operate the systems so only widely used platforms can be used. The quality of available administrators may vary and therefore operating the BBS should require as little knowledge as possible.
The following platforms are chosen:
- (default) operating system platform: Red Hat Linux;
- database platform: Oracle database;
- development platform: Oracle Apex and PL/SQL.
Development standards and procedures
The BBS is built using programming standards. This means that software components with a similar function look similar. The following development standards are used in the BBS:
- The programming is done in PL/SQL. Apex programming is minimal to provide a user interface. In this way it is possible to replace the user interface more easily.
- All rules data should adhere to must be checked in the database. In this way it will not be possible to build programs that bypass the data checks.
- Checks and supporting programs are organised around the entities of the datamodel and consequently around the tables in the database.
Implementing and maintaining the system
Using a standard configuration of hardware and software, it must be possible to implement the BBS in large numbers. Consequently the software, such as the operating system, the database and the BBS itself must be copied from preconfigured templates. It must be possible to do maintenance operations on the BBS from a remote site. Therefore the BBS must be able to report critical conditions to system administrators at a remote site. The BBS should therefore have a system adminstration packages that can monitor critical conditions. Monitoring packages like Oracle Enterprise Manager or HP OpenView are to cumbersome in a simple configuration as they require new configurations that may be additional sources of failure.
High level design
Roles
Within the BBS the following basic roles can be identified:
- account holders: people that have accounts in the bank;
- data entry: enters data on behalf of the account holders;
- account and account holder data review: reviews submitted account and account holder data;
- account and account holder data approval: approves submitted account and account holder data;
- transaction data review: reviews transaction requests;
- transaction data approval: approves transaction requests;
- application administration: administrates the application and represents the end users of the system and manages data, parameters and users;
- system administration: administrates the technical infrastructure, which includes database, network, hardware and software;
- auditing: verifies the procedures and the operations of the bank.
Functions
Withing the BBS the following main functions can be identified:
- administrating master data, such as banks, countries and currencies;
- administrating account holder data, such as personal data, addresses and nationalities;
- administrating account data, such as account number and name and account currencies;
- administrating legal documents, such as contracts and passports;
- viewing transaction data, such as transactions, payment orders and bank statements;
- adding transaction data, such as payment orders and collect permissions;
- verifying account and account holder data using manual checks by bank employees;
- verifying transaction data, using tresholds and manual checks by bank employees;
- computing holding taxes and intermediary compensations;
- bookkeeping and accounting, with bookkeeping reports and legally required reports.
Withing the BBS the following main functions can be identified:
- exchanging messages with services and other banks;
- exchanging currencies;
- validating addresses;
- system administration.
| System design |
Introduction
The system design of the BBS consists of the following parts:
- global business model;
- global data flows;
- data flow reports;
- global data model;
- data model reports;
- global function model;
- function model reports;
- data design and integrity;
- security;
- user interfaces;
- distribution of transaction processing;
- account naming;
- bank card payments;
- user identification;
- third party packages.
Global business model
- System glossary (as of January 4, 2012);
- Business unit definitions (as of January 4, 2012);
Global data flows
As of September 21, 2009.
Data flow reports
The following detailed data model reports give additional information about the data flows of the BBS:
- Detailed DFD Diagrams (as of September 21, 2009);
- Datastore definitions (as of September 21, 2009);
- Dataflow definitions (as of September 21, 2009).
Global data model
As of January 4, 2012.
Data model reports
The following detailed data model reports give additional information about the data model of the BBS:
- Detailed ERD Diagrams (as of September 21, 2009);
- Domain definitions (as of January 4, 2012);
- Entity definitions (as of January 4, 2012);
- Entities and their attributes (as of January 4, 2012);
- Attributes in a domain (as of January 4, 2012).
Global function model
As of September 21, 2009.
Function model reports
The following detailed data model reports give additional information about the data model of the BBS:
- Detailed FHD Diagrams (as of January 4, 2012);
- Function Hierarchy Summary (as of January 4, 2012);
- Function Definitions (as of January 4, 2012);
- Function to Entity Matrix (as of January 4, 2012);
- Function to Attribute Matrix (as of January 4, 2012).
Data design and integrity
The structure of data should be central in the system design because the BBS is a data driven system. The data design is the most important part of the design because a good data design will make the system easy to build and maintain. A flaw in the data model will cost the most to repair, especially when the system is operational. Other errors are more easy to solve.
Data is an important asset so data that does not meet data integrity rules should not be inserted. In recent years philosophers with a low regard for data integrity have infiltrated software corporations and propagated the placement of business logic in tiers. In this way functions can bypass constraints by skipping the libraries that enforce them. Only the database management system can enforce data integrity rules. Data should 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 may be disabled.
Table API packages can 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. XML messaging is the preferred method for exchanging data between applications. The table API packages should be built manually because generated code often has many unneeded features and is difficult to read.
Security
Security threats come from both inside and outside the Natural Money banks. The most important asset to be secured is the data. If the critical business data can be recovered then it is possible to continue the operations, even when computers, software and buildings have been lost. Therefore security measures should be directed at protecting critical business data from loss, theft or destruction.
There are measures that should be taken by any company to secure its information systems. It is beyond the scope of this document to make an in-depth security analysis because there are many people and companies that can make them. If the BBS is to be implemented, an in-depth security analysis should be made and security policies should be implemented.
Apart from the usual security measures some additional issues should be addressed. To protect against destruction of data, there should be a backup of the BBS on at least one other site with different personnel. This also guards the BBS against deliberate destruction and fraud by IT personnel.
The Basic Banking System may also face attacks from hackers, most likely by SQL-injection. As the Basic Banking System is public domain software, hackers may find vulnerabilities in the software and the default configuration. It may be a good idea to test the system on a limited scale and organise hackers to find security issues. People that oppose the Natural Financial System may try to disrupt it. Probably there is not much that can be done about it, except from warning them for the consequences. They may not get away with it.
Distribution of transaction processing
It must be possible for banks to run the BBS at a local 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.
Account names and numbers
In the Natural Money Financial System accounts have names. Account names are 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: [bankcode].[accountname].
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. The combination of account number and account name can provide an additional safeguard agains typing errors.
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 it 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.
To make sure that no unauthorised transactions are made an email may be sent to the account holder with a link to confirm the payment orders.
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;
- message brokers;
- 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 well. System administrators often find it hard to cope with new options. If system administrators do not understand the operations of the system then 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. In most cases the required flow of operations can be achieved using a status field, which is more easy to understand. Workflow and BPEL also generate large amounts of status information. BPEL requires an additional application server. The use of workflow and BPEL makes application development unneccesarily complex and BPEL creates a coordinating layer that will become a single point of failure.
An application system can be modeled as a group of function that process data and bring it from a certain status that is part of a set of begin statuses into another status that is part of a set of end statuses. In some cases the functions may need data from other application systems that can be called as services. If every system is capable of handling all possible error situations then there is no need for a central coordinating system. Even though this may require more programming it will create systems that are more manageable as there are no dependencies between them. Every system is able to continue even when all other systems are out of order. Only operations that need a system that is not available must then be postponed.
There are a number of message brokers available. Oracle offers B2B for message processing on a node. Those solutions have options and probably are less efficient than a tailor made packages. Furthermore those packages are not based on PL/SQL and require an additional platforms or servers. Another option is the use of an Enterprise Service Bus (ESB) for coordinating messaging between nodes. A centralised messaging coordintor like an ESB will become a single point of failure. Therefore it is better that application system is responsible for relaying the messages and handling possible errors and exceptional situations.
The use of complex data types and queues should be avoided because complex data types and queues should be handled with care and require specialist knowledge. Bad handling of complex data types and queues often results in performance problems and system crashes. Complex data types and queues are not needed in most cases as the same can be achieved using traditional programming techniques.
| 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. For the BBS it is important that small organisations should be able to run it with a minimum of expert knowledge. 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 and development platforms
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.
The programming in Apex should be minimal and only provide a user interface. The rest of the programming should be done in PL/SQL. In this way it is possible to replace the user interface quickly when a better user interface comes available or when a specific bank prefers another user interface.
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 because the tool is Windows based and has become complex. It may be a good idea to build a similar tool and make it 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. Some banks may choose to 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;
- database.
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.
Database redundancy
The backup of the Oracle database should be on another site maintained by different personnel. In this way it is always possible to restore a database from a disaster situation. The backup consists of the following:
- the RMAN backup sets;
- the archive log files;
- a copy of the control file;
- a copy of the redo log files;
- a copy of the server parameter file.
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 to maintain and often the use of those features leads to lower scalability, availability and performance. Additional technology makes an architecture complex and every additional component introduces more potential for failure (see also: Naturalmoney.org - Murphy's law). Only in large data centres with skilled personnel using those features can improveme scalability, availability and performance.
Small organisations should avoid them where possible because the cost of operating those features is often higher than the gains that may come from them. Small 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.
Data security is however an important issue because data loss is unacceptable. A bank can recover from the loss of its buildings and its computers but it cannot survive data loss. By keeping things simple the same level of data security can be achieved at less cost. Data security may result in less uptime of the website. For small local banks this will not be a problem. If it is a problem then the bank should run its systems at a data centre with experienced personnel.
Small and medium sized banks operating the BBS can better avoid the use of the following features that require advanced knowledge and most likely have little or no value for them:
- virtual servers;
- operating system clustering;
- middle tiers;
- advanced monitoring tools;
- storage management;
- database clustering;
- standby databases;
- single sign on and central user directories.
Virtual servers
Virtual servers are servers that exist on physical servers but there is no link between the virtual servers and the physical servers. Under normal circumstances the failure of a physical server will have no consequence for the virtual servers because other physical servers will take over the tasks of the failed server.
A virtual server can fail because of the virtual server software failing. This can result in cascading virtual server errors where virtual servers share the same physical server pool. The use of virtual servers requires additional expertise that may not be available at a small bank. Therefore virtual servers are difficult to handle for small banks.
Operating system clustering
Clustering at the operating system level combines a number of servers together for a specific purpose. Under normal circumstances when one server fails the remaining servers take over the workload of the failed server. Sometimes the malfunction of a machine in a cluster can make the cluster itself malfunction.
Clustering requires specific expertise that may not be available at a small bank. If clustering is not used then there is also no need for load balancers. Load balancers also require specific knowledge and can be a source of failures.
Middle tiers
Middle tiers are used to make systems scalable but they generate additional network traffic and causes performance degradation. Performance problems in application environments with middle tiers require advanced problem solving skills. Because of synchronisation issues and network traffic, middle tiers may make systems less scalable.
In recent years the capacity of database servers has increased to the point that it is now possible to load most databases entirely into memory. This will make a single database and application server more efficient than systems with distributed processing and middle tiers.
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 will then 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. This does not mean that system monitoring is not important but in general only a few conditions are critical. Scripts made by system administrators that monitor those conditions will probably be a better solution for most banks using the BBS. It probably is a good idea to incorporate essential monitoring functions in the software platform of the BBS. This eleminates the need for an additional monitoring architecture.
Automatic Storage management
Oracle ASM (Automatic Storage Management) is a storage management feature for Oracle files that is in fact a file system. ASM offers the same functionality as a file system combined with an intelligent SAN or NAS system.
ASM can be used when you can avoid using another cluster file system. If no clustering is used and there is a storage system with load balancing then there is no need for the use of Oracle ASM.
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. 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. This may cause additional failure and downtime.
Single sign on and central user directories
The use of single sign on and central user directories, such as the Oracle Internet Directory, may cause additional downtime. Downtime in the single sign on system or the central user directory will make all connected application systems unaccessible. There is little benefit in using single sign on and central user directories when most users only use one or two application systems. Therefore most banks using the BBS have no need for single sign on and central user directories.
Standby databases
Oracle Data Guard maintains standby databases that are copies of a primary source database. In case of a failure of the primary database, one of the standby databases can take over the role of the primary database. The same can be achieved using an Oracle Fast Recovery Area. An Oracle Fast Recovery Area is a less complex solution than Oracle Data Guard.
Because data security is the most important issue it may not be necessary to bring up a database copy in a short timeframe in case of failure of the primary database. Only a complete restorable backup of the data is imperative.
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; - Oracle database setup.
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.
If there is a separate production environment then a user named oracle is sufficient.
Global file system setup
A file transparent file system has a functional division of volumes, each of them having different data security 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.
It is preferable to have the backup volume on another physical location administrated by different personnel. This can be another Natural Money Bank nearby or another division within the same IT facilitator. On the backup volume a Fast Recovery Area should be installed consisting of an up to date back up including a copy of the most recent control file, server parameter file and redo log files. If the remote location is not accessible, no further transaction processing is possible.
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.
It may be a good idae to modify rm 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 could 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
When ASM is not used then the Oracle data volume file system should be set up. When using Oracle Managed Files then all subdirectories of /data/oracle are created automatically.
The setup of the Oracle data directory will then be like this:
- /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 online redo 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
If a Fast Recovery area is used the the database parameter db_recovery_file_dest must be set to a location on /backup/oracle. There should be a copy of the control file and the online redo logfiles.
If a Fast Recovery area is not used then the following setup will make it possible to recover the database from failure:
- /backup/oracle/[DBNAME]/archivelog: the location for the archived redo 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 online redo 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 regularly to an offline location such as a tape device;
- Old backup copies can be removed after becoming obsolete.
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
Oracle database setup
The database character set should be AL32UTF8, as it contains all character sets that are in use worldwide.
The following database parameter values should be set:
- nls_length_semantics = 'CHAR'