Installing Oracle 9i on Mac OS X, Part 2

What the FileMaker developer can expect from Oracle 9i on Mac OS X

Editor's note: In the first part of a three-part series exploring Oracle 9i on Mac OS X, David Simpson provided you with some excellent background information and a look at the ramifications for Solaris administrators. Here in Part 2 he discusses the conversion process for FileMaker administrators.

Why license Oracle 9i? Is it worth the extra cost?

The quick answer to these questions is the standard Oracle DBA answer of "It depends", followed by more questions.

Do you need better scalability beyond the 250 user FileMaker Server limit?

If your users simply need read only access to their data via desktop or Web interfaces, FileMaker database files can simply be duplicated on a periodic basis from a master server to multiple cloned servers. But this type of scalability may not work very well for databases that require both read and write access to the data because each database will contain differing data.

It would be possible to extract newly created records from each of the cloned databases and aggregate the data together on one master server. But in this scenario, how do you handle existing records, which have simply been updated? User #1 might update field #1 of a record on one server, and user #2 might update field #2 of the same record on another server. Which version of the record should be retained? Now you start to see some of the issues involved with writing database replication software.

Oracle reports that Oracle 9i has been tested with 64,000 simultaneous users connected to a single database instance using the shared servers (formerly MTS) feature. Therefore, if you need hundreds or thousands of users to be directly connected to your database via client software, an enterprise class database like Oracle will be necessary. If you need to have hundreds or thousands of users connecting to your database via a Web-based application server like Apple's WebObjects server, then it will not be mandatory to have as many simultaneous connections to the database.

In this scenario, the application server will manage the aggregation of connections to the database. In order to conclusively answer these types of questions, it is generally necessary to prototype the installation in advance in order to perform load testing of all of the components. In any case, the use of an Oracle database for even dozens of users should be considered for the many recoverability features available within the database.

Do you need to create a highly available database configuration to provide availability to the data under a wide range of disaster scenarios?

Related Reading Mac OS X for Unix Geeks By Brian Jepson, Ernest E. Rothman Table of Contents Index Sample Chapter Read Online--Safari Search this book on Safari:   Only This Book All of Safari Code Fragments only

Oracle offers multiple high availability features, which is one of the key reasons customers implement a database like Oracle. These features include:

• Standby databases: Oracle logs all transactions into archivelog files if the DBA has enabled this feature, which is highly recommended). In a standby database configuration, the primary database electronically sends copies of all of its archivelog files to a second database, which is generally running on another computer as the standby instance. The standby database is generally only made available for read-only access on an intermittent basis; the rest of the time it is running in recovery mode in order to continuously apply the archivelog data (for EE edition).

  If the primary server fails, the standby instance can quickly perform its final recovery tasks, then be started up and used in place of the primary instance. This feature allows the primary and standby instances to be located on separate servers , can be physically located many miles apart from each other. This allows recoverability in situations where a disaster affects all of the computer equipment within the room or building at one of the locations containing the computer equipment.

• Materialized Views: A materialized view generally provides a read-only copy of summarized data from a master database to one or more client databases. This functionality is often used to provide a summarized copy of the data to improve the performance of database queries. Unless the materialized view is created as an updateable, materialized view (requiring EE edition), its contents will only be available on a read-only basis. This feature is sometimes used to enable the updating of mostly static data used by field salespeople on laptop computers running the Personal Oracle database. Each evening the salesperson dials into the corporate network to retrieve updated info from headquarters and uploads the daily sales orders at the same time.

• Advanced Replication: Advanced Replication (requiring Oracle EE edition) enables multiple database servers to maintain copies of selected database tables in sync with each other and available for simultaneous reading and writing by users. If either server encounters a failure, the users logged into that server can be automatically redirected to the other server via the use of the Net8 Transparent Application Failover (TAF) feature. This feature permits an almost instantaneous failover for the users and also permits the database servers to be physically located far away from each other.

• Streams Replication: Streams Replication (requiring Oracle 9.2 EE edition) improves upon the Advanced Replication functionality by providing for the transformation of data as it is being replicated and offering the potential for greater throughput of transactions in future revisions of the Oracle database. This feature reads through the archivelog data written during the normal course of operating the source database and tranforms the data with user-defined procedures (if requested) before transmittal to the other database instances. At the destination database(s) the data may be transformed further prior to being applied to the database.

  Oracle has also designed the Streams Replication method to accept and replicate data from applications and databases other than Oracle. This is a very new feature for Oracle which was just introduced a few months ago when Oracle 9.2 was released, therefore customers must apply the 9.2.0.2 patch (not yet available for Mac OS X) and perform extensive testing prior to committing to a production implementation.

• RAC: Real Application Clusters (RAC) makes use of a single shared hard disk or disk array used by multiple computers and database instances at the same time. If any individual computer or instance fails, the clients are immediately connected to a surviving database instance by TAF.

  RAC offers the advantage of permitting computers to be added or removed from the RAC configuration for maintenance without affecting availability of the server from the client perspective. Customers can start with a small RAC configuration and then add more servers on an as-needed basis to improve performance at a low hardware cost. The single point of failure ends up being the centrally located disk array. All of the servers in the cluster need to be physically located close to the disk array, thus making them vulnerable to a disaster, which affects the computer room or building where they are housed. By itself, RAC can't be considered to be a complete high availability solution, therefore it ideally would be combined with either a standby or replicated database configuration in order to ensure that there is an offsite live copy of the data available.

• Import/Export: The Oracle supplied Import/Export utilities can be used along with DBA written scripts to implement a periodic refresh of data from a master database into one or more alternate databases. This technique is sometimes used for cost sensitive implementations in which a customer may not be able to afford the DBA staff to set up or maintain a replicated or standby configuration.

Do you need to create a no data-loss configuration with transaction rollback?

If the Oracle database is configured to operate in archivelog mode--which is generally recommended--then redo data is written to disk as transactions occur within the database. During a serious recovery situation, the database can be restored to some point in time in the past from backup files, and then the database is rolled forward to a specified point in time by using the archived redo from the archivelog files.

The database can be recovered as long as database backups and archivelog files are available. It's even possible to read through archivelog files on the disk with the graphical version of the Log Miner utility in order to manually undo individual transactions. These features can be used along with database export files, standby/replication implementations, and RMAN backups to provide the DBA with a comprehensive set of tools to recover from almost every potential data loss scenario.

Programs can be written within the Oracle database using either the PL/SQL language or Java. The PL/SQL language is actually used by the Oracle corporation to implement many of the newest features within the database. With Oracle 9i and higher, both the PL/SQL and Java code can be compiled into native machine code with the resulting object code stored as files on the server. PL/SQL code can run 2x to 8x faster after it has been compiled natively. This functionality is primarily useful for customer-written code at this time because some of the internal database procedures and packages do not work correctly if they are natively compiled.

Do you have the financial resources to license and manage an Oracle database?

If spending $1,000 for FileMaker Server sounds like a lot of money, then you will be shocked at the licensing costs associated with an enterprise class database like Oracle.

The database can be downloaded at no charge from otn.oracle.com for evaluation purposes, but once the database is ready to go into production it does need to be properly licensed. Oracle encourages customers to license the database on the per-processor licensing model. With this licensing method you count up the number of CPUs in your computer, and multiply that number by the licensing cost of the database and database options you need.

Currently the Standard (STD) edition of the database is priced at $15,000 per processor, and the Enterprise (EE) edition is priced at $40,000 per processor. The RAC feature is $20,000 per processor extra, and you need to add 22 percent annually for the support contract.

It's possible to license the database on a per-user basis, which makes financial sense if there'll never be many users accessing the database. However, the licensing method can't be changed after it is initially licensed. So if the business grows and requires significantly more users to access the database, the costs could exceed the costs under the per-processor model. You also have to understand what Oracle corporation considers to be a user for the purposes of licensing purposes. If 1,000 users access the database through an application server, which only makes five connections to the database, then Oracle will require that either 1,000 user licenses be purchased or that the database be licensed via the per-processor pricing model.

The Oracle STD edition is licensed at $300 per user (with a five user minimum), and EE edition costs $800 per user (with a 25 user minimum). There is still an annual support fee of 22 percent, which should be budgeted in addition to the licensing fees. If the support contract is not paid each year, then the customer is not licensed to upgrade to the latest version of the database and must re-purchase all of the licenses over again in order to upgrade versions. This section only gives you a brief overview of the available licensing options and costs, so if you have additional questions you really should contact an Oracle sales representative.

You will need a DBA to set up and manage your database(s).

Unlike a FileMaker database in which even a casual user can set up and administer the database, you do need an experienced DBA to manage a production Oracle configuration. If you are continually making changes to the structure of your database, you'll probably need a DBA on your staff full-time. The DBA will need to work with the programmer(s) in order to plan the structure of the database, create/drop indexes, and create views and stored procedures to improve usability and performance of the database. These tasks will be in addition to the DBA's responsibilities to maintain both the production and development databases. A database installation that makes use of pre-packaged software, which rarely needs changes, could be maintained remotely by any number of different companies that provide remote DBA services via the Internet.