I went to an Oracle seminar once, and the instructor told us that as of version 10g, it was possible to create a database in Oracle from the sqlplus command line with the sparse verbiage “create database;” and nothing else. He then challenged us to figure out how it could be done. Keep in mind, that as an Oracle DBA for about 20 years, I have seen “create database” clauses, on certain platforms, with certain options, that ran for pages of SQL in a single statement. Specifications for the command in the oracle manual are here:
The complete description, including all options for CREATE DATABASE covers 21 pages of the manual!!!
There is also a new command called “drop database;” and both of these were likely implemented to make oracle configuration simpler and more consistent with the other RDBMS. So I took up the challenge, and was successful in creating a database in this manner. I did this as an exercise, and because I believe it teaches some interesting concepts as to exactly how oracle works. It could also be useful to quickly stand up an instance to verify the software install, meet a tight deadline, or validate available resources for a more complex build later on.
MINIMAL REQUIRED SETUP
Starting an Oracle instance still requires, at a minimum, a valid initialization file, with certain specifications, sufficient system memory and disk space, and system rights to create files and access system memory. Your user session (usually called “oracle” on most systems, needs ENV settings as well. Many shops use the “oratab” file to set ORACLE_HOME and ORACLE_SID and other settings, so be sure to add this database to that file, which is something that the Oracle installer does anyway.
I called my database, for these purposes “mynewdb”. Here is the sparsest initmynewdb.ora initialization file possible to create a database:
Some of these parameters are required, depending upon your system specifications, and others just make the database more useful and self documenting (such as the bdump, cdump and udump directories). The above assumes that directories specified for control_files and db_create_file_dest already exist. Before trying this, create them and check carefully that they are spelled correctly. If not, your startup will fail.
After running “create database;” with the above, a running database exists. However, the catalog and dictionary still need to be updated to make it truly useful. This is done by running the traditional catalog and catproc scripts:
Sqlplus > @?/rdbms/admin/catalog
Sqlplus > @?/rdbms/admin/catproc
In the log above, I also create a temporary tablespace, a users tablespace (both considered minimal best practices), and make a list of users. As you can see, 7 are created by the Oracle binaries. An additional step would be to make those tablespaces the temporary and default respectively for each of thoese users. This can be easily scripted.
Of course, if you have requirements for options or features such as partitioning or statspack, scripts to create those optional objects supporting them will also need to be run. Your default database is also minimally secure, and should probably have default passwords for users changed, but that is considered out of the scope of this discussion. Even though more work may need to be done, you can say your database is up and running!
HINTS AND TROUBLESHOOTING
I’d be lying if I said this worked the first time. On most systems, the storage parameter defaults are insufficient to support opening the database. That is why parameters such as sga_max_size and shared_pool_size are specified for the solaris 64-bit example above. If your first attempt fails, be sure to remove controlfiles and any directories generated by the failed creation attempt.
My next document will highlight the new “DROP DATABASE” syntax. While it sounds as simple as pie, like anything else Oracle, there are caveats and a moderately difficult recipe to follow as well. Maybe you can even figure it out on your own?
PROOF THAT IT WORKS (please do try this at home!):
> sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Mon Sep 14 14:47:00 2009
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 503316480 bytes
Fixed Size 1268340 bytes
Variable Size 427820428 bytes
Database Buffers 67108864 bytes
Redo Buffers 7118848 bytes
SQL> create database;
SQL> set termout off
SQL> set termout on
SQL> select file_name from dba_data_files;
3 rows selected.
SQL> create temporary tablespace temp1 tempfile;
SQL> select username from dba_users;
7 rows selected.
SQL> create tablespace users datafile;