| Basic Banking System |
July 24, 2009 - December 17, 2012
Author: Bart klein Ikink
| Introduction |
Background
Local currencies have proliferated in recent years and a number of transaction systems 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.
One of the most used systems is Cyclos. Cyclos offers a complete on-line banking system with additional modules such as e-commerce and communication tools. Cyclos is currently available in ten languages and is used worldwide by many organisations and communities. The Cyclos platform permits a de-centralisation of banking services that can stimulate local trade and development. With the latest version it is possible to roll out mobile banking services using mobile channels such as SMS. Cyclos is published under the GPL (open source) license meaning that it can be downloaded for free and used at no cost.
Most existing transactional systems are not able to support a replacement of the current usury financial system by local currencies with a holding tax. There probably will 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 support a financial system based on local, regional, national and international Natural Money currencies.
Cyclos has been around for many years and it is installed on a large number of locations. It may be possible to adapt Cyclos or another system so it can replace the usury financial system. The BBS is new and is not complete yet, but it has been built to support a large multi currency banking network on a professional basis with strict security and data safety requirements. The BBS is intended for banking under the regime of Natural Money. The features of Cyclos can also be built into the BBS. If Natural Money is to be implemented then it may be needed to decide which system is the best choice for replacing the usury financial system. As the underlying structure and not features will ultimately determine the future possibilities of a system, the best choice may well be the BBS.
System scope
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.
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 it 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 will mostly be small and sometimes those banks will be run by volunteers. The BBS should therefore be a basic system that covers only banking operations. The BBS will also not be a platform for local trade.
Additional information
More information and materials can be found here:
- [Download Basic Banking System software];
- [Basic Banking System installation guide].
- [Customer Banking System user guide].
- [Employee Banking System user guide].
| Functional design |
Introduction
The Basic Banking System supports Natural Money, which means that:
- For money in the current account a holding tax is levied by the government or organisation issuing the currency.
- When currencies are exchanged an exchange tax can be levied by the government or organisation issuing the currency.
- On savings a compensation is withheld for the costs of the bank to make loans.
- On loans no interest or other costs are charged.
Natural Money assumes the existence of commercial banks because markets allocate capital better than bureaucrats. This means that there must be a profit margin for the banks. Because there is no interest on money, savers need to pay a fee to the bank so banks can lend out money at zero percent interest. This proposition is attractive to savers as there is no money creation, so economic growth will translate into lower prices and therefore there will be a real net return with zero percent interest and low negative rates.
The math is as follows: currently interest rates are around 3%, economic growth is 2% while money supply increases with 8%. The real return is a negative 3% (3+2-8). With Natural Money interest rates on deposits may be around -2%, economic growth could be 3%, while money supply increases with 0%. The real return is then a positive 1% (-2+3). Savers may have 4% higher returns in the Natural Economy. It is likely that the improved circulation of money will bring higher growth rates. If this is true then returns on savings with Natural Money are even more attractive.
Roles
Within the BBS the following roles can be identified:
- account holder: someone who has accounts in the bank;
- account holder clerk: enters account holder data;
- account holder reviewer: reviews submitted account holder data;
- account holder approver: approves submitted account holder data;
- account clerk: enters account data;
- account reviewer: reviews submitted account data;
- account approver: approves submitted account data;
- transaction clerk: enters transaction data;
- transaction reviewer: reviews transaction requests;
- transaction approver: approves transaction requests;
- application administration: administrates the application on behalf of the end users;
- system administration: administrates the technical infrastructure;
- auditing: verifies the procedures and the operations of the bank.
Functional areas
Within the BBS the following main functional areas 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 a manual check by bank employees;
- verifying transaction data, using thresholds and a manual check by bank employees;
- computing holding taxes and intermediary compensations;
- bookkeeping and accounting, with bookkeeping reports and legally required reports.
Related to the BBS the following main functions can be identified:
- exchanging messages with services and other banks;
- exchanging currencies;
- validating addresses;
- system administration and monitoring.
Not all functions mentioned in the functional design have been implemented yet.
Administrating account holders
Intoduction
The following processes can be identified:
- submit account holder;
- review account holder;
- approve account holder;
- initial login;
- end account holder.
Submit account holder
When a new customer arrives at the bank and wants to become an account holder, he or she must show an identification, which can be passport or an ID-card. A bank employee with the role account holder clerk will enter the personal data of the customer, nationality, representatives such as legal representatives, guardians or agents and address data.
If the new account holder is a human and not a corporation, he or she will get a login account for the BBS. The prospective account holder signs an account holder start document, which then will be stored in the BBS. If all data is supplied, the account holder clerk will submit the account holder data for processing.
Review account holder
If account holder reviewing is enabled then a bank employee with the role account holder reviewer will have to verify the personal data of the customer. He or she will check the identification, the supplied social security number, the representative relationship documents that state the representation and the address data.
If the account holder reviewer sees errors in the data, he or she will send the data back to the account holder clerk for correction. If the account holder reviewer sees problems with the application, such as a criminal record of the applicant, he or she will advise to disapprove the account holder application. If there are no problems, then the account holder reviewer will advise to approve the application.
Approve account holder
If account holder approval is enabled then a bank employee with the role account holder approver will approve or disapprove the account holder application. He or she approves the verified components of the account holder application, which are the documents claiming the identity of the applicant and the representation by the representatives.
As soon as the application has been approved, and the account holder is eglible to use the BBS, then a login account and a password are supplied. The account holder will get a letter at the supplied postal address with his or her username, requiring him or her to go to the bank office to get a letter with the password. At the bank office the account holder has to identify himself or herself. The bank employee will then activate the user account. After that he or she gets the letter with the password.
Initial login
Now the account holder can logon to the system. After the account holder has logged on to the system, verification emails will be sent to the supplied email addresses. The account holder will then have to open the emails and click on the verification links to verify the email addresses. After the email addresses have been verified, they can be used for bank communication, such as alerts and bank statements.
End account holder
To end a relationship of an account holder with the bank, the account holder must have no open account holdings, which means that he or she is an account holder of an account in the bank. If this condition is met then the account holder relationship can be ended.
Administrating accounts
Intoduction
The following processes can be identified:
- submit account;
- review account;
- approve account;
- close account.
Submit account
When a customer wants to open an account he or she must go to the bank office. The bank may require an identification but in small communities people are often known by the bank employees, so there may be no need for this. A bank employee with the role account clerk will enter the account data, account holderships and account currencies. In the future it may be possible to open an account via the BBS itself.
The account holder may choose a name for the account as account names can be chosen freely. The BBS generates the account number. The account number has a checksum similar to the Dutch bank account numbers (the 11 check). A valid account number and a valid account name are needed to make a valid transaction. After all the needed data items have been supplied, the account clerk submits the account holder data for processing.
If the account holder has not been approved yet, the processing of the account application will wait for the account holder approval.
Review account
If account reviewing is enabled then a bank employee with the role account reviewer will then verify the account data, the account holderships and the related documents. If the account reviewer sees errors in the data, he or she will send the data back to the account clerk. If the account reviewer sees problems with the application, such as a bad credit history of the applicant, he or she will advise to disapprove the account application. If there are no problems, then the account reviewer will advise to approve the application.
Approve account
If account approval is enabled then a bank employee with the role account approver will approve or disapprove the account application. He or she will approve the verified components of the application, which are the documents underlying the account opening and the account holderships. As soon as the account has been approved, it can be used by the account holder.
Close account currency
Closing an account currency means that:
- all pending collect permissions will be cancelled;
- all pending payment orders will be cancelled;
- all pending transactions will be finalised if possible or cancelled otherwise;
- the remaining balance must be transferred to a designated account.
If there are pending transactions the transferral of the remaining balance must wait until all pending transactions have been resolved.
Close account
When an account must be closed, all account currencies of the account must be closed first. If the account has been closed, new transactions for the account will be diverted to a designated account. The designated account will be:
- the contra account of the account if the account was a savings or loan account;
- the replacement account of the account if the account was a current account.
The replacement account may be an account in another bank. The contra account needs to be an account in the same bank as the account.
| 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).
Design and development considerations
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.
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.
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.
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 most likely cause of system malfunction is human error. All errors are recoverable except data loss:
You can mess up anything you want but you cannot lose your data. You can recover from any fault but you cannot recover from the loss of critical data. |
The technical architecture of the Basic Banking System (BBS) environment should be built around this primary concern. The technical architecture should be based on experience and observing good ideas and mistakes. In many situations there is a best way of doing things. Small organisations should be able to run the BBS 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.
- 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:
- data protection;
- system platform and application platform;
- high availability;
- features to avoid.
Data protection
Logical level
On the logical level there should be data and a backup that can restore the data. At any time it must be possible to use the backup to restore the data to the point just before it was lost.
In an Oracle environment, the backup consists of the most recent full backup set, the incremental back up sets on top of the full back up set, archivelogs, redo logs, a control file and a server parameter file (spfile).
Hardware level
On the hardware level the backup and the data should be on different disks unless you use mirroring. The backup and the data should be different logical units (luns) and the backup and the data should also be on different disk volumes.
Ideally, the backup and the data should be administrated by different administrators on different locations. This should protect the data against accidental dropping of disk volumes and the destruction of one location.
Operating system level
Unix environment do not have a recycle bin function. This is a serious issue and adding this feature would be a great improvement. On production environments, commands that can destroy data should have other names. For example, rm and mv should not work on production environments. Instead administrators must use another command, for example rmprod and mvprod.
In other environments rm and mv must work and rmprod and mvprod should not work. In this way adminstrators can only use the commands knowingly in a production environment and not mistakenly believe that they work in a test or development environment.
Administrators including root should not be able to remove data and backup files in production environments. When it is needed to move or remove data or backup files by hand, this must be enabled temporarily. Privileges to move or remove data and backup files should be revoked every night.
Database level
Copies of the control file and the redo logs should be on the data as well as the back up location. The archive logs and the back up sets should be on the back up location. The data files should be on the data location.
In production environments database administrators and other users should not have the privilege to drop tablespaces or to drop tables in other database schemas. In production environments database administrators must have their own personal accounts for regular operations and should not be able to log in as sys or system.
It must be possible for database administrators to acquire drop table or drop tablespace privileges in production environments when needed. Database administrators may be able to grant those privileges to themselves, otherwise it should be granted to them by a super administrator that never does any database administration work. Any granted extra privileges should be revoked every night.
Ideally, there should be two groups of database administrators: database administrators that can perform backup actions and database administrators that can perform data actions. In many cases this is not feasible.
In other environments those rules should not apply so if a database administrator drops a tablespace or a table in a production environment, he or she is doing so knowingly.
System platform and application platform
The technical architecture should be a scalable with but have a minimum number of components. The system is built in Oracle Apex. In this way only the Oracle database has to be installed on top of the operating system. The Oracle database has many features that seem cool such as advanced queueing but can cause trouble to less experienced system administrators.
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.
In order to save cost and to be independent of hardware suppliers the preferred OS platform is Red Hat Linux.
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. Those considerations can be useful for small banks that run the BBS.
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. In such a situation it may not be needed to have data and back ups on different 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 are not in the same tubes.
Database redundancy
It is possible to have a physical standby database that operates at a time lag. If the production database becomes unusable by operating system actions then it is possible to bring the standby database in the state just before the error occurred, switch over to the standby database and make it the production database. This requires technical expertise that may not be available to small sites.
Features to avoid
Introduction
Hardware suppliers and software suppliers offer technical features to improve scalability, availability and performance of applications. 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 because of Murpy's Law [+]. Only in large data centres with skilled personnel using those features can improve scalability, availability and performance.
Small organisations should avoid them because the cost of operating those features is often higher than the gains that may come from them. Small organisations should ask themselves why they should be open 7 days a week and 24 hours a day without interruption. Before the Internet banking emerged, most businesses were only open during office hours. So why should organisations 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 organisations can better avoid the use of the following features that require advanced knowledge:
- 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 organisation. Therefore virtual servers are difficult to handle for small organisations.
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 bring the cluster down.
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 can 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 a small 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 small organisations 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 small organisation. 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 useful if a cluster file system or storage load balancing is needed. 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 failures and downtime.
Single sign on and central user directories
The use of single sign on and central user directories, such as the Micrsoft Active Directory, require specific expertise and can 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.
In the BBS most users are account holders that do not have access to other appliations of the bank. Therefore the BBS has no need for single sign on and central user directories.
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. 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 MBKP0002.prd.miambank.us for production database MBKP0002 of Miami Bank and MBKD0011.dev.miambank.us for development database MBKD0011 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 location administrated by different personnel. The backup volume should contain 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 unlikely that the system administrator or the database administrator accidentally removes 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 idea to modify rm using a superseding script in such a way that it is not possible for the user root to database files and back up files. Therefore the database adminstrator must not be allowed to rm on /data and /backup. 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
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/MBKP0002/controlfile
- /data/oracle/MBKP0002/datafile
- /data/oracle/MBKP0002/onlinelog
- /data/oracle/MBKP0002/pfile
- /data/oracle/MBKD0011/controlfile
- /data/oracle/MBKD0011/datafile
- /data/oracle/MBKD0011/onlinelog
- /data/oracle/MBKD0011/pfile
In this example the /data/oracle/MBKP0002/controlfile directory contains the control files of the MBKP0002 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/MBKP0002/archivelog
- /backup/oracle/MBKP0002/backupset
- /backup/oracle/MBKP0002/controlfile
- /backup/oracle/MBKP0002/onlinelog
- /backup/oracle/MBKD0011/archivelog
- /backup/oracle/MBKD0011/backupset
- /backup/oracle/MBKD0011/controlfile
- /backup/oracle/MBKD0011/onlinelog
In this example the /backup/oracle/MBKP0002/archivelog directory contains the archived database recovery logfiles of the MBKP0002 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/MBKD0011/adump
- /logging/oracle/MBKD0011/bdump
- /logging/oracle/MBKD0011/cdump
- /logging/oracle/MBKD0011/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'