How to deploy an Oracle database?

Question

I have an ASP.NET application that connects to an Oracle or SQL Server database. The installer was designed to install a new database on an existing SQL Server using sql commands, such as "restore the database ...", which simply restores the ".bak" file, which we save under source control.

I am very new to Oracle, and our application has only recently been ported to 10g compatibility.

We are currently using the exp.exe tool to create the .dmp file, and then using imp.exe to import it into the developers window.

How are you going to create the "Oracle Database Installer"?

Do you create a database using script files and then populate the database with the default data?

Did you run the "imp.exe" tool backstage?

Do we need to provide a clean interface for system administrators so that they can simply select the destination server and done, or just provide them with a ".dmp" file? What are the best practices?

Thanks.

Answer 1

Q: what do your customers know about Oracle?

• Nothing? You should probably rethink this position. Oracle is very large and complex. If you assume that your customers do not know anything, then you will begin to provide tutorials and help out of place.

• Minimally competent? If they are competent, they know enough to manage it themselves. In addition, they know enough to run a script that executes SQL.

• Actual DBA? Most organizations that can afford Oracle can afford real DBAs. A real database administrator can do many things — they don’t need many hands. Some of them like to set storage options according to their store standards.

You must provide the script with reasonable defaults. You should define your script in such a way that someone can easily find all your storage options and, if necessary, configure them.

Your source data can be exported / imported or through a script. I prefer script.

Answer 2

I have repeatedly done this on both sides (consumer and supplier) as a database administrator, developer and architect.

As a provider, one of my great achievements (in 1996) was the creation of an installation CD for commercial insurance claims management software for the largest insurance companies (multi-million dollar product). This installation CD installed the Oracle 7.2 RDBMS engine, the FileNet optical storage system (scans paper documents and creates cataloged binary versions) and our custom claims processing application (built-in to VB 4.0), all integrated and ready to run. As part of the installation process, the user can skip the installation of Oracle software or configure it, and the user can configure / override the database configuration in all its basic details (database, schemas, table spaces, sizes, disks, etc.).

I also provided a field service for this product, which included, when necessary, switching to a client site. I tested the installation CD literally hundreds of times in any imaginary scenario that I could reproduce, and we NEVER had a field failure that required even a phone call, not to mention the trip (I traveled four times, but for pre-sale material instead of this).

Most recently (2007) I wrote a script to create an Oracle 10g database for an internal system on a megacasing. In production, the database was 8 TB in size, mainly for a single transaction table with a large amount of data. In the test, the database was about 1 TB in size for a modest server. In development, the database was about 100 MB in size to work on my laptop. EXACTLY SUCH SCRIPTS created all three environments, and I could expand them to handle the new environment / machine in about five minutes. This database included extreme performance tuning, so tuning all the relevant characteristics was absolutely critical.

Back to the insurance claims processing product - let me add that I was initially hired to convert it from a SQL Server database to an Oracle database. This transformation was defined as a business need, as most potential customers did not see the SQL Server-based product as a professional serious solution. This is not so common today, but it still applies in general: a software product has a better chance of penetrating the market if it can use several database options, as preferred by target customers (especially enterprise-class clients).

Similarly, the installation CD was also seen as an important element. However, this situation and many others have shown me that most “real” database administrators will not accept import-based database installations. As a database administrator and architect, I know that I definitely will not for the same reasons.

Simply put, installing an import-based database gives the client virtually no control over the resulting database. This is opaque to the client, and they do not ask what he did. This forces the client to make great efforts to try to realize that little control that they can do. This is known to be fragile and error prone (Oracle imports are well known for ownership and permissions, restriction problems, etc.). Weighing all of these impacts, installing an import-based database is unprofessional - at first it does not pose customer needs.

Database installation scripts provide the right kind of transparency, customizability, selective repeatability and overall client control that requires professionalism. It also encourages you to correctly understand the implications of database design decisions so that imports fail.

Best wishes.

Answer 3

Personally, I prefer SQL scripts to create a database and load data where possible. I usually use PL / SQL Developer . It has good features for generating scripts from an existing database. After that, you can run scripts using sqlplus or any application code that can execute arbitrary SQL (for example, JDBC with Java). Toad is a more common (and more expensive) Oracle development tool.

The only limitation of SQL export is that it cannot export CLOB / BLOB fields. If you have one, you either need to do them separately (as a PL / SQL export), or do it all as a PL / SQL export. Theres no dramas with this, besides the file, it is actually a binary export (.pde extension) and more limited to how you can execute it.

Another big advantage of SQL source files is that they can be easily versioned. It is very convenient to create a database environment by running one or two scripts.

Import and export tools for Oracle, I think, are more applicable for backup and restore operations.

Now, as for delivering this to the client, from your comments, it seems that you will give it to the database administrator. Virtually any installation of Oracle will include database administrators. They will be fine with SQL scripts to create the schema and load the data. They will perform most of the configuration on a particular site (for example, configure SGA, temporary table spaces, number of simultaneous connections, etc. Based on the expected load).

You, as a supplier, can provide guidance on any appropriate configuration, and you can participate in support and possibly installation, but ultimately it is up to them to find out what works for them. Oracle runs on a large number of operating systems and hardware options with endless variations in network topology and firewall configuration. You cannot influence all this on the installer or even on the set of instructions (except for the recommendations mentioned earlier).

Answer 4

The last time I participated in creating (oracle) db (for a large enough company with its own database administrators), database administrators wanted to know things like:

• what we wanted to call db,
• what table spaces we need, and an estimate of how much data will be in each
• how many users will connect.

(From memory) they installed db and tablespaces, then we provided a combination of simple scripts that they could run (or clear instructions if the task was not easy to automate)
As I said, this was for an internal application, so your mileage may vary, but in my case they wanted all the instructions to be clearly stated so that (a) there was no possible misunderstanding leading to incorrect conduct of the case, and (b) there was no guilt on their part if something didn’t work (“we just followed the instructions”)