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.
The quick answer to these questions is the standard Oracle DBA answer of "It depends", followed by more questions.
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.
|
Related Reading 
Mac OS X for Unix Geeks |
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.
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.
Do you need to create automated tasks that are more sophisticated than the FileMaker ScriptMaker feature?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.
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.
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.
|
It's possible to manage a production FileMaker database without having any type of support contract. To FileMaker Inc.'s credit, they have created a very manageable product and generally make free database patches available to their customers.
But with an Oracle database you have to maintain a support contract before you can even download patches to fix problems. Due to the complexity of the Oracle database, there is no way a single person can know everything about managing every aspect of the database. Oracle's own support department is subdivided into over a dozen different support areas just to provide support to customers. Additionally, there are bugs, incorrect and poorly worded documentation for complex tasks for which only Oracle has the answers. And there are some issues for which even Oracle doesn't have the answers because they come under the category of being bugs that will be fixed in the next major patch release.
Being a faceless server application there's no graphical user interface built directly into the Oracle database as there is with FileMaker. The first tool that most DBAs will use is SqlPlus, which is available in command line, Java, and Web-based versions. Most DBAs will generally use the command line version shown below in Figure 1.
|
|
This tool provides access to the actual database server software for database creation, database startup, database shutdown, and for running SQL queries. With Developer Release 1, SqlPlus is one of the few tools available for managing the database. The production release will include the full Oracle Enterprise Manager Console application, providing a graphical interface for database instance management, import/export, backup, recovery, graphical Log Miner interface, creation of database objects, and graphical data editing. Please see Figure 2 for an example of the OEM Console running on Windows.
|
|
Until the OEM software is available, it is possible to use external tools like DBArtisan (unfortunately only available under Windows), SQLGrinder or SQLX Manager J which run natively on Mac OS X.
This is not the case with FileMaker. Everything you need to develop and deploy a database is contained within the FileMaker application. If you want to create a stand-alone FileMaker application, then you would purchase FileMaker Developer, but for most databases accessed either locally or across a network you won't need to make this extra cost purchase when working with FileMaker. (You will have to purchase a copy of FileMaker for each user who will be accessing the FileMaker database.)
|
|
A view of the data within a similar Oracle database table is shown in Figure 4.
|
|
It will be necessary to purchase some type of development environment to create a stand-alone graphical application for an Oracle database.
One development tool, which works very well on Mac OS X and works cross-platform too, is Revolution. If you are familiar with Apple's Hypercard, then you will feel very comfortable with this development environment. Compiled double-clickable database applications can be created that make full use of a graphical interface.
If you want to create Web-based applications, you could write Perl scripts making use of the DBI and DBD:Oracle Perl modules to access the Oracle database. These applications could run within the Apache Web server that is already installed on Mac OS X. This would be the lowest cost development and deployment environment that you could put together by using these open source tools. But low cost does not mean low performance. Using the mod_perl Apache module will enable this type of Web environment to serve many times the load of the same system that just runs Perl code as standard cgi scripts.
One interesting product, which could be helpful for automating the development of Perl, ASP, PHP, ColdFusion .NET, and JSP Web-based applications is Code Charge Studio. Once you define the application functionality in the graphical environment, you just pick the output programming language, and the application generates the code. There are no deployment licensing costs or royalties required on the resulting code that this tool generates. Unfortunately this tool is only available for Windows, so you would need to either run it on a PC or under Virtual PC.
Oracle has developed their own product named JDeveloper for creating Web-based Java applications. This tool provides a wealth of graphical features for RAD development, project/team management, UML modeling, and software configuration management. JDeveloper provides a graphical interface to the feature-rich Oracle BC4J Java framework that handles the management of data between the Web user and the database. It is important to note that if you make use of some of these features like BC4J, you do need to purchase licenses for the Oracle 9iAS application server product. This $20,000 per-processor cost is in addition to the $995 licensing cost of the JDeveloper software. As with many Oracle products, you can download JDeveloper at no charge for evaluation purposes from otn.oracle.com, but you will need to license the product for a production implementation. JDeveloper is now written as a Java application but is only officially supported on Windows NT/2000/XP, HU/UX, Linux, and Solaris at this time. Since it is a Java application containing no x86 code, it is possible that Oracle will add official support for Mac OS X in the future.
Apple also has a full-featured Java development environment with their WebObjects product. One of the notable features of WebObjects is that it's very economical to license and deploy compared to most of the other application servers on the market. Java capable applications servers (IBM WebSphere, BEA WebLogic, Oracle 9iAS) are often licensed for prices in the range of $20,000 - $30,000 per processor.
Apple's WebObjects development product is priced at $700, and it includes an unlimited processor deployment license and software that can be deployed on any Java 2 capable Web server, including Mac OS X, Linux, and Solaris. In fact Mac OS X server (both 10 user and unlimited editions) include a WebObjects deployment license as part of the purchase price. As you would expect with a product from Apple, WebObjects provides a rich user interface for creating HTML or desktop Java client applications. Apple implements an enterprise objects framework in Java for managing database access, sessions, events, and Web requests. Quick prototypes can be created with the Direct to Java and Direct to HTML Assistants. Apple is one of the few development tool vendors that does not provide any type of demo software download directly from their Web site.
Actually with Developer Release 1 of Oracle for Mac OS X you need to know a lot of UNIX commands just to build, start, and stop the database. When the production version is released, you'll have the option to install the database with the Java-based Oracle Universal Installer and create the database 3instance with the graphical database creation wizard. You will also be able to use the OEM Console application to view/edit data, create tables, and manage the instance.
With Developer Release 1 you will need to know quite a few commands in order to create and manage the database instance since none of the graphical management tools are available.
|
Nobody can complain that Oracle doesn't provide enough documentation for their database. There are 118 HTML/PDF reference books provided on the documentation CD for the production Windows version of Oracle 9.2.0. This info is also available via the Web at tahiti.oracle.com (there is no www prefix to this URL). The "getting started" link at the top of the Oracle 9.2 documentation page is a good place to start in order to read about the duties of a database administrator, developer, network administrator, or security officer. Each of these topics includes links to a subset of the entire list of books.
As a DBA, I find that I generally need to be familiar with almost all of the books to some extent even if I have not completely read each of them. This way I can suggest appropriate topics for reading and examples for various people I work with who need to access, maintain, or write programs for the database.
|
Related Reading Learning Unix for Mac OS X |
Once a database license and support contract are purchased, an additional set of information resources becomes available via Oracle's Metalink website. It is often very helpful to be able to perform a quick search of Metalink when a new or unusual error message is logged by the database. The Metalink search provides a search of previous issues submitted by customers via the Metalink forums, knowledge base records, white papers, and example documentation. If a Metalink search does not produce results. then a Google web/newsgroup search is a good second choice. There is also a built in error number reporter included on UNIX systems (even in Developer Release 1). For example if the error is ORA-32006, you type the following in the Terminal window:
oerr ora 32006And you get the following response:
32006, 00000, "%s initialization parameter has been deprecated"
// *Cause: A deprecated parameter was specified at startup
// *Action: Consult Oracle documentation to find new parameters
// to use instead.There are many items to consider when contemplating a migration from FileMaker to Oracle. If you have the budget and need for a high performance and highly scalable database, then Oracle would make a great choice. There is a wealth of information in the form of books, Web sites, and consultants for Oracle databases. Oracle's own technical support service is also an excellent reason to consider Oracle. But if your budget can't afford an Oracle database, then a more economical alternative such as MySQL using a Mac OS X graphical front-end application like SQL4X Manager is worth considering. Future production releases of MySQL 4.0.X will even include replication and online backup features. OpenBase and FrontBase are additional commercial alternatives available for Mac OS X, which offer database replication and online backup features.
The availability of the Oracle database for Mac OS X is a significant event for Apple. Even in its development release status, Oracle 9.2 for Mac OS X is functionally very complete and suitable for testing and evaluation. Its modest hardware requirements make it a viable install even for the laptop of a DBA or software developer.
Instance: An instance is one running copy of the Oracle executable software. You can run multiple copies of the Oracle executable on the same computer with the appropriate amount of memory, disk space, processing power and configuration steps. You can even run different versions of the database on the same server.
SQL: Structured Query Language. These are the commands available to manage data and objects within the database. These commands are broken up into DML (Data Manipulation Language) and DDL (Data Definition Language) commands. The DML commands are used to query, insert, and delete information. The DDL commands are used to make structural changes to the database such as creating the database itself, adding/dropping tables, tablespaces and columns.
PL/SQL: This is an Oracle specific procedural language that is compiled and stored either within the database or externally by the gcc compiler for higher performance. Many of the newest database features are actually written in PL/SQL. As features become more popular or require higher performance they are re-written in C and incorporated into the core of the database.
SqlPlus: SqlPlus provides a command line interface to startup, shutdown, create, query, and maintain the database. This little explanation doesn't really do justice to the complete functionality of SqlPlus. There are entire books written about SqlPlus.
Oerr: This handy little command line utility prints out the text of error messages for Oracle databases running on UNIX servers. This way you don't have to go searching the Error messages manual in order to read a quick summary of the error message text. There may be additional info located within the Error messages HTML manual, and searching Metalink is also helpful if you need more info.
otn.oracle.com - Discussion Forums - Technologies > Platforms > Apple. (Requires free OTN membership account)
David Simpson is president of .com Solutions Inc. and the developer of the Installgen application.
Related articles:
Installing Oracle 9i on Mac OS X, Part 1 -- The Oracle 9i Developer Release for Mac OS X opens up a new world of corporate database management for both Apple and Oracle. David Simpson begins this series with a look at the issues for DBAs who are considering making the move to Oracle on OS X.
Return to the Mac DevCenter.
Copyright © 2009 O'Reilly Media, Inc.